From a physical and logical point of view, backups can be divided into physical and logical backups.
(1) Physical backup: A backup of physical files (such as data files, log files, etc.) of the database operating system. can also be divided into cold backup and hot backup.
Cold Backup: Backup operation when the database is closed
Hot backup: A backup operation in the database running state that relies on the log files of the database.
(2) Logical backup: A backup of a database logical component, such as a database object, such as a table.
From a database backup strategy perspective, backups can be divided into full, differential, and incremental backups.
(1) Full backup: Every time a full backup is made to the database. You can back up a single database, multiple databases, all databases, or you can back up a single table in a database, multiple tables.
(2) Differential backup: Backs up files that have been modified since the last full backup, only part of the database is backed up, but the storage and recovery speed is fast.
(3) Incremental backup: Only those files that were modified after the last full or incremental backup are backed up.
With the Mysqldump tool, you can flexibly control the contents of a backup, such as a few tables or libraries that can be backed up separately.
1. Make a full backup of a single library. The command format is as follows:
Mysqldump-u user name-p[password] [options] [database name] >/backup path/backup file name
2. Make a full backup of multiple libraries. The command format is as follows:
Mysqldump-u user name-p[password] [options]--databases library name 1 Library Name 2 ... >/backup path/backup file name
3. Make a full backup of all the libraries. The command format is as follows:
Mysqldump-u user name-p[password]--opt--all-databases >/backup path/backup file name
4. Make a full backup of the table structure. The command format is as follows:
Mysqldump-u user name-p[password]-d database Name table name >/backup path/backup file name
5. Make a full backup of the table. The command format is as follows:
Mysqldump-u user name-p[password] Database name table name >/backup path/backup file name
The following walkthrough is a full backup of the experiment.
To fully back up a single library:
[[email protected] ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql [[email protected] ~]# ls /opt/school-2018-07-19.sql
To fully back up a single table:
[[email protected] ~]# mysqldump -uroot -pabc123 school info > /opt/school_info-$(date +%F).sql[[email protected] ~]# ls /opt/school_info-2018-07-19.sql
1. Log in to MySQL and use the source command to restore the library. The command format is as follows:
Path to Source Library backup script
2. Without logging in to MySQL, use the MySQL command to restore the entire library directly. The command format is as follows:
Mysql-u user name-p[password] < The path to the library backup script
The following walkthrough resumes an experiment after a full backup.
To restore a single library:
If the database is corrupted, delete the database school.
[[email protected] ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.mysql> drop database school;Query OK, 1 row affected (0.02 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)
1) restore using the source command.
Note: First create a database with the same name, then into the database, with the Surce command to recover, or will error.
mysql> create database school;Query OK, 1 row affected (0.01 sec)mysql> use school;Database changedmysql> source /opt/school-2018-07-19.sqlQuery OK, 0 rows affected (0.01 sec)......Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0...Query OK, 0 rows affected (0.00 sec)
2) restore using MySQL command.
Delete the database school once again.
Note: Create a database with the same name before the restore operation, or you will get an error.
After the two methods are restored, review the database recovery success.
To restore a single table:
If the data is corrupted, delete the table info from the database school.
mysql> use school;Database changedmysql> select * from info;+----------+-------+| name | score |+----------+-------+| zhangsan | 75.00 || lisi | 85.00 |+----------+-------+2 rows in set (0.00 sec)mysql> drop table info;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)
1) restore using the source command.
```**
Mysql> Use school;
Database changed
Mysql> Source/opt/school_info-2018-07-19.sql
Query OK, 0 rows Affected (0.00 sec)
...
...
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0
...
Query OK, 0 rows Affected (0.00 sec)
**2）使用mysql命令恢复。**再一次删除表info,然后进行恢复操作。
[Email protected] ~]# Mysql-uroot-pabc123 School </opt/school_info-2018-07-19.sql
两种方法恢复完成后查看表info恢复成功。
Mysql> Use school;
Database changed
### 三、增量备份与恢复MySQL没有提供直接的增量办法，但是可以通过对MySQL的二进制日志间接实现增量备份。二进制日志保存了所有更新或者可能更新数据库的操作。**特点：**- 没有重复数据，备份量不大，时间短- 需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要进行逐个反推恢复，操作繁琐。### 1.实现增量备份**（1）要进行MySQL增量备份，首先要开启二进制日志功能。**在mysql的配置文件的[mysqld]选项中加入log-bin=mysql-bin，然后重启服务。
Vim/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin
Systemctl Restart Mysqld.service
**（2）使用mysqldump完全备份school库。**
[Email protected] ~]# mysqldump-uroot-pabc123 School >/opt/school-$ (Date +%f). sql
[Email protected] ~]# ls/opt/
School-2018-07-19.sql
**（3）使用mysqladmin的选项flush-logs生成新的二进制文件，这样在插入新的数据后，新的二进制文件对应的就是数据库的变化的内容。**
[Email protected] ~]# cd/usr/local/mysql/data/
[[email protected] data]# ls./| grep Mysql.bin.
mysql-bin.000001
Mysql-bin.index
[[email protected] data]# mysqladmin-uroot-pabc123 flush-logs//Log Split
[[email protected] data]# ls./| grep Mysql.bin.
mysql-bin.000001
MYSQL-BIN.000002//Generate a new binary file mysql-bin.000002
Mysql-bin.index
**（4）插入一条新的数据，以模拟数据的增加或变更。**
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Tom ', ' 65 ');
Query OK, 1 row affected (0.01 sec)
Mysql> exit
Bye
此时的数据库变化保存在编号2 的二进制文件中，使用mysqlbinlog命令可以查看二进制文件的内容，里面保存了插入数据的语句。
[Email protected] data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000002
**（5）再次执行flush- logs操作生成新的二进制文件，而新的二进制文件会保存之后的数据操作。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
MYSQL-BIN.000003//Generate a new binary file mysql-bin.000003
Mysql-bin.index
**（6）再次向数据库插入一条数据。**
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Lucy ', ' 70 ');
Query OK, 1 row Affected (0.00 sec)
Mysql> exit
Bye
**（7）最后再执行一次flush-logs操作，确保之前的二进制文件不再发生变化。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004//Generate a new binary file, save the data operation statement
Mysql-bin.index
### 2.丢失完全备份之后更改的数据的恢复**（1）使用delete删除插入的两条数据，也就是假设完全备份后的数据丢失了。**
Mysql> Use school;
Database changed
mysql> Delete from info where name= ' Tom ';
Query OK, 1 row affected (0.01 sec)
mysql> Delete from info where name= ' Lucy ';
Query OK, 1 row Affected (0.00 sec)
**（2）使用二进制文件恢复时需要注意的是恢复顺序，要先恢复最先生成的二进制文件，然后依次执行。**
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-pabc123
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000003|mysql-uroot-pabc123
查看数据库内容，完全备份之后新插入的两条数据找回来了，说明数据恢复成功。### 3. 完全备份之后丢失所有数据的恢复当完全备份和增量备份之后，所有数据丢失，需要把完全备份和所有增量备份文件逐个恢复。**（1）执行flush-logs操作分割日志，插入一条数据。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005//Generate a new binary file, save the data operation statement
Mysql-bin.index
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Jack ', ' 55 ');
Query OK, 1 row affected (0.01 sec)
Mysql> exit
Bye
这条插入语句保存在mysql-bin.000005二进制文件中。**（2）再执行一次flush-logs操作，插入一条数据，之后还要再执行一次flush-logs操作，确保插入数据保存在二进制文件中，不再改变。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006//Generate a new binary file, save the data operation statement
Mysql-bin.index
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Xixi ', ' 45 ');
Query OK, 1 row Affected (0.00 sec)
Mysql> exit
Bye
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.000007//Generate a new binary file, save the data operation statement
Mysql-bin.index
这条插入语句保存在mysql-bin.000006二进制文件中。**（3）使用drop删除表info，也就是假设完全备份前info的数据和完全备份后的数据都丢失了。**
Mysql> Use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.03 sec)
**（4）先使用mysql命令进行完全备份的恢复操作。**
[Email protected] data]# Mysql-uroot-pabc123 School </opt/school-2018-07-19.sql
[Email protected] data]# mysql-uroot-pabc123
MySQL: [Warning] Using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Mysql> Use school;
Database changed
Mysql> SELECT * from info;
+----------+-------+
| name | Score |
+----------+-------+
| Zhangsan | 75.00 |
| Lisi | 85.00 |
| Wangwu | 95.00 |
+----------+-------+
3 Rows in Set (0.00 sec)
被删除的表的数据又可以查询出来**（5）使用二进制文件恢复时需要注意的是恢复顺序，要先恢复最先生成的二进制文件，然后依次执行。**
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000005|mysql-uroot-pabc123
[Email protected] ~]# Mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000006|mysql-uroot-pabc123
可以看到数据全部恢复出来了。### 4. 基于时间点与位置恢复利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复时没有用的，因为日志里面还存在误操作的语句。我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。
Mysql> Use school;
Database changed
Mysql> SELECT * from info;
+----------+-------+
| name | Score |
+----------+-------+
| Zhangsan | 75.00 |
| Lisi | 85.00 |
| Wangwu | 95.00 |
+----------+-------+
3 Rows in Set (0.00 sec)
**首先对数据库进行备份。**
[Email protected] ~]# mysqldump-uroot-pabc123 School >/opt/school-$ (Date +%f). sql
[Email protected] ~]# ls/opt/
School-2018-07-20.sql
### （1）基于时间点恢复将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复。**1）执行flush-logs操作生成新的二进制文件。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.0000020
Generate a new binary file, save the data operation statement
**2）插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。为了确保数据保存在二进制文件中不改变，执行一次flush-logs操作。**
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Coco ', ' 65 ');
Query OK, 1 row Affected (0.00 sec)
mysql> Delete from info where name= ' Lisi ';
Query OK, 1 row affected (0.06 sec)
mysql> INSERT into info (name,score) VALUES (' Momo ', ' 85 ');
Query OK, 1 row Affected (0.00 sec)
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.0000021
Generate a new binary file, save the data operation statement
**3）假设数据损坏，删除表info。**
Mysql> Use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)
**4）使用mysql命令进行完全备份的恢复操作。**
[Email protected] data]# Mysql-uroot-pabc123 School </opt/school-2018-07-20.sql
[Email protected] data]# mysql-uroot-pabc123
MySQL: [Warning] Using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Mysql> Use school;
Database changed
Mysql> SELECT * from info;
+----------+-------+
| name | Score |
+----------+-------+
| Zhangsan | 75.00 |
| Lisi | 85.00 |
| Wangwu | 95.00 |
+----------+-------+
3 Rows in Set (0.00 sec)
**5）查看数据操作语句保存的二进制文件mysql-bin.0000020。**
[Email protected] data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000020
**6）根据二进制文件的数据操作语句的时间点进行完全备份后增量备份的恢复操作。**
[Email protected] data]# mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000020--stop-datetime= ' 18-07-20 0:36:18 ' |mysql-uroot-pabc123
[Email protected] data]# mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000020--start-datetime= ' 18-07-20 0:36:41 ' |mysql-uroot-pabc123
### （2）基于位置恢复使用基于时间点的恢复可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为精确的恢复方式。**假设数据损坏，删除表info。****
Mysql> Use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)
**使用mysql命令进行完全备份的恢复操作。**
[Email protected] data]# Mysql-uroot-pabc123 School </opt/school-2018-07-20.sql
[Email protected] data]# mysql-uroot-pabc123
MySQL: [Warning] Using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Mysql> Use school;
Database changed
Mysql> SELECT * from info;
+----------+-------+
| name | Score |
+----------+-------+
| Zhangsan | 75.00 |
| Lisi | 85.00 |
| Wangwu | 95.00 |
+----------+-------+
3 Rows in Set (0.00 sec)
**1）执行flush-logs操作生成新的二进制文件。**
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.0000024
Generate a new binary file, save the data operation statement
**2）插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。为了确保数据保存在二进制文件中不改变，执行一次flush-logs操作。**
Mysql> Use school;
Database changed
mysql> INSERT into info (name,score) VALUES (' Lili ', ' 65 ');
Query OK, 1 row Affected (0.00 sec)
mysql> Delete from info where name= ' Wangwu ';
Query OK, 1 row affected (0.06 sec)
mysql> INSERT into info (name,score) VALUES (' Mimi ', ' 85 ');
Query OK, 1 row Affected (0.00 sec)
[Email protected] data]# mysqladmin-uroot-pabc123 flush-logs
[[email protected] data]# ls./| grep mysql.bin.*
mysql-bin.0000025
Generate a new binary file, save the data operation statement
**3）查看数据操作语句保存的二进制文件mysql-bin.0000024。**
[Email protected] data]# mysqlbinlog--no-defaults--base64-output=decode-rows-v mysql-bin.000024
**4）根据二进制文件的数据操作语句的时间点进行完全备份后增量备份的恢复操作。**
[Email protected] data]# mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000024--stop-position= ' 615 ' | Mysql-uroot-pabc123
[Email protected] data]# mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000024--start-position= ' 661 ' | Mysql-uroot-pabc123
MySQL full backup, incremental backup and recovery
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
