mysql双主双向数据同步配置详细教程

A数据库有变化，自动同步到B数据库

B数据库有变化，自动同步到A数据库

以下步骤两台数据库服务器都需要设置：

以某一台ip地址162.14.81.131为例

1.在服务器提供商设置页面找到“防火墙”或者“安全组”

放通3306端口和对应一台服务器ip地址

2.在宝塔面板的【安全】中添加3306端口和对应一台服务器ip地址

3.打开MySQL配置文件（my.cnf或my.ini）或者在宝塔的mysql设置中，修改配置文件里，并添加以下内容：

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog-do-db = database_name
auto_increment_offset = 1
auto_increment_increment = 2

database_name 需要同步的数据库名称

第一台数据库：

server-id = 1

auto_increment_offset = 1

第二台数据库：

server-id = 2

auto_increment_offset = 2

• auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
• auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

这样同时两台mysql更新就不会导致如下的自增id冲突：

Last_SQL_Error: Could not execute Write_rows event on table www_77nn_net.wp_options; Duplicate entry ‘_transient_doing_cron’ for key ‘option_name’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000001, end_log_pos 350

完成修改后需要重启mysql

4.使用root账号登录mysql数据库（账号root、密码password）

mysql -uroot -ppassword

5.创建一个复制的账号，以账号www_77nn_net，密码password为例

CREATE USER 'www_77nn_net'@'%' IDENTIFIED BY 'password'; 
GRANT REPLICATION SLAVE ON *.* TO 'www_77nn_net'@'%';

刷新权限

FLUSH PRIVILEGES;

查看远程mysql连接是否成功：

mysql -u[用户名] -p[密码] -h[远程mysql服务器ip地址]

mysql -uwww_77nn_net -ppassword -h162.14.81.131

6.运行一下命令查看当前的二进制日志文件和位置：

SHOW MASTER STATUS;

7.上图的file和position值填入到下面的配置中

change master to master_host='162.14.72.131',
master_port=3306,
master_user='www_77nn_net',
master_password='password',
master_log_file='mysql-bin.000010', 
master_log_pos=1120;

8.启动复制

start slave;

9.使用以下命令来检查复制状态：

SHOW SLAVE STATUS\G

两台mysql服务器的Slave_IO_Running和Slave_SQL_Running都是Yes，这表明数据同步正常。