Shulou(Shulou.com)06/01 Report--

=

Asynchronous replication (Asynchronous replication)

The default replication of MySQL is asynchronous. The master database will immediately return the result to the client after executing the transaction committed by the client, and does not care whether the slave database has received and processed it. There will be a problem. If the master crash is dropped, the committed transaction on the master may not be transferred to the slave. If it is forced to promote the slave master at this time, the data on the new master may be incomplete.

Fully synchronous replication (Fully synchronous replication)

When the master library executes a transaction, all slave libraries execute the transaction before returning to the client. Because you need to wait for all slaves to complete the transaction before returning, the performance of fully synchronous replication is bound to be seriously affected.

Semi-synchronous replication (Semisynchronous replication)

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

Reference: http://www.cnblogs.com/ivictor/p/5735580.html

AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

=

Semi-synchronous replication will set a timeout period. If the slave server does not give confirmation to the master server after this time, the master server will enable asynchronous replication. When the slave server is good again, the master server will use semi-synchronous replication.

Configure semi-synchronous replication

Mysql > show variables like "have_dynamic_loading"

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

1 row in set (0.02 sec)

Mysql >

To use semi-synchronous replication, the following conditions must be met:

1. MySQL 5.5 and above

two。 Variable have_dynamic_loading is YES

3. Asynchronous replication already exists

First load the plug-in

Because the user needs to perform INSTALL PLUGIN, SET GLOBAL, STOP SLAVE and START SLAVE operations, the user needs to have SUPER permission.

Lord:

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

From:

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

Then check whether the plug-in is enabled on the master and slave

Mysql > show plugins

| | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | |

=

Start semi-synchronous replication

After installing the plug-in, semi-synchronous replication is off by default. In this case, you need to set parameters to enable semi-synchronization.

Lord:

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

From:

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

The above startup mode is operated on the command line or can be written in the configuration file.

Lord:

Plugin-load=rpl_semi_sync_master=semisync_master.so

Rpl_semi_sync_master_enabled=1

From:

Plugin-load=rpl_semi_sync_slave=semisync_slave.so

Rpl_semi_sync_slave_enabled=1

Check to see if semi-synchronization is running

Lord:

Mysql > show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | ON |

+-+ +

1 row in set (0.00 sec)

From:

Mysql > show status like 'Rpl_semi_sync_slave_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

1 row in set (0.20 sec)

These two variables are often used to monitor whether the master and slave are running in semi-synchronous replication mode.

At this point, MySQL semi-synchronous replication has been built ~

=

After modifying the configuration file, add the load plug-in directly and enable semi-synchronous replication. After restarting the refresh mysqld service, the semi-synchronous replication will not start.

Mysql > show status like 'Rpl_semi_sync_slave_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | OFF |

+-+ +

1 row in set (0.02 sec)

Mysql >

The reason is that the file and location of the binary log on the master server are inconsistent with the file name and location in the relay-log.info of the slave server.

Information on the primary server

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | MySQL-master-bin.000002 | 154 | |

+-+

1 row in set (0.00 sec)

From the information on the server

Mysql > show slave status\ G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 5

Current database: * * NONE * *

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.0.137

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Mysql-master-bin.000001

Read_Master_Log_Pos: 1508

Relay_Log_File: mysql-slave-relay-bin.000010

Relay_Log_Pos: 4

Relay_Master_Log_File: Mysql-master-bin.000001

Slave_IO_Running: No cannot be started

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' error reminder

[root@mysql-slave mysql] # cat relay-log.info

seven

. / mysql-slave-relay-bin.000010

four

Mysql-master-bin.000001-"the file and location are inconsistent

1508

0

0

one

[root@mysql-slave mysql] #

Solution:

Mysql > show master status; view log files and locations on the primary server

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | MySQL-master-bin.000002 | 154 | |

+-+

1 row in set (0.00 sec)

Mysql >

Set the log file name and location from the server

Mysql > change master to master_log_file='MySQL-master-bin.000002',master_log_pos=154

Mysql > show status like 'Rpl_semi_sync_slave_status'; view

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

1 row in set (0.02 sec)

Mysql >

Finally, ok.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.