Wang Liang is a senior engineer at Tencent Cloud. He joined Tencent in 2010 and was once in charge of Tencent social product CDN image business and operation and maintenance of dynamic acceleration business. Now I am responsible for the solution work of database products.

A large domestic game developer has more than 130 MySQL instances deployed in IDC, with a total storage of 20 TERabytes. For service needs, all instances are migrated to Tencent cloud CDB for MySQL. In order to ensure the smooth operation of the business migration, Tencent Cloud database team conducted preliminary investigation and research on the migration process and tools, and timely solved the four major problems found in the process. The actual migration experience is shared as follows:

I. Test cases/procedures

At present, developers on the cloud (migration of external MySQL to CDB) provide a variety of solutions, among which MySQL instances of developers with external IP can directly use Tencent cloud database migration tool to complete the migration (for other migration methods, see link in this migration task all MySQL instances of the developer have external proxy IP for use. Therefore, the migration tool is directly selected to complete the data import.

The basic principle of the migration tool is as follows: Obtain the basic MySQL instance configuration of the source instance by using the high-permission account provided by the instance to be migrated, and synchronize the configuration to the target CDB instance. Mysqldump directly transfers the source instance to the CDB instance and then imports it. The source database instance and the target CDB establish a master-slave relationship to synchronize new data. The CDB instance communicates with the source IDC in NAT mode through an outband server.

1. Migrate basic functions of the tool

Establish the migration task on the console page of Tencent Cloud database according to the guidance; On the background management page, view background logs of the migration task.

After the task starts running, it detects the change of agent machine traffic and shows data such as CDB writing





Knowledge point: How to generate a large amount of data for the test database. The recommended tool is mysql_gen_data. The procedure for generating test data and importing it into MySQL is as follows:

#! / bin/bash, / mysql_gen_data - f "% % 1 n, % 100 s, 100 s and 100 s %, % 100 s, 100 s" % \ -n > 10000000 random. Dat mysql - uroot - p * * * * * * * * * * * E "the create database cdbtest; use cdbtest; \ CREATE TABLE cdbtest_tb \ (c1 varchar(100),c2 varchar(100),c3 varchar(100), \ c4 varchar(100),c5 varchar(100),c6 varchar(100)) \ 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" doneCopy the code



The background and Tencent cloud management console check the test task and the migration is successfully completed.

2. Establish primary/secondary synchronization between the primary and secondary computers and the CDB

Since the developer of this migration will use their self-built IDC to migrate data from the PC to CDB, the simple relationship is shown in the following figure. Similar operations have not been carried out before using the migration tool, so this test is conducted.



How to configure the primary/secondary relationship of MySQL? The primary/secondary configuration of MySQL is as follows:

server_id = 98
log_bin = binlog
binlog_format = ROW
innodb_stats_on_metadata = offCopy the code







The background and Tencent cloud management console check the test task and the migration is successfully completed.

3. Multiple instances + large binlog concurrent synchronization

After testing, the developer plans to migrate 15 instances to CDB concurrently in the first phase, generating a total of about 100 GB of binlogs per day. The user scenario was tested in advance because the migration tool was not used concurrently and had large data updates in a single day. The basic architecture of the test is shown as follows: Enable 15 MySQL instances to map to different ports on a server, establish primary and secondary relationships between 15 MySQL instances and 15 CDB instances at the same time, and initiate migration tasks.



How to create multiple MySQL instances on one server? Mysqld_multi = mysqLD_multi = mysqLD_multi/etc/my.confMysqld = mysqld = mysqld = mysqld

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root 
password = ******

[mysqld1]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysqld.pid
server_id = 11 
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off

symbolic-links=0
user = root
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld2]
port = 3312
datadir=/data/mysql12
socket=/data/mysql12/mysql.sock
pid-file=/data/mysql12/mysqld.pid
server_id = 12
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off
user = root
symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3]
........
[mysqld4]
.......Copy the code

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



A large number of binlogs are generated by periodically updating the data of the corresponding database instance. 700 mbinlogs are generated by a single update every two hours and 700 binlogs are generated every day1215=126G. Simple code is as follows:

#! /bin/sh SET_STRING=`date +"%s"` LOG_NAME="/data/log/update.log" NOW_STRING=`date +"[%Y-%m-%d %H:%M:%S]"` for i in {12.. 26} do BEGIN_TIME=`date +"[%Y-%m-%d %H:%M:%S]"` echo ${BEGIN_TIME}" Update data in this time is:"$SET_STRING >> $LOG_NAME echo ${BEGIN_TIME}" Update database"${i} "start..." >> $LOG_NAME mysql -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_NAME doneCopy the code

useDatabase migration tool15 migration tasks were established, and the console and background checks were successfully migrated:



At the same time, in order to check the data integrity in the case of a large number of binlogs, a simple script is written to periodically check whether the data is updated. The script is as follows: (After testing here, it is found that the masterIP of CDB instance can be directly connected through the Guangzhou board jumper. Therefore, IDC update data can be pulled directly from the Script of Guangzhou board jumper, and CDB instance data can be compared and written into the log.)

#! /bin/sh DATA_CORRECT=$1 NOW_TIME=`date +"[%Y-%m-%d %H:%M:%S]"` cat my.file | while read line do IP=`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_CORRECT echo ${NOW_TIME}"[INFO]Data from Database "$DATABASE" is:"$DATA_INBASE if [ $DATA_INBASE -eq $DATA_CORRECT ] then echo ${NOW_TIME}"[SUCCESS]"$DATABASE" update succesfully!" else echo ${NOW_TIME}"[ERROR]"$DATABASE" update ERROR!" fi doneCopy the code

By checking the logs, it can be seen that all data updates are successfully completed.



Developers migrate test data records

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



Bandwidth performance of a migration. As the developer’s outbound bandwidth was only about 500Mbps, the migration bottleneck was found to be mainly in bandwidth limitations. Confirm the actual concurrent bandwidth during phase 2 migration.

3. Problems encountered

  1. Symptom When the primary and secondary databases fail to connect to the source database when the primary and secondary databases are created for the first time: As shown in the figure, the primary and secondary databases fail to connect to the source database after each task is created

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


    Solution: Because the essence of the migration tool is that the CDB agent is connected to the IDCMySQL instance through NAT through the external network, the system time of the CDB agent is different from that of the NAT external network machine, and the CONNECTION reuse is enabled in IDC. As a result, the time before and after the connection is set up is inconsistent. The system considers it as an abnormal packet, and the connection fails. Modify the KERNEL parameters of the IDC servernet.ipv4.tcp_timestamps = 0andnet.ipv4.tcp_tw_recycle = 0Can be

  2. Stored procedure migration failure across version migration: As shown in figure 1, the proc table cannot be migrated during the migration

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


    Solution: CDB developer confirmed that the proc table migrated across versions was abnormal due to different field definitions, and the proc table was skipped when the version was released.

  3. Symptom The binlog import failure caused by the creation of a new database during the migration test: An error occurs in the migration task, the stored procedure cannot be migrated, and the binlog fails to be appended

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


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

4. To summarize

Anticipation is the end of everything. It is because we have a number of functional tests, performance tests and boundary condition tests prepared before the customer migration that there are no data inconsistency, live network operation switchover failure and other abnormal situations in the formal data migration. Experience is accumulated for large-scale database instance migration of live network. Up to now, more than 130 MySQL instances have been successfully migrated to Tencent Cloud CDB and started live network operation. Based on such experience, we have the ability and confidence to provide Tencent cloud users with more high-quality and efficient data storage migration services.

Tencent_cloud_sdk 2.0. Zip