MySQL——全量，增量备份与恢复（实战篇！）

一，全量备份与恢复

1，进入数据库，创建表，插入表数据

[root@master2 ~]# mysql -uroot -p   ##进入数据库Enter password: mysql> create database school;    ##创建数据库Query OK, 1 row affected (0.01 sec)mysql> use school;   ##使用数据库Database changedmysql> create table info(    ##创建表        -> 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;   ##查看表结构+-------+--------------+------+-----+---------+----------------+| 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);   ##插入表数据Query OK, 2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from info;   ##查看表内容+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 ||  2 | stu02 |  77.0 |+----+-------+-------+2 rows in set (0.01 sec)mysql> select * from info limit 1;   ##只显示表中的前1行+----+-------+-------+| id | name  | score |+----+-------+-------+|  1 | stu01 |  88.0 |+----+-------+-------+1 row in set (0.00 sec)

2，对数据库进行物理的完全备份

[root@master2 ~]# cd /usr/local/mysql/data/   ##切换到数据库的数据目录下[root@master2 data]# lsauto.cnf        ibdata1      ib_logfile1  mysql               school  testib_buffer_pool  ib_logfile0  ibtmp1       performance_schema  sys[root@master2 data]# cd school/[root@master2 school]# ls   ##数据中的文件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/ ##用xz格式压缩[root@master2 data]# cd /opt/[root@master2 opt]# lsmysql-2019-11-26.tar.xz  mysql-5.7.20  rh

3，对单个数据库进行逻辑上的备份

[root@master2 opt]# mysqldump -uroot -p school > /opt/school.sql  ##逻辑备份单个数据库Enter password: [root@master2 opt]# lsmysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql[root@master2 opt]# vim school.sql  ##查看备份数据库脚本...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，对多个数据库进行备份

