Linux O & M Study Notes MySQL backup and recovery (incremental recovery) tutorial, Study Notes mysql

Linux O & M Study Notes MySQL backup and recovery (incremental recovery) tutorial, Study Notes mysql

Chapter 2 MySQL backup and recovery advanced-incremental recovery

I. Under what circumstances do I need incremental database recovery?

1. Do I need incremental recovery if the master database or slave database goes down due to hardware damage?

No. The master database goes down. You only need to switch the fastest synchronized slave database to the master database. The slave database goes down and does not affect usage. You can fix it properly.

2. Do I need to perform incremental recovery if the SQL statement of the human operation database destroys the master database?

Yes, because at this time, the master and slave databases have been synchronized and related operations must be restored through backup. If a delete statement such as drop database test is executed on the master database, the slave database also executes this statement, resulting in the loss of the test database on the master database, only Backup files can be used to restore data.

Therefore, this enterprise does not allow the execution of the delete statement. All delete Operations update the status code on the record through the update statement. related records are excluded based on the status code During query.

3. Is incremental recovery required if there is only one primary database?

If there is only one master database, you should first perform regular full backup (once a day) and Incremental Backup (cut binlog logs every 1-10 minutes and then back up the logs to other servers, or backup servers of other local hard disks or network file systems)

If data loss is not allowed, the best way is to synchronize data from the slave database through drbd (based on disk blocks.

4. Summary

Generally, manual (or program) logic misoperations such as SQL statements executed in the database require incremental recovery, because at this time, misoperation statements are also executed from the database.

Under normal circumstances, master-slave synchronization not only shares the read/write splitting pressure, but also prevents data loss caused by physical device damage and facilitates data recovery.

Full and Incremental backup in the slave database can prevent data loss caused by misoperation of the master database.

Ensure that the backup slave database and master database are in synchronization status at any time.

Ii. Backup Policy Selection for production scenarios

1. Small and Medium Enterprises

Full backup is generally performed once a day. Full backup is performed at low traffic valleys, and the table is locked during Backup. For a single-node database, use rsync with scheduled tasks (more frequent, or inotify) to back up all binlogs to a remote server. However, we do not recommend that you use this method. We recommend that you perform master-slave replication and add a server (which can be shared with the web) to the slave database in any case.

2. Large Companies

Generally, it is a full backup every week. If you start a full backup at every Saturday, Incremental backup is performed from Sunday to next Saturday.

The advantage is to save backup time and reduce backup pressure.

The disadvantage is that there are too many copies of the incremental binlog file, which makes restoration troublesome.

3. Current mainstream enterprise practices

At present, the mainstream practice of enterprises is one master multiple slave, there will be a slave database for backup, delay synchronization, do not provide external services, and enable binlog, implement regular full backup and real-time Incremental backup.

4. Reasons for mainstream enterprise practices (that is, MySQL backup scenarios)

(1) when migrating or upgrading the database.

(2) When the slave database is added.

(3) manual DDL and DML statements are executed in both the master and slave databases. Backup is required.

(4) For cross-data-center disaster recovery, the backup must be copied.

(5) The master database or slave database is down due to hardware failures or special exceptions. The master and slave databases can switch between each other without backup.

Iii. Prerequisites for MySQL incremental recovery

1. binlog is enabled for both the master database and backup slave database.

2. There is a backup of all incremental binlog files from full backup and after full backup to the time when the problem occurs.

3. incremental recovery Diagram

Iv. demo of MySQL incremental recovery

1. log on to database 3306 and check the student table data.

Mysql-uroot-p-S/data/3306/mysql. sock

Use test;

Show tables;

Select * from student;

+ ---- + ------ +

| Id | name |

+ ---- + ------ +

| 1 | a1 |

| 2 | a2 |

| 3 | a3 |

| 4 | a4 |

| 5 | a5 |

+ ---- + ------ +

2. Full backup of the test Database

Mysqldump-uroot-p '000000'-S/data/123456/mysql. sock-F-B -- master-data = 2 test | gzip>/wddg/dbbak/test_all _ $ (date must have f2.16. SQL .gz

3. write new data to the student table

Insert into student (name) values ('b1 '), ('b2'), ('b3'), ('b4'), ('b5 ');

Select * from student;

+ ---- + ------ +

| Id | name |

+ ---- + ------ +

| 1 | a1 |

| 2 | a2 |

| 3 | a3 |

| 4 | a4 |

| 5 | a5 |

| 6 | b1 |

| 7 | b2 |

| 8 | b3 |

| 9 | b4 |

| 10 | b5 |

+ ---- + ------ +

3. Simulate misoperations and delete the test Database

Drop database test;

4. Fault Detection and troubleshooting

The error log shows that the test database is deleted by mistake.

5. Prohibit Web applications from writing data to the master database or directly locking the table through the firewall, so that the master database can temporarily stop updating and prepare for recovery.

6. Check the backup file

Ll dbbak

-Rw-r -- 1 root 862 Apr 23 test_all_2017-04-23. SQL .gz

7. decompress the backup file and view the log location

Gzip-d test_all_2017-04-23. SQL .gz

Grep-I "change" test_all_2017-04-23. SQL

-- Change masterto MASTER_LOG_FILE = 'mysql-bin.000007 ', MASTER_LOG_POS = 107;

8. Refresh the binlog and generate a new binlog file.

Flush logs;

Or

Mysqladmin-uroot-p '000000'-S/data/123456/mysql. sock flush-logs

9. Copy the binlog files to be used for restoration to the backup directory.

Cp mysql-bin.000007/dbbak/

10. filter out the content of the test database required for restoration in the binlog file and write it to the bin. SQL file.

Mysqlbinlog-d test mysql-bin.000007> bin. SQL

11. Use vi to open the bin. SQL file, delete the drop database test statement, and save the statement.

Vi bin. SQL

# At 576

#170423 17:07:03 server id 1end_log_pos 657 Query thread_id = 3 exec_time = 0 error_code = 0

Set timestamp = 1492938423 /*! */;

Dropdatabase test # Delete this sentence and save it

12. MySQL will write the mysqldump update and insert statement into the binlog log, so there are three cases:

(1) The primary database does not provide the write function for external users during restoration.

A. Restore the full backup file

Mysql-uroot-p '808080'-S/data/123456/mysql. sock </dbbak/test_all_2017-04-23. SQL

B. Restore the SQL statement in the bin. SQL file (you must specify the test database)

Mysql-uroot-p '000000'-S/data/123456/mysql. sock test

(2) The primary database is still providing the write function for external users during recovery.

A. temporarily stop updating the master database and prepare for restoration.

B. Disable the SQL _log_bin parameter so that mysql does not record SQL statement logs.

(I) view the SQL _log_bin parameter status

Show variables like '% log_bin % ';

+ --------------------------------- + ------- +

| Variable_name | Value |

+ --------------------------------- + ------- +

| Log_bin | ON |

| Log_bin_trust_function_creators | OFF |

| SQL _log_bin | ON |

+ --------------------------------- + ------- +

(Ii) set SQL _log_bin = OFF

Set SQL _log_bin = 0

(Ii) Considerations for setting SQL _log_bin

Do not add the global modifier (set global SQL _log_bin = 0) without thinking about it. As a result, all statements executed on the Master database do not record binlog. The INSERT, UPDATE, and delete SQL statements may cause data inconsistency between the Master database and the Slave database. Therefore, exercise caution when performing this operation.

C. Restore the full backup file

Mysql-uroot-p '808080'-S/data/123456/mysql. sock </dbbak/test_all_2017-04-23. SQL

D. Restore the SQL statement in the bin. SQL file (you must specify the test database)

Mysql-uroot-p '000000'-S/data/123456/mysql. sock test

E. Resolve the binlog after the bin. SQL time point to SQL for restoration.

F. Change SQL _log_bin TO THE ON status.

Set SQL _log_bin = 1

(3) During recovery, the master database is still providing the write function, and manual master-slave switching is implemented.

A. Stop a slave database and switch to the master database.

B. Refresh the binlog on the master database and resolve the last binlog file (mysql-bin.000007) before refreshing to the bin. SQL

C. Remove the drop statement in bin. SQL.

D. Restore the full backup file and bin. SQL to the slave database.

E. Stop the master database and resolve the binlog logs refresh the master database to SQL.

F. Switch to the slave database to provide external services

13. If the update operation is not a drop operation, but destroys the data, it is more complicated to parse than the drop operation. Generally, you need to stop the database or disable writing by the application service, and then restore it.

V. incremental recovery Summary

1. misoperation caused by human SQL

2. Full backup recovery before incremental recovery

3. We recommend that you stop the update when resuming the operation.

4. during incremental recovery, you must delete the problematic SQL statements in the incremental log and restore the SQL statements to the database.

Vi. Core Idea of incremental recovery

1. process system control. If you do not do this, you will be faced with the problem of stopping services or data loss.

2. delayed backup. Information Monitoring, black and white list mechanisms

3. Set quantifiable targets based on Business Requirement tolerance, and choose to stop databases or lock tables or tolerate the loss of some data as needed.