Tag Archives: Mysqldump consistent backup

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

Advertisements