MySQL - full, incremental backup and recovery (Theory + Practice)

Keywords: MySQL Database SQL mysqldump

The importance of data backup

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

2. Causes of data loss

Program error
Human error
Computer failure
Disk failure
Disaster and theft

Classification of database backup

From a physical and logical point of view, backup can be divided into:

1. Physical backup: backup of physical files (such as data files, log files, etc.) 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 shut down
Hot backup: the database is running. This backup method depends on the log files of the database

2. Logical backup: backup of database logical components (such as tables and other database objects)

From the perspective of database backup strategy, backup can be divided into:

Full backup: full backup of data every time

Differential backup: backup files that have been modified since the last full backup

Incremental backup: only files modified after the last full backup or incremental backup will be backed up

Note: differences and increments complement each other

MySQL full backup

1. Full backup refers to the backup of the whole database, database structure and file structure
2. Full backup saves the database at the time of backup completion
3. Full backup is the foundation of incremental backup

(1) advantages of full backup

● simple and convenient backup and recovery operation

(2) disadvantages of full backup

● there is a large amount of duplication of data
● large amount of backup space
● long backup and recovery time

mysqldump backup Library

1. MySQL database can be backed up in many ways

● package the database folder directly, such as / usr/local/mysql/data
 ● use the special backup tool mysqldump

2. mysqldump command

● MySQL's own backup tool, which is quite convenient for MySQL backup
 ● with this command tool, the specified library, table or all libraries can be exported to SQL script, and data recovery can be carried out when recovery is needed

3. mysqldump command makes a full backup of a single library

mysqldump -u user name - p [password] [options] [database name] > / backup path / backup file name

Single database backup example
mysqldump -u root -p auth > /backup/auth.sql
mysqldump -u root -p mysql > /bakcup/mysql.sql

4. mysqldump command backs up multiple databases completely

mysqldump -u user name - p [password] [options] - databases library name 1 [library name 2]... > / backup path / backup file name

Multi database backup example
mysqldump -u root -p --databases autth mysql > /backup/databases-auth-mysql.sql

5. Full backup of all libraries

mysqldump -u username - p [password] [options] - All Databases > / backup path / backup filename

All library backup examples
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

mysqldump backup table

1. In the actual production environment, there are maintenance operations for a specific table. mysqldump also plays an important role

2. Use mysqldump to backup the table
 mysqldump -u user name - p [password] [options] database name table name > / backup path / backup file name

Examples of backup tables
mysqldump -u root -p mysql user > /backup/mysql-user.sql

3. Operation of using mysqldump to backup table structure
 mysqldump -u user name - p [password] [options] - d database name table name > / backup path / backup file name

Example of backup table structure
mysqldump -u root -p mysql -d user > /backup/mysql-user.sql

Recover database

1,Use mysqldump Command exported SQL Backup script, which can be imported by the following methods during data recovery
●source command          Running in database mode
●mysq|command           Linux Running in mode

2,Use source To restore a database
●Log in to MySQL data base
●implement source backups sq|Path to script (absolute path)

source Recovery example
MySQL [(none)] > source /backup/all-data.sql

3,Use mysq|Command recover data
mysql -u User name-p [Password] <Path to library backup script

mysql Command recovery example
mysql -u root -p < /backup/all-data.sql

Operations to recover tables

1,You can also use the source perhaps mysql Command to proceed

2,source The operation of the recovery table is the same as that of the recovery Library

3,When the backup file contains only the backup of the table, not the statement of creating the library, the library name must be specified, and the target library must exist
mysql -u User name-p [Password] < Path to table backup script
mysql -u root -p mysql < /backup/mysql-user.sql

4,In a production environment, you can use shell Script automatic regular backup

MySQL backup ideas

1. Regularly implement backup, make backup plan or strategy, and strictly abide by

2. In addition to full backup, it is important to enable the log function of MySQL server

● full backup plus log can maximize MySQL restore

MySQL Bin: the binary log of MySQL database, used to record the SQL statement (except data query statement) information of the user's operation on the database. You can use the mysqlbin command to view the contents of the binary log

3. Use unified and easy to understand backup file name

● do not use such meaningless names as backup1, backup2, etc
● it is recommended to use the naming rule of library name or table name plus time

MySQL incremental backup

1. Problems in using mysqldump for full backup

● duplicate data in backup data
● long backup time and recovery time

2. Incremental backup is to back up the files or contents added or changed since the last backup

3. Features of incremental backup

● no duplicate data, small amount of backup and short time
● recovery trouble: all incremental backups can be recovered only after the last full backup and full backup, and all incremental backups should be reversely recovered one by one

4. MySQL does not provide a direct incremental backup method

5. Incremental backup can be realized indirectly through binary logs provided by MySQL

6. Significance of MySQL binary log to backup

● binary logs hold all updates or operations that may update the database
● binary log starts recording after starting MySQL server, and recreates a new log file when the file reaches the size set by Max binlog size or after receiving the flush logs command
It only needs to execute the flush logs method regularly to re create new logs, generate binary file sequences, and save these old logs to a safe place in time to complete an incremental backup of a time period

