Mysql DBA Advanced Operations Learning note-mysql Backup and recovery scenarios and production solutions

1. Full backup and incremental backup 1.1 full-scale backup

Full-volume data is all the data in the database, and a full backup is the backup of all the data in the database.

Back Up all libraries:

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B –A gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

Back up a library:

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B linzhongniao|gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

1.2 Incremental backup

Incremental backups are data that has been updated since the last full-scale backup, and Binlog is the incremental backup data for MySQL. Incremental backups can be backed up on a daily basis. The next day is fully prepared, in theory, the days before the backup is useless. We generally maintain an incremental backup of the primary server for seven days, and you can set its value to 7 by setting the Expire_logs_days parameter in the MY.CNF configuration file. Incremental backups on the backup server are typically maintained for 180 days. Backup by day is a day to do a full backup, 0 points a day full backup, before 0 o'clock to do incremental backup; In addition to press Tianquan prepared can also be prepared, do a weekly full-time to do incremental backups every day.

Advantages of days-per-day backup: Recovery time: Short maintenance costs: low

Disadvantage: Occupy more space, occupy more system resources, often lock table affect user experience.

According to the advantages of comprehensive: Occupy less space, occupy less system resources user experience better.

Cons: Recovery trouble Maintenance Original high, long time.

How often does the enterprise scenario be fully prepared and incremental?

(1) Small and medium-sized companies, the total amount is usually once a day, business flow trough the full preparation, the need to lock the table before execution.

(2) Single database does not have the master from the synchronization, how to increment. Use Rsync (with a timed task frequency, or inotify, master-slave replication) to back up all binlog to a remote server and copy as much as possible.

Examples of incremental backups:

rsync –avz /data/3306/mysql-bin.000* [email protected]::backup –-password-file=/etc/rsync.password

(3) Large company Zhou Bei, 600 points per week, full amount, next Sunday-next Saturday 00 o'clock are all incremental.

A little: Save backup time, reduce backup pressure, disadvantage: There are too many copies of incremental binlog files, and restoring can be cumbersome.

(4) a master multi-slave, there will be a backup from the library, delay synchronization.

When does MySQL's mysqldump backup come in handy?

A. When migrating or upgrading a database.

B. Increase the time from the library.

C. Because of hardware or special abnormal situation, the main library or from the library down, master and slave can switch, no backup. One of the fastest synchronization switches to the main library.

D. Man-made DDL, DML statements, master and slave libraries are not able to do, all libraries will execute, at this time need to backup.

E. Cross-room disaster preparedness, backup copy data.

2.MySQL Incremental Restore Prerequisites 2.1 Turn on MySQL log-bin log feature

[[email protected] ~]# egrep "\[mysqld]|log-bin" /data/3306/my.cnf [mysqld]log-bin = /data/3306/mysql-bin

Tip: Both the main library and the backup from the library need to turn on the Binlog logging feature.

Summary: Conditions for incremental recovery:

There is a full standby plus all incremental Binlog file backups at the time of the problem.

2.2 Existing MySQL database fully prepared 2.2.1 Production environment mysqldump backup command

At some point in the morning, the database is fully prepared (the production scenario is usually performed by the morning of the scheduled task), and the backup command is as follows:

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B linzhongniao|gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

Innode Engine Backup

mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -A -B|gzip >/server/backup/mysql_$(date +%F).sql.gz

MyISAM Engine Backup

mysqldump -uroot -p123456 -S /data/3306/mysql.sock --lock-all-tables  -A -B|gzip >/server/backup/mysql_$(date +%F).sql.gz

2.3 Data Recovery Ideas

The idea of recovery is to first find out the cause of the failure, who did what when, and check whether this operation caused the database failure. Then try to stop the database external access, first back up the 0:00 to 10 updated data binlog log file, refresh Binlog. The first step is to restore full standby, and the second step restores data from 0:00 to 10:00 for this time period.

3. Demo Data Recovery process 3.1 EXECUTE statement and check Environment 3.1.1 Execute the Build list statement and INSERT DATABASE statement

