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依靠操作系統(tǒng)將master.info文件刷新到磁盤。
sync_relay_log_info = 1 #MariaDB依靠操作系統(tǒng)將relay-log.info文件刷新到磁盤。
expire_logs_days = 7 #日志文件過期天數(shù),默認是 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線程數(shù)
#replicate-do-db = renwoleblogdb#復制指定的數(shù)據(jù)庫,多個寫多行
replicate-ignore-db = mysql #不備份的數(shù)據(jù)庫,多個寫多行
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

《復制和二進制日志服務器系統(tǒng)變量》
https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/

關于系統(tǒng)變量的兼容性，可參閱官方

《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'; //創(chuàng)建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; //鎖定數(shù)據(jù)庫防止master值變化
MariaDB [(none)]> show master status; //獲取master狀態(tài)值
+-----------------+----------+------------+-----------------+
| 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; //解鎖數(shù)據(jù)表
MariaDB [(none)]> show slave hosts; //查看從服務器連接狀態(tài)
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!