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

Data is the most important part for most companies, and MySQL servers use plaintext to transmit data by default when synchronizing data, so let's talk about the master-slave replication mode of MySQL ciphertext transmission based on SSL protocol.

Logical topology:

In the next experiment, the Master node server is not only the Master node database server, but also CA.

Environmental preparation:

1. Master-slave server time needs to be synchronized:

[root@node9 ~] # chronyc sources

210 Number of sources = 1

MS Name/IP address Stratum Poll Reach LastRx Last sample

=

^ * server.magelinux.com 37 377 82 + 71us [+ 148us] + /-100ms

[root@node10 ~] # chronyc sources

210 Number of sources = 1

MS Name/IP address Stratum Poll Reach LastRx Last sample

=

^ * server.magelinux.com 37 377 95 + 116us [+ 155us] + /-100ms

2. Build the CA environment on the master node node9:

[root@node9 ~] # cd / etc/pki/CA

[root@node9 CA] # touch index.txt serial

[root@node9 CA] # echo 01 > serial

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

[root@node9 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) []: BeiJing

Locality Name (eg, city) [Default City]: BeiJing

Organization Name (eg, company) [Default Company Ltd]: hisen

Organizational Unit Name (eg, section) []: Ops

Common Name (eg, your name or your server's hostname) []: ca.hisen.com

Email Address []: admin.com

3. The master node node9 generates a certificate application, which is signed by CA:

[root@node9 ~] # cd / var/lib/mysql/ssl/

[root@node9 ssl] # (umask 077 / OpenSSL genrsa-out master.key 2048)

[root@node9 ssl] # openssl req-new-key master.key-out master.csr-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) []: BeiJing

Locality Name (eg, city) [Default City]: BeiJing

Organization Name (eg, company) [Default Company Ltd]: hisen

Organizational Unit Name (eg, section) []: Ops

Common Name (eg, your name or your server's hostname) []: master.hisen.com

Email Address []: master.com

Please enter the following 'extra' attributes

To be sent with your certificate request

A challenge password []:

An optional company name []:

[root@node9 ssl] # openssl ca-in master.csr-out master.crt-days 2048

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 22 11:21:11 2017 GMT

Not After: Oct 2 11:21:11 2022 GMT

Subject:

CountryName = CN

StateOrProvinceName = BeiJing

OrganizationName = hisen

OrganizationalUnitName = Ops

CommonName = master.hisen.com

EmailAddress = master.com

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Netscape Comment:

OpenSSL Generated Certificate

X509v3 Subject Key Identifier:

2B:1D:F7:18:00:89:1B:CB:6D:09:59:4B:5E:03:78:BA:60:6A:62:BB

X509v3 Authority Key Identifier:

Keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

Certificate is to be certified until Oct 2 11:21:11 2022 GMT (2048 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

4. Generate a certificate from the node node10 and sign it by CA:

[root@node10 ~] # cd / var/lib/mysql/ssl

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

Generating RSA private key, 2048 bit long modulus

.. + +

.. +

E is 65537 (0x10001)

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

Generating RSA private key, 2048 bit long modulus

.. + +

.. +

E is 65537 (0x10001)

[root@node10 ssl] # openssl req-new-key slave.key-out slave.csr-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) []: BeiJing

Locality Name (eg, city) [Default City]: BeiJing

Organization Name (eg, company) [Default Company Ltd]: hisen

Organizational Unit Name (eg, section) []: Ops

Common Name (eg, your name or your server's hostname) []: slave.hisen.com

Email Address []: slave.com

Please enter the following 'extra' attributes

To be sent with your certificate request

A challenge password []:

An optional company name []:

[root@node10 ssl] # scp slave.csr 192.168.17.90:/root

[root@node9] # openssl ca-in 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: 2 (0x2)

Validity

Not Before: Feb 22 11:27:17 2017 GMT

Not After: Feb 20 11:27:17 2027 GMT

Subject:

CountryName = CN

StateOrProvinceName = BeiJing

OrganizationName = hisen

OrganizationalUnitName = Ops

CommonName = slave.hisen.com

EmailAddress = slave.com

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Netscape Comment:

OpenSSL Generated Certificate

X509v3 Subject Key Identifier:

68:31:D7:B1:03:5A:C0:6E:A3:58:4D:67:53:AC:F7:F5:1E:2A:19:4E

X509v3 Authority Key Identifier:

Keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

Certificate is to be certified until Feb 20 11:27:17 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@node9 ~] # scp slave.crt 192.168.17.100:/var/lib/mysql/ssl/

5. Copy the CA certificate of node9 to Master and Slave:

[root@node9 ~] # cp / etc/pki/CA/cacert.pem / var/lib/mysql/ssl/ # copy the CA certificate to local

[root@node9 ~] # scp / etc/pki/CA/cacert.pem 192.168.17.100:/var/lib/mysql/ssl/ # copy CA to node10

6. Modify the master group of the files under / var/lib/mysql/ssl/ and give them minimum permissions:

[root@node9] # chown-R mysql:mysql / var/lib/mysql/ssl/; chmod 600 / var/lib/mysql/ssl/*

[root@node10] # chown-R mysql:mysql / var/lib/mysql/ssl/; chmod 600 / var/lib/mysql/ssl/*

MySQL file configuration:

Master:

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Symbolic-links=0

Skip_name_resolve=ON

Innodb_file_per_table=1

Server_id=2

Log-bin=master-log

Ssl # enable SSL

Ssl_ca=/var/lib/mysql/ssl/cacert.pem # Master node CA certificate location

Ssl_cert=/var/lib/mysql/ssl/master.crt # Master Node Certificate

Ssl_key=/var/lib/mysql/ssl/master.key # Master Node key

Slave:

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Symbolic-links=0

Skip_name_resolve=ON

Innodb_file_per_table=ON

Server_id=3

Relay-log=relay-log

Read-only=1

Ssl # enable SSL

Ssl_ca=/var/lib/mysql/ssl/cacert.pem # Slave node CA certificate location

Ssl_cert=/var/lib/mysql/ssl/slave.crt # Slave Node Certificate

Ssl_key=/var/lib/mysql/ssl/slave.key # Slave Node key

MySQL service configuration:

Start the MySQL service and view the SSL information in MySQL:

[root@node9 ~] # systemctl start mariadb.service # start the MySQL service of node9

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% ssl%'; # View SSL information of node9

+-- +

| | 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 |

+-- +

7 rows in set (0.01 sec)

[root@node10 ssl] # systemctl start mariadb.service # start the MySQL service of node10

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% ssl%'; # View SSL information of node10

+-- +

| | 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/slave.crt |

| | ssl_cipher |

| | ssl_key | / var/lib/mysql/ssl/slave.key |

+-- +

7 rows in set (0.00 sec)

Second, the Master node authorizes a user who can use the SSL protocol to copy information, and tests the user:

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'userssl'@'192.168.17.%' IDENTIFIED BY' passwordssl' REQUIRE SSL

Query OK, 0 rows affected (0.00 sec) # authorizes a user who can only copy data through SSL

MariaDB [(none)] > FLUSH PRIVILEGES; # Refresh permissions

Query OK, 0 rows affected (0.00 sec)

[root@node9] # mysql-uuserssl-ppasswordssl-h292.168.17.90\

>-- ssl_ca=/var/lib/mysql/ssl/cacert.pem\

>-- ssl_cert=/var/lib/mysql/ssl/master.crt\

>-- ssl_key=/var/lib/mysql/ssl/master.key # tests whether users can log in using the SSL protocol

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 4

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)] >

3. Configure the Slave node and point to the Master node:

The binlog file currently being used by the Master node: master-log.000004, and the binlog location: 512

MariaDB [(none)] > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | master-log.000004 | 512 | |

+-+

1 row in set (0.00 sec)

Slave node configuration:

MariaDB [(none)] > CHANGE MASTER TO

-> MASTER_HOST='192.168.17.90'

-> MASTER_USER='userssl'

-> MASTER_PASSWORD='passwordssl'

-> MASTER_LOG_FILE='master-log.000004'

-> MASTER_LOG_POS=512

-> 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'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > START SLAVE; starts the SLAVE function

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > SHOW SLAVE STATUS\ G; View SLAVE status information

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.17.90

Master_User: userssl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-log.000004

Read_Master_Log_Pos: 512

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

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: 512

Relay_Log_Space: 818

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

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

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: 2

1 row in set (0.00 sec)

Fourth, test the master-slave synchronization data:

Master node:

MariaDB [(none)] > CREATE DATABASE hisendb; node9 master node to create hisendb database

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)] > USE hisendb

