当前位置：网站首页>Mysql database backup scheme

Mysql database backup scheme

2022-04-23 20:11:00 【Why am I hungry again】

Database backup

Mysqldump： Logical backup , Hot backup , Total quantity
xtrabackup： Physics , heat , Total quantity + Incremental backup

One 、 What is? MySQL The main equipment

Situation 1 ：

Business operation of the client , read 、 Write Access to the main library
The main library through some mechanism , Synchronize the data to the standby database in real time
The main library for some reasons , Unable to respond to the request of the client

Situation two ：

Complete the active / standby switching
Client read and write , You are accessing the standby database （ At this time, the standby database is upgraded to the new primary database ）

Data synchronization How is it realized ？

1. Master slave synchronization principle

1、 Execute in the standby database change master command , Bind the information of the main library

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.1', MASTER_USER = 'repl', MASTER_PASSWORD = 'replpassword', MASTER_PORT = 3306, MASTER_AUTO_POSITION = 1, MASTER_RETRY_COUNT = 0, MASTER_HEARTBEAT_PERIOD = 10000;

MASTER_HOST ：master Host name （ or IP Address ）
MASTER_PORT ：mysql Instance port number
MASTER_USER： user name
MASTER_PASSWORD： password
MASTER_AUTO_POSITION： If carried out change master to When using MASTER_AUTO_POSITION = 1,slave Connect master Will use based on GTID Replication agreement for
MASTER_RETRY_COUNT： Number of reconnections
MASTER_HEARTBEAT_PERIOD： Copy heartbeat cycle

MySql Chinese document https://www.docs4dev.com/docs/zh/mysql/5.7/reference/change-master-to.html

2、 Standby database execution start slave command , The standby database starts two threads ：I/O thread and SQL thread

3、master Main library , There are data updates , Write the event type of this update to the main library binlog In file

4、 The main library will create log dump Threads , notice slave There are data updates

5、slave, towards master Node log dump The thread requests a specified binlog A copy of the file location , And will request back binlog Save to local Relay log In the relay log

6、slave Open one more SQL Threads Read Relay log journal , Parse out the commands in the log , And implement , So as to ensure the data synchronization of the primary and standby databases

2.mysqldump Backup and recovery logic

Full volume backup

Full backup basic mode ：

Enter into mysql In the container
Create backup directory
adopt mysqldump command , Perform database logical backup operation , Output the result to sql In file .

The main orders are as follows ：

#  Cascade create data backup directory 
mkdir -p /data/backups/dmp
#  Realize all database backup 
mysqldump --opt --single-transaction --master-data=2 --host=localhost --user=root --password=admin --all-databases > /data/backups/dmp/dmp1.sql

mysqldump Description of relevant parameters ：

–opt Suitable for backing up large tables , It also activates -add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset command
–single-transaction Start a transaction , And set the backup transaction to be repeatable , Maintain the consistency of backup data
–master-data=2 Indicates that the of the master database is recorded during the backup process binlog and pos spot , And in dump The comment in the document has been changed to
–all-databases Export all databases , Include mysql library

Full recovery

By in the above Library , perform drop table t_user1, After deleting the table , Carry out recovery ：

adopt mysql command , Can be dump sql Execute the file into the corresponding database .

mysql -h localhost -u root -p < /data/backups/dmp/dmp1.sql

Other backup

1、 Export the specified table of the specified database ：

–databases Specify the database to back up
–tables Specify the specific database table to be backed up

mysqldump --opt --single-transaction --master-data=2 --host=localhost --user=root -p --databases user --tables t_user1 > /data/backups/dmp/dmp2.sql

2、 Only export table creation statements ：

–no-data Declare not to export data , Export only table structure

mysqldump --host=localhost --user=root -p --databases user --tables t_user1 --no-data > /data/backups/dmp/dmp3.sql

3、 Conditional backup ：

–where To specify specific query criteria
–no-create-db Do not export database information
–no-create-info Declare that information such as creating tables will not be exported , This prevents the data table from being deleted

mysqldump --single-transaction --no-create-db --no-create-info --default-character-set=utf8 --host=localhost --user=root --password=admin --databases user --tables t_user1  --where="id >=3" > /data/backups/dmp/dmp4.sql

3.Xtrabackup Physical backup and recovery

Xtrabackup As always MEB（(MySQL Enterprise Backup) Namely MySQL One of the most important tools in Enterprise Edition , It is a data backup scheme for enterprise customers ） The open source spare wheel exists

At present xtrabackup Of 8.0.13 Has supported mysql 8.0.20 edition （8.0.20 Version pair innodb The data file mode has been modified ）

