MySQL Data Backup and restore

First, data backup

　　1. Backup with mysqldump command

The mysqldump command backs up the data in the database into a text file. The structure of the table and the data in the table are stored in the generated text file.

The mysqldump command works very simply. It first identifies the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all the records in the table into an INSERT statement. With these statements, you can create tables and insert data.

　　1. Back up a database

Mysqldump Basic Syntax:

Mysqldump-u username-p dbname table1 table2 ...-> backupname.sql

which

• The dbname parameter represents the name of the database;
• The table1 and table2 parameters represent the names of the tables that need to be backed up, and the entire database is backed up as empty;
• Backupname.sql parameter table design the name of the backup file with an absolute path before the file name. Typically the database is partitioned into a file with a suffix called SQL;

Use the root user to back up the person table under the test database

Mysqldump-u root-p Test Person > D:\backup.sql

2. Backing up multiple databases

Grammar:

Mysqldump-u username-p--databases dbname2 dbname2 > Backup.sql

The--databases option is added, followed by multiple databases

Mysqldump-u root-p--databases Test mysql > D:\backup.sql

3. Back up all databases

The syntax for backing up all databases with the mysqldump command is as follows:

Mysqldump-u username-p-all-databases > Backupname.sql

Example:

Mysqldump-u-root-p-all-databases > D:\all.sql

Second, data restoration

　　1. The syntax for restoring a database backed up using the mysqldump command is as follows:

　　Mysql-u root-p [dbname] < BACKUP.SQ

Example:

Mysql-u Root-p < C:\backup.sql

MySQL Data Backup and restore