- A+
所属分类：MySQL
当你需要恢复MySQL时，也会需要使用到二进制日志文件。
mysqlbinlog 命令，以用户可视的方式展示出二进制日志中的内容。同时，也可以将其中的内容读取出来，供其他MySQL实用程序使用。
在此示例中，我们将会涉及以下内容：
- 获取当前二进制日志列表
- mysqlbinlog默认行为
- 获取特定数据库条目
- 禁止恢复过程产生日志
- 在输出中控制base-64 BINLOG
- mysqlbinlog输出调试信息
- 跳过前N个条目
- 保存输出到文件
- 从一个特定位置提取条目
- 将条目截止到一个特定的位置
- 刷新日志以清除Binlog输出
- 在输出中只显示语句
- 查看特定开始时间的条目
- 查看特定结束时间的条目
- 从远程服务器获取二进制日志
1 获取当前二进制日志列表
|
1 2 3 4 5 6 7 8 |
mysql> SHOW BINARY LOGS; +----------------------+----------+ | Log_name | File_size | +--------------------------+------------+ | mysqld-bin.000001 | 15740 | | mysqld-bin.000002 | 3319 | .. .. |
|
1 2 |
mysql> SHOW BINARY LOGS; ERROR 1381 (HY000): You are not using binary logging |
|
1 2 3 4 5 |
$ ls -l /var/lib/mysql/ -rw-rw----. 1 mysql mysql 15740 Aug 28 14:57 mysqld-bin.000001 -rw-rw----. 1 mysql mysql 3319 Aug 28 14:57 mysqld-bin.000002 .. .. |
2 mysqlbinlog 默认行为
|
1 |
$ mysqlbinlog mysqld-bin.000001 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/;# at 4#170726 14:57:37 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup# Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #170726 14:59:31 server id 1 end_log_pos 182 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1501095571/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; .. .. .. # at 14191 #170726 15:20:38 server id 1 end_log_pos 14311 Query thread_id=4 exec_time=0 error_code=0SET TIMESTAMP=1501096838/*!*/; insert into salary(name,dept) values('Ritu', 'Accounting')/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE* |
3 获取特定数据库条目
|
1 |
$ mysqlbinlog -d crm mysqld-bin.000001 > crm-events.txt |
|
1 |
$ mysqlbinlog -database crm mysqld-bin.000001 > crm-events.txt |
4 禁止恢复过程产生日志
|
1 |
$ mysqlbinlog -D mysqld-bin.000001 |
|
1 |
$ mysqlbinlog --disable-log-bin mysqld-bin.000001 |
|
1 2 3 |
/*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; |
当使用-to-last-log选项时，这个选项也会有所帮助。另外，请记住，该命令需要root权限来执行。
5 在输出中控制base-64 BINLOG
- never
- always
- decode-rows
- auto（默认）
|
1 |
$ mysqlbinlog --base64-output=never mysqld-bin.000001 |
|
1 |
BINLOG ' IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC |
|
1 |
$ mysqlbinlog --base64-output=always mysqld-bin.000001 |
|
1 2 3 4 5 6 7 8 9 10 |
BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #170726 14:59:31 server id 1 end_log_pos 182 BINLOG ' k+Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBCRUdJTg== '/*!*/; # at 182 #170726 14:59:30 server id 1 end_log_pos 291 BINLOG ' kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ== '/*!*/; # at 291 #170726 14:59:30 server id 1 end_log_pos 422 BINLOG ' kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx LTAxJyBXSEVSRSBpZCA9IDE= |
|
1 |
$ mysqlbinlog --base64-output=decode-rows --verbose mysqld-bin.000001 |
|
1 2 |
$ mysqlbinlog --base64-output=auto mysqld-bin.000001 $ mysqlbinlog mysqld-bin.000001 |
6 mysqlbinlog输出调试信息
|
1 |
$ mysqlbinlog --debug-check mysqld-bin.000001 |
|
1 2 3 4 5 6 |
$ mysqlbinlog --debug-info mysqld-bin.000001 > /tmp/m.di User time 0.00, System time 0.00 Maximum resident set size 2848, Integral resident set size 0 Non-physical pagefaults 863, Physical pagefaults 0, Swaps 0 Blocks in 0 out 48, Messages in 0 out 0, Signals 0 Voluntary context switches 1, Involuntary context switches 2 |
7 跳过前N个条目
下面将跳过指定的mysql bin日志中的前10个条目。
|
1 |
$ mysqlbinlog -o 10 mysqld-bin.000001 |
|
1 2 3 4 5 6 |
$ mysqlbinlog -o 10000 mysqld-bin.000001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; .. .. # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; |
8 保存输出到文件
|
1 |
$ mysqlbinlog mysqld-bin.000001 > output.log |
|
1 |
$ mysqlbinlog -r output.log mysqld-bin.000001 |
|
1 |
$ mysqlbinlog --server-id=1 -r output.log mysqld-bin.000001 |
9 从一个特定位置提取条目
|
1 2 3 4 5 6 7 8 |
#170726 15:38:14 server id 1 end_log_pos 15028 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1501097894/*!*/; insert into salary values(400,'Nisha','Marketing',9500) /*!*/; # at 15028 #170726 15:38:14 server id 1 end_log_pos 15146 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1501097894/*!*/; insert into salary values(500,'Randy','Technology',6000) |
|
1 |
$ mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out |
|
1 |
$ mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out |
10 将条目截止到一个特定的位置
|
1 |
$ mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out |
11 刷新日志以清除Binlog输出
|
1 |
$ mysqlbinlog mysqld-bin.000001 > output.out |
|
1 2 3 4 5 6 7 |
# head output.log /*!40019 SET @@session.max_insert_delayed_threads=0*/; .. .. # Warning: this binlog is either in use or was not closed properly. .. .. .. BINLOG ' IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC |
|
1 |
mysql> flush logs; |
12 在输出中只显示语句
也可以使用 --short-form 选项，效果相同。
|
1 2 3 |
$ mysqlbinlog -s mysqld-bin.000001 $ mysqlbinlog --short-form mysqld-bin.000001 |
|
1 2 3 4 5 6 |
SET TIMESTAMP=1501096106/*!*/; insert into employee values(400,'Nisha','Marketing',9500)/*!*/; SET TIMESTAMP=1501096106/*!*/; insert into employee values(500,'Randy','Technology',6000) .. .. |
|
1 2 |
# at 1201 #170726 15:08:26 server id 1 end_log_pos 1329 Query thread_id=3 exec_time=0 error_code=0 |
13 查看特定开始时间的条目
|
1 |
$ mysqlbinlog --start-datetime="2017-08-16 10:00:00" mysqld-bin.000001 |
14 查看特定结束时间的条目
|
1 |
$ mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001 |
15 从远程服务器获取二进制日志
此处使用-R选项。-R选项与-read-from-remote-server相同。
|
1 |
$ mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001 |
- -R 选项指示mysqlbinlog命令从远程服务器读取日志文件
- -h 指定远程服务器的ip地址
- -p 将提示输入密码。默认情况下，它将使用“root”作为用户名。也可以使用 -u 选项指定用户名。
- mysqld-bin.000001 这是在这里读到的远程服务器的二进制日志文件的名称。
下面命令与上面的命令完全相同：
|
1 |
$ mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001 |
|
1 2 |
$ mysqlbinlog -h 192.168.101.2 mysqld-bin.000001 mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2) |
|
1 2 3 |
$ mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001 ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect to this MySQL server |
|
1 2 |
$ mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001 ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES) |
|
1 |
$ mysqlbinlog -R --host=192.168.101.2 -u root -p mysqld-bin.000001<span style="text-indent: 2em;"> </span> |
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
03/05/2018 下午 5:49 沙发
222让人