[root@master2 opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql##备份多个数据库Enter password: [root@master2 opt]# lsdb_school_mysql.sql  mysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql

5，对数据库进行完全备份

[root@master2 opt]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql ##完全备份Enter password: [root@master2 opt]# lsall.sql              mysql-2019-11-26.tar.xz  rhdb_school_mysql.sql  mysql-5.7.20             school.sql

6，对数据库中的表进行备份

[root@master2 opt]# mysqldump -uroot -p school info > /opt/school_info.sql ##对数据库中的表进行备份Enter password: [root@master2 opt]# lsall.sql              mysql-2019-11-26.tar.xz  rh               school.sqldb_school_mysql.sql  mysql-5.7.20             school_info.sql

7，对数据库中的表结构进行备份

[root@master2 opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql  ##对表结构进行备份Enter password: [root@master2 opt]# lsall.sql                  mysql-5.7.20          school_info.sqldb_school_mysql.sql      rh                    school.sqlmysql-2019-11-26.tar.xz  school_info_desc.sql

8，基于脚本恢复数据库

[root@master2 opt]# mysql -uroot -p     ##进入数据库Enter password: mysql> show databases;   ##查看数据库+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                || test               |+--------------------+6 rows in set (0.00 sec)mysql> use school;    ##使用数据库Database changedmysql> show tables;   ##查看表+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row 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)mysql> source /opt/school.sql  ##恢复数据库脚本文件mysql> show tables;    ##查看表+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)

9，基于外部MySQL命令恢复数据库

mysql> drop table info;   ##删除表Query OK, 0 rows affected (0.01 sec)mysql> show tables;   ##查看表Empty set (0.00 sec)mysql> quit   ##退出Bye[root@master2 opt]# mysql -uroot -p123123 school < /opt/school.sql   ##利用mysql命令进行恢复mysql: [Warning] Using a password on the command line interface can be insecure.[root@master2 opt]# mysql -uroot -p123123    ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;    ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;   ##查看表+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)

二，MySQL增量备份及恢复

1，开启二进制日志文件

[root@master2 opt]# vim /etc/my.cnf  ##开启二进制日志文件[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysql.pidsocket = /usr/local/mysql/mysql.socklog-bin=mysql-bin   ##开启二进制日志文件server-id = 1[root@master2 opt]# systemctl restart mysqld.service   ##重启mysql服务[root@master2 opt]# cd /usr/local/mysql/data/   ##切换到mysql站点[root@master2 data]# ls     ##查看二进制日志文件auto.cnf        ib_logfile0  mysql             performance_schema  testib_buffer_pool  ib_logfile1  mysql-bin.000001  schoolibdata1         ibtmp1       mysql-bin.index   sys

2，进行完全备份

[root@master2 data]# mysqldump -uroot -p123123 school > /opt/school.sql ##一次完全备份mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@master2 data]# lsauto.cnf        ib_logfile0  mysql             performance_schema  testib_buffer_pool  ib_logfile1  mysql-bin.000001  schoolibdata1         ibtmp1       mysql-bin.index   sys[root@master2 data]# mysqladmin -uroot -p123123 flush-logs  ##刷新二进制日志文件mysqladmin: [Warning] Using a password on the command line interface can be insecure.[root@master2 data]# ls     ##生成新的二进制日志文件，接下来的操作会保存在mysql-bin.000002中auto.cnf        ib_logfile0  mysql             mysql-bin.index     sysib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema  testibdata1         ibtmp1       mysql-bin.000002  school

3，进入数据库，模拟误操作

[root@master2 data]# mysql -uroot -p123123  ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;    ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;  ##查看表+----+------+-------+| 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);   ##正确操作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';   ##错误操作Query OK, 1 row affected (0.00 sec)mysql> insert into info (name,score) values ('by02',99); ##正确操作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    ##刷新二进制日志文件        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##用64位解码器查看二进制日志文件，并生成一个文件[root@master2 data]# cd /opt/[root@master2 opt]# lsbak.txt  mysql-5.7.20  rh  school.sql[root@master2 opt]# vim bak.txt    ##查看二进制日志文件# 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`   ##第一次正确操作的时间和位置### SET###   @1=3###   @2='by01'###   @3=66.0...# at 1302   ##停止位置点#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`   ##第二次执行错误操作的时间和位置191127 20:14:46### WHERE###   @1=1###   @2='st01'###   @3=88.0# at 1405  ##开始位置点#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`   ##第二次正确操作的时间和位置191127 20:15:16### SET###   @1=4###   @2='by02'###   @3=99.0

4，基于时间点进行断点恢复

[root@master2 opt]# mysql -uroot -p123123   ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;  ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> drop table info;   ##删除数据库Query OK, 0 rows affected (0.01 sec)mysql> select * from info;   ##查看表ERROR 1146 (42S02): Table 'school.info' doesn't existmysql> source /opt/school.sql   ##恢复完全备份数据库脚本...mysql> show tables;   ##查看表+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;  ##查看表数据+----+------+-------+| 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##恢复bin.000002中前一个正确的执行语句（从第二个错误语句时间点停止）mysql: [Warning] Using a password on the command line interface can be insecure.[root@master2 opt]# mysql -uroot -p123123   ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;  ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)[root@master2 opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-27 20:15:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123   ##跳过错误节点，恢复最后一个正确的操作（从最后一个正确的操作时间点开始）mysql: [Warning] Using a password on the command line interface can be insecure.[root@master2 opt]# mysql -uroot -p123123  ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;   ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;    ##查看表数据，恢复了第二次正确操作，跳过了错误的操作+----+------+-------+| 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，基于位置点进行断点恢复

mysql> delete from info where name='by01';    ##为实验方便直接删除Query OK, 1 row affected (0.01 sec)mysql> delete from info where name='by02';    ##删除Query OK, 1 row affected (0.00 sec)mysql> select * from info;  ##完全备份的初始状态+----+------+-------+| id | name | score |+----+------+-------+|  1 | st01 |  88.0 ||  2 | st02 |  77.0 |+----+------+-------+2 rows in set (0.00 sec)mysql> quitBye[root@master2 opt]# mysqlbinlog --no-defaults --stop-position='1302' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123                           ##跳过错误操作的位置点从上一个位置点开始[root@master2 opt]# mysql -uroot -p123123   ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;    ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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> quitBye[root@master2 opt]# mysqlbinlog --no-defaults --start-position='1405' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123   ##从错误的位置后一个位置点开始，跳过错误操作的位置点[root@master2 opt]# mysql -uroot -p123123   ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use school;   ##使用数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from info;    ##查看表数据，跳过错误操作，恢复第二次正确操作数据+----+------+-------+| 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，对于增量备份全部恢复

[root@master2 opt]# mysqlbinlog --no-defaults  /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123##全部增量恢复

谢谢阅读！