mysql data backup

I summary

The main purpose of backup is disaster recovery. Backup can also test applications, rollback data modification, query historical data, audit, etc.

II Importance of data backup

In the production environment, the security of data is very important, and any loss of data may have serious consequences.

III Causes of data loss

1. Program error
2. Human operation error
3. Operation error
4. Disk failure
5. Disasters (such as fire, earthquake) and theft

IV log file

Log plays an important role in backup and recovery.

1.mysql log file

Common log files (modified in [mysqld] client configuration in / etc/my.cnf) are in / usr/local/mysql/data / by default.

2. Error log

It is used to record the error messages generated when mysql starts, stops or runs. It is enabled by default
Updates can be made through the following fields:

log-error=/usr/local/mysql/data/mysql_error.log  #Specify the save location and file name of the log

3. Binary log

Binary log is used to record all updated data or statements that have potentially updated data. It records the changes of data and can be used for data recovery. It is enabled by default.
Opening mode:

log-bin=mysql-bin
 or
log_bin=mysql-bin

4. Relay log

Generally, it is enabled only at the slave node of mysql master-slave synchronization (replication) and read-write separation cluster. The primary node generally does not need this log.

5. Slow query log

Slow query log is used to record all execution times exceeding long_ query_ For the statement of time seconds, you can find which query statements take a long time to execute for optimization. It is closed by default.

slow_query_log=ON  #Enable slow query log
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log  #Specify the file path and name
long_query_time=5  #Set that statements executed for more than 5 seconds will be recorded. The default is 10 seconds

systemctl restart mysqld  

Query command

mysql -u root -p
show variables like 'log_bin%';  #Check whether binary log is enabled
show variables like '%slow%';  #Check whether the slow query log function is enabled
show variables like 'long_query_time';  #View slow query time settings
set global slow_query_log=ON;  #Set the method of starting slow query in the database (temporary)

variable-variable
like-Fuzzy query

#xxx (field)
xxx%  #Fields starting with xxx
%xxx  #Fields ending with xxx
%xxx%  #As long as the xxx field appears, it will be displayed
xxx  #Accurate query

V Backup type

1. Classification from the perspective of physics and logic

Database backup can be divided into physical backup and logical backup. Physical backup refers to the backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be restored quickly in case of problems.
Physical backup can be divided into cold backup (offline backup), hot backup (online backup) and warm backup.

Cold backup	The backup operation is performed when the database is closed
Hot backup	The backup operation is performed when the database is running. The backup method depends on the log file of the database
Warm backup	The backup operation is performed when the database locks the table (not writable but readable).

Logical backup refers to the backup of database logical components (such as tables and other database objects), which is expressed as the information of logical database structure (create database, create table statement) and content (insert statement or separated text file). This type of backup is suitable for small amounts of data that can be marked with data values or table structures, or recreated on different machine architectures.

2. Classification from the perspective of database backup strategy

From the perspective of database backup strategy, database backup can be divided into full backup, differential backup and incremental backup.

(1) Full backup
Every time the data is backed up completely, that is, the whole database, database structure and file structure are backed up. What is saved is the database at the completion time of backup, which is the basis of differential backup and incremental backup. The backup and recovery operations of full backup are very simple and convenient, but there are a lot of data duplication, which will occupy a lot of disk space and take a long time.
(2) Differential backup
Back up all files that have been modified since the last full backup. The backup time node is from the last full backup. The amount of backup data will be larger and larger. When restoring data, you only need to restore the last full backup and the latest differential backup.
(3) Incremental backup
Only those files that have been modified since the last full or incremental backup will be backed up. Taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the backed up data is the smallest, takes up less space and has fast backup speed. However, during recovery, all increments from the last full backup to the last incremental backup need to be recovered in turn. If the backup data in the middle is damaged, it will lead to data loss.

3. Backup method

There are many ways to backup mysql database, such as directly packaging database files (physical cold backup), special backup tools (mysqldump), binary log incremental backup, third-party tool backup, etc.
(1) Physical cold backup
During physical cold backup, it is necessary to maintain the integrity of the database when the database is closed. Physical cold backup is generally used for non core businesses, which are generally allowed to be interrupted. Physical cold backup is characterized by fast speed and the simplest recovery. Backup is usually achieved by directly packaging the database folder (/ usr/local/mysql/data).
(2) Mysqlapdump or mysqlapdump
Both mysqldump program and mysqlhotcopy can be backed up.
mysql dump is a common logic backup program on the client side, which can generate a set of sql statements that can reproduce the original database object definition and table data after being executed. It can be dumped to one or more mysql databases, backed up or transferred to a remote sql server. mysqldump is more versatile because it can back up various tables.
Mysqlhotcopy is only applicable to some storage engines.
(3) Incremental backup mysql by enabling binary logs
Incremental backup is supported. Binary log must be enabled during incremental backup. Binary log files provide users with replication and restore the information required for database changes after the backup point. In case of incremental backup (including data modification since the last full backup or incremental backup), the binary log needs to be refreshed.

Backup through a third-party tool. Percona XtraBackup is a free mysql hot backup software. It supports online hot backup of Innodb and XtraDB. It can also support mysql table backup. However, the backup of myisam table should be carried out under the condition of table lock.

Vi Backup and recovery

1. Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz					#xz is a compression tool
#Compressed backup
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#Decompression recovery
tar Jxvf /opt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data

