前言
看完本文，删库跑路！？再也不可能产生了！肯定要养成危险操作备份数据的习惯，防止复原数据浪费时间。
本文内容：
- binlog 的作用
- mysqldump 和 mysqlbinlog 做数据备份和数据恢复。
- XtraBackup 全量备份和增量备份
binlog
binlog 作用及配置
Mysql 的 binlog (二进制日志) 是 Server 层的，不论你的存储引擎是什么都能够应用 binlog。
binlog 记录的是数据库 DML 和 DDL 批改的数据内容，也能够用于数据的备份与复原。个别咱们会用
binlog 也用于主从复制，从库申请主库的 binlog 写入到本人的中继日志，而后将中继日志转换为 sql , 而后将 sql 执行在从库执行。
-- 查看是否开启 binlog
SHOW VARIABLES LIKE '%log_bin%'
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+开启二进制日志配置
log_bin 配置是否启用 binlog。Mysql 8.0 默认开启 binlog。
log_bin_index 配置的是 binlog 日志文件的索引信息。这个配置最好配置了之后不要批改。
log_bin_basename 配置的是 binlog 日志的根底门路名称。
server_id 这个也须要配置，在一个集群中这个数字不能反复。
sql_log_bin 配置以后会话 DML 和 DDL 语句是否记录。
[root@centos-7 mysql]# pwd
/var/lib/mysql
[root@centos-7 mysql]# ll | grep binlog
-rw-r-----. 1 mysql mysql 16162 11 月 21 15:58 binlog.000013
-rw-r-----. 1 mysql mysql 179 11 月 21 15:58 binlog.000014
-rw-r-----. 1 mysql mysql 3765 11 月 22 14:42 binlog.000015
-rw-r-----. 1 mysql mysql 1700 11 月 23 23:40 binlog.000016
-rw-r-----. 1 mysql mysql 64 11 月 22 14:42 binlog.index
[root@centos-7 mysql]#binlog 日志格局
-- 查看以后 binlog 文件存储什么数据
SHOW VARIABLES LIKE '%binlog_format%';binlog 日志格局有以下三种
STATEMENT
记录的是 sql 语句。
ROW
Mysql 8.0 默认采纳这个格局。记录每行的批改。相较于 STATEMENT 它可能记录的内容会更多，然而主从复制时更平安。
比方全表更新 update test set a=1; STATEMENT 只会记录这个 sql , 而 ROW 会记录所有数据的批改。
MIXED
当须要时，Mysql 将日志格局从 STATEMENT 切换为 ROW。
比如说更新语句可能记录为逻辑 sql（STATEMENT）, 而插入语句记录为（ROW）格局。
binlog 日志格局验证
创立一张表，插入 10 w 数据
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP PROCEDURE if EXISTS insertData;
CREATE PROCEDURE insertData ( )
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 10000 DO
SET i = i + 1;
INSERT INTO account (username, age)VALUES('测试', 12);
END WHILE;
END;
CALL insertData ();binlog_format在ROW模式下记录的是每行数据的批改
mysql> SHOW BINLOG EVENTS IN 'binlog.000018' limit 10;
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+
| binlog.000018 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000018 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000018 | 156 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 236 | Query | 1 | 322 | BEGIN |
| binlog.000018 | 322 | Table_map | 1 | 386 | table_id: 99 (ceshi2.account) |
| binlog.000018 | 386 | Update_rows | 1 | 8600 | table_id: 99 |
| binlog.000018 | 8600 | Update_rows | 1 | 16814 | table_id: 99 |
| binlog.000018 | 16814 | Update_rows | 1 | 25028 | table_id: 99 |
| binlog.000018 | 25028 | Update_rows | 1 | 33242 | table_id: 99 |
| binlog.000018 | 33242 | Update_rows | 1 | 41456 | table_id: 99 |
+---------------+-------+----------------+-----------+-------------+--------------------------------------+binlog_format在STATEMENT模式下记录的是 sql
flush logs;
update ceshi2.account set username='2';
mysql> SHOW BINLOG EVENTS IN 'binlog.000019' limit 10;
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000019 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000019 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000019 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 235 | Query | 1 | 324 | BEGIN |
| binlog.000019 | 324 | Query | 1 | 446 | update ceshi2.account set username='2' |
| binlog.000019 | 446 | Xid | 1 | 477 | COMMIT /* xid=300671 */ |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+binlog 操作
查看所有的 binlog
-- 查看链接的数据库 binlog 文件信息
SHOW BINARY LOGS;
SHOW MASTER LOGS;
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000013 | 16162 | No |
| binlog.000014 | 179 | No |
| binlog.000015 | 3765 | No |
| binlog.000016 | 1700 | No |
+---------------+-----------+-----------+查看以后正在写入的 binlog
-- 查看以后正在写入的 binlog 文件信息
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000016 | 1700 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)生成新的 binlog
-- 刷新产生新的日志文件
FLUSH LOGS;
-- 原来的日志文件是 binlog.000016
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000017 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)查看 binlog 中的操作
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
mysql> show binlog events limit 100,3;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| binlog.000013 | 9382 | Query | 1 | 9539 | use `ceshi`; GRANT SELECT ON `ceshi`.`test2` TO 'db_dev'@'localhost' /* xid=1023 */ |
| binlog.000013 | 9539 | Anonymous_Gtid | 1 | 9616 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000013 | 9616 | Query | 1 | 9711 | use `ceshi`; FLUSH PRIVILEGES |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------+binlog 落盘机会
Mysql 中有很多 Buffer Pool (能够简略了解为内存)，为了进步数据库性能，个别提交事务之后，二进制日志先写入 Buffer Poll，在写入到二进制文件中。
如果二进制日志没有落盘，那么二进制日志有可能失落，从库进行复制时会失落数据。
参数 sync_binlog 配置写入 Buffer Poll 多少次的时候调用零碎调用 fsync 将内存中的二进制日志数据落盘。
sync_binlog=1示意提交事务的时候同步将二进制日志数据落盘。配合innodb_flush_log_at_trx_commit=1（管制 redo log 落盘）数据安全。sync_binlog=N示意提交了 N 个二进制数据时才将日志数据落盘。也有人将其设置为 2，进步并发性，零碎解体时可能失落二进制日志数据。sync_binlog=0示意由操作系统 IO 调度来决定日志什么时候落盘。个别没人采纳这个。
Mysql 备份和复原
为了防止意外状况产生导致数据失落，数据库须要定时全备和增量备份。以便于能够将数据库复原到任意工夫点的数据。
依据备份办法的不同能够划分为：
- 热备（Hot Backup）
- 冷备（Clod Backup）
热备是在数据库正在运行时间接备份，对业务的影响不大。
冷备须要进行 Mysql 进行备份，速度较快。能够在从库进行冷备。
依据备份后的文件内容能够划分为：
- 逻辑备份，数据库执行的 sql 内容
- 文件备份，备份数据库的物理文件
个别咱们会定时对数据执行备份脚本，而后将备份的内容压缩发送到存储文件的服务器，比方 OSS。
备份与复原应用到程序
- mysqldump，对数据库进行不停机执行逻辑备份及复原
- mysqlbinlog，操作 binlog 日志，使数据恢复到某个工夫点的数据
- xtrabackup，percona 开源工具，对数据库不停机进行文件备份
mysqldump 应用
备份某些数据库
mysqldump --master-data --single-transaction --databases ceshi2 ceshi -h10.211.55.8 -uroot -pMysql@12345678 > backup.sql备份所有数据库
mysqldump --master-data --single-transaction --all-databases -h10.211.55.8 -uroot -pMysql@12345678 > backup.sql参数阐明
--single-transaction用于全是InnoDB表的备份。备份开始执行前START TRANSACTION会开启事务，因为MVCC的个性这种备份不会影响数据库读写，而且还保障了备份期间数据的一致性--master-data为 1 时记录CHANGE MASTER语句，能够在从库中应用备份的文件，比方新减少一个从库，就能够在从库上执行这个备份的数据。为 2 时 会正文CHANGE MASTER。--lock-tables锁住单个数据库中所有表，只容许读取数据。为了保障备份时数据的一致性。因为只能锁住单个数据库，如果有多个数据库就不能保证数据的一致性了。当数据库采纳的存储引擎既有InnoDB和MyISAM时须要应用这个属性--lock-all-tables锁住备份所有数据库的表，能保障多个数据库数据的一致性。--databases能够指定备份哪些数据库实例--all-databases备份连贯中所有的数据库实例。--evnets备份事件调度器--routines备份存储过程和存储函数--triggers备份触发器--flush-logs导出之前刷新日志，因为有的数据在内存中，可能还没有写入到二进制日志中
mysqlbinlog 应用
mysqlbinlog 能够解析 binlog 生成 sql 语句。
# 在本地生成 sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql
mysqlbinlog --disable-log-bin /Users/zhangpanqin/Desktop/binlog.000019 > test.sql
# 依据日志的地位
mysqlbinlog binlog.000019 --disable-log-bin --start-position 775 > 775.sql
mysqlbinlog binlog.000019 --disable-log-bin --start-position 477 --stop-position 556 > 477-556.sql
# 依据工夫
mysqlbinlog binlog.000019 --start-date='2017-12-19 10:10:00' --stop-date='2017-12-19 18:52:00' > aa.sql
# 链接近程应用
mysqlbinlog --disable-log-bin --read-from-remote-server --host=10.211.55.8 --user=root --password=Mysql@12345678 binlog.000019 binlog.000020> remote_test.sql--start-position指定从哪个地位开始--stop-position指定从哪个地位开始--start-datetime指定开始工夫--stop-datetime指定完结工夫--disable-log-bin生成的 sql 语句中，增加SET SQL_LOG_BIN=0, 执行转换的 sql 时，不会生成二进制日志--read-from-remote-server从近程服务器读取
数据恢复
个别咱们会应用 mysqldump 进行一个全量备份，在这个全量备份的根底上，从 binlog 提取后续 sql 进行数据恢复。
模仿一个场景
1、比方咱们在某个 2020-11-28 16:30:00 进行了全量备份。
2、2020-11-28 16:35:00 删除了 account 表中全副数据
3、删除之后不晓得，又插入了两条数据
INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '删除全库之后插入', 11);
INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111112, 'asdfasd', 12);<font color=red> 复原数据的时候，为防止复原操作写入到二进制日志中去，须要临时敞开二进制日志，复原会话期间不写入二进制日志 </font>
SET SQL_LOG_BIN=0;
SHOW VARIABLES LIKE '%sql_log_bin%';当初开始对数据库进行数据恢复
- 开始复原之前先
flush logs刷新新的二进制日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+- 设置以后会话不记录二进制日志，并复原全备数据
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/backup.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f- 查看 backup.sql 记录的是什么时候备份的数据
/*CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=477;*/- 应用 mysqlbinlog 导出
binlog从地位477开始的 sql
-- 笨的办法就是，查看删除的 sql 语句
SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;
mysql> SHOW BINLOG EVENTS IN 'binlog.000019' FROM 477 LIMIT 0,10;
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+
| binlog.000019 | 477 | Anonymous_Gtid | 1 | 556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 556 | Query | 1 | 642 | BEGIN |
| binlog.000019 | 642 | Query | 1 | 744 | use `ceshi2`; DELETE FROM `account` |
| binlog.000019 | 744 | Xid | 1 | 775 | COMMIT /* xid=300922 */ |
| binlog.000019 | 775 | Anonymous_Gtid | 1 | 854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 854 | Query | 1 | 940 | BEGIN |
| binlog.000019 | 940 | Query | 1 | 1126 | use `ceshi2`; INSERT INTO `ceshi2`.`account`(`id`, `username`, `age`) VALUES (11111111, '删除全库之后插入', 11) |
| binlog.000019 | 1126 | Xid | 1 | 1157 | COMMIT /* xid=301033 */ |
| binlog.000019 | 1157 | Anonymous_Gtid | 1 | 1236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000019 | 1236 | Query | 1 | 1322 | BEGIN |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------+ -- 导出 477-556 之间的 sql
mysqlbinlog binlog.000019 --start-position 477 --stop-position 556 > 477-556.sql
-- 导出 从 775 开始的 sql
mysqlbinlog binlog.000019 --start-position 775> 775.sql这里比拟好的做法就是间接应用工具间接解析 sql
binlog2sql
https://github.com/danfengcao/binlog2sql- 执行剩下的 sql
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/477-556.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f
(echo "SET SQL_LOG_BIN=0;";cat /Users/zhangpanqin/Desktop/775.sql) | mysql -u root -h 10.211.55.8 -pMysql@12345678 -f- 查看 binlog 日志，没有增加二进制日志到数据库中，不影响从库
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+XtraBackup 应用
XtraBackup 只能备份 InnoDB 和 XtraDB 两种数据表。
装置
零碎环境：Centos 7 x86_64
数据库：Mysql 8.0.21
由以上环境决定了 xtrabackup 须要装置 8.0.14 版本。
https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.htmlwget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
# 验证版本
xtrabackup --version命令解说
--backup备份操作，备份数据到--target-dir指定的目录。--prepare复原数据执行的阶段。--use-memory指定备份时占用的内存，–use-memory=4G。--copy-back将筹备好的数据文件复制到 mysql datadir 目录。`--apply-log-only阻止回滚未实现的事务
全量备份
创立备份应用的用户
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'Mysql@12345678';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;xtrabackup 全量备份
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --target-dir=/opt/test22/backup全量数据恢复
# 先进行数据库
systemctl stop mysqld
# 复原数据执行的筹备
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --prepare --target-dir=/opt/test22/backup
# 备份数据库文件，并删除数据库数据目录下的文件
cp -r /var/lib/mysql{,"$(date'+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*
# 复原数据
xtrabackup --host=localhost --user=xtrabackup --password=Mysql@12345678 --copy-back --target-dir=/opt/test22/backup
# 查看 /var/lib/mysql 目录下所有文件的所属人，须要改成 mysqld 运行的用户
chown -R mysql:mysql /var/lib/mysql
# 启动 mysql 数据库
systemctl start mysqld增量备份
在全量备份的根底上，增量备份。
增量备份
# 创立全量备份在那个目录下
mkdir -p /opt/xtrabackup_mysql/full_data_dir
# 全量根底之后的增量数据一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir
# 在上一次增量备份的根底上在增量备份一次
mkdir -p /opt/xtrabackup_mysql/increment_data_dir_2
# 全量备份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 全量备份之后，操作数据。# 做增量备份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/increment_data_dir --incremental-basedir=/opt/xtrabackup_mysql/full_data_dir
# 操作了数据之后，在上一次增量备份根底上做第二次增量备份
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=xtrabackup --password=Mysql@12345678 --backup --parallel=3 --target-dir=/opt/xtrabackup_mysql/increment_data_dir_2 --incremental-basedir=/opt/xtrabackup_mysql/increment_data_dir增量备份数据恢复
# 进行数据库
systemctl stop mysqld
# 筹备全备份日志数据
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 合并第一次增量备份数据到全量中，留神门路别写错了
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/opt/xtrabackup_mysql/full_data_dir --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir
# 合并第二次增量备份数据到全量中，留神门路。最初一次不须要增加 --apply-log-only
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/opt/xtrabackup_mysql/full_data_dir --incremental-dir=/opt/xtrabackup_mysql/increment_data_dir_2
# 将原来数据库备份
cp -r /var/lib/mysql{,"$(date'+%Y-%m-%d %H:%M:%S')"_bak} && rm -fr /var/lib/mysql/*
-- 拷回数据
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/opt/xtrabackup_mysql/full_data_dir
# 批改 mysql 数据文件的权限为 mysql
chown -R mysql:mysql /var/lib/mysql
# 启动数据库
systemctl start mysqld本文由 张攀钦的博客 http://www.mflyyou.cn/ 创作。可自在转载、援用，但需署名作者且注明文章出处。
如转载至微信公众号，请在文末增加作者公众号二维码。微信公众号名称：Mflyyou