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

Before we introduce lossless replication, let's first introduce semi-synchronous replication.

Semi-synchronous replication:

Lossless replication is a kind of semi-synchronous replication.

Introduction to semi-synchronous replication

1, two plug-ins to achieve semi-synchronous replication. The master library has a plug-in and the slave library has a plug-in.

2. The system variable controls the plug-in features. A few columns

Parameters:

1) rpl_semi_sync_master_enabled

Controls whether semi-synchronous replication is enabled on the primary library. To enable or disable the plug-in, set this variable to 1 or 0, respectively. The default value is 0 (off).

2) rpl_semi_sync_master_timeout

A value in milliseconds that controls how long the master server waits for confirmation commits from the slave server and resumes to asynchronous replication, beyond which is a timeout. The default value is 10000 (10 seconds). After the timeout, it goes from semi-synchronous replication to asynchronous replication.

3) rpl_semi_sync_slave_enabled

The function is similar to the parameter rpl_semi_sync_master_enabled, but controls the plug-ins from the library.

3. Enable status variables for semi-synchronous replication monitoring. Some examples:

Parameters:

1) Rpl_semi_sync_master_clients

The number of semi-synchronous slave libraries.

2) Rpl_semi_sync_master_status

Whether semi-synchronous replication is currently running on the primary server. If the plug-in is enabled and no confirmation is submitted, the value is 1. 0 if the plug-in is not enabled, or if the primary server has fallen back to asynchronous replication because the commit confirmation timed out.

3) Rpl_semi_sync_master_yes_tx

The number of submissions successfully confirmed from the library.

4) Rpl_semi_sync_master_no_tx

The number of submissions that were not successfully confirmed from the library.

5) Rpl_semi_sync_slave_status

Whether semi-synchronous replication is currently running on the slave station. 1 if the plug-in is enabled and the dependent Ithumb O thread is running, otherwise 0.

Installation and configuration of semi-synchronous replication

Semi-synchronous replication is implemented using plug-ins, so the plug-in must be installed into the database to make it available. After the plug-in is installed, the plug-in is controlled by the system variable associated with it. These system variables are not available until the associated plug-ins are installed.

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

1) MySQL 5.5 or later must be installed.

2) the ability to install the plug-in requires a MySQL server that supports dynamic loading. To verify this, check that the value of the have_dynamic_loading system variable is YES.

3) replication must already be working.

4) you cannot have multiple replication channel configurations. Semi-synchronous replication is only compatible with the default replication channel.

To set up semi-synchronous replication, use the following instructions.

The INSTALL PLUGIN,SET GLOBAL,STOP SLAVE and START SLAVE statements mentioned here require SUPER permissions.

The MySQL release includes semi-synchronous replication plug-in files on the master side and the slave side.

To be used by the master or slave library, the corresponding plug-in library files must be located in the MySQL plug-in directory (the directory named by the plugin_dir system variable). If necessary, set the value of plugin_dir when the server starts to tell the server plug-in directory location.

The base names of plug-in library files are semisync_master and semisync_slave. The file name suffix varies from platform to platform (for example, .so for Unix and Unix-like systems, .dll for Windows)

The main plug-in library file must exist in the plug-in directory of the main server. The slave plug-in library file must exist in the plug-in directory of each slave server

To load the plug-in, use the INSTALL PLUGIN statement on the master and each slave to be semi-synchronized (adjust the .so suffix for your platform as needed).

The MySQL release includes semi-synchronous replication plug-in files on the master side and the slave side.

To be used by the master or slave library, the corresponding plug-in library files must be located in the MySQL plug-in directory (the directory named by the plugin_dir system variable). If necessary, set the value of plugin_dir when the server starts to tell the server plug-in directory location.

The base names of plug-in library files are semisync_master and semisync_slave. The file name suffix varies from platform to platform (for example, .so for Unix and Unix-like systems, .dll for Windows)

The main plug-in library file must exist in the plug-in directory of the main server. The slave plug-in library file must exist in the plug-in directory of each slave server

