Mysql 实现基于binlog的主从同步

工作原理

1、主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
2、从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件
3、主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
4、从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个，在后面详细讲解）。
5、从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次

配置mysql主从同步

准备两台测试的虚拟机，如上安装mysql环境，并开启mysql服务

主master : 192.168.8.10

从slave  :   192.168.8.11

1、配置主库：

1）、授权给从数据库服务器

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.8.11' identified by 'test123456'; mysql> FLUSH PRIVILEGES; 

2）、修改主库配置文件，开启binlog，并设置server-id，每次修改配置文件后都要重启mysql服务才会生效

vim /etc/my.cnf

在该配置文件[mysqld]下面添加下面内容：

[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=1
binlog-do-db = cmdb

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......

server-id：master端的ID号【必须是唯一的】；

log-bin：同步的日志路径及文件名，一定注意这个目录要是mysql有权限写入的（我这里是偷懒了，直接放在了下面那个datadir下面）；

binlog-do-db：要同步的数据库名

还可以显式设置不同步的数据库：

binlog-ignore-db = mysql 不同步mysql库和test库
binlog-ignore-db = test

修改配置文件后，重启服务：service mysqld restart

如果启动失败，通过cat /var/log/mysqld.log | tail -30  查看mysql启动失败的日志，从日志内容寻找解决方案。

3）、查看主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复

mysql> show master status;

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 |     1304 | cmdb         |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

主服务器已配置好。

2、配置从库

1）、理所当然也是从配置文件着手，在/etc/my.cnf 添加下面配置：

[mysqld]
server-id=2
master-host=192.168.8.10
master-user=rep1
master-password=test123456
master-port=3306
replicate-do-db=cmdb
......

# 开启中继日志
relay-log=relay-log
relay-log-index=relay-log.index
server-id=2
innodb_file_per_table=ON
skip_name_resolve=ON

重启时报错：mysqld: unknown variable ‘master-host=

说明mysql不认识这些变量，网上搜罗了一番，原因是mysql5.5+版本主从复制不支持这些变量，需要在从库上用命令来设置：

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.10',
　　MASTER_PORT=3306,
　　MASTER_USER='rep1',
　　MASTER_PASSWORD='test123456',
　　MASTER_LOG_FILE='binlog.000001',
　　MASTER_LOG_POS=1304; #后面两个参数的值与主库保持一致

PS: 最好在从服务器的my.cnf里设置read_only选项，防止发生意外（连接用户[rep1]不能有SUPER权限，否则无效）

2）、启动slave进程

mysql> slave start;
Query OK, 0 rows affected (0.04 sec)

3）、查看slave的状态，如果下面两项值为YES，则表示配置正确：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

从库正在等待主库更新数据。。。Waitin for master to send event...

三、同步主库已有数据到从库

进行第一次同步之前，先手动同步一下主从服务器

主库操作：

1、停止主库的数据更新操作

mysql> flush tables with read lock;

2、新开终端，生成主数据库的备份（导出数据库）

[root@zhoujietest ~]# mysqldump -uroot -ptest123 cmdb > cmdb.sql

3、将备份文件传到从库

[root@zhoujietest ~]# scp cmdb.sql root@192.168.8.11:/root/

4、主库解锁

mysql> unlock tables; 

从库操作：

1、停止从库slave

mysql> slave stop;

2、新建数据库cmdb

mysql> create database cmdb default charset utf8;

3、导入数据

[root@ops-dev ~]# mysql -uroot -ptest123 cmdb<cmdb.sql 

4、查看从库已有该数据库和数据 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cmdb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

 5、再次启动slave，启动的时候，记得加上skip-slave-start选项，使之不会立刻去连接master 

mysql> slave start --skip-slave-start;
[or]
mysql> start slave (可以指定线程类型：IO_THREAD ,SQL_THREAD, 如果不指定，则两个都启动。)

此时主从库的数据完全一致，如果对主库进行增删改操作，从库会自动同步进行操作。 

Reference links:

mysql主从同步配置 - 单曲荨环 - 博客园

MySQL binlog-do-db选项是危险的[转] - rorshach - 博客园 （次要）

mysql主从同步 binlog-do-db replicate-do-db_z69183787的专栏-CSDN博客_binlog-do-db

mysql参数：binlog-do-db和replicate-do-db_小濱_新浪博客 (binlog-do-db配置慎用)

实践出真章：

从理想角度看，主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。

遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：

mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

同样的操作可能需要进行多次，也可以设置自动处理此类操作，在从服务器的my.cnf里设置：

slave-skip-errors=1062

最后再唠叨一下日志的问题：时间长了，数据库服务器上的二进制文件会越来越多，清理是必要的，你可以设置自动清理，相关参数是expire_logs_days，

也可以使用手动删除的方式，但这里说的手动不是指rm，而是指PURGE BINARY LOGS，删除任何日志前，最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。

更详细的介绍参考官方文档：How to Set Up Replication，不喜欢英文的话可以看老叶同志的中文翻译。

补充：[ERROR] Error in Log_event::read_log_event(): 'Event too big'

在使用主从复制的时候，出现的问题多半是和日志（主服务器的二进制日志，从服务器的延迟日志）相关的。比如说加入你遇到了上面的错误，你可以根据错误日志的信息在主从数据库服务器上分别执行：

mysqlbinlog 日志文件 > /dev/null

查看错误，如果没有错误，则不会有任何输出，反之会输出错误信息，如果确定了错误是出现在主服务器二进制日志上，可以跳过适当的位置，再在从服务器上重新设定LOG_POS，如果确定了错误是出现在从服务器延迟日志上，则可以删除从服务器的延迟日志（使用CHANGE TO MASTER的时候，除非设定了延迟日志信息，否则会自动删除延迟日志），并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的SQL日志。

补充：配置的时候如果版本允许最好打开sync_binlog选项。

补充：有时候，从服务器延迟日志可能已经损坏，这时需要执行CHANGE MASTER TO设置新的日志文件信息，但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息，他们的含义有所不同：

Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息

从服务器需要设置的是SQL相关的日志信息：

slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;

1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.

参考链接

补充：缺省情况下，从服务器会以主机名命名延迟日志，所以一旦你修改了从服务器的主机名就会造成问题，新版MySQL会提示你这个情况：

[Warning] Neither --relay-log nor --relay-log-index were used;
so replication may break when this MySQL server acts as a slave and has his hostname changed!!
Please use '--relay-log=name-relay-bin' to avoid this problem.

补充：主库 binlog-do-db 配置，建议在没有完全测试清楚的情况下，mysql复制的几个选择性参数要慎用，因为在binlog_format [STATEMENT, ROW, MIXED]不同的情况下，会对binlog产生不同的影响，从而可能导致主从数据不一致。
如果有需要，可以使用replicate-wild-do-table和replicate-ignore-table代替。

转载自:Mysql 实现基于binlog的主从同步 - 下班闪电去打卡 - 博客园