MySQL的二进制日志，MySQL复制原理，MySQL主从模式搭建，MySQL双主模式搭建，MySQL级联模式搭建，MySQL半同步模式复制
一、二进制日志
1、概念
MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。
2、二进制日志特点
1、记录导致数据改变或潜在导致数据改变的SQL语句
2、记录已提交的日志
3、不依赖于存储引擎类型
4、可通过“重放”日志文件中的事件来生成数据副本
3、二进制日志相关的服务器变量
默认服务器变量配置文件为/etc/my.cnf，可以在此文件中设置相关变量的值
（1）、sql_log_bin=ON|OFF：是否记录二进制日志，默认ON，所以不需要设置，可以在mysql运行的过程中通过修改变量参数来临时关闭记录二进制日志
mysql> set sql_log_bin=off
（2）、log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF。要想开启服务器的二进制日志功能必须设置此项，有两种设置方式
log_bin
不指定文件的位置，会默认二进制日志默认放在mysql的datadir目录中（不推荐）
log_bin=/data/mysql/logs/log_bin
指定文件的位置，生成的二进制文件就会在/data/mysql/logs/目录中，二进制文件名为log_bin.000001, log_bin.000002以此类推。数据库每次一重启服务，就会生成一个新的二进制文件，编号以此类推
（3）、max_binlog_size=1073741824：单个二进制日志文件的最大体积（单位：字节），到达最大值会自动滚动，默认为1G
（4）、binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT。建议设置为ROW，这样删除数据的时候不记录删除语句，而是删除的行每条都记录
4、二进制管理的语句
（1）、查看mariadb自行管理使用中的二进制日志文件列表，及大小，所有二进制日志
SHOW MASTER LOGS 或者 SHOW BINARY LOGS
（2）、查看使用中的二进制日志文件，当前正在使用的二进制日志
SHOW MASTER STATUS
（3）、查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
示例：SHOW BINLOG EVENTS IN 'mariadb.000002' FROM 6516 LIMIT 2,3
（4）、清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’; #不包括该文件
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
（5）、删除所有二进制日志，index文件重新记数，慎重操作
RESET MASTER
（6）、切换日志文件：会生成新的二进制文件
FLUSH LOGS;
5、mysqlbinlog命令用法
mysqlbinlog二进制日志的客户端命令工具
mysqlbinlog [OPTIONS] log_file…
--start-position= # 指定开始位置
--stop-position= # 指定结束位置
--start-datetime=
--stop-datetime=
命令用法示例
# mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
# mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
二、MySQL复制原理
主节点Master：
1、dump 线程：当从节点通过I/O线程连接主节点时，主节点会创建一个dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对bin-log加锁，当读取完成，锁会被释放。
从节点Slave：
1、I/O 线程：当从节点上执行`start slave`命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地中继日志relay-log中。
2、SQL线程：从中继日志中读取日志事件，在本地完成重放
对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。
三、主从同步方法
1、从0搭建主从模式
适用于项目未开始时，数据库刚刚安装完毕，没有任何数据情况。
假设A服务器（192.168.1.100）为主数据库，B服务器（192.168.1.200）为从数据库
主节点A配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] # 打开2进制日志选项 log_bin # 配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=100
2、登录mysql数据库，创建同步账号 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY '123456'; 3、查看二进制日志的位置节点 mysql > SHOW MASTER STATUS -> ; +--------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------+----------+--------------+------------------+ | mysql.000002 | 402 | | | +--------------+----------+--------------+------------------+
从节点B配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] # 配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=200 2、配置同步属性 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.200', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=402; 3、打开同步进程 mysql> START SLAVE 4、查看同步状态 mysql> SHOW SLAVE STATUS
2、数据库A上已有数据
此模式更为普遍，通常是原业务中已有单台数据库服务器，业务需要进行主从同步
假设A服务器（192.168.1.100）为主数据库，B服务器（192.168.1.200）为从数据库
主节点A配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] # 打开2进制日志选项 log_bin # 配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=100
2、登录mysql数据库，创建同步账号 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY '123456';
3、通过mysqldump导出数据库 # mysqldump –uroot -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >/tmp/backup.sql 4、将备份的数据库拷贝到B主机上 # scp /tmp/backup.sql 192.168.1.200:/tmp
从节点B配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] #配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=200 2、启动B主机的数据库 # systemctl start mariadb 3、导入已备份的数据库 # mysql < /tmp/backup.sql 4、查看备份到的位置 less /tmp/backup.sql 。。。。省略。。。。 CHANGE MASTER TO MASTER_LOG_FILE='mysql.000003', MASTER_LOG_POS=245; 。。。。省略。。。。 5、配置同步属性 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.200', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000003', MASTER_LOG_POS=245;
6、打开同步进程 mysql> START SLAVE
四、双主同步方法
建议从0开始配置
假设A服务器（192.168.1.100）为主数据库，B服务器（192.168.1.200）为第二主数据库
主节点A配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] #打开2进制日志选项 log_bin #配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=100 # 配置自增ID的起始点，和增长幅度 auto_increment_offset=1 auto_increment_increment=2 2、登录mysql数据库，创建同步账号 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY '123456'; 3、查看数据库当前二进制位置，给B使用的 mysql > SHOW MASTER STATUS; +--------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------+----------+--------------+------------------+ | mysql.000001 | 407 | | | +--------------+----------+--------------+------------------+ 4、配置同步属性 注意：此处的日志位置是B通过SHOW MASTER STATUS查看到的日志位置 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.200', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=407;
5、打开同步进程 mysql> START SLAVE
另一主节点B配置：
1、编辑mysql的配置文件/etc/my.cnf [mysqld] #打开2进制日志选项 log_bin #配置server_id，要求主从必须不一样，可以采用ip地址的尾号形式 server_id=200 # 配置自增ID的起始点，和增长幅度 auto_increment_offset=2 auto_increment_increment=2 2、登录mysql数据库，创建同步账号 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY '123456'; 3、查看数据库当前二进制位置，给A使用的 mysql > SHOW MASTER STATUS; +--------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------+----------+--------------+------------------+ | mysql.000001 | 407 | | | +--------------+----------+--------------+------------------+ 4、配置同步属性 注意：此处的日志位置是A通过SHOW MASTER STATUS查看到的日志位置 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000001', MASTER_LOG_POS=407;
5、打开同步进程 mysql> START SLAVE
5、级联复制方法
级联复制是将主库的数据同步到级联库，然后级联库把自己的数据同步到从库上，这样可以减少主库的压力
主库配置：
1、修改主库配置文件 # vim /etc/my.cnf server-id=100 log-bin 2、创建复制的用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY '123456'; 3、将主库的数据进行全备份 # mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql 4、把备份数据拷贝到从库 # scp /data/all.sql 192.168.1.200:/data
级联库配置：
1、修改配置文件 # vim /etc/my.cnf
server-id=200
log-bin
# 将中继日志同步到二进制日志中↓
log_slave_updates
2、修改主库的的全备份文件，在文件的首页 把 CHANGE MASTER TO MASTER_LOG_FILE='***', MASTER_LOG_POS=***;语句修改为如下。注意***的地方数据不变 # vim /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='***', MASTER_LOG_POS=***; 3、导入数据库 mysql > source /date/all.sql 4、刷新权限 mysql> FLUSH PRIVELEGES; 5、开启同步 mysql> start slave;
6、级联库数据进行全备份
mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql
7、级联库数据拷贝到从库1，和从库2
scp /data/all.sql 192.168.1.201:/data
scp /data/all.sql 192.168.1.202:/data
从库1配置：
1、修改配置文件 # vim /etc/my.cnf server-id=201 2、修改级联库的全备份文件，在文件的首页 把 CHANGE MASTER TO MASTER_LOG_FILE='***', MASTER_LOG_POS=***;语句修改为如下。注意***的地方数据不变 # vim /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.1.200', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='***', MASTER_LOG_POS=***; 3、导入数据库 mysql > source /date/all.sql 4、刷新权限 mysql> FLUSH PRIVELEGES; 5、开启同步 mysql> start slave;
从库2配置与从库1类似
6、半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主库或从库发生故障时，有可能从库没有接收到主库发送过来的binlog日志，这就会造成主库和从库的数据不一致，甚至在恢复时造成数据的丢失。
在开启了半同步复制机制后，主库只有当有任意一台从库已经接收到主库的数据后，告诉主库。主库收到从库同步成功的信息后，才继续后面的操作。
主库配置：
主数据库A（192.168.1.100），从数据库B（192.168.1.201），从数据库B（192.168.1.202）
1、修改配置文件 # vim /etc/my.cnf [mysqld] server-id=100 log-bin 2、创建同步用户账户 mysql>grant replication slave on *.* to repluser@'192.168.1.%' identified by '123456'; 3、主库全备份，并将备份数据传送给从库 # mysqldump -A --single-transaction --master-data=1 -F > /data/all.sql # scp /data/all.sql 192.168.1.201:/tmp 4、主库安装半同步模块，并开启该功能 mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql>set global rpl_semi_sync_master_enabled=on;
可以放到服务器启动参数里 # vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
5、半同步状态查询参考命令
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql>SHOW GLOBAL STATUS LIKE '%semi%';
从库B配置：
1、修改配置文件 # vim /etc/my.cnf [mysqld] server-id=201 2、修改主库的的全备份文件，在文件的首页 把 CHANGE MASTER TO MASTER_LOG_FILE='***', MASTER_LOG_POS=***;语句修改为如下。注意***的地方数据不变 # vim /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='***', MASTER_LOG_POS=***; 3、导入数据库 mysql > source /date/all.sql 4、刷新权限 mysql> FLUSH PRIVELEGES; 5、从库安装半同步模块（与主库模块不同，注意） mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1;
可以放到服务器启动参数里 # vim /etc/my.cnf rpl_semi_sync_slave_enabled=1 6、开启主从同步 mysql>start slave;
从库C配置类似