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

This article introduces the relevant knowledge of "how MySQL migrates data". 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!

one。 Test case / process

Currently, developers go to Cloud (external MySQL migrates to CDB) to provide a variety of solutions. Developers whose MySQL instances have public network IP can directly use Tencent Cloud database migration tool to complete the migration. For other migration methods, please see link in this migration task. All MySQL instances of the developer are provided with a public network proxy IP, so you can directly select the migration tool to complete data import.

The basic principle of the migration tool: obtain the basic MySQL instance configuration of the source instance through the high-permission account provided by the instance to be migrated, and synchronize it to the target CDB instance; export the source instance to the CDB instance directly through mysqldump and import it; and establish a master-slave relationship between the source database instance and the target CDB to synchronize new data. The communication between the CDB instance and the source IDC is initiated by a server with an external network through NAT.

1. Basic functions of the migration tool

Create the migration task according to the guide on the Tencent Cloud database console page, and observe the backend log of the migration task on the background management page.

After the task starts to run, check the change of agent machine traffic, write CDB and other data display.

Knowledge points: how to generate a large amount of data for the test database. A tool called mysql_gen_data is recommended here. The process of generating test data and importing it into MySQL is as follows:

#! / bin/bash./mysql_gen_data-f "% 1nGravity 0sRecrow0sRecorder 0s0sPower0s"\-n 10000000 > random.datmysql-uroot-p *-e "create database cdbtest;use cdbtest;\ CREATE TABLE cdbtest_tb\ (C1 varchar, c2 varchar, c3 varchar,\ c4 varchar, c5 varchar, c6 varchar)\ ENGINE=InnoDB DEFAULT CHARSET=utf8;" for i in {1.. 10} Do echo "$(date'+% T') round $I start" echo "prepare data..." Sed-I "s / ^ / $I /" random.dat echo "insert data..." Mysql-uroot-p * cdbtest-e "LOAD DATA local INFILE'/ data/random.dat' into table cdbtest_tb fields terminated by',';" echo "$(date'+% T') round $I end" done

Check this test task with Tencent Cloud console at the backend, and the migration has been completed successfully.

two。 Master-slave and slave computer establish master-slave synchronization with CDB

Since the developers of this migration will use the slave computer of their self-built IDC to migrate data to CDB, the simple relationship is shown in the figure below. No similar operation has been done with the migration tool before, so this test is carried out.

Knowledge: how to configure the master-slave relationship of MySQL. The master-slave configuration of the tested MySQL is as follows: (master MySQL)

Server_id = 98log_bin = binlogbinlog_format = ROWinnodb_stats_on_metadata = off

Check this test task with Tencent Cloud console at the backend, and the migration has been completed successfully.

3. Multi-instance + larger binlog concurrent synchronization

After relevant testing, the developer plans to migrate 15 instances to CDB concurrently, generating a total of about 100G of binlog per day. Since the migration tool was not used concurrently before, and it was updated more than big data in a single day, the user scenario was tested ahead of time. The basic architecture of the test is as follows: 15 MySQL instances are mapped to different ports on one server, 15 MySQL instances are established as master and slave with 15 CDB instances at the same time, and migration tasks are initiated.

Knowledge: how to create multiple MySQL instances on one server? The mysqld_multi tool included in MySQL used here is actually just a perl script. The configuration of multiple instances can be enabled as follows (/ etc/my.conf), depending on the memory size, and multiple mysqld configuration items can be opened:

[mysqld_multi] mysqld = / usr/bin/mysqld_safemysqladmin = / usr/bin/mysqladminuser = root password = * [mysqld1] port = 3306datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockpid-file=/var/lib/mysqld.pidserver_id = 11 log_bin = binlogbinlog_format = ROWexpire_logs_days=1innodb_stats_on_metadata = offsymbolic-links=0user = rootsql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES [mysqld2] port = 3312datadir=/data/mysql12socket=/data/mysql12/mysql.sockpid-file=/data/mysql12/mysqld.pidserver_id = 12log_bin = binlogbinlog_format = ROWexpire_logs_days=1innodb_stats_on_metadata = offuser = rootsymbolic-links=0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_ tables [mysqld3] .mysqld4.

Then use the corresponding number of instances in the mysqld_multi start 1-4 startup configuration item. Start multiple MySQL instances as shown below:

