mysql互为主从

https://wen.lu/#q=mysql+%E4%BA%92%E4%B8%BA%E4%B8%BB%E4%BB%8E

http://svenman.blog.51cto.com/6867097/1363110

一、A、B双主模型的实现条件：

1. 开启二进制日志

2. 开启中继日志

3. 解决自动增长列的问题

如果A服务器上自动增长的列编号有一个35，此时还没有同步到B服务器上，在B服务器上插入一条数据，编号也是35。当同步A的35到B服务器上来的话，必然产生数据丢失。

解决办法：

让在A上插入的行的自动增长都为奇数，让B服务器上的自动增长都为偶数。这样就解决了自动增长的问题。

假设A是一台生产环境中的数据库，现在想添加B服务器,实现双主模型。

二、步骤：

1. 在A、B服务器上创建具有复制权限的帐号

2. 在A、B服务器上修改配置文件（开启二进制日志、中继日志等）

3. 将A服务器上存在的数据文件导入到B服务器中

注意：导入数据的时候，先关闭B服务器的二进制日志。

4. 让B先成为slave，再让A成为slave

5. 测试

三、开始配置

1. 创建授权用户

mysql> grant replication slave on *.* to 'slave'@'192.168.2.96' identified by '12345';
Query OK, 0 rows affected (0.00 sec)
#A服务器
mysql> grant replication slave on *.* to 'slave'@'192.168.2.93' identified by '12345';
Query OK, 0 rows affected (0.00 sec)
#B服务器

2. 编辑配置文件

[root@oracle ~]# vim /etc/my.cnf
log-bin=mysql-bin                 #开启二进制日志
server-id=1
relay-log=mysql-relay-bin         #开启中继日志
log_slave_updates = on            #从服务器将时间记录到二进制日志中
auto_increment_increment=2        #自动增长的步长
auto_increment_offset=1           #自动增长的起始数值
#A服务器的配置

[root@node2 ~]# vim /etc/my.cnf
server-id=2
#log-bin=mysql-bin
#log_slave_updates = on
auto_increment_increment=2
auto_increment_offset=2
#B服务器的配置，先关闭二进制日志

重启服务

[root@oracle ~]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

创建测试用的表

mysql> select * from info;
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|   1 | zhang |  23 |
|   2 | li    |  12 |
|   3 | cheng |  34 |
|   4 | wang  |  22 |
|   5 | chen  |  44 |
+-----+-------+-----+
5 rows in set (0.00 sec)
#在A服务器上创建测试用的表

3. 将表导入到B服务器上

[root@oracle ~]# mysqldump --databases data --lock-all-tables --master-data=2 > /root/dump.sql
[root@oracle ~]# scp /root/dump.sql root@192.168.2.96:/root/
#A服务器上dump+拷贝
[root@node2 ~]# mysql < dump.sql     #B服务器上执行

4. 让B服务器先成为slave

mysql> change master to master_host='192.168.2.93',master_user='slave',master_password='12345',master_port=3306,MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1068;
#MASTER_LOG_FILE和MASTER_LOG_POS在dump.sql中有记录

[root@node2 ~]# vim /etc/my.cnf
#将刚刚注释掉的参数生效
log-bin=mysql-bin
log_slave_updates = on

[root@node2 ~]# service mysqld restart
Shutting down MySQL..                                      [确定]
Starting MySQL.                                            [确定]

再让A服务器成为slave

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#在B上查看二进制日志信息
mysql> change master to master_host='192.168.2.96',master_user='slave',master_password='12345',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
mysql> start slave;

查看A、B服务器的状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.96
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#A服务器上的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.93
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#在B服务器上查看状态

5. 测试：

mysql> insert into info(name,age) values('sun',25),('ding',29);
#在A服务器上插入2行数据
mysql> select * from info;
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|   1 | zhang |  23 |
|   2 | li    |  12 |
|   3 | cheng |  34 |
|   4 | wang  |  22 |
|   5 | chen  |  44 |
|   7 | sun   |  25 |
|   9 | ding  |  29 |
+-----+-------+-----+
7 rows in set (0.00 sec)
#在B服务器上查看的结果

mysql> insert into info(name,age) values('BB',33),('BC',21);
#在B服务器上插入2行数据
mysql> select * from info;
+-----+-------+-----+
| sid | name  | age |
+-----+-------+-----+
|   1 | zhang |  23 |
|   2 | li    |  12 |
|   3 | cheng |  34 |
|   4 | wang  |  22 |
|   5 | chen  |  44 |
|   7 | sun   |  25 |
|   9 | ding  |  29 |
|  10 | BB    |  33 |
|  12 | BC    |  21 |
+-----+-------+-----+
9 rows in set (0.00 sec)
#在A服务器上查看

