笺注：MySQL的安装可参考 LNMP一键安装包（lnmp_CentOS6.9)
查看MySQL的版本信息：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -V
mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
查看MySQL的主配置文件：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# cat /etc/my.cnf |grep -v ^# |grep -v ^$
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
max_connections = 1000
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
笺注：
log-bin=mysql-bin 代表开启binlog日志
binlog日志文件命名规则的前缀为 mysql-bin
服务器本地登录MySQL数据库：（使用的是数据库管理员root@localhost，密码888）
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -uroot -p888
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3821
Server version: 5.5.48-log Source distribution
Copyright (c) 2000, 2016, 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>
查看binlog日志是否已经开启：
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
3 rows in set (0.00 sec)
mysql>
注释：
log_bin：ON 代表binlog日志已经开启了
查看binlog日志文件的存放目录：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# mysql -uroot -p888 -e"show variables like '%datadir%';"
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| datadir | /usr/local/mysql/var/ |
+---------------+-----------------------+
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
binlog日志文件：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# cd /usr/local/mysql/var
[root@iZj6c1a39n0ss415rjbuoqZ var]# ls
discuz iZj6c1a39n0ss415rjbuoqZ.err mysql-bin.001281 mysql-bin.001285 mysql-bin.001289 performance_schema
ibdata1 iZj6c1a39n0ss415rjbuoqZ.log mysql-bin.001282 mysql-bin.001286 mysql-bin.001290 wordpress
ib_logfile0 iZj6c1a39n0ss415rjbuoqZ.pid mysql-bin.001283 mysql-bin.001287 mysql-bin.001291 zabbix
ib_logfile1 mysql mysql-bin.001284 mysql-bin.001288 mysql-bin.index
[root@iZj6c1a39n0ss415rjbuoqZ var]#
注释：
有三个自定义创建的库 discuz、wordpress、zabbix
服务器的主机名为 iZj6c1a39n0ss415rjbuoqZ
会保留一定天数的日志文件；
递增数值越大代表越新：
[root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001291
26M mysql-bin.001291
[root@iZj6c1a39n0ss415rjbuoqZ var]#
[root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001290
80M mysql-bin.001290
[root@iZj6c1a39n0ss415rjbuoqZ var]#
[root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001289
79M mysql-bin.001289
[root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001288
79M mysql-bin.001288
[root@iZj6c1a39n0ss415rjbuoqZ var]# du -sh mysql-bin.001287
78M mysql-bin.001287
[root@iZj6c1a39n0ss415rjbuoqZ var]#
笺注：
binlog日志文件并非只有一个文件，而是会产生很多，太久的会被自动删除；
binlog日志文件的命名规则: 前缀 + 递增数值
服务器本地登录MySQL数据库后，查看最新的binlog日志文件“mysql-bin.001291”的前十条操作记录：
mysql> show binlog Events in 'mysql-bin.001291' LIMIT 0,10;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.001291 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.48-log, Binlog ver: 4 |
| mysql-bin.001291 | 107 | Query | 1 | 177 | BEGIN |
| mysql-bin.001291 | 177 | Query | 1 | 300 | use `zabbix`; delete from event_suppress where suppress_until<1671559718 |
| mysql-bin.001291 | 300 | Query | 1 | 371 | COMMIT |
| mysql-bin.001291 | 371 | Query | 1 | 441 | BEGIN |
| mysql-bin.001291 | 441 | Query | 1 | 632 | use `zabbix`; insert into history (itemid,clock,ns,value) values (23259,1671559719,183821990,0.000000),(30459,1671559719,195618016,0.010747) |
| mysql-bin.001291 | 632 | Xid | 1 | 659 | COMMIT /* xid=146 */ |
| mysql-bin.001291 | 659 | Query | 1 | 729 | BEGIN |
| mysql-bin.001291 | 729 | Query | 1 | 854 | use `zabbix`; delete from task where status in (3,4) and clock<=1671473320 |
| mysql-bin.001291 | 854 | Query | 1 | 925 | COMMIT |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
mysql>
注释：
可以看到，命令“use `zabbix`; delete from task where status in (3,4) and clock<=1671473320”的Pos为729 ，End_log_pos为854
在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix的操作日志，并输出到屏幕上：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# find / -name mysqlbinlog
/usr/local/mysql/bin/mysqlbinlog
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 2:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |more
# at 729
#221221 2:08:40 server id 1 end_log_pos 854 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1671559720/*!*/;
delete from task where status in (3,4) and clock<=1671473320
/*!*/;
注释：
可以看到，命令“use `zabbix`; delete from task where status in (3,4) and clock<=1671473320”的执行时间为 2022-12-21 02:08:40
######
创建测试的表和数据：
mysql> use zabbix;
Database changed
mysql> CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into table_1(name) values('zhuohua');
Query OK, 1 row affected (0.01 sec)
mysql> insert into table_1(name) values('Python');
Query OK, 1 row affected (0.00 sec)
mysql> insert into table_1(name) values('Mary');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from table_1;
+----+---------+
| id | name |
+----+---------+
| 1 | zhuohua |
| 2 | Python |
| 3 | Mary |
+----+---------+
3 rows in set (0.00 sec)
mysql>
###
在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix、表为table_1的操作日志，并输出到屏幕上：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |grep table_1
CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`))
insert into table_1(name) values('zhuohua')
insert into table_1(name) values('Python')
insert into table_1(name) values('Mary')
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
###
删除数据：
mysql> use zabbix;
Database changed
mysql>
mysql> delete from table_1 where name = 'Python';
Query OK, 1 row affected (0.01 sec)
mysql> select * from table_1;
+----+---------+
| id | name |
+----+---------+
| 1 | zhuohua |
| 3 | Mary |
+----+---------+
2 rows in set (0.00 sec)
mysql>
在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix、表为table_1的操作日志，并写入到文件a.txt中：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 |grep table_1 > a.txt
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
[root@iZj6c1a39n0ss415rjbuoqZ ~]# cat a.txt
CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`))
insert into table_1(name) values('zhuohua')
insert into table_1(name) values('Python')
insert into table_1(name) values('Mary')
delete from table_1 where name = 'Python'
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
###
在日志文件“mysql-bin.001291”中，查询2022-12-21 02:08:00到2022-12-21 20:30:00之间，库为zabbix的操作日志，并写入到文件1.txt中：
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --database=zabbix --start-datetime="2022-12-21 02:08:00" --stop-datetime="2022-12-21 20:30:00" /usr/local/mysql/var/mysql-bin.001291 > 1.txt
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
查看文件1.txt：
# at 33976713
#221221 12:13:29 server id 1 end_log_pos 33976884 Query thread_id=3821 exec_time=0 error_code=0
SET TIMESTAMP=1671596009/*!*/;
CREATE TABLE table_1 (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`))
/*!*/;
# at 34045288
#221221 12:14:27 server id 1 end_log_pos 34045396 Query thread_id=3821 exec_time=0 error_code=0
SET TIMESTAMP=1671596067/*!*/;
insert into table_1(name) values('zhuohua')
/*!*/;
# at 34050885
#221221 12:14:41 server id 1 end_log_pos 34050992 Query thread_id=3821 exec_time=0 error_code=0
SET TIMESTAMP=1671596081/*!*/;
insert into table_1(name) values('Python')
/*!*/;
# at 34054842
#221221 12:14:49 server id 1 end_log_pos 34054947 Query thread_id=3821 exec_time=0 error_code=0
SET TIMESTAMP=1671596089/*!*/;
insert into table_1(name) values('Mary')
/*!*/;
# at 34263682
#221221 12:18:35 server id 1 end_log_pos 34263788 Query thread_id=3821 exec_time=0 error_code=0
SET TIMESTAMP=1671596315/*!*/;
delete from table_1 where name = 'Python'
/*!*/;
#####
从日志文件/usr/local/mysql/var/mysql-bin.001291中，重新对库zabbix执行Pos为34050885，End_log_pos为34050992之间的命令：（等于重新在库zabbix中执行命令“insert into table_1(name) values('Python')”）
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --database=zabbix /usr/local/mysql/var/mysql-bin.001291 --start-position 34050885 --stop-position 34050992 | mysql -uroot -p888
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
删除的数据恢复成功：
mysql> use zabbix;
Database changed
mysql>
mysql> select * from table_1;
+----+---------+
| id | name |
+----+---------+
| 1 | zhuohua |
| 3 | Mary |
| 4 | Python |
+----+---------+
3 rows in set (0.00 sec)
mysql>
#####
从日志文件/usr/local/mysql/var/mysql-bin.001291中，重新对库zabbix执行Pos为34263682，End_log_pos为34263788之间的命令：（等于重新在库zabbix中执行命令“delete from table_1 where name = 'Python'”）
[root@iZj6c1a39n0ss415rjbuoqZ ~]# /usr/local/mysql/bin/mysqlbinlog --database=zabbix /usr/local/mysql/var/mysql-bin.001291 --start-position 34263682 --stop-position 34263788 | mysql -uroot -p888
[root@iZj6c1a39n0ss415rjbuoqZ ~]#
效果如下：
mysql> use zabbix;
Database changed
mysql>
mysql> select * from table_1;
+----+---------+
| id | name |
+----+---------+
| 1 | zhuohua |
| 3 | Mary |
+----+---------+
2 rows in set (0.00 sec)
mysql> |
