MySQL主主复制（双主复制）配置过程介绍
发表于：2025-01-21 作者：千家信息网编辑
千家信息网最后更新 2025年01月21日，一、修改配置文件my.cnf服务器A（172.16.16.70）配置如下server_id = 70socket = /tmp/mysql.sockinnodb_buffer_pool_size =
千家信息网最后更新 2025年01月21日MySQL主主复制（双主复制）配置过程介绍一、修改配置文件my.cnf
服务器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服务器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
两台服务器都重启，使配置生效:
# service mysqld restart
说明:auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两台服务器都设定了一次自动增长值2，所以它们的起点必须要不同，这样才能避免两台服务器数据同步时出现主键冲突.
replicate-do-db 指定同步的数据库，本例为ixinnuo_sjcj库，另外，建议两台服务器的硬件配置也都一样。
二、同步数据,建立复制账号:
在服务器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、执行change master命令同步:
在服务器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服务器A上执行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服务器B上执行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在两服务器都执行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看状态:
A服务器（172.16.16.70）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 618
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服务器（172.16.16.71）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 411
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
说明已经配置成功了
测试:
在服务器A(172.16.16.70)上ixinnuo_sjcj库里创建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服务器B上查看服务器A上创建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服务器B(172.16.16.71)上ixinnuo_sjcj库里创建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服务器上A上查看服务器B上创建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
测试成功，说明MySQL主主复制配置成功.
服务器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服务器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
两台服务器都重启，使配置生效:
# service mysqld restart
说明:auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两台服务器都设定了一次自动增长值2，所以它们的起点必须要不同，这样才能避免两台服务器数据同步时出现主键冲突.
replicate-do-db 指定同步的数据库，本例为ixinnuo_sjcj库，另外，建议两台服务器的硬件配置也都一样。
二、同步数据,建立复制账号:
在服务器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、执行change master命令同步:
在服务器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服务器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服务器A上执行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服务器B上执行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在两服务器都执行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看状态:
A服务器（172.16.16.70）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 618
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服务器（172.16.16.71）状态如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 411
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
说明已经配置成功了
测试:
在服务器A(172.16.16.70)上ixinnuo_sjcj库里创建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服务器B上查看服务器A上创建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服务器B(172.16.16.71)上ixinnuo_sjcj库里创建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服务器上A上查看服务器B上创建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
测试成功，说明MySQL主主复制配置成功.
服务器
服务
配置
数据
同步
成功
状态
命令
数据库
起点
库里
增长
测试
不同
增长值
建议
文件
硬件
账号
时出
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库技术双语教材
虚拟网络技术软件定义
容错 数据库 设计
网络安全供电
崩坏三安卓服务器可以转官服吗
小度wifi连接不上服务器
怎样保证网络安全原理
金塔网络安全宣传
篆书网络安全
软件开发两种基本策略的优劣
三级计算机网络技术例题
网络安全大赛和机器人大赛
魔兽世界服务器图片
java的数据库很难学
常州java软件开发中心
计算机网络安全大实验报告
蜜汁炖鱿鱼网络安全吗
方舟以前玩的服务器找不到了
网络安全报警2
网络安全 原则 审计 漏洞
营养分析软件数据库
浙江服务器管理机房it维保
波阳软件开发技术
学习思科网络技术的收获
云服务器安全性如何
win8.1数据库
计算机网络技术维护
数据库同步会同步哪些数据
方舟以前玩的服务器找不到了
数据库从使用分为几个版本
