MySQL-5.5.33主从复制,半同步复制,以及基于SSL的复制

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL-5.5.33主从复制,半同步复制,以及基于SSL的复制

技术小胖子 2017-11-09 07:46:00 浏览865
展开阅读全文

一,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 开始支持延时复制。


下面是复制的基本原理:

wKiom1M4yg7wvXg6AAFzdgtnwWg078.jpg


二,配置主从复制

为了演示方便,实验使用通用的二进制包来安装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,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注