Shulou(Shulou.com)06/01 Report--

The following mainly brings you how mysql can achieve master-slave replication through SSL. I hope these contents can bring you practical use. This is also the main purpose of how I edit mysql to achieve master-slave replication through SSL. All right, don't talk too much nonsense, let's just read the following.

Experimental environment:

Node1:192.168.4.61

Node2:192.168.4.62

CA:192.168.4.63

Node1 and node2 time synchronization

[root@node1~] # ntpdate 172.18.0.1

[root@node2~] # ntpdate 172.18.0.1

[root@node1~] # vim / etc/chrony.conf # node1 is the same as node2 operation

[root@node1~] # systemctl start chronyd.service # start the service

Node1 and node2 are connected based on key

[root@node1~] # ssh-keygen

[root@node1~] # ssh-copy-id-I / root/.ssh/id_rsa.pub 192.168.4.6 copy the public key to node2

The node2 operation is the same as the node1 operation.

Modify the hosts file

Node1 and node2 have the same configuration

[root@node1~] # vim / etc/hosts

3 192.168.4.61 node1 4 192.168.4.62 node2 ensure that iptables and selinuxnode1 and node2 are closed to install the mariadb database

[root@node1~] # yum install-y mariadb-server

[root@node2~] # yum install-y mariadb-server

Configure node1 as the primary CVM

[root@node1~] # vim / etc/my.cnf.d/server.cnf

[root@node1~] # systemctl start mariadb # start the mariadb service

MariaDB [(none)] > GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *. * TO 'joah'@'192.168.4.62' IDENTIFIED BY' 123456log; # authorize the user to check whether binary log MariaDB [(none)] > SHOW VARIABLES LIKE'% log is enabled

Binary log status information MariaDB [(none)] > SHOW MASTER STATUS on node1

Configure node2 slave server

[root@node2~] # vim / etc/my.cnf.d/server.cnf

[root@node2~] # systemctl start mariadb # start the mariadb service

MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.4.61',MASTER_USER='joah',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=417; # set connection to master server to launch slave MariaDB [(none)] > START SLAVE; launch IO thread and SQL thread MariaDB [(none)] > START SLAVE IO_THREAD,SQL_THREAD View the slave CVM status MariaDB [(none)] > START SLAVE IO_THREAD,SQL_THREAD

Test whether master-slave replication has been implemented

Realize SSL function and build CA CVM

[root@CA/etc/pki/CA] # touch index.txt

[root@CA/etc/pki/CA] # echo 01 > serial

Generate key file

[root@CA/etc/pki/CA] # (umask 077 / OpenSSL genrsa-out private/cakey.pem 2048)

Generate self-signed certificate

[root@CA/etc/pki/CA] # openssl req-new-x509-key private/cakey.pem-out cacert.pem-days 3650

Node1 generates certificates

[root@node1~] # mkdir / etc/mysql/ssl-pv

[root@node1~] # chown mysql.mysql / etc/mysql/ssl/-R

[root@node1/etc/mysql/ssl] # (umask 077 / OpenSSL genrsa-out master.key 2048) [root@node1/etc/mysql/ssl] # openssl req-new-key master.key-out master.csr

[root@node1/etc/mysql/ssl] # scp master.csr 192.168.4.63:/etc/pki/CA/newcerts/

[root@CA/etc/pki/CA/newcerts] # openssl ca-in master.csr-out master.crt-days 365

[root@CA/etc/pki/CA/newcerts] # scp master.crt.. / cacert.pem 192.168.4.61:/etc/mysql/ssl

[root@node1~] # vim / etc/my.cnf.d/server.cnf

[root@node1~] # systemctl restart mariadb

Check whether the SSL function is enabled

Node2 generates certificates

[root@node2~] # mkdir / etc/mysql/ssl-pv

[root@node2~] # chown mysql.mysql / etc/mysql/ssl/-R

[root@node2/etc/mysql/ssl] # (umask 077 / OpenSSL genrsa-out slave.key 2048)

[root@node2/etc/mysql/ssl] # openssl req-new-key slave.key-out slave.csr

[root@node2/etc/mysql/ssl] # scp slave.csr 192.168.4.63:/etc/pki/CA/newcerts

[root@CA/etc/pki/CA/newcerts] # openssl ca-in slave.csr-out slave.crt-days 365

[root@CA/etc/pki/CA/newcerts] # scp slave.crt.. / cacert.pem 192.168.4.62:/etc/mysql/ssl

[root@node2/etc/mysql/ssl] # vim / etc/my.cnf.d/server.cnf

Restart the service

[root@node2/etc/mysql/ssl] # systemctl restart mariadb

Node1 Authorization MariaDB [(none)] > GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *. * TO 'joah'@'192.168.4.62' IDENTIFIED BY' 123456 'REQUIRE SSL; Test based on SSL connection

[root@node2~] # mysql-ujoah-p123456-h292.168.4.61-- ssl

Node2 connects to the primary CVM to copy MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.4.61',MASTER_USER='joah',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000008',MASTER_LOG_POS=429,MASTER_SSL=1,MASTER_SSL_CA='/etc/mysql/ssl/cacert.pem',MASTER_SSL_CERT='/etc/mysql/ssl/slave.crt',MASTER_SSL_KEY='/etc/mysql/ssl/slave.key' with ssl MariaDB [(none)] > START SLAVE; # launch slave CVM MariaDB [(none)] > SHOW SLAVE STATUS\ G

Summary

(1) if you have correctly added the certificate, but still do not start the SSL function after startup, you may not have permission.

[root@node2~] # chown mysql.mysql-R / etc/mysql/ssl

(2) if there is a problem in the picture, just stop slave.

(3) in each process, you need to verify whether it is successful or not and then do the following.

For the above about how mysql through SSL to achieve master-slave replication, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.