Linux系统环境
[root@mysql ~]# cat /etc/redhat-release #==》系统版本
CentOS release 6.7 (Final)
[root@mysql ~]# uname –r #==》内核版本
2.6.32-573.el6.x86_64
[root@mysql ~]# uname -m #==》系统架构
x86_64
[root@mysql ~]# echo $LANG #==》系统字符集
en_US.UTF-8
[root@mysql ~]# mysql –V #==》MySQL版本
mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1
MySQL配置文件
提示：/application/mysql是MySQL程序目录
#==》MySQL启动脚本，一般复制到/etc/init.d/mysqld
/application/mysql/support-files/mysql.server
#==》MySQL主配置文件，一 般复制到/etc/my.cnf
/application/mysql/support-files/my-small.cnf /etc/my.cnf
#==》MySQL所有二进制命令存放目录，可复制到/usr/local/sbin目录下或者添加环境变量
/application/mysql/bin/
#==》MySQL错误日志
/application/mysql/data/ MySQL01.err
#==》MySQL默认端口 3306
[root@mysql ~]# netstat -tlunp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4780/mysqld
#==》MySQL套接字文件sock
/application/mysql-5.5.32/tmp/mysql.sock
mysqlbinlog简介
mysqlbinlog命令是用来处理MySQL 数据库二进制binlog日志文件(增量日志)，binlog日志记录了MySQL数据库增删改操作记录。
mysqlbinlog语法格式
mysqlbinlog [options] log_file
mysqlbinlog [options]参数
1、-d #==》指定要查询的库
2、--no-defaults #==》屏蔽mysqlbinlog字符集报错问题
3、--start-position=N #==》从二进制binlog日志第N个位置开始
4、--stop-position=N #==》从二进制binlog日志第N个位置结束
5、--start-datetime=time #==》从二进制binlog日志某个time日期时间开始
6、--stop-datetime=time #==》从二进制binlog日志某个time日期时间结束
一、MySQL开启binlog日志功能(增加日志)
[root@mysql ~]# vim /etc/my.cnf
log-bin=mysqlbin_log
[root@mysql ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL... SUCCESS!
[root@mysql ~]# ls -l /application/mysql/data/
total 28784
-rw-rw---- 1 mysql mysql 18874368 Aug 2 09:14 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Aug 2 09:14 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jul 22 20:16 ib_logfile1
-rw-rw---- 1 mysql mysql 59 Aug 2 09:14 master.info
drwx------ 2 mysql mysql 4096 Aug 1 19:39 mysql
#==》binlog日志
-rw-rw---- 1 mysql mysql 107 Aug 2 09:14 mysqlbin_log.000001
#==》binlog索引记录
-rw-rw---- 1 mysql mysql 22 Aug 2 09:14 mysqlbin_log.index
-rw-r----- 1 mysql mysql 68426 Aug 2 09:14 mysql.err
-rw-rw---- 1 mysql mysql 6 Aug 2 09:14 mysql.pid
-rw-rw---- 1 mysql mysql 107 Aug 2 09:14 mysql-relay-bin.000001
-rw-rw---- 1 mysql mysql 25 Aug 2 09:14 mysql-relay-bin.index
drwx------ 2 mysql mysql 4096 Jul 24 15:35 performance_schema
-rw-rw---- 1 mysql mysql 51 Aug 2 09:14 relay-log.info
drwx------ 2 mysql mysql 4096 Aug 1 19:39 testdb
二、查看binlog日志（增量日志内容）
[root@mysql ~]# cat /application/mysql/data/mysqlbin_log.index
./mysqlbin_log.000001
[root@mysql ~]# mysqlbinlog /application/mysql/data/mysqlbin_log.000001
#==》如果查看时报如下错误提示可以在查看时添加—no-defaults参数屏蔽
mysqlbinlog: unknown variable 'default-character-set=utf8'
[root@mysql ~]# mysqlbinlog --no-defaults /application/mysql/data/mysqlbin_log.000001
/*!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
#190802 9:43:15 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 190802 9:43:15 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
M5VDXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAzlUNdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql ~]#
三、往表时插入数据查看binlog日志信息（增量日志）
标注：binlog日志只记录插入/更新/删除信息有记录之外其它操作都不会被记录
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> use oldboy
Database changed
mysql> insert into teacher(id,name) values(4,"老男孩"); #==》插入表数据
Query OK, 1 row affected (0.04 sec)
mysql> use testdb
Database changed
mysql> insert into student(id,name) values(10,"老男孩学生"); #==》插入表数据
Query OK, 1 row affected (0.02 sec)
mysql> select * from student;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | xiaoming |
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 6 | xiaohong |
| 10 | 老男孩学生 |
+----+-----------------+
6 rows in set (0.00 sec)
mysql> delete from student where id=6 and name='xiaohong'; #==》删除表数据
Query OK, 1 row affected (0.03 sec)
mysql> select * from student；
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | xiaoming |
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 10 | 老男孩学生 |
+----+-----------------+
5 rows in set (0.00 sec)
#==》更新表数据
mysql> update student set name='Tom' where id=1 and name='xiaoming';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Tom |
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 10 | 老男孩学生 |
+----+-----------------+
5 rows in set (0.00 sec)
[root@mysql ~]# mysqlbinlog --no-defaults /application/mysql/data/mysqlbin_log.000001
/*!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
#190802 9:43:15 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 190802 9:43:15 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
M5VDXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAzlUNdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190802 9:47:23 server id 1 end_log_pos 177 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710443/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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
#190802 9:47:23 server id 1 end_log_pos 292 Query thread_id=1 exec_time=0 error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1564710443/*!*/;
insert into teacher(id,name) values(4,"老男孩")
/*!*/;
# at 292
#190802 9:47:23 server id 1 end_log_pos 319 Xid = 6
COMMIT/*!*/;
# at 319
#190802 9:47:46 server id 1 end_log_pos 389 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710466/*!*/;
BEGIN
/*!*/;
# at 389
#190802 9:47:46 server id 1 end_log_pos 511 Query thread_id=1 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1564710466/*!*/;
insert into student(id,name) values(10,"老男孩学生")
/*!*/;
# at 511
#190802 9:47:46 server id 1 end_log_pos 538 Xid = 9
COMMIT/*!*/;
# at 538
#190802 9:55:29 server id 1 end_log_pos 608 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564710929/*!*/;
BEGIN
/*!*/;
# at 608
#190802 9:55:29 server id 1 end_log_pos 723 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564710929/*!*/;
delete from student where id=6 and name='xiaohong'
/*!*/;
# at 723
#190802 9:55:29 server id 1 end_log_pos 750 Xid = 29
COMMIT/*!*/;
# at 750
#190802 10:02:25 server id 1 end_log_pos 820 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564711345/*!*/;
BEGIN
/*!*/;
# at 820
#190802 10:02:25 server id 1 end_log_pos 945 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564711345/*!*/;
update student set name='Tom' where id=1 and name='xiaoming'
/*!*/;
# at 945
#190802 10:02:25 server id 1 end_log_pos 972 Xid = 38
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
四、查询binlog日志（增量日志）指定的库并进行恢复
[root@mysql ~]#
mysqlbinlog --no-defaults -d testdb /application/mysql/data/mysqlbin_log.000001
/*!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
#190802 9:43:15 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 190802 9:43:15 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
M5VDXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAzlUNdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190802 9:47:23 server id 1 end_log_pos 177 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710443/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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
# at 292
#190802 9:47:23 server id 1 end_log_pos 319 Xid = 6
COMMIT/*!*/;
# at 319
#190802 9:47:46 server id 1 end_log_pos 389 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710466/*!*/;
BEGIN
/*!*/;
# at 389
#190802 9:47:46 server id 1 end_log_pos 511 Query thread_id=1 exec_time=0 error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1564710466/*!*/;
insert into student(id,name) values(10,"老男孩学生")
/*!*/;
# at 511
#190802 9:47:46 server id 1 end_log_pos 538 Xid = 9
COMMIT/*!*/;
# at 538
#190802 9:55:29 server id 1 end_log_pos 608 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564710929/*!*/;
BEGIN
/*!*/;
# at 608
#190802 9:55:29 server id 1 end_log_pos 723 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564710929/*!*/;
delete from student where id=6 and name='xiaohong'
/*!*/;
# at 723
#190802 9:55:29 server id 1 end_log_pos 750 Xid = 29
COMMIT/*!*/;
# at 750
#190802 10:02:25 server id 1 end_log_pos 820 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564711345/*!*/;
BEGIN
/*!*/;
# at 820
#190802 10:02:25 server id 1 end_log_pos 945 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1564711345/*!*/;
update student set name='Tom' where id=1 and name='xiaoming'
/*!*/;
# at 945
#190802 10:02:25 server id 1 end_log_pos 972 Xid = 38
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@mysql ~]# mysql -uroot -p'123456' -e "select * from testdb.student";
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Tom |
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 10 | 老男孩学生 |
+----+-----------------+
[root@mysql ~]#
#==》把binlog 日志导二进制文件备份成普通文件
mysqlbinlog --no-defaults -d testdb /application/mysql/data/mysqlbin_log.000001 > /opt/testdb_binlog.sql
[root@mysql ~]# ls -l /opt/testdb_binlog.sql
-rw-r--r-- 1 root root 2703 Aug 2 10:13 /opt/testdb_binlog.sql
[root@mysql ~]# mysql -uroot -p'123456'
mysql> delete from student where id=10;
Query OK, 2 rows affected (0.04 sec)
mysql> insert into student(id,name) values(6,'xiaohong');
Query OK, 1 row affected (0.01 sec)
mysql> update student set name='lishi' where id=1 and name='Tom';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into student(id,name) values(15,'testshuju');
Query OK, 1 row affected (0.03 sec)
mysql> insert into student(id,name) values(16,'testshuju0001');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+---------------+
| id | name |
+----+---------------+
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 1 | xiaoming | #==》修改成Tom
| 6 | xiaohong | #==》这条记录会被删除掉
| 15 | testshuju |
| 16 | testshuju0001 |
+----+---------------+
7 rows in set (0.00 sec)
#==》把备份的binlog导入进行恢复
[root@mysql ~]# mysql -uroot -p'123456' < /opt/testdb_binlog.sql
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from testdb.student";
+----+-----------------+
| id | name |
+----+-----------------+
| 2 | oldboy |
| 3 | 张三 |
| 4 | 李四 |
| 1 | Tom | #==》xiaoming被修改成Tom
| 15 | testshuju |
| 16 | testshuju0001 |
| 10 | 老男孩学生 | #==》插入了一条数据
+----+-----------------+
五、根据位置信息进行恢复binlog日志（增量日志），精确查找建议使用此方法
[root@mysql ~]#
mysqlbinlog --no-defaults -d oldboy /application/mysql/data/mysqlbin_log.000001
# at 107 #==》以107位置开始，建议尽量往前一些
#190802 9:47:23 server id 1 end_log_pos 177 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710443/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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
#190802 9:47:23 server id 1 end_log_pos 292 Query thread_id=1 exec_time=0 error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1564710443/*!*/;
insert into teacher(id,name) values(4,"老男孩")
/*!*/;
# at 292
#190802 9:47:23 server id 1 end_log_pos 319 Xid = 6
COMMIT/*!*/;
# at 319 #==》以292位置结束，建议尽量往后一些
#190802 9:47:46 server id 1 end_log_pos 389 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710466/*!*/;
BEGIN
/*!*/;
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from oldboy.teacher";
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | yage |
| 3 | oldbzhang |
| 4 | 老男孩 |
+----+-----------+
[root@mysql ~]# mysql -uroot -p'123456' -e "use oldboy;delete from teacher where id=4 and name='老男孩'";
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from oldboy.teacher";
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | yage |
| 3 | oldbzhang |
+----+-----------+
[root@mysql ~]#
mysqlbinlog --no-defaults --start-position=107 --stop-position=319 /application/mysql/data/mysqlbin_log.000001 -r /opt/database_all_bak.sql
[root@mysql ~]# ls -l /opt/database_all_bak.sql
-rw-r--r-- 1 root root 1639 Aug 2 11:20 /opt/database_all_bak.sql
[root@mysql ~]# cat /opt/database_all_bak.sql
/*!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
#190802 9:43:15 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 190802 9:43:15 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
M5VDXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAzlUNdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190802 9:47:23 server id 1 end_log_pos 177 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1564710443/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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
#190802 9:47:23 server id 1 end_log_pos 292 Query thread_id=1 exec_time=0 error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1564710443/*!*/;
insert into teacher(id,name) values(4,"老男孩")
/*!*/;
# at 292
#190802 9:47:23 server id 1 end_log_pos 319 Xid = 6
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#==》恢复binlog日志备份
[root@mysql ~]# mysql -uroot -p'123456' -e "source /opt/database_all_bak.sql;"
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from oldboy.teacher;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | yage |
| 3 | oldbzhang |
| 4 | 老男孩 |
+----+-----------+
六、根据日期时间信息进行恢复binlog日志（增量日志），模糊查询
#==》恢复binlog日志备份
[root@mysql ~]#
mysqlbinlog --no-defaults --start-datetime=’ 2019-08-02 9:47:23’ --stop-datetime=’ 2019-08-02 9:47:46’ /application/mysql/data/mysqlbin_log.000001 -r /opt/database_all_bak.sql
#==》指定日期时间备份binlog日志
[root@mysql ~]# mysql -uroot -p'123456' -e "source /opt/database_all_bak.sql;"
网友评论