本文详细介绍了MySQL的binlog日志，包括其重要性、开启方式、操作命令以及如何利用binlog进行数据恢复。binlog记录了所有DDL和DML语句，用于MySQL复制和数据恢复。文章通过实验展示了如何从binlog中恢复数据，包括全量恢复和部分恢复。
binlog 基本认识
MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
一、开启binlog日志：
vi编辑打开mysql配置文件
centos7查找mysql的配置文件my.cnf: #whereis my.cnf
在[mysqld] 区块
设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；
[root@hdp-3 ~]# whereis my.cnf
my: /etc/my.cnf
[root@hdp-3 ~]#
[root@hdp-3 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=mysql-bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
user=your_backup_user_name
password=your_backup_password重启mysqld服务使配置生效
[root@hdp-3 ~]# systemctl stop mysqld.service
[root@hdp-3 ~]# systemctl start mysqld.service二、也可登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启
登录服务器
[root@hdp-3 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>show variables like 'log_%';mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+--------------------------------+
13 rows in set (0.00 sec)注：ON表示已经开启binlog日志
三、常用binlog日志操作命令
1.查看所有binlog日志列表
show master logs;mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
3 rows in set (0.00 sec)2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
show master status;mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)注：Position = 120时，该日志为空。
3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
flush logs;mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 167 |
| mysql-bin.000004 | 120 |
+------------------+-----------+
4 rows in set (0.00 sec)注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
4.重置(清空)所有binlog日志
reset master;mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)注：不推荐这种方式，因为会把所有的binlog日志删除掉。
为了增加日志数量，增删改查一些数据；
新建数据库
CREATE SCHEMA `beautyGirl` ;新建表
CREATE TABLE `beautyGirl`.`beauty-girl` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
`age` VARCHAR(45) NULL,
`interest` VARCHAR(45) NULL,
PRIMARY KEY (`id`));插入数据
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('1', 'orange', '10', 'dance');
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('2', 'purple', '25', 'sing');
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('3', 'bluee', '18', 'photo');
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('4', 'yellow', '15', 'print');
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('5', 'red', '20', 'swim');
修改数据
UPDATE `beautyGirl`.`beauty-girl` SET `age` = '8' WHERE (`id` = '1');删除数据
delete from `beautyGirl`.`beauty-girl` where id =4;mysql> SELECT * FROM beautyGirl.`beauty-girl`;
+----+--------+------+----------+
| id | name | age | interest |
+----+--------+------+----------+
| 1 | orange | 8 | dance |
| 2 | purple | 25 | sing |
| 3 | bluee | 18 | photo |
| 5 | red | 20 | swim |
+----+--------+------+----------+
4 rows in set (0.00 sec)四、查看某个binlog日志内容，常用有两种方式：
1.使用mysqlbinlog自带查看命令法：
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中(通过ps -ef | grep mysql命令查看datadir)
[root@hdp-3 ~]# ps -ef | grep mysql
root 3257 1433 0 19:32 pts/0 00:00:00 mysql -u root -p
mysql 3709 1 0 20:11 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 3887 3709 0 20:11 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 3915 3400 0 20:15 pts/3 00:00:00 mysql -uroot -p
root 3919 3419 0 20:27 pts/2 00:00:00 grep --color=auto mysqlmysqlbinlog命令
[root@hdp-3 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.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
#191203 20:24:22 server id 1 end_log_pos 120 CRC32 0x26d1fcff Start: binlog v 4, server v 5.6.44-log created 191203 20:24:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
9lPmXQ8BAAAAdAAAAHgAAAABAAQANS42LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD2U+ZdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf/8
0SY=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;在MySQL5.5以下版本使用或者mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项
错误：mysqlbinlog: unknown variable ‘default-character-set=utf8’
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
两个方法可以解决这个问题
一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
二是用 mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 命令打开
[root@hdp-3 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002
/*!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
#191204 8:34:41 server id 1 end_log_pos 120 CRC32 0x0c3d99aa Start: binlog v 4, server v 5.6.44-log created 191204 8:34:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
If/mXQ8BAAAAdAAAAHgAAAABAAQANS42LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh/+ZdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaqZ
PQw=
'/*!*/;
# at 120
#191204 8:38:09 server id 1 end_log_pos 232 CRC32 0x4e30901a Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575419889/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE SCHEMA `beautyGirl`
/*!*/;
# at 232
#191204 8:39:19 server id 1 end_log_pos 476 CRC32 0x611361b0 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575419959/*!*/;
CREATE TABLE `beautyGirl`.`beauty-girl` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
`age` VARCHAR(45) NULL,
`interest` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
/*!*/;
# at 476
#191204 8:44:37 server id 1 end_log_pos 557 CRC32 0x2f25de19 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
BEGIN
/*!*/;
# at 557
#191204 8:44:37 server id 1 end_log_pos 743 CRC32 0xa648091e Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('1', 'orange', '10', 'dance')
/*!*/;
# at 743
#191204 8:44:37 server id 1 end_log_pos 928 CRC32 0x843d1caf Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('2', 'purple', '25', 'sing')
/*!*/;
# at 928
#191204 8:44:37 server id 1 end_log_pos 1113 CRC32 0xc3a15bc9 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('3', 'bluee', '18', 'photo')
/*!*/;
# at 1113
#191204 8:44:37 server id 1 end_log_pos 1299 CRC32 0x7db6fdfe Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('4', 'yellow', '15', 'print')
/*!*/;
# at 1299
#191204 8:44:37 server id 1 end_log_pos 1481 CRC32 0x8ef84311 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1575420277/*!*/;
INSERT INTO `beautyGirl`.`beauty-girl` (`id`, `name`, `age`, `interest`) VALUES ('5', 'red', '20', 'swim')
/*!*/;
# at 1481
#191204 8:44:37 server id 1 end_log_pos 1512 CRC32 0xce21f177 Xid = 101
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
注：一个事务执行由BEGIN开始，COMMIT结束。
| server id 1 | 数据库主机的服务号 |
| end_log_pos 1737 | pos点 |
| thread_id=5 | 线程号 |
2.show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
| IN 'log_name' |
最低0.47元/天 解锁文章
扫一扫
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
