Mysql 主从

本篇博文主要讲解Mysql主从复制、半同步、基于SSL加密的复制

简介

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库

Mysql复制

Mysql内建的复制功能是构建大型、高性能应用程序的基础；将Mysql的数据分布到多个系统上，而这种分布的机制是通过将一台Mysql服务器的数据复制到其他主机(slave)上，由slave主机读取Master服务器的二进制日志文件然后重新在本地执行一遍来实现

注意：做Mysql主从复制时，所有更新操作都只能在Master服务器，而Slave服务只负责更新自己的数据并提供查询操作

做Mysql复制能解决什么问题？

1、数据的分布

2、负载均衡

3、备份操作

4、高可用和容错性

Mysql复制原理

总的来说Mysql的复制就三个步骤：

1、在Master服务器将改变的数据记录到二进制日志(binary log)中（这些记录叫做二进制日志事件）

2、Slave服务器将Master服务器上的二进制日志拷贝到自己的中继日志(relay-log)中

3、Slave服务器读取中继日志中的事件，然后将改变的数据写入到自己的数据库中

下面我们使用一张图来说明复制的过程：

第一步：是在Master服务器上记录二进制日志。在每个更新数据的事务完成之前，Master服务器都会将数据更改记录到二进制日志中。即使事务在执行期间是交错的，Mysql也会串行地将事务写入到二进制日志中。在把事件写入二进制日志之后，Master服务器告诉存储引擎可以提交事务了

第二步：是Slave服务器把主服务器的二进制日志拷贝到自己的硬盘上，进入所谓的“中继日志”中。首先，它启动一个工作线程，叫I/O线程，这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志转储进程（它没有相应的SQL命令）。这个转储进程Master服务器的二进制日志中读取数据。它不会对事件进行轮询。如果3跟上了Master服务器，就会进入休眠状态并等待有新的事件发生时Master服务器发出的信号。I/O线程把数据写入Slave服务器的中继日志中

第三步：SQL线程读取中继日志，并且重放其中的事件，然后更新Slave服务器的数据。由于这个线程能跟上I/O线程，中继日志通常在操作系统的缓存中，所以中继日志的开销很低。SQL线程执行事件也可以被写入Slave服务器自己的二进制日志中，它对于有些场景很实用

上图中显示了在Slave服务器有两个运行的线程，在Master服务器上也有一个运行的线程：和其他普通连接一样，由Slave服务器发起的连接，在Master服务器上同样拥有一个线程

配置注意事项

1、Master服务器必须开启二进制日志

2、Master和Slave的Server-id不能相同

3、同一个Master的多个Slave，Server-id也不能相同

4、Binlog_format最好相同

5、在Slave服务器上配置log-slave-updates=1时，也需要开启二进制日志；如果可以推荐使用read_only选项，该选项会阻止没有权限的线程修改数据

---

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

软件版本

系统版本：CentOS 6.4_x86_64

Mysql版本：mysql-5.5.33-linux2.6-x86_64

环境介绍

---

安装前准备

1、修改主机名称

1 2 3 4 5 6 7

######NOD1节点执行 sed  -i  's@\(HOSTNAME=\).*@\1master.allen.com@g'  /etc/sysconfig/network hostname  master.allen.com ######NOD2节点执行 sed  -i  's@\(HOSTNAME=\).*@\1slave.allen.com@g'  /etc/sysconfig/network hostname  slave.allen.com 注释：修改文件须重启系统生效，这里使用 "hostname" 命令先修改文件然后执行命令修改主机名称可以不用重启

2、配置主机名解析，这里修改hosts文件来实现

1 2 3 4 5

######在两台服务器执行如下命令 cat  >>  /etc/hosts  << EOF 172.16.14.1 master.allen.com master 172.16.14.2 slave.allen.com slave EOF

3、同步两台服务器时间,保持时间一致;使用"ntpdate"命令更新时间,使用"date"命令查看时间;这里不在介绍

---

Mysql安装 Mysql下载点此处

