Mysql Data Backup and mysql Data Backup

Mysql Data Backup and mysql Data Backup

This article uses mysql5.5 centos6.5 64-bit (I use rpm to install mysql, the default database installation directory)

I. Considerations for Data Backup

Read locks: Once a database (or a table) performs read locks, the database write operations are affected. Therefore, you cannot perform write operations on the database. The reason why the read Lock operation is performed during data backup is to ensure the integrity and consistency of the backup data, it will be automatically unlocked after the data backup is complete.

Log update: A New log file is generated every time the data is backed up. In this way, when the database crashes for a period of time, the data in this period can be restored through the bin-log. Data Recovery steps: Restore the backup data + restore the data in the latest bin-log

Ii. Use mysqldump to back up books

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

How the mysqldump command works: it first detects the structure of the table to be backed up, and then generates a CREATE statement in a text file. Then, convert all records in the table into an INSERT statement. Then, you can use these statements to create a table and insert data.

 

Back up the test database to the/tmp/mysql_back/directory.

[Root @ localhost tmp] # mysqldump-uroot-p111111 test-l-F> '/tmp/mysql_back/test. SQL ';

Parameter explanation:

1. mysqldump mysql Data Backup command

2.-uroot-p111111 username and password

3. test: name of the database to be backed up

4.-l: Read lock Control

5.-F: generate a new Log File

After the statement is executed, a new bin-log mysql-bin.000002 is generated in the/var/lib/mysql directory.

6./tmp/mysql_back/test. SQL: location and name of the backup file generated