一、实验目的：
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.6023 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/datadefault_storage_engine = innodbinnodb_file_per_table = 1sync_binlog = 1# Trynumber of CPU's*2 for thread_concurrencythread_concurrency= 2binlog_format=mixed配置主服务器1.创建具有复制权限的用户帐号；2. 设置server-id；3. 启用二进制日志；主server-id = 1主log-bin=/mydata/binlogs/master-bin二进制日志和中继日志一定要绝对路径，为安全必须挂载另外卷与数据卷分离[root@master~]# service mysqld restart[root@master~]# mysqlmysql>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/CAcerts 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 36500You areabout to be asked to enter information that will be incorporatedintoyour 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 blankFor somefields there will be a default value,If youenter '.', the field will be left blank.-----CountryName (2 letter code) [XX]:CNState orProvince Name (full name) []:ShanghaiLocalityName (eg, city) [Default City]:ShanghaiOrganizationName (eg, company) [Default Company Ltd]:sinaOrganizationalUnit Name (eg, section) []:mysqlCommonName (eg, your name or your server's hostname) []:master.example.comEmailAddress []:[root@masterCA]# touch index.txt serial crlnumber[root@masterCA]# echo 01 > serial[root@masterCA]# lscacert.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 36500You areabout to be asked to enter information that will be incorporatedintoyour 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 blankFor somefields there will be a default value,If youenter '.', the field will be left blank.-----CountryName (2 letter code) [XX]:CNState orProvince Name (full name) []:ShanghaiLocalityName (eg, city) [Default City]:ShanghaiOrganizationName (eg, company) [Default Company Ltd]:sinaOrganizationalUnit Name (eg, section) []:mysqlCommonName (eg, your name or your server's hostname) []:master.example.comEmailAddress []:Pleaseenter the following 'extra' attributesto besent with your certificate requestAchallenge password []:Anoptional company name []:[root@masterssl]# lsmaster.csr master.key[root@masterssl]# openssl ca -in master.csr -out master.crt -days36500Usingconfiguration from /etc/pki/tls/openssl.cnfCheckthat the request matches the signatureSignatureokCertificateDetails: 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:CCCertificateis to be certified until Apr 29 06:41:59 2114 GMT (36500 days)Sign thecertificate? [y/n]:y1 out of1 certificate requests certified, commit? [y/n]yWriteout database with 1 new entriesDataBase 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 36500You areabout to be asked to enter information that will be incorporatedintoyour 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 blankFor somefields there will be a default value,If youenter '.', the field will be left blank.-----CountryName (2 letter code) [XX]:CNState orProvince Name (full name) []:ShanghaiLocalityName (eg, city) [Default City]:ShanghaiOrganizationName (eg, company) [Default Company Ltd]:sinaOrganizationalUnit Name (eg, section) []:mysqlCommonName (eg, your name or your server's hostname) []:slave.example.comEmailAddress []:Pleaseenter the following 'extra' attributesto besent with your certificate requestAchallenge 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 36500Usingconfiguration from /etc/pki/tls/openssl.cnfCheckthat the request matches the signatureSignatureokCertificateDetails: 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:CCCertificateis to be certified until Apr 29 06:55:25 2114 GMT (36500 days)Sign thecertificate? [y/n]:y1 out of1 certificate requests certified, commit? [y/n]yWriteout database with 1 new entriesDataBase 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.为master和slave提供CA证书
[root@master~]# ls /etc/pki/CAcacert.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]最后添加sslssl_ca =/usr/local/mysql/ssl/cacert.pemssl_key= /usr/local/mysql/ssl/master.keyssl_cert= /usr/local/mysql/ssl/master.crt[root@master~]# service mysqld restart [root@slave~]# vim /etc/my.cnf[mysqld]最后添加sslssl_ca =/usr/local/mysql/ssl/cacert.pemssl_key= /usr/local/mysql/ssl/slave.keyssl_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~]# 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 |+---------------+----------------------------------+
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: 0Master_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: 11 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.2在slave上验证自动复制成功
mysql>show databases;+--------------------+|Database |+--------------------+|information_schema ||mysql ||performance_schema ||school ||test |+--------------------+
本文出自 “aha45” 博客，请务必保留此出处http://manfred12.blog.51cto.com/137312/1416410