Use openssl in linux to implement mysql master-slave replication and opensslmysql

Use openssl in linux to implement mysql master-slave replication and opensslmysql

Certificate preparation:

CA certificate:

Step 1: Create a CA private key

[root@localhost CA]# (umask 066;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)

Step 2: generate self-signed documents

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

-----------------------------

Prepare the private key and certificate application file for mysql:

Step 1: Create a mysql private key:

[root@localhost ~]# (umask 066;openssl genrsa -out /var/lib/mysql/ssl/mysql.key 2048)

Step 2: generate a certificate application file and send it to the CA Server

[root@localhost ~]# openssl req -new -key /var/lib/mysql/ssl/mysql.key -days 365 -out /var/lib/mysql/ssl/mysql.csr

Note: The country, province, and company name must be the same as that of CA.

Send the certificate application file to the CA Server

-----------------------------

Issue a certificate on the CA Server:

[root@localhost CA]# openssl ca -in /tmp/mysql.csr -out /tmp/mysql.crt -days 365

Attached the command to view the information in the certificate:

openssl x509 -in /PATH/FROM/CERT_FILE -noout -text|subject|serial|dates

-----------------------------

Send the certificate to the mysql server

And send CA self-signed certificates to the slave server.

Certificate preparation ends here

To implement master-slave Replication Based on the ssl function, both the master and slave sides must verify each other, that is, the slave server must also have its own certificate.

Therefore, generate the slave server certificate according to the above process.

========================================================== ==========

Configure the mysql server:

On the master server side, view the ssl-related parameters and the configuration items of master-slave replication-master server:

MariaDB [(none)]> show variables like ‘%ssl%';

Because the ssl function configuration item is a global configuration parameter, edit the/etc/my. cnf file:

Because the server is verified by the client, you only need to configure ssl_cert (certificate location on the mysql server), ssl_key (mysql Private Key location), and ssl_ca (CA certificate location ).

Enable the service and check:

-----------------------------

Create an ssl-based account that is used by the slave server to copy the database of the mysql master server:

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘slave1'@'10.1.35.25' IDENTIFIED BY ‘passwd' REQUIRE ssl ;    

View the binlog location and record it for slave Server Configuration:

MariaDB [(none)]> show master logs;

========================================================== ==========

Slave Server Configuration:

Edit/etc/my. cnf. Because it is a slave server, you need to enable relay_log, and server_id should not be the same as the mysql server at the same level.

Start the mysql service and check:

-----------------------------

The configuration slave server points to the master server (this is a global configuration parameter, but it is not recommended to write it in the configuration file/etc/my. in cnf, if slave goes down unexpectedly, the replication function is automatically started when mysql is started again to check data integrity, which is not conducive to troubleshooting errors)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.35.1′,MASTER_USER='slave1′,MASTER_PASSWORD='passwd',MASTER_LOG_FILE=' master-log.000025 ‘,MASTER_LOG_POS= 245 , 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;

========================================================== ==========

Verify the Master/Slave effect:

-----------------------------

Master server create database mydb

-----------------------------

View from the server:

========================================================== ==========

Summary:

Notes during replication:

1. Set the slave service to "read-only" (No need to set the dual-master mode)

Start read_only on the slave server, but only valid for users with non-SUPER permissions;

Stop all users:

mysql> FLUSH TABLES WITH READ LOCK;

2. Ensure transaction security during replication as much as possible

Enable parameters on the master node:

Sync_binlog = ON # when a transaction is committed, the binary log time is re-stored to the disk immediately, which ensures that the slave server can receive events immediately and protect local data security.

If InnoDB Storage engine is used:

Innodb_flush_logs_at_trx_commit = ON # immediately fl transaction logs from memory to disk when the transaction is committed

Innodb_support_xa = ON # support for distributed transactions

# When these two items are started, the slave server can immediately obtain the latest events of the master server to a certain extent.

3. Avoid starting the replication thread automatically when the slave server is accidentally suspended.

Because data is important and the unexpected termination time of the server is strange, do not enable the replication thread automatically from the server after the termination, and enable the replication thread only after manual troubleshooting; an exception may occur when the replication thread is replicated to half of the thread. The replication thread may not have this function-I don't know how to copy the last interrupted event again.

4. Slave node: set parameters

Each slave server stores a file relay-log-info, which records the location where binary logs are copied on the master server and the location of local relay logs. To avoid busy IO operations, these parameters are stored in the memory before they are synchronized to the disk to a certain extent. Therefore, they are not safe.

Sync_master_info = ON

Sync_relay_log_info = ON

The above two items are for flushing data from memory to disk immediately

The above is all the content in this article. I hope you will be familiar with mysql master-slave replication.