MySQL Full, Incremental Backup and Recovery (Theory + Practice)

Importance of data backup

  • In a production environment, data security is critical, and any loss of data can have serious consequences
  • Causes of data loss
    • Program error
    • Artificial error
    • Computer Failure
    • Disk failure
    • Disasters (such as fires, earthquakes) and theft

Classification of database backups

From a physical and logical perspective, backups can be divided into

  • Physical backup: Backup of physical files (such as data files, log files, etc.) of the database operating system
    • Physical backups can also be divided into offline backups (cold backups) and online backups (hot backups)
    • Cold backup: when the database is closed
    • Hot backup: The database is running and this backup method relies on the database's log files
  • Logical backup: Backup of database logical components, such as database objects such as tables

From the perspective of database backup strategies, backups can be divided into

  • Full backup: A full backup of the data at a time
  • Differential backup: Back up files that have been modified since the last full backup
  • Incremental backup: Only files that were modified after the last full or incremental backup will be backed up

MySQL Full Backup

  • A full backup is a backup of the entire database, database structure, and file structure
  • A full backup saves the database at the end of the backup
  • Full backup is the basis of incremental backup

Advantages of full backup

  • Easy and convenient operation for backup and recovery

Disadvantages of full backup

  • There is a lot of duplication in the data
  • Take up a lot of backup space
  • Long backup and recovery time

