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才会漂移到另外一台备份机上,继续提供服务。这样就不会产生主动双主同时做写操作,造成复制误差甚至失败。