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 of backup

--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.

Consistent Backup with MYSQLDUMP
Use Single transaction keywords in backup command.

mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql


Restore the database from MySQLDUMP utility backup
1. Connect with the MySQL Server:

mysql -u username -p
Enter password:


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

CREATE DATABASE database_name;
exit;


3. Load the file using the MySQL command:

Syntax:
mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]


Example:

mysql -u username -p database_name < backup_name.sql
mysql -u root -p TEST < D:\test_backup.sql;


Errors and Solutions:
1. C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql < D:\test.sql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

Solution: Use following syntax with -u and -p option
mysql -u root -p < d:\test.sql;

2. C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p < D:\test.sql;
Enter password: *****
ERROR 1049 (42000): Unknown database ';'

Solution: Remove the ‘;’ marks from end of file. Use following:
mysql -u root -p < D:\test.sql

3. C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p < D:\test.sql
Enter password: *****
ERROR 1046 (3D000) at line 22: No database selected

Solution: Specify the database name in syntax.
mysql -u root -p < D:\test.sql