1、在Master与Slave服务器上分别安装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 36 37 38

######在Master服务器上安装Mysql ==================================================================== ######添加Mysqld运行用户 [root@master ~] # useradd -r -u 300 mysql ######创建数据存放目录 [root@master ~] # mkdir -p /mydata/data ######解压并创建软链接 [root@master ~] # tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/ [root@master ~] # cd /usr/local/ [root@master  local ] # ln -s mysql-5.5.33-linux2.6-x86_64 mysql [root@master  local ] # cd mysql ######为Mysqld服务提供Sysv服务脚本并添加到系统服务设置为开机自启动 [root@master mysql] # cp support-files/mysql.server /etc/init.d/mysqld [root@master mysql] # chmod +x /etc/init.d/mysqld [root@master mysql] # chkconfig --add mysqld [root@master mysql] # chkconfig mysqld on ######为Mysqld服务提供主配置文件 [root@master mysql] # cp support-files/my-large.cnf /etc/my.cnf ######修改主配置文件添加以下选项 [root@master mysql] # vim /etc/my.cnf datadir =  /mydata/data         #数据存放目录 innodb_file_per_table = 1      #innodb表每表一个表空间 ######修改PATH变量 [root@master mysql] # echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile [root@master mysql] # . /etc/profile ######修改Mysqld服务的头文件让系统可以识别 [root@master mysql] # ln -s /usr/local/mysql/include /usr/include/mysql ######修改Mysqld服务的库文件让系统可以识别 [root@master mysql] # echo "/usr/local/mysql/lib" >> /etc/ld.so.conf [root@master mysql] # ldconfig ######设置Mysqld服务的安装程序与数据存放目录属主、属组用户 [root@master mysql] # chown -R root.mysql ./* [root@master mysql] # chown -R  mysql.mysql /mydata/data ######初始化数据库 [root@master mysql] # ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ ######启动Mysqld服务测试 [root@master ~] # service mysqld start Starting MySQL....                                         [  OK  ]

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

######在Slave服务器上安装Mysql ==================================================================== ######添加Mysqld运行用户 [root@slave ~] # useradd -r -u 300 mysql ######创建数据存放目录 [root@slave ~] # mkdir -p /mydata/data ######解压并创建软链接 [root@slave ~] # tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/ [root@slave ~] # cd /usr/local/ [root@slave  local ] # ln -s mysql-5.5.33-linux2.6-x86_64 mysql [root@slave  local ] # cd mysql ######为Mysqld服务提供Sysv服务脚本并添加到系统服务设置为开机自启动 [root@slave mysql] # cp support-files/mysql.server /etc/init.d/mysqld [root@slave mysql] # chmod +x /etc/init.d/mysqld [root@slave mysql] # chkconfig --add mysqld [root@slave mysql] # chkconfig mysqld on ######为Mysqld服务提供主配置文件 [root@slave mysql] # cp support-files/my-large.cnf /etc/my.cnf ######修改主配置文件添加以下选项 [root@slave mysql] # vim /etc/my.cnf datadir =  /mydata/data         #数据存放目录 innodb_file_per_table = 1      #innodb表每表一个表空间 ######修改PATH变量 [root@slave mysql] # echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile [root@slave mysql] # . /etc/profile ######修改Mysqld服务的头文件让系统可以识别 [root@slave mysql] # ln -s /usr/local/mysql/include /usr/include/mysql ######修改Mysqld服务的库文件让系统可以识别 [root@slave mysql] # echo "/usr/local/mysql/lib" >> /etc/ld.so.conf [root@slave mysql] # ldconfig ######设置Mysqld服务的安装程序与数据存放目录属主、属组用户 [root@slave mysql] # chown -R root.mysql ./* [root@slave mysql] # chown -R  mysql.mysql /mydata/data ######初始化数据库 [root@slave mysql] # ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ ######启动Mysqld服务测试 [root@slave ~] # service mysqld start Starting MySQL....                                         [  OK  ]

---

主从复制配置