7. General recovery
Add data - perform full backup - enter new data - perform incremental backup - simulate failure - restore operation

8. Reply based on location
It is to import the binary log of a certain starting time into the database, so as to skip a wrong time point and realize data recovery

9. Recovery based on point in time
Using point in time based recovery may occur at a point in time with both correct and wrong operations, so we need a more accurate recovery method

10. Incremental recovery method

(1) general recovery

Mysqlbinlog [-- no defaults] incremental backup file | mysql -u user name - p
(2) location based recovery
● recover data to designated location
Mysqlbinlog -- stop position = operation 'id' 1 base log | mysql -u user name - p password
● recover data from designated location
Mysqlbinlog -- start position = operation 'id' binary log | mysql -u user name - p password

(3) recovery based on time point

● recovery from the beginning of the log to a certain point in time
Mysqlbinlog [-- no defaults] - stop datetime = 'year month day hour: minute: second' binary log | mysql -u user name - p password
● recovery from a certain point in time to the end of the log
Mysqlbinlog [-- no defaults] - start datetime = 'year month day hour: minute: second' binary log | mysql -u user name - p password
● recovery from a point in time to a point in time
Mysqlbinlog [-- no defaults] - start datetime = 'year month day hour: minute: second' -- stop datetime = 'year month day hour: minute: second' binary log | mysql -u user name - p password

View binary log files (decode)
mysqlbinlog --no-defaults --base64-output=decode-rows -V mysql-bin.000002 > /opt/ bak. txt

Full backup and recovery operation

1. Enter database, create table and insert table data

[root@master2 ~]# mysql -uroot -p   ##Enter database
Enter password: 

mysql> create database school;    ##Create database
Query OK, 1 row affected (0.01 sec)

mysql> use school;   ##Use database
Database changed
mysql> create table info(    ##Create table
        -> id int(3) not null primary key auto_increment,
        -> name varchar(10) not null,
        -> score decimal(4,1) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc info;   ##View table structure
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(3)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
| score | decimal(4,1) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('stu01',88),('stu02',77);   ##Insert table data
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;   ##View table contents
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.01 sec)

mysql> select * from info limit 1;   ##Show only the first 1 row in the table
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
+----+-------+-------+
1 row in set (0.00 sec)

2. Make a physical full backup of the database

