Solve the problem of mysql master-slave multi-slave switch from database to master continue and slave synchronization tutorial

Objective: the master database is down and upgraded from the master database to the master database.

Prepare the environment:

1 web Server

3 MySQL servers

Environment description:

1. The bbs website service is running on the web, and the database service is provided by the MySQL-01.

2. MySQL-02 and-03 are MySQL-01 slave Library

Emergencies:

At this time, the MySQL machine is down and bbs.teddy.com cannot be opened, prompting that the database cannot be connected.

Master-slave multi-slave switch from database to master continue and slave synchronization process steps:

• Make sure all relay logs are updated.

Execute stop slave io_thread; show processlist on each slave database;

Until the Has read all relay log is displayed, the slave database update is completed.

All relay logs have been updated.

Log on to all slave database, view the master.info file, compare and select the largest pos as the new master database, here we choose mysql-02 as the new master database

• Log on to the mysql-02, execute stop slave; and enter the Database Directory, delete the Info and relay-log.info files, configure my. in the cnf file, enable log-bin. If there are log-slaves-updates and read-only files, comment out the files. Upgrade the slave database to the master database and execute reset master,

Create a user for synchronization

Grant replication slave on *. * to rep @ '10. 0.0.% 'indentified by '000000'; flush privileges;

Create a user for bbs website

Grant all on bbs. * TO 'bbs '@ 'web-01' identified by '000000'; flush privileges;

 

Update hosts files on all machines

[Root @ web-01 teddylu] # cat/etc/hosts127.0.0.1 localhost. localdomain localhost4 localhost4.localdomain4: 1 localhost. localdomain localhost6 localhost6.localdomain6 ########### by teddylu ########## 10.0.0.201 LVS-DR-0110.0.0.202 LVS-DR-0210.0.0.203 Web-0110.0.0.204 Web-02 #10.0.0.205 MySQL-01 #10.0.0.206 MySQL-0210.0.0.206 MySQL-01 # bbs website program information about the database, the MySQL-01 is the address of the database 10.0.0.207 NFS-Share

Log on to another slave database operation, that is, mysql-03

Change master TOMASTER_HOST = '10. 0.0.205 ', MASTER_PORT = 3306, MASTER_USER = 'Rep', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 123456; start slave;

Finally, check whether the master/slave synchronization is complete and whether the website can be opened.

Conclusion: it takes less than three minutes from the primary database to the slave database to take over (if the system architecture is well established in the early stage, the time will be shorter ). Use the master.info file to determine the new master database. In this way, your database has a higher consistency and integrity.

A