1、在Master服务器上建立用于Slave服务器复制数据的帐户

1 2 3 4 5 6 7 8 9 10 11 12 13

[root@master ~] # mysql mysql> grant replication slave,replication client on *.* to  'allen' @ '172.16.14.2'  identified by  'p@ssword' ; Query OK, 0 rows affected (0.02 sec); mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants  for  'allen' @ '172.16.14.2' ;   #查看用户授权 +------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants  for  allen@172.16.14.2                                                                                                                   | +------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  'allen' @ '172.16.14.2'  IDENTIFIED BY PASSWORD  '*4F477FE814A0E3A4A5FD42BBB87C2DE8C36750DE'  | +------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row  in  set  (0.01 sec)

2、在Slave服务器上使用授权用户连接测试

1 2 3 4 5 6 7 8 9 10

[root@slave ~] # mysql -uallen -pp@ssword -h 172.16.14.1 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection  id  is 2 Server version: 5.5.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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>

3、修改Master服务器上的Mysqld主配置文件如下：

1 2 3 4 5 6

[root@master ~] # vim /etc/my.cnf log-bin = mysql-bin               #二进制日志文件 log_bin_index = mysql_bin.index   #二进制日志文件索引 binlog_format = mixed             #设置日志格式为混合模式 server- id        = 10              #用于识别的ID [root@master ~] # service mysqld restart #重启服务使配置文件生效

4、修改Slave服务器上的Mysqld主配置文件如下：

1 2 3 4 5 6 7 8 9

[root@slave ~] # vim /etc/my.cnf #binlog_format=mixed               #注释此行 skip_slave_start = 1                #启动服务时不自动启动从服务线程 read_only = 1                       #设置Slave服务器为只读 relay_log = relay_log               #开启中继日志文件 relay_log_index = relay_log.index   #开启中继日志文件索引 server- id        = 20                #用户识别的ID号 #log-bin=mysql-bin                 #注释掉二进制日志文件,因为Master服务器已经记录了一份,这里没有必要再记录一份,避免浪费资源 [root@slave ~] # service mysqld restart #重启服务使配置生效

5、查看Master服务器的二进制日志及二进制日志事件位置用于Slave服务器复制

1 2 3 4 5 6 7 8 9

[root@master ~] # mysql -e 'show master status;' +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 |      107 |              |                  | +------------------+----------+--------------+------------------+ 注释：      File：表示从此日志开始复制      Position：表示从这个事件开始复制

6、在Slave服务器上同步Master服务器上面的数据如下：

1 2 3 4 5 6 7 8 9 10 11 12

mysql> change master to master_host= '172.16.14.1' ,master_user= 'allen' ,master_password= 'p@ssword' , master_port=3306,master_log_file= 'mysql-bin.000004' ,master_log_pos=107; ============================================================================ ######猎取指令帮助 mysql> help change master to CHANGE MASTER TO    MASTER_HOST= 'master.allen.com' ,      #主机名称    MASTER_USER= 'allen' ,                 #连接Master服务器的授权用户    MASTER_PASSWORD= 'p@ssword' ,          #授权用户密码    MASTER_PORT=3306,                    #端口    MASTER_LOG_FILE= 'mysql-bin.000004' ,  #二进制日志文件    MASTER_LOG_POS=107,                  #二进制日志事件位置

7、启动Slave服务器的复制线程并查看状态

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