Program installation

And mysql The environment is the same , Need to put xtrabackup The installation to mysql In the container ：

stay https://www.percona.com/downloads/Percona-XtraBackup-LATEST/ Download binary package , For example docker-mysql The container is debian Of buster System , Then download the corresponding file percona-xtrabackup-80_8.0.13-1.buster_amd64.deb
Because the above file installation will also rely on other libraries , So we have to /etc/apt/sources.list Replace the content with domestic image , For example, Alibaba cloud's .
More installation instructions , Reference resources https://www.percona.com/doc/percona-xtrabackup/8.0/installation/apt_repo.html

primary /etc/apt/sources.list The contents of the document are as follows ：

# deb http://snapshot.debian.org/archive/debian/20200422T000000Z buster main
deb http://deb.debian.org/debian buster main
# deb http://snapshot.debian.org/archive/debian-security/20200422T000000Z buster/updates main
deb http://security.debian.org/debian-security buster/updates main
# deb http://snapshot.debian.org/archive/debian/20200422T000000Z buster-updates main
deb http://deb.debian.org/debian buster-updates main

Replace all contents with ：

deb http://mirrors.aliyun.com/debian/ buster main non-free contrib
deb http://mirrors.aliyun.com/debian-security buster/updates main
deb http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib
deb http://mirrors.aliyun.com/debian/ buster-backports main non-free contribb

Specific command operation ：

#  Copy out the files in the container 
docker cp mysql-dump-test:/etc/apt/sources.list D:\dev2\test\mysqldump
#  Overwrite the modified file back into the container 
docker cp D:\dev2\test\mysqldump\sources.list mysql-dump-test:/etc/apt/
#  After entering the container , Execute the following command to update apt Information 
apt-get update

After completing the above preparations , You can start the installation xtrabackup：

Will download good files percona-xtrabackup-80_8.0.13-1.buster_amd64.deb, Copy to container
adopt dpkg To install , The first execution will report an error , According to the error message , Find the bottom dependency libev4
perform apt install libev4, At this time, an error will still be reported , Follow the prompt , perform apt --fix-broken install, All dependent packages will be downloaded and installed .
After the previous step is completed , Re execution dpkg name , Complete the installation

The specific command operations are as follows ：

#  Copy the downloaded file to the container 
docker cp D:\dev2\test\mysqldump\percona-xtrabackup-80_8.0.13-1.buster_amd64.deb mysql-dump-test:/data
#  Perform the first installation , An error message appears 
dpkg -i percona-xtrabackup-80_8.0.13-1.buster_amd64.deb
#  perform libev4  install , There will be mistakes 
apt install libev4
#  Perform dependent installation 
apt --fix-broken install
#  Re execution , Complete the installation 
dpkg -i percona-xtrabackup-80_8.0.13-1.buster_amd64.deb

Full backup and recovery

After successful installation , Full backup can be tested ：

Establish in advance /data/backups/ Catalog
By looking at the user manual , The whole backup and recovery is mainly three processes ：backup、prepa、copy-back

The specific command operations are as follows ：

#  Start full backup 
xtrabackup --backup --target-dir=/data/backups/base1 --user=root --password=admin
#  Through execution drop table t_user1 To simulate misoperation 
#  Prepare for full recovery 
xtrabackup --prepare --target-dir=/data/backups/base1
#  Synchronize backup files to mysql In the data file directory 
rsync -avrP /data/backups/base1/ /var/lib/mysql/
#  After exiting the container , Execute container restart , Complete recovery 
docker restart mysql-dump-test

meanwhile , Above rsync You can also use the following command instead of , But it needs to be guaranteed datadir（ That is to say /var/lib/mysql/） It's empty. ：

xtrabackup --copy-back --target-dir=/data/backups/base1

Incremental backup and recovery

The incremental backup and recovery process is as follows ：

First create a full backup
On the basis of full backup , Perform incremental backup
When you recover , Execute the full path first prepare, Then execute the incremental path prepare

Backup specific operation ：

#  Full volume backup 
xtrabackup --backup --target-dir=/data/backups/base2 --user=root --password=admin

#  First incremental backup 
xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base2 --user=root --password=admin

#  Second incremental backup 
xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 --user=root --password=admin

Restore the specific operation ：

#  Restore full backup first 
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2
#  Restore incremental backups item by item 
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2 --incremental-dir=/data/backups/inc1
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base2 --incremental-dir=/data/backups/inc2
#  Synchronize recovered files 
rsync -avrP /data/backups/base2/ /var/lib/mysql/
#  After exiting the container , Execute container restart , Complete recovery 
docker restart mysql-dump-test

