Category Archives: MySQL

Delimited Text Format Backups with mysqldump utility

Delimited-Text Format Backups with mysqldump utility

TAB Option
Without –tab option
MYSQLDUMP writes SQL statements to the standard output as default. Output consists of DDL statements to create dumped objects (databases, tables, stored routines, and so forth), and DML statements to load data into tables like insert. It will generate output as one file which will used to reproduced the current state of database.

With –tab option
MYSQLDUMP produces two output files for each dumped table.
One file is used for tab-delimited text, one line per table row. This file is named tbl_name.txt in the output directory.
Second file has CREATE TABLE statement for the table to mysqldump, which writes it as a file named tbl_name.sql in the output directory.
Example of tab option
For a table named t1, the files are named t1.sql and t1.txt.
The .sql file contains a CREATE TABLE statement for the table.
The .txt file contains the table data, one line per table row.

Backup with delimited format

-- Backup the database in /tmp directory
mysqldump --tab=/tmp db1

-- Backup the database with specific option as used needed for other ETL process:
mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

Restore or Reload Delimited file
Restore and reload the delimited file into the database.

-- First Way:
shell> mysql db1 mysqlimport db1 t1.txt

-- Second way run from mysql commands for import data:
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

-- First way with options:
mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

Advertisements

MYSQLDUMP for Backup and Restore the MySQL Database

MYSQLDUMP for Backup and Restore the MySQL Database

MYSQLDUMP is a used to performs the logical backups, generating a set of SQL statements like DDL , DML for reproduced backup Schema.
It dumps one or more MySQL database for backup or transfer to another SQL server.
You can also generate output in CSV, other delimited text, or XML format.

Drawback of MySQLdump
The restoration procedure can take a long time to execute if you have a very large database.

Note:
–To make incremental backups, we need to save the incremental changes.
–In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the –log-bin option to enable that log.
–Always run the MySQL Server with the –log-bin option, or even –log-bin=log_name, where the log file name is located on some safe media different from the drive on which the data directory is located.

Backup with MYSQLDUMP
mysqldump is used to take logical backup of the database or multiple database.

Following is the syntax of MYSQLDUMP:

Syntax:
mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] Your database username
[pass] The password for your database (note there is no space between -p and the password)
[dbname] The name of your database
[backupfile.sql] The filename for your database backup
[--opt] The mysqldump option

Example:
--Take Backup of SalesDB database:
mysqldump -u root -p Salesdb > Salesdb_backup.sql

--Take backup of tables present in salesdb database such as TRAN, TRANX :
mysqldump -u root -p Salesdb TRAN TRANX > TRAN_TABLE_backup.sql

-- More databasez backup taken in one commands:
mysqldump --databases db1 db2 db3 > all_database_dump.sql

-- command to use mysqldump to simply backup all of your databases
mysqldump -u root -p --all-databases > alldb_backup.sql
mysqldump --all-databases > dump.sql

--single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change.
mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql

Options in mysqldump
–all-databases – this dumps all of the tables in all of the databases
–user – The MySQL user name you want to use for the backup
–password – The password for this user. You can leave this blank or include the password value (which is less secure)
–single-transaction – for InnoDB tables is used for taking consistent backup with mysqldump after changes present in log bin.

Restore commands in mysqldump

1. Connect with the mysql Server:

mysql -u username -p

2. Create an appropriately named database on the target machine;

CREATE DATABASE database_name;
exit

3. Load the file using the mysql command:

mysql < dump.sql
mysql db1 < dump.sql

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
mysql -u root -p Tutorials < tut_backup.sql
mysql -u username -p database_name < backup_name.sql

Manage the Isolation level in MySQL

Manage the Isolation level in MySQL

InnoDB offers all four transaction isolation levels

Serializable:
Most conservative reading level, all transaction is treated complete separated.
Always get accurate data, but performance is slow because it make every transaction in serialized form.

REPEATABLE READ:
Default for INNODB.

