Summary of MySQL logical backup and recovery testing

1. What kind of backup is a database logical backup?

As we all know, when the database returns data for us to use, it presents the data one by one in the form of a certain logical association format that we originally designed and expected, and has certain business logic attributes. However, at the physical storage level, the database software stores the data after certain processing in a specific format designed by the database software.

Database logical backup means that the backup software generates related text files one by one according to the predefined logical association format for the data in the database, based on the logical relationship we originally designed and taking the logical structure objects of the database as units, so as to achieve the purpose of backup.

2. Commonly used logical backup

Logical backup can be said to be the simplest and most commonly used backup method for small and medium-sized systems. There are two main types of logical backups we commonly use in MySQL. One is to generate INSERT statements that can completely reproduce the data in the current database. The other is to use logical backup software to separate our database table data with specific delimiters and record them in a text file.

① Generate INSERT statement backup

The two types of logical backups have their own advantages and disadvantages, and the usage scenarios they target are slightly different. Let's first look at the logical backup that generates INSERT statements.

In MySQL database, we usually use mysqldump in the tool program that comes with MySQL database software to realize the logical backup file of the so-called INSERT statement. The basic usage is as follows:

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Since mysqldump is relatively simple to use, most of the information you need can be obtained by running "mysqldump --help". Here I just want to combine some concepts and principles of MySQL database to discuss with you some tips and what we need to pay attention to when we use mysqldump to do database logical backup.

We all know that for most software or websites that use databases, they hope that their databases can provide the highest possible availability, rather than having to shut down and stop providing services from time to time. Because once the database cannot provide services, the system can no longer provide some dynamic functions by accessing data.

Therefore, for most systems, it may be unacceptable to shut down the system for each backup. However, the implementation principle of the mysqldump program is to obtain data from each table through the parameter information we provide plus the system table information in the database, and then generate INSERT statements and write them into the backup file. This presents a problem: during normal system operation, there may be continuous requests for data changes being executed, which may cause inconsistencies in the data backed up by mysqldump.

In other words, the backup data may not be the data at the same point in time, and may not even be able to meet the integrity constraints. Such a backup set may not be a big problem for some systems, but it is a big problem for some systems that have strict requirements on data consistency and integrity, because it is a completely invalid backup set.

What should we do in such a scenario? We know that if we want the data in the database to be consistent, this can be done in only two situations.

• First, take out all data at the same time;
• Second, the data in the database is at rest.

For the first situation, everyone must be thinking, is this possible?

No matter what, as long as there are more than two tables, no matter how we write the program, it is impossible to get the data at the same time as last night. Yes, we cannot make the time points of data retrieval completely consistent through conventional methods, but don’t forget that in the same transaction, the database can ensure that the data read is at the same time point.

Therefore, for storage engines that support transactions, such as Innodb or BDB, we can control the entire backup process within the same transaction to achieve consistency and integrity of the backup data. The mysqldump program also provides us with relevant parameter options to support this function, that is, through the "--single-transaction" option, it can not affect any normal services of the database.

For the second case, I think the first thing that comes to everyone's mind is to lock the table that needs to be backed up, allowing only reading but not writing.

Yes, that's all we can do. We can only use a compromise approach, which is to let the database only provide data query services during the backup process and lock the write service, so that the data is temporarily in a consistent state that will not be modified. After mysqldump completes the backup, the write lock will be canceled and the full service will be resumed.

The mysqldump program itself also provides related options such as "--lock-tables" and "--lock-all-tables", which will lock the table before execution and automatically release the lock after execution.

One thing to note here is that "--lock-tables" does not lock all the tables that need to be dumped at once, but only locks the tables of one database at a time. If the tables you need to dump are in multiple databases, you must use "--lock-all-tables" to ensure the consistency and integrity of the data.

When generating a logical backup file of an INSERT statement through mysqldump, there is a very useful option available to us, that is "--master-data[=value]". When "--master-data=1" is added, mysqldump will record the name and position of the binlog log currently used by MySQL in the dump file, and it will be recorded in the form of CHANGE_MASTER statement. If only "--master-data" or "--master-data＝2" is used, the CHANGE_MASTER statement will exist in the form of a comment. This option is very useful when implementing online slave setup. Even if the slave is not being setup online, you can still perform further recovery operations through the backup binlog during the recovery process in some cases.

In some scenarios, we may just want to export some special data to other databases, but we don't want to create a temporary table first. We can also use the "--where='where-condition'" of the mysqldump program to achieve this, but it can only be used when dumping only one table.