[[email protected] ~]# mysql-uroot-p123456-s/data/3306/mysql.sock Welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.5.32-log Source distributioncopyright (c), +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show variables like '%chaeacter_set% '; Empty Set (0.01 sec) mysql> CREATE TABLE student (with ID int (4) NOT NULL auto_increment,-> name char (a) not null,-& Gt Primary key (ID); Query OK, 0 rows affected (0.01 sec) mysql> insert into student (name) VALUES (' Nishishei '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into student (name) VALUES (' Zhangsan '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into student (name) VALUES (' Lisi '); Query OK, 1 row Affected (0.00 sec)

3.1.2 Checking database and data

Check if there is a Linzhongniao library

mysql> show databases like ‘linzhongniao‘;+-------------------------+| Database (linzhongniao) |+-------------------------+| linzhongniao|+-------------------------+1 row in set (0.00 sec)

4.0 o'clock in the morning full-scale backup DATABASE 4.1 Scheduled Task database backup script

(1) Here we use MySQL master-slave to copy the backup script in the course content

#/bin/sh#date:2018-02-11 #Author: Create by Linzhongniao #Mail: [email protected] #Function: This scripts Function I s more complex backup scripts, which need to find Binlog log files and location points #Version: 1.1 user=rootpass=123456m Ysock=/data/3306/mysql.sockdata_path=/server/backupdata_file=${data_path}/mysql_backup_ ' Date +%F '. sql.gzLOG_ File=${data_path}/mysql_backup_ ' Date +%f '. logmysql_path=/usr/local/mysql/binmysqldb=linzhongniao#--  Single-transaction specifically for the InnoDB engine, when the data was updated when the data was updated, it can ' t see the Whole isolation. mysql_dump= "${mysql_path}/mysqldump-u$user-p$pass-s $MYSOCK--events-b-F--master-data=2--single-transaction $ MySQLdb "mysql_cmd=" ${mysql_path}/mysql-u$user-p$pass-s $MYSOCK "cat |${mysql_cmd}<<eofflush table with read  Lock;system echo "-----Show Master Status result-----" >> $LOG _file;system ${mysql_cmd}-E "show Master Status" |tail -1 >> $LOG _file;system ${mysql_dump}|gzip > $DATA_file;unlock tables;quiteof   

(2) Execute script full-scale backup Linzhongniao library principle

[[email protected] ~]# date -s ‘2018/02/11‘ 更改系统时间模拟零点时刻2018年 02月 11日 星期日 00:00:00 CST[[email protected] ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --master-data=2 linzhongniao|gzip >/server/backup/bak_$(date +%F).sql.gz

View backed-up data

[[email protected] ~]# ll /server/backup/总用量 684 -rw-r--r--. 1 root root203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root 529266 2月   8 19:11 mysql_backup_2018-02-08.sql -rw-r--r--. 1 root root805 2月  10 23:18 mysql_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root306 2月  11 00:25 mysql_backup_2018-02-11.log -rw-r--r--. 1 root root915 2月  11 00:25 mysql_backup_2018-02-11.sql.gz -rw-r--r--. 1 root root805 2月  10 23:18 mysql_backup_.sql

(3) The actual work is backed up by scheduled tasks.

[[email protected] ~]# crontab –l#mysql backup by linzhongniao on 2018021100 00 * * * /bin/sh beifen1.sh >/dev/null 2>&1

4.2 View Binlog log status after backup

