MySQL semi-synchronous replication and replication filtering

Configure MySQL semi-sync, provided the master and slave environments are already in place.

MySQL Semi-synchronous configuration

Master configuration

Installing the Semisync_master Plugin

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Query OK, 0 rows affected (0.02 sec)

Mysql>show plugins;

+----------------------------+----------+--------------------+--------------------+---------+

| Name | Status | Type | Library | License |

+----------------------------+----------+--------------------+--------------------+---------+

| Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

Vim/etc/my.cnf

[Mysqld]

Rpl_semi_sync_master_enabled=on

Service mysqld Restart

Mysql> show global variables like '%semi% ';

+------------------------------------+-------+

| variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | On |

| Rpl_semi_sync_master_timeout | 10000 |

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_no_slave | On |

+------------------------------------+-------+

4 rows in Set (0.00 sec)

Slave configuration

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

Query OK, 0 rows Affected (0.00 sec)

Vim/etc/my.cnf

[Mysqld]

Rpl_semi_sync_slave_enabled=on

Service mysqld Restart

Mysql> show global variables like '%semi% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | On |

| Rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in Set (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

View semi-synchronous replication status at master after configuration is complete

Mysql> show global status like '%sem% ';

+--------------------------------------------+-------+

| variable_name | Value |

+--------------------------------------------+-------+

| rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 703 |

| Rpl_semi_sync_master_net_wait_time | 703 |

| Rpl_semi_sync_master_net_waits | 1 |

| 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 | 777 |

| Rpl_semi_sync_master_tx_wait_time | 777 |

| Rpl_semi_sync_master_tx_waits | 1 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 1 |

+--------------------------------------------+-------+

Rows in Set (0.00 sec)

MySQL Replication filtering

Slave configuration:

Vim/etc/my.cnf

[Mysqld]

replicate-do-db =sync_db //Specify the db to be copied when copying

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.1.202

Master_user:repluser

master_port:3306

Connect_retry:60

master_log_file:master_bin.000006

read_master_log_pos:344

relay_log_file:relay_log.000019

relay_log_pos:284

relay_master_log_file:master_bin.000006

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:sync_db

MySQL semi-synchronous replication and replication filtering