MySQL实现基于binlog的主从同步、主主同步

最新推荐文章于 2023-06-16 21:15:41 发布

青霄

最新推荐文章于 2023-06-16 21:15:41 发布

阅读量700

点赞数

分类专栏： mysql 文章标签： mysql主从、主主 mysql

本文链接：https://blog.csdn.net/leiwuhen92/article/details/112008062

版权

mysql 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

主机、备机关闭防火墙： systemctl stop firewalld.service

一、工作原理

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

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

主master : 10.21.144.110

从slave : 10.21.144.111

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

要想同步数据库状态，需要相同的初态，然后配置同步才有意义。 进行第一次同步之前，先手动同步一下主从服务器。

2.1 主库110操作

1、停止主库的数据更新操作
mysql> flush tables with read lock;
2、新开终端，生成主数据库的备份（导出数据库）
[root@zq ~]# mysqldump -h127.0.0.1 -uroot -pP@ssw0rd -P3306 > all_database.sql
3、将备份文件传到从库
[root@zq ~]# scp all_database.sql root@10.21.144.111:/home/testzq/
4、主库解锁
mysql> unlock tables;

2.2 从库111操作

1、停止从库slave
mysql>  stop slave;
2、导入数据
[root@zq2 ~]#  mysql -h127.0.0.1 -uroot -pP@ssw0rd -P3306 < all_databases.sql
4、查看从库已有该数据库和数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ASIA               |
| ctdi               |
| company             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5、再次启动slave
mysql> start slave ;

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

三、配置master到slave的同步（主从同步）

3.1、配置主库master

1）、授权给从数据库服务器 用户ctdidb/密码P@ssw0rd

mysql>GRANT REPLICATION SLAVE ON *.* to 'my'@'10.21.144.111' identified by 'P@ssw0rd';   # slave机器的IP地址，只允许slave机器登录
mysql> FLUSH PRIVILEGES;  #将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。

进入从slave（10.21.144.111），mysql -h10.21.144.110 -uctdidb -pP@ssw0rd看能否在在slave直接连接master中的MySQl。

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

vim /etc/my.cnf，在该配置文件[mysqld]下面添加下面内容：

[mysqld]

log-bin=/var/lib/mysql/binlog/binlog    # 开启bin-log， 存放binlog路径目录， mkdir -p /var/lib/mysql/binlog

binlog_format = mixed    # 设置bin-log日志文件格式为：MIXED，可以防止主键重复。

server-id=1   # 设置服务器ID，要唯一，为1表示主服务器

read-only = 0

expire_logs_days = 7 # 设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

log_bin_trust_function_creators=TRUE   # 若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

binlog-do-db = ASIA    # 指定mysql的binlog日志记录哪个db，要同步的数据库名、多个写多行

binlog-do-db = ctdi

binlog-do-db = company

binlog-do-db = di_task

binlog-ignore-db=mysql   # 需要忽略哪些数据库

binlog-ignore-db=sys

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-increment=2    # 有多少台服务器，n就设置为多少

auto-increment-offset=1   # 这 两个参数用于在双主（多主循环）互相备份。因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。解决这个问题的办法就是让每个数据库的自增主键不连续。上图说是，我假设需要将来可能需要2台服务器做备份，所以auto-increment-increment 设为2. 而 auto-increment-offset=1 表示这台服务器的序号。从1开始，不超过auto-increment-increment。

这样做之后，我在这台服务器上插入的第一个id就是 1，第二行的id就是 3了，而不是2. （同理，在第二台服务器上插入的第一个id就是2，第二行就是4，总之就是避免自增主键冲突）这样就不会出现主键冲突了

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

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

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

mysql> show variables like 'server_id';  # 查看server_id(值应该就是我们在my.cnf中配置的1)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show master status;  # 查看binlog的状态，记住这里的值File与Position，后续会在slave上用到
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      154 | ASIA,company,ctdi,di_task|                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

日志名为binlog.000001，偏移量为154。

主服务器已配置好

3.2、配置从库slave

1）、slave机器上，在/etc/my.cnf 添加下面配置：

[mysqld]

log-bin = /var/lib/mysql/binlog/binlog

binlog_format = mixed

server_id = 2

relay_log = /var/lib/mysql/relay-log/relay-log # 中继日志

log-slave-updates=ON # 中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。我们这里需要，所以打开。

replicate-do-db=ASIA # 指定需要复制的数据库

replicate-do-db=ctdi

replicate-do-db=company

binlog-do-db = di_task

replicate-ignore-db=mysql # 复制时需要排除的数据

replicate-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

重启服务： service mysqld restart

2）、 需要在 从库slave上 用命令来同步：

mysql> CHANGE MASTER TO MASTER_HOST = ' 10.21.144.110 ' , # master的ip

　　MASTER_PORT = 3306 ,

　　MASTER_USER = 'ctdidb ' ,

