第三十七章及增量

1、第三十七章：Mysql备份及增量备份一、数据库备份概述；二、数据库备份的分类；三、数据库备份及其恢复；：物理文件冷备份四、数据库增量备份及其恢复；：mysqldump 热备份：二进制文件备份、mysqlbinlog 恢复五、生产环境 Mysql 数据库备份策略；六、扩展：Xtarbackup 工具备份 Mysql 数据库；一、数据库备份概述；数据库备份概述：将数据库中存在的现有数据，进行存放成为副本数据，可解决数据容灾；提高系统的高可用性和恢复性，数据时，以最小代价重新恢复数据；造成数据丢失的：程序错误、人为错误、磁盘错误、二、数据库备份的分类；物理备份：指对数据库操作系统的物理文件（数据文件

2、、日志文件）等的备份；冷备份：必须在数据库关闭的状态下进行备份，能够更好的保证数据库的完整性； 热备份：能够在数据库处于正常运行的情况下备份，能够更高的保证服务的可用性；逻辑备份：指对数据库的逻辑组件（数据库、表、数据对象）进行备份；完全备份：将数据进行完整的备份，包含完整的库、表、索引、视图等，需要花费更长的时间差异备份：备份自上次完全备份到现在发生改变的数据库内容，备份的文件比完整备份的文件小，备份的速度更快；增量备份：备份至上次完全备份或增量备份后被修改的数据库内容；备份方式比较：三、数据库备份及其恢复；：物理备份（冷备份）rootmy # systemctl stop mysqld r

3、ootmy # mkdir /opt/backup#备份物理文件rootmy # tar zcvf /opt/backup/mysql-backup-$(date +%F).tar.gz /usr/local/mysql/data/rootmy # ls /opt/backup/备份方式完全备份差异备份增量备份完全备份的状态表 1，表 2表 1，表 2表 1，表 2第一次添加内容创建表 3创建表 3创建表 3备份内容表 1，表 2，表 3表 3表 3第二次添加内容创建表 4创建表 4创建表 4备份内容表 1，表 2，表 3，表 4表 3，表 4表 4mysql-backup-2018-08-0

4、2.tar.gz#模拟数据库丢失rootmy # systemctl start mysqldrootmy # mysql -uroot -p123123 mysql> show databases;+| Database+|+| information_schema | mysql| performance_schema | sys+|+4 rows in set (0.00 sec)mysql> drop database mysql;Query OK, 31 rows affected, 2 warnings (0.06 sec) mysql> show databas

5、es;+| Database+|+| information_schema | performance_schema | sys+|+3 rows in set (0.00 sec)mysql> exit#恢复数据库文件rootmy # systemctl stop mysqlrootmy # rm -rf /root/* rootmy # mkdir /root/backuprootmy # tar zxvf /opt/backup/mysql-backup-2018-08-02.tar.gz -C /root/backup/rootmy # cp /root/backup/usr/l

6、ocal/mysql/data/mysql/ /usr/local/mysql/data/ -rf rootmy # chown mysql:mysql -R /usr/local/mysql/data/mysql/rootmy # systemctl start mysqld rootmy # mysql -uroot -p123123 mysql> use mysql;mysql> show tables;+| Tables_in_mysql+|+| columns_priv.mysql> exit|：Mysqldump 命令（热备份） 优点：使用简单、备份还原速度快、缺

7、点：mysql> create database linuxfan; Query OK, 1 row affected (0.01 sec)mysql> show databases;#准备备份的数据库数据+| Database+|+| information_schema | linuxfan| mysql| performance_schema | sys+|+mysql> use linuxfan;Database changedmysql> create table it(岗位 char(16), int);Query OK, 0 rows affected (

