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;
