mysql双主集群搭建

本文介绍如何搭建两台mysql服务器，实现Dual-Master（双主）集群。至于使用方面的问题，暂不讨论。

环境介绍：

OS: CentOS 6.6 64位
ip: 192.168.1.110 192.168.1.120
mysql版本: 5.6.13

1.分别在两台服务器上安装同一版本的mysql。

安装过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 首先解压mysql安装包到/opt下
tar zxvf mysql-advanced-5.6.13-linux-glibc2.5-x86_64.tar.gz /opt/
# 建立软链接
ln -s mysql-advanced-5.6.13-linux-glibc2.5-x86_64 /usr/local/mysql
# 安装依赖包
yum -y install libaio perl
# 添加mysql用户
groupadd mysql
useradd -r -g mysql mysql
# 更改目录所属用户
chown -R mysql.mysql /usr/local/mysql
# 执行mysql安装脚本，这里的--datadir可以自定义为mysql用户可以访问的任何位置
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# 安装mysqld系统服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# 设置mysql环境变量
echo "MYSQL_HOME=/usr/local/mysql">/etc/profile.d/mysql.sh
echo 'export PATH=$PATH:$MYSQL_HOME/bin'>>/etc/profile.d/mysql.sh
source /etc/profile

2.修改my.cnf，启动mysql服务

修改如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
 # 这个必须打开
 log_bin = mysql-bin

# These are commonly set, remove the # and set as required.
 basedir = /usr/local/mysql
 datadir = /usr/local/mysql/data
 port = 3306
 # 这里的server_id需要设置成不同的值，比如192.168.110的server_id是1，192.168.1.120的server_id是2
 server_id = 1
 socket = /tmp/mysql.sock

 character_set_server = utf8
 collation_server = utf8_bin
 lower_case_table_names = 1
 innodb_file_per_table
 group_concat_max_len = -1
 skip-name-resolve
# autocommit = 0

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

启动服务

1
service mysqld start

3.设置两台服务器互相复制，即双主复制

以下操作在192.168.1.110上操作.
两条命令分别是：创建复制专用的账号，查看日志状态。

1
2
3
4
5
6
7
8
grant GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.11.120' IDENTIFIED BY 'repl';
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set:

记录下日志文件名与位置。去192.168.1.120上设置复制。
以下操作在192.168.1.120上进行

1
2
3
grant GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.11.110' IDENTIFIED BY 'repl';
change master to master_host='192.168.1.110', master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=120;
start slave;

然后通过show slave status\G查看复制进程是否成功。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.110
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 259070105
               Relay_Log_File: centos-relay-bin.000001
                Relay_Log_Pos: 120
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 259070105
              Relay_Log_Space: 259070489
              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
                  Master_UUID: 016bf7ec-38c0-11e4-ba3f-fa163e1246bf
             Master_Info_File: /usr/local/mysql-advanced-5.6.13-linux-glibc2.5-x86_64/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)

主要查看下面这两个状态是否正常，及bin-log文件名和位置是否和192.168.11.110上的show master status\G里面的数据是否一致。

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这些数据都正常，说明192.168.11.120复制192.168.11.110是ok的。
执行show master status\G获取日志相关信息供下面的复制用。

然后，回到192.168.11.110上，与之前的步骤差不多，设置192.168.11.110对192.168.11.120的复制

1
2
change master to master_host='192.168.1.120', master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=120;
start slave;

最后就是如在192.168.11.120上的操作一样检查复制是否ok了，不再赘述。

4.最后说一下有可能复制不成功的原因：

按照教程一步一步来了，还是不能成功设置复制。这里首先要检查mysql服务器防火墙是否放开3306端口，然后检查两台服务器是否可以相互ping通。基本就是网络问题，mysql在主从复制上做的还是挺好的。

5.困境中的“最佳”实践

我们项目之前是用的haproxy代理做mysql的负载均衡，请求会随机分到两台机器上，时间长了经常出现两台机器的复制进程不同步，不得不重新设置日志同步位置。后来只好做被动的Dual-Master模式了，即不再使用haproxy代理两台机器，让192.168.11.110和192.168.11.120通过keepalived使用同一个vip(虚拟ip)，外部请求通过vip来访问数据库服务。只有在其中的一台正在提供服务的机器宕机后，vip才会漂移到另外一台备份机上，继续提供服务。这样就不会产生主动双主同时做写操作，造成复制误差甚至失败。

linux, mysql