4.binlog Several formats of

binlog There are three formats ：row、statement、mixed

Case study ：

First create a table

CREATE TABLE `person` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ' Since the primary key ',
  `income` bigint(20) NOT NULL COMMENT ' income ',
  `expend` bigint(20) NOT NULL COMMENT ' spending ',
  PRIMARY KEY (`id`),
  KEY `idx_income` (`income`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' Personal income statement ';

Insert 4 Bar record ：

insert into person values(50,500,500);
insert into person values(60,600,600);
insert into person values(70,700,700);
insert into person values(80,800,800);

see binlog Pattern ：

View what is currently being written binlog file ：

see binlog The content in , So let's see row Pattern

show binlog events in 'mysql-bin.000001';

explain ：

SET @@SESSION.GTID_NEXT='ANONYMOUS’
BEGIN Start a transaction
Table_map Which record library has been updated 、 Which watch
Write_rows Record what you did , See in detail binlog Need help mysqlbinlog Tools .
COMMIT /* xid=157 */ End a transaction

lookup binlog The physical location of the file ：

[email protected]:/# find / -name mysql-bin.000001
/var/lib/mysql/mysql-bin.000001

With the help of mysqlbinlog command , Check the details ：

mysqlbinlog -vv mysql-bin.000001 --start-position=2986;

The content in the red box indicates that the insert command has been executed ,insert into person values(80,800,800);

among ,@1、@2、@3 Express surface person The first few fields of , No original name , To save space .

modify binlog Format , Set to STATEMENT , Look at the log format ：

set global binlog_format='STATEMENT';

After setting , Need to quit mysql Reconnect the , To see it take effect

show binlog events in 'mysql-bin.000001';

As we can see from the picture , When binlog_format=statement when ,binlog It records SQL The original text of the sentence .

among ,use tomge ： It means to switch to the corresponding database first

If you want to view from the specified location binlog, Can increase from Optional parameters , as follows ：

show binlog events in 'mysql-bin.000001'  from 5168;

statement And row contrast ：

statement Format binlog The record is sql sentence ;row Format binlog The record is event（Table_map,Write_rows,Delete_rows）

When binlog stay statement Under format , The record is sql sentence , The index may be used when the main library is executed A; But when synchronizing to the standby database , Maybe it used Indexes B.

Different indexes , Same article sql sentence , The running results may also be different .

For this scenario , We suggest that row Format binlog.

Even if we use a belt where Conditions （ Such as ：income>720） Of delete sentence , but binlog Records the primary key to be deleted id（id =80 ）, So there will be no mistakes .

mixed Format Of binlog What is a ？

because statement Format binlog It may cause the main library 、 The data synchronization between standby databases is inconsistent , So we will use row Format .

however ,row Format takes up a lot of space , Write binlog It also takes up a lot of IO resources .

therefore , The official proposed a mixed Mixed mode , Integrates the advantages of both .

The contents are as follows ：

mysql Will judge automatically statement Format , Whether it will cause the inconsistency between active and standby
If statement The format will cause the inconsistency between the active and standby , Automatic use row Format .
If statement The format will not cause inconsistency between active and standby , Then use statement Format ,

5. Restore data

Of course , We also suggest that MySQL Of binlog Set to row Pattern , Because it can be used for data recovery . Let's see insert、update、delete Three DML How to recover data .

1、delete：

When we execute delete On command , If binlog_row_image Set up ‘FULL’, that Delete_rows Inside , Contains the values of all fields of the deleted row .

If deleted by mistake , because binlog The values of all fields are recorded , Reverse execution insert That's all right. .

When binlog_row_image Set to MINIMAL, Record only key information , such as id=80

2、insert：

row Under format ,binlog Meeting Record insert All field values for .

If misoperation , Just find the corresponding row based on these values , Re execution delete Just operate

3、update：

row Under format ,binlog Meeting Record update Before the change 、 The modified whole row of data .

If misoperation , Just overwrite with the data before modification .

Recover data through commands ：

If you want to perform data recovery , You can use the following command

mysqlbinlog mysql-bin.000001  --start-position=1  --stop-position=3000 | mysql -h192.168.0.1 -P3306 -u$user -p$pwd;

take mysql-bin.000001 File location from 1 To 3000 Of binlog stay 192.168.0.1 Playback on the machine's database , Restore .

reference ：

1.https://www.zhihu.com/question/38374712/answer/2431612490