Shulou(Shulou.com)06/01 Report--

Below to bring you about how to achieve MySQL data migration content, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after seeing how MySQL data can be migrated.

Due to the migration of CVMs, a set of hardware-aging MySQL master and slave CVMs need to be replaced with new CVM. According to the overall evaluation, the newer 5.6 sub-version is adopted without changing the version. These are the left and right sides shown in the following figure.

If you want to do this complete switch, in fact, there are many ways and ideas, I chose one to do.

First of all, I underestimated the data and thought it was all InnoDB tables, so I used-- single-transaction to export the table data from the main library and imported it directly. There seems to be no obvious exception, but start slave and find a data conflict.

2017-08-07 17:40:31 75211 [Warning] Slave: Duplicate entry '632107' for key 'PRIMARY' Error_code: 1062

2017-08-07 17:40:31 75211 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave S

QL thread with "SLAVE START". We stopped at log 'binlog.000019' position 934115248

So take a closer look at the data and find that there is a database with a large number of MYISAM tables. In this case, backup MYISAM table data is likely to have problems, because-- single-transaction will not take effect, backup can be done using mysqlhotcopy and other methods, I do not like MYISM tables, and this person is actually quite lazy, do not want to do so, but also have a certain impact on the main database. So I set my sights on Congku.

In order to ensure data consistency, I can stop slave first and export data in a limited time, which is transparent to the main library.

It is important to note that the option of using mysqldump to export using master-data is bound to conflict, so we can get the specific offset directly based on the result of show slave status\ G without the option of master-data=2.

# mysqldump-all-databases-master-data=2 > master.sql

Mysqldump: Error: Binlogging on server not active

The new master library and the new slave library are built in this way, and the data comes from the old slave library, so that if the service is configured, the master library will have 3 binlogDump threads.

During the whole process of data import, you can see from the processlist data that it is still time-consuming to process MyISAM tables, so I just waited for several threads of MyISAM large tables, State, to switch from "Repair by sorting" and "Repair with keycache".

At this time, we need to change, we can point the new slave database to the new master database, that is, to achieve cascading replication, to do this work, after leveling the data, it is safe to stop the slave thread of the new master database and let the new slave database re-apply logs (show master status\ G) from the new master database. In this way, everything will be done in a cascading environment.

Some fine-tuning in the later stage can also be done naturally.

When it comes to formal switching, there is little work to be done. The new main library can use reset slave all.

The above is a very basic environment switching, if you can go a step further and do a good job at the network level, in fact, this switch will be more transparent and completely imperceptible.

Do you think it's what you want about how to migrate MySQL data above? If you want to know more about it, you can continue to follow our industry information section.

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.