By timing the update corresponding to the data of the database instance, a large amount of binlog is generated. A single update generates 700Mbinlog, which is executed every 2 hours, and 7001215g / day is generated. The simple code is as follows:

#! / bin/shSET_STRING= `date + "% s" `date "/ data/log/update.log" NOW_STRING= `date + "[% Y-%m-%d% H:%M:%S]" `for i in {12.26} doBEGIN_TIME= `date + "[% Y-%m-%d% H:%M:%S]" `echo ${BEGIN_TIME} "Update data in this time is:" $SET_STRING > > $LOG_NAMEecho ${BEGIN_TIME} "Update database" ${I} "start..." > > $LOG_NAMEmysql-uroot migrate$ {I}-S / data/mysql$ {I} / mysql.sock-e "update tb set data1=" ${SET_STRING} "END_TIME= `date +" [% Y-%m-%d% H:%M:%S] "`echo ${END_TIME}" Update database "${I}" end... "> > $LOG_NAMEdone

Use the database migration tool to establish 15 migration tasks, and both the console and background checks are migrated successfully:

At the same time, in order to verify the data integrity in the case of a large number of binlog, a simple script is written to check whether the data is updated regularly. The script is as follows: (after testing, it is found that the masterIP of the CDB instance can be directly connected through the Guangzhou jump machine, so the IDC update data is pulled directly in the Guangzhou jump machine script, and the CDB instance data is compared and written to the log.)

#! / bin/shDATA_CORRECT=$1NOW_TIME= `date + "[% Y-%m-%d% H:%M:%S]" `cat my.file | while read linedoIP= `echo $line | awk-F "'{print $1} '`PORT= `echo $line | awk-F"' {print $2} '`DATABASE= `echo $line | awk-F "' {print $3} '`DATA_INBASE= `mysql-uroot-P$ {PORT}-h$ {IP}-p123456cdb ${DATABASE}-e" select data1 from tb limit 1\ G "| grep data1 | awk-F"'{print $2} '`echo ${NOW_TIME} "[INFO] Data you want to update to ${DATABASE} is:" $DATA_CORRECTecho ${NOW_TIME} "[INFO] Data from Database" $DATABASE "is:" $DATA_INBASEif [$DATA_INBASE-eq $DATA_CORRECT] thenecho ${NOW_TIME} "[SUCCESS]" $DATABASE "update succesfully!" elseecho ${NOW_TIME} "[ERROR]" $DATABASE "update ERROR!" Fidone

From the verification log, you can see that the data updates have been completed successfully.

two。 Developer migrates test data records

After the completion of the above internal tests, the developer carried out three migrations by himself, and the relevant data are as follows:

The bandwidth performance of a migration.

As the developer's exit bandwidth is only about 500Mbps, after testing, it is found that the migration bottleneck mainly appears in the bandwidth limit. The actual concurrency bandwidth will be confirmed during the second phase of migration.

three。 Problems encountered

Create a master-slave unable to connect to the source database for the first time

Phenomenon: as shown in the figure, the source database cannot be connected after each task is built.

Error:Can't connect to MySQL server on 10.*

Analysis and solution: because the essence of the migration tool is that the CDB proxy is connected to the IDCMySQL instance through the public network through NAT, the proxy system time of CDB is different from that of the NAT public network machine, and the connection is reused by IDC, which leads to the inconsistent time before and after the connection is established, which is considered by the system as abnormal packet, discarded and connection failure. Directly modify the kernel parameters of IDC server, that is, net.ipv4.tcp_timestamps = 0 and net.ipv4.tcp_tw_recycle = 0

Stored procedure migration failed for cross-version migration

Phenomenon: as shown in the figure, developers have the phenomenon that the proc table cannot be migrated during the migration process.

ERROR:Can't load from mysql.proc. The table is probably corrupted

Solution: after the colleague of CDB developer confirmed that there are exceptions in the proc table migrated across versions due to different field definitions, the release version skips the proc table solution.

Creating a new database in the migration test caused the binlog import to fail

Phenomenon: an error occurred in the migration task, the stored procedure could not be migrated, and the binlog append failed

Errno:1049:Error 'Unknown database' xxxx'on query.

Solution: only a certain database is selected for this migration. During the migration, a new database is created and binlog is enabled. As a result, the binlog pulled by CDB has new database information, which does not match the migrated database. The solution is to avoid DDL operations during the migration process.

That's all for "how MySQL migrates data". 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.