基于SSL的Mysql主从复制

一、实验目的:

1.跨越互联网加密复制

mysql基于SSL加密连接,为安全从服配置证书,从服拿账号到主服复制时,必须得到主服验证。双方建立SSL会话。

 

二、实验架构

在分布式架构中,时间必须同步

主节点: station20:192.168.1.20

从节点: station21:192.168.1.21

 

三、实验步骤

1.建立ssh互信

[root@station20 ~]# echo -e"192.168.1.21\tstation21\tstation21.example.com" >> /etc/hosts
[root@station20 ~]# echo -e"192.168.1.20\tmaster\tmaster.example.com" >> /etc/hosts
[root@station20 ~]# echo -e"192.168.1.21\tslave\tslave.example.com" >> /etc/hosts
[root@station20 ~]# mkdir .ssh
[root@station20 ~]# chmod 700 .ssh
[root@station20 ~]# ssh-keygen -t rsa
[root@station20 ~]# service network reload
[root@station21 ~]# echo -e"192.168.1.20\tstation20\tstation20.example.com" >> /etc/hosts
[root@station21 ~]# echo -e"192.168.1.20\tmaster\tmaster.example.com" >> /etc/hosts
[root@station21 ~]# echo -e"192.168.1.21\tslave\tslave.example.com" >> /etc/hosts
[root@station21 ~]# mkdir .ssh &&chmod 700 .ssh
[root@station21 ~]# service network reload
[root@station20 ~]# cat.ssh/id_rsa.pub>> .ssh/authorized_keys
[root@station20 ~]# ssh root@slave cat.ssh/id_rsa.pub >> .ssh/authorized_keys
[root@station20 ~]# scp .ssh/authorized_keysroot@slave:.ssh/
[root@station20 ~]# hostname
station20.example.com
[root@station20 ~]# ssh root@slave‘hostname‘
station21.example.com


2. ntp源同步

[root@master~]# yum -y install ntp
[root@master~]# ssh root@192.168.1.21 "yum -y install ntp"
[root@master~]# service ntpd start
[root@master~]# ssh root@192.168.1.21 "service ntpd start"
#http://www.cnblogs.com/JemBai/archive/2012/04/15/2450045.html
#北京邮电大学ntp源
#s1a.time.edu.cn
[root@master~]# ntpdate 202.112.10.60
23 May06:26:20 ntpdate[2228]: the NTP socket is in use, exiting
[root@master~]# crontab -e
*/30 * ** * /usr/sbin/ntpdate 202.112.10.60 &>/dev/null
[root@slave~]# crontab -e
*/30 * ** * /usr/sbin/ntpdate 202.112.10.60 &>/dev/null
[root@master~]# service crond reload
[root@master~]# ssh root@192.168.1.21 "service crond reload"

 

3.用脚本安装mysql,修改配置文件

[root@master~]# source install-mysql.sh
[root@master~]# scp install-mysql.sh 192.168.1.21:/root/
[root@slave~]# source install-mysql.sh
[root@master~]# vi /etc/my.cnf
[mysqld]
datadir = /mydata/data
default_storage_engine = innodb
innodb_file_per_table = 1
sync_binlog = 1
# Trynumber of CPU‘s*2 for thread_concurrency
thread_concurrency= 2
binlog_format=mixed
配置主服务器
1.创建具有复制权限的用户帐号;
2.               设置server-id;
3.               启用二进制日志;
主server-id      = 1
主log-bin=/mydata/binlogs/master-bin二进制日志和中继日志一定要绝对路径,为安全必须挂载另外卷与数据卷分离
[root@master~]# service mysqld restart
[root@master~]# mysql
mysql>GRANT REPLICATIONSLAVE,REPLICATION CLIENT ON *.* TO repluser@‘192.168.1.%‘ IDENTIFIED BY‘replpass‘;
mysql>FLUSH PRIVILEGES;
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      107 |              |                  |
+-------------------+----------+--------------+------------------+
[root@master ~]# ls /mydata/binlogs/
master-bin.000001  master-bin.000002  master-bin.index
 