mysqldump backup database

  • Backup of MySQL databases can be done in a variety of ways
    • Package database folders directly, such as/usr/local/mysql/data
    • Using the dedicated backup tool mysqldump
  • mysqldump command
    • MySQL's own backup tool makes backing up MySQL fairly easy
    • The command tool allows you to export specified libraries, tables, or all libraries as SQL0 scripts for data recovery when needed
  • The mysqldump command makes a full backup of a single library
    • `mysqldump-u username-p [password] [option] [database name] >/backup path/backup file name
  • Single library backup example
    • mysqldump -u root -p auth > /backup/auth.sql
  • The mysqldump command makes a full backup of multiple libraries
    • Mysqldump-u username-p [password] [option] -- databases library name 1 [library name 2]... > /backup path/backup file name
  • Example of multilibrary backup
    • mysqldump -u root -p --databases autth mysql > /backup/databases-auth-mysql.sql
  • Make a full backup of all libraries
    • Mysqldump-u username-p [password] [option] -- all-databases > /backup path/backup file name
  • All Library Backup Examples
    • mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

mysqldump backup table

  • mysqldump also plays an important role in real-world production environments where maintenance operations exist for a particular table
  • Operation of backing up tables using mysqldump
    • Mysqldump-u username-p [password] [option] database name table name >/backup path/backup file name
  • Example of backup tables
    • mysqldump -u root -p mysql user > /backup/mysql-user.sql

Restore Database

  • SQL backup scripts exported using the mysqldump command can be imported as follows for data recovery
    • source command
    • mysql command
  • Steps to restore the database using source
    • Log on to MySQL database
    • Path to execute source backup sql script
  • Sorce recovery example
    • MySQL[(none)]>source /backup/all-data.sql
  • Recover data using mysql command
    • Mysql-u username-p [password] < path to library backup script
  • mysql command recovery example
    • mysql -u root -p &lt; /backup/all-data.sql

Operation to restore tables

  • You can also use the source or mysq| commands when restoring tables
  • The operation of the source recovery table is the same as that of the recovery Library
  • When a backup file contains only a backup of a table, not a statement to create a library, the library name must be specified and the target library must exist
    • Mysq|-u username-p [password] < path to table backup script
    • mysql -u root -P mysq| &lt; /backup/mysql-user.sql
  • In a production environment, regular backups can be automated using shell scripts

MySQL backup ideas

  • Perform backups regularly, make backup plans or strategies, and strictly adhere to them
  • In addition to making a full backup, it is important to turn on the logging capabilities of the MySQL server
    • Full backup plus log to maximize MySQL restore
  • Use a uniform and easy-to-understand backup file name
    • Don't use meaningless names like backup1, backup2, etc.
    • Naming rules for library or table names plus time are not recommended

MySQL Incremental Backup

Problems with full backup using mysqldump

  • Duplicate data in backup data
  • Backup and recovery time

An incremental backup is a backup of files or content that have been added or changed since the last backup

Features of Incremental Backup

  • No duplicate data, small backups, short time
  • Recovery hassle: All incremental backups after the last full backup and full backup are required to recover, and all incremental backups are backed up one by one

MySQL does not provide a direct incremental backup method

Incremental backup can be achieved indirectly through the binary logs provided by MySQL

Meaning of MySQL binary log for backup

  • The binary log holds all operations that update or possibly update the database
  • Binary logging starts after MySQL server is started and recreates a new log file after the file reaches the size set by max_binlog_size or receives the flush logs command
  • Just perform the flush logs method periodically to recreate new logs, generate binary file sequences, and save these old logs in a safe place in time to complete an incremental backup of a time period

MySQL database incremental recovery

  • General recovery

    • Add Data Make Full Backup Enter New Data Make Incremental Backup Simulate Failure Restore Operation
  • Location-based recovery
    • Is to import a binary log of a starting time into the database, c to skip a point in time at which an error occurred and recover the data
  • Point-in-time recovery

    • With point-in-time recovery, it may occur at a point in time with both correct and incorrect operations, so we need a more precise way to recover

    Incremental recovery methods

    • General recovery
    • mysqlbinlog [--no-defaults] incremental backup file | mysql-u username-p
  • Location-based recovery
    • Recover data to specified location
    • mysqlbinlog --stop-position='operation id'binary log| mysql-u username-p password
    • Restore data from specified location
    • mysqlbinlog --start-position='operation id'binary log| mysql-u username-p password
  • Point-in-time recovery
    • Recovery from the beginning of the log to a point in time
    • Mysqlbinlog [-no-defaults]--stop-datetime='year-month-day hour: minutes: seconds'binary log | mysql-u username-p password
    • Recovery from a point in time to the end of the log
    • Mysqlbinlog [-no-defaults]--start-datetime='year-month-day hour: minutes: seconds'binary log | mysql-u username-p password
    • Recovery from a point in time to a point in time
    • mysqlbinlog [-no-defaults] --start-datetime='year-month-day hour: minutes: seconds'-- stop-datetime='year-month-day hour: minutes: seconds' binary log | mysql-u username-p password

Operation Instances

Create database, edit table data

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

mysql> create database school;      //Create a 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 row in the table
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
+----+-------+-------+
1 row in set (0.00 sec)

Full backup operation

[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

Single Database Logical Backup

[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 script

...
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);
...

Multiple database backups

[root@master2 opt]# Mysqldump-uroot-p --databases school MySQL >/opt/db_school_mysql.sql //Back 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

Full backup

[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

Backup of tables in the database

[root@master2 opt]# Mysqldump-uroot-p school info >/opt/school_info.sql //Back up 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

Table Structure Backup

[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

Script-based database recovery

[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  //Restore database script files

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

Restore database based on external MySQL commands

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 //Restore 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)

MySQL Incremental Backup and Recovery

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

Make a full backup

[root@master2 data]# Mysqldump-uroot-p123123 school >/opt/school.sql //a 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, the next actions 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

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';   //Error 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 files with a 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
#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 second error operation 191127 20:14:46
### WHERE
###   @1=1
###   @2='st01'
###   @3=88.0
# at 1405 //start position
#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 location of second correct operation 191127 20:15:16
### SET
###   @1=4
###   @2='by02'
###   @3=99.0

Breakpoint recovery based on point-in-time

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

mysql> use school;  //Enter Library
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//Recovery bin.000002 The previous correct execution statement (stopped at the second error statement 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;       //Looking at the table data restored 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 restore the last correct operation (starting at the last correct operating 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;      //Looking at the table data, the second correct operation was restored, and the wrong operation was skipped
+----+------+-------+
| 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)

Breakpoint recovery based on location points

mysql> delete from info where name='by01';    //Delete directly for experiment 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 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 the wrong operation from the previous location 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;   //Viewing table data restored 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 //Start from a location after the wrong location, skip the location 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 table data, skip erroneous operations, and restore the second correct 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)

Incremental Backup Full Recovery

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

Tags: Linux MySQL Database SQL mysqldump

Posted on Thu, 12 Dec 2019 17:17:41 -0800 by greekuser