READ COMMITTED
Uses a locking strategy that is ideal for performance.

READ UNCOMMITTED
Least amount of protection between transactions.

File used to change transactional level in MySQL:

C:\MYSQL\my sql server 5.6\my.ini

Parameter:
transaction-isolation = REPEATABLE-READ

Check parameter:
show variable like 'tx-isolation';

Note: Need to reboot the MySQL before my.ini file parameter change affects

Check the Current Isolation level:

select @@global.tx_isolation as Global_isolation_level,@@tx_isolation as session_isolation_level;

Change the isolation level at session level

set session transaction isolation level read committed;

Change the global transaction isolation level

set global transaction isolation level read committed;

set global transaction isolation level serializable;

You can change with following commands also:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';

Grant and Revoke commands in MySQL

Grant and Revoke commands in MySQL

1. Login with mySQL Database.

mysql -u root -p

2. Create user with Grant command if user is not presented.

--Create user at same time with grants privileges on db of all objects.
grant select on database_name.* to 'testuser'@'localhost' identified by 'Password';

-- Grant all privileges make user as Superuser
grant all on database_name.* to 'testuser'@'localhost;

-- Grant select on specific table
GRANT SELECT ON db_name.sales TO 'testuser'@'localhost';

-- Grant privileges to all the databases
GRANT SELECT, INSERT ON *.* TO 'testuser'@'localhost';

--Grant on specific columns of the table
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'testuser'@'localhost';

-- Grant procedure or routine priviliges
GRANT CREATE ROUTINE ON DB_NAME.* TO 'testuser'@'localhost';
GRANT EXECUTE ON PROCEDURE DB_NAME.myproc TO 'testuser'@'localhost';

3. Check the permission present on the User:

show grants for 'testuser'@'localhost';

4. Revoke the permission from User in MySQL:

-- Revoke all privileges from the User.
revoke all privileges on database_name.* from 'testuser'@'localhost';

--Revoke only delete privilege
revoke delete on database_name.* from 'testuser'@.'localhost';

--Revoke only alter privilege
revoke alter on database_name.* from ''@'localhost';

--Revoke CREATE permissions for all databases * and all tables *
revoke create on *.* from 'testuser'@'localhost';

--Revoke drop from specific database
REVOKE DROP ON database_name.* FROM 'testuser'@'localhost';

--Revoke all privileges with grant option
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user

5. Flush commands is used to reload all the privileges after making all the changes with grant or revoke commands in MySQL. It’s better to run this commands for refresh the privileges.

FLUSH PRIVILEGES;

Storage Engine in MySQL

Storage Engine in MySQL

A Storage Engine is the way to manages the table data and how it stored in the system. MySQL used different storage engine to manage the tables. MYSQL is plug gable Storage engine is select the storage engine which you need and which fulfill you requirement. MySQL Support multiple Storage engine which manages Transactional table or non Transactional table.

Following are the Storage engine support by MySQL:
InnoDB
MYISAM
Merge
MEMORY
EXAMPLE
FEDERATED
ARCHIVE
CSV
BLACKHOLE

INNODB Storage Engine
Default storage Engine from 5.5 or later
High reliable and high performance storage engine
Transactional Storage Engine
Support ACID Property
Handle large number of update delete operation
Support for relational constraints

MYISAM Storage Engine
Default Storage Engine before 5.5 version
Non Transactional Storage Engine
Table level locking, its good for data warehousing
Not support ACID property
No Support for relational constraints

Merge Storage Engine
Good for data warehousing
Logical group a series of table into a single object.
Data mining technique used on Data warehouse for fetch data.

MEMORY or HEAP Storage Engine
Non-transnational
Non persistent
Store all in RAM

FEDERATED Storage Engine
Distribute database environment.
Can link separate MySQL Servers to Create one logical database from many physical servers.

Archive Storage Engine
Stores and retrieve large amount of history data or archive information.

CSV Storage Engine
Stored data in files, Uses comma-separated values formats, Good for exchanging data with other environment in CSV format.

