Backup and recovery of MySQL database

Suppose our target database is test and the table is user.
Assume that the user name and password for MySQL are root.

The programs used for backup and recovery are the mysqldump commands and MySQL commands provided by the MySQL package. The idea is simple, that is, through mysqldump the contents of the database is exported to a file composed of SQL statements, and then restore the SQL file to MySQL to execute.

Three ways are mentioned in the MySQL Man Handbook:
Shell> mysqldump [Options] db_name [tbl_name ...]
Shell> mysqldump [options]--databases db_name ...
Shell> mysqldump [Options]--all-databases

The first way is to back up some tables under a particular database, where the specified table is optional, and if you do not specify a table, back up all tables under that database.

The second way is to back up some databases, and you should specify at least one database.

The third way is to back up all of the databases.

[Options] is optional, the options specified in the commands below are common in these three ways, and in fact the manual of the mysqldump command does not differentiate between these three ways.


/** Backup **/
Since we want to back up the specified table for the specified database, we use the first method.

Use the following command:
Mysqldump-u root-proot--add-drop-table--add-locks--create-options--quick--skip-extended-insert test user

Explain the meaning of each option in the command:
-U root Specifies the user name.
-PROOT Specifies the password (no spaces between-p and the password, you can use the-P option only, and the password is entered after you press ENTER).
--add-drop-table in the recovery phase, drop first if the target table exists. In the exported SQL file, it is reflected as: DROP TABLE IF EXISTS ' user ';
--add-locks in the recovery phase, lock the table before inserting the data into the table, and then unlock the data.
--create-options indicates that additional options are added to the statement that created the table, as shown in the exported SQL file: Engine=innodb auto_increment=266 DEFAULT Charset=utf8;
--quick is useful when a row is written, and the contents of the table are very large. If you are using the--skip-quick option, the contents of the table are all taken back and then written.
--skip-extended-insert uses an INSERT statement for each row of data. If you use the--extended-insert option, the content is placed in an INSERT statement.

After the command executes, you can see that the content is output to stdout, to back up to the file, redirect the content, and use the following command:
Mysqldump-u root-proot--add-drop-table--add-locks--create-options--quick--skip-extended-insert Test user > Test. User.sql

The mysqldump command also provides the option to specify the file to which the content is to be output,--result-file=file_name,-R file_name

So you can also use the following command:
Mysqldump-u root-proot--add-drop-table--add-locks--create-options--quick--skip-extended-insert--result-file= Test.user.sql test User

Or
Mysqldump-u root-proot--add-drop-table--add-locks--create-options--quick--skip-extended-insert-r test.user.sql te St User




/** Recovery **/
Use the following command:
Mysql-u Root-proot Test < Test.user.sql

Or into the MySQL interactive mode, use test;
then source Test.user.sql;


For more detailed information, refer to the Manual of the corresponding command.

Backup and recovery of MySQL database