[[email protected] ~]# ll/data/3306/total dosage 172drwxr-xr-x. 8 MySQL mysql 4096 February 2018 Data-rw-r--r--. 1 MySQL MySQL 2099 February 06:48 my.cnf-rwx------. 1 root root 1126 February 8 10:10 MYSQL-RW-RW----. 1 MySQL MySQL 1227 February 8 19:17 mysql-bin.000001-rw-rw----. 1 MySQL MySQL 126 February 9 05:20 mysql-bin.000002-rw-rw----. 1 MySQL MySQL 239 February 9 05:25 mysql-bin.000003-rw-rw----. 1 MySQL MySQL 458 February 9 05:30 mysql-bin.000004-rw-rw----. 1 MySQL MySQL 233 February 9 05:34 mysql-bin.000005-rw-rw----. 1 MySQL MySQL 415 February 9 05:57 mysql-bin.000006-rw-rw----. 1 MySQL MySQL 126 February 9 05:59 mysql-bin.000007-rw-rw----. 1 MySQL MySQL 126 February 9 05:59 mysql-bin.000008-rw-rw----. 1 MySQL MySQL 388 February 9 08:22 mysql-bin.000009-rw-rw----. 1 MySQL mysql 652 February 04:51 mysql-bin.000010-rw-rw----. 1 MySQL MySQL 107 February 04:51 MYSQL-BIN.000011-RW-RW----. 1 mysql mysql 3355 February 06:17 mysql-bin.000012-rw-rw----. 1 MySQL mysql 126 February 06:17 MYSQL-BIN.000013-RW-RW----. 1 MySQL mysql 126 February 06:17 mysql-bin.000014-rw-rw----. 1 MySQL MySQL 1914 February 08:34 mysql-bin.000015-rw-rw----. 1 MySQL mysql 150 February 23:18 mysql-bin.000016-rw-rw----. 1 MySQL mysql 150 February 23:18 mysql-bin.000017-rw-rw----. 1 MySQL MySQL 126 February 2018 Mysql-bin.000018-rw-rw----. 1 MySQL mysql 150 February 00:24 mysql-bin.000019-rw-rw----. 1 MySQL mysql 150 February 00:24 mysql-bin.000020-rw-rw----. 1 MySQL mysql 150 February 00:25 mysql-bin.000021-rw-rw----. 1 MySQL mysql 893 February 01:41 mysql-bin.000022-rw-rw----. 1 MySQL mysql 616 February 00:25 mysql-bin.index-rw-rw----. 1 MySQL MySQL 5 February 2018 mysqld.pidsrwxrwxrwx. 1 mysql MySQL 0 February 2018 Mysql.sock-rw-r-----. 1 mysql root 42156 February 2018 Mysql_zbf3306.err-rw-rw----. 1 MySQL mysql56 February 00:34 relay-bin.index-rw-rw----. 1 MySQL mysql55 February 01:41 relay-log.info

Tip: We can look at the new file mysql-bin.000022 generated with the-F parameter backup, and the incremental recovery begins with mysql-bin.000022.

5. After backup impersonate the user to continue to update Data 5.1 impersonate site user update data

Because the user will update the data at any time after a 0-point backup to a failure period

[[email protected] ~]# mysql-uroot-p123456-s/data/3306/mysql.sock Welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 18Server version:5.5.32-log Source distributioncopyright (c) $, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> use linzhongniaodatabase changedmysql> INSERT into student (NAM e) VALUES (' Linzhongniao10 '); Query OK, 1 row affected (0.35 sec) mysql> insert into student (name) VALUES (' linzhongniao11 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into student (name) VALUES (' linzhongniao12 '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from student;+----+----------------+| ID |  Name |+----+----------------+| 1 |  Nishishei | | 3 |  Zhangsan | | 5 |  Lisi | | 6 |  Burenshi | | 8 | LiuShishi | | 10 | Luhan | | 11 | Linzhongniao10 | | 13 | linzhongniao11 | | 15 | Linzhongniao12 |+----+----------------+9 rows in Set (0.00 sec) mysql>

5.2 Viewing the database content after the backup has been updated again

After a while we simulated the recovery should also be the data, if not the data is lost data.

mysql> select * from linzhongniao.student;+----+----------------+| id | name   |+----+----------------+|  1 | nishishei  ||  3 | zhangsan   ||  5 | lisi   ||  6 | burenshi   ||  8 | liushishi  || 10 | luhan  || 11 | linzhongniao10 || 13 | linzhongniao11 || 15 | linzhongniao12 |+----+----------------+9 rows in set (0.00 sec)

6. Impersonate a user to destroy database 6.1 Delete database Linzhongniao

The boss of the company at 10 o'clock in the morning, the DELETE database statement was executed. And then, like all the others, I wanted to delete a library that was useless.

mysql> drop database linzhongniao;Query OK, 1 row affected (0.11 sec)

6.2 Checking for damage results

Linzhongniao Library is missing.

mysql> show databases;+--------------------+| Database   |+--------------------+| information_schema || dfhjdhf|| mysql  || performance_schema || school || test   |+--------------------+