　　MASTER_PASSWORD = ' P@ssw0rd ' ,

　　MASTER_LOG_FILE = ' binlog.000001 ' ,

　　MASTER_LOG_POS = 154 ; #后面两个参数(log_file、log_pos )的值是主服务器master的 show master status\G 看到的信息

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

3）、启动slave进程

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

4）、查看slave的状态（show slave status\G;），如果下面两项值为YES，则表示配置正确：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

此时，实现了从master到slave的主从复制，接下来要实现从slave到master的同步，双向同步，即主主同步。

四、配置slave到master的同步：（主主同步）

配置从服务器到主服务器的同步，只需要将上述的配置步骤重新再反过来执行一下。

4.1、配置从库slave

1）、在slaver机器上 用户ctdidb/密码P@ssw0rd

mysql>GRANT REPLICATION SLAVE ON *.* to 'my'@'10.21.144.110' identified by 'P@ssw0rd'; # master的ip地址，只允许master机器登录
mysql> FLUSH PRIVILEGES;  # 将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。

进入主master（10.21.144.110），mysql -h10.21.144.111 -uctdidb -pP@ssw0rd看能否在在master直接连接slave中的MySQl。

2） 、在/etc/my.cnf 添加下面配置， 开启slave的binlog ：

[mysqld]

log-bin = /var/lib/mysql/binlog/binlog

binlog_format = mixed

server_id = 2

expire_logs_days = 7 # 设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

log_bin_trust_function_creators=TRUE # 若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

relay_log = /var/lib/mysql/relay-log/relay-log # 中继日志

log-slave-updates=ON # 中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。我们这里需要，所以打开。

replicate-do-db=ASIA # 指定需要复制的数据库

replicate-do-db=ctdi

replicate-do-db=company

replicate-ignore-db=mysql # 复制时需要排除的数据

replicate-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

read-only = 0

binlog-do-db = ASIA # 指定mysql的binlog日志记录哪个db，要同步的数据库名、多个写多行

binlog-do-db = ctdi

binlog-do-db = company

binlog-ignore-db=mysql # 需要忽略哪些数据库

binlog-ignore-db=sys

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-increment=2

auto-increment-offset=2

重启服务： service mysqld restart

3）、 直接记住它的master日志状态

mysql >show master statu; # 记住这里的值File与Position，后续会在master上用到

4.2、配置主库master

1）、 在/etc/my.cnf 添加下面配置，开启master的中继：

[mysqld]

log-bin=/var/lib/mysql/binlog/binlog # 存放binlog路径目录， mkdir -p /var/lib/mysql/binlog

binlog_format = mixed

server-id=1 # 设置服务器ID，要唯一，为1表示主服务器

read-only = 0

expire_logs_days = 7 # 设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

log_bin_trust_function_creators=TRUE # 若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

binlog-do-db = ASIA # 指定mysql的binlog日志记录哪个db，要同步的数据库名、多个写多行

binlog-do-db = ctdi

binlog-do-db = company

binlog-ignore-db=mysql # 需要忽略哪些数据库

binlog-ignore-db=sys

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

auto-increment-increment=2

auto-increment-offset=1

relay_log = /var/lib/mysql/relay-log/relay-log # 中继日志

log-slave-updates=ON # 中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。我们这里需要，所以打开。

replicate-do-db=ASIA # 指定需要复制的数据库

replicate-do-db=ctdi

replicate-do-db=company

replicate-ignore-db=mysql # 复制时需要排除的数据

replicate-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

重启服务： service mysqld restart

3）、在master上启动同步

mysql> CHANGE MASTER TO MASTER_HOST = ' 10.21.144.111 ' , # slave的ip

　　MASTER_PORT = 3306 ,

　　MASTER_USER = 'ctdidb ' ,

　　MASTER_PASSWORD = ' P@ssw0rd ' ,

　　MASTER_LOG_FILE = ' binlog.000001 ' ,

　　MASTER_LOG_POS=1304; #后面两个参数(log_file、log_pos)的值是从服务器slave的show master status\G看到的信息

4） 、启动slave进程

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

5）、查看slave的状态（show slave status\G;），如果下面两项值为YES，则表示配置正确：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

五、参考

https://blog.csdn.net/lzhcoder/article/details/88814364 Mysql的Binlog原理

https://www.cnblogs.com/shuidao/p/3551238.html 学一点 mysql 双机异地热备份----快速理解mysql主从，主主备份原理及实践

青霄

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL实现基于binlog的主从同步、主主同步

主机、备机关闭防火墙：systemctl stop firewalld.service一、工作原理1、主节点必须启用二进制日志，记录任何修改了数据库数据的事件；2、从节点开启一个线程（I/OThread）把自己扮演成mysql客户端，通过mysql协议，请求主节点的二进制日志文件中的事件；3、主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从...
复制链接

扫一扫