基于SSL的Mysql主从复制
浏览数：26 /
时间：2015年06月12日
一、实验目的：
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 [email protected] "yum -y install ntp" [root@master~]# service ntpd start [root@master~]# ssh [email protected] "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 [email protected] "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 [email protected]:/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 [email protected]:/usr/local/mysql/ssl/ [root@master~]# ls /usr/local/mysql/ssl/ master.crt master.csr master.key [root@master~]# ssh [email protected] "ls /usr/local/mysql/ssl/" slave.crt slave.csr slave.key
8.为master和slave提供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 [email protected]:/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 [email protected] "chown -R mysql.mysql /usr/local/mysql/ssl" [root@master~]# ssh [email protected] "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.2在slave上验证自动复制成功
mysql>show databases; +--------------------+ |Database | +--------------------+ |information_schema | |mysql | |performance_schema | |school | |test | +--------------------+
本文出自 “aha45” 博客，请务必保留此出处http://manfred12.blog.51cto.com/137312/1416410
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
