mysql5.7 Gtid-based master-slave replication

Basic Environment

Version 5.7.14

Main Library ip:192.168.1.100 port:3306

From library ip:102.168.1.101 port:3306

Building considerations

Main Library Configuration

Gtid-mode=on

Enforce-gtid-consistency=1

Binlog_format==row

server-id=3306100

Log-bin=/data/mysql/mysql3306/logs/mysql-bin

binlog_cache_size=4m

Skip_slave_start=1

Configure from Library

Gtid-mode=on

Enforce-gtid-consistency=1

Server-id=3306101

Binlog_format=row

Log-bin=/data/mysql/mysql3306/logs/mysql-bin

Skip_slave_start=1

Configure the reboot to make the configuration file effective

/etc/init.d/mysqld restart

Viewing in the main library

Show global variables like "%server%";

can view server_uuid number

Show master status;

Now it's empty, found no Gtid record.

Create a table

CREATE TABLE G1;

Check Again

Show master status;

You can view the information in the Executed_gtid_set

Primary Library Backup

Mysqldump-uroot-p--single-transaction--master-data=2-a >backup.sql

The backup file contains a statement that needs to be noted

SET @ @global. gtid_purged= "A57a3eb0-5d40-11e6-9609-080027f7e774:1-3"

It was passed from 4 onwards.

Then transfer the backup file to the vault from the Library for recovery

Mysql-uroot-p <backup.sql

(If you back up again, you will get an error and if you need to back up again, you can do a reset master from the library)

Then synchronize the change to Operation

Change Master to master_host= "192.168.1.100", master_port=3306,master_user= ' Repl ',

Master_password= ' Repl4slave ', master_auto_position=1;

Then you can test it

Extended

In the execution from the library

Show global variables like "%server%"; You can see the UUID number from the vault.

If you are performing an operation from a library

Through show master status;

You can also see the uuid+ thing number from the library.

mysql5.7 Gtid-based master-slave replication