点击跳转原文
推荐一本书<<高性能MySQL>>
一.开启binlog日志功能
shell> vim /etc/my.cnf
#在[mysqld] 区块添加
log-bin=mysql-bin #确认是打开状态(mysql-bin 是日志的基本名或前缀名)
#binlog-ignore-db=mysql #忽略数据库(被忽略的数据库中,二进制文件中是无记录的!)
注意:每次服务器(数据库)重启,服务器会调用flush logs;,新创建一个binlog日志!
1.查看所有二进制日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
2.查看mysql某个日志的事件
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 | 103 | 123 | Server ver: 5.7.12-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 103 | 154 | |
| mysql-bin.000001 | 154 | Stop | 103 | 177 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3.查看binlog日志是否开启
mysql> show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name | Value |
+----------------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
......
+----------------------------------------+---------------------------------------+
二.常用的binlog日志操作命令
1.查看所有binlog日志列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
2.查看最新master状态
mysql> show master status;
+------------------+----------+--------------+--------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------+-------------------+
| mysql-bin.000003 | 154 | | jeesite,mysql,test | |
+------------------+----------+--------------+--------------------+-------------------+
3.flush刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 se
注意:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;
4.重置(清空)所有binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
三.查看binlog日志内容(常用有两种方式)
1.binlog日志与数据库文件在同目录中,mysqlbinlog与mysql在同一目录2.可通过查看mysql进程的方式查找mysql命令所在的目录及binlog所在的目录
1.方式一,使用mysqlbinlog脚本 (取出binlog日志的全文内容,不推荐)
[root@emans data]# mysqlbinlog mysql-bin.000001
..............
# at 624
#160925 21:29:53 server id 1 end_log_pos 796 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474810193/*!*/;
insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #执行的sql语句
/*!*/;
# at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 #执行的时间
.............
解释:
server id 1 : 数据库主机的服务号;
end_log_pos 796: sql结束时的pos节点
thread_id=11: 线程号
2.方式二,使用sql命令
语法:
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 :查询总条数(不指定就是所有行)
示例1:
mysql> show binlog events in 'mysql-bin.000001'\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 103
End_log_pos: 123
Info: Server ver: 5.7.12-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 103
End_log_pos: 154
Info:
2 rows in set (0.00 sec)
示例2:
1)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,查询10条(即10条语句)
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
2)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,偏移2行(即中间跳过2个),查询10条
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
四.定时备份数据
语法:mysqldump -u用户名 -p密码 -B -F -R -E -x --master-data=2 数据库名称|gzip >/存储路径/文件名_$(date +%Y%m%d_%H%M%S).sql.gz
参数解释:
- -B:指定数据库
- -F:刷新日志
- -R:备份存储过程等
- -E : 备份事件
- -x:锁表
master-data:
- mysqldump导出数据时,当这个参数的值为1的时候,mysqldump出来的文件就会包括CHANGE MASTER TO这个语句,CHANGE MASTER TO后面紧接着就是file和position的记录,在slave上导入数据时就会执行这个语句,salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1
- 当这个值是2的时候,chang master to也是会写到dump文件里面去的,但是这个语句是被注释的状态
示例每天凌晨4点进行备份指定的库(此处库名为jeesite):
[root@emans data]# crontab -e
#输入一下内容后保存
0 4 * * * /usr/local/mysql/bin/mysqldump -uroot -pemans_My0624 -B -F -R -E -x --master-data=2 jeesite|gzip >/tmp/backup/jeesite_$(date +%Y%m%d_%H%M%S).sql.gz
[root@emans data]# service crond restart #重启定时服务
此处:手动调用一下
[root@emans backup]# /usr/local/mysql/bin/mysqldump -uroot -pemans_My0624 -B -F -R -E -x --master-data=2 jeesite|gzip >/tmp/backup/jeesite_$(date +%Y%m%d_%H%M%S).sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@emans backup]# ll
total 28
-rw-r--r--. 1 root root 26708 Mar 25 23:17 jeesite_20190325_231744.sql.gz
由于上面在备份的时候使用了-F选项,那么当数据备份操作刚开始的时候系统就会自动刷新log,这样就会自动产生
一个新的binlog日志,这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
五.利用binlog日志恢复mysql数据
以下为准备的测试数据;
CREATE TABLE `test` (
`id` varchar(64) NOT NULL COMMENT '编号',
`create_by` varchar(64) NOT NULL COMMENT '创建者',
`create_date` datetime NOT NULL COMMENT '创建时间',
`update_by` varchar(64) NOT NULL COMMENT '更新者',
`update_date` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试数据';
insert test(`id`,`create_by`,`create_date`,`update_by`,`update_date`) values(UUID_SHORT(),'1',NOW(),'1',NOW());
insert test(`id`,`create_by`,`create_date`,`update_by`,`update_date`) values(UUID_SHORT(),'1',NOW(),'1',NOW());
insert test(`id`,`create_by`,`create_date`,`update_by`,`update_date`) values(UUID_SHORT(),'1',NOW(),'1',NOW());
mysql> select * from test;
+---------------------+-----------+---------------------+-----------+---------------------+
| id | create_by | create_date | update_by | update_date |
+---------------------+-----------+---------------------+-----------+---------------------+
| 7447996945420779523 | 1 | 2019-03-25 23:24:51 | 1 | 2019-03-25 23:24:51 |
| 7447996945420779524 | 1 | 2019-03-25 23:24:52 | 1 | 2019-03-25 23:24:52 |
| 7447996945420779525 | 1 | 2019-03-25 23:24:52 | 1 | 2019-03-25 23:24:52 |
+---------------------+-----------+---------------------+-----------+---------------------+
3 rows in set (0.00 sec)
假设现在手贱执行了drop语句,直接删除了jeesite库(自己创建的测试库)!
mysql> drop database jeesite;
Query OK, 46 rows affected (0.14 sec)
这种时候,一定不要慌张!!! 先仔细查看最后一个binlog日志,并记录下关键的pos点,到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步);
1.先备份一下当前binlog日志文件
- 使用 mysql> show master status 查看当前是在哪个二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1811 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 进入mysqlbinlog所在的目录,使用cp命令将当前的二进制文件复制一份保存
[root@emans data]# cp -v mysql-bin.000001 /tmp/backup/
‘mysql-bin.000001’ -> ‘/tmp/backup/mysql-bin.000001’
2.使用flush logs重新生成一个二进制文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
注: 此时mysql-bin.000001(drop database操作在此日志中)这个文件不会再有后续写入了,为便于我们分析原因及查找pos节点,以后所有数据库操作都会写入到下一个日志文件(mysql-bin.000002)
3.读取二进制文件,分析问题
- 方法一:使用mysqlbinlog读取binlog日志,上面已经谈到
- 方法二:使用sql读取二进制文件 (推荐)
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 | 103 | 123 | Server ver: 5.7.12-log, Binlog ver: 4 |
....................
| mysql-bin.000001 | 1640 | Anonymous_Gtid | 103 | 1705 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1705 | Query | 103 | 1811 | drop database jeesite |
| mysql-bin.000001 | 1811 | Rotate | 103 | 1858 | mysql-bin.000002;pos=4 |
....................
或者(通过上述查询已知pos节点在1811)
mysql> show binlog events in 'mysql-bin.000001' from 1705 limit 10\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 1705
Event_type: Query
Server_id: 103
End_log_pos: 1811
Info: drop database jeesite
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 1811
Event_type: Rotate
Server_id: 103
End_log_pos: 1858
Info: mysql-bin.000002;pos=4
2 rows in set (0.00 sec)
通过分析,造成数据库破坏的pos点区间是介于 1705–1811之间(这是按照日志区间的pos节点算的),只要恢复到1705前就可。
4.先将数据恢复到最近一次备份
本例中:先把凌晨4点备份的数据恢复(定时调用)[root@emans data]# cd /tmp/backup/
[root@emans backup]# ll
total 32
-rw-r--r--. 1 root root 26736 Mar 25 23:17 jeesite_20190325_231744.sql.gz
-rw-r-----. 1 root root 1811 Mar 25 23:28 mysql-bin.000001
[root@emans backup]# gzip -d jeesite_20190325_231744.sql.gz
[root@emans backup]# mysql -uroot -p -v < jeesite_20190325_231744.sql
5.将最近一次备份之后的数据变更也恢复
从binlog日志恢复数据语法格式:
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用参数选项解释:
- start-position 起始pos点
- stop-position 结束pos点
- start-datetime 起始时间点
- stop-datetime 结束时间点
不常用选项
- -u --user=name 连接到远程主机的用户名
- -p --password[=name] 连接到远程主机的密码
- -h --host=name 从远程主机上获取binlog日志
- –read-from-remote-server 从某个MySQL服务器上读取binlog日志
注: 实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;
[root@emans backup]# mysqlbinlog /tmp/backup/mysql-bin.000001 > /tmp/backup/000001.sql #提取sql语句
[root@emans backup]# ll
total 208
-rw-r--r--. 1 root root 5105 Mar 26 00:19 000001.sql
-rw-r--r--. 1 root root 197947 Mar 25 23:17 jeesite_20190325_231744.sql
-rw-r-----. 1 root root 1811 Mar 25 23:28 mysql-bin.000001
[root@emans backup]# vim 000001.sql #删除里面的drop语
[root@emans backup]# mysql -uroot -p -v < /tmp/backup/000001.sql
上述为完全恢复.验证;
mysql> select * from test;
+---------------------+-----------+---------------------+-----------+---------------------+
| id | create_by | create_date | update_by | update_date |
+---------------------+-----------+---------------------+-----------+---------------------+
| 7447996945420779523 | 1 | 2019-03-25 23:24:51 | 1 | 2019-03-25 23:24:51 |
| 7447996945420779524 | 1 | 2019-03-25 23:24:52 | 1 | 2019-03-25 23:24:52 |
| 7447996945420779525 | 1 | 2019-03-25 23:24:52 | 1 | 2019-03-25 23:24:52 |
+---------------------+-----------+---------------------+-----------+---------------------+
3 rows in set (0.00 sec)
温馨提示:
在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱!
测试恢复到创建test表之前
mysql> show binlog events in 'mysql-bin.000001' from 1705 limit 10\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001.............................
************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 103
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 219
Event_type: Query
Server_id: 103
End_log_pos: 683
Info: use `jeesite`; CREATE TABLE `test` (
`id` varchar(64) NOT NULL COMMENT '编号',
`create_by` varchar(64) NOT NULL COMMENT '创建者',
`create_date` datetime NOT NULL COMMENT '创建时间',
`update_by` varchar(64) NOT NULL COMMENT '更新者',
`update_date` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试数据'
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 683
Event_type: Anonymous_Gtid
Server_id: 103
End_log_pos: 748
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
............................
指定pos结束点恢复(部分恢复):
–stop-position=219 pos结束节点(按照事务区间算,是219)
[root@emans backup]# mysqlbinlog --stop-position=219 /tmp/backup/mysql-bin.000001 > /tmp/backup/000001_test.sql
[root@emans backup]# mysql -uroot -p -v < /tmp/backup/000001_test.sql
测试前先将上述完全恢复中,已经插入到数据库中的test表删除.
此时因为恢复到截止到test表创建前所以再次查询库显示没有test表,可以继续验证,截止恢复到创建test表后但为插入数据为止