mysql> start slave;             #启动Slave服务器线程 mysql> show slave status\G; *************************** 1. row ***************************                 Slave_IO_State: Waiting  for  master to send event                    Master_Host: 172.16.14.1         #Master服务器地址                    Master_User: allen               #连接Master服务器用户名                    Master_Port: 3306                #Master服务器监听端口                  Connect_Retry: 60                  #重试时间间隔                Master_Log_File: mysql-bin.000004    #I/O线程读取的二进制日志文件            Read_Master_Log_Pos: 107                 #I/O线程读取的二进制日志文件事件位置                 Relay_Log_File: relay_log.000002    #SQL线程正在读取的中继日志文件                  Relay_Log_Pos: 253                 #SQL线程读取和执行的中继日志文件事件位置          Relay_Master_Log_File: mysql-bin.000004               Slave_IO_Running: Yes                 #Slave服务器IO线程状态              Slave_SQL_Running: Yes                 #Slave服务器SQL线程状态                Replicate_Do_DB:                     #下面Replicate开头的表示用来指明哪些库或者表在复制时不需要同步            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                  #SQL线程读取日志参数的错误数量                     Last_Error:                    #SQL线程读取日志参数的错误消息                   Skip_Counter: 0                  #最近被用于SQL_SLAVE_SKIP_COUNTER的值            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连接             Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0                  #落后于Master服务器的时间 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: 10

8、在Slave服务器查看启动的线程

1 2 3 4 5 6 7 8

[root@slave ~] # mysql -e 'show processlist;' +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ |  1 | system user |           | NULL | Connect |  851 | Waiting  for  master to send event                                            | NULL             | |  2 | system user |           | NULL | Connect |  851 | Slave has  read  all relay log; waiting  for  the slave I /O  thread to update it | NULL             | | 20 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | show processlist | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

9、在Master服务器创建数据库并在Slave服务器上验证是否存在

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

######在Master服务器创建数据库并查看 [root@master ~] # mysql -e 'create database allen;' [root@master ~] # mysql -e 'show databases' +--------------------+ | Database           | +--------------------+ | information_schema | | allen              | | mysql              | | performance_schema | |  test                | +--------------------+ =========================================================== ######在Slave服务器查看是否有"allen"数据库 [root@slave ~] # mysql -e 'show databases;' +--------------------+ | Database           | +--------------------+ | information_schema | | allen              |     #数据库已成功同步到Slave服务器 | mysql              | | performance_schema | |  test                | +--------------------+

10、在Master与Slave服务器查看二进制日志事件位置已更新

1 2 3 4 5 6 7 8 9 10 11

######查看Master服务器 [root@master ~] # mysql -e 'show master status;' +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 |      192 |              |                  | +------------------+----------+--------------+------------------+ ========================================================================== ######查看Slave服务器 [root@slave ~] # mysql -e 'show slave status\G;' | grep "Read_Master_Log_Pos"            Read_Master_Log_Pos: 192

---

半同步复制

简述

半同步意思：表示Master服务器只需要接收到其中一台Slave的返回信息，就会commit;否则需要等待直到超时时间然后切换成异步再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以在损失很小的性能的前提下提高数据安全性

1、半同步的开启也是比较简单滴，只需要在Master与Slave服务器上都安装上半同步的插件并启用即可；而插件在Mysql的安装目录中："/usr/local/mysql/lib/plugin/"

1 2 3 4

######查看半同步插件 ls  /usr/local/mysql/lib/plugin semisync_master.so     #用于Master服务器安装的半同步插件 semisync_slave.so      #用于Slave服务器安装的半同步插件

2、在Master与Slave服务器分别安装半同步插件

1 2 3 4 5 6 7 8 9 10 11

######在Master服务器安装半同步插件 [root@master ~] # mysql mysql>  install  plugin rpl_semi_sync_master soname  'semisync_master.so' ;  #安装Master半同步插件 mysql>  set  global rpl_semi_sync_master_enabled = 1;     #开启Master半同步功能 mysql>  set  global rpl_semi_sync_master_timeout = 1000; ========================================================================= ######在Slave服务器安装半同步插件 [root@slave ~] # mysql mysql>  install  plugin rpl_semi_sync_slave soname  'semisync_slave.so' ;  #安装Slave半同步插件 mysql>  set  global rpl_semi_sync_slave_enabled = 1;       #开启Slave半同步功能 mysql> stop slave io_thread;start slave io_thread;       #重启IO线程生效

3、查看半同步开启状态

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

