Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to establish GTID master-slave replication in MySQL5.6". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The experiment is based on

MySQL 5.6.x (5.6.30)

Ps. MySQL 5.7.It is more convenient to open GTID.

Master: 192.168.1.186

Slave: 192.168.1.187

0 synchronize the data of two mysql instances

Execute it on slave first.

SET @ @ global.read_only=ON

Then backup the master data through mysqldump, xtrabackup and other tools, and restore it to slave.

Users with super privileges may be required because read_only recovery is enabled.

0 close both mysqld

0 modify the configuration file and start it in GTID mode:

Both master and slave configuration files need to be changed

At least the configuration file [mysqld] needs to be added:

Log-bin

Relay-log = relay-log

Log-slave-updates

Gtid_mode = ON # 5.6is static parameter, and after 5.7.6 is dynamic parameter

Enforce-gtid-consistency = ON # 5.6 is the Boolean type, and after 5.7.6 is the enumerated type, with two more options

In addition, make sure that the server_id of master and slave are inconsistent.

(slave can also start GTID without opening binlog and store it in mysql.gtid_executed.)

0 establish a connection

To create a replication user on master:

GRANT replication slave ON *. * to repl@'192.168.1.187' IDENTIFIED BY 'repl@pwd'

FLUSH PRIVILEGES

And execute on slave:

CHANGE MASTER TO

MASTER_HOST = '192.168.1.186'

MASTER_PORT = 3306

MASTER_USER = 'repl'

MASTER_PASSWORD = 'repl@pwd'

MASTER_AUTO_POSITION = 1

START SLAVE

0 close read_only on master

SET @ @ global.read_only=OFF

This is the end of the introduction of "how to establish GTID master-slave replication in MySQL5.6". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.