[root@master2 ~]# cd /usr/local/mysql/data/   ##Switch to the data directory of the database
[root@master2 data]# ls
auto.cnf        ibdata1      ib_logfile1  mysql               school  test
ib_buffer_pool  ib_logfile0  ibtmp1       performance_schema  sys
[root@master2 data]# cd school/
[root@master2 school]# ls   ##Files in data
db.opt  info.frm  info.ibd
[root@master2 school]# cd ..
[root@master2 data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/ ##Compress in xz format
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz  mysql-5.7.20  rh

3. Make a logical backup of a single database

[root@master2 opt]# mysqldump -uroot -p school > /opt/school.sql  ##Logical backup of a single database
Enter password: 
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql
[root@master2 opt]# vim school.sql  ##View backup database scripts

...
CREATE TABLE `info` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `name` varchar(10) NOT NULL,
    `score` decimal(4,1) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
...
LOCK TABLES `info` WRITE;
/*!40000 ALTER TABLE `info` DISABLE KEYS */;
INSERT INTO `info` VALUES (1,'stu01',88.0),(2,'stu02',77.0);

4. Backup multiple databases

[root@master2 opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql
##Backing up multiple databases
Enter password: 
[root@master2 opt]# ls
db_school_mysql.sql  mysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql

5. Make a full backup of the database

[root@master2 opt]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql ##Full backup
Enter password: 
[root@master2 opt]# ls
all.sql              mysql-2019-11-26.tar.xz  rh
db_school_mysql.sql  mysql-5.7.20             school.sql

6. Backup the tables in the database

[root@master2 opt]# mysqldump -uroot -p school info > /opt/school_info.sql ##Backup the tables in the database
Enter password: 
[root@master2 opt]# ls
all.sql              mysql-2019-11-26.tar.xz  rh               school.sql
db_school_mysql.sql  mysql-5.7.20             school_info.sql

7. Backup the table structure in the database

[root@master2 opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql  
##Back up the table structure
Enter password: 
[root@master2 opt]# ls
all.sql                  mysql-5.7.20          school_info.sql
db_school_mysql.sql      rh                    school.sql
mysql-2019-11-26.tar.xz  school_info_desc.sql

8. Recover database based on script

[root@master2 opt]# mysql -uroot -p     ##Enter database
Enter password: 

mysql> show databases;   ##view the database
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use school;    ##Use database
Database changed

mysql> show tables;   ##View table
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> drop table info;    ##Delete table
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;        ###View table
Empty set (0.00 sec)

mysql> source /opt/school.sql  ##Recover database script file

mysql> show tables;    ##View table
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

9. Recover database based on external MySQL command

mysql> drop table info;   ##Delete table
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;   ##View table
Empty set (0.00 sec)

mysql> quit   ##Sign out
Bye
[root@master2 opt]# mysql -uroot -p123123 school < /opt/school.sql   ##Recovery with mysql command
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123    ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;   ##View table
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

Incremental backup and recovery of MySQL

1. Open binary log file

[root@master2 opt]# vim /etc/my.cnf  ##Open binary log file
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin   ##Open binary log file
server-id = 1
[root@master2 opt]# systemctl restart mysqld.service   ##Restart mysql service
[root@master2 opt]# cd /usr/local/mysql/data/   ##Switch to mysql site
[root@master2 data]# ls     ##View binary log files
auto.cnf        ib_logfile0  mysql             performance_schema  test
ib_buffer_pool  ib_logfile1  mysql-bin.000001  school
ibdata1         ibtmp1       mysql-bin.index   sys

2. Make a full backup

[root@master2 data]# mysqldump -uroot -p123123 school > /opt/school.sql ##One full backup
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls
auto.cnf        ib_logfile0  mysql             performance_schema  test
ib_buffer_pool  ib_logfile1  mysql-bin.000001  school
ibdata1         ibtmp1       mysql-bin.index   sys
[root@master2 data]# mysqladmin -uroot -p123123 flush-logs  ##Refresh binary log file
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls     ##Generate a new binary log file, and the next operation will be saved in mysql-bin.000002
auto.cnf        ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema  test
ibdata1         ibtmp1       mysql-bin.000002  school

3. Enter database and simulate misoperation

[root@master2 data]# mysql -uroot -p123123  ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;  ##View table
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('by01',66);   ##Correct operation
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> delete from info where name='st01';   ##Wrong operation
Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,score) values ('by02',99); ##Correct operation
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
3 rows in set (0.00 sec)

[root@master2 data]# mysqladmin -uroot -p123123 flush-logs    ##Refresh binary log file        
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
##View binary log file with 64 bit decoder and generate a file
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
bak.txt  mysql-5.7.20  rh  school.sql
[root@master2 opt]# vim bak.txt    ##View binary log files

# at 1084
#191127 20:14:01 server id 1  end_log_pos 1132 CRC32 0xdcc90eb5         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info`   ##Time and location of first correct operation
### SET
###   @1=3
###   @2='by01'
###   @3=66.0
...
# at 1302   ##Stop position point
#191127 20:14:46 server id 1  end_log_pos 1357 CRC32 0x6648509a         Table_map: `school`.`info` mapped to number 221
# at 1357
#191127 20:14:46 server id 1  end_log_pos 1405 CRC32 0x1eeb752b         Delete_rows: table id 221 flags: STMT_END_F
### DELETE FROM `school`.`info`   ##Time and location of the second incorrect operation 191127 20:14:46
### WHERE
###   @1=1
###   @2='st01'
###   @3=88.0
# at 1405  ##Starting point
#191127 20:14:46 server id 1  end_log_pos 1436 CRC32 0xf1c8d903         Xid = 54
...
# at 1630
#191127 20:15:16 server id 1  end_log_pos 1678 CRC32 0x08d9b0f4         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info`   ##Time and position of the second correct operation 191127 20:15:16
### SET
###   @1=4
###   @2='by02'
###   @3=99.0

4. Breakpoint recovery based on time point

[root@master2 opt]# mysql -uroot -p123123   ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;  ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table info;   ##Delete database
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;   ##View table
ERROR 1146 (42S02): Table 'school.info' doesn't exist
mysql> source /opt/school.sql   ##Restore full backup database script
...
mysql> show tables;   ##View table
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;  ##View table data
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

[root@master2 opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-27 20:14:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123
##Recover the previous correct execution statement in bin.000002 (stop from the second error statement point in time)
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123   ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;  ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;   ##View the table data and recover the first correct operation
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

[root@master2 opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-27 20:15:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123   
##Skip the error node and resume the last correct operation (starting from the last correct operation time point)
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123  ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;   ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;    ##View the table data, recover the second correct operation, and skip the wrong operation
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
4 rows in set (0.00 sec)

5. Breakpoint recovery based on location point

mysql> delete from info where name='by01';    ##Direct deletion for experimental convenience
Query OK, 1 row affected (0.01 sec)

mysql> delete from info where name='by02';    ##delete
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;  ##Initial state of a full backup
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> quit
Bye

[root@master2 opt]# mysqlbinlog --no-defaults --stop-position='1302' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123                           
##Skip position point of wrong operation from previous position point
[root@master2 opt]# mysql -uroot -p123123   ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;   ##View the table data and recover the first correct operation
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye

[root@master2 opt]# mysqlbinlog --no-defaults --start-position='1405' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123   
##Starting from the position point after the wrong position, skip the position point of the wrong operation
[root@master2 opt]# mysql -uroot -p123123   ##Enter database
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;   ##Use database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;    ##View the table data, skip the wrong operation and recover the second correct operation data
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
4 rows in set (0.00 sec)

6. For incremental backup, restore all

[root@master2 opt]# mysqlbinlog --no-defaults  /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123
##Full incremental recovery

Posted by cyberdesi on Thu, 12 Dec 2019 00:15:01 -0800

Programmer Group