systemctl start mysqld

2.mysqldump backup and recovery

(1) Full backup of one or more complete libraries (including all tables therein)

#The exported backup file is the database script file
mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql
 Example:
mysqldump -u root -p --databases new > /opt/new.sql
mysqldump -u root -p --databases mysql new > /opt/mysql-new.sql

(2) Fully back up all libraries in the MySQL server

mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql
 Example:
mysqldump -u root -p --all-databases > /opt/all.sql

(3) Fully backs up some tables in the specified library

mysqldump -u root -p[password] [-d] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.sql
#Use the "- d" option to explain that only the table structure of the database is saved
#If the "- d" option is not used, the table data will also be backed up
 Example:
mysqldump -u root -p new first > /opt/new-first.sql

3. View backup files

cat /opt/Backed up files |grep -v "^--" | grep -v "^/" | grep -v "^$"

cat new-first.sql  #There will be comments
 Example:
cat /opt/new-first.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

4.mysql full recovery

(1) Restore database

#"- e" option is used to specify the command to be executed after connecting to MySQL. After the command is executed, it will exit automatically
mysql -u root -p -e 'drop database new;'
mysql -u root -p -e 'show databases;'

mysql -u root -p < /opt/new.sql
mysql -u root -p -e 'show databases;

Operation in table  source /opt/new.sql

Delete database

Restore database

2. Restore data table
When the backup file contains only the backup of the table, but not the statement of the created library, the library name must be specified when performing the import operation, and the target library must exist.

mysql -u root -p -e 'drop table new.first;'
mysql -u root -p -e 'show tables from new;'

mysql -u root -p new < /opt/new_first.sql
mysql -u root -p -e 'show tables from new;'

5.mysql incremental backup

The binary log function needs to be enabled for incremental backup

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id = 1
binlog_format = MIXED				#Specifies that the recording format of binary log is MIXED

#There are three different recording formats for binary log: state (based on SQL STATEMENT), row (based on row) and mixed (mixed mode). The default format is state
binlog_format=STATEMENT((default)
Each article relates to the modified sql Will be recorded binlog Yes.
Disadvantages: the amount of logs is too large, such as sleep()Function, last_insert_id()>，as well as user-defined functions(udf),There will be problems when recording logs for master-slave replication and other architectures

shell binlog_format=ROW
 Only change records are recorded, not recorded sql Context.
Disadvantages: if encountered updata ...... set ...... where true that binlog The data will become larger

binlog_format=MIXED((recommended)
General statement usage statement，Function use ROW Mode access.

(1) The database or table can be fully backed up every week

Incremental backup is based on full backup, so we directly back up the database here

mysqldump -u root -p123456 new > /opt/new$(date +%F).sql  #Backup database

mysqldump -u root -p123456 new second > /opt/new_second$(date +%F).sql  #Backup data table

(2) Incremental backup can be performed every day to generate new binary log files

For example, MySQL bin 000002

mysqladmin -u root -p flush-logs
 Insert new data to simulate the addition or change of data
use new；
insert into second values(2,'xiaohuang','222','running');
insert into decond values(3,'xiaolong','333','swiming');
Generate a new binary file again (for example mysql-bin.000003)
mysqladmin -u root -p flush-logs
 The database operations in previous steps 2 and 3 will be saved in mysql-bin.000002 In the file, and then the database data changes again, it is saved in the mysql-bin.000003 File.

(3) View the contents of binary log files

cp /usr./local/mysql/data/mysql-bin.000002 /opt/   #Copy the log file to the opt directory
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002  #View MySQL bin Contents in 00000 2 document

#--Base64 output = decode rows: use the 64 bit encoding mechanism to decode and read by line
#-v: Show details
#---No defaults: a UTF-8 error will be reported if it is not added

6.mysql incremental recovery

(1) General recovery

Simulate recovery steps for lost changed data

use new；
delete from second where id=2;
delete from second where id=3;

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

Simulate recovery steps for all lost data

use new;
drop table second;
mysql -u root -p new < /opt/new_second_2021-4-15.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 |mysql -u root -p

(2) Breakpoint recovery

The above figure is an example
1. Location based recovery

mysqlbinlog --no-defaults --stop-position='570' /opt/mysql-bin.000003 | mysql -u root -p  
#Only recover the data before the operation ID is "570", that is, the operation of delete from second where id=4 will not be recovered
mysqlbinlog --no-defaults --start-position='570' /opt/mysql-bin.000003 | mysql -u root -p
#Only recover the data after the operation ID is "570", and skip the operation of delete from second where id=3

2. Time based recovery

#Analog data loss
mysql -uroot -p123456 new < /opt/new_second_2021-04-15.sql

mysqlbinlog --no-defaults --stop-datetime='2021-04-15 19:18:04' /opt/mysql-bin.000003 | mysql -uroot -p123456
#By 19:18:04, April 15, 2021, data recovery
mysqlbinlog --no-defaults --start-datetime='2021-04-15 19:18:08' /opt/mysql-bin.000003 |mysql -uroot -p123456
#Only recover the content after 19:18:08 on 2021-04-15, that is, skip the data at 19:18:04 on 2021-04-15

3. Full recovery

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

Summary: breakpoint recovery

If you restore all the data before an SQL statement, stop at the location node or time point of the statement
If you restore an SQ statement and all subsequent data, start from the location node or time point of the statement