MySQL Operations management-mysql Backup and recovery practical case and production plan 17

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

[root@mysql ~]# 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

Recovery idea 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 procedure 3.1 EXECUTE statement and check Environment 3.1.1 Execute the Build list statement and INSERT DATABASE statement

[root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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(
-> id int(4) not null AUTO_INCREMENT,
-> name char(20) not null,
-> 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: xxxxxxxxx@163.com 
#Function:This scripts function is More complex backup scripts, which need to find binlog log files and location points 
#Version: 1.1 
USER=root
PASS=123456
MYSOCK=/data/3306/mysql.sock
DATA_PATH=/server/backup
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
LOG_FILE=${DATA_PATH}/mysql_backup_`date +%F`.log
MYSQL_PATH=/usr/local/mysql/bin
mysqldb=linzhongniao
#--single-transaction Specifically for the InnoDB engine, when the data is updated when the data is 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}<<EOF
flush 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;
quit
EOF

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

[root@mysql ~]# date -s ‘2018/02/11’ Change system time to simulate zero time
Sunday, February 11, 2018 00:00:00 CST
[root@mysql ~]# 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

[root@mysql ~]# ll /server/backup/
Total dosage 684
  -rw-r--r--. 1 root root203 February 10 23:26 mysq_backup_2018-02-10.sql.gz
  -rw-r--r--. 1 root root 529266 February 8 19:11 mysql_backup_2018-02-08.sql
  -rw-r--r--. 1 root root805 February 10 23:18 mysql_backup_2018-02-10.sql.gz
  -rw-r--r--. 1 root root306 February 11 00:25 mysql_backup_2018-02-11.log
  -rw-r--r--. 1 root root915 February 11 00:25 mysql_backup_2018-02-11.sql.gz
  -rw-r--r--. 1 root root805 February 10 23:18 mysql_backup_.sql

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

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

4.2 View Binlog log status after backup

[root@mysql ~]# ll /data/3306/
Total usage 172
Drwxr-xr-x. 8 mysql mysql 4096 February 12 2018 data
-rw-r--r--. 1 mysql mysql 2099 February 10 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 10 04:51 mysql-bin.000010
-rw-rw----. 1 mysql mysql 107 February 10 04:51 mysql-bin.000011
-rw-rw----. 1 mysql mysql 3355 February 10 06:17 mysql-bin.000012
-rw-rw----. 1 mysql mysql 126 February 10 06:17 mysql-bin.000013
-rw-rw----. 1 mysql mysql 126 February 10 06:17 mysql-bin.000014
-rw-rw----. 1 mysql mysql 1914 February 10 08:34 mysql-bin.000015
-rw-rw----. 1 mysql mysql 150 February 10 23:18 mysql-bin.000016
-rw-rw----. 1 mysql mysql 150 February 10 23:18 mysql-bin.000017
-rw-rw----. 1 mysql mysql 126 February 11 2018 mysql-bin.000018
-rw-rw----. 1 mysql mysql 150 February 11 00:24 mysql-bin.000019
-rw-rw----. 1 mysql mysql 150 February 11 00:24 mysql-bin.000020
-rw-rw----. 1 mysql mysql 150 February 11 00:25 mysql-bin.000021
-rw-rw----. 1 mysql mysql 893 February 11 01:41 mysql-bin.000022
-rw-rw----. 1 mysql mysql 616 February 11 00:25 mysql-bin.index
-rw-rw----. 1 mysql mysql 5 February 12 2018 mysqld.pid
Srwxrwxrwx. 1 mysql mysql 0 February 12 2018 mysql.sock
-rw-r-----. 1 mysql root 42156 February 12 2018 mysql_zbf3306.err
-rw-rw----. 1 mysql mysql56 February 11 00:34 relay-bin.index
-rw-rw----. 1 mysql mysql55 February 11 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

[root@mysql ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> use linzhongniao
Database changed
mysql> insert into student(name) 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

[root@mysql ~]# ll /server/backup/mysql_backup_2018-02-11.*
-rw-r--r--. 1 root root 306 February 11 00:25 /server/backup/mysql_backup_2018-02-11.log
-rw-r--r--. 1 root root 915 February 11 00:25 /server/backup/mysql_backup_2018-02-11.sql.gz

7.1.2 Check all the binlog after full preparation

[root@mysql backup]# ls -lrt /data/3306/ >/opt/mysql-bin.txt
[root@mysql backup]# sed -n "21,30p" /opt/mysql-bin.txt
  -rw-rw----. 1 mysql mysql 150 February 11 00:24 mysql-bin.000019
  -rw-rw----. 1 mysql mysql 150 February 11 00:24 mysql-bin.000020
  -rw-rw----. 1 mysql mysql 150 February 11 00:25 mysql-bin.000021
  -rw-rw----. 1 mysql mysql 616 February 11 00:25 mysql-bin.index
  -rw-rw----. 1 mysql mysql 253 February 11 00:34 relay-bin.000023
  -rw-rw----. 1 mysql mysql 253 February 11 00:34 relay-bin.000024
  -rw-rw----. 1 mysql mysql56 February 11 00:34 relay-bin.index
  -rw-rw----. 1 mysql mysql 126 February 11 07:51 mysql-bin.000018
Drwxr-xr-x. 7 mysql mysql 4096 February 11 23:52 data
  -rw-rw----. 1 mysql mysql 995 February 11 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.

[root@mysql ~]# 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.

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

7.1.4 Incremental Backup Binlog

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

[root@mysql 3306]# cp mysql-bin.000022 /server/backup/

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

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

8. Recovering data

If there is a problem with the non-stop access to the library, 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

[root@mysql backup]# ll
Total usage 24
-rw-r--r--. 1 root root 2868 February 12 13:45 bin.sql
-rw-r--r--. 1 root root 203 February 10 23:26 mysq_backup_2018-02-10.sql.gz
-rw-r--r--. 1 root root 306 February 11 00:25 mysql_backup_2018-02-11.log
-rw-r--r--. 1 root root 2331 February 11 00:25 mysql_backup_2018-02-11.sql
-rw-r--r--. 1 root root 805 February 10 23:18 mysql_backup_.sql
-rw-r-----. 1 root root 1038 February 12 13:41 mysql-bin.000022
[root@mysql 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.sql8.3 Check 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 Operations management-mysql Backup and recovery practical case and production plan 17