Hyper-verbose MySQL backup strategy

The importance of data backup

In a production environment, the security of data is critical, and any loss of data can have serious consequences. The importance can be reflected in the following points:
1. Improved system high availability and disaster recoverability, no database backup to find data when database crashes
2. Using data Backup to restore the database is the best solution to provide the least cost of data recovery when the database crashes, re-add the data, the cost is too high
3. No data is not everything, database backup is a powerful means of disaster prevention

Causes of data loss

Program error
Human error
Computer failure
Disk failed
Disaster (e.g. fire, earthquake) and theft

Classification of data backups 1, from a physical and logical perspective, backups can be divided into physical and logical backups

Logical backup: A backup of a database logical component, such as a database object, such as a table. Note to lock the table before backing up

Physical Backup: A backup of physical files (such as data files, log files, and so on) of the database operating system
Physical backup can be divided into offline backup (cold backup) and online backup (hot backup)
Cold backup: When the database is closed (compressed package)
Hot backup: The database is in a running state, and this backup method relies on the log files of the database. In the running state, most operations are recorded in the log, restored through log playback, only need to back up the log

2, from the database backup policy perspective, backup can be divided into full backup, differential backup and incremental backup

Full backup: Every time you make a full backup of your data
Differential backup: Backs up files that have been modified since the last full backup. Note that this is only for the last full backup, regardless of whether the backup will occur again
Incremental backup: Only those files that were modified after the last full or incremental backup are backed up. Note that this is based on the last backup, the last backup method no matter what, will be more out of the backup

Experimental environment

• System Environment: CentOS7.4
• Server IP Address: 192.168.100.71
• Yum Mount directory:/mnt/sr0
• Related Source information: mysql-5.7.17

Command step one, use the TAR command to package a folder Backup 1, install the XZ package

[Email protected]_1 ~]# yum-y install XZ #使用xz压缩格式 with a large compression ratio

2. Backup Data Directory

[Email protected]_1 ~]# tar jcvf/opt/mysql-$ (date +%f). tar.xz/usr/local/mysql/data/#将mysql数据目录进行备份, to/opt/directory, Use the current time as a suffix to prevent file names from repeating

3. View Backup Data

[Email protected]_1 ~]# ls/opt/

4. View data size before and after backup

5. Restore Backup Data

[Email protected]_1 ~]# Tar jxvf/opt/mysql-2018-08-30.tar.xz/usr/local/mysql/data/

6. Introduction of periodic Scheduled Tasks

[Email protected]_1 ~]# crontab-e #编辑周期性计划任务

[Email protected]_1 ~]# crontab-l

Second, use the Mysqldump tool to back up 1, create test data

Mysql> CREATE DATABASE School default character set UTF8 collate utf8_general_ci; #创建数据库
Mysql> Use school; #进入school数据库
#创建 "Student" table and add data
Mysql> CREATE TABLE student (Sid int NOT null PRIMARY key Auto_increment,sname char (TEN), saddress varchar (), Sscore Deci Mal (5,2));
mysql> INSERT into student (Sname,saddress,sscore) VALUES (' Huamanlou ', ' Nanjing ', 99); #添加数据
mysql> INSERT into student (Sname,saddress,sscore) VALUES (' Zhanzhao ', ' Kaifeng ', 95);
mysql> INSERT into student (Sname,saddress,sscore) VALUES (' Xiaoqi ', ' Shanghai ', 91);
#创建 "Teache" table and add data
Mysql> CREATE TABLE teacher (Tid int not null PRIMARY key auto_increment,tname varchar (20));
Mysql> insert INTO teacher (tname) VALUES (' Mr Zhang ');
Mysql> insert INTO teacher (tname) VALUES (' Mr Wang ');
Mysql> insert INTO teacher (tname) VALUES (' Mrs Liu ');

2. mysqldump command to make a full backup of a single library

[[Email protected]_1 ~]# mysqldump-uroot-p school >/opt/school.sql #对 ' School ' library for backup
[Email protected]_1 ~]# vim/opt/school.sql #查看备份sql文件

3. mysqldump command to make a full backup of multiple libraries

[[Email protected]_1 ~]# mysqldump-u root-p--databases MySQL school >/opt/mysql-school-mysql.sql
[Email protected]_1 ~]# vim/opt/mysql-school-mysql.sql #查看备份sql文件

4. Full backup of all libraries

[[Email protected]_1 ~]# mysqldump-u root-p--opt--all-databases >/opt/all-data.sql
[Email protected]_1 ~]# Vim/opt/all-data.sql

5. Specify a table or table structure

[[Email protected]_1 ~]# mysqldump-u root-p School student >/opt/school_student.sql
[Email protected]_1 ~]# Vim/opt/school_student.sql

[[Email protected]_1 ~]# mysqldump-u root-p- D School student >/opt/school_stu.sql
[Email protected]_1 ~]# Vim/opt/school_stu.sql

Iii. Recovering database Operations 1, using source to recover a database operation

[Email protected]_1 ~]# mysqldump-u root-p--databases School >/opt/mysql_schooldb.sql #备份数据
[Email protected]_1 ~]# mysql-u root-p
mysql> drop Database School;
mysql> show databases;

Mysql> Source/opt/mysql_school.sql #导入school;
Mysql> Select from school.student;
Mysql> select from School.teacher;

2. Recover data using MySQL command

[[Email protected]_1 ~]# mysqldump-u root-p School student >/opt/school_student.sql #备份 ' student ' table data
[Email protected]_1 ~]# mysql-u root-p
mysql> drop table school.student;
mysql> desc school.student;
Mysql> quit

