- my.cnf 配置开启bin-log
root@ba586179fe4b:/# vi /etc/my.cnf
[mysqld]
# 开启二进制日志功能，mysql-bin 是日志的基本名或前缀名
log-bin=mysql-bin
- 查看是否开启了bin-log
MySQL [(none)]> show variables like 'log_%';
+----------------------------------------+-----------------------------+
| Variable_name | Value |
+----------------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql-bin |
| log_bin_index | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /data/mysql/mysql-error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+-----------------------------+
21 rows in set (0.00 sec)
binlog 常用命令
- 查看所有bin-log列表
MySQL [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000002 | 697 |
| mysql-bin.000003 | 546 |
| mysql-bin.000004 | 177 |
| mysql-bin.000005 | 436245642 |
+------------------+-----------+
- 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
MySQL [(none)]> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 436245642 | | | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 刷新log日志，自此刻开始产生一个新编号的binlog日志文件
MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.03 sec)
- 重置(清空)所有binlog日志
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.02 sec)
查看binlog日志内容，常用有两种方式
使用mysqlbinlog自带查看命令
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看，binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/data/mysql中)
在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上"–no-defaults"选项
截取bin-log信息
# at 340
#210116 23:00:15 server id 1 end_log_pos 340 CRC32 0x3292f416 Intvar
SET INSERT_ID=500001/*!*/;
#210116 23:00:15 server id 1 end_log_pos 487 CRC32 0x3069a482 Query thread_id=31 exec_time=0 error_code=0
use `whale`/*!*/;
SET TIMESTAMP=1610809215/*!*/;
insert into t_notice_send values(null,12,'ddd',now(),1,1,now())
/*!*/;
# at 487
#210116 23:00:15 server id 1 end_log_pos 518 CRC32 0xa64f3c34 Xid = 446255
参数解释
- server id 1 ： 数据库主机的服务号；
- end_log_pos 487: 这条update语句结束的pos点
- thread_id=31： 线程号
使用show binlog events命令查看
上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
- 查询第一个(最早)的binlog日志
MySQL [whale]> show binlog events\G;
- 指定查询 mysql-bin.000002 这个文件：
MySQL [whale]> show binlog events in 'mysql-bin.000002'\G;
- 指定查询 mysql-bin.000002 这个文件，从pos点:8224开始查起：
MySQL [whale]> show binlog events in 'mysql-bin.000002' FROM 3661146 LIMIT 10\G;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
ERROR:
No query specified
其他的模式有问题,不懂为啥
通过binlog数据恢复
whale数据库会在每天凌晨1点使用计划任务进行一次备份，这里先手动执行一下备份任务。然后就有了数据库截止今天凌晨1点的数据库备份文件。早上9点和中午12点数据库都执行了增删改操作，然后下午18点直接删掉了codehui数据库，场景大概就是这样，下面进行测试数据的恢复。
- 创建表
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 插入数据
MySQL [whale]> INSERT INTO `test` (`id`, `name`) VALUES ('1', 'code');
Query OK, 1 row affected (0.00 sec)
MySQL [whale]> INSERT INTO `test` (`id`, `name`) VALUES ('2', 'php');
Query OK, 1 row affected (0.00 sec)
- 模拟定时备份数据
mysqldump -uroot -p123456 -B -F -R -x --master-data=2 whale|gzip > /opt/mysql/whale.bak.sql.gz
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库"增删改"操作
MySQL [whale]> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
也就是说， mysql-bin.000005 是用来记录凌晨1点之后对数据库的所有"增删改"操作。
4. 早上9点新增3条语句
MySQL [whale]> INSERT INTO `test` (`id`, `name`) VALUES ('3', 'cffff');
Query OK, 1 row affected (0.01 sec)
MySQL [whale]> INSERT INTO `test` (`id`, `name`) VALUES ('4', 'gagsgfs');
Query OK, 1 row affected (0.01 sec)
MySQL [whale]> INSERT INTO `test` (`id`, `name`) VALUES ('5', 'm,mmmms');
Query OK, 1 row affected (0.00 sec)
MySQL [whale]> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | code |
| 2 | php |
| 3 | cffff |
| 4 | gagsgfs |
| 5 | m,mmmms |
+----+---------+
5 rows in set (0.00 sec)
- 中午12点对数据库进行"改"操作，修改1条数据
MySQL [whale]> update test set name='peter' where id = '1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [whale]> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | peter |
| 2 | php |
| 3 | cffff |
| 4 | gagsgfs |
| 5 | m,mmmms |
+----+---------+
5 rows in set (0.00 sec)
- 下午6点的时候,被人恶意的修改了一条语句并把库给删了
MySQL [whale]> update test set name='fuckyou';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
MySQL [whale]> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | fuckyou |
| 2 | fuckyou |
| 3 | fuckyou |
| 4 | fuckyou |
| 5 | fuckyou |
+----+---------+
5 rows in set (0.00 sec)
MySQL [whale]> drop database whale;
Query OK, 3 rows affected (0.05 sec)
- 此时我们先刷新一下bin-log,重新开始新的文件记录
MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 读取日志 分析问题，读取日志方法上面已经说到，这里使用第二种
MySQL [(none)]> show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.25-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 1 | 300 | BEGIN |
| mysql-bin.000005 | 300 | Query | 1 | 431 | use `whale`; INSERT INTO `test` (`id`, `name`) VALUES ('3', 'cffff') |
| mysql-bin.000005 | 431 | Xid | 1 | 462 | COMMIT /* xid=507022 */ |
| mysql-bin.000005 | 462 | Anonymous_Gtid | 1 | 527 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 527 | Query | 1 | 608 | BEGIN |
| mysql-bin.000005 | 608 | Query | 1 | 741 | use `whale`; INSERT INTO `test` (`id`, `name`) VALUES ('4', 'gagsgfs') |
| mysql-bin.000005 | 741 | Xid | 1 | 772 | COMMIT /* xid=507023 */ |
| mysql-bin.000005 | 772 | Anonymous_Gtid | 1 | 837 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 837 | Query | 1 | 918 | BEGIN |
| mysql-bin.000005 | 918 | Query | 1 | 1051 | use `whale`; INSERT INTO `test` (`id`, `name`) VALUES ('5', 'm,mmmms') |
| mysql-bin.000005 | 1051 | Xid | 1 | 1082 | COMMIT /* xid=507024 */ |
| mysql-bin.000005 | 1082 | Anonymous_Gtid | 1 | 1147 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 1147 | Query | 1 | 1228 | BEGIN |
| mysql-bin.000005 | 1228 | Query | 1 | 1347 | use `whale`; update test set name='peter' where id = '1' |
| mysql-bin.000005 | 1347 | Xid | 1 | 1378 | COMMIT /* xid=507026 */ |
| mysql-bin.000005 | 1378 | Anonymous_Gtid | 1 | 1443 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 1443 | Query | 1 | 1524 | BEGIN |
| mysql-bin.000005 | 1524 | Query | 1 | 1630 | use `whale`; update test set name='fuckyou' |
| mysql-bin.000005 | 1630 | Xid | 1 | 1661 | COMMIT /* xid=507028 */ |
| mysql-bin.000005 | 1661 | Anonymous_Gtid | 1 | 1726 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 1726 | Query | 1 | 1821 | drop database whale |
| mysql-bin.000005 | 1821 | Rotate | 1 | 1868 | mysql-bin.000006;pos=4 |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
可以看到,最后破话的语句在1630到1821之间,所以恢复到1630就可以了.
9. 恢复上次备份的数据
mysql -uroot -p123456 -v < whale.bak.sql
获得到凌晨1点的数据
MySQL [whale]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | code |
| 2 | php |
+----+------+
2 rows in set (0.00 sec)
- 从binlog恢复数据
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
mysqlbinlog --no-defaults mysql-bin.000005 --stop-position=1630 --database=whale |mysql -uroot -p123456 -v whale;
- 数据已经恢复了
MySQL [whale]> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | peter |
| 2 | php |
| 3 | cffff |
| 4 | gagsgfs |
| 5 | m,mmmms |
+----+---------+
5 rows in set (0.00 sec)
3669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