To load the plug-in, use the INSTALL PLUGIN statement on the master and each slave to be semi-synchronized (adjust the .so suffix for your platform as needed).

1, install the plug-in

On the master:

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

On each slave:

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

If trying to install the plug-in results in an error similar to the one shown here, you must install libimf:

2 to see which plug-ins are installed

To see which plug-ins are installed, use the SHOW PLUGINS statement, or query the INFORMATION_ schema. Programming table.

For example:

Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS

FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_NAME LIKE'% semi%'

After the semi-synchronous replication plug-in is installed, it is disabled by default. Both the master and slave libraries must have plug-ins enabled to enable semi-synchronous replication. If only one side is enabled, replication will be asynchronous.

To control whether installed plug-ins are enabled, set the appropriate system variable. You can set these variables at run time using SET GLOBAL or in the command line or options file when the server starts.

At run time, these primary library-side system variables are available:

SET GLOBAL rpl_semi_sync_master_enabled = {0 | 1}

SET GLOBAL rpl_semi_sync_master_timeout = N

In terms of libraries, this system variable is available:

SET GLOBAL rpl_semi_sync_slave_enabled = {0 | 1}

For rpl_semi_sync_master_enabled or rpl_semi_sync_slave_enabled, the value should be 1 to enable semi-synchronous replication, or use 0 to disable it. By default, these variables are set to 0.

For rpl_semi_sync_master_timeout, the value N is given in milliseconds. The default value is 10000 (10 seconds).

3, if semi-synchronous replication is enabled on the slave library at run time, you must also start the slave library Iswap O thread (if it is already running, stop first) so that the slave library connects to the master library and registers as a semi-synchronous slave library:

STOP SLAVE IO_THREAD

START SLAVE IO_THREAD

If the Ithumb O thread is already running and does not restart, the slave device will continue to use asynchronous replication

When the server starts, you can set the variables that control semi-synchronous replication to command-line options or options files. The settings listed in the options file take effect each time the server starts. For example, you can set variables in the my.cnf files of master and slave stations as follows.

On the master:

[mysqld]

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

On each slave:

[mysqld]

Rpl_semi_sync_slave_enabled=1

Third, semi-synchronous replication monitoring

The plug-in for semi-synchronous replication exposes several system and state variables that can be checked to determine their configuration and operational status.

The system variable reflects how to configure semi-synchronous replication. To check their values, use SHOW VARIABLES:

Mysql > SHOW VARIABLES LIKE 'rpl_semi_sync%'

Status variables enable you to monitor the operation of semi-synchronous replication. To check their values, use SHOW STATUS:

Mysql > SHOW STATUS LIKE 'Rpl_semi_sync%'

When the primary station switches between asynchronous or semi-synchronous replication due to a commit blocking timeout or slave chase, it sets the value of the Rpl_semi_sync_master_status state variable appropriately.

The automatic fallback from semi-synchronous replication on the host to asynchronous replication means that the rpl_semi_sync_master_enabled system variable may have a value of 1 on the primary side, even if semi-synchronous replication is actually inoperable at this point. You can monitor the Rpl_semi_sync_master_status status variable to determine whether the current primary server is using asynchronous or semi-synchronous replication.

To see how many semi-synchronous slaves are connected, check the status parameter Rpl_semi_sync_master_clients.

Show status like'% rpl_semi_sync_master_clients%'

The Rpl_semi_sync_master_yes_tx and Rpl_semi_sync_master_no_tx variables indicate the number of submissions that have been successfully confirmed or subordinate failed.

Show status like'% Rpl_semi_sync_master_yes_tx%'

Show status like'% Rpl_semi_sync_master_no_tx%'

In terms of dependencies, Rpl_semi_sync_slave_status indicates whether semi-synchronous replication is currently running.

Mysql > SHOW STATUS LIKE 'Rpl_semi_sync_slave_status%'

Four: specific experimental steps

1) check to see if dynamically loaded MySQL servers are supported

Mysql > show variables like'% dynamic%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

