MySQL备份与恢复（10）mysqlbinlog命令
一、mysqlbinlog #解析 mysql的binlog日志
1、mysql的binlog日志是什么？
数据目录下的如下文件就是
mysqlbin_oldboy.000001
mysqlbin_oldboy.000002
mysqlbin_oldboy.000003
mysqlbin_oldboy.000004
2、mysql的binlog日志作用是什么？
用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录。
3、binlog功能怎么打开
[root@localhost data]# grep log-bin /etc/my.cnf
log-bin=mysqlbin_oldboy
4、mysqlbinlog查看日志，（这里不能用cat查看），不记录 select 记录
[root@localhost bak]# ll /application/mysql/data/ 总用量 29292 -rw-r--r--. 1 root root 2014 9月 20 22:24 bin.sql -rw-rw----. 1 mysql mysql 18874368 9月 21 17:46 ibdata1 -rw-rw----. 1 mysql mysql 5242880 9月 21 17:46 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 9月 19 01:03 ib_logfile1 -rw-r-----. 1 mysql root 21685 9月 21 17:46 localhost.localdomain.err -rw-rw----. 1 mysql mysql 5 9月 21 17:46 localhost.localdomain.pid drwx------. 2 mysql mysql 4096 9月 21 17:07 mysql -rw-rw----. 1 mysql mysql 547 9月 20 22:18 mysqlbin_oldboy.000001 -rw-rw----. 1 mysql mysql 556247 9月 21 17:44 mysqlbin_oldboy.000002 -rw-rw----. 1 mysql mysql 126 9月 21 17:46 mysqlbin_oldboy.000003 -rw-rw----. 1 mysql mysql 107 9月 21 17:46 mysqlbin_oldboy.000004 -rw-rw----. 1 mysql mysql 100 9月 21 17:46 mysqlbin_oldboy.index drwx------. 2 mysql mysql 4096 9月 21 17:26 oldboy drwx------. 2 mysql mysql 4096 9月 21 17:07 oldboy_gbk drwx------. 2 mysql mysql 4096 9月 21 17:07 oldboy_utf8 drwx------. 2 mysql mysql 4096 9月 19 01:02 performance_schema [root@localhost bak]# mysql -uroot -pdubin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use oldboy Database changed mysql> show tables; +------------------+ | Tables_in_oldboy | +------------------+ | SC | | course | | student | | test | | test1 | +------------------+ 5 rows in set (0.00 sec) mysql> select *from test; +----+---------+ | id | name | +----+---------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | +----+---------+ 5 rows in set (0.00 sec) mysql> insert into test1 values(5); Query OK, 1 row affected (0.12 sec) mysql> select *from test1; +------+ | id | +------+ | 2 | | 3 | | 2 | | 5 | +------+ 4 rows in set (0.00 sec) mysql> insert into test1 values(4); Query OK, 1 row affected (0.00 sec) mysql> select *from test1; +------+ | id | +------+ | 2 | | 3 | | 2 | | 5 | | 4 | +------+ 5 rows in set (0.00 sec) mysql> update test set id=1 where id=3; ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> update test1 set id=1 where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select *from test1; +------+ | id | +------+ | 2 | | 1 | | 2 | | 5 | | 4 | +------+ 5 rows in set (0.00 sec) mysql> quit Bye [root@localhost bak]# ll /application/mysql/data/ 总用量 29292 -rw-r--r--. 1 root root 2014 9月 20 22:24 bin.sql -rw-rw----. 1 mysql mysql 18874368 9月 21 20:42 ibdata1 -rw-rw----. 1 mysql mysql 5242880 9月 21 20:42 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 9月 19 01:03 ib_logfile1 -rw-r-----. 1 mysql root 21685 9月 21 17:46 localhost.localdomain.err -rw-rw----. 1 mysql mysql 5 9月 21 17:46 localhost.localdomain.pid drwx------. 2 mysql mysql 4096 9月 21 17:07 mysql -rw-rw----. 1 mysql mysql 547 9月 20 22:18 mysqlbin_oldboy.000001 -rw-rw----. 1 mysql mysql 556247 9月 21 17:44 mysqlbin_oldboy.000002 -rw-rw----. 1 mysql mysql 126 9月 21 17:46 mysqlbin_oldboy.000003 -rw-rw----. 1 mysql mysql 679 9月 21 20:42 mysqlbin_oldboy.000004 -rw-rw----. 1 mysql mysql 100 9月 21 17:46 mysqlbin_oldboy.index drwx------. 2 mysql mysql 4096 9月 21 17:26 oldboy drwx------. 2 mysql mysql 4096 9月 21 17:07 oldboy_gbk drwx------. 2 mysql mysql 4096 9月 21 17:07 oldboy_utf8 drwx------. 2 mysql mysql 4096 9月 19 01:02 performance_schema [root@localhost bak]# cd /application/mysql/data/ [root@localhost data]# cat mysqlbin_oldboy.000004 ]Fststd!!!oldboyinsert into test1 values(5)] ]Fstd!!!oldboyBEGIN]\ȁstd!!!oldboyinsert into test1 values(4)] ]Fstd!!!oldboyBEGIN¿]astd!!!oldboyupdate test1 set id=1 where id=3¿][root@localhost data]# [root@localhost data]# mysqlbinlog mysqlbin_oldboy.000004 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #190921 17:46:21 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 190921 17:46:21 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' bfGFXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABt8YVdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #190921 20:41:38 server id 1 end_log_pos 177 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569069698/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 177 #190921 20:41:38 server id 1 end_log_pos 269 Query thread_id=2 exec_time=0 error_code=0 use `oldboy`/*!*/; SET TIMESTAMP=1569069698/*!*/; insert into test1 values(5) /*!*/; # at 269 #190921 20:41:38 server id 1 end_log_pos 296 Xid = 9 COMMIT/*!*/; # at 296 #190921 20:41:49 server id 1 end_log_pos 366 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569069709/*!*/; BEGIN /*!*/; # at 366 #190921 20:41:49 server id 1 end_log_pos 458 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569069709/*!*/; insert into test1 values(4) /*!*/; # at 458 #190921 20:41:49 server id 1 end_log_pos 485 Xid = 11 COMMIT/*!*/; # at 485 #190921 20:42:39 server id 1 end_log_pos 555 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569069759/*!*/; BEGIN /*!*/; # at 555 #190921 20:42:39 server id 1 end_log_pos 652 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569069759/*!*/; update test1 set id=1 where id=3 /*!*/; # at 652 #190921 20:42:39 server id 1 end_log_pos 679 Xid = 14 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost data]# mysql -uroot -pdubin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
5、mysqlbinlog -d 数据库 数据库日志分库
mysql> create table oldgirl(name char(10)); Query OK, 0 rows affected (0.16 sec) mysql> insert into oldgirl values("oldgirl"); Query OK, 1 row affected (0.01 sec) mysql> insert into oldgirl values("ddddd"); Query OK, 1 row affected (0.00 sec) mysql> select * from oldgirl; +---------+ | name | +---------+ | oldgirl | | ddddd | +---------+ 2 rows in set (0.00 sec) [root@localhost data]# mysqlbinlog mysqlbin_oldboy.000004 >all.sql [root@localhost data]# less all.sql [root@localhost data]# mysqlbinlog -d oldboy mysqlbin_oldboy.000004 >oldboy.sql [root@localhost data]# vimdiff oldboy.sql all.sql
二、基于位置点和时间点的增量恢复
1、指定开始位置和结束位置（不指定开始位置是从头开始，不指定结束位置是到结尾）
2、指定开始时间和结束时间（与上述同理）（-r 代表大于号）
