General steps for restoring data from MySQL backup

The following is an example of backing up a database:

1. Tables involved in pre-Backup read locks

Mysql> lock tables tbl1 READ, tbl1 READ ,...
If you use the -- lock-tables option in the mysqldump utility, you do not need to use the preceding SQL statement.

2. Export the structure and data of tables in the database

Shell> mysqldump -- opt db_name> db_name. SQL

3. enable new update logs

Shell> mysqladmin flush-logs

In this way, you can record the data changes after backup to prepare for data recovery.

4. Remove the table read lock.

Mysql> unlock tables;

To speed up the above process, you can do this:

Shell> mysqldump -- lock-tables -- opt db_name> db_name. SQL; mysqladmin flush-logs

However, this may cause minor problems. The command restores the table read lock before enabling the new update log,
In the busy Update site, the backup data may not be recorded in the new log.
Restore the database backed up above

1. Use a write lock on the involved table

Mysql> lock tables tbl1 WRITE, tbl1 WRITE ,...

2. Restore backup data

Shell> mysql db_name <db_name. SQL

3. Restore the updated log Content

Shell> mysql -- one-database db_name

Assume that the log name to be used is hostname. nnn.

4. enable new update logs

Shell> mysqladmin flush-logs

5. Remove the table write lock.

Mysql> unlock tables;

I hope the above example will inspire you, because there are a variety of methods to back up data, you may be using a lot different from the above, but for backup and recovery, the timing for locking tables and enabling new update logs should be similar. consider this issue carefully.

Questions

Enable regular logs and update logs before completing these questions, because the following questions will involve this problem.

1. Use the mysqldump utility with the -- all-databases option to back up all your existing data to a file. if your data is not too large in SQL), view the output SQL statement.

2. Use mysqldump with the -- AB option to back up the table structure and data in your test database to a directory. Check whether the generated SQL statement is different from the backup file in the previous question.

3. Create a new database test1 and restore the backup file of the above question to the database. Briefly describe the process.

4. Check your Update log and general log to see if all of the above operations have been recorded and what records have been left.

5. First INSERT a record into any table in the test database, and then delete the record. The purpose is to leave a record in the Update log .) Delete the database test1 and try to restore the database test1.