Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to switch MySQL master and slave". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, immediately dump the hot data on the main database to the local disk ib_buffer_pool file

1) on the main database, execute the following command to dump 100% of the hot data in innodb buffer pool to the disk file / data/ib_buffer_pool:

Mysql > SET GLOBAL innodb_buffer_pool_dump_pct=100

2) on the main database, execute the following command to dump 100% of the hot data in innodb buffer pool to the disk file / data/ib_buffer_pool:

Mysql > SET GLOBAL innodb_buffer_pool_dump_now=ON

3) on the main library, execute the following command to display the progress of dump until you see the word completed complete:

Mysql > SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'

4) copy the master library / data/ib_buffer_pool disk file to the / data directory of the slave library:

5) on the slave library, execute the following command to load the hot data in / data/ib_buffer_pool into innodb buffer pool

Mysql > SET GLOBAL innodb_buffer_pool_load_now=ON

6) on the slave library, execute the following command to display the progress of load

Mysql > SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'

7) on the slave library, execute the following command to see how many pages there are in the LRU LIST of the library:

Mysql > select count (*) from information_schema.INNODB_BUFFER_PAGE_LRU

8) when you see the completion of the load progress display of the slave library, the hot data has been loaded on the slave library.

Mysql > SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'

Second, force the dirty data of the main database to be refreshed to the hard disk

Mysql > flush tables

Mysql > set global innodb_max_dirty_pages_pct=0 / / means that the dirty data in the BP is kept at 0%, that is, all the dirty data is brushed.

Mysql > show global status like'% dirty%'

Mysql > show engine innodb status\ G; / / generally, you don't need to wait for the last checkpoint to be equal to the current LSN, as long as the two are similar (show slave status\ G / / is executing in the slave library to see if sencond_behind_master is 0).

2) remove read-only from the library mysql

3) close the master library first and wait for the slave library to finish applying relay log

Mysql > show master status; / / record the poss value until the binlog pos is no longer changed when executed from the library

4) Log in to the main library and release vip

Ifconfig eth2:1 down (release vip 172.16.x.x)

5) bind vip to slave library

Ifconfig eth2:1 172.16.x.x/24

Arping-I eth2:1-c 1-s 172.16.x.x 172.16.x.254

The machine ping 172.16.x.x to other network segments is accessible.

Check whether there is a connection from the library show processlist to test whether the business is normal.

In this way, the manual master-slave switching database is completed. After switching, the running performance of the slave library is the same as that of the master library, and has little impact on the business.

6) re-establish the master-slave replication relationship

CHANGE MASTER TO MASTER_HOST = 'x.x.x.bicycle, MASTER_USER =' repl', MASTER_PASSWORD = 'xxxx', MASTER_PORT = 3306 master master logo FILEX recording binglog.001145, MASTER_LOG_POS=39951979

This is the end of the content of "how to switch MySQL master and slave". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.