Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to import and export MySQL backup, the article is very detailed, has a certain reference value, interested friends must read it!

1. Detailed explanation of MySQL backup types

For modern Internet companies, the data stored in the server database has gradually become the lifeline of enterprises and companies, and has a very significant impact on the survival and development of enterprises and companies. In order to ensure the security of database data storage and ensure that it will not be destroyed by misoperation and force majeure, we usually back up the data in the database. There are many ways to back up MySQL databases. Let's explain these backup methods according to different classification criteria:

(1) Classification according to the impact of backup on database

1. Hot backup (Hot Backup). Refers to the backup during the operation of the database and does not have any effect on the normal operation of the database.

2. Cold backup (Cold Backup). Refers to a backup that begins after the database stops running.

3. Warm backup (Warm Backup). It refers to the backup mode in which the backup is carried out during the operation of the database, but it will degrade the performance of the database and affect the provision of database services.

(2) to classify according to the backed-up documents

1. Logical backup. Using logical backup, the data backed up is usually a file of type .sql. The contents of the backup are readable and are text files. This method is generally used in scenarios such as database upgrade, migration or batch modification of database tables. This method takes a long time to recover.

2. Backup of naked files. Refers to copying the physical files of the database. In this way, the recovery time of the database is short.

(3) classify according to the way of backup

1. Full backup. Refers to a full backup of the database.

2. Incremental backup. Refers to the backup of updated data based on the previous backup, rather than backing up all data.

3. Log backup. Refers to the backup of the logs of the database, which is used in the master-slave synchronization architecture of MySQL.

II. MySQL database export (1) mysqldump export data

Mysqldump is a data backup and export tool that comes with the MySQL database, which supports data tables from the MyISAM and InnoDB engines. The parameters for mysqldump backup are as follows:

-u: indicates the user used to specify the login database.

-p: indicates the user password used to specify the login database.

-d: indicates that only the table structure of the database is exported on export.

-t: indicates that only the specific data of the database is exported and does not include the table structure.

-A: indicates that all databases are exported.

An example of using mysqldump is as follows:

Mysqldump-uroot-proot-A > all_database.sqlmysqldump-uroot-proot pzz > pzz.sqlmysqldump-uroot-proot pzz student > student.sqlmysqldump-uroot-proot-d pzz > pzz_table.sqlmysqldump-uroot-proot-t pzz > pzz_data.sql

The above five commands respectively represent exporting all the data in the database, exporting the data in the pzz database, exporting all the data in the student table in the pzz database, exporting the data related to the table structure in the pzz database and exporting the actual data except the table structure in the pzz database.

Using the mysqldump exported file, the result is as follows:

(2) backup of mysqlhotcopy naked files

In the database of MySQL5.5 and below (the mysqlhotcopy command has been deleted from the MySQl5.7 version of the database), you can make a quick backup. The backup carried out by mysqlhotcopy is essentially a direct physical copy of the database table files, but locks are used to lock the contents of the database. Also, the mysqlhotcopy command can only back up the data tables of the MyISAM engine. Examples of using the mysqlhotcopy command are as follows:

Mysqlhotcopy-u root-p root pzz / rootmysqlhotcopy-u root-p root pzz./student/root

Of the above two commands, the first is to back up the pzz database, and the second is to back up the student tables in the pzz database. The backup process is as follows:

(3) comparison between mysqldump and mysqlhotcopy

1. Mysqldump is backed up as a .sql file, while mysqlhotcopy uses naked file backup.

2. Mysqldump backup and recovery is slower than mysqlhotcopy, so it is not suitable for large file backup.

3. Mysqldump supports MyISAM and InnoDB engines, while mysqlhotcopy only supports MyISAM engines.

4. Mysqlhotcopy is not included in versions above MySQL5.5 (not included).

5. Mysqlhotcopy can only run on the device where the database is located, while mysqldump can run on the local device or on the remote client.

6. Both mysqldump and mysqlhotcopy lock the database when they are running.

7. The recovery of mysqldump backup is essentially the execution of SQL statements in .sql files, while the recovery of mysqlhotcopy backup is essentially a direct overwrite.

III. MySQL database import

If we use mysqlhotcopy to back up the database, we only need to overwrite the original data when restoring. If you restore a .sql file exported by mysqldump, you need to import data from MySQL. There are two ways to import MySQL database, one is to use "

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.