Mysql data backup logical backup recovery method

Just having backups is not enough, we have to know how to use these backups. Now let’s take a look at the recovery method of the logical backup made above:

Since all backup data are related to the design of our original database structure It is stored in the form of , so the recovery of logical backup is relatively simple. Of course, the recovery methods are slightly different for the two different logical backup forms. Below we will give a brief introduction to the recovery methods of these two logical backup files.

1. Recovery of INSERT statement files:

The recovery of backup files in the form of INSERT statements is the simplest. We only need to run all (or part of) the SQL commands in the backup file. First, if a complete recovery is required, we can use "mysql < backup.sql" to directly call the backup file and execute all the commands in it to completely restore the data to the state at the time of backup. If you have used mysql to connect to MySQL, you can also restore it by executing "source/path/backup.sql" or "./path/backup.sql" in mysql.

2. Restoration of pure data text backup:

If it is the second form of logical backup above, it will be a little more troublesome to restore. It needs to be restored table by table through relevant commands. Of course, if it is realized automatically through a script Multi-table recovery is also more convenient. There are two recovery methods. One is through MySQL's "LOAD DATA INFILE" command, and the other is through the tool mysqlimport provided by MySQL.

What can logical backup do? What can't be done?

After knowing how to use logical backups for corresponding recovery, we need to know what we can do with these logical backups.

1. Through logical backup, we can completely restore the relevant data in the database to the state at the time of backup by executing relevant SQL or commands without affecting irrelevant data;

2. Through logical backup of the entire database , we can completely reconstruct a database that is exactly the same as the backup in the new MySQL environment, and is not restricted by the platform type on which MySQL is located;

3. Through logical backup of specific conditions, we can transfer certain data to Easily migrate (or synchronize) to other MySQL or other database environments;

4. Through logical backup, we can only restore part of the data in the backup set without restoring all of it.

After knowing what logical backup can do, we must also know what it cannot do, so that we can clearly know whether such a backup can meet our expectations and whether it is indeed what we want.

1. Logical backup cannot restore data to any time other than the backup time;

2. Logical backup cannot restore data.

The above is the content of the recovery method of logical backup of Mysql data backup. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!