从服务器:
                   启用中继日志;(可选:关闭二进制日志,只读)
                   设置server-id;
                   启动复制线程;
从server-id      =2
从#log-bin=/mydata/binlogs/slave-bin 从库只启中继,不用二进制日志
从relay-log=/mydata/relaylogs/relay
从read-only = 1                    从库只读
从skip_slave_start                 跳过slave自启动,不让从库I/O线程和  SQL线程自启动
[root@slave~]# service mysqld restart
[root@slave~]# mysql  从库连接主库
mysql> CHANGE MASTER TO  MASTER_HOST=‘192.168.1.20‘, MASTER_USER=‘repluser‘,  MASTER_PASSWORD=‘replpass‘;
主从库都是空库,因为从当前主库二进制日志的开始位置复制,无需加master_log_file=‘master-bin.000002‘,master_log_pos=107起始位置参数,否则主库mysqldump>db.sql,从库关闭二进制日志并导入,再打开二志,从库连接主库的指定二志当前位置开始
mysql>START SLAVE; 启动从库复制进程
mysql>SHOW SLAVE STATUS\G;
***************************1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.1.20
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:master-bin.000002   读取主库二进制文件
          Read_Master_Log_Pos: 107               读取日志位置
               Relay_Log_File: relay.000003         写入中继日志文件
                Relay_Log_Pos: 254               写入中继日志位置
        Relay_Master_Log_File:master-bin.000002    从库复制主库哪个二进制日志
             Slave_IO_Running: Yes                主从复制I/O线程
            Slave_SQL_Running: Yes               主从复制SQL线程


 

 

4. 配置master(192.168.1.20)CA证书服务器

[root@master~]# ls /etc/pki/CA
certs  crl newcerts  private
[root@master~]# cd /etc/pki/CA
[root@masterCA]# (umask 077;openssl genrsa -out private/cakey.pem2048)
GeneratingRSA private key, 2048 bit long modulus
[root@masterCA]# openssl req -new -x509 -key private/cakey.pem -outcacert.pem -days 36500
You areabout to be asked to enter information that will be incorporated
intoyour certificate request.
What youare about to enter is what is called a Distinguished Name or a DN.
Thereare quite a few fields but you can leave some blank
For somefields there will be a default value,
If youenter ‘.‘, the field will be left blank.
-----
CountryName (2 letter code) [XX]:CN
State orProvince Name (full name) []:Shanghai
LocalityName (eg, city) [Default City]:Shanghai
OrganizationName (eg, company) [Default Company Ltd]:sina
OrganizationalUnit Name (eg, section) []:mysql
CommonName (eg, your name or your server‘s hostname) []:master.example.com
EmailAddress []:
[root@masterCA]# touch index.txt serial crlnumber
[root@masterCA]# echo 01 > serial
[root@masterCA]# ls
cacert.pem  certs crl  crlnumber  index.txt newcerts  private  serial

 

5.master(192.168.1.20)签发证书