Database changed

MariaDB [hisendb] > CREATE TABLE friends (id INT UNSIGNED PRIMARY KEY NOT NULL,Name VARCHAR (20) NOT NULL,Age TINYINT,Gender ENUM)

Query OK, 0 rows affected (0.01 sec) # create the friends table in the hisendb database

MariaDB [hisendb] > INSERT INTO friends VALUES (1ZHI Xu He',25,'M'), (2LECH Xu Mingying',27,'F'), (3MJI Tian Tao',26,'M'), (4MIT LIANG Juntaoji 28M')

Query OK, 4 rows affected (0.00 sec) # insert data into the friends table

Records: 4 Duplicates: 0 Warnings: 0

MariaDB [hisendb] > SELECT * FROM friends; # View the results

+-- +

| | id | Name | Age | Gender | |

+-- +

| | 1 | Xu He | 25 | M |

| | 2 | Xu Mingying | 27 | F | |

| | 3 | Tian Tao | 26 | M |

| | 4 | Liang Juntao | 28 | M |

+-- +

4 rows in set (0.00 sec)

Slave node:

MariaDB [(none)] > USE hisendb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

MariaDB [hisendb] > SELECT * FROM friends; # can view the data written by the master node in the slave node

+-- +

| | id | Name | Age | Gender | |

+-- +

| | 1 | Xu He | 25 | M |

| | 2 | Xu Mingying | 27 | F | |

| | 3 | Tian Tao | 26 | M |

| | 4 | Liang Juntao | 28 | M |

+-- +

4 rows in set (0.00 sec)

As can be seen from the above, the master and slave have completed the data replication based on SSL protocol.

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.