MySQL master-slave replication semi-sync settings and support for ssl-based replication Configuration

I. Master-slave replication process of mysql:

The dump process in the master node reads binary files and has the replication client and replication slave permissions on the slave server's I/O threads.
Read the binary file of the master server and record it to relay-log. Read the relay-log from the SQL thread of the server according to the rules defined in my. cnf and update it to the database.

The above process shows that the master maintains the bin-log and the slave maintains the relay-log to achieve master-slave replication.
 
Master-slave implementation:
Configuration in the master server (172.16.21.1)
# Vim my. cnf
[Mysqld]
Server-id = 1
Sync_binlog = 1 // when a transaction is executed, the generated data and DDL are synchronized to the binlog immediately.
Innodb_flush_logs_at_trx_commit = 1

# Service mysqld restart
Log on to the database and add a user. The user has the permissions of replication client and replication slave.
Mysql> grant replication client, replication slave on *. * to repl @ '192. 16. %. % 'identifided by '123 ';
Mysql> show grants for repl @ '192. 16. %. % ';
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 11404543 |
+ ------------------ + ---------- + -------------- + ------------------ +

Slave server configuration (172.16.21.2)
Vim my. cnf
[Mysqld]
Server-id = 11
Skip_slave_start = 1
Read_only = 1

# Bin-log = mysql-bin
Relay-log = relay-bin
Relay-log-index = relay-bin.index
Log on to the database and direct the master server to 172.16.21.1. The repl password 123456 corresponds to the above master server settings.
Mysql> change master to master_user = 'repl', master_host = '2017. 16.21.1 ', master_bin_log = 'mysql-bin.000001 ';
Mysql> start slave;
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.21.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 11404543
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 11404689
Relay_Master_Log_File: mysql-bin.000001
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: 11404543
Relay_Log_Space: 11404839
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_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: 1
1 row in set (0.00 sec)

If the master and slave servers are built for the first time and no data is stored, the solution for enabling slave errors is as follows:
Run
Mysql> flush master; // scroll the binary log
Run
Mysql> flush slave; // scroll the relay log

Ii. Master-master replication:
 
Master 1 :( 172.16.21.2)
If it is enabled for the first time:
Enable binary logs and relay logs
[Mysqld]
Server-id = 1
Log-bin = mysql-bin
Relay-log = relay-bin
Relay-log-index = realy-bin.index
> Grant replication client, replication slave on *. * to repl @ '1970. 16. %. %'
> Change master to MASTER_HOST = '192. 16.21.1 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016'
MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 811 // MASTER_LOG_FILE and MASTER_LOG_POS here are the master server's
Show master status; Information
> Start slave;
> Show slave status;

Master 2: (172.16.21.1)
If it is enabled for the first time:
Enable binary logs and relay logs
[Mysqld]
Server-id = 1
Log-bin = mysql-bin
Relay-log = relay-bin
Relay-log-index = relay-bin.index
> Grant replication client, replication slave on *. * to repl @ '1970. 16. %. %'
> Change master to MASTER_HOST = '192. 16.21.2 ', MASTER_USER = 'repl', MASTER_PASSWORD = '2016'
MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 811 // MASTER_LOG_FILE and MASTER_LOG_POS here are the master server's
Show master status; Information
> Start slave;
> Show slave status;
 
3. Set semi-Sync:
Mysql master-slave replication is implemented asynchronously. you can install the semisync_master.so plug-in on the master side.
Install semisync_slave.so plug-in on the slave side to achieve semi-synchronization. Here the so-called semi-synchronization is when one master node and multiple slaves,
The master node synchronizes data to a slave node. When the synchronization process exceeds the time limit (set through rpl_semi_sync_master_timeout ),
Downgrade to asynchronous mode.
Setting method:
Master server (172.16.21.1)
Mysql> install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';
Mysql> set global rpl_semi_sync_master_enabled = 1;
Mysql & gt; set global rpl_semi_sync_master_timeout = 1000;
Slave server (172.16.21.2)
Mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';
Mysql> set global rpl_semi_sync_slave_enabled = 1;
Mysql> stop slave IO_THREAD; start slave IO_THREAD;
// Restart the IO thread of the slave server to implement the plug-in Function

Check whether semi_sync is enabled on the slave server
Mysql> show global status like 'rpl _ semi % ';
+ ---------------------------- + ------- +
| Variable_name | Value |
+ ---------------------------- + ------- +
| Rpl_semi_sync_slave_status | ON |
+ ---------------------------- + ------- +
1 row in set (0.04 sec)
 
Check whether semi_sync is enabled on the master server
Mysql> show global status like 'rpl _ semi % ';
+ ------------------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------------------ + ------- +
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+ ------------------------------------------ + ------- +
14 rows in set (0.01 sec)

The set parameters in the preceding command can be written under my. cnf [mysqld] on the respective servers to take effect permanently.
To cancel the plug-in loading, run the following command;
Mysql> uninstall plugin rpl_semi_sync_master;

4. ssl-based master-slave Replication
Master-slave Replication refers to the transmission of data in plain text on the network. Therefore, it is necessary to set up ssl-based replication for the master-slave service. Here are official documents. You can also use the following configurations.
Official documentation: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

The following configuration must be performed on the basis of the master-slave replication, so only the ssl configuration options are added. For other options, refer to the master-slave replication configuration.
First, apply for the certificate from the master (the certificate application process is skipped)
Master (172.16.21.1 ):
CA certificate:/etc/pki/CA/cacert. pem
Private key:/usr/local/mysql/ssl/master. key
Server Certificate:/usr/local/mysql/ssl/master. crt

Vim my. cnf
[Mysqld]
Ssl-ca =/etc/pki/CA/cacert. pem
Ssl-cert =/usr/local/mysql/ssl/master. crt
Ssl-key =/usr/local/mysql/ssl/master. key
# Serivce mysqld restart
 
Slave (172.16.21.2 ):
CA certificate:/etc/pki/CA/cacert. pem
Private key:/usr/local/mysql/ssl/slave. key
Server Certificate:/usr/local/mysql/ssl/slave. crt
Vim my. cnf
[Client]
Ssl-ca =/etc/pki/CA/cacert. pem
Ssl-cert =/usr/local/mysql/ssl/slave. crt
Ssl-key =/usr/local/mysql/ssl/slave. key
 
Service mysqld restart // to change the related options of the master, you do not need to start slave immediately to restart mysql. You can define skip_slave_start = 1 in the configuration file.
 
Log on to the slave server mysql and change the related options of the master to implement the ssl function.
Mysql> CHANGE MASTER
-> MASTER_HOST = '2017. 16.21.1 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = '123 ',
-> MASTER_SSL = 1,
-> MASTER_SSL_CA = '/etc/pki/CA/cacert. pem ',
-> MASTER_SSL_CAPATH = '/etc/pki/ca ',
-> MASTER_SSL_CERT = '/usr/local/mysql/ssl/master. crt ',
-> MASTER_SSL_KEY = '/usr/local/mysql/ssl/master. key ';
Mysql> start slave;
Mysql> show slave status;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Master_SSL_Allowed: Yes
Master_SSL_CA_File:/etc/pki/CA/cacert. pem
Master_SSL_CA_Path:/etc/pki/CA
Master_SSL_Cert:/usr/local/mysql/ssl/slave. crt
Master_SSL_Cipher:
Master_SSL_Key:/usr/local/mysql/slave. key

So far, it is implemented based on the ssl function.