8、0.00 sec)号char(48),学历 char(16),工资char(16),mysql> insert into it values('网络工程师','张三',' Query OK, 1 row affected (0.00 sec)mysql> insert into it values('网络工程师','李四',' Query OK, 1 row affected (0.00 sec)','高中','5500');','专科',

9、'12000');mysql> insert into it values('java 工程师',' Query OK, 1 row affected (0.00 sec) mysql> insert into it values('网络工程师',' Query OK, 1 row affected (0.00 sec) mysql> insert into it values('ui 工程师',' Query OK, 1 row affected (0.00 sec) mysql>

10、 select * from it;','初中','10000');','','高中','15000');','','本科','7000');','+| 岗位+号| 学历| 工资|+|+| 网络工程师| 网络工程师| java 工程师| 网络工程师| ui 工程师张三李四| 高中| 专科| 初中| 高中| 本科|5500 |12000 |10000 |15000 |7000 |+mysql> exitrootmy # rm

11、 -rf /opt/*备份所有数据库：rootmy # mysqldump -uroot -p123123 -all-databases >/opt/all.sqlmysqldump: Warning Using a password on the command line interface can be insecure.备份单个数据库的所有表：rootmy # mysqldump -uroot -p123123 -databases linuxfan>/opt/linuxfan.sqlmysqldump: Warning Using a password on the com

12、mand line interface can be insecure.备份数据库中的单个表的结构：rootmy # mysqldump -uroot -p123123 -d linuxfan it>/opt/linuxfan_it_jiegou.sqlmysqldump: Warning Using a password on the command line interface can be insecure.备份数据库中的单个表的内容：rootmy # mysqldump -uroot -p123123 linuxfan it>/opt/linuxfan_it_neirong

13、.sqlmysqldump: Warning Using a password on the command line interface can be insecure.跨服务器进行导出导入，将服务器 a 的数据导入到服务器 b，注：b 服务器的 test 数据库必须已经存在，不然会报错，-C 代表压缩传输；rootmy # mysqldump -host=a -uroot -p123123 -databases linuxfan -C | mysql -host=b-uroot -p123123 test压缩备份：rootmy#mysqldump-uroot-p123123-databas

14、eslinuxfan>/dev/null|gzip >/opt/linuxfan.sql.gzmysqldump: Warning Using a password on the command line interface can be insecure.还原压缩后的数据库文件:rootmy # gunzip -c /opt/linuxfan.sql.gz |mysql -uroot -p123123 linuxfanmysql: Warning Using a password on the command line interface can be insecure.#模拟数

15、据库丢失rootmy # mysql -uroot -p123123mysql> drop database linuxfan; Query OK, 1 row affected (0.01 sec) mysql> show databases;+| Database+|+| information_schema | mysql| performance_schema | sys+|+mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> exit恢复数据库文件：#-f 忽略在还原roo

16、tmy # mysql -uroot -p123123 -f test </opt/linuxfan_it_neirong.sql中的 sql 错误恢复数据库文件：rootmy # mysql -uroot -p123123mysql> create database text;Query OK, 1 row affected (0.00 sec) mysql> use text;Database changedmysql> source /opt/linuxfan_it_neirong.sql.mysql> select * from it;#准备导入数据的数据

17、库#导入数据+| 岗位+号| 学历| 工资|+|+| 网络工程师| 网络工程师| java 工程师| 网络工程师| ui 工程师张三李四| 高中| 专科| 初中| 高中| 本科|5500 |12000 |10000 |15000 |7000 |+mysql> exit四、数据库增量备份及其恢复；：二进制日志文件增量备份1.开启二进制日志文件；rootmy # echo -e "log-bin =max_binlog_size = 100M " >>/etc/f rootmy # tail -2 /etc/f/usr/local/mysql/loysql_b

18、in nserver_id= 1n#添加到mysql 区域内#开启二进制日志文件log-bin = /usr/local/mysql/loserver_id = 1 max_binlog_size = 100M大小ysql_bin#指定单个二进制日志文件的最大rootmy # systemctl restart mysqldrootmy # ls /usr/local/mysql/logs/mysql_bin.000001mysql_bin.indexmysqld.logmysqld.pid件rootmy # cat /usr/local/mysql/loysql_bin.index#二进制文

19、件、索引文/usr/local/mysql/loysql_bin.000001rootmy # cat /usr/local/mysql/lo?binysql_bin.000001.2.查看 mysql 的二进制日志情况；rootmy # mysql -uroot -p123123mysql> show master status;+| File+| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+|+mysql_bin.000001|154|+1 row in set (0.00 sec)mysql>

20、; show master logs;+| Log_name+| File_size |+| mysql_bin.000001 |154 |+1 row in set (0.00 sec)mysql> flush logs; mysql> reset master;mysql>insert into it values('运维工程师',' Query OK, 1 row affected (0.00 sec)mysql>insert into it values('net 工程师',' Query OK, 1 row af

21、fected (0.00 sec)mysql> select * from it;#重新建立二进制文件日志#清空所有二进制文件日志','本科','18000');','','小学','8000');','+| 岗位+号| 学历| 工资|+|+| 网络工程师| 网络工程师| java 工程师| 网络工程师| ui 工程师| 运维工程师| net 工程师张三李四| 高中| 专科| 初中| 高中| 本科| 本科| 小学|5500 |12000 |10000 |15000 |7000

22、|18000 |8000 |+mysql> show master logs;+| Log_name+| File_size |+| mysql_bin.000001 |795 |+1 row in set (0.00 sec)#查看二进制文件的变化mysql> show binlog events in 'mysql_bin.000001'+|+Log_name| Pos| Event_type|Server_id|End_log_pos |Info+|+mysql_bin.000001 |4 | Format_desc|1 |123 | Serverver:5.

23、7.12-log, Binlog ver: 4 | mysql_bin.000001 | 123 | Previous_gtids |.mysql> exit3.模拟故障，删除数据库： rootmy # mysql -uroot -p123123 mysql> drop database linuxfan; Query OK, 1 row affected (0.08 sec)mysql> show databases;#模拟故障，删除数据库+| Database+|+| information_schema | mysql| performance_schema | sys

24、+|+7 rows in set (0.00 sec)4.恢复数据库备份 linuxfan；#创建数据库，进行恢复数据mysql> create database linuxfan;Query OK, 1 row affected (0.01 sec) mysql> use linuxfan;Database changedmysql> source /opt/linuxfan_it_neirong.sql Query OK, 0 rows affected (0.00 sec).mysql> show tables;#首先进行恢复+| Tables_in_linuxf

25、an |+| it+|+1 row in set (0.00 sec)mysql> select * from it;+| 岗位号学历| 工资|+| 网络工程师| 网络工程师| java 工程师| 网络工程师| ui 工程师张三李四| 高中| 专科| 初中| 高中| 本科|5500 |12000 |10000 |15000 |7000 |+mysql> exit5.恢复数据库增量备份 linuxfan； 根据 position 值位置进行恢复；rootmy#mysqlbinlog-start-position=1-stop-position=795/usr/local/mysql/

26、loysql_bin.000001 |mysql -uroot -p123123mysql: Warning Using a password on the command line interface can be insecure.mysqlbinlog: Warning option 'start-position': unsigned value 1 adjusted to 4根据时间点位置进行恢复；-d 指定数据库，选项-h 指定主机rootmy#mysqlbinlog-start-date="2010-09-2918:00:00"-stop-da

27、te="2010-09-2923:00:00" -d text -h 127.0.0.1 /usr/local/mysql/loysql_bin.000001 |mysql -uroot -p123123将二进制日志文件中所有的数据全部恢复；rootmy # mysqlbinlog /usr/local/mysql/lorootmy # mysql -uroot -p123123 mysql> use linuxfan;Database changedmysql> select * from it;ysql_bin.000001 |mysql -uroot -p

28、123123#验证增量恢复的数据+| 岗位+号| 学历| 工资|+|+| 网络工程师| 网络工程师| java 工程师| 网络工程师| ui 工程师| 运维工程师| net 工程师张三李四| 高中| 专科| 初中| 高中| 本科| 本科| 小学|+|5500 |12000 |10000 |15000 |7000 |18000 |8000 |+7 rows in set (0.00 sec)mysql> show master logs;+| Log_name+| File_size |+| mysql_bin.000001 |3242 |+mysql> exitrootmy # m

29、ysqladmin -uroot -p123123 flush-logs#重新建立二进制日志文件mysqladmin: Warning Using a password on the command line interface can be insecure. rootmy # ls /usr/local/mysql/logs/mysql_bin.000001mysql_bin.0000026.查看二进制日志文件的内容；rootmy/usr/local/mysql/lo件内容#mysqlbinlog-stop-position=795# 查看二进制文ysql_bin.000001 >/

30、root/mysql_bin_001.sqlrootmy # cat /root/mysql_bin_001.sql# at 617#二进制日志文件中的 position 值及时间Table_map:#1808023:52:15 server id 1end_log_pos 678 CRC32 0x998a3925linuxfan.it mapped to number 103 # at 678#1808023:52:15 server id 1end_log_pos 764 CRC32 0x18fd7c87103 flags: STMT_END_FWrite_rows: table idBI

31、NLOG 'bw9iWxMBAAAAPQAAAKYCAAAAAGcAAAAAAAEACGxpbnV4ZmFuAAJpdAAF/v7+/gMI/jD+MP6Q/jAf JTmKmQ=bw9iWx4BAAAAVgAAAPwCAAAAAGcAAAAAAAEAAgAF/+AMbmV05bel56iL5biIBuW6nue7nxI4OT U0MzQxOTg5MDkxMDE0NTYG5bCP5a2mQB8AAId8/Rg='/*!*/;.7.查看恢复数据后二进制文件的变化； rootmy # mysql -uroot -p123123mysql> show master logs;+

32、| Log_name+| File_size |+| mysql_bin.000001 | mysql_bin.000002 |3581 |154 |+2 rows in set (0.00 sec)mysql> exitrootmy # ls /usr/local/mysql/loysql_bin.*/usr/local/mysql/lo/usr/local/mysql/loysql_bin.000001ysql_bin.index/usr/local/mysql/loysql_bin.000002五、生产环境 Mysql 数据库备份策略；策略设计思路：1. 数据更新频繁，则应该进行较

33、为频繁的备份；2. 数据较为重要，则在有适当更新时进行备份；3.在数据库备份方案：小的时段进行备份；1.在每周末的某个时段使用 mysqldump 进行重要数据库的而不是 all）；备份（最好备份单个数据库，2.在每天的晚上时段将二进制日志文件终止（前提需要根据数据的生成数量合理指定单个二进制文件的大小），这样每隔 24 小时会生成一个二进制日志文件；3.每周的备份文件和每天的二进制日志文件相加，就是这一周内数据库的整体内容；4.出现故障时，首先恢复备份，可以执行 mysqlbinlog 命令结合实际情况根据 position 值或者时间点进行恢复，确保万无一失；备份：#备份rootmy #

34、vi /opt/mysql_quan_backup.sh#!/bin/bash#quan liang beifen # mkdir /opt/quanliangmkdir /opt/quanliang/backup-$(date +%F)/usr/local/mysql/bin/mysqldump+%F)/linuxfan.sql-uroot-p123123linuxfan>/opt/quanliang/backup-$(dateif -f /opt/quanliang/backup-$(date +%F)/linuxfan.sql ;thenecho"mysqlisquanbackupsuccessontime-$(date+%F)" >>/usr/local/mysql/loelseysql_quan_backup.logecho"mysqlisquanbackupfailontime-$(date+%F)" >>/usr/local/mysql/lofiysql_quan_backup.logrootmy # chmod +x /opt/mysql_quan_ba