MARIDDB master-slave replication, dual master model, semi-synchronous configuration

First, master-slave replication

MARIADB is a copy of the client-uploaded data from the master node to the slave node, which can improve read performance, which does not improve write performance, because each piece of data will be written on the node

Disadvantage: In order to increase read and write performance, the database is stored in memory first, and then synchronized to the data file, that is, the synchronization is asynchronous synchronization, that is, the data from the node is behind the master node

Replication process:

The client writes the data----> The transaction log memory of the primary server----> The thing log file----> Synchronize to the data file---->---The IO thread from the server----> Relay log---> by using a binary file Replay relay logs with SQL threads

Realize:

Primary database server

[[Email protected] ~]# vim /etc/my.cnf[mysqld]server_id=1log_bin=log-binmariadb [( None)]> grant replication slave,replication client on *.* to [email  protected] "192.168.1.%"  IDENTIFIED BY  ' CentOS ';   #授权主从复制账户MariaDB  [(none)] > FLUSH PRIVILEGES;   #刷新MariaDB  [(none)]> show master status;    #查看当前使用的是哪一个二进制日志, and POS location, need to use   + from node----------------+----------+--------------+------- -----------+| file           | position  | binlog_do_db | binlog_ignore_db |+----------------+----------+--------------+----------- -------+| log-bin.000001 |      489 |               |                   |+----------------+----------+--------------+------------------+ 

From the server

[[email protected] ~]# vim/etc/my.cnfserver_id=2relay_log=relay-logread_only=onmariadb [(none)]> change MASTER to Master_host= ' 192.168.1.151 ', master_user= ' test ', master_password= ' CentOS ', master_log_file= ' log-bin.000001 ', master_log_pos=489; MariaDB [(None)]> start slave;  MariaDB [(none)]> show Slave status\g; Slave_io_running:yes #这两项需要为yes, representing the open IO thread and SQL thread Slave_sql_running:yes

Test create a database on the master node or insert data to synchronize

Two, two-master model

Double master model, easy to cause data inconsistency, generally not recommended to use

Host A

[[email protected] ~]# vim/etc/my.cnf[mysqld]server_id=1log_bin=log-binrelay_log=relay-logmariadb [(None)]> GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] "192.168.1.%" identified by ' CentOS '; Change MASTER to master_host= ' 192.168.1.152 ', master_user= ' test ', master_password= ' CentOS ', master_log_file= ' Log-bin.000001 ', master_log_pos=489; MariaDB [(None)]> start slave; MariaDB [(none)]> show Slave status\g;

Host B

[[email protected] ~]# vim/etc/my.cnf[mysqld]server_id=2log_bin=log-binrelay_log=relay-logmariadb [(None)]> GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] "192.168.1.%" identified by ' CentOS '; Change MASTER to master_host= ' 192.168.1.151 ', master_user= ' test ', master_password= ' CentOS ', master_log_file= ' Log-bin.000001 ', master_log_pos=489; MariaDB [(None)]> start slave; MariaDB [(none)]> show Slave status\g;

Third, semi-synchronous replication

Semi-synchronous replication is a host that has a synchronous write disk, when the client writes data, the primary database server will immediately write the data to the specified slave server, immediately synchronize, and then on the corresponding client to prove that there is a complete data in case the primary server fails

Implementing a semi-synchronous replication requires adding a primary server to the database, adding semisync_master.so, and adding semisync_slave.so from the server

Primary server

mariadb [none]> install plugin rpl_semi_sync_master soname  ' semisync_ Master.so '; mariadb [none]> show global variables like  ' rpl_semi% '; mariadb [none]> set global rpl_semi_sync_master_enabled=on; #开启同步写数据文件MariaDB  [none ]> show global variables like  ' rpl_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    |+------------------------------------+-------+ 

From server

install plugin rpl_semi_sync_slave soname  ' semisync_slave.so '; mariadb [none]> show global variables like  ' rpl_semi% ';                          +---------------------------------+-------+| variable_name                    | value |+ ---------------------------------+-------+| rpl_semi_sync_slave_enabled     |  off   | |  rpl_semi_sync_slave_trace_level | 32    |+-------------------------------- -+-------+mariadb [none]> stop slave io_thread; mariadb [none]> show global variables like  ' rpl_semi% '; mariadb [none]> start slave io_thread; mariadb [none]>&nbsp show global variables like  ' rpl_semi% ';                         +------------- --------------------+-------+| variable_name                    | value |+---------------------------------+-- -----+| rpl_semi_sync_slave_enabled     | off   | |  rpl_semi_sync_slave_trace_level | 32    |+-------------------------------- -+-------+

MARIDDB master-slave replication, dual master model, semi-synchronous configuration