MariaDB GTID Replication Synchronization

MariaDB GTID Replication Synchronization

Gtid:global Transaction ID, global transaction ID, no two thing IDs are the same in the entire master-slave replication schema. The global transaction ID is the ID number of the Mster server that generates a 128-bit uuid+ thing, and the UUID identifies the identity of the primary server, which is absolutely unique throughout the master-slave replication architecture. And even if the main server is replaced, the UUID will not change but inherit the current master server's UUID identity.

First, environmental recognition

Master ip:10.6.0.96

Slave ip:10.6.0.138

Configure the local hosts

Vim/etc/hosts

10.6.0.96 Master.mysql

10.6.0.138 Slave.mysql

Second, the configuration file confirmation, ensure that the configuration file has the following configuration:

Binlog_format binary log format, there are several types of row, statement and mixed;

Log-slave-updates,

Report-port

Report-host: Used to start the Gtid and meet other requirements of the subsidiary;

Master-info-repository

Relay-log-info-repository enables these two items, which can be used to ensure the security of the binary and slave servers in the case of crashes;

Sync-master-info enabled to ensure no loss of information;

Slave-parallel-workers set the number of SQL threads from the server; 0 to turn off multithreaded replication;

Binlog-checksum

Master-verify-checksum

Slave-sql-verify-checksum enable all verification functions related to replication;

Binlog-rows-query-log-events enables the use of information related to logging events in binary logging to reduce the complexity of troubleshooting;

Log-bin enable the binary log, which is the basic premise of ensuring the replication function;

Server-id the ID number of all servers in the same replication topology must be unique.

Third, configure the master configuration file

Modify the following content

Log-bin=/opt/local/mysql/binlog/mysql-bin #二进制日志文件目录

Server-id = 1 #从服务器不能跟此id重复

Binlog_format=row #二进制日志文件格式

Innodb_file_per_table=1 #innodb表空间独立

Log-slave-updates=true #从master取得并执行的二进制日志写入自己的二进制日志文件中

Add the following content

Master-info-repository=table #用于实现在崩溃时保证二进制及从服务器安全的功能;

Relay-log-info-repository=table #用于实现在崩溃时保证二进制及从服务器安全的功能;

Sync-master-info=1 #启用之可确保无信息丢失

slave-parallel-threads=2 #设定从服务器的SQL线程数; 0 means turn off multithreaded replication

BINLOG-CHECKSUM=CRC32 #启用复制有关的所有校验功能

Master-verify-checksum=1 #启用复制有关的所有校验功能

Slave-sql-verify-checksum=1 #启用复制有关的所有校验功能

Binlog-rows-query-log_events=1 #启用之可用于在二进制日志记录事件相关的信息, can reduce the complexity of troubleshooting;

Report-host=master.mysql #master host name, you must be able to ping the

report-port=3306 #端口

Restart MySQL

Service mysqld Restart

Master creates accounts for master-slave replication

Mysql-uroot-p

Grant replication Slave,replication Client on * * to "rep" @ ' 10.6.0.138 ' identified by ' rep12345 ';

Flush privileges;

Iv. Configuring the Slave configuration file

Modify the following content

Log-bin=/opt/local/mysql/binlog/mysql-bin #二进制日志文件目录

Server-id = Ten #从服务器不能跟此id重复

Binlog_format=row #二进制日志文件格式

Innodb_file_per_table=1 #innodb表空间独立

Log-slave-updates=true #从master取得并执行的二进制日志写入自己的二进制日志文件中

Relay-log=/opt/local/mysql/relaylog/s74-relay-bin

Add the following content

Master-info-repository=table #用于实现在崩溃时保证二进制及从服务器安全的功能;

Relay-log-info-repository=table #用于实现在崩溃时保证二进制及从服务器安全的功能;

Sync-master-info=1 #启用之可确保无信息丢失

slave-parallel-threads=2 #设定从服务器的SQL线程数; 0 means turn off multithreaded replication

BINLOG-CHECKSUM=CRC32 #启用复制有关的所有校验功能

Master-verify-checksum=1 #启用复制有关的所有校验功能

Slave-sql-verify-checksum=1 #启用复制有关的所有校验功能

Binlog-rows-query-log_events=1 #启用之可用于在二进制日志记录事件相关的信息, can reduce the complexity of troubleshooting;

Report-host=slave.mysql #slave host name, you must be able to ping the

report-port=3306 #端口

# login on the slave server using the account password created on master MySQL

Mysql-uroot-p

Change Master to master_host= ' 10.6.0.96 ', master_user= ' rep ', master_password= ' rep12345 ', Master_use_gtid=current_pos ;

Start slave;

# See if Gtid is enabled:

Show Processlist;

# View sync Status:

show slave status\g;

Appears Got fatal error 1236 from master when reading data from binary log: ' error:connecting slave requested to start from GT ID 0-10-223, which is not in the master ' s Binlog '

This type of error

# in Master view Position

Show master status;

Record Position

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|     mysql-bin.000008 |              1062 |                  | |

+------------------+----------+--------------+------------------+

# Execute on slave

Stop slave;

Change MASTER to master_log_file= ' mysql-bin.000008 ', master_log_pos=1062;

Start slave;

show slave status \g;

MariaDB GTID Replication Synchronization