Mysql之Binlog日志 完全备份|增量 +binlog日志基于时间点恢复数据
| 初步了解binlog： |
众所周知，binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下，我们往往会想到用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数据部分），化险为夷！
废话不多说，下面是梳理的binlog日志操作解说：
MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
DDL
----Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
DML
----Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
| mysqlbinlog 常用选项 一般来说开启binlog日志大概会有1%的性能损耗： |
1 --start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间 2 --stop-datetime： 从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样 3 --start-position：从二进制日志中读取指定position 事件位置作为开始。 4 --stop-position： 从二进制日志中读取指定position 事件位置作为事件截至
| binlog日志有两个最重要的使用场景： |
1）MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到
master-slave数据一致的目的。
2）自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
binlog日志包括两类文件：
1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。
| 开启binlog日志： |
1 编辑mariadb配置文件 2 [root@node-zabbix ~]# cat /etc/my.cnf 3 在[mysqld] 区块添加 4 innodb_file_per_table =ON #为每个表开启单独的表空间 5 log-bin=mysql-bin #开启binlog mysql-bin 只是日志前缀 6 [root@node-zabbix ~]# systemctl restart mariadb.service
| 查看binlog日志是否开启： |
1 [root@node-zabbix ~]# mysql -e "show variables like 'log_%'" 2 +---------------------------------+--------------------------------------------------------------------------------------------------------------+ 3 | Variable_name | Value | 4 +---------------------------------+--------------------------------------------------------------------------------------------------------------+ 5 | log_bin | ON | 6 | log_bin_trust_function_creators | OFF | 7 | log_error | /var/log/mariadb/mariadb.log | 8 | log_output | FILE | 9 | log_queries_not_using_indexes | OFF | 10 | log_slave_updates | OFF | 11 | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | 12 | log_slow_queries | OFF | 13 | log_slow_rate_limit | 1 | 14 | log_slow_verbosity | | 15 | log_warnings | 1 | 16 +---------------------------------+--------------------------------------------------------------------------------------------------------------+
| 常用binlog日志操作命令： |
#查看所有binlog日志列表
[root@node-zabbix ~]# mysql -e "show master logs" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 30349 | | mysql-bin.000002 | 1038814 | | mysql-bin.000003 | 106446169 | | mysql-bin.000004 | 16705415 | +------------------+-----------+
#查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
[root@node-zabbix ~]# mysql -e "show master status" +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 16725872 | | | +------------------+----------+--------------+------------------+
#flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件
注意：
每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
[root@node-zabbix ~]# mysql -e "flush logs" [root@node-zabbix ~]# mysql -e "show master logs" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 30349 | | mysql-bin.000002 | 1038814 | | mysql-bin.000003 | 106446169 | | mysql-bin.000004 | 16761013 | | mysql-bin.000005 | 1698 | +------------------+-----------+
#重置(清空)所有binlog日志
[root@node-zabbix ~]# mysql -e "reset master" [root@node-zabbix ~]# mysql -e "show master logs" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1032 | +------------------+-----------+
| 查看binlog日志内容： |
1 #使用mysqlbinlog自带查看命令法： 2 [root@node01 mysql]# mysqlbinlog mysql-bin.000001 .......... 3 .......... 4 .......... 5 # at 593 6 #180416 21:59:10 server id 1 end_log_pos 712 Query thread_id=3 exec_time=0 error_code=0 7 SET TIMESTAMP=1523930350/*!*/; 8 insert into tb1(name,age)values('simple',23),('alex',34) #执行SQL语句 9 /*!*/; 10 # at 712 11 #180416 21:59:10 server id 1 end_log_pos 739 Xid = 24 12 COMMIT/*!*/; #执行sql语句时间 13 # at 739 14 #180416 22:09:54 server id 1 end_log_pos 807 Query thread_id=3 exec_time=0 error_code=0 15 SET TIMESTAMP=1523930994/*!*/; 16 BEGIN 17 /*!*/; 18 # at 807 19 #180416 22:09:54 server id 1 end_log_pos 835 Intvar 20 SET INSERT_ID=5/*!*/; 21 # at 835 22 #180416 22:09:54 server id 1 end_log_pos 941 Query thread_id=3 exec_time=0 error_code=0 23 SET TIMESTAMP=1523930994/*!*/; 24 insert into tb1(name,age)values('simle',23) 25 /*!*/; 26 # at 941 27 #180416 22:09:54 server id 1 end_log_pos 968 Xid = 25 28 COMMIT/*!*/; 29 DELIMITER ; 30 # End of log file 31 ROLLBACK /* added by mysqlbinlog */; 32 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 33 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 34 解释： 35 server id 1 ： 数据库主机的服务号； 36 end_log_pos 712： sql结束时的pos节点 37 thread_id=3： 线程号
1 2）上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息 2 下面介绍一种更为方便的查询命令： 3 命令格式： 4 mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 5 参数解释： 6 IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件) 7 FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) 8 LIMIT [offset,] ：偏移量(不指定就是0) 9 row_count ：查询总条数(不指定就是所有行) 10 11 如下操作示例： 12 MariaDB [test]> show binlog events in 'mysql-bin.000001'\G; 13 *************************** 1. row *************************** 14 Log_name: mysql-bin.000001 15 Pos: 4 16 Event_type: Format_desc 17 Server_id: 1 18 End_log_pos: 245 19 Info: Server ver: 5.5.56-MariaDB, Binlog ver: 4 20 *************************** 2. row *************************** 21 Log_name: mysql-bin.000001 22 Pos: 245 23 Event_type: Query 24 Server_id: 1 25 End_log_pos: 497 26 Info: use `test`; CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(32) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 27 *************************** 3. row *************************** 28 Log_name: mysql-bin.000001 29 Pos: 497 30 Event_type: Query 31 Server_id: 1 32 End_log_pos: 565 33 Info: BEGIN 34 *************************** 4. row *************************** 35 Log_name: mysql-bin.000001 36 Pos: 565 37 Event_type: Intvar 38 Server_id: 1 39 End_log_pos: 593 40 Info: INSERT_ID=3 41 *************************** 5. row *************************** 42 Log_name: mysql-bin.000001 43 Pos: 593 44 Event_type: Query 45 Server_id: 1 46 End_log_pos: 712 47 Info: use `test`; insert into tb1(name,age)values('simple',23),('alex',34) 48 *************************** 6. row *************************** 49 Log_name: mysql-bin.000001 50 Pos: 712 51 Event_type: Xid 52 Server_id: 1 53 End_log_pos: 739 54 Info: COMMIT /* xid=24 */ 55 *************************** 7. row *************************** 56 Log_name: mysql-bin.000001 57 Pos: 739 58 Event_type: Query 59 Server_id: 1 60 End_log_pos: 807 61 Info: BEGIN 62 *************************** 8. row *************************** 63 Log_name: mysql-bin.000001 64 Pos: 807 65 Event_type: Intvar 66 Server_id: 1 67 End_log_pos: 835 68 Info: INSERT_ID=5 69 *************************** 9. row *************************** 70 Log_name: mysql-bin.000001 71 Pos: 835 72 Event_type: Query 73 Server_id: 1 74 End_log_pos: 941 75 Info: use `test`; insert into tb1(name,age)values('simle',23) 76 *************************** 10. row *************************** 77 Log_name: mysql-bin.000001 78 Pos: 941 79 Event_type: Xid 80 Server_id: 1 81 End_log_pos: 968 82 Info: COMMIT /* xid=25 */ 83 10 rows in set (0.00 sec) 84 85 ERROR: No query specified 86 a）查询第一个(最早)的binlog日志： 87 mysql> show binlog events\G; 88 89 b）指定查询 mysql-bin.000002这个文件： 90 mysql> show binlog events in 'mysql-bin.000002'\G; 91 92 c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起： 93 mysql> show binlog events in 'mysql-bin.000002' from 624\G; 94 95 d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句） 96 mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G; 97 98 e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条 99 mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
| 使用binglog日志恢复mysql数据： |
MariaDB [test]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | jeebbs | | mysql | | performance_schema | | shop | | test | | xtrabackup | | zrlog | +--------------------+ 8 rows in set (0.00 sec) MariaDB [test]> use test; Database changed MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | tb1 | | tb2 | +----------------+ 2 rows in set (0.00 sec) MariaDB [test]> select * from tb1; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 3 | simple | 23 | | 4 | alex | 34 | | 5 | simle | 23 | +----+--------+-----+ 3 rows in set (0.00 sec)
下面开始进行场景模拟
[root@node01 ~]#crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B test -F -R -x --master-data=2 gzip >/tmp/test_$(date +%F).sql.gz
命令的语法格式：
mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库
mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库
[root@node01 ~]# mysqldump -uroot -B test -F -R -x --master-data=2 |gzip >/tmp/test_$(date +%F).sql.gz
[root@node01 ~]# ll /tmp/
total 504
-rw-r--r-- 1 root root 513534 Apr 17 01:32 test_2018-04-17.sql.gz
参数说明：
-A, --all-databases 备份所有表
-x, --lock-all-tables：锁定所有表
-l, --lock-tables：锁定备份的表
--single-transaction：启动一个大的单一事务实现备份
-B, --databases db_name1 db_name2 ...：备份指定的数据库
-C, --compress：压缩传输；
-E, --events：备份指定库的事件调度器；
-R, --routines：备份存储过程和存储函数；
--master-data[=#]：
1：记录CHANGE MASTER TO语句；此语句未被注释；
2：记录为注释语句；
--flush-logs, -F：锁定表之后执行flush logs命令；
-----------------
待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！
由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生
一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
[root@node01 ~]# mysql -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |
+------------------+----------+--------------+------------------+
也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。
2）
早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。
比如：在ops库下member表内插入、修改了数据等等：
先是早上进行插入数据：
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28);
Query OK, 3 rows affected (0.52 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> update tb1 set name='诸葛亮' where id=5;
Query OK, 1 row affected (0.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from tb1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 3 | simple | 23 |
| 4 | alex | 34 |
| 5 | 诸葛亮 | 23 |
| 6 | 张三 | 23 |
| 7 | 李四 | 24 |
| 8 | 店小二 | 28 |
+----+-----------+-----+
6 rows in set (0.00 sec)
在中午12:00的时候，误删了一条数据，过了一会才知道数据被误删了，此时表里又插入了许多数据
MariaDB [test]> delete from tb1 where id=5;
MariaDB [test]> insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
这种时候，一定不要慌张！！！
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
先备份一下最后一个binlog日志文件
[root@node01 mysql]# cp -a mysql-bin.000003 /root/
接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003
这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件
[root@node01 mysql]# mysql -e "flush logs"
[root@node01 mysql]# mysql -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
读取binlog日志，分析问题。
MariaDB [(none)]> show binlog events in 'mysql-bin.000003'\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 245
Info: Server ver: 5.5.56-MariaDB, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000003
Pos: 245
Event_type: Query
Server_id: 1
End_log_pos: 313
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.000003
Pos: 313
Event_type: Intvar
Server_id: 1
End_log_pos: 341
Info: INSERT_ID=6
*************************** 4. row ***************************
Log_name: mysql-bin.000003
Pos: 341
Event_type: Query
Server_id: 1
End_log_pos: 479
Info: use `test`; insert into tb1(name,age)values('张三',23),('李四',24),('店小二',28)
*************************** 5. row ***************************
Log_name: mysql-bin.000003
Pos: 479
Event_type: Xid
Server_id: 1
End_log_pos: 506
Info: COMMIT /* xid=143 */
*************************** 6. row ***************************
Log_name: mysql-bin.000003
Pos: 506
Event_type: Query
Server_id: 1
End_log_pos: 574
Info: BEGIN
*************************** 7. row ***************************
Log_name: mysql-bin.000003
Pos: 574
Event_type: Query
Server_id: 1
End_log_pos: 679
Info: use `test`; update tb1 set name='诸葛亮' where id=5
*************************** 8. row ***************************
Log_name: mysql-bin.000003
Pos: 679
Event_type: Xid
Server_id: 1
End_log_pos: 706
Info: COMMIT /* xid=144 */
*************************** 9. row ***************************
Log_name: mysql-bin.000003
Pos: 706
Event_type: Query
Server_id: 1
End_log_pos: 774
Info: BEGIN
*************************** 10. row ***************************
Log_name: mysql-bin.000003
Pos: 774
Event_type: Query
Server_id: 1
End_log_pos: 863
Info: use `test`; delete from tb1 where id=5
*************************** 11. row ***************************
Log_name: mysql-bin.000003
Pos: 863
Event_type: Xid
Server_id: 1
End_log_pos: 890
Info: COMMIT /* xid=146 */
*************************** 12. row ***************************
Log_name: mysql-bin.000003
Pos: 890
Event_type: Query
Server_id: 1
End_log_pos: 958
Info: BEGIN
*************************** 13. row ***************************
Log_name: mysql-bin.000003
Pos: 958
Event_type: Intvar
Server_id: 1
End_log_pos: 986
Info: INSERT_ID=9
*************************** 14. row ***************************
Log_name: mysql-bin.000003
Pos: 986
Event_type: Query
Server_id: 1
End_log_pos: 1124
Info: use `test`; insert into tb1(name,age)values('张三丰',23),('李奎',24),('曹操',28)
*************************** 15. row ***************************
Log_name: mysql-bin.000003
Pos: 1124
Event_type: Xid
Server_id: 1
End_log_pos: 1151
Info: COMMIT /* xid=147 */
*************************** 16. row ***************************
Log_name: mysql-bin.000003
Pos: 1151
Event_type: Rotate
Server_id: 1
End_log_pos: 1194
Info: mysql-bin.000004;pos=4
16 rows in set (0.00 sec)
ERROR: No query specified
通过分析，造成数据库破坏的pos点区间是介于 774--863 之间（这是按照日志区间的pos节点算的）。
先把凌晨4点全备份的数据恢复：
[root@node01 ~]# mysql -uroot </tmp/test_2018-04-17.sql
但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--12:00之间的数据还没有恢复回来！！
怎么办呢？
莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。
恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
通过刚刚分析binlog日志，我们已经找到了造成数据库破坏的pos点区间是介于 774--863 之间
a）完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)
b）指定pos结束点恢复(部分恢复)：
-stop-position=706 pos结束节点（按照事务区间算，是706）恢复截止到706
--start-position=958 pos开始点
[root@node01 ~]# mysqlbinlog mysql-bin.000003 --stop-position=706 > step1.sql
[root@node01 ~]# mysqlbinlog mysql-bin.000003 --start-position=958 > step2.sql
[root@node01 ~]# mysql -uroot --database=test <step1.sql
[root@node01 ~]# mysql -uroot --database=test <step2.sql
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> select * from tb1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 3 | simple | 23 |
| 4 | alex | 34 |
| 5 | 诸葛亮 | 23 |
| 6 | 张三 | 23 |
| 7 | 李四 | 24 |
| 8 | 店小二 | 28 |
| 9 | 张三丰 | 23 |
| 10 | 李奎 | 24 |
| 11 | 曹操 | 28 |
+----+-----------+-----+
9 rows in set (0.00 sec)
本文参照 http://www.cnblogs.com/kevingrace/p/5907254.html