BLACKHOLE Storage Engine
Enter data but not able to retrieve it back. Used to distribute replicate data but not store locally.

MySQL

MySQL is a relational database management system (RDBMS) based on Structured Query Language (SQL). Databases run the back-end of almost every website, business, corporation, school, etc; MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle.

• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• It is free of cost
• MySQL works on many platforms and with many languages including PHP, PERL, C, C++, JAVA, etc.
• MySQL works well even with large data sets.
• MySQL supports large databases, up to 50 million rows or more in a table.

Keypoints to use MYSQL:-

• Database: A database is a collection of data stored in form of tables using various rows and columns.
• Redundancy: it means duplication of data. Storing data twice to make the system faster.
• Primary Key: A primary key is unique key, which does not contain null value.
• Foreign Key: A foreign key is the linking key between two tables.
• Compound Key: A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
• Index: An index in a database resembles an index at the back of a book.
• Referential Integrity: Referential Integrity makes sure that a foreign key value always points to an existing row.

How to Install MYSQL:-
1)The first step is to download the Mysql server and choose the platform for which we want to download the server for example windows ,Linux or for Unix etc;
2)Next is the turn to install the mysql server.

mysql-installer

Next question is asked for how many bits you want to install.
If you’re on Windows7 32bit then you should only install MySQL Server 32bit. If you use Window 7 64bit, then you have the option, but the decision will be based on the other technology you intend to use with it. and after that process

• On the Welcome page, press the Install MySQL Products link.

• Then there is License Agreement page, Check the box to accept the license terms and press Next.
• On the Choosing a Setup Type choose Custom so you can decide exactly which packages to install. This is the only place where you can specify whether to install 32-bit or 64-bit MySQL Server.

• On the Select Products and Features page, select the 32bit or 64bit versions of all the applications that you want. At the very least get:
o MySql Server
o MySQL Workbench (a UI used to manage your local databases)
o MySQL Notifier (adds a tray icon that lets you quickly start/stop MySQL server)
o MySQL Connectors (you’ll probably want to install them all because you never know              which future tool might depend on a particular connector)

• On the Check Requirements page, install any missing requirements by pressing the Execute button, or go back to the previous page and change the packages that will be installed to remove the requirement. Once all of the requirements are met, press the Next button.

mysql-check-requirement

• On the Installation page, you will be shown a list of software that will be downloaded. Press the Execute button to begin the downloads.

mysql-installation

Installation Progress : Complete Downloading. Click Next button to continue…

 

4) Click Next button to configure MySQL Database Server. Choose Config Type and MySQL port (3306 by default) and click Next button to continue.

mysql-configuration

Then create the password and keep it safe. MySQL Installer is configuring MySQL database server. Wait until it is done and click Next button to continue.

MySQL Configuration Overview.png

Installation Completes: the installation completes. Click finish button to close the installation wizard.

mysql-installation-complete

5) Now its turn to Add path for Mysql in windows.

  • Click the Start button. then Right click on the computer menu/button Computer and Select the Properties menu option.
  • On the dialog that opens, select Advanced system settings.
  • Select the Advanced tab.
  • Click the Environment Variables button.
  • Select the Path variable in the table, then press the Edit button.
  • Then go to the path where mysql is stored. Copy that path and paste it on edit button and end with semicolon. The default path is C:\Program Files\MySQL\MySQL Server 5.6\bin.

Rename table name and column name commands

SQL Server:

EXEC sp_rename ‘oldtablename’, ‘newtablename’;

Oracle:

alter table tablename rename to  newtablename;

MYSQL:

RENAME TABLE table_name TO new_table_name;

POSTGRESQL

ALTER TABLE table_name RENAME TO newtablename;

Rename Column Name

SQL Server:

EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’

Oracle:

alter table tablename columname rename to newcolumname

MYSQL:

alter table change oldcoumnname newcoklumnaate

POSTGRESQL

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;