Backup and restore of mysql database

This article mainly explains "mysql database backup and restore", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql database backup and restore" bar!

one。 Data backup:

1. Use the mysqldump command to back up

The mssqldump 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 simply. It first finds out the structure of the table that needs to be backed up, then generates a create statement in a text file, and then converts all the records in the table into an insert statement. Through these statements, he can create a new table and insert data.

two。 Back up a database:

Basic usage of MySQLdump:

Mysqldumo-u username-p dbname table1 table2.-> Backupname.sql

Where:

Dbname: name of the database

Table1 and table2: indicates the name of the table to be backed up. If it is empty, the entire database will be backed up.

Backup.sql: the file can be preceded by an absolute path, and the database is usually backed up into a file with the suffix sql

For example: back up the persion table under the test database with the root user:

Mysqldump-u root-p test persion > d:\ backup.sql

3. Back up multiple databases:

Mysqldump-u username-p-- database dbname1 dbname2 > backup.sql

4. Back up all databases

Mysqldump-u root-p-all-database > alldb.sql

Second, directly copy the entire database directory

Stop the service first to ensure that the data will not change. This method is not suitable for innodb and is convenient for myisam.

Third, use mssqlhotcopy tool to express backup

Is a hot backup, can not stop the mysql server, faster than mysqldump, is a per script, mainly used in linux, which uses lock tables flush tables and cp for fast backup

The format of the command is as follows:

Mysqlhotcopy dbname1 dbname2 backupdir/

Dbname: database name

Backupdir: backup file path

Currently it is also used for tables of type myisam

Four: data restore:

1. The commands used for restore are as follows:

Mysql-u root-p [dbnams] < backup.sql

two。 Restore a direct copy directory backup:

When restoring in this way, you must ensure that the versions of the two mysql databases are consistent. Tables for myisam are valid, tables of type innodb are invalid, and table spaces of innodb cannot be replicated directly.

Thank you for your reading, the above is the content of "backup and restore of mysql database". After the study of this article, I believe you have a deeper understanding of the backup and restore of mysql database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!