2) install semisync_master plug-in in the main library

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

Query OK, 0 rows affected (0.05 sec)

3) install the semisync_slave plug-in for the library

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

Query OK, 0 rows affected (0.07 sec)

4) the main library views some parameter values about semi-synchronous replication

Mysql > show variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | OFF |

| | rpl_semi_sync_master_timeout | 10000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_for_slave_count | 1 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

| | rpl_semi_sync_master_wait_point | AFTER_SYNC |

+-+ +

6 rows in set (0.00 sec)

5) reset the main library

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

Query OK, 0 rows affected (0.00 sec)

6) prepare the database to view the parameter values of this synchronous replication

Mysql > show variables like'% rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | OFF |

| | rpl_semi_sync_slave_trace_level | 32 | |

7) reset the parameters for the standby database

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

Query OK, 0 rows affected (0.00 sec)

8) close again from the library, and then start the IO_ thread

Mysql > STOP SLAVE IO_THREAD

Query OK, 0 rows affected (0.00 sec)

Mysql > START SLAVE IO_THREAD

Query OK, 0 rows affected (0.00 sec)

9) standby database to view semi-synchronous replication status

Mysql > SHOW STATUS LIKE 'Rpl_semi_sync_slave_status%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

Lossless replication

Ordinary semi-synchronous replication

Test 1, semi-synchronous replication

1. The timeout of the master database is set to 10000 seconds, and the slave database stops replication to simulate timeout.

Mysql > set global rpl_semi_sync_master_timeout=100000000

Query OK, 0 rows affected (0.00 sec)

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

2. Modify the parameter pl_semi_sync_master_wait_point of the main library to ordinary semi-synchronous replication.

Mysql > set global rpl_semi_sync_master_wait_point=AFTER_COMMIT

Query OK, 0 rows affected (0.00 sec)

3. The main library opens window 1 and inserts a piece of data into the table

Found window 1, stuck

The main library opens window 2 to query this table

It is found that the data are already available.

Therefore, it is concluded that ordinary semi-synchronous replication is after commit binlog. And then need to get the confirmation of the reserve database. So at this time, the main database is down, the data of the last thing, the standby database is not available, and data loss will occur.

Test 2, lossless semi-synchronous replication

1. The main library modifies the parameter rpl_semi_sync_master_wait_point, which is a lossless copy

Mysql > set global rpl_semi_sync_master_wait_point=AFTER_SYNC

Query OK, 0 rows affected (0.00 sec)

2, the main library opens window 1 and inserts a piece of data into the table

Found window 1, stuck

The main library opens window 2 to query this table

Found that the data are not yet available.

Therefore, it is concluded that lossless semi-synchronous replication is after write binlog. It needs to be confirmed by the reserve database. Therefore, at this time, the main database is down, and data loss will not occur.

Esperanto Chinese, simplified Chinese, traditional Danish, Ukrainian, Uzbek, Urdu, Armenian, Ibo, Russian, Sinhalese, Croatian, Icelandic, Galician, Catalan, Hungarian, South African, Zulu, Kannada, Hindi, Indonesia, Sunda, Indonesia, Java, Indonesian, Gujarat, Kazakh, Turkish, Tajik, Serbian, Sesotto. Welsh, Bangladeshi, hostel, Nepalese, Basque, Boer (Afrikaans), Hebrew, Greek, German, Italian, Latin, Latvian, Norwegian, Czechoslovakia, Slovenia, Swahili, Punjab, Japanese, Georgian, Maori, French, Polish, Persian, Telugu, Tamil, Haitian Creole. Irish, Estonian, Swedish, Belarusian, Lithuanian, Somali, Yoruba, Burmese, Romanian, Lao, Finnish, Hmong, English, Dutch, Filipino, Portuguese, Mongolian, Spanish, Hausa, Azerbaijani, Albanian, Arabic, Korean, Macedonian, Malayalam, Malay, Malay, Khmer, Zicheva. Language options: history: feedback: Donate off

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.