MariaDB database is installed and configured based on GTID master-slave synchronous replication

Operating system: CentOS Linux release 7.3.1611 (Core)

Database:MariaDB-10.2.6-linux-glibc_214-x86_64

Main server:10.10.10.56

Slave server:10.10.10.163

1. What we need to do today is one-way master-slave synchronization

For information on MariaDB (MySQL) database installation, please refer to "Centos7.3 x64-bit binary installation (MySQL) MariaDB 10.1.20 database production". Next, the configuration process of the MariaDB (MySQL) server cluster begins.

2. Modify or add the following information in the MariaDB (MySQL) configuration file

vim /etc/my.cnf

主从通用配置

binlog-format = mixed #二进制日志记录的模式（高版本默认开启）
binlog-checksum = CRC32 #可使主机为写入二进制日志的事件写入校验（高版本默认开启）
sync-master-info = 1 #MariaDB依靠操作系统将master.info文件刷新到磁盘。
sync_relay_log_info = 1 #MariaDB依靠操作系统将relay-log.info文件刷新到磁盘。
expire_logs_days = 7 #日志文件过期天数,默认是 0,表示不过期 
master-verify-checksum = 1 #主服务器效验
slave-sql-verify-checksum = 1 #从服务器效验

3. In addition to the general configuration, the main server Master also needs to add the following code

server-id = 56 #MySQL服务器ID,不重复
log-bin = mysql-bin #二进制日志（默认开启）
sync-binlog = 1 #主服务器进行设置,用于事务安全
log-bin-index = mysql-bin

4. In addition to the general configuration, the slave server Slave also needs to add the following code

server-id = 163
relay-log = relay-bin #中继日志
slave-parallel-threads = 2 #设定从服务器的SQL线程数
#replicate-do-db = renwoleblogdb#复制指定的数据库,多个写多行
replicate-ignore-db = mysql #不备份的数据库,多个写多行
relay_log_recovery = 1 #从站崩溃后可以使用,防止损坏的中继日志处理。
log-slave-updates = 1 #slave将复制事件写进自己的二进制日志
relay-log-index = relay-bin

In addition, it is not necessary to enable the binary log on the Mysql slave server, but in some cases, it must be set. For example, if the slave is the master of another slave, bin_log must be set. I leave it on by default here.

5. The above is just a brief introduction to the role of each parameter. The specific settings of these parameters need to be adjusted according to the actual situation of the user. For details, please go to the official website

《复制和二进制日志服务器系统变量》
https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/

关于系统变量的兼容性，可参阅官方

《MariaDB与MySQL兼容性》
https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

6. Main server Master authorization configuration

Create a dedicated account on the main MariaDB server and authorize database permissions, as well as remote access from the server IP

# mysql -uroot -p
Enter password:【输入你的MySQL密码回车】
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'renwoleuseracc'@'%' IDENTIFIED BY 'renwoleuserpass'; //创建Slave专用备份账号
MariaDB [(none)]> flush privileges; //刷新MySQL权限
MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; //查看授权情况
MariaDB [(none)]> flush tables with read lock; //锁定数据库防止master值变化
MariaDB [(none)]> show master status; //获取master状态值
+-----------------+----------+------------+-----------------+
| File |Position |Binlog_Do_DB|Binlog_Ignore_DB |
+-----------------+----------+------------+-----------------+
| mysql-bin.000006| 627 | | |
+-----------------+----------+------------+-----------------+
1 row in set (0.00 sec)

7. Once you have obtained the correct Binlog location (file name and offset) during backup, you can use the BINLOG_GTID_POS() function to calculate the GTID

MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000006", 627);
+------------------------------------------+
| BINLOG_GTID_POS('mysql-bin.000006', 627) |
+------------------------------------------+
| 0-56-4 |
+------------------------------------------+
1 row in set (0.01 sec)

8. Configure from server Slave

As the official said, starting from MariaDB version 10.0.13, mysqldump will automatically complete this work and write the GTID in the export file. Just set –master-data or –dump-slave and set –gtid at the same time.

In this case, the new SLAVE can set the starting position of replication by setting the value of @@gtid_slave_pos, use CHANGE MASTER to pass this value to the main library, and then start replication:

# mysql -uroot -p
Enter password:【输入你的MySQL密码】
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-56-4";
MariaDB [(none)]> change master to master_host='10.10.10.56',MASTER_PORT = 3306,master_user='renwoleuseracc',master_password='renwoleuserpass',master_use_gtid=slave_pos; //进行主从授权
MariaDB [(none)]> START SLAVE; //启动Slave
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.10.10.56
 Master_User: renwoleuseracc
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 627
 Relay_Log_File: relay.000035
 Relay_Log_Pos: 537
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ...
 ...
 ...
 Using_Gtid: Slave_pos
 Gtid_IO_Pos: 0-56-4

9. If both Slave_IO_Running and Slave_SQL_Running are YES, it indicates that the slave service is running. The Using_Gtid column determines whether the GTID value is consistent.
Description:

master_host 表示master授权地址
MASTER_PORT MySQL端口
master_user 表示master授权账号
master_password 表示密码
master_use_gtid GTID变量值

10. Next unlock the main server database table

MariaDB [(none)]> unlock tables; //解锁数据表
MariaDB [(none)]> show slave hosts; //查看从服务器连接状态
MariaDB [(none)]> show global status like "rpl%"; //查看客户端

11. View all relevant parameters of relay from the server Slave

MariaDB [(none)]> show variables like '%relay%';

12. The master-slave configuration has been completed. Now, no matter what is added, modified, deleted, or checked on the master server, it will be synchronized to the slave server. You can perform relevant tests according to your own needs.
About master slave reset syntax

Reset the core syntax of master

RESET MASTER; means that executing RESET MASTER will delete all binary log files and create a blank binary log file with a numeric suffix of .000001. RESET MASTER will not affect the working status of the SLAVE server, so executing this command will cause Slave The Master's binlog cannot be found, causing synchronization to fail.

Reset slave’s core syntax

RESET SLAVE; means; RESET SLAVE will clear the synchronization position on the slave and delete all old synchronization relay log files, but the slave service must be stopped before resetting (STOP SLAVE)

The above is the detailed content of MariaDB database is installed and configured based on GTID master-slave synchronous replication. For more information, please follow other related articles on the PHP Chinese website!