Mariadb-10 for semi-synchronous replication and SSL Secure Replication

For more information about the principle and implementation of master-slave replication, see mysql master-slave replication outline about replication-Related Issues semi-sync implementation process semi-sync summary attention to details of master-slave replication I. About replication-related question 1: Asynchronization

For more information about the principle and implementation of master-slave replication, see mysql master-slave replication outline about replication-Related Issues semi-sync implementation process semi-sync summary attention to details of master-slave replication I. About replication-related question 1: Asynchronization

Tip

For the principle and implementation of master-slave replication, see mysql master-slave replication.

Outline

I. Questions about Replication

1. asynchronous replication solves those problems

The basic problem solved by replication is that data on one server is synchronized with that on another server, which can be located in different network topologies and specific databases on the whole server, you can even copy a specific table.

There are two replication schemes:

First, both statement-based replication and row-based replication are performed asynchronously by recording the binary logs of the master server and performing replay on the slave server.

Mariadb or mysql replication is mostly backward compatible. This means that newer servers can be slave servers of older versions. Replication usually does not significantly increase the server overhead. It requires the master server to enable binary logs. Replication is good for extended reading. You can direct the read to the slave server.

2. Why do I need semi-synchronous replication?

MySQL Replication in MySQL versions earlier than MySQL is asynchronous (asynchronous). After the master database completes some transactions, it does not manage the progress of the slave database. If the standby database is unfortunately lagging behind, and the primary database crashes again, the data in the standby database is incomplete. In short, when the master database fails, we cannot use the slave database to continue providing data consistency services. Semisynchronous Replication ensures that the committed transaction has been passed to at least one slave database to a certain extent.

3. Why not full synchronization?

In Semi_synchronous, only ensure that the transaction has been passed to the slave database, but it does not ensure that the execution has been completed on the slave database.

In addition, there is another situation that will cause data inconsistency between the master and slave nodes. In a session, after a transaction is committed on the master database, the transaction will wait for the transaction to be passed to at least one slave database. If the master database Crash during the waiting process, the slave database may be inconsistent with the master database, which is fatal. (After the master database is restored, you can observe it through the Rpl_semi_sync_master_no_tx parameter)

4. If the connection between the master and slave nodes fails, will the master database keep waiting?

If the master-slave network fails or the slave database crashes, the master database will continue after 10 seconds (the default value of rpl_semi_sync_master_timeout) after the transaction is committed. Then, the master database changes back to the original asynchronous state.

II. Implementation Process of semi-sync

Note:

1. As mentioned in the previous blog post, how to install mariadb is directly configured here (the same as that for msyql compilation or binary common format installation.

2. If you want to implement semi-synchronous replication, you must configure master-slave replication first.

===========================Formal start ========================= =====

System Version: centos6.5

Database: mariadb-10 (general binary format installation)

System Resource Allocation

Master:

IP Address: 192.168.1.114

Slave end:

IP Address: 192.168.1.109

Semi-sync plug-ins are provided by Google. The specific location is/usr/local/mysql/lib/plugin/. One is semisync_master.so used by the master, and the other is semisync_slave.so used by the slave.

Run the following command on the master:

MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync _ master. so ';

Enable semi_sync in/etc/my. cnf and write the following two statements to the [mysqld] segment.

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = 1000

Or directly execute the following command in the database:

MariaDB [(none)]> set global rpl_semi_sync_master_enabled = 1

Restart the service

[Root @ bogon ~] # Service mysqld restartMySQL server PID file cocould not be found! [FAILED] Starting MySQL [OK]

Run the following command on slave:

MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so ';

Add the following statement in the/etc/my. cnf configuration file [mysqld] section.

Rpl_semi_sync_slave_enabled = 1

Or directly execute the following statement in the database.

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = 1

Restart the server

[Root @ bogon data] # service mysqld restartShutting down MySQL... [OK] Starting MySQL... [OK]

Check the status on the master.

MariaDB [(none)]> show global status like 'rpl _ semi % '; + region + ------- + | Variable_name | Value | + region + ------- + | Rpl_semi_sync_master_clients | 1 | region | 0 | region | 0 | region | 0 | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | slave | 0 | slave | 0 | slave | 0 | slave | 0 | | Rpl_semi_sync_master_yes_tx | 0 | + -------------------------------------------- + ------- + 14 rows in set (0.00 sec)

Note:

Rpl_semi_sync_master_clients
Record the number of slave instances that support semi-synchronization.
Rpl_semi_sync_master_net_avg_wait_time
Average waiting time for the master to wait for the slave reply. Unit: milliseconds.
Rpl_semi_sync_master_net_wait_time
Total master wait time.
Rpl_semi_sync_master_net_waits
The total number of waits for the master to wait for the slave reply.
Rpl_semi_sync_master_no_times
The number of times the master disables semi-synchronous replication.
Rpl_semi_sync_master_no_tx
The number of times that the master node has not received a response from the server Load balancer instance.
Number)
Rpl_semi_sync_master_status
Mark whether the master is in the semi-synchronous replication status.

Rpl_semi_sync_master_tx_avg_wait_time
The average wait time that the master spends on each transaction.
Rpl_semi_sync_master_tx_wait_time
Total number of master waits.

Rpl_semi_sync_master_wait_sessions
The number of sessions currently waiting due to the slave reply.
Rpl_semi_sync_master_yes_tx
The number of times the master successfully received the slave reply.
Rpl_semi_sync_slave_status
Indicates whether the slave is in the semi-sync status.