这样双主模型就已经实现了。

本文出自 “My favorite technology” 博客，请务必保留此出处http://svenman.blog.51cto.com/6867097/1363110

http://www.52youpiao.com/it/post/mysql-slave.html

项目要求

两台服务器互为主从，双向同步数据。当一台服务器上的数据有增删改等操作时，另一台服务器上的mysql同步的数据库也有同样的操作。利用MySQL主从同步原理，实现MySQL双向同步。

实施环境

服务器为windows 2008 R2，mysql版本为5.5.30，服务器server1：192.168.0.165 server2：192.168.0.166

1、配置数据库my.ini文件

修改server1：192.168.0.165的my.ini文件，增加以下内容：

server-id=1
binlog-do-db=ctbsdb
binlog-ignore-db=mysql
log-slave-updates
replicate-do-db=ctbsdb
replicate-ignore-db=mysql
log-bin=mysql-bin

auto_increment_offset=1
auto_increment_increment=2

修改server2：192.168.0.166的my.ini文件，增加以下内容：

server-id=2
binlog-do-db=ctbsdb
binlog-ignore-db=mysql
log-slave-updates
replicate-do-db=ctbsdb
replicate-ignore-db=mysql
log-bin=mysql-bin

auto_increment_offset=2
auto_increment_increment=2

重启两台机器的mysql服务。

配置项释义

server-id：标识，唯一，值范围在：1至2^23-1

binlog-do-db和replicate-to-db：要同步的数据库，多个需要同步的数据可以继续向下写

binlog-ignore-db和replicate-ignore-db：不需要同步的数据库

log-bin：配置是否在数据库有变动时写二进制日志

auto_increment_offset和auto_increment_increment：当同步断开，两台服务器分别有新数据进入，那么主键ID是自增长列会出现冲突的情况，会导致同步无法继续。加上上面两个设置后server1的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数，server2的为偶数。

2、在两台机器上新增同步用户

在两台服务器的mysql用户里新增用户名为slaveuser，密码为123456的同步用户。

在192.168.0.165上运行
grant replication slave on *.* to slaveuser@192.168.0.166 identified by '123456';

在192.168.0.166上运行
grant replication slave on *.* to slaveuser@192.168.0.165 identified by '123456';

3、设置主机并启动从服务器线程

在服务器server1：192.168.0.165上用MySQL命令行工具执行如下语句：

mysql> show master status;

得到的结果如下

+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.000020 | 107 | ctbsdb | mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.00 sec)

再到服务器server2：192.168.0.166上打开mysql命令

mysql> stop slave;

mysql> change master to master_host='192.168.0.165',master_user='slaveuser',master_password='123456',master_port=3306,master_log_file='mysql-bin.000020',master_log_pos=107;

mysql> start slave;

mysql> show slave status\G

查看如下两项，得到的结果为Yes即表示正常。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

操作到此步即已完成两台服务器的主从同步，接下来是把server1作为从，server2作为主服务器来设置。

4、执行与第三步同样的操作，只是机器反过来

在服务器server2：192.168.0.166上用MySQL命令行工具执行如下语句：

mysql> show master status;

得到如下结果

+---------------------+------------+---------------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+---------------------+--------------------------+
| mysql-bin.000004 | 38026 | ctbsdb | mysql |
+---------------------+------------+---------------------+--------------------------+
1 row in set (0.00 sec)

再到服务器192.168.0.165上打开mysql命令

mysql> stop slave;

mysql> change master to master_host='192.168.0.166',master_user='slaveuser',master_password='123456',master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=38026;

mysql> start slave;

mysql> show slave status\G

同样查看slave-io-running与slave-sql-running的结果是否为yes

做完上面的步骤，即完成两台服务器mysql数据库的主主同步。

写在最后

如果只是想完成主从同步，做到第三步就可以了，而且my.ini的配置文件也不用加上auto_increment_offset和auto_increment_increment，网上有很多资料也介绍了主主同步，但没写清楚所用的mysql使用的是什么版本。mysql5.5以后就在配置文件里不支持写master-host和master-user等方式了，如果my.ini配置文件没有写正确，重启mysql服务是不能正常启动的。所以在配置之前先看清楚使用的mysql是什么版本。