MARIADB semi-synchronous replication, master-slave replication and dual master replication

Knowledge background

??

I. MARIADB copy-related knowledge

??
Source of 1.mariadb
??
?? MySQL database is currently in the database market share only second only to the Oracle database, but also the open source database leader. Because MySQL database is open-source and high-performance, but also free (but not optimistic after Oracle acquisition), so the father of MySQL has opened a new MySQL branch mariadb,mariadb relative MySQL version of the update fast, the new features are installed quickly, The benefits of the official MySQL version mariadb also be absorbed quickly.
??
the process of 2.MARIADB replication
??
?? The MARIADB database supports synchronous replication, semi-synchronous replication, and asynchronous replication, in which one server acts as the primary server and one or more servers as slave servers. When the primary server is modified, the update is written to the binary log file, and a lasso of the file is maintained to trace the log loop. Log records are sent to data modifications from the server. When a binary log is obtained from the server to the primary server, it notifies the primary server to read the last successful update from the log, and the server receives all operations that occurred after that location.
??
3. Synchronous replication, asynchronous replication, semi-synchronous replication
??
?? Synchronous replication: Master commits the transaction until the transaction is committed at all slave, and the client is returned, and the transaction is completed. Disadvantage: There can be a significant delay in completing a transaction.
?? Asynchronous replication: Requests Binlog to Master when slave is ready. Cons: There is no guarantee that some events can be received by all slave.
?? Semi-synchronous replication: The mechanism of semi-synchronous replication is between synchronous and asynchronous, and master's transaction commits are blocked as long as a slave has received the event of the transaction and is logged. It does not wait for all slave to be notified that it has been received, and it just receives it, and does not wait for it to be fully executed and submitted. If the timeout is reached, it is not received and then the asynchronous copy is converted.
??
4. Common Architecture for Replication
??
?? Replication technology in the actual application has a variety of implementation architecture, the following are common:
?? 1. A master one from, that is, a master database, one from the database;
?? 2. A master multi-slave, that is, a master database, multiple from the database, mainly used in the write operation is not frequent, but has a large number of reading operations in the environment;
?? 3. The master is mutually prepared, that is, two MARIADB database servers each other as their own primary server, and at the same time as the other side of the copy from the server. It is mainly used to prevent a single point of failure of a database in an environment where database writes are more important.
?? 4. Dual master and multi-Slave, on the basis of the main master mutual preparation, in addition to a number of slave servers, the main users of the database write operations more, at the same time check the operation is more than the environment.
??
??

Implementation of replication

??

Master-slave replication (asynchronous based)

??

node	system	mariadb	IP
Master Node	CentOS7.4	10.3.7	192.168.99.130
From the node	CentOS7.4	10.3.7	192.168.99.131

??
Icon

2.1. Install MARIADB, here in order to save the direct Yum installed, other installation methods will not affect the subsequent operation; clean up iptalbes and selinux to prevent interference experiments

    iptables -F    iptables -X    setenforce 0    yum install mariadb-server -y

2. Modify the configuration file for the primary database

    # mariadb 10.2以前的版本    vim /etc/my.cnf    #10.3之后的版本已经放到/etc/my.cnf.d/下    

Add a field under [Mysqld]

#唯一标示的id段，不可重复server-id=1 #开启二进制日志，可以自定义路径和文件名log-bin=mysql-bin#开启二进制中继日志并定义命名格式relay-log=mysq-relay-bin#复制的过滤项，负责过滤掉不需要复制的库和表replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

3. Modify the configuration file from the data

# mariadb 10.2以前的版本    vim /etc/my.cnf    #10.3之后的版本已经放到/etc/my.cnf.d/下    

    #唯一标示的id段，不可重复    server-id=2    #开启二进制日志，可以自定义路径和文件名    log-bin=mysql-bin    #开启二进制中继日志并定义命名格式    relay-log=mysq-relay-bin    #复制的过滤项，负责过滤掉不需要复制的库和表    replicate-wild-ignore-table=mysql.%    replicate-wild-ignore-table=test.%    replicate-wild-ignore-table=information_schema.%

4. Start MARIADB

#主从都执行    systemctl start mariadb

5. Synchronize the database (if all new databases do not need to do this)
If data is already on the primary database, synchronize the data of the two databases before performing the replication.

#创建读锁，禁止写入数据flush tables with read lock;#同步完成后记得释放

6. Create a replication user, it is generally recommended to create an account that is designed to replicate data to avoid the security of the affected data.

#grant replication slave on *.* to ‘用户名‘@‘允许登陆的ip‘ identified by ‘密码‘;grant replication slave on *.* to ‘slave‘@‘192.168.99.131‘ identified by ‘slave‘;#使得授权生效flush privileges;#查看当前主库的二进制日志信息show master status;

7. Configure the 192.168.99.130 as the main library from the database

                #mariadb> change master to                                 #-> master_host=‘192.168.99.130‘, 指定主库ip                                #-> master_user=‘slave‘, 指定备份账号名                                #-> master_password=‘slave‘, 指定备份账号的密码                            #-> master_log_file=‘mysql-bin.000001‘, 主服务器的二进制日志名                                #-> master_log_pos=851; 二进制文件的位置mariadb> change master to                                 -> master_host=‘192.168.99.130‘,                                -> master_user=‘slave‘,                                -> master_password=‘slave‘,                                -> master_log_file=‘mysql-bin.000001‘,                                -> master_log_pos=851;

8. Open slave from the database

start slave;

9. Check the operating status

show slave status\G;

slave_IO_running和slave_SQl_running 是运行在从服务器节点上的主从复制线程，正常情况下均为yesreplicate_wild_ignore_table 显示的是不复制的库和表

??

Dual master replication (asynchronous based)

??
Dual master replication is based on the master-slave replication, the master and slave to do an identity reversal.
Architecture diagram

1. Continue to do the following on a master-slave basis

#上一次的从作为本次的主#grant replication slave on *.* to ‘用户名‘@‘允许登陆的ip‘ identified by ‘密码‘;grant replication slave on *.* to ‘slave‘@‘192.168.99.130‘ identified by ‘slave‘;#使得授权生效flush privileges;#查看当前主库的二进制日志信息show master status;

2. Perform the following actions from the database (the last master)

mariadb> change master to                                 -> master_host=‘192.168.99.131‘,                                -> master_user=‘slave‘,                                -> master_password=‘slave‘,                                -> master_log_file=‘mysql-bin.000001‘,                                -> master_log_pos=650;

3. Start the Backup

    start slave;#查看运行状态show status slave;

??

Configure semi-synchronous replication

??
Semi-synchronous replication needs to start the corresponding plug-in

#查看半同步复制协议是否开启show global variables like ‘%semi%‘;

Turn on the half-sync protocol (the old version needs to be installed manually, the new version is built-in, directly open and repaired)

#旧版本安装#主节点INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;#开启主节点半同步SET GLOBAL rpl_semi_sync_master_enabled=1;#设置超时时间SET GLOBAL rpl_semi_sync_master_timeout=2000;#从节点INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;开启从节点半同步复制SET GLOBAL rpl_semi_sync_slave_enabled=1;

#新版本#开启主节点半同步SET GLOBAL rpl_semi_sync_master_enabled=1;#设置超时时间SET GLOBAL rpl_semi_sync_master_timeout=2000;#从节点SET GLOBAL rpl_semi_sync_slave_enabled=1;

The master and slave of semi-synchronous replication are based on basic asynchronous replication, so the configuration process is the same as above.

MARIADB semi-synchronous replication, master-slave replication and dual master replication