######在Master服务器上查看 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     | +--------------------------------------------+-------+ mysql> show global variables like  '%rpl%' ; +------------------------------------+-------+ | Variable_name                      | Value | +------------------------------------+-------+ | rpl_recovery_rank                  | 0     | | rpl_semi_sync_master_enabled       | ON    |  #Master半同步已经开启 | rpl_semi_sync_master_timeout       | 1000  |  #超时时间 | rpl_semi_sync_master_trace_level   | 32    | | rpl_semi_sync_master_wait_no_slave | ON    | +------------------------------------+-------+ ========================================================================= ######在Slave服务器上查看 mysql> show global status like  'rpl_semi%' ; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON    |          #已经为开启状态 +----------------------------+-------+ mysql> show global variables like  '%rpl%' ; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | rpl_recovery_rank               | 0     | | rpl_semi_sync_slave_enabled     | ON    |     #Slave半同步已经开启 | rpl_semi_sync_slave_trace_level | 32    | +---------------------------------+-------+

4、查看Slave线程是否启动

1 2 3 4

[root@slave ~] # mysql -e 'show slave status\G;' | grep Running               Slave_IO_Running: Yes              Slave_SQL_Running: Yes 注释：这两项必须为 "Yes" ,如果是 "No" 说明启动失败

5、在Master服务器上将前面创建的"allen"数据库删除，然后验证Slave服务器

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

######在Master服务器删除数据库 [root@master ~] # mysql -e 'drop database allen;' [root@master ~] # mysql -e 'show databases;' +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | |  test                | +--------------------+ ======================================================= ######在Slave服务器查看 [root@slave ~] # mysql -e 'show databases;' +--------------------+ | Database           |    注释：已经成功删除 "allen" 数据库 +--------------------+ | information_schema | | mysql              | | performance_schema | |  test                | +--------------------+

6、以上配置都不能永久生效，如果想要永久生效，将以上配置加入到配置文件重启服务即可；这里就不在演示了

---

基于SSL的复制

简述

由于Mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据

1、将Master服务器自己做成CA服务器

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[root@master ~] # cd /etc/pki/CA/ [root@master CA] # (umask 077;openssl genrsa -out private/cakey.pem 2048) [root@master CA] # openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter  '.' , the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's  hostname ) []:master.allen.com Email Address []: [root@master CA] # touch index.txt [root@master CA] # echo 01 > serial

2、为Master创建证书申请并由CA服务器签发证书

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

[root@master CA] # mkdir /usr/local/mysql/ssl [root@master CA] # cd /usr/local/mysql/ssl [root@master ssl] # (umask 077;openssl genrsa -out master.key 2048) [root@master ssl] # openssl req -new -key master.key -out master.csr -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter  '.' , the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's  hostname ) []:master.allen.com Email Address []:master@allen.com Please enter the following  'extra'  attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@master ssl] # openssl ca -in master.csr -out master.crt -days 365 Using configuration from  /etc/pki/tls/openssl .cnf Check that the request matches the signature Signature ok Certificate Details:          Serial Number: 1 (0x1)          Validity              Not Before: Sep 20 12:22:19 2013 GMT              Not After : Sep 20 12:22:19 2014 GMT          Subject:              countryName               = CN              stateOrProvinceName       = ShangHai              organizationName          = Allen              organizationalUnitName    = Tech              commonName                = master.allen.com          X509v3 extensions:              X509v3 Basic Constraints:                  CA:FALSE              Netscape Comment:                  OpenSSL Generated Certificate              X509v3 Subject Key Identifier:                16:89:07:36:58:C9:AD:7B:97:D6:77:2E:13:FB:66:4F:A9:2B:3E:A3              X509v3 Authority Key Identifier:             keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6A Certificate is to be certified  until  Sep 20 12:22:19 2014 GMT (365 days) Sign the certificate? [y /n ]:y 1 out of 1 certificate requests certified, commit? [y /n ]y Write out database with 1 new entries Data Base Updated

