MySQL database backup scheme and strategy

Because MySQL has a variety of database backup methods, and each has its pros and cons, for us beginners, choosing the right backup method is indeed a bit difficult. Personally think, first of all, based on the company's needs, consider the ability to tolerate the loss of data, the cost of labor costs, and so on, this is the basis for our backup plan, while the formulation of the program to be executable, to implement, can not be used as a plan. Below I put our actual backup solution for everyone to refer to the exchange.

As an important content of data security, the importance of data backup is often neglected. As long as data transmission, data storage and data exchange occur, it is possible to create a failure. At this point, if you do not take data backup and data recovery means and measures, it will lead to the loss of data, and sometimes the loss can not be compensated and measured. Combined with the actual situation of our company's online business, we say that our backup plan is mainly to take the full standby +binlog backup method at present. It is fully divided into logical backup + physical backup, while master-slave replication also exists as a backup method to minimize the risk of data failure.

A Data Backup section

1 Logical Backup

• Application Scenarios

Logical backup, we mainly used when the data volume is small, the database has the data failure, for the recovery time request is not high, constructs the master-slave environment, constructs the test environment and the backup storehouse and so on.

• Backup Time and location

Daily 3:10 in the backup from the library, the backup file is stored in the/data/backup/fullbackup from the library, but if there is sufficient machine, the more secure way is to back up to the remote server.

• Backup method

Use mysqldump for full-Library backup, and execute the Shell backup script regularly through timed tasks. It's not available here.

2 Physical Backup

• Application Scenarios

The main response to the demand for recovery time is higher, data volume is large;

• Backup Time and location

Back up on the main library every Monday 3:10. Backup files are stored in the remote server directory

• Backup method

Using the Percona Community tool Innobackupex, the tool can be hot-prepared online without compromising business on line.

The above two methods of backup can only restore data for a certain period of time, for the recovery at the point of time is powerless, how to do? Binlog log, yes, we are taking a real-time synchronization of Binlog logs to the remote server, which can theoretically be restored to any point in time.

3 Binlog Backup

• Application Scenarios

For some data loss errors due to error operations, you need to restore at a point in time.

• Backup Time and location

The backup server synchronizes the Binlog on the main library to the remote server in real time.

• Backup method

Mysqlbinlog tool for log pull, the shell script is as follows:

Mysqlbinlog--read-from-remote-server--host=1.1.1.1--port=3306--user= "Backup"--password= "Backup"--raw-- Stop-never mysql-bin.000840--result-file=/data/backup/binlog/

After the above three combinations of backup methods, basically can meet in the case of abnormal data loss, return to normal state.

4 master-slave replication

• Application Scenarios

Mainly used in the case of read/write separation and failover

• Backup Time and location

Can almost be thought of as synchronous data replication

• Backup method

Replication technology provided by MySQL

For master-slave replication, if you use Ubeku, it is best to let Sql_thread perform slowly for a period of time, can be 1 days. This combination of the actual situation, their choice.

Two data recovery and testing section

Backup files have to be tested periodically after they have been restored, or they might be a white-busy one. Because in many cases, some backup files may be corrupted. When we encounter data loss failures, in the event of an emergency, we find that the backup files cannot be recovered or the data consistency and integrity are not met, and if we regularly restore the backup files to test, this tragedy may not happen.

1 Recovery time and location

A weekly recovery test, mainly on the test machine

2 Recovery mode

Simulating a point-in-time host data loss, requiring recovery to all data at a lost point in time, full recovery first, and then recovering to the nearest point in time based on Binlog.

As a DBA, don't neglect the importance of data backup and recovery testing. You know, sometimes, backups can save our lives!!! Remember, remember.

MySQL database backup scheme and strategy