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

This article introduces the relevant knowledge of "how to innodb buffer pool from the database". In the operation of actual cases, many people will encounter such a dilemma. Next, 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!

Recently, a mysql master-slave switching operation was done in production. When the slave library was promoted to master, it was found that the pressure from the slave library was extremely high at the beginning. The reason for the analysis should be that the thermal data of the slave database innodb_buffer_pool was not immediately loaded, resulting in a large number of disk reads. Our innodb_buffer_pool was set to 42G, so it took about 100 minutes to get the business back to normal.

Later, I think it should be possible to dump the hot data in the master library innodb buffer pool into a disk file, and then copy the disk file to the slave library, and let the disk file from the dump of the slave library load the hot data into the innodb buffer pool of the slave library. So I looked at the MySQL official documentation and found this solution.

The specific steps are as follows:

1) on the main database, execute the following command to set innodb_buffer_pool_dump_pct to 40%, which means that you want to dump the hot data of innodb buffer pool 40% (which can also be set to 100% depending on the situation) into the disk file / data/ib_buffer_pool:

Mysql > SET GLOBAL innodb_buffer_pool_dump_pct=40

2) on the main database, execute the following command to dump 40% of the hot data in innodb buffer pool to 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 of the slave database, you can switch between master and slave. Close the master database first, and wait for the slave library to apply relay log, then bind the vip to the slave database, thus completing the manual master-slave switching database. 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.

This is the end of the introduction of "how to innodb buffer pool the Heat data from the Library". Thank you for your 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.