[MySQL] Replication database with GTID

 

Master::::::::::::::::::::::::

systemctl stop mysqld

vi /etc/my.cnf

The following parameters should be added under the [mysqld] section of my.cnf file

server-id = 1

log-bin = mysql-bin

binlog_format = row

relay-log = relay-log-server

relay-log = relay-log-server

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

systemctl start mysqld

mysql>create user 'repl_user'@'%' identified by '123456';

mysql>Grant replication slave on *.* to 'repl_user'@'%';

mysql>SET @@GLOBAL.read_only = ON;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SET GLOBAL read_only = ON;

mysql> show master status;

show global variables like 'gtid_executed';

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events -hex-blob --host=localhost --port=3306 --user=root  --password=123456 > /tmp/mysqlbackup_dump.sql

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

slave::::::::::::::::

service mysqld stop

vi /etc/my.cnf

server-id = 2

log-bin = mysql-bin

binlog_format = row

relay-log = relay-log-server

relay-log = relay-log-server

read-only = ON

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

service mysqld start

mysql> show global variables like 'gtid_executed';

mysql> source mysqlbackup_dump.sql ;

mysql> show global variables like 'gtid_executed';

CHANGE MASTER TO MASTER_HOST = '192.168.114.135',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

start slave;

mysql> show slave status \G

SELECT RECEIVED_TRANSACTION_SET FROM peformance_schema.replication_connection_status;

Switchover::::::::::::::

Master:

SET @@GLOBAL.read_only = ON;

FLUSH TABLES WITH READ LOCK;

SET GLOBAL read_only = ON;

FLUSH PRIVILEGES;

Slave:

Make sure that all slaves has processed any statements in their relay log with:

mysql> STOP SLAVE IO_THREAD;

mysql> SHOW PROCESSLIST; to see `Has read all relay log` state.on slave 1, promote it to become a master with:

mysql> STOP SLAVE;

mysql> RESET MASTER;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

on old master (or slave 2), point to new master with:

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.114.136',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

mysql> START SLAVE;

Failover::::::::::::::

mysql> STOP SLAVE IO_THREAD;

mysql> SHOW PROCESSLIST; to see `Has read all relay log` state.on slave 1, promote it to become a master with:

mysql> STOP SLAVE;

mysql> RESET MASTER;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;