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

When mysql replicates across the Internet, others can steal the replication information of mysql, which is plaintext, so there is insecurity. Here, the copied information is encrypted through ssl.

Prepare the environment:

1. System environment: Centos6.5

two。 Database version: 5.5.36-MariaDB-log MariaDB Server

3.Host:

Master host: master.samlee.com 172.16.100.7

Slave host: slave.samlee.com 172.16.100.8

Public host: public.samlee.com 172.16.100.9

-

The implementation process is as follows:

Operations on the Public host:

1. Create a certificate authority:

-- create a certificate authority on the public host # cd / etc/pki/CA/-- to generate the private key # (umask 077 / OpenSSL genrsa-out / etc/pki/CA/private/cakey.pem 2048)

To generate a self-signed certificate, you need to enter a large amount of user information, so edit the configuration file of the certificate and create the certificate on the private CA. Note that all user information should be the same as that in the CA, from the country to the department, otherwise the certificate will not be available.

-- modify the configuration file according to the experimental requirements as follows: # vim / etc/pki/tls/openssl.cnf [req_distinguished_name] countryName_default = CNstateOrProvinceName_default = GUANGDONGlocalityName_default = GUANGZHOU0.organizationName_default = JUST.LTDorganizationalUnitName_default = IT

Generate self-signed certificate-- self-build CA certificate as CAserver

# openssl req-new-x509-key / etc/pki/CA/private/cakey.pem-out / etc/pki/CA/cacert.pem-days 3650Country Name (2 letter code) [CN]: State or Province Name (full name) [GUANGDONG]: Locality Name (eg, city) [GUANGZHOU]: Organization Name (eg, company) [JUST.LTD]: Organizational Unit Name (eg, section) [IT]: Common Name (eg, your name or your server's hostname) []: public.samlee.com

Create a certificate number

# touch {index.txt,serial} # echo 01 > serial

Generate key, certificate request, certificate for Master server:

-- create the private key # cd / etc/pki/CA/# (umask 077 Openssl genrsa-out master.key 2048)-- generate certificate request # openssl req-new-key master.key-out master.csrCountry Name (2 letter code) [CN]: State or Province Name (full name) [GUANGDONG]: Locality Name (eg, city) [GUANGZHOU]: Organization Name (eg, company) [JUST.LTD]: Organizational Unit Name (eg, section) [IT]: Common Name (eg) Your name or your server's hostname) []: master.samlee.com-- issues master's certificate on the certificate server # openssl ca-in master.csr-out master.crt-days 3650Using configuration from / etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details: Serial Number: 1 (0x1) Validity Not Before: Jun 23 00:09:31 2016 GMT Not After: Jun 21 00:09: 31 2026 GMT Subject: countryName = CN stateOrProvinceName = GUANGDONG organizationName = JUST.LTD organizationalUnitName = IT commonName = master.samlee.comData Base Updated-seeing this indicates that the certificate has been issued successfully

Generate key, certificate request, certificate for Slave server:

-- create the private key # cd / etc/pki/CA/# (umask 077 Openssl genrsa-out slave.key 2048)-- generate certificate request # openssl req-new-key slave.key-out slave.csrCountry Name (2 letter code) [CN]: State or Province Name (full name) [GUANGDONG]: Locality Name (eg, city) [GUANGZHOU]: Organization Name (eg, company) [JUST.LTD]: Organizational Unit Name (eg, section) [IT]: Common Name (eg) Your name or your server's hostname) []: slave.samlee.com-- issues master's certificate on the certificate server # openssl ca-in slave.csr-out slave.crt-days 3650Using configuration from / etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate Details: Serial Number: 2 (0x2) Validity Not Before: Jun 23 00:18:21 2016 GMT Not After: Jun 21 00:18: 21 2026 GMT Subject: countryName = CN stateOrProvinceName = GUANGDONG organizationName = JUST.LTD organizationalUnitName = IT commonName = slave.samlee.comData Base Updated-seeing this indicates that the certificate has been issued successfully

Operations on Master and Slavr hosts:

two。 Install MariaDB on Master hosts and Slave hosts

Use the software installation package mariadb-5.5.36-linux-x86_64.tar.gz, upload it to the root directory, install and configure Mairadb, and set the mydata directory as the data and log storage directory; the following steps are consistent on node1 and node2

# cd / root# tar-xzf mariadb-5.5.36-linux-x86_64.tar.gz-C / usr/local/# ln-sv mariadb-5.5.36-linux-x86_64 mysql# groupadd=mysql# useradd=mysql-g mysql# mkdir / mydata/ {data,binlog Relaylog}-pv# chown-R mysql.mysql / mydata/* # mkdir / etc/mysql# cp / usr/local/mysql/support-files/my-large.cnf / etc/mysql/my.cnf modify / etc/mysql/my.cnf# vim / etc/mysql/my.cnf the last line adds the following statement datadir=/mydata# chown-R mysql.mysql / etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > / etc/profile.d/mysql .sh # source / etc/profile.d/mysql.sh# mkdir / var/lib/mysql/# chown-R mysql.mysql / var/lib/mysql/# cd / usr/local/mysql# cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld# chmod + x / etc/init.d/mysqld# scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data# service mysqld restart

Operations on the Master host:

