MySQL 5.6 一主多从的 半同步复制搭建
1
2
3
4
|
# yum -y groupinstall "Perl Support"
# yum -y instdall vim wget
# cd /usr/local/src
# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz
|
1
2
3
4
|
# mkdir -pv /mydata/data
# groupadd -r mysql
# useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql
# chown -R mysql:mysql /mydata/data
|
1
2
3
4
5
6
7
|
# tar zxvf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz -C /usr/local
# cd /usr/local/
方法一(推荐)
mv mysql-5.6.24-linux-glibc2.5-x86_64 mysql
方法二(不建议)
# ln -sv mysql-5.6.24-linux-glibc2.5-x86_64 mysql
# cd mysql
# chown -R mysql:mysql .
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
# chown -R root .
|
1
2
|
# cd /usr/local/mysql
# cp support-files/my-default.cnf /etc/my.cnf
|
1
2
3
4
|
# cd /usr/local/mysql
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld //添加至服务列表
# chkconfig mysqld on
|
1
|
MANPATH /usr/local/mysql/man
|
1
|
# ln -sv /usr/local/mysql/include /usr/include/mysql
|
1
2
|
# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
# ldconfig //让系统重新载入系统库
|
1
2
|
# echo "export PATH=$PATH:/usr/local/mysql/bin " >/etc/profile.d/mysql.sh
# source /etc/profile.d/mysql.sh
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
[mysqld]
binlog-format=ROW
log
-bin=master-bin
log
-slave-updates=
true
gtid-mode=on
enforce-gtid-consistency=
true
master-info-repository=TABLE
relay-
log
-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1 #改成主机的ID
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master #改成自己的主机名
rpl_semi_sync_master_enabled=ON
|
禁用 rpl_semi_sync_master_enabled=ON 参数不然启动报错
注意:添加
上面的配置文件都可以不要,对于新手来说,看不懂,就很容易出错,新手朋友照着这个方案搭建起来也不能主从复制
选填下面的my.cnf配置
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /tmp
slave-load-tmpdir = /tmp
pid-file = /data/mysql/mysql.pid
#skip-name-resolve
##skip-symbolic-links
#replicate-wild-ignore-table = mysql.%
#replicate-wild-ignore-table = test.%
#log-bin-index = mysql-bin.index
#relay-log-index = relay-log.index
max_connect_errors = 10000
max_connections = 500
wait-timeout = 30
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#new add DBA
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
#report-h
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /tmp
slave-load-tmpdir = /tmp
pid-file = /data/mysql/mysql.pid
#skip-name-resolve
##skip-symbolic-links
#replicate-wild-ignore-table = mysql.%
#replicate-wild-ignore-table = test.%
#log-bin-index = mysql-bin.index
#relay-log-index = relay-log.index
max_connect_errors = 10000
max_connections = 500
wait-timeout = 30
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#new add DBA
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
#report-h
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr | | |
| datadir | /var/lib/mysql/ | |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr | | |
| datadir | /var/lib/mysql/ | |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
binlog-format:二进制日志的格式,有row、statement和mixed三种类型;当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;
log
-bin: 二进制日志存名,可指定存放路径;
log
-slave-updates:slave更新时是否记录到日志中;
gtid-mode:指定GTID的类型,否则就是普通的复制架构;
enforce-gtid-consistency:是否强制GTID的一致性;
master-info-repository和relay-
log
-info-repository:启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能;
binlog-rows-query-
log
-events:启用之可用于在二进制日志详细记录事件相关的信息,可降低故障排除的复杂度;
server-id:同一个复制拓扑中的所有服务器的id号必须惟一;
report-port:产生复制报告时在哪个端口上提供相关功能;
report-host:产生复制报告时在哪个主机上提供相关功能,一般为自己的主机名;
sync-master-info:启用之可确保服务器崩溃时无信息丢失;
slave-paralles-workers:设定从服务器启动几个SQL复制线程数;0表示关闭多线程复制功能;数字太大也无意义最好与要复制的数据库的数目相同
binlog-checksum:复制时是否校验二进制文件的完整性等相关功能;binlog的校验格式校验算法(CRC32:循环冗余校验码32位);
datadir:数据文件存放路径;
master-verify-checksum:检验主服务器二进制日志的相关功能;
slave-sql-verify-checksum:校验从服务器中继日志的相关功能的;
rpl_semi_sync_master_enabled:是否开启半同步复制;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[mysqld]
binlog-format=ROW
log
-slave-updates=
true
gtid-mode=on
enforce-gtid-consistency=
true
master-info-repository=TABLE
relay-
log
-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11 #改成主机的ID
report-port=3306
port=3306
log
-bin=mysql-bin.
log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave1 #改成自己的主机名
rpl_semi_sync_slave_enabled=ON
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[mysqld]
binlog-format=ROW
log
-slave-updates=
true
gtid-mode=on
enforce-gtid-consistency=
true
master-info-repository=TABLE
relay-
log
-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=12 #改成主机的ID
report-port=3306
port=3306
log
-bin=mysql-bin.
log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave2 #改成自己的主机名
rpl_semi_sync_slave_enabled=ON
|
1
2
3
|
mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.201(备库IP) IDENTIFIED BY
'passwd'
;
mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.202(备库IP) IDENTIFIED BY
'passwd'
;
mysql> FLUSH PRIVILEGES;
|
1
|
master> INSTALL PLUGIN rpl_semi_sync_master SONAME
'semisync_master.so'
;
|
1
|
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME
'semisync_slave.so'
;
|
1
2
3
|
mysql> CHANGE MASTER TO MASTER_HOST=
'192.168.1.152(
主库IP
)'
,MASTER_USER=
'slave'
, MASTER_PASSWORD=
'passwd'
, MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> show global variables like
'%gtid%'
;
|
1
2
3
4
5
6
7
8
|
mysql> show slave hosts;
+-----------+--------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------+------+-----------+--------------------------------------+
| 11 | slave1 | 3306 | 1 | 9cb0c60a-9676-11e4-9d56-000c2989857d |
| 12 | slave2 | 3306 | 1 | 485767ea-9676-11e4-9d54-005056291b33 |
+-----------+--------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
|
1
|
mysql> show slave status\G;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> create database just_for_test;
Query OK, 1 row affected (0.15 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| just_for_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
|
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
|
slave1> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| just_for_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)
slave1> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.1.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 360
Relay_Log_File: slave1-relay-bin.000005
Relay_Log_Pos: 572
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库2
slave2> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| just_for_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.11 sec)
slave2> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.1.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 360
Relay_Log_File: slave2-relay-bin.000003
Relay_Log_Pos: 572
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
|
1
2
3
4
|
# mkdir /usr/local/mysql/ssl
# cd /usr/local/mysql/ssl
# umask 077 openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
|
1
2
3
|
# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout master-key.pem -out master-req.pem
# openssl rsa -in master-key.pem -out master-key.pem
# openssl x509 -req -in master-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out master-cert.pem
|
1
2
|
# chown mysql.mysql -R *
# chmod 600 *
|
1
2
|
# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout slave-key.pem -out slave-req.pem
# openssl x509 -req -in slave-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out slave-cert.pem
|
1
2
|
# scp ca-cert.pem slave-key.pem slave-cert.pem master-cert.pem master-key.pem 192.168.1.201:/usr/local/mysql/ssl/
# scp ca-cert.pem slave-key.pem slave-cert.pem master-cert.pem master-key.pem 192.168.1.202:/usr/local/mysql/ssl/
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# vim /etc/my.cnf
//添加内容如下
ssl
ssl-ca = /usr/local/mysql/ssl/ca-cert.pem
ssl-cert = /usr/local/mysql/ssl/master-cert.pem
ssl-key = /usr/local/mysql/ssl/master-key.pem
//更改证书属主属组
# chown mysql.mysql -R /usr/local/mysql/ssl
# chmod 600 * /usr/local/mysql/ssl
//重启mysql服务
# service mysqld restart
//在主服务器上创建ssl连接用户
mysql> grant replication client,replication slave on*.* to
"ssl"
@
"192.168.1.201"
identified by
'mysql'
;
mysql> grant replication client,replication slave on*.* to
"ssl"
@
"192.168.1.202"
identified by
'mysql'
;
mysql> flush privileges;
|
1
|
# mkdir /usr/local/mysql/ssl
|
1
2
3
4
5
6
7
8
|
# vim /etc/my.cnf
//添加内容如下
ssl
ssl-ca = /usr/local/mysql/ssl/ca-cert.pem
ssl-cert = /usr/local/mysql/ssl/slave-cert.pem
ssl-key = /usr/local/mysql/ssl/slave-key.pem
//重启mysql服务
# service mysqld restart
|
1
2
3
4
|
mysql > stop slave;
mysql > change master to master_host=
'192.168.1.152'
, master_user=
'ssl'
,master_password=
'mysql'
,MASTER_AUTO_POSITION=1,master_ssl=1,master_ssl_ca=
'/usr/local/mysql/ssl/ca-cert.pem'
,master_ssl_cert=
'/usr/local/mysql/ssl/master-cert.pem'
,master_ssl_key=
'/usr/local/mysql/ssl/master-key.pem'
;
mysql> start slave;
mysql> 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
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.1.152
Master_User: ssl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 872
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 411
Relay_Master_Log_File: master-bin.000003
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: 872
Relay_Log_Space: 616
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/master-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/master-key.pem
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: 963e99b9-9674-11e4-9d49-000c297410f1
Master_Info_File: mysql.slave_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: 963e99b9-9674-11e4-9d49-000c297410f1:1-6
Auto_Position: 1
|