Implementation of semi-synchronous replication
1 、 Configuration 1) install mariadb-server [root@localhost ~] # yum-y install mariadb-server2) Edit / etc/my.cnf [root@localhost ~] # vim / etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 1 log-bin = master-log3) authorize hosts that can copy local database information [root@localhost ~] # systemctl start mariadb.service (start mariadb server) [root@localhost ~] # mysql MariaDB [(none)] > grant replication slave Replication client on *. * to 'repluser'@'10.1.51.%' identified by' replpasswd' MariaDB [(none)] > flush privileges MariaDB [(none)] > show master status\ G (View the status information of the master server Install the rplsemisync_master plug-in in) * * 1. Row * * File: master-log.000003 (binary log file in use) Position: 245 (location) Binlog_Do_DB: Binlog_Ignore_DB:4) to be used from the server And enable [root@localhost ~] # mysqlMariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so' MariaDB [(none)] > set global rpl_semi_sync_master_enabled = ON; add: MariaDB [(none)] > show plugins; (check whether the plug-in is activated) MariaDB [(none)] > show global variables like 'rpl_semi%'; (you can check whether the installed plug-in is enabled) MariaDB [(none)] > show global status like'% semi%' (you can view the number of slave servers At this time it is 0) 2. From server configuration 1) install mariadb-server [root@localhost ~] # yum-y install mariadb-server2) Edit / etc/my.cnf file [root@localhost ~] # vim / etc/my.cnf add the following at the end of [mysqld] paragraph: skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 (id number cannot be the same as master server) relay-log = slave-log (custom (binary log file name) 3) set the location of the primary server from which to start synchronization [root@localhost ~] # systemctl start mariadb.service [root@localhost ~] # mysql MariaDB [(none)] > change master to master_host='10.1.51.60' Master_user='repluser',master_password='replpasswd',master_log_file='master-log.000003',master_log_pos=245 4) install the rplsemisync_slave plug-in and enable [root@localhost ~] # mysql MariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; MariaDB [(none)] > set global rpl_semi_sync_slave_enabled = ON; MariaDB [(none)] > start slave
After completing the above configuration, you can view the information about semi-synchronous replication on the master server as follows:
MariaDB [(none)] > show global status like'% semi%'; Rpl_semi_sync_master_clients 1 (there is one slave server) 3. The test depends on the actual situation of the individual. 1) Import the prepared database hellodb.sqlMariaDB [hellodb] > source / root/hellodb.sql;2 on the master server to view the status of semi-synchronous replication MariaDB [hellodb] > show master status on the master server. +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+ | master-log.000003 | 8102 | +- -+ MariaDB [hellodb] > show global status like'% semi%' +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 1 | Rpl_semi_sync_master_net_avg_wait_time | 1684 | | Rpl_semi_sync_master_net_wait_time | 60630 | | Rpl_semi_sync_master_net_waits | 36 | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no _ tx | 1 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 1884 | | Rpl_semi_sync_master_tx_wait_time | 65965 | Rpl_semi_sync_master_tx_waits | 35 | | Rpl_semi_sync_ Master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 35 | +-- +-- + 3) check whether to synchronize MariaDB [(none)] > show databases on the slave server. MariaDB [(none)] > use hellodb;MariaDB [hellodb] > select * from students Add: based on the semi-synchronous replication configuration above, replication filtering is best set on the slave server. The steps are as follows 1. From server configuration 1) close mariadb server [root@localhost ~] # systemctl stop mariadb.service2) Edit / etc/my.cnf file [root@localhost ~] # vim / etc/my.cnf skip_name_resolve = ON innodb_file_per_table = ON server-id = 2 relay-log = slave-log replicate-do-db = mydb (copy only the contents of mydb database) add: common filtering options are as follows: DB= Replicate_Ignore_DB= Replicate_Do_Table= Replicate_Ignore_Table= Replicate_Wild_Do_Table= Replicate_Wild_Ignore_Table=3) restart mariadb server [root@localhost ~] # systemctl start mariadb.service4) after restarting mariadb server Semi-synchronous replication will be turned off, so restart MariaDB [(none)] > show global variables like'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync _ slave_trace_level | 32 | +-+-+ MariaDB [(none)] > set global rpl_semi_sync_slave_enabled = ON MariaDB [(none)] > stop slave; (replication from the slave server needs to be disabled before restart) MariaDB [(none)] > start slave;2, test 1) create a new table semitableMariaDB [hellodb] > create table semitable (id int) in the hellodb database on the master server; 2) check whether the hellodb database has semitableMariaDB [(none)] > use hellodbMariaDB [hellodb] > show tables on the slave server (no) +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +- -- + 3) create a mydb database on the primary server And create a tbl1 table MariaDB [hellodb] > create database mydb for it 4) check whether there are tbl1 tables in the mydb database from the server: MariaDB [hellodb] > use mydb;MariaDB [mydb] > show tables; (can be seen) +-+ | Tables_in_mydb | +-+ | tbl1 | +-+