[Email protected]_1 ~]# mysql-u Root-p School </opt/school_student.sql #重新导入数据
[Email protected]_1 ~]# mysql-u root-p
Mysql> select * from School.student;

Iv. Database Incremental Backup Restore 1, turn on MySQL binary log function

[Email protected]_1 ~]# vim/etc/my.cnf

Under the Mysld tab, add:

[Mysqld]
Log_bin=mysql-bin

[Email protected]_1 ~]# systemctl restart Mysqld.service #重启服务

[Email protected]_1 ~]# cd/usr/local/mysql/data/
[Roo[email protected]_1 data]# ls

2. Create the appropriate test data

[Email protected]_1 ~]# mysql-u root-p
mysql> drop Database School; #删除之前的 "School" library
Mysql> CREATE DATABASE School;
Mysql> Use school;
Mysql> CREATE TABLE student (ID int not NULL PRIMARY key auto_increment, name varchar (ten), Score decimal (5,2));
Mysql> INSERT into student (Name,score) VALUES (' Tom '; #添加以下两条数据
Mysql> INSERT into student (Name,score) VALUES (' Bob ', *);
Mysql> quit

3, the "school" library for a full backup

[[Email protected]_1 ~]# mysqldump-uroot-p--databases School >/opt/school.sql

4. View the binary log

[Email protected]_1 ~]# mysqlbinlog--no-defaults--base64-output=decode-rows-v/usr/local/mysql/data/ mysql-bin.000001

5. Truncate the log

[Email protected]_1 ~]# mysqladmin-uroot-p flush-logs
[Email protected]_1 ~]# ls/usr/local/mysql/data/

[Email protected]_1 ~]# mysqlbinlog--no-defaults--base64-output=decode-rows-v/usr/local/mysql/data/ mysql-bin.000002

6. Inserting test data

[Email protected]_1 ~]# mysql-uroot-p
mysql> INSERT INTO school.student (Name,score) VALUES (' t01 ', n);
mysql> INSERT INTO school.student (Name,score) VALUES (' t02 ', and;
Mysql> quit

7. View the binary log

[Email protected]_1 ~]# mysqlbinlog--no-defaults--base64-output=decode-rows-v/usr/local/mysql/data/ mysql-bin.000002

8. Truncate the log again

[Email protected]_1 ~]# mysqladmin-uroot-p flush-logs
[Email protected]_1 ~]# ls/usr/local/mysql/data/

9, analog failure to restore data

[Email protected]_1 ~]# mysql-uroot-p
mysql> drop Database School; #删除数据库
Mysql> quit

[Email protected]_1 ~]# mysql-u root-p </opt/school.sql #导入数据
Mysql> select * from School.student;

[Email protected]_1 ~]# cd/usr/local/mysql/data/
[Email protected]_1 data]# mysqlbinlog--no-defaults mysql-bin.000002 | Mysql-u root-p #导入增量数据
[Email protected]_1 ~]# mysql-u root-p
Mysql> select * from School.student;

Five, based on point-in-time recovery data 1, re-import "school" library full backup

[Email protected]_1 ~]# mysql-u root-p </opt/school.sql
[Email protected]_1 ~]# mysql-u root-p

2. Truncate the log

[Email protected]_1 ~]# mysqladmin-uroot-p flush-logs
[Email protected]_1 ~]# ls/usr/local/mysql/data/

3, add data simulation failure

[Email protected]_1 ~]# mysql-uroot-p
mysql> INSERT INTO school.student (Name,score) VALUES (' User01 ', 79);
mysql> Delete from school.student where name= ' Bob '; #注意这条sql语句模拟误删除
mysql> INSERT INTO school.student (Name,score) VALUES (' User02 ', 84);
mysql> INSERT INTO school.student (Name,score) VALUES (' User03 ', 83);

Mysql> select * from School.student;

4. Truncate the log again

[Email protected]_1 ~]# mysqladmin-uroot-p flush-logs
[Email protected]_1 ~]# ls/usr/local/mysql/data/

5, delete the library simulation failure

[Email protected]_1 ~]# mysql-u root-p
mysql> drop Database School;
Mysql> quit

[Email protected]_1 ~]# mysql-u root-p </opt/school.sql #导入完全备份数据

6. Recovering data

[Email protected]_1 ~]# cd/usr/local/mysql/data/
[Email protected]_1 data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000004

[Email protected]_1 data]# mysqlbinlog--no-defaults--stop-datetime= ' 2018-09-03 18:52:11 ' mysql-bin.000004 | Mysql-uroot-p123

# "Stop-datetime" represents an error point in time, loading from the beginning of the binary log file until the end of the error point in time

[Email protected]_1 data]# mysqlbinlog--no-defaults--start-datetime= ' 2018-09-03 18:52:17 ' mysql-bin.000004 | Mysql-uroot-p123

# "Start-datetime" represents the right point of operation, from which point in time to start

Six, location-based recovery data 1, delete the library simulation failure

[Email protected]_1 ~]# mysql-u root-p
mysql> drop Database School;
Mysql> quit

[Email protected]_1 ~]# mysql-u root-p </opt/school.sql #导入完全备份数据

2. Recovering data

[Email protected]_1 ~]# cd/usr/local/mysql/data/
[Email protected]_1 data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000004

[Email protected]_1 data]# mysqlbinlog--no-defaults--stop-position= ' 559 ' mysql-bin.000004 | Mysql-uroot-p123

# "Stop-position" represents the wrong location, loaded from the beginning of the binary log file, until the end of the error location

[Email protected]_1 data]# mysqlbinlog--no-defaults--start-position= ' 664 ' mysql-bin.000004 | Mysql-uroot-p123

# "Start-position" represents the correct operating position, from which location to start

Hyper-verbose MySQL backup strategy