Using OpenSSL in Linux system to realize Mysql master-slave replication _mysql

Certificate Preparation:

CA Certificate:

First step: Create the CA private key

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

Step two: Generate a self-signed certificate

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

————————————————————————————–

MySQL prepares the private key and the certificate request file:

First step: Create the MySQL private key:

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

Step two: Generate the certificate request 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: Country, province, company name must be consistent with CA

To send a certificate request file to the CA server

————————————————————————————–

To issue certificates on the server side of the CA:

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

Attached to view the information in the certificate command:

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

————————————————————————————–

To send a certificate to a MySQL server

and sending the CA's self-visa book to the server

The certificate preparation action ends here

Based on the SSL function to achieve master-slave replication, is the principal and subordinate both need to authenticate each other, that is, from the server also have their own certificates.

So, according to the above process, generate the certificate of the slave server

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

To configure the MySQL service side:

View SSL-related parameters and master-slave replication on the primary server-primary server configuration items:

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

Because the SSL feature configuration entry is a global configuration parameter, edit the/etc/my.cnf file:

Because it is a client-side authentication server, you only need to configure the Ssl_cert (the location of the certificate on the MySQL servers), Ssl_key (the MySQL private key location) and Ssl_ca (CA certificate location)

Open the service and check:

————————————————————————————–

Create a minimum number of permissions from the server used to replicate the MySQL master server database based on SSL functionality:

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, for configuration from the server:

MARIADB [(None)]> show MASTER LOGS;

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

From server configuration:

Edit/etc/my.cnf, because it is from the server, so need to turn on the relay log (Relay_log), and server_id should not be the same level with the MySQL server

Start the MySQL service and check:

————————————————————————————–

Configuration from the server, point to the primary server (this is the global configuration parameters, but not recommended in the configuration file/etc/my.cnf, because if slave due to unexpected downtime, in order to check the data integrity, restart MySQL, will also automatically start the replication function, 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 master and slave effects:

————————————————————————————–

Primary server CREATE DATABASE MyDB

————————————————————————————–

View from server:

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

Summarize:

Issues to be aware of when replicating:

1, from the service set to "read-only"; (Dual main mode does not need to set)

READ_ONLY is initiated from the server, but is only valid for users with non-super privileges;

Block All Users:

mysql> FLUSH TABLES WITH READ LOCK;

2, as far as possible to ensure that the replication of the transaction security

To enable parameters on the master node:

Sync_binlog = on #每次事务提交的时候, which immediately synchronizes the binary log time to disk, ensures immediate access to the event from the server, and protects local data security

If you are using the InnoDB storage engine:

Innodb_flush_logs_at_trx_commit=on #在事务提交时, immediately brush write transaction log from memory to disk

Innodb_support_xa=on #支持分布式事务

#这两项启动起来, to some extent, to ensure that the most recent events from the server are immediately available to the primary server

3, as far as possible from the server accidentally aborted to avoid the automatic start of the replication thread

Because the data is important, the server can accidentally terminate the time is singular, so, do not let the termination of the server automatically start the replication thread, such as manual manually troubleshooting after the new start of the replication thread, it is possible to replicate the thread to half of the accident occurred, the replication thread may not have this function-– I don't know when I need to replicate the last interrupted event again.

4, from the node: Set parameters

Every server will save a file Relay-log-info, is to record the location of the copy binary log on the primary server and the location of the local relay log, which are stored in memory first, to a certain extent, to avoid a busy IO operation, so it is not secure

Sync_master_info=on

Sync_relay_log_info=on

The above two items are for immediate brush write data from memory to disk

The above is the entire content of this article, I hope that everyone familiar with the MySQL master copy can help