MySQL主主同步复制

by LauCyun Jun 29,2016 13:50:04 12,032 views

MySQL主主同步跟MySQL主从同步类似,只是再另一台上反向再做一次主从同步。只不过MySQL的配置文件有些地方需要注意一下。

实验环境:

  • 系统:CentOS 6.5
  • MySQL版本:MySQL 5.5.48
  • master 1:192.168.0.211
  • master 2:192.168.0.212

注意下面几点:

  • 要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。
  • 关闭selinux。
  • 同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。

1 配置master1

1.1 修改master1数据库的my.cnf文件

[root@master1 ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master 1 (192.168.0.211)
#
server-id = 1
log_bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql,information_schema
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
log-slave-updates = ON
expire_logs_days = 365
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 1

参数说明:

  • server-id:数据库唯一标识,主从的标识号绝对不能重复。
  • log_bin:开启bin-log,并指定文件目录和文件名前缀。
  • binlog-do-db:只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
  • binlog-ignore-db:不同步的数据库。如果有多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
  • replicate-do-db:需要同步的数据库。如果不指明同步那些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
  • replicate-ignore-db:不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突。
  • log-slave-updates:中继日志执行之后将变化写入自己的二进制文件。
  • expire_logs_days:日志文件过期天数,默认是 0,表示不过期。
  • sync_binlog:确保binlog日志写入后与硬盘同步。
    • 注意:

      在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

      sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于sync_binlog参数的各种设置的说明如下:

      • sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
      • sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
    • 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为sync_binlog=1的时候,是最安全但是性能损耗最大的设置。因为当设置为sync_binlog=1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

      从以往经验和相关测试来看,对于高并发事务的系统来说,sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

  • auto-increment-increment:设定为主服务器的数量,防止auto_increment字段重复。
  • auto-increment-offset:自增长字段的初始值,在多台master环境下,不会出现自增长ID重复。

1.2 设置数据同步权限

创建一个同步用的用户:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.212' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for 'sync'@'192.168.0.212';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for sync@192.168.0.212                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.212' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.3 重启mysql

[root@master1 ~]# service mysqld restart

1.4 查看master1状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

注意FilePosition项,master2需要这两项参数。

到此为止,master1配置完成,接下来就是配置master2。

2 配置master2

2.1 修改master2数据库的my.cnf文件

[root@master1 ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master 2 (192.168.0.212)
#
server-id = 2
log_bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql,information_schema
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
log-slave-updates = ON
expire_logs_days = 365
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 2

2.2 设置数据同步权限

创建一个同步用的用户:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.211' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for 'sync'@'192.168.0.211';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for sync@192.168.0.211                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.211' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.3 重启mysql

[root@master2 ~]# service mysqld restart

2.4 查看master2状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

到此为止,master2配置完成。

3 配置双主同步

设置master1从master2同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.212',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.86 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.212
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 539
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 685
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 539
              Relay_Log_Space: 1227
              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: 2
1 row in set (0.00 sec)

如出现以下两项,则说明配置成功!

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

设置master2从master1同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.211',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.40 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.211
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 187
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 333
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 187
              Relay_Log_Space: 636
              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: 1
1 row in set (0.00 sec)

如出现以下两项,则说明配置成功!

​mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

4 双主同步复制测试

4.1 新建数据库表测试

在maste1新建数据库表users

mysql> create table users(
    -> id int primary key auto_increment,
    -> name varchar(50) not null,
    -> email varchar(50)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

在msater2上验证:

mysql> use test;
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

说明同步成功!

4.2 在master1插入数据测试

在master1分别插入2条数据:

mysql> INSERT INTO users(name,email) VALUES('laucyun','liu@liuker.xyz');
Query OK, 1 row affected (0.26 sec)

mysql> SELECT * FROM users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

mysql> INSERT INTO users(name,email) VALUES('test1','test1@liuker.xyz');
Query OK, 1 row affected (0.28 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
+----+---------+------------------+
2 rows in set (0.00 sec)

在master2上验证:

mysql> SELECT * FROM users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
+----+---------+------------------+
2 rows in set (0.00 sec)

说明同步成功!

4.3 在master2插入数据测试

在master2分别插入2条数据:

mysql> INSERT INTO users(name,email) VALUES('test2','test2@liuker.xyz');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO users(name,email) VALUES('test3','test3@liuker.xyz');
Query OK, 1 row affected (0.27 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
|  4 | test2   | test2@liuker.xyz |
|  6 | test3   | test3@liuker.xyz |
+----+---------+------------------+
4 rows in set (0.00 sec)

在master1上验证:

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
|  4 | test2   | test2@liuker.xyz |
|  6 | test3   | test3@liuker.xyz |
+----+---------+------------------+
4 rows in set (0.00 sec)

说明同步成功!

现在,主主同步环境已经实现了!

Tags