本篇博文主要讲解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、修改主机名称
######NOD1节点执行sed -i 's@\(HOSTNAME=\).*@\@g' /etc/sysconfig/networkhostname ######NOD2节点执行sed -i 's@\(HOSTNAME=\).*@\@g' /etc/sysconfig/networkhostname 注释：修改文件须重启系统生效，这里使用"hostname"命令先修改文件然后执行命令修改主机名称可以不用重启
2、配置主机名解析，这里修改hosts文件来实现
######在两台服务器执行如下命令cat >> /etc/hosts << EOF172.16.14.1 master172.16.14.2 slaveEOF
3、同步两台服务器时间,保持时间一致;使用"ntpdate"命令更新时间,使用"date"命令查看时间;这里不在介绍
Mysql安装 Mysql下载点此处
1、在Master与Slave服务器上分别安装Mysql
######在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-f /etc/f######修改主配置文件添加以下选项[root@master mysql]# vim /etc/fdatadir = /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 startStarting MySQL.... [ OK ]
######在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-f /etc/f######修改主配置文件添加以下选项[root@slave mysql]# vim /etc/fdatadir = /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 startStarting MySQL.... [ OK ]
主从复制配置
1、在Master服务器上建立用于Slave服务器复制数据的帐户
[root@master ~]# mysqlmysql> grant replication slave,replication clienton *.* 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服务器上使用授权用户连接测试
[root@slave ~]# mysql -uallen -pp@ssword -h 172.16.14.1Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.33-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
3、修改Master服务器上的Mysqld主配置文件如下：
[root@master ~]# vim /etc/flog-bin = mysql-bin #二进制日志文件log_bin_index = mysql_bin.index #二进制日志文件索引binlog_format = mixed #设置日志格式为混合模式server-id = 10 #用于识别的ID[root@master ~]# service mysqld restart #重启服务使配置文件生效
4、修改Slave服务器上的Mysqld主配置文件如下：
[root@slave ~]# vim /etc/f#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服务器复制
[root@master ~]# mysql -e 'show master status;'+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 |107 | | |+------------------+----------+--------------+------------------+注释：File：表示从此日志开始复制Position：表示从这个事件开始复制
6、在Slave服务器上同步Master服务器上面的数据如下：
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 toCHANGE MASTER TOMASTER_HOST='',#主机名称MASTER_USER='allen',#连接Master服务器的授权用户MASTER_PASSWORD='p@ssword', #授权用户密码MASTER_PORT=3306, #端口MASTER_LOG_FILE='mysql-bin.000004', #二进制日志文件MASTER_LOG_POS=107, #二进制日志事件位置
7、启动Slave服务器的复制线程并查看状态
mysql> start slave; #启动Slave服务器线程mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_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.000004Slave_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: NoneUntil_Log_File:Until_Log_Pos: 0Master_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 10
8、在Slave服务器查看启动的线程
[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服务器上验证是否存在
######在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服务器查看二进制日志事件位置已更新
######查看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/"
######查看半同步插件ls /usr/local/mysql/lib/pluginsemisync_master.so #用于Master服务器安装的半同步插件semisync_slave.so#用于Slave服务器安装的半同步插件
2、在Master与Slave服务器分别安装半同步插件
######在Master服务器安装半同步插件[root@master ~]# mysqlmysql> 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 ~]# mysqlmysql> 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、查看半同步开启状态
######在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线程是否启动
[root@slave ~]# mysql -e 'show slave status\G;' | grep RunningSlave_IO_Running: YesSlave_SQL_Running: Yes注释：这两项必须为"Yes",如果是"No"说明启动失败
5、在Master服务器上将前面创建的"allen"数据库删除，然后验证Slave服务器
######在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服务器
[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 365You are about to be asked to enter information that will be incorporatedinto 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 blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:ShangHaiLocality Name (eg, city) [Default City]:PuDongOrganization Name (eg, company) [Default Company Ltd]:AllenOrganizational Unit Name (eg, section) []:TechCommon Name (eg, your name or your server's hostname) []:Email Address []:[root@master CA]# touch index.txt[root@master CA]# echo 01 > serial
2、为Master创建证书申请并由CA服务器签发证书
[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 365You are about to be asked to enter information that will be incorporatedinto 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 blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:ShangHaiLocality Name (eg, city) [Default City]:PuDongOrganization Name (eg, company) [Default Company Ltd]:AllenOrganizational Unit Name (eg, section) []:TechCommon Name (eg, your name or your server's hostname) []:Email Address []:master@Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:[root@master ssl]# openssl ca -in master.csr -out master.crt -days 365Using configuration from /etc/pki/tls/fCheck that the request matches the signatureSignature okCertificate Details:Serial Number: 1 (0x1)ValidityNot Before: Sep 20 12:22:19 GMTNot After : Sep 20 12:22:19 GMTSubject:countryName= CNstateOrProvinceName = ShangHaiorganizationName= AllenorganizationalUnitName = TechcommonName= X509v3 extensions:X509v3 Basic Constraints:CA:FALSENetscape Comment:OpenSSL Generated CertificateX509v3 Subject Key Identifier:16:89:07:36:58:C9:AD:7B:97:D6:77:2E:13:FB:66:4F:A9:2B:3E:A3X509v3 Authority Key Identifier: keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6ACertificate is to be certified until Sep 20 12:22:19 GMT (365 days)Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated
3、为Slave服务器创建证书申请
[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 365You are about to be asked to enter information that will be incorporatedinto 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 blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:ShangHaiLocality Name (eg, city) [Default City]:PuDongOrganization Name (eg, company) [Default Company Ltd]:AllenOrganizational Unit Name (eg, section) []:TechCommon Name (eg, your name or your server's hostname) []:Email Address []:Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:
4、为Slave服务器签署证书
######将证书申请请求拷贝到CA服务器签署[root@slave ssl]# scp slave.csr :/tmp/[root@master ~]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365Using configuration from /etc/pki/tls/fCheck that the request matches the signatureSignature okCertificate Details:Serial Number: 2 (0x2)ValidityNot Before: Sep 20 12:32:55 GMTNot After : Sep 20 12:32:55 GMTSubject:countryName= CNstateOrProvinceName = ShangHaiorganizationName= AllenorganizationalUnitName = TechcommonName= X509v3 extensions:X509v3 Basic Constraints:CA:FALSENetscape Comment:OpenSSL Generated CertificateX509v3 Subject Key Identifier: 4E:19:98:5D:F5:D2:D1:71:8B:93:4F:84:3C:A2:C7:2C:FE:6D:E2:62X509v3 Authority Key Identifier: keyid:D8:0B:06:3B:6B:1B:36:88:17:56:EB:2A:41:1A:20:A4:89:7F:97:6ACertificate is to be certified until Sep 20 12:32:55 GMT (365 days)Sign the certificate? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated######签署好证书申请拷贝到Slave服务器[root@master ~]# scp /tmp/slave.crt :/usr/local/mysql/ssl/
5、将CA证书拷贝到Slave服务器并为Master拷贝一份
[root@master ~]# scp /etc/pki/CA/cacert.pem :/usr/local/mysql/ssl/[root@master ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
6、修改Master与Slave服务器证书属主、属组为"mysql"用户
######修改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加密功能
######修改Master服务器[root@master ~]# vim /etc/f #添加如下选项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/fsslssl_ca = /usr/local/mysql/ssl/cacert.pemssl_cert = /usr/local/mysql/ssl/slave.crtssl_key = /usr/local/mysql/ssl/slave.key[root@slave ~]# service mysqld restart
8、在Master服务器查看SSL加密是否开启；然后创建授权一个基于密钥认证的用户
[root@master ~]# mysqlmysql> 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服务器连接从这个位置开始复制
mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 |350 | | |+------------------+----------+--------------+------------------+
10、测试使用加密用户指定密钥连接Master服务器
[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.keyWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.33-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
11、查看Slave服务器SSL是否开启并连接Master服务器
######查看Slave服务器SSL是否开启[root@slave ~]# mysqlmysql> 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';
######获取命令帮助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服务器状态
mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State:Master_Host: 172.16.14.1Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 350Relay_Log_File: relay_log.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 350Relay_Log_Space: 107Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: YesMaster_SSL_CA_File: /usr/local/mysql/ssl/cacert.pemMaster_SSL_CA_Path:Master_SSL_Cert: /usr/local/mysql/ssl/slave.crtMaster_SSL_Cipher:Master_SSL_Key: /usr/local/mysql/ssl/slave.keySeconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 0
基于SSL复制的结果验证
1、在Master服务器上创建数据库
[root@master ~]# mysql -e 'create database slave;'[root@master ~]# mysql -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || slave || test|+--------------------+
2、登录Slave服务器验证"slave"数据库是否存在
[root@slave ~]# mysql -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || slave || test|+--------------------+
到此Mysql的主从复制、半同步复制、基于SSL加密的复制已全部完成，后续会更新Mysql的主、主复制，敬请关注！！！
