基本概念
binlog是Mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句,并以"事务"的形式保存在磁盘中
使用场景:
- 复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
- 数据恢复:通过mysqlbinlog工具恢复数据
- 增量备份
开启binlog
MySQL安装完成后,MySQL5.7版本binlog默认不开启,MySQL8默认开启binlog;
登录MySQL:(docker部署的先进入docker容器)
mysql -uroot -p
查看binlog状态sql如下:
show variables like '%log_bin%';
show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------------+
如未开启binlog日志,则可按以下步骤开启binlog日志
- 修改MySQL配置文件,linux中配置文件为my.conf,window下问my.ini,下面以centos为例演示
-
编辑配置文件
vim /etc/my.cnf
-
添加配置项
log-bin=mysql-bin
server-id=1
-
重启MySQL服务
systemctl restart mysqld
进入MySQL查看binlog日志是否开启成功
binlog信息查询binlog开启后,可以在配置文件中查看其位置信息,也可以在myslq命令行中查看:
show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| 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 |
| sql_log_bin | ON |
+---------------------------------+-------------------------------------+
binlog文件开启binlog后,会在数据目录(默认)生产host-bin.n(具体binlog信息)文件及host-bin.index索引文件(记录binlog文件列表)。当binlog日志写满(binlog大小max_binlog_size,默认1G),或者数据库重启才会生产新文件,但是也可通过手工进行切换让其重新生成新的文件(flush logs);另外,如果正使用大的事务,由于一个事务不能横跨两个文件,因此也可能在binlog文件未满的情况下刷新文件
mysql> show binary logs; //查看binlog文件列表,
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 10343266 |
| mysql-bin.000004 | 10485660 |
| mysql-bin.000005 | 53177 |
| mysql-bin.000006 | 2177 |
| mysql-bin.000007 | 1383 |
+------------------+-----------+
查看binlog的状态:show master status可查看当前二进制日志文件的状态信息,显示正在写入的二进制文件,及当前position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
使用binlog日志恢复数据
当数据库发生变化时,binlog会记录数据库中的所有变化;需要恢复的时候可以根据binlog中的开始位置和结束位置还原本部分操作;结束位置一般是数据被删除之前的位置。
数据恢复
当需要恢复数据时,为了防止恢复数据后影响最新业务,需要执行flush logs,产生一个新的binlog文件,此时旧的binlog文件不会再有写入;
下面具体通过mysql-bin.000001来进行数据恢复
恢复时需要在binlog中找到两个位置:
-
数据恢复的起始位置
-
数据恢复的结束位置
如在数据准备中的drop操作,需要在binlog中找到该位置,并将该位置作为数据恢复的结束位置
通过mysqlbinlog将binlog转为sql,以方便查询具体位置
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000001 > backuptmp.sql
查看生成的backuptmp.sql,最终确定需要恢复的起始位置为219,结束位置为982
通过mysqlbinlog执行恢复操作
mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p databasename
var/lib/mysql/mysql-bin.000001 要操作binlog文件
--start-position=219 数据恢复的起始位置
--stop-position=982 数据恢复的结束位置
mysql -uroot -p123456 数据恢复需要登录数据库
使用binlog进行数据库主从复制
复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;从5.6开始复制有两种实现方式,基于binlog和基于GTID(全局事务标示符);本文接下来将介绍基于binlog的一主一从复制;其复制的基本过程如下:
a.Master将数据改变记录到二进制日志(binary log)中
b.Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容
c.Master接收到来自Slave的IO进程的请求后,负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息,返回给Slave的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
d.Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
e.Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行
接下来使用实例演示基于binlog的主从复制:
a.配置master
主要包括设置复制账号,并授予REPLICATION SLAVE权限,具体信息会存储在于master.info文件中,及开启binlog;
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
查看master当前binlogmysql状态:mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
建表插入数据:
CREATE TABLE `tb_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(36) NOT NULL,
`address` varchar(36) NOT NULL DEFAULT '',
`sex` varchar(12) NOT NULL DEFAULT 'Man' ,
`other` varchar(256) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
insert into tb_person set name="name1", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name2", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name3", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name4", address="beijing", sex="man", other="nothing";
b.配置slave
Slave的配置类似master,需额外设置relay_log参数,slave没有必要开启二进制日志,如果slave为其它slave的master,须设置bin_log
c.连接master
mysql> CHANGE MASTER TO
MASTER_HOST='10.108.111.14',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=120;
d.show slave status;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ---------------------------- slave io状态,表示还未启动
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60 ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
Master_Log_File: mysql-bin.000003 ------------ 当前读取master binlog文件
Read_Master_Log_Pos: 120 ------------------------- slave读取master binlog文件位置
Relay_Log_File: relay-bin.000001 ------------ 回放binlog
Relay_Log_Pos: 4 -------------------------- 回放relay log位置
Relay_Master_Log_File: mysql-bin.000003 ------------ 回放log对应maser binlog文件
Slave_IO_Running: No
Slave_SQL_Running: No
Exec_Master_Log_Pos: 0 --------------------------- 相对于master从库的sql线程执行到的位置
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。
e.启动复制
start slave
f.再次观察slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event -- 等待master新的event
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3469 ---------------------------- 3469 等于Exec_Master_Log_Pos,已完成回放
Relay_Log_File: relay-bin.000002 ||
Relay_Log_Pos: 1423 ||
Relay_Master_Log_File: mysql-bin.000003 ||
Slave_IO_Running: Yes ||
Slave_SQL_Running: Yes ||
Exec_Master_Log_Pos: 3469 -----------------------------3469 等于slave读取master binlog位置,已完成回放
Seconds_Behind_Master: 0
可看到slave的I/O和SQL线程都已经开始运行,而且Seconds_Behind_Master=0。Relay_Log_Pos增加,意味着一些事件被获取并执行了。
最后看下如何正确判断SLAVE的延迟情况,判定slave是否追上master的binlog:
1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异;
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话,再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异,对比SQL线程比IO线程慢了多少个binlog事件;
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样,那说明延迟可能较大,需要从MASTER上取得binlog status,判断当前的binlog和MASTER上的差距;
4、如果以上都不能发现问题,可使用pt_heartbeat工具来监控主备复制的延迟。
g.查询slave数据,主从一致
mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name | address | sex | other |
+----+-------+---------+-----+---------+
| 5 | name4 | beijing | man | nothing |
| 6 | name2 | beijing | man | nothing |
| 7 | name1 | beijing | man | nothing |
| 8 | name3 | beijing | man | nothing |
+----+-------+---------+-----+---------+
关于mysql复制的内容还有很多,比如不同的同步方式、复制格式情况下有什么区别,有什么特点,应该在什么情况下使用....这里不再一一介绍。