P
P
Pavel2020-08-22 10:06:57
MySQL
Pavel, 2020-08-22 10:06:57

What is the best way to set up Master-Slave GTID replication in MySQL?

Setting up gtid replication in MySQL. Both on the master and on the mysql 5.7
slave There is very little information on the network on gtid replication and it is all of the same type, the mysql docks also do not answer the question "how to configure optimal replication using gtid".

The problem is that the folder with binlogs grows very quickly (VERY FAST!) and the slave does not have time to synchronize, the lag only increases. What am I doing wrong? Maybe some tables should be ignored?

I take a backup from the master to the slave using Percona Xtrabackup.

Master my.cnf

[mysqld]
server-id = 1
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
log-slave-updates
log_bin = /var/log/mysql/mysql-bin.log
performance_schema_max_digest_length = 8192
max_digest_length = 8192
max_binlog_size= 500M
expire_logs_days = 4
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
key_buffer_size             = 16M
innodb_flush_method         = O_DIRECT
max_connections             = 200
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G


Slave my.cnf
server-id = 2
binlog-format = row
gtid_mode=ON
enforce-gtid-consistency=ON
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
skip_slave_start
log_slave_updates = 1 
read_only = ON
innodb_file_per_table = ON
innodb_buffer_pool_size = 3G
max_binlog_size = 500M
max_relay_log_size = 500M
relay_log_space_limit = 5G
relay_log_recovery = ON
expire_logs_days = 4
slave-parallel-workers = 0
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G


In one of the articles I saw an example where the following system tables were added to ignore replication on the master. Is it right to do so, and if not, what is the danger? Can the size of binlogs grow so fast because of these system tables?
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema


Also, I have not seen anywhere that with gtid replication the binlog format was mixed, everywhere only raw.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
V
Vitaly Karasik, 2020-08-22
@vitaly_il1

the slave does not have time to synchronize, the lag only increases.

And in general replication started?
If yes, then everything is correct.
The only question left is speed. Two questions: is the slave not weaker than the master? network normal?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question