Selection and thinking of MySQL data backup method

Time:2021-12-1

I have been engaged in the DBA industry for more than two years. I have accumulated some experience in data backup both in theory and practice. It coincides with some problems in data backup in these two days. Here, I will simply sort out the data backup methods I have encountered before.

As we all know, it is the job responsibility of DBA to ensure the stable, safe and efficient operation of database. For DBAs, data backup may be a crucial link to achieve data security.

I once encountered a case where the business side mistakenly deleted data and caused a failure and asked the DBA to recover the data. It was found that the data on that day was not backed up. The scene was once very embarrassing. Originally, the failure was caused by the business, but because there was no data backup, the business and DBA played 50 boards together. If the business does not require the DBA to recover the data, the DBA can quietly back up the data later, and finally avoid being implicated. Of course, the production environment is not involved.

The importance of backup is self-evident. What are our common backup ideas? According to whether backup affects the online environment, it can be roughly divided into two methods: cold backup and hot backup. Cold backup can be understood as affecting the online business and requiring offline backup, while hot backup has no or little impact on the online business.

I myself summarized the following methods:

1. Rsync, CP copy files

This method is more violent, that is, stop the MySQL database directly, and then backup the data through the Linux physical file copy commands such as CP and Rsync.

Of which:

CP refers specifically to other directories backed up to the current server

Rsync specifically refers to backup to other servers

For this method, pay attention to the following points:

a. The Rsync method is better for the online environment, because if the server goes down, the CP method backup will not be available.

b. Both Rsync and CP methods need to be implemented on the slave Library of the master-slave architecture or on the specified backup library. (here, I recommend that the online environment use the replication architecture of one master, one slave and one backup)

The biggest advantage of cold backup is fast speed and simple operation.

Suitable for daily backup of large databases.

2. Select XXX into outfile syntax

In fact, this method can not be called backup, because it only backs up the data of one table, but it is very useful.

Imagine a scenario where the business side lets you update hundreds of data and tens of millions of data in the table. At this time, if you back up the whole table, it will be less flexible and take a long time. If we use the select syntax to save the data to be modified to a TXT file first, even if the business feedback is inconsistent with the expectation after the update, we can quickly find the appearance before the update, which can greatly shorten the time you recover the data.

This backup method is suitable for the backup of some data of a single table before it is changed.

3. Delay slave Library

In mysql, this backup scheme is relatively few. In mongodb, delayed slave database is very common. However, delayed slave database is a better online backup method, and its main feature is the high possibility of recovery.

Sometimes, you can’t restore a MySQL instance on other servers because of various dependency or version problems, which is embarrassing. Delaying the slave library can solve this problem well, because the slave library itself is running, so you don’t have to worry about the failure of the library.

In the backup scheme of delayed slave database, the delay time of the slave database should be controlled. If it is set too short, the SQL that may cause problems on the master database has been executed on the slave database, the delay effect will not be achieved, and the delayed slave database will lose its significance.

4. Logical backup mysqldump

This may be the most common MySQL backup method. Mysqldump, an official backup tool, can provide you with a consistent snapshot at a certain point in time. You can clearly know the current point in time. The MySQL database has been executed to the binlog site, and the recovery accuracy is very high. Moreover, it supports specified library table backup and other functions, which is very powerful.

It is suitable for database backup with data volume less than 50g. If it exceeds 50g, the recovery will be particularly slow.

Needless to say, we all know.

Remember to add — single transaction, otherwise there may be the problem of locking the table and unable to write the business. A little brother once fell here.

Its feature is that it can give you a database snapshot at a certain point in time, and the backup files are relatively small.

5. Xtrabackup backup

This is another hot backup method. Xtrabackup backup can perform hot backup on the database without affecting the database writing. In fact, it is a physical file copy, which is faster than mysqldump.

If the read-write pressure of the primary database is relatively small, it can be backed up directly on the primary database. However, it is not recommended to use it on the main database, because there has been a case where the online main database has great writing pressure and xtrabackup directly shuts down the backup of the main database. It is a relatively safe method to carry out xtrabackup from the library without stopping the library.

It is characterized by fast backup speed and little impact on online backup.

6. Binlog backup

Binlog backup is essentially a backup of SQL statements, similar to the result of mysqldump. However, more data is retained in binlog than mysqldump.

If you have all the binlogs of the database from startup, there is no doubt that you can restore the database. However, all binlogs must have a relatively large amount of data. Generally, it is difficult to retain the full amount of logs, so binlog backup generally needs to be combined with snapshot backup.

We can stop the replication relationship when a slave database goes online, then flush log generates a new binlog and records it as binlog0, and then copy a complete backup of the slave database, and then save all binlogs after binlog0. In this way, we can use the method of full backup of the slave database + binlog to recover the data from the slave database.

Binlog backup is applicable to those databases with average binlog write speed. If your database binlog refresh speed is very fast, the data capacity may become a bottleneck.

7、clone plugin

Clone plugin is a highlight function of 8.0. It can not only support data backup to the local, but also support the rapid remote pulling up of a database slave library. It is a backup method worth studying. The previous articles are also more detailed. If you are interested, you can read them. You can also refer to official documents.

However, at present, the online environment is rarely used, because most domestic databases currently stay in MySQL 5.7. Of course, 8.0 is the trend. If you don’t upgrade, upgrade quickly.

The above is the details of how to select MySQL data backup methods. For more information about MySQL data backup, please pay attention to other relevant articles of developeppaer!

Recommended Today

Yolov5 assertionerror: image not found solution

Run yolov5 train Py error: assertionerror: image not found/ data/images/xxx. png Operating environment At first, running training with a graphics card on the notebook can run normally. Later, with the increasing amount of data, the graphics memory of the notebook graphics card was not enough, so the school’s Inspur server was used to run, but […]