6 rows in Set (0.00 sec)

6.3 Find troubleshooting and check for reasons

Database problems 10 minutes later, the company's website operators reported the site failure, contact the DBA OPS personnel to resolve. At this point, the DBA or developer looks at the site for an error (or to view the background log). You can see that the display of the Linzhongniao database is not connected. Then log in to the database to find out if the database Linzhongniao library is indeed absent. After many inquiries to know the eldest boss in the morning to deal with the database, so asked the boss are doing what. The answer is that a "useless" database has just been cleared around 10. Cause this problem to be found, and start preparing for recovery, possibly because the developer is judged by the program log.
Tip: The idea of a database's rights management is here. Do not let others have delete permission.

7. Incremental backup process

Prevent applications such as the Web from writing data to the main library through firewalls, let the main library pause updates, and then recover.

7.1 Check full standby and Binlog log 7.1.1 check early morning backup

[[email protected] ~]# ll /server/backup/mysql_backup_2018-02-11.*-rw-r--r--. 1 root root 306 2月  11 00:25 /server/backup/mysql_backup_2018-02-11.log-rw-r--r--. 1 root root 915 2月  11 00:25 /server/backup/mysql_backup_2018-02-11.sql.gz

7.1.2 Check all the binlog after full preparation

  [[email protected] backup]# ls-lrt/data/3306/>/opt/mysql-bin.txt[[email protected] Backup ]# sed-n "21,30p"/OPT/MYSQL-BIN.TXT-RW-RW----. 1 MySQL mysql 150 February 00:24 mysql-bin.000019-rw-rw----. 1 MySQL mysql 150 February 00:24 mysql-bin.000020-rw-rw----. 1 MySQL mysql 150 February 00:25 mysql-bin.000021-rw-rw----. 1 MySQL mysql 616 February 00:25 mysql-bin.index-rw-rw----. 1 MySQL mysql 253 February 00:34 relay-bin.000023-rw-rw----. 1 MySQL mysql 253 February 00:34 relay-bin.000024-rw-rw----. 1 MySQL mysql56 February 00:34 relay-bin.index-rw-rw----. 1 MySQL mysql 126 February 07:51 mysql-bin.000018drwxr-xr-x. 7 MySQL mysql 4096 February 23:52 data-rw-rw----. 1 MySQL MySQL 995 February 23:52 mysql-bin.000022  

Tip: We are fully prepared at 0, so the Binlog log file is updated from 0 points onwards. We can use the Mysqlbinlog parameter to view the updated Binlog file after zero to see if there is any updated data. Of course we can also view the latest updated Binlog file by looking at the location of Change master in the 0-point full data file. You can see all the Binlog log files after the full backup, which records all the updated data from 0 to 10 points on the second day, including the execution of the misoperation, so we must be in the incremental recovery when the execution of the statement to delete the error. For example, this presentation should erase the drop statement.

[[email protected] ~]# grep -i "change" /server/backup/mysql_backup_2018-02-11.sql  -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000022‘, MASTER_LOG_POS=107;

7.1.3 immediately refreshes and backs up the Binlog

General database failure We want to stop the database, if the leader said can not stop, in this case we have to refresh the Binlog. Refresh Binlog will generate a new Mysql-bin log file mysql-bin.000023, and then update the data will be written in this new. Now the goal of incremental recovery is mysql-bin.000022.

[[email protected] ~]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs [[email protected] ~]# ls -lrt /data/3306/ >/opt/mysql-bin.txt[[email protected] ~]# vim /opt/mysql-bin.txt [[email protected] ~]# sed -n "21,27p" /opt/mysql-bin.txt  -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000019 -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000020 -rw-rw----. 1 mysql mysql   150 2月  11 00:25 mysql-bin.000021 -rw-rw----. 1 mysql mysql   126 2月  11 07:51 mysql-bin.000018drwxr-xr-x. 7 mysql mysql  4096 2月  11 23:52 data -rw-rw----. 1 mysql mysql  1038 2月  12 01:36 mysql-bin.000022 -rw-rw----. 1 mysql mysql   107 2月  12 01:36 mysql-bin.000023

7.1.4 Incremental Backup Binlog