3. Configure the Master host as the primary node (primary server)

-- create a binary log storage directory and grant directory permissions to # mkdir-pv / mydata/binglogs# chown-R mysql.mysql / mydata/binglogs-- configuration my.cnf setting Master master node server-id = 1log-bin=/mydata/binglogs/master-binlog

Operations on the Slave host:

4. Configure the Slave host as a slave node (slave server)

-- create a relay log storage directory and grant directory permissions to # mkdir-pv / mydata/relaylogs# chown-R mysql.mysql / mydata/relaylogs-- configuration my.cnf settings Master master node server-id = 11relay-log=/mydata/relaylogs/relay-bin

Operations on the Master host:

Create a user with replication permissions

-- create a user with replication permission in Master, support SSL authentication, create a new user repluser@'172.16.100.%', password replpass, and authorize all access rights MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'172.16.%.%' IDENTIFIED BY' replpass' REQUIRE ssl;MariaDB [(none)] > FLUSH PRIVILEGES;MariaDB [(none)] > SHOW MASTER STATUS +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + -+ | master-binlog.000001 | 684 | + -+

5. Configure Master hosts and Slave hosts to support SSL authentication

Operations on the Master host:

Configure Master hosts to support SSL authentication:

-create a folder to store the ca certificate and Master's own key+ certificate # mkdir / etc/mysql/ssl-- to obtain the CA certificate and its own certificate from the Public host # scp root@172.16.100.9:/etc/pki/CA/private/cakey.pem / etc/mysql/ssl/# scp root@172.16.100.9:/etc/pki/CA/cacert.pem / etc/mysql/ssl/# scp root@172.16.100.9:/ Etc/pki/CA/master.key / etc/mysql/ssl/# scp root@172.16.100.9:/etc/pki/CA/master.crt / etc/mysql/ssl/# chown-R mysql.mysql / etc/mysql/ssl-- modify the configuration file of mysql Enable mysql to support ssl mode # vim / etc/mysql/my.cnfssl enable SSL function ssl-ca = / etc/mysql/ssl/cacert.pem specify CA file location ssl-cert = / etc/mysql/ssl/master.crt specify certificate file location ssl-key = / etc/mysql/ssl/master.key specify key location-restart mysql service # service mysqld restart-- query whether SSL related variables start mysql > SHOW GLOBAL VARIABLES LIKE'% ssl%' +-+-+ | Variable_name | Value | +-+-+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | / etc/mysql/ssl/cakey.pem | | ssl_capath | ssl_cert | / etc/mysql/ssl/master.crt | | ssl_cipher | | ssl_key | / etc/mysql/ssl/master.key | +-| -+

Operations on the Slave host:

Configure Slave hosts to support SSL authentication:

-create a folder to store the ca certificate and Master's own key+ certificate # mkdir / etc/mysql/ssl-- to obtain the CA certificate and its own certificate from the Public host # scp root@172.16.100.9:/etc/pki/CA/private/cakey.pem / etc/mysql/ssl/# scp root@172.16.100.9:/etc/pki/CA/cacert.pem / etc/mysql/ssl/# scp root@172.16.100.9:/ Etc/pki/CA/slave.key / etc/mysql/ssl/# scp root@172.16.100.9:/etc/pki/CA/slave.crt / etc/mysql/ssl/# chown-R mysql.mysql / etc/mysql/ssl-- modify the configuration file of mysql Enable mysql to support ssl mode # vim / etc/mysql/my.cnfssl enable SSL function ssl-ca = / etc/mysql/ssl/cacert.pem specify CA file location ssl-cert = / etc/mysql/ssl/slave.crt specify certificate file location ssl-key = / etc/mysql/ssl/slave.key specify key location-restart mysql service # service mysqld restart-- query whether SSL related variables start mysql > SHOW VARIABLES LIKE'% ssl%' +-+-+ | Variable_name | Value | +-+-+ | have_openssl | YES | | have_ Ssl | YES | | ssl_ca | / etc/mysql/ssl/cakey.pem | | ssl_capath | ssl_cert | / etc/mysql/ssl/slave.crt | | ssl_cipher | | ssl_key | / etc/mysql/ssl/slave.key | +- +-+

6.Slave accesses the Master master server through a SSL remote connection

# mysql-urepluser-preplpass-h272.16.100.7-ssl-ca=/etc/mysql/ssl/cacert.pem-- ssl-cert=/etc/mysql/ssl/slave.crt-- ssl-key=/etc/mysql/ssl/slave.key

7.Slave connects to the database in ssl and sets the default to support master-slave replication

Mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',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',MASTER_LOG_FILE='master-binlog.000005',MASTER_LOG_POS=245 > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.16.100.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-binlog.000005 Read_Master_Log_Pos: 245 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 533 Relay_Master_Log_File: master-binlog.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 : 245 Relay_Log_Space: 821 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: / etc/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: / etc/ Mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: / etc/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: 1

Connect and access the database Master master node server by ssl through "Navicat for MySQL" under 8.Windows

Store several files under node1/etc/mysql/ssl on windows, where I put them in the ssl directory under my H drive.

Note that when you select a certificate and key in ssl, you need to click "use Verification" first, and then check "use Verification" when the selection is finished.

The configuration is as follows:

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.