3、为Slave服务器创建证书申请

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[root@slave ~] # mkdir /usr/local/mysql/ssl [root@slave ~] # cd /usr/local/mysql/ssl [root@slave ssl] # (umask 077;openssl genrsa -out slave.key 2048) [root@slave ssl] # openssl req -new -key slave.key -out slave.csr -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter  '.' , the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's  hostname ) []:slave.allen.com Email Address []: Please enter the following  'extra'  attributes to be sent with your certificate request A challenge password []: An optional company name []:

4、为Slave服务器签署证书

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

######将证书申请请求拷贝到CA服务器签署 [root@slave ssl] # scp slave.csr master.allen.com:/tmp/ [root@master ~] # openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365 Using configuration from  /etc/pki/tls/openssl .cnf Check that the request matches the signature Signature ok Certificate Details:          Serial Number: 2 (0x2)          Validity              Not Before: Sep 20 12:32:55 2013 GMT              Not After : Sep 20 12:32:55 2014 GMT          Subject:              countryName               = CN              stateOrProvinceName       = ShangHai              organizationName          = Allen              organizationalUnitName    = Tech              commonName                = slave.allen.com          X509v3 extensions:              X509v3 Basic Constraints:                  CA:FALSE              Netscape Comment:                  OpenSSL Generated Certificate              X509v3 Subject Key Identifier:                 4E:19:98:5D:F5:D2:D1:71:8B:93:4F:84:3C:A2:C7:2C:FE:6D:E2:62              X509v3 Authority Key Identifier:             keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6A Certificate is to be certified  until  Sep 20 12:32:55 2014 GMT (365 days) Sign the certificate? [y /n ]:y 1 out of 1 certificate requests certified, commit? [y /n ]y Write out database with 1 new entries Data Base Updated ######签署好证书申请拷贝到Slave服务器 [root@master ~] # scp /tmp/slave.crt slave.allen.com:/usr/local/mysql/ssl/

5、将CA证书拷贝到Slave服务器并为Master拷贝一份

1 2	[root@master ~] # scp /etc/pki/CA/cacert.pem slave.allen.com:/usr/local/mysql/ssl/ [root@master ~] # cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/

6、修改Master与Slave服务器证书属主、属组为"mysql"用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

######修改Master服务器 [root@master ~] # chown -R mysql.mysql /usr/local/mysql/ssl [root@master ~] # ll /usr/local/mysql/ssl/ -rw-r--r-- 1 mysql mysql 1415 Sep 20 20:57 cacert.pem -rw-r--r-- 1 mysql mysql 4600 Sep 20 20:22 master.crt -rw-r--r-- 1 mysql mysql 1054 Sep 20 20:20 master.csr -rw------- 1 mysql mysql 1675 Sep 20 20:17 master.key =============================================================== ######修改Slave服务器 [root@slave ~] # chown -R mysql.mysql /usr/local/mysql/ssl [root@slave ~] # ll /usr/local/mysql/ssl/ -rw-r--r-- 1 mysql mysql 1415 Sep 15 03:10 cacert.pem -rw-r--r-- 1 mysql mysql 4598 Sep 15 03:05 slave.crt -rw-r--r-- 1 mysql mysql 1054 Sep 15 03:00 slave.csr -rw------- 1 mysql mysql 1675 Sep 15 02:59 slave.key 注意：Master与Slave服务器上的证书属主、属组必须为mysql用户及组

7、在Master与Slave服务器修改主配置文件开启SSL加密功能

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

######修改Master服务器 [root@master ~] # vim /etc/my.cnf                  #添加如下选项 ssl                                                #开启SSL功能 ssl_ca =  /usr/local/mysql/ssl/cacert .pem           #指定CA文件位置 ssl_cert =  /usr/local/mysql/ssl/master .crt   #指定证书文件位置 ssl_key =  /usr/local/mysql/ssl/master .key    #指定密钥所在位置 [root@master ~] # service mysqld restart           #重启服务生效 ==================================================================== ######修改Slave服务器 [root@slave ~] # vim /etc/my.cnf ssl ssl_ca =  /usr/local/mysql/ssl/cacert .pem ssl_cert =  /usr/local/mysql/ssl/slave .crt ssl_key =  /usr/local/mysql/ssl/slave .key [root@slave ~] # service mysqld restart