[root@masterCA]# mkdir /usr/local/mysql/ssl
[root@masterCA]# cd /usr/local/mysql/ssl
[root@masterssl]# (umask 077;openssl genrsa -out master.key 2048)
GeneratingRSA private key, 2048 bit long modulus
[root@masterssl]# openssl req -new -key master.key -out master.csr-days 36500
You areabout to be asked to enter information that will be incorporated
intoyour certificate request.
What youare about to enter is what is called a Distinguished Name or a DN.
Thereare quite a few fields but you can leave some blank
For somefields there will be a default value,
If youenter ‘.‘, the field will be left blank.
-----
CountryName (2 letter code) [XX]:CN
State orProvince Name (full name) []:Shanghai
LocalityName (eg, city) [Default City]:Shanghai
OrganizationName (eg, company) [Default Company Ltd]:sina
OrganizationalUnit Name (eg, section) []:mysql
CommonName (eg, your name or your server‘s hostname) []:master.example.com
EmailAddress []:
Pleaseenter the following ‘extra‘ attributes
to besent with your certificate request
Achallenge password []:
Anoptional company name []:
[root@masterssl]# ls
master.csr  master.key
[root@masterssl]# openssl ca -in master.csr -out master.crt -days36500
Usingconfiguration from /etc/pki/tls/openssl.cnf
Checkthat the request matches the signature
Signatureok
CertificateDetails:
        Serial Number: 1 (0x1)
        Validity
            Not Before: May 23 06:41:59 2014GMT
            Not After : Apr 29 06:41:59 2114GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Shanghai
            organizationName          = sina
            organizationalUnitName    = mysql
            commonName                = master.example.com
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
                63:A4:FC:A2:E2:D4:10:7E:67:6A:92:5B:EF:D2:64:41:04:04:6C:0E
            X509v3 Authority Key Identifier:
               keyid:5B:F9:87:AE:B4:BA:65:07:F9:9E:89:19:A9:16:3B:D5:E7:53:2F:CC
Certificateis to be certified until Apr 29 06:41:59 2114 GMT (36500 days)
Sign thecertificate? [y/n]:y
1 out of1 certificate requests certified, commit? [y/n]y
Writeout database with 1 new entries
DataBase Updated

 

6.slave(192.168.1.20)生成证书申请请求

[root@slave~]# mkdir /usr/local/mysql/ssl
[root@slave~]# cd /usr/local/mysql/ssl
[root@slavessl]# (umask 077;openssl genrsa -out slave.key 2048)
GeneratingRSA private key, 2048 bit long modulus
[root@slavessl]# openssl req -new -key slave.key -out slave.csr-days 36500
You areabout to be asked to enter information that will be incorporated
intoyour certificate request.
What youare about to enter is what is called a Distinguished Name or a DN.
Thereare quite a few fields but you can leave some blank
For somefields there will be a default value,
If youenter ‘.‘, the field will be left blank.
-----
CountryName (2 letter code) [XX]:CN
State orProvince Name (full name) []:Shanghai
LocalityName (eg, city) [Default City]:Shanghai
OrganizationName (eg, company) [Default Company Ltd]:sina
OrganizationalUnit Name (eg, section) []:mysql
CommonName (eg, your name or your server‘s hostname) []:slave.example.com
EmailAddress []:
Pleaseenter the following ‘extra‘ attributes
to besent with your certificate request
Achallenge password []:
Anoptional company name []:
[root@slavessl]# scp slave.csr root@192.168.1.20:/root

 

7.slave(192.168.1.21)签发证书

[root@master~]# openssl ca -in slave.csr -out slave.crt -days 36500
Usingconfiguration from /etc/pki/tls/openssl.cnf
Checkthat the request matches the signature
Signatureok
CertificateDetails:
        Serial Number: 2 (0x2)
        Validity
            Not Before: May 23 06:55:25 2014GMT
            Not After : Apr 29 06:55:25 2114GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Shanghai
            organizationName          = sina
            organizationalUnitName    = mysql
            commonName                = slave.example.com
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
               19:64:2A:A8:8B:E3:B8:F8:67:4A:5F:4F:2F:CE:DC:A0:4C:06:60:F8
            X509v3 Authority Key Identifier:
                keyid:5B:F9:87:AE:B4:BA:65:07:F9:9E:89:19:A9:16:3B:D5:E7:53:2F:CC
Certificateis to be certified until Apr 29 06:55:25 2114 GMT (36500 days)
Sign thecertificate? [y/n]:y
1 out of1 certificate requests certified, commit? [y/n]y
Writeout database with 1 new entries
DataBase Updated
[root@master~]# scp slave.crt root@192.168.1.21:/usr/local/mysql/ssl/
[root@master~]# ls /usr/local/mysql/ssl/
master.crt  master.csr master.key
[root@master~]# ssh root@192.168.1.21 "ls /usr/local/mysql/ssl/"
slave.crt  slave.csr  slave.key

 