(1) To copy the mysql-bin.000022 to prevent two damage data

[[email protected] 3306]# cp mysql-bin.000022 /server/backup/

(2) parse the mysql-bin.000022 into a bin.sql data file

[[email protected] backup]# mysqlbinlog -d linzhongniao mysql-bin.000022 >bin.sql [[email protected] backup]# ll总用量 24 -rw-r--r--. 1 root root 2868 2月  12 13:45 bin.sql -rw-r--r--. 1 root root  203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root  306 2月  11 00:25 mysql_backup_2018-02-11.log -rw-r--r--. 1 root root 2331 2月  11 00:25 mysql_backup_2018-02-11.sql -rw-r--r--. 1 root root  805 2月  10 23:18 mysql_backup_.sql -rw-r-----. 1 root root 1038 2月  12 13:41 mysql-bin.000022

8. Recovering data

If the library prohibits external access there will be a problem, what is the problem?
The first problem is that when recovering, there are users writing data to the database, MYSQL-BIN.000023 also records the updated content, recovery of mysql-bin.000023 updates after full and incremental recoveries, backup mysql-bin.000023 refresh Binlog, incremental recovery of mysql-bin.000023 and incremental recovery of updated Binlog, so that the deadlock is old to recover.
The second problem is that when we recover incrementally, mysql-bin.000023 also records the updates for full and incremental recoveries. After the full and incremental recovery to restore the records of the mysql-bin.000023 update will cause a primary key conflict, you can edit the mysql-bin.000023 resolved to the MySQL data file to delete the conflicting data, if more data? You can also turn off sql_log_bin. Closing Sql_log_bin will not update the Binlog log file, which will result in data loss. So at this time, the best way is to hold the library, prohibit external access, and then do full and incremental backup. The most fundamental is the database rights management, do not give permission to delete modify, only operations have to delete Modify permissions to prevent the occurrence of failures. Who has the authority to have the record, who causes the fault to be responsible.

mysql> show variables like ‘%log_bin%‘;+---------------------------------+-------+| Variable_name   | Value |+---------------------------------+-------+| log_bin | ON|| log_bin_trust_function_creators | OFF   || sql_log_bin | ON|3 rows in set (0.00 sec)

8.1 Full-volume recovery

[[email protected] backup]# ll总用量 24-rw-r--r--. 1 root root 2868 2月  12 13:45 bin.sql-rw-r--r--. 1 root root  203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz-rw-r--r--. 1 root root  306 2月  11 00:25 mysql_backup_2018-02-11.log-rw-r--r--. 1 root root 2331 2月  11 00:25 mysql_backup_2018-02-11.sql-rw-r--r--. 1 root root  805 2月  10 23:18 mysql_backup_.sql-rw-r-----. 1 root root 1038 2月  12 13:41 mysql-bin.000022[[email protected] backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock <mysql_backup_2018-02-11.sql

8.2 Incremental Recovery

[Email protected] backup]# mysql-uroot-p123456-s/data/3306/mysql.sock Linzhongniao <bin.sql

8.3 Checking the recovered data

mysql> select * from linzhongniao.student;+----+----------------+| id | name   |+----+----------------+|  1 | nishishei  ||  3 | zhangsan   ||  5 | lisi   ||  6 | burenshi   ||  8 | liushishi  || 10 | luhan  || 11 | linzhongniao10 || 13 | linzhongniao11 || 15 | linzhongniao12 |+----+----------------+9 rows in set (0.00 sec)

9. Incremental Recovery Summary

1. Mis-operation caused by human SQL

2. Full volume and increment

3. Recommended to stop updating when recovering

4. Restore the full amount, then delete the problematic SQL statement in the Delta log and revert to the database.

The core idea of incremental recovery:

1. The control of the process system, if not done, the face of services and data, fish and bear cake can not be combined.

2. The whitelist mechanism can be resolved by delaying the backup or by monitoring the blacklist (without the where statement is not allowed to execute).

3. Business requirements tolerance, choose to stop the library, according to business needs to choose a stop or lock table or tolerate the loss of some of the data.

Mysql DBA Advanced Operations Learning note-mysql Backup and recovery scenarios and production solutions