Detailed explanation of mysql GTID master-slave replication

MySQL replication is called mysql synchronization, ab replication, etc. in the industry. The professional name is: replication

Replication is one-way and can only be copied from the master to the slave. The delay is basically at the millisecond level.

There can be multiple slaves in a set of replication structures. For general scenarios, only one master is recommended.

The master user writes data and generates events and records them in the binary log.

The slave receives the binlog uploaded by the master and then applies it in sequence to reproduce the user operations on the master.

The smallest unit of recording is an event. The first 4 bytes of the log are a magic number, and the next 19 bytes record formatt desc event: FDE

MySQL5.6 adds GTID replication

Requirements:

1. The main database is online, and master-slave replication is performed without stopping the service of the main database.

2. Add a new cluster library

Operation:

1. Export data in the main database (the main database is running normally);

　　

2. Transfer the sql file of the main database to the cluster database;

　　　　　　　　　　　　　　　　　　　　　　　　　 should should must be

Mysql > GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'3.9.8.%' IDENTIFIED BY 'replpass';

Mysql > flush privileges;

5. Configure the firewall of the slave server to allow the firewall to pass port 3306

# vim /etc/sysconfig/iptables

Add

-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT (allow port 3306 to pass through the firewall)

Restart the firewall service to make it effective

#service iptables restart

6 、                         Modify the configuration file of the Cong library

# vim /etc/my.cnf

Add

　　　

　　　

7. Use the account and password created on the master mysql to log in and copy on the slave server

Mysql> change master to master_host='3.9.8.13', master_user='repluser',master_password='replpass', master_auto_position = 1; ;

9、 Detection of master-slave replication

Mysql> show slave status\G

10、 Problem set

1) 1) Start master-slave replication, error 1872 ：slave failed to initialize relay log info structure from the repository.

　　　　

mysql> reset slave;
mysql> change master to master_host='3.9.8.13', master_user='repluser',master_password='replpass',master_auto_position=1;
mysql> start slave;

The above is the detailed content of Detailed explanation of mysql GTID master-slave replication. For more information, please follow other related articles on the PHP Chinese website!