Selection and thinking of MySQL data backup method

I have been working in the DBA industry for more than two years. I have accumulated some experience in data backup both in theory and practice. It so happens that some data backup problems have arisen in the past two days. Here, I will briefly summarize the data backup methods that I have encountered before.

We all know that ensuring the stable, secure and efficient operation of the database is the job responsibility of the DBA. For DBAs, data backup may be a crucial link in achieving data security.

I once encountered a case where the business party accidentally deleted data, causing a failure. They asked the DBA to restore the data, but found that the data for that day had not been backed up. The situation was very embarrassing. Originally, the failure was caused by the business, but because there was no data backup, in the end the business and DBA were both blamed. If the business does not require the DBA to restore the data, then the DBA can quietly back up the data later and avoid being implicated in the end. Of course, there are no ifs in the production environment.

The importance of backup is self-evident. So what are some common backup ideas? Depending on whether the backup affects the online environment, it can be roughly divided into two methods: cold backup and hot backup. Cold backup can be understood as having an impact on online business and requiring offline backup, while hot backup has no impact on online business or has a relatively small impact.

I have summarized the following methods:

1. rsync, cp copy files

This method is more violent. It is to directly stop the MySQL database and then use Linux physical file copy commands such as cp and rsync to back up the data.

in:

cp specifically refers to backing up to other directories on the current server

Rsync specifically refers to backing up to other servers

In this method, the following points should be noted:

a. For online environments, the rsync method is better, because if the server goes down, the cp method backup will also be unavailable.

b. Both rsync and cp methods need to be performed on the slave database of the master-slave architecture, or on a designated backup database. (Here I recommend using a one-master-one-slave-one-backup replication architecture for the online environment)

The biggest advantages of cold backup are fast speed and simple operation.

Suitable for daily backup of large databases.

2. select xxx into outfile syntax

This method is often not called a backup, because it only backs up the data of one table, but it is very useful.

Imagine a scenario where the business side asks you to update hundreds of data items, but there are tens of millions of data items in the table. At this time, if you back up the entire table, it will be less flexible and take a long time. If we use the select syntax to save the data that needs to be modified into a txt file first, even if the business feedback does not meet expectations after the update, we can quickly find the appearance before the update, which can greatly shorten your data recovery time.

This backup method is suitable for backing up a part of the data in a single table before it is changed.

3. Delayed slave

In MySQL, this type of backup solution is relatively rare, but in MongoDB, delayed slaves are very common. However, delayed slave is a better means of online backup, and its most important feature is the high possibility of recovery.

Sometimes, the data you have worked so hard to back up cannot be restored on other servers due to various dependencies or version issues. This is quite embarrassing, and the delayed slave library can solve this problem very well, because the slave library itself is running, so there is no need to worry about the library not being able to start up.

In the delayed slave backup plan, the delay time of the slave must be controlled. If it is set too short, the SQL that may cause problems on the master may have been executed on the slave, and the delay effect will not be achieved, and the delayed slave will lose its meaning.

4. Logical backup mysqldump

This may be the most common way to back up MySQL. The official backup tool mysqldump can provide you with a consistent snapshot at a certain point in time. You can clearly know the current point in time and the location of the binlog to which the MySQL database has executed, and the recovery accuracy is very high. Moreover, it supports backup of specified libraries and tables, etc., and its functions are very powerful.

It is suitable for database backup with a data volume below 50G. If it exceeds 50G, the recovery will be very slow.

There is no need to say more about this, everyone knows it.

Remember to add --single-transaction, otherwise the table may be locked and the business may not be written. 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 file is generally small.

5. xtrabackup backup

This is another hot backup method. The xtrabackup backup method can perform hot backup of the database without affecting database writes. In essence, it is still a physical file copy, which is faster than mysqldump.

If the read and write pressure on the main database is relatively small, you can back up directly on the main database. However, it is still recommended not to use it on the master database, because there have been cases where the online master database has been under heavy write pressure, and xtrabackup directly caused the master database backup to crash. It is a relatively safer method to perform xtrabackup on the slave database without stopping the database.

Its characteristics are fast backup speed and relatively small impact on online backup.

6. Binlog backup

Binlog backup is essentially a backup of SQL statements, similar to the results of mysqldump, but binlog retains more data than mysqldump.

If you have all the binlogs of this database since its startup, you can undoubtedly restore this database. However, all binlogs must be a relatively large amount of data, and it is generally difficult to retain the full log, so binlog backup generally needs to be combined with snapshot backup.

We can stop the replication relationship when a slave library is online, then flush log to generate a new binlog, record it as binlog0, and then copy the complete backup of the slave library, and then save all binlogs after binlog0. In this way, we can use the complete backup + binlog method of the slave library to restore the data of the slave library.

Binlog backup is suitable for databases with average binlog writing speed. If your database binlog refreshes very quickly, the data capacity may become a bottleneck.

7. Clone plugin

The clone plugin is a highlight feature of 8.0. It can support both local data backup and quick remote pull-up of a database slave. It is a backup method worth studying. This has been covered in some detail in previous articles, you can go and read it if you are interested. You can also refer to the official documentation.

However, at present, it is rarely used in online environments because most domestic databases are currently at MySQL version 5.7. Of course, 8.0 is the trend, so if you haven't upgraded yet, upgrade as soon as possible.

The above is the details of how to choose the MySQL data backup method. For more information about MySQL data backup, please pay attention to other related articles on 123WORDPRESS.COM!