一，Mysql复制概述
Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。
1.复制能解决的问题
数据分布(多个地区的数据分发)
负载均衡(读写分离)
备份
高可用和故障切换的核心功能
测试mysql升级
2.复制的原理
mysql复制的原理现阶段都是一样的，master将操作记录到bin-log中，slave的一个线程去master读取bin-log，并将他们保存到relay-log中，slave的另外一个线程去重放relay-log中的操作来实现和master数据同步。
3.复制的历史
mysql-3.2 开始支持基于命令的复制，也就是statement-based replication。mysql-5.1 开始支持基于行的复制和混合复制，也就是row-based replication和mixed-based relication，mysql-5.5 开始支持semi-synchronous的复制，也叫半同步复制，目的在于事务环境下保持主从一致，mysql-5.6 开始支持延时复制。
下面是复制的基本原理：
二，配置主从复制
为了演示方便，实验使用通用的二进制包来安装mysql:
mysql版本：mysql-5.5.33-linux2.6-x86_64
OS版本：Centos 6.4 x86:64
首先来安装master服务器
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[root@localhost ~]
# useradd -r -u 120 mysql
[root@localhost ~]
# tar zxvf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@localhost
local
]
# ln -s mysql-5.5.33-linux2.6-x86_64 mysql
[root@localhost
local
]
# cd mysql
[root@localhost mysql]
# mkdir /mydata/data -p
[root@localhost mysql]
# chown -R root.mysql ./*
[root@localhost mysql]
# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data
[root@localhost mysql]
# cp support-files/my-large.cnf /etc/my.cnf
[root@localhost mysql]
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@localhost mysql]
# chmod +x /etc/init.d/mysqld
[root@localhost mysql]
# chkconfig --add mysqld
[root@localhost mysql]
# chkconfig mysqld on
[root@localhost mysql]
# vi /etc/my.cnf
# 在mysqld中添加如下俩行
datadir=
/mydata/data
innodb_file_per_table=1
[root@localhost mysql]
# service mysqld start
Starting MySQL...... SUCCESS!
|
在从服务器上执行上面相同的操作
在master服务器上启用二进制日志,设置server-id（必须保证和从服务器不同）
|
1
2
3
4
|
[root@localhost mysql]
# vi /etc/my.cnf
log-bin =
/mydata/data/master-bin
log-bin-index =
/mydata/data/master-bin
.index
server-
id
= 1
|
创建具体复制权限的用户
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@localhost ~]
# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> grant replication slave,replication client on *.* to
'repl'
@
'192.168.30.%'
identified by
'123456'
;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]
# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL... SUCCESS!
|
登录从服务器
|
1
2
3
4
5
6
7
8
9
10
|
[root@localhost mysql]
# vi /etc/my.cnf
# 开启中继日志
relay-log=
/mydata/data/relay-log
relay-log-index=
/mydata/data/relay-log
.index
server-
id
=2
# 关闭二进制日志
#log-bin=mysql-bin
[root@localhost ~]
# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL... SUCCESS!
|
连接至主服务器，并开始复制
|
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
58
59
60
61
|
# 查看主服务器正在使用的binlog和当前的position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
1 row
in
set
(0.00 sec)
# 在从服务器连接主服务器
mysql> change master to master_host=
'192.168.30.115'
-> master_port=3306
-> master_log_file=
'mysql-bin.000002'
-> master_user=
'repl'
-> master_password=
'123456'
-> master_log_pos=107;
Query OK, 0 rows affected (0.49 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.30.115
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
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: 107
Relay_Log_Space: 403
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)
|
以上信息发现
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明主从服务器配置成功了。
三，配置半同步复制
登录主服务器
|
1
2
3
4
5
6
7
8
9
|
# 加载半同步复制模块
mysql>
install
plugin rpl_semi_sync_master soname
'semisync_master.so'
;
Query OK, 0 rows affected (0.00 sec)
# 开启半同步复制功能
mysql>
set
global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
# 设置主服务器等待从服务器多长时间，开始转为异步复制
mysql>
set
global rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)
|
登录从服务器
|
1
2
3
4
5
6
7
8
|
mysql>
install
plugin rpl_semi_sync_slave soname
'semisync_slave.so'
;
Query OK, 0 rows affected (0.00 sec)
mysql>
set
global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.07 sec)
mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
|
在Master和Slave的my.cnf中编辑
|
1
2
3
4
5
6
7
|
# On Master
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
# 1 second
# On Slave
[mysqld]
rpl_semi_sync_slave_enabled=1
|
|
1
2
3
4
|
# 也可通过设置全局变量的方式来设置,如下：
set
global rpl_semi_sync_master_enabled=1
# 取消加载插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
|
查看从服务器上的semi_sync是否开启:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> show global status like
'rpl_semi%'
;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows
in
set
(0.00 sec)
# 注意clients 变为1 ，证明主从半同步复制连接成功
|
在主-从架构上，建议使用的配置：
主服务器：
sync_binlog=1 # 主服务器崩溃，任何一个事务提交之后就立即写入到磁盘中的二进制文件
innodb_flush_logs_at_trx_commit=1 #任何一个事物提交之后就立即写入到磁盘中的日志文件
从服务器：
skip_slave_start=1 #重启从服务器时不自动开启slave进程
read_only=1 #设置从服务器为只读模式
四，配置基于SSL主从复制
配置Master为CA服务器
|
1
2
3
4
5
6
|
[root@localhost ~]
# cd /etc/pki/CA
[root@localhost CA]
# (umask 077;openssl genrsa 2048 > private/cakey.pem)
[root@localhost CA]
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
[root@localhost CA]
# mkdir certs crl newcerts
[root@localhost CA]
# touch index.txt
[root@localhost CA]
# echo 01 > serial
|
在主服务器上为mysql准备私钥和证书
|
1
2
3
4
5
6
7
8
|
[root@localhost ~]
# mkdir /usr/local/mysql/ssl
[root@localhost ~]
# cd /usr/local/mysql/ssl/
[root@localhost ssl]
# (umask 077;openssl genrsa 1024 > mysql.key)
# 这里生成的证书请求要去CA的一致
[root@localhost ssl]
# openssl req -new -key mysql.key -out mysql.csr
[root@localhost ssl]
# openssl ca -in mysql.csr -out mysql.crt
[root@localhost ssl]
# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@localhost ssl]
# chown -R mysql.mysql /usr/local/mysql/ssl/
|
在从服务器上：
|
1
2
3
4
5
6
7
8
|
[root@localhost ~]
# mkdir /usr/local/mysql/ssl
[root@localhost ~]
# chown -R mysql.mysql /usr/loca/mysql/ssl
[root@localhost ~]
# cd /usr/local/mysql/ssl/
[root@localhost ssl]
# (umask 077;openssl genrsa 1024 > mysql.key)
# 这里填写也要与CA一致
root@localhost ssl]
# openssl req -new -key mysql.key -out mysql.csr
# 把证书申请传到CA服务器上
[root@localhost ssl]
# scp mysql.csr root@192.168.30.115:/root/
|
在master为slave签署证书
|
1
2
3
4
5
|
[root@localhost ~]
# openssl ca -in mysql.csr -out mysql.crt
[root@localhost ~]
# scp ./mysql.crt root@192.168.30.116:/usr/local/mysql/ssl
[root@localhost ~]
# scp /etc/pki/CA/cacert.pem root@192.168.30.116:/usr/local/mysql/ssl/
#在从服务器，设置ssl属主属组为mysql
[root@localhost ~]
# chown -R mysql.mysql /usr/local/mysql/ssl/
|
在主从服务器上都开启ssl功能：
|
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
|
[root@localhost ~]
# vi /etc/my.cnf
# 添加如下几行
ssl
ssl-ca=
/usr/local/mysql/ssl/cacert
.pem
ssl-cert=
/usr/local/mysql/ssl/mysql
.crt
ssl-key=
/usr/local/mysql/ssl/mysql
.key
[root@localhost ~]
# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL... SUCCESS!
[root@localhost ssl]
# mysql
mysql> show variables like
'%ssl%'
;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection
id
: 3
Current database: *** NONE ***
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca |
/usr/local/mysql/ssl/cacert
.pem |
| ssl_capath | |
| ssl_cert |
/usr/local/mysql/ssl/mysql
.crt |
| ssl_cipher | |
| ssl_key |
/usr/local/mysql/ssl/mysql
.key |
+---------------+---------------------------------+
7 rows
in
set
(0.00 sec)
|
以上信息显示master服务器ssl功能已经开启
在slave服务器执行上面同样的操作，开启ssl功能
在master服务器删除原来的复制账号，添加新的复制账号
|
1
2
3
|
mysql> delete from user where User=
'repl'
;
mysql> grant replication client,replication slave on *.* to
'sslrepl'
@
'192.168.30.%'
identified by
'123456'
require ssl;
mysql> flush privileges;
|
在slave服务器连接master
|
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
58
59
60
|
mysql> stop slave;
mysql> change master to master_host=
'192.168.30.115'
-> master_port=3306
-> master_log_file=
'mysql-bin.000005'
-> master_user=
'sslrepl'
-> master_password=
'123456'
-> master_log_pos=107
-> master_ssl=1
-> master_ssl_ca=
'/usr/local/mysql/ssl/cacert.pem'
-> master_ssl_cert=
'/usr/local/mysql/ssl/mysql.crt'
-> master_ssl_key=
'/usr/local/mysql/ssl/mysql.key'
;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 192.168.30.115
Master_User: sslrepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000005
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: 107
Relay_Log_Space: 403
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
/usr/local/mysql/ssl/cacert
.pem
Master_SSL_CA_Path:
/usr/local/mysql/ssl
Master_SSL_Cert:
/usr/local/mysql/ssl/mysql
.crt
Master_SSL_Cipher:
Master_SSL_Key:
/usr/local/mysql/ssl/mysql
.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)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes 三个都输出为
yes
才表明配置成功
|
五，在slave主机测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
[root@localhost ~]
# mysql -u sslrepl -h 192.168.30.115 -p123456 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.1.66,
for
redhat-linux-gnu (x86_64) using readline 5.1
Connection
id
: 6
Current database:
Current user: sslrepl@192.168.30.116
SSL: Cipher
in
use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile:
''
Using delimiter: ;
Server version: 5.5.33-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.30.115 via TCP
/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 24 min 25 sec
Threads: 2 Questions: 23 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.015
|
信息：SSL: Cipher in use is DHE-RSA-AES256-SHA 表明连接是加密的
Mysql-5.5.33主从复制，半同步复制，以及基于ssl的复制已经配置完成
本文转自ljl_19880709 51CTO博客，原文链接：http://blog.51cto.com/luojianlong/1387371，如需转载请自行联系原作者
