一、配置openssl
1、 在master server(node0)上根CA的搭建(及生成自签名证书)
[root@node0 ~]# cd /etc/pki/CA/ [root@node0 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048) Generating RSA private key, 2048 bit long modulus .................+++ ..................+++ e is 65537 (0x10001)
[root@node0 CA]# touch index.txt [root@node0 CA]# echo 01 > serial
[root@node0 CA]# openssl req -new -x509 -key private/cakey.pem -out ./cacert.pem -days 3650 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) []:BJ Locality Name (eg, city) [Default City]:BJ Organization Name (eg, company) [Default Company Ltd]:rj Organizational Unit Name (eg, section) []:rj Common Name (eg, your name or your server's hostname) []:www.rj.com Email Address []:
2、为master主机生成密钥，并名为之签名
[root@node0 CA]# cd /etc/my.cnf.d/ [root@node0 my.cnf.d]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus ...............................................+++ ................+++ e is 65537 (0x10001)
[root@node0 my.cnf.d]# openssl req -new -key /etc/my.cnf.d/master.key -out /etc/my.cnf.d/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) []:BJ Locality Name (eg, city) [Default City]:BJ Organization Name (eg, company) [Default Company Ltd]:rj Organizational Unit Name (eg, section) []:rj Common Name (eg, your name or your server's hostname) []:www.rj.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
3、根CA签发master请求
[root@node0 my.cnf.d]# openssl ca -in master.csr -out master.crt -days 3650
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: Feb 16 06:55:06 2017 GMT
Not After : Feb 14 06:55:06 2027 GMT
Subject:
countryName = CN
stateOrProvinceName = BJ
organizationName = rj
organizationalUnitName = rj
commonName = www.rj.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
B4:A1:66:8C:5B:2B:F9:59:9D:F6:4F:F7:35:72:E2:87:9C:A5:95:F9
X509v3 Authority Key Identifier:
keyid:71:DD:03:78:51:12:5F:58:C2:1B:53:76:A0:2B:E9:BF:60:D8:67:36
Certificate is to be certified until Feb 14 06:55:06 2027 GMT (3650 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
3、为slave结点(node1)配置openssl
[root@node1 CA]# cd /etc/my.cnf.d/ [root@node1 my.cnf.d]# (umask 077;openssl genrsa -out slave.key 2048) Generating RSA private key, 2048 bit long modulus .............+++ ......................................................................................................................................................................................................................................................+++ e is 65537 (0x10001)
[root@node1 my.cnf.d]# 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) []:BJ Locality Name (eg, city) [Default City]:BJ Organization Name (eg, company) [Default Company Ltd]:rj Organizational Unit Name (eg, section) []:rj Common Name (eg, your name or your server's hostname) []:www.rj.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
[root@node1 my.cnf.d]# scp slave.csr node0:/tmp The authenticity of host 'node0 (172.16.23.10)' can't be established. ECDSA key fingerprint is 2b:98:49:35:5b:78:24:ed:f0:ab:fa:54:b1:8e:df:29. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'node0,172.16.23.10' (ECDSA) to the list of known hosts. root@node0's password: slave.csr
4、为slave结点签发请求
[root@node0 my.cnf.d]# mv /etc/pki/CA/serial /root/
[root@node0 my.cnf.d]# mv /etc/pki/CA/index.txt /root/
[root@node0 my.cnf.d]# touch /etc/pki/CA/index.txt
[root@node0 my.cnf.d]# echo 01 > /etc/pki/CA/serial
[root@node0 my.cnf.d]# openssl ca -in /tmp/slave.csr -out slave.crt -days 3650
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: Feb 16 07:07:14 2017 GMT
Not After : Feb 14 07:07:14 2027 GMT
Subject:
countryName = CN
stateOrProvinceName = BJ
organizationName = rj
organizationalUnitName = rj
commonName = www.rj.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
A5:FE:11:EB:4D:B5:F1:85:61:E7:18:E3:1D:B7:25:C6:1B:24:97:AF
X509v3 Authority Key Identifier:
keyid:71:DD:03:78:51:12:5F:58:C2:1B:53:76:A0:2B:E9:BF:60:D8:67:36
Certificate is to be certified until Feb 14 07:07:14 2027 GMT (3650 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@node0 my.cnf.d]# scp slave.crt node1:/etc/my.cnf.d/ slave.crt 100% 4382 4.3KB/s 00:00
此时主从结点的证书都已经准备好
二、配置mariadb主从服务器
1、工作拓扑图
2、主服务器配置
[root@node0 ~]# yum -y install mariadb
[root@node0 my.cnf.d]# vim /etc/my.cnf
将mysqld段中的配置修改为以下内容
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 innodb-file-per-table = ON skip-name-resolve = ON server-id = 1 log-bin = master-log ssl ssl_ca=/etc/my.cnf.d/cacert.pem ssl_cert=/etc/my.cnf.d/master.crt ssl_key=/etc/my.cnf.d/master.key
[root@node0 my.cnf.d]# cp /etc/pki/CA/cacert.pem . [root@node0 my.cnf.d]# chown mysql.mysql cacert.pem master.*
[root@node0 my.cnf.d]# systemctl start mariadb
3、从服务配置
[root@node0 ~]# yum -y install mariadb
[root@node0 my.cnf.d]# vim /etc/my.cnf
将mysqld段中的配置修改为以下内容
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 skip_name_resolve = ON relay-log = relay-log server-id = 2 注id号不能与主服务的一样 ssl ssl-ca = /etc/my.cnf.d/cacert.pem ssl-cert = /etc/my.cnf.d/slave.crt ssl-key = /etc/my.cnf.d/slave.key
[root@node0 my.cnf.d]# scp /etc/pki/CA/cacert.pem node1:/etc/my.cnf.d/ cacert.pem [root@node1 my.cnf.d]# cd /etc/my.cnf.d/ && chown mysql.mysql cacert.pem slave.*
[root@node1 my.cnf.d]# systemctl start mariadb
4、主服务器授权一个用户可连接mysql拉取二进制文件
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rj'@'172.16.23.11' IDENTIFIED BY 'centos.123' REQUIRE ssl;
5、配置从服务器连接到主服务器，并拉取数据
先查看主结点的二进制日志
MariaDB [(none)]> SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-log.000007 | 342 | | |+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.23.10', MASTER_USER='rj', MASTER_PASSWORD='centos.123', MASTER_LOG_FILE='master-log.000007', MASTER_LOG_POS=245, MASTER_SSL=1, MASTER_SSL_CA='/etc/my.cnf.d/cacert.pem', MASTER_SSL_CERT='/etc/my.cnf.d/slave.crt', MASTER_SSL_KEY='/etc/my.cnf.d/slave.key'; Query OK, 0 rows affected (0.03 sec)
查看主从服务器的openssl的是用否用
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/master.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/master.key | +---------------+--------------------------+
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/slave.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/slave.key | +---------------+--------------------------+
6、启用从服务器
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.23.10
Master_User: rj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000007
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000007
Slave_IO_Running: Yes 注:IO SQL这两项表示主从同步已经正学进行
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: 245
Relay_Log_Space: 818
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/my.cnf.d/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/my.cnf.d/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /etc/my.cnf.d/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 in set (0.00 sec)
7、由于从服务器只能读，所以需要开启mariadb的只读
MariaDB [(none)]> SHOW VARIABLES LIKE 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> SET GLOBAL read_only=ON; Query OK, 0 rows affected (0.00 sec)
8、openssl进行测试
[root@node1 my.cnf.d]# mysql -urj -pcentos.123 -h172.16.23.10 --ssl-ca=/etc/my.cnf.d/cacert.pem --ssl-cert=/etc/my.cnf.d/slave.crt --ssl-key=/etc/my.cnf.d/slave.key Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7 Server version: 5.5.44-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)
9、主从同步测试
MariaDB [(none)]> CREATE DATABASE node0create;在主服务上创建了一个库 Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES; 在从服务器上也可以查看到了 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | node0create | | performance_schema | | test | +--------------------+
原创文章，作者：renjin，如若转载，请注明出处：http://www.178linux.com/69467
