MySQL Backup and Restore Guide: Full, Partial, and Table-Level Operations

First, list the existing databases with mysql> show databases; which displays databases such as information_schema , mysql , performance_schema , sys , and the user database mzl .

Full backup : Use the following command to dump all databases (except information_schema ) into all_db.sql : mysqldump -uroot -p123456 -h192.168.210.85 -P3306 --all-databases --single-transaction --force > all_db.sql

Backup multiple databases (e.g., mysql and mzl ): mysqldump -uroot -p123456 -h192.168.210.85 -P3306 --databases mysql mzl --single-transaction --force > mzl_mysql_db.sql

Backup a single database can be done in two ways:

Method 1 – with --databases option (includes CREATE DATABASE statement): mysqldump -uroot -p123456 -h192.168.210.85 -P3306 --databases mzl --single-transaction --force > mzl.sql

Method 2 – without --databases (no CREATE DATABASE statement): mysqldump -uroot -p123456 -h192.168.210.15 -P3306 mzl --single-transaction --force > mzl01.sql

When the --databases flag is used, the dump contains a CREATE DATABASE line such as: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

Export specific tables from a single database (e.g., tables class01 and class02 in mzl ): mysqldump -uroot -p123456 -h192.168.210.85 -P3306 --tables mzl class01 class02 --single-transaction --force > mzlall.sql

Restore the full backup with: mysql -uroot -p123456 -h192.168.210.85 -P3306 < all_db.sql

After restoration, verify the databases with mysql> show databases; which should list the same set as before.

If a dump was created with --databases , you must create the target database first or specify it during import; otherwise you will encounter errors such as:

ERROR 1046 (3D000) at line 22: No database selected

Correct restoration for a single‑database dump includes the database name, for example: mysql -uroot -p123456 -h192.168.210.85 -P3306 mzl < mzl.sql

Note that using a password on the command line triggers a warning about security, but the operation proceeds if the credentials are correct.