In fact, in addition to the above usage tips, mysqldump also provides many other useful options for you to use in different scenarios, such as using "--no-data" to only dump the database structure creation script, using "--no-create-info" to remove the command to create the table structure in the dump file, etc. Interested readers can read the introduction to the use of the mysqldump program in detail and then test it by themselves.

② Generate a plain text backup data file backup in a specific format

In addition to making logical backups by generating INSERT commands, we can also use another method to separate the data in the database with specific separator characters and record them in a text file to achieve the effect of logical backup. Compared with INSERT command files, such backup data requires less storage space, has a clearer data format, and is easier to edit. However, the disadvantage is that the backup data of multiple tables cannot exist in the same backup file, and there is no command to rebuild the database structure. The backup set requires multiple files, which has an impact on us only in that the maintenance and recovery costs increase due to the increase in files, but these can basically be achieved by writing some simple scripts.

So what method can we generally use to generate such a backup set file? In fact, MySQL has already implemented the corresponding function for us.

In MySQL, the following two methods are generally used to obtain plain text backup files with custom delimiters.

1. Implemented by executing the SELECT ... TO OUTFILE FROM ... command

MySQL provides a SELECT syntax for users to output certain specific data in a specified format to a text file through SQL statements. It also provides practical tools and related commands to easily import the exported file into the database as is. Isn't this exactly what we need for backup?

This command has several parameters that need attention as follows:

• "FIELDS ESCAPED BY ['name']" implements the character escape function to escape the characters that need to be escaped in the SQL statement;
• "FIELDS [OPTIONALLY] ENCLOSED BY 'name'" can "wrap" the contents of the field. If "OPTIONALLY" is not used, all types of data including numeric data will be "wrapped". After using "OPTIONALLY", numeric data will not be "wrapped" with the specified characters.
• "FIELDS TERMINATED BY" can be used to set the separator between every two fields;
• "LINES TERMINATED BY" will tell MySQL what characters to add at the end of each record in the output file.

For example:

root@localhost : test 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM test_outfile limit 100;
Query OK, 100 rows affected (0.00 sec)
root@localhost : test 10:02:11> exit
Bye
root@sky:/tmp# cat dump.text
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
... ...

2. Export via mysqldump

We all know that mysqldump can generate related backup files with the data in the database in the form of INSERT statements. In fact, in addition to generating INSERT statements, mysqldump can also implement the functions implemented by the above "SELECT ... TO OUTFILE FROM ...", and at the same time generate a creation script corresponding to the relevant database structure.

For example:

root@sky:~# ls -l /tmp/mysqldump
total 0
root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields enclosed-by=\" --fields-terminated-by=,
root@sky:~# ls -l /tmp/mysqldump
total 8
-rw-r--r-- 1 root root 1346 2021-4-20 22:18 test_outfile.sql
-rw-rw-rw- 1 mysql mysql 2521 2021-4-20 22:18 test_outfile.txt
root@sky:~# cat /tmp/mysqldump/test_outfile.txt
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
... ...
root@sky:~# cat /tmp/mysqldump/test_outfile.sql
--MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
--Server version 5.0.51a-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_outfile`
--
DROP TABLE IF EXISTS `test_outfile`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `test_outfile` (
`id` int(11) NOT NULL default '0',
`t_id` int(11) default NULL,
`a` char(1) default NULL,
`mid` varchar(32) default NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-4-20 14:18:23

This output structure is very suitable for us to use as a backup. Of course, if multiple tables need to be dumped at a time, two corresponding files will be generated for each table.

3. Logical backup and recovery method

Just having backups is not enough. We need to know how to use these backups. Now let's look at how to restore the logical backups made above:

Since all backup data is stored in a format related to our original database structure design, the recovery of logical backups is relatively simple. Of course, the recovery methods are slightly different for the two different logical backup forms. Below we will briefly introduce the recovery methods of these two logical backup files.

①Recovery of INSERT statement files

Restoring a backup file in the form of an INSERT statement 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 directly call the backup file using "mysql < backup.sql" to execute all the commands in it and completely restore the data to the state at the time of the backup. If you have connected to MySQL using mysql, you can also restore by executing "source/path/backup.sql" or "\./path/backup.sql" in mysql.

②Recovery of pure data text backup

If it is the second form of logical backup above, recovery will be a little more troublesome, and you need to restore each table one by one through relevant commands. Of course, it is also more convenient to use scripts to achieve automatic multi-table recovery. There are also two recovery methods. One is to use the MySQL "LOAD DATA INFILE" command, and the other is to use the mysqlimport tool provided by MySQL to recover.

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

After understanding how to use logical backups for 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 the logical backup of the entire database, we can completely rebuild a database that is exactly the same as the one at the time of the backup in the new MySQL environment, and it is not limited by the type of platform on which MySQL is located;
3. Through logical backup under specific conditions, we can easily migrate (or synchronize) certain specific data to other MySQL or other database environments;
4. Through logical backup, we can restore only part of the data in the backup set without restoring all of it.
   

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

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

4. Logical backup and recovery test

Sometimes you hear that someone's database has a problem, and when you are confidently preparing to restore the database you made before, you find that your backup set is unavailable or cannot achieve the recovery effect you expected when you made the backup. When encountering such a situation, I'm afraid everyone will be extremely depressed. The most important and critical use of database backup is when our database encounters some abnormal conditions and needs to restore data.

As a maintainer, we should never make such low-level mistakes. So how can we avoid such problems?

There is only one way, which is to perform simulated recovery tests periodically to verify whether our backup set is really valid and whether it can be restored according to our backup expectations.

At this point, some people may ask, how should we do the recovery test? We can't really restore the data in the online environment, right?

Yes, data in the online environment cannot be restored, but why can't we do it in the test environment or elsewhere?

The purpose of doing recovery testing is just to verify whether our backup is valid and can meet our expectations.

Therefore, before doing a recovery test, we must first clearly know what kind of scenario our backup is for.

For example, if we make a logical backup of the entire database, the purpose may be to be able to restore the entire database data to the backup time when the database has logical or physical abnormalities. Then our recovery test only needs to restore the entire logical backup to the entire database to see whether we can successfully rebuild a complete database.

As for whether the restored data is consistent with the backup time, we can only rely on ourselves to manually judge and compare.

In addition, we may also hope that when a database object, such as a table, has a problem, the table data can be restored to the backup time as soon as possible. Then we can perform a sample recovery test on a single specified table.

Let's assume that the database host crashes and the hardware is damaged, causing all database data to be lost, and do a test example of full database recovery:

When our database encounters a hardware failure and all data is lost, we must find a new host as soon as possible to replace the damaged host and restore the corresponding service. Before restoring service, we first need to rebuild the damaged database. Assume that we have already got a new host, the MySQL software has been installed, and the relevant settings have been adjusted, and we are just waiting to restore the database.

We need to retrieve the most recent full database logical backup file from the time of the crash, copy it to the prepared new host, and start the installed MySQL.

Since we have two logical backup formats, the recovery method of each format is different, so here we will give examples of the recovery of logical backups in both formats.

①If it is a logical backup of the INSERT statement

a. Prepare the backup file and copy it to a specific directory, such as "/tmp";

b. Execute the relevant commands in the backup set by executing the following commands:

mysql -uusername -p < backup.sql

Or log in to the database through mysql first, and then execute the following command:

root@localhost : (none) 09:59:40> source /tmp/backup.sql

c. Then check the corresponding database objects in the database to see if they are complete;

d. Randomly check the data in several tables for manual verification, and notify the application to start internal test verification. When all verifications pass, services can be provided to the outside world.

Of course, the steps mentioned above are all performed under the premise that each step is normal. If a problem is found in a certain step. If an exception occurs in step b and the process cannot proceed, we first need to check whether there is something wrong with our recovery command based on the error that occurred. Is there something wrong with our environment? etc.

If we confirm that the problem lies with the backup file, then our backup is invalid and the test has failed. If our recovery process is normal, but during verification we find that database objects are missing, or the data in some objects is incorrect, or there is no data at all. This also means that our backup level cannot meet expectations and the backup fails.

Of course, if we encounter a similar situation during the recovery process of actual work, if there is an earlier backup set, we must take a step back and use the earlier backup set to do the same recovery operation. Although the data in the earlier backup set may be somewhat distorted, it can at least be partially restored without losing all data.

② If we are backing up a plain data text file separated by a special delimiter

a. The first step is the same as INSERT backup file, which is to prepare the backup file closest to the crash time;

b. Import data into the database through specific tools or commands:

Since the database structure creation script and the plain text data backup file are stored separately, we first need to execute the database structure creation script and then import the data. The method of creating the structure script is exactly the same as step b in the recovery test of the first backup above.

Now that we have the database structure, we can import the backup data as follows:

mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt

or

LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';

The subsequent steps are exactly the same as restoring the backup file for INSERT statements, so they will not be repeated here.

The above is the detailed summary of MySQL logical backup and recovery test. For more information about MySQL logical backup and recovery test, please pay attention to other related articles on 123WORDPRESS.COM!