MYSQL master-slave switchover, mysql master-slave

MYSQL master-slave switchover, mysql master-slave

1. modify the configuration file
Read-only = 1 (master database)
# Read-only = 1 (slave database)

2. view the status of the Master/Slave Database
Show slave status \ G
Show master status \ G

3. Operate the slave Database
STOP SLAVEIO_THREAD;
Show processlist;
See Has read all relay log
Show slave status \ G
Stop slave;
Reset master;
Reset slave;

Grant replication slave on *. * to 'testuser' @ '10. 45.172.40 'identified by 'test123 ';
Show master status \ G

For example, after executing reset master;

ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER

Solution:

Modify vim/etc/my. cnf

Add log-bin = mysql-bin

Restart the mysqld service.

 

4. Operate the master database
Show master status \ G
Reset master;
Reset slave;

CHANGE MASTER

MASTER_HOST = '10. 45.172.37 ',

MASTER_USER = 'testuser ',

MASTER_PASSWORD = 'test123 ',

MASTER_PORT = 3306,

MASTER_LOG_FILE = 'mysql-bin.000001 ',

MASTER_LOG_POS = 98,

MASTER_CONNECT_RETRY = 60;

 

5. Restart the Master/slave database and enable the slave process.

Start slave

Show processlist;
Show slave status \ G