MySQL import and export backup details

1. Detailed explanation of MySQL backup types

For modern Internet companies, data stored in server databases has gradually become the lifeblood 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 prevent it from being damaged due to misoperation and force majeure, we usually back up the data in the database. There are many ways to back up a MySQL database. Let's explain these backup methods according to different classification standards:

1. Classification based on the impact of backup on the database

1. Hot Backup. It means backing up the database while it is running and has no impact on the normal operation of the database.
2. Cold Backup. Refers to a backup that is started after the database is stopped.
3. Warm Backup. Refers to a backup method that performs backup while the database is running, but this will cause database performance to degrade and affect the services provided by the database.

(II) Classify by backup files

1. Logical backup. When using logical backup, the backed-up data is usually a .sql file. The backed-up content is readable and is a text file. This method is generally used in scenarios such as database upgrades, migrations, or batch modifications of database tables. This method takes a long time to recover.
2. Bare file backup. Refers to copying the physical files of the database. In this way, the database recovery time is shorter.

3. Classification by backup method

1. Full backup. Refers to a complete backup of the database.
2. Incremental backup. It means backing up updated data based on the last backup, rather than backing up all data.
3. Log backup. It refers to backing up the database logs. This backup method is used in the MySQL master-slave synchronization architecture.

2. MySQL database export

(I) mysqldump export data

mysqldump is a data backup and export tool that comes with the MySQL database. The tool supports data tables of MyISAM and InnoDB engines. The parameters used for mysqldump backup are as follows:
-u: specifies the user to log in to the database.
-p: specifies the user password used to log in to the database.
-d: Indicates that only the table structure of the database is exported.
-t: Indicates that only the specific data of the database will be exported without including the table structure.
-A: means exporting all databases.
The following are examples of using mysqldump:

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

The above five commands respectively represent exporting all data in the database, exporting data in the pzz database, exporting all data in the student table in the pzz database, exporting data related to the table structure in the pzz database, and exporting the actual data in the pzz database except the table structure.
Using the file exported by mysqldump, the results are as follows:

(II) mysqlhotcopy bare file backup

In databases of MySQL 5.5 and below (the mysqlhotcopy command has been deleted in databases of MySQL 5.7), quick backup can be performed. The backup performed by mysqlhotcopy is essentially a direct physical copy of the database table files, except that a lock is used to lock the database contents during the copy. In addition, the mysqlhotcopy command can only back up data tables of the MyISAM engine. The following are examples of using the mysqlhotcopy command:

mysqlhotcopy -u root -p root pzz /root
mysqlhotcopy -u root -p root pzz./student/root

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

(III) Comparison between mysqldump and mysqlhotcopy

1. mysqldump will back up to .sql files, while mysqlhotcopy uses bare file backup.
2. mysqldump backup and recovery are slower than mysqlhotcopy, so it is not suitable for large file backup.
3. mysqldump supports MyISAM and InnoDB engines, while mysqlhotcopy only supports MyISAM engine.
4. mysqlhotcopy is not included in MySQL versions above 5.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 a remote client.
6. When mysqldump and mysqlhotcopy are running, they will lock the database.
7. Restoring a mysqldump backup is essentially the execution of SQL statements in the .sql file, while restoring a mysqlhotcopy backup is essentially a direct overwrite.

3. MySQL database import

If we use mysqlhotcopy to back up the database, then when restoring it, we only need to overwrite the original data with the backup data. If you want to restore the .sql file exported by mysqldump, you need to import the MySQL data. There are two ways to import MySQL database. One is to use the "<" symbol to directly import the information in the .sql file into the MySQL database. The second is to use the source command to import data after logging into the database.
For example, to import the backup data of exp.sql, you can execute the command:

mysql -uroot -proot < exp.sql

Or after logging into the database, execute:

source /root/exp.sql

Summarize

This is the end of this article about the detailed explanation of MySQL import and export backup. For more relevant MySQL backup content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!