首页 > 代码库 > 基于ssl的mysql的主从复制
基于ssl的mysql的主从复制
实验环境：
system：CentOS Linux release 7.2.1511 (Core) mariadb server：mariadb-server-5.5.44-2.el7.centos.x86_64 master server：10.1.51.20/16 slave server：10.1.51.30/16 最先安装mariadb-server [root@localhost ~]# yum -y install mariadb-server
1、配置SSL
（1）master server上配置根CA
1）生成私钥文件
[root@localhost ~]# cd /etc/pki/CA/ [root@localhost CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
2）生成自签证书
[root@localhost 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) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:linuxpao Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server‘s hostname) []:www.linuxpao.vip Email Address []:admin@linuxpao.vip
3）创建所需的辅助文件
[root@localhost CA]# touch index.txt [root@localhost CA]# echo 01 > serial [root@localhost CA]# ls cacert.pem certs crl index.txt newcerts private serial
（2）为master server创建证书
1）创建私钥
[root@localhost ~]# mkdir /var/lib/mysql/ssl [root@localhost ~]# cd /var/lib/mysql/ssl/ [root@localhost ssl]# (umask 077;openssl genrsa -out ./master.key 2048)
2)生成证书申请文件
[root@localhost ssl]# openssl req -new -key master.key -out master.csr 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) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:linuxpao Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server‘s hostname) []:www.linuxpao.vip Email Address []:admin@linuxpao.vip Please enter the following ‘extra‘ attributes to be sent with your certificate request A challenge password []: An optional company name []:
（3）根CA签发master server的证书申请
[root@localhost 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: Nov 22 02:17:15 2016 GMT Not After : Nov 22 02:17:15 2017 GMT Subject: countryName = cn stateOrProvinceName = beijing organizationName = linuxpao organizationalUnitName = ops commonName = www.linuxpao.vip emailAddress = admin@linuxpao.vip X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: DC:5E:B2:B7:8F:1D:D0:FC:88:17:F5:01:B7:D7:2F:B0:8E:36:E4:5C X509v3 Authority Key Identifier: keyid:23:9E:40:8C:86:1E:4B:58:9D:94:EE:C8:FA:1B:BD:E6:BA:C5:87:C6 Certificate is to be certified until Nov 22 02:17:15 2017 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 [root@localhost ssl]# ls master.crt master.csr master.key
（4）为slave server上的SSL配置
1）生成私钥
[root@localhost ~]# mkdir /var/lib/mysql/ssl [root@localhost ~]# cd /var/lib/mysql/ssl/ [root@localhost ssl]# (umask 077;openssl genrsa -out slave.key 2048)
2）创建证书申请
[root@localhost ssl]# openssl req -new -key ./slave.key -out slave.csr 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) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:linuxpao Organizational Unit Name (eg, section) []:ops Common Name (eg, your name or your server‘s hostname) []:slave.linuxpao.vip Email Address []:salve@linuxpao.vip Please enter the following ‘extra‘ attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@localhost ssl]# scp slave.csr 10.1.51.20:/testdir（将证书申请文件拷贝到master server上）
（5）根CA（master server）上为从服务器签署证书
[root@localhost testdir]# openssl ca -in slave.csr -out slave.crt -days 365 [root@localhost testdir]# ls slave.crt slave.csr [root@localhost testdir]# scp slave.crt 10.1.51.30:/var/lib/mysql/ssl（将授权的证书拷贝回slave server上）
（6）将master server上的自检证书拷贝的到各服务器上
[root@localhost ssl]# cd /etc/pki/CA/ [root@localhost CA]# cp cacert.pem /var/lib/mysql/ssl/ [root@localhost CA]# scp cacert.pem 10.1.51.30:/var/lib/mysql/ssl/
（7）在master server上修改证书权限
[root@localhost CA]# cd /var/lib/mysql/ssl [root@localhost ssl]# chown -R mysql.mysql ./ [root@localhost ssl]# ll total 20 -rw-r--r--. 1 mysql mysql 1432 Nov 22 10:44 cacert.pem -rw-r--r--. 1 mysql mysql 4641 Nov 22 10:17 master.crt -rw-r--r--. 1 mysql mysql 1062 Nov 22 10:14 master.csr -rw-------. 1 mysql mysql 1679 Nov 22 10:12 master.key
（8）在slave server上修改证书权限
[root@localhost ssl]# chown -R mysql.mysql ./ [root@localhost ssl]# ll total 20 -rw-r--r--. 1 mysql mysql 1432 Nov 22 10:40 cacert.pem -rw-r--r--. 1 mysql mysql 4647 Nov 22 10:31 slave.crt -rw-r--r--. 1 mysql mysql 1062 Nov 22 10:26 slave.csr -rw-------. 1 mysql mysql 1675 Nov 22 10:24 slave.key
2、配置mariadb server
（1）分别编辑主从服务器的/etc/my.cnf文件
1）master server
在[mysqld]段后面添加如下内容 skip_name_resolve = ON innodb_file_per_table = ON log-bin = master-log server-id = 1 ssl ssl-ca = /var/lib/mysql/ssl/cacert.pem ssl-cert = /var/lib/mysql/ssl/master.crt ssl-key = /var/lib/mysql/ssl/master.key
2）slave server
在[mysqld]段后面添加如下内容 skip_name_resolve = ON innodb_file_per_table = ON relay-log = relay-log server-id = 2 ssl ssl-ca = /var/lib/mysql/ssl/cacert.pem ssl-cert = /var/lib/mysql/ssl/slave.crt ssl-key = /var/lib/mysql/ssl/slave.key
（2）在master server上授权复制的用户
[root@localhost ~]# systemctl start mariadb.service [root@localhost ~]# mysql MariaDB [(none)]> grant replication slave,replication client on *.* to ‘repluser‘@‘10.1.51.%‘ identified by ‘replpasswd‘ require ssl MariaDB [(none)]> flush privileges； MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000003 | 761 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
（3）在slave server上指定复制的主机
[root@localhost ssl]# systemctl start mariadb.service [root@localhost ssl]# mysql MariaDB [(none)]> change master to -> master_host=‘10.1.51.20‘, -> master_user=‘repluser‘, -> master_password=‘replpasswd‘, -> master_log_file=‘mysql-log.000003‘, -> master_log_pos=‘761‘, -> master_ssl=1, -> master_ssl_ca=‘/var/lib/mysql/ssl/cacert.pem‘, -> master_ssl_cert=‘/var/lib/mysql/ssl/slave.crt‘, -> master_ssl_key=‘/var/lib/mysql/ssl/slave.key‘; MariaDB [(none)]> start slave;
3、测试
1）在master server上可以看到ssl功能开启
MariaDB [(none)]> show global variables like ‘%ssl%‘; +---------------+-------------------------------+ | Variable_name | Value | +---------------+-------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /var/lib/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /var/lib/mysql/ssl/master.crt | | ssl_cipher | | | ssl_key | /var/lib/mysql/ssl/master.key | +---------------+-------------------------------+
2）在slave server上slave复制的状态
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.51.20 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-log.000004 Read_Master_Log_Pos: 245 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-log.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...剪切部分内容... Master_SSL_Allowed: Yes Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /var/lib/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /var/lib/mysql/ssl/slave.key ...剪切部分内容... Master_Server_Id: 1
3）在slave server上登录master server
[root@localhost ssl]# mysql -urepluser -h10.1.51.20 -preplpasswd --ssl-ca=/var/lib/mysql/ssl/cacert.pem --ssl-cert=/var/lib/mysql/ssl/slave.crt --ssl-key=/var/lib/mysql/ssl/slave.key MariaDB [(none)]> \s -------------- mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: Current user: repluser@10.1.51.30 SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server: MariaDB Server version: 5.5.44-MariaDB-log MariaDB Server Protocol version: 10 Connection: 10.1.51.20 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 1 hour 6 min 19 sec
4）在master serve上创建数据库mydb,然后再slave server上查看
创建
MariaDB [(none)]> create database mydb; Query OK, 1 row affected (0.02 sec)
查看
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | ssl | | test | +--------------------+ 6 rows in set (0.01 sec)
自此，基于ssl的mysql主从复制的实验已完成，新手实习，不足之处，望指正。
遇到的问题
问题1
由于粗心在slave server上指定master_host是，将ip地址一个的.号打成,号，导致如下错误（使用show slave status\G查看）
解决方法：
1）关闭slave server的slave复制功能
MariaDB [(none)]> stop slave
2）在master server上刷新日志
MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000004 | 245 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.01 sec)
3）在slave server上更新日志文件及位置，并重新启动
MariaDB [(none)]> change master to master_log_file=‘master-log.000004‘,master_log_pos=245; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec)
本文出自 “新手学Linux” 博客，请务必保留此出处http://kop309.blog.51cto.com/9034739/1875368
基于ssl的mysql的主从复制
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。