8、在Master服务器查看SSL加密是否开启；然后创建授权一个基于密钥认证的用户

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[root@master ~] # mysql mysql> show variables like  '%ssl%' ; +---------------+---------------------------------+ | 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/master .crt | | ssl_cipher    |                                 | | ssl_key       |  /usr/local/mysql/ssl/master .key | +---------------+---------------------------------+ mysql> grant replication client,replication slave on *.* to  'slave' @ '172.16.%.%'  identified by  'passwd'  require ssl; mysql> flush privileges;

9、查看Master服务器二进制日志文件和事件位置用于Slave服务器连接从这个位置开始复制

1 2 3 4 5 6

mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 |      350 |              |                  | +------------------+----------+--------------+------------------+

10、测试使用加密用户指定密钥连接Master服务器

1 2 3 4 5 6 7 8 9 10

[root@slave ~] # mysql -uslave -ppasswd -h 172.16.14.1 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection  id  is 5 Server version: 5.5.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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>

11、查看Slave服务器SSL是否开启并连接Master服务器

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

######查看Slave服务器SSL是否开启 [root@slave ~] # mysql mysql> show variables like  '%ssl%' ; +---------------+---------------------------------+ | 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/slave .crt  | | ssl_cipher    |                                 | | ssl_key       |  /usr/local/mysql/ssl/slave .key  | +---------------+---------------------------------+ ######连接Master服务器 mysql> change master to master_host= '172.16.14.1' ,master_user= 'slave' ,master_password= 'passwd' , master_log_file= 'mysql-bin.000004' ,master_log_pos=350,master_ssl=1, master_ssl_ca= '/usr/local/mysql/ssl/cacert.pem' , master_ssl_cert= '/usr/local/mysql/ssl/slave.crt' , master_ssl_key= '/usr/local/mysql/ssl/slave.key' ;

1 2 3 4 5 6

######获取命令帮助 mysql> help change master to    | MASTER_SSL = {0|1}                         #是否使用SSL功能    | MASTER_SSL_CA =  'ca_file_name'             #CA证书位置    | MASTER_SSL_CERT =  'cert_file_name'         #指定自己的证书文件    | MASTER_SSL_KEY =  'key_file_name'           #指定自己的密钥文件

12、查看Slave服务器状态

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

mysql> show slave status\G; *************************** 1. row ***************************                 Slave_IO_State:                    Master_Host: 172.16.14.1                    Master_User: slave                    Master_Port: 3306                  Connect_Retry: 60                Master_Log_File: mysql-bin.000004            Read_Master_Log_Pos: 350                 Relay_Log_File: relay_log.000001                  Relay_Log_Pos: 4          Relay_Master_Log_File: mysql-bin.000004               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: 350                Relay_Log_Space: 107                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:                Master_SSL_Cert:  /usr/local/mysql/ssl/slave .crt              Master_SSL_Cipher:                 Master_SSL_Key:  /usr/local/mysql/ssl/slave .key          Seconds_Behind_Master: NULL 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: 0

---

基于SSL复制的结果验证

1、在Master服务器上创建数据库

1 2 3 4 5 6 7 8 9 10 11

[root@master ~] # mysql -e 'create database slave;' [root@master ~] # mysql -e 'show databases;' +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | slave              | |  test                | +--------------------+

2、登录Slave服务器验证"slave"数据库是否存在

1 2 3 4 5 6 7 8 9 10

[root@slave ~] # mysql -e 'show databases;' +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | slave              | |  test                | +--------------------+

到此Mysql的主从复制、半同步复制、基于SSL加密的复制已全部完成，后续会更新Mysql的主、主复制，敬请关注！！！

本文出自 “ALLEN” 博客，请务必保留此出处http://502245466.blog.51cto.com/7559397/1299731