8.masterslave提供CA证书

[root@master~]# ls /etc/pki/CA
cacert.pem  certs crl  crlnumber  index.txt index.txt.attr index.txt.attr.old index.txt.old  newcerts  private serial  serial.old
[root@master~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@master~]# scp /etc/pki/CA/cacert.pem root@192.168.1.21:/usr/local/mysql/ssl/
[root@master~]# chown -R mysql.mysql /usr/local/mysql/ssl
[root@master~]# ll /usr/local/mysql/ssl
总用量 20
-rw-r--r--1 mysql mysql 1342 5月  23 15:04 cacert.pem
-rw-r--r--1 mysql mysql 4476 5月  23 14:42 master.crt
-rw-r--r--1 mysql mysql 1013 5月  23 14:39 master.csr
-rw-------1 mysql mysql 1679 5月  23 14:37 master.key
[root@master~]# ssh root@192.168.1.21 "chown -R mysql.mysql /usr/local/mysql/ssl"
[root@master~]# ssh root@192.168.1.21 "ls -l /usr/local/mysql/ssl"
总用量 20
-rw-r--r--1 mysql mysql 1342 5月  23 15:04 cacert.pem
-rw-r--r--1 mysql mysql 4471 5月  23 14:57 slave.crt
-rw-r--r--1 mysql mysql 1013 5月  23 14:51 slave.csr
-rw-------1 mysql mysql 1679 5月  23 14:50 slave.key

 

9.修改mysql配置文件开启SSL加密

[root@master~]# vim /etc/my.cnf
[mysqld]最后添加
ssl
ssl_ca =/usr/local/mysql/ssl/cacert.pem
ssl_key= /usr/local/mysql/ssl/master.key
ssl_cert= /usr/local/mysql/ssl/master.crt
[root@master~]# service mysqld restart
 
[root@slave~]# vim /etc/my.cnf
[mysqld]最后添加
ssl
ssl_ca =/usr/local/mysql/ssl/cacert.pem
ssl_key= /usr/local/mysql/ssl/slave.key
ssl_cert= /usr/local/mysql/ssl/slave.crt
[root@slave~]# service mysqld restart

 

10. master上验证开启SSL加密功能并创建基于密钥认证用户

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 replicationslave,replication client on *.* to ‘repluser‘@‘192.168.1.%‘ identified by‘replpass‘ require ssl;
mysql>flush privileges;
mysql>show master status;
+-------------------+----------+--------------+------------------+
|File              | Position |Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
|master-bin.000004 |      107 |              |                 |
+-------------------+----------+--------------+------------------+

 

11. 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   |
+---------------+----------------------------------+

 

12.slave重新连接master

mysql>stop slave;
mysql>change master to master_host=‘192.168.1.20‘, master_user=‘repluser‘, master_password=‘replpass‘, master_log_file=‘master-bin.000004‘,  master_log_pos=107,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>start slave; 
mysql>show slave status\G;
***************************1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.1.20
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:master-bin.000005
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay.000003
                Relay_Log_Pos: 254
        Relay_Master_Log_File:master-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: 547
              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: 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 inset (0.00 sec)

 

13.同步验证

13.1 master上新建数据库school

mysql>create database school;
mysql>show databases;
+--------------------+
|Database           |
+--------------------+
|information_schema |
|mysql              |
|performance_schema |
|school             |
|test               |
+--------------------+

13.2slave上验证自动复制成功

mysql>show databases;
+--------------------+
|Database           |
+--------------------+
|information_schema |
|mysql              |
|performance_schema |
|school             |
|test               |
+--------------------+


本文出自 “aha45” 博客,请务必保留此出处http://manfred12.blog.51cto.com/137312/1416410

基于SSL的Mysql主从复制,布布扣,bubuko.com

基于SSL的Mysql主从复制

上一篇:Nginx 1.5.2 + PHP 5.5.1 + MySQL 5.6.10 在 CentOS 下的编译安装


下一篇:如何写出高性能的SQL语句(转)