mysql5.5 read-Write separation semi-synchronous

Read/write separation

Generally we are only responsible for the client's read request from the server side, the main service side is responsible for writing the request. Then configure it!
First look at whether read-only mode is turned on from the server side.
Mysql> show global variables like ' read% ';
+----------------------+---------+
| variable_name | Value |
+----------------------+---------+
| Read_buffer_size | 1048576 |
| Read_Only | OFF |
| Read_rnd_buffer_size | 4194304 |
+----------------------+---------+
3 Rows in Set (0.00 sec)
There are two ways to open our read-only method
One is configured in the global mode of the MySQL server, but is invalidated after the MySQL restart is configured in global mode;
One is configured in the/ETC/MY.CNF configuration file, this configuration is permanent; Here we choose the second.
Add a line to the/ETC/MY.CNF configuration file as follows:
[Email protected] ~]# VIM/ETC/MY.CNF
Read_Only = On
Then reboot to start under our MySQL server.
[Email protected] ~]# service mysqld restart
Shutting down MySQL ... [OK]
Starting MySQL ......... ..... [OK]
Enter read-only mode under the MySQL service test.

The primary server performs a write operation, and the log files are immediately synchronized to the slave server, which is set up to guarantee the integrity of the transaction.
Add Skip_slave_start = 1 on the primary server side [mysqld]

MySQL5.5, in addition to supporting the built-in asynchronous replication mechanism, also provides a mechanism for the interface to support semi-synchronous replication.
The disadvantage of asynchronous replication is that MySQL replication defaults to asynchronous replication, and master writes events to Binlog, but does not know whether or when slave has been received and processed. In the case of the asynchronous replication mechanism, if master goes down, the transaction is committed on master, but it is likely that these transactions have not been uploaded to any slave. Assuming there is a mechanism for master->salve failover, slave can also lose transactions at this time.
The concept of semi-synchronous replication:
I.
When the slave host is connected to master, it is able to see whether it is in a semi-synchronous replication mechanism.
II. There should be at least one slave to turn on the function of the semi-synchronous copy on master. At this point, a thread commits a transaction on master that will be blocked until it learns that a slave that has turned on the semi-synchronous copy has received all events for this transaction, or waits for a timeout.
Iii.
When a transaction event has been written to its relay-log and has been flushed to disk, slave will not be informed of the receipt.
Iv. If you wait for a timeout, that is, master is not told that it was received, Master automatically transitions to the mechanism of asynchronous replication. When at least one half-synchronized slave catches up, master and its slave automatically convert to a semi-synchronous replication mechanism.
V.
Semi-synchronous replication will only work if the function of the master,slave is turned on; otherwise, only one side is turned on, and it is still replicated asynchronously.


Second, semi-synchronous definition: The primary server side performs a write operation, must be copied from the server side, in order to return the submission status to the client.
Here we need to install semisync_master.so from the server side on the main server side semisync_slave.so
The steps are as follows:
Main server side:
mysql> Install plugin rpl_semi_sync_master SONAME ' semisync_master.so ';
Query OK, 0 rows affected (0.36 sec)
After installing it, look under whether to start if it does not start under Enable.
Mysql>show Global variables like '%rpl% ';

The main server side turns on the Semi_sync feature and sets the wait time to 3 seconds.
mysql> Set Global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.02 sec)
mysql> Set Global rpl_semi_sync_master_timeout = 3000;
Query OK, 0 rows Affected (0.00 sec)

Mysql>show Global variables like '%rpl% ';
From the server side:
mysql> Install plugin rpl_semi_sync_slave SONAME ' semisync_slave.so ';
Query OK, 0 rows affected (0.28 sec)
Mysql>show Global variables like '%rpl% ';
Turn on the Semi_sync function from the server side.
mysql> Set Global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.03 sec)
Restart Service
Stop slave Io_thread
Start slave Io_thread
Mysql>show Global variables like '%rpl% ';
Acceptance from the main service side:
Mysql>show Global status like ' rpl%;
Semi-synchronous replication is the first time the primary server is committed after a delay of 3 seconds if it is not turned on from the server, and then the primary server slows down the latency and no longer waits from the server. After being opened from the server, after the main server is catching up, it realizes the semi-synchronous.
Implementation Demo under:
1, first off from the service side.
mysql> stop Slave;
Query OK, 0 rows affected (0.02 sec)
2. Create a SEMIDB database on the main server
Mysql>create database semidb;

3, start the service from the service side.
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
Mysql> Show Slave Status\g