MySQL migration solution in different scenarios _ MySQL

Summary of considerations for MySQL Migration Solution overview I. Why is MySQL migration a task of DBA routine maintenance. Migration is to remove an existing object to ensure its integrity and continuity. Why migration is required in the production environment?

MySQL Migration Solution Overview

MySQL migration practices

Notes

Tips

Summary

I. Why migration?

MySQL migration is a task of DBA routine maintenance. Migration is to remove an existing object to ensure its integrity and continuity.

Migration is required in the production environment in the following situations:

1. insufficient disk space. For example, for some old projects, the selected model does not necessarily apply to databases. As time passes, there may be a shortage of hard disks;

2. Business bottlenecks. For example, the project uses a single machine to undertake all the read and write services, increasing the business pressure and being overwhelmed. If the I/O pressure is within the acceptable range, the read/write splitting scheme is adopted;

3. machine bottlenecks. Machine bottlenecks mainly occur in disk I/O capability, memory, and CPU. in addition to optimizing the bottleneck, selecting migration is a good solution;

4. Project transformation. In some cases, databases in different data centers may increase nodes in different data centers or migrate machines from one data center to another. For example, if different services share the same server, migration is also performed to relieve the pressure on the server and facilitate maintenance.

In a word, migration is a last resort. Migration is implemented to ensure smooth and continuous operation of the business.

II. MySQL Migration Solution Overview

MySQL migration implements backup and recovery while ensuring the smooth and continuous operation of the business. The problem is how to quickly and securely perform backup recovery.

First, back up. Each slave or Slave node of the master node is backed up. This backup may be full backup, or incremental backup. The online backup method may use mysqldump (MySQL is a utility used to store databases. It mainly generates an SQL script, including the commands required to re-create a database from scratch), xtrabackup (a tool for backing up InnoDB data, supports online hot backup, mydumper (a high-performance multi-thread backup and recovery tool for MySQL and Drizzle) is a good alternative to InnoDB Hotbackup.

You can use mysqldump for small-capacity backup (less than 10 GB. However, for large-capacity databases (GB or TB level), mysqldump is not suitable and will produce locks, which takes too long.

In this case, you can select xtrabackup or directly copy the data directory. The direct copy data directory method allows you to use rsync for transmission between different machines. the time consumed is related to the network. Xtrabackup is mainly used for backup and network transmission. If there is a backup file for full backup or the specified database, this is the best way to obtain the backup. If the standby database can stop the service, directly copying the data directory is the fastest way. If the standby database does not allow service interruption, we can use xtrabackup (InnoDB tables are not locked), which is the best compromise for backup.

Second, restore. For backup files of small databases (less than 10 GB), we can import them directly. Recovery of large-capacity databases (GB or TB level) is not difficult after the backup files are obtained to the local machine. For specific restoration methods, see section 3.

III. MySQL migration practices

In the interview, why should we perform migration and what needs to be done for migration? next, how should we perform the operation in the production environment. Different application scenarios have different solutions.

Assume that the following conventions exist:

1. to protect privacy, the server IP address and other information in this article have been processed;

2. if the server is in the same data center, use the D segment of the server IP address to replace the server. for specific IP addresses, see the architecture diagram;

3. if the server is in different data centers, use the C and D segments of the server IP address instead of the server. for specific IP addresses, see the architecture diagram;

4. methods are provided in each scenario, but no detailed commands are provided for each step. On the one hand, this will lead to too long articles. on the other hand, I think that as long as you know the methods, the specific method will be targeted, only depending on the degree of knowledge and the ability to obtain information;

5. For more information, see section 4.

3.1. Scenario 1: master-slave structure migration from the database

Let's start with a simple structure. Project A is originally A master-slave structure. 101 is the master node and 102 is the slave node. Because of business needs, 102 is migrated from the node to 103. the architecture diagram is 1. 102 the data capacity of the Slave node is too large to be backed up using mysqldump. After communication with R & D personnel, a consistent solution is formed.

The following figure shows the MySQL architecture of Project.

The specific method is as follows:

1. R & D switches 102 of the read business to the master database;

2. check the status of MySQL 102 (mainly in the process list), observe the machine traffic, and stop the service of MySQL 102 Slave node after confirmation;

3. 103 create a new MySQL instance. after the MySQL instance is built, stop the MySQL service and back up the whole data directory mv elsewhere;

4. copy 102 of the mysql data directory to 103 using rsync;

5. at the same time of copying, authorize 101 to grant 103 the permission to pull the binlog (replication slave, replication client );

6. after the copy is completed, modify the server_id in the 103 configuration file. Be sure not to be consistent with the server_id in 102;

7. start the MySQL instance in MySQL 103. pay attention to the permissions on the data file path and data directory in the configuration file;

8. enter the 103 MySQL instance and use show slave status to check the SLAVE database STATUS. you can see that Seconds_Behind_Master is decreasing;

9. after Seconds_Behind_Master is changed to 0, the synchronization is complete. in this case, you can use pt-table-checksum to check the data consistency between 101 and 103, but it is time-consuming and has an impact on the master node, data consistency can be verified together with development;

10. communicate with R & D personnel. in addition to data consistency verification, account permissions must be verified to prevent access errors after business migration;

11. after completing the above steps, you can coordinate with R & D to switch 101 of the Read services to 103 and observe the service status;

12. if there is no problem with the service, the migration is successful.

3.2. Scenario 2: Migrate the specified database from the master-slave structure

After we know how to migrate only one master node and one slave database, let's take a look at how to migrate both master and slave nodes. Because different services access the same server at the same time, the pressure on a single database is too high and management is not convenient. Therefore, we plan to migrate the master node 101 and Slave node 102 to the new machines 103 and 104,103 as the master node, and 104 as the Slave node in the architecture. During this migration, only the specified database needs to be migrated. the database capacity is not too large and the data is not real-time.

Is the MySQL architecture of Project B.

The procedure is as follows:

1. 103 and 104 create an instance and establish a master-slave relationship. the master node and Slave node are in no-load status;

2. 102 export data. the correct method is to configure a scheduled task and perform export during off-peak hours. here, mysqldump is selected;

3. 102 collect accounts and permissions required for the specified database;

4. after 102 data is exported, use rsync to transmit the data to 103 and compress the data if necessary;

5. 103 import data. The data is automatically synchronized to 104 to monitor the server status and MySQL status;

6. 103 the import is complete, 104 the synchronization is complete, and 103 the data and account permissions collected according to 102 are notified to R & D personnel after the authorization is completed;

7. after completing the preceding steps, you can perform R & D and collaboration to migrate 101 and 102 of services to 103 and 104, and observe the business status;

8. if there is no problem with the service, the migration is successful.

3.3. Scenario 3: Migrate a specified database from a master-slave structure in bilateral mode

Next, let's take a look at how a master-slave structure migrates a specified database. This is also because business sharing leads to heavy server pressure and chaotic management. Therefore, we plan to migrate the master node 101 and Slave node 102 to the new machine 103, 104, 105, and 106,103 as the master node, 104 as the Slave node of 104, and 103 as the master node of 105, 106 acts as the Slave node and architecture of 105. During this migration, only the specified database needs to be migrated. the database capacity is not too large and the data is not real-time. We can see that this migration is very similar to Scenario 2, and there are only two migrations.

Is the architecture of MySQL in Project C.

The procedure is as follows:

1. 103 and 104 create an instance and establish a master-slave relationship. the master node and Slave node are in no-load status;

2. 102 export the data of the specified database required by 103. the correct method is to configure a scheduled task and perform export during off-peak hours. here mysqldump is selected;

3. 102 collect accounts and permissions required for the specified database required by 103;

4. 102 after the data of the specified database is exported to 103, rsync is used to transmit the data to 103. compress the data if necessary;

5. 103 import data. The data is automatically synchronized to 104 to monitor the server status and MySQL status;

6. 103 the import is complete, 104 the synchronization is complete, and 103 the data and account permissions collected according to 102 are notified to R & D personnel after the authorization is completed;

7. after completing the preceding steps, migrate 101 and 102 of services to 103 and 104, and observe the business status;

8. 105 and 106 create an instance and establish a master-slave relationship. the master node and Slave node are in no-load status;

9. 102 export the data of the specified database required by 105. the correct method is to configure a scheduled task and perform export during off-peak hours. here mysqldump is selected;

10. 102 collect accounts and permissions required for the specified database required by 105;

11. 102 after the data of the specified database is exported to 105, rsync is used to transmit the data to 105. compress the data if necessary;

12. 105 import data. The data is automatically synchronized to 106 to monitor the server status and MySQL status;

13. 105 the import is complete, 106 the synchronization is complete, and 105 the data and account permissions collected according to 102 are notified to R & D personnel after the authorization is completed;

14. after completing the above steps, migrate 101 and 102 of the businesses to 105 and 106, and observe the business status;

15. if there is no problem with all services, the migration is successful.

3.4. Scenario 4: master-slave full structure migration

Next, let's take a look at how the master-slave structure is fully migrated. Similar to Scenario 2, however, all databases are migrated here. Due to the IO bottleneck of the 101 master node, we plan to migrate the 101 master node and the 102 Slave node to the new machine 103 and 104,103 as the master node, and 104 as the Slave node. After the migration is completed, the previous master node and Slave node are deprecated and the architecture is deployed. This migration is a full-database migration with a large capacity and needs to be ensured in real time. This migration is special because the new slave database is replaced first, and then the new master database is replaced. So the practice is a little more complicated.

The following figure shows the MySQL architecture of Project D.

The specific method is as follows:

1. R & D switches 102 of the read business to the master database;

2. check the STATUS of MySQL 102 (mainly in process list and master status), observe the traffic of the machine, and stop the service of MySQL 102 Slave node after confirmation;

3. 104 create a MySQL instance. after the MySQL instance is built, stop the MySQL service and back up the whole data directory mv to other places. Note that the operation here is 104, that is, the slave database of the future;

4. copy 102 of the mysql data directory to 104 using rsync;

5. at the same time of copying, authorize 101 to grant 104 the permission to pull the binlog (replication slave, replication client );

6. after the copy is completed, modify the server_id in the 104 configuration file. Be sure not to be consistent with the server_id in 102;

7. start the MySQL instance in MySQL 104. pay attention to the permissions on the data file path and data directory in the configuration file;

8. enter the 104 MySQL instance and use show slave status to check the SLAVE database STATUS. you can see that Seconds_Behind_Master is decreasing;

9. after Seconds_Behind_Master is changed to 0, the synchronization is complete. in this case, you can use pt-table-checksum to check the data consistency between 101 and 104, but it is time-consuming and has an impact on the master node, data consistency can be verified together with development;

10. in addition to data consistency verification, you also need to verify account permissions to prevent access errors after business migration;

11. work with R & D personnel to switch the previous 102 read services from nodes to 104;

12. use 102 of the data to change 103 to 101 slave nodes;

13. now we have reached the key point. we need to change 104 to 103 slave database;

-104 stop slave;

-103 stop slave IO_THREAD;

-103 stop slave SQL _THREAD, remember MASTER_LOG_FILE and MASTER_LOG_POS;

-104 start slave until to MASTER_LOG_FILE and MASTER_LOG_POS;

-104 stop slave again;

-104 reset slave all clears SLAVE database configuration information;

-103 show master status. remember MASTER_LOG_FILE and MASTER_LOG_POS;

-103 Grant binlog access permissions to 104;

-104 change master to 103;

-104 restart MySQL, because after reset slave all, check the slave status, Master_Server_Id is still 101, rather than 103;

-104 after MySQL is restarted, SLAVE restarts automatically. check whether IO_THREAD and SQL _THREAD are YES;

-103 start slave;

-Check the status of 103 and 104. we can see that the Slave node with 104 in the past has changed to 101.

14. the synchronization relationship between 103 and 101 is broken before the business migration;

15. after completing the above steps, you can coordinate with R & D to switch 101 of read/write businesses back to 102 and 104 of Read businesses. Note that both 101 and 103 can be written at this time. you must ensure that 101 is switched to 103 without writing. you can use flush tables with read lock to LOCK 101 and then switch the business to 103. Note: it must be executed during off-peak hours. remember;

16. observe the service status after switching;

17. if there is no problem with the service, the migration is successful.

3.5. Scenario 5: Dual-master architecture migration across data centers

Next, let's take a look at how to migrate the dual-master architecture across data centers. For disaster tolerance, a project uses a dual-master structure across data centers and can be written on both sides. Due to disk space problems, you need to replace the machines in location. We plan to migrate the master node 1.101 and Slave node 1.102 to the new machines 1.103 and 1.104 at the same time. 1.103 serves as the master node and 1.104 serves as the Slave node. 2.101 and 2.102 of B remain unchanged, but after the migration is complete, 1.103 and 2.101 are mutually active. Architecture. Because it is a dual-master structure, both sides write at the same time, if you want to replace the master node, you must have a node to stop the service.

Is the architecture of MySQL migration in Project E.

The procedure is as follows:

1. 1.103 and 1.104 create an instance and establish a master-slave relationship. the master node and Slave node are in no-load status;

2. check the STATUS of MySQL 1.102 (mainly in the process list). observe that the master status does not change. Observe the machine traffic and stop the service of 1.102 slave nodes after confirmation;

3. 1.103 create a new MySQL instance. after the MySQL instance is built, stop the MySQL service and back up the whole data directory mv elsewhere;

4. copy 1.102 of the mysql data directory to 1.103 using rsync;

5. at the same time of copying, authorize 1.101 to grant 1.103 the permission to pull the binlog (replication slave, replication client );

6. after the copy is completed, modify the server_id in the 1.103 configuration file. Be sure not to be consistent with the server_id in 1.102;

7. start the MySQL instance in MySQL 1.103. pay attention to the permissions on the data file path and data directory in the configuration file;

8. enter the 1.103 MySQL instance and use show slave status to check the SLAVE database STATUS. you can see that Seconds_Behind_Master is decreasing;

9. after Seconds_Behind_Master is changed to 0, the synchronization is complete. in this case, you can use pt-table-checksum to check the data consistency between 1.101 and 1.103, but it is time-consuming and has an impact on the master node, data consistency can be verified together with development;

10. We use the same method to change 1.104 to 1.103 slave database;

11. communicate with R & D personnel. in addition to data consistency verification, they also need to verify account permissions to prevent access errors after business migration;

12. at this time, we need to change 1.103 to 2.101 Slave Database. for specific practices, refer to scenario 4;

13. it should be noted that the single and double numbers of 1.103 must be consistent with those of 1.101;

14. after completing the above steps, you can coordinate with R & D to cut 1.101 of read/write businesses to 1.103 and 1.102 of Read businesses to 1.104. Observe the business status;

15. if there is no problem with the service, the migration is successful.

3.6. Scenario 6: Multi-instance migration across data centers

Next, let's take a look at the proof of multi-instance Cross-data-center migration. For more information about the instance relationship of each machine, see. The purpose of this migration is to repair the data. Create 2.117 and 7938 instances on 7939 to replace the instances with previous data exceptions. For business reasons, some databases are only written in the region, and some databases are only written in the B region, so synchronization filtering exists.

Is the MySQL architecture of project F.

The procedure is as follows:

1. 1.113 for 7936 instances, innobackupex is used for data backup. you must specify the database and add the slave-info parameter;

2. after the backup is complete, copy the compressed file to 2.117;

3. 2.117 create a data directory and related directories involved in the configuration file;

4. 2.117 use innobackupex to restore logs;

5. 2.117 use innobackupex to copy data;

6. 2.117 modify the configuration file. Note the following parameters: replicate-ignore-db, innodb_file_per_table = 1, read_only = 1, and server_id;

7. 2.117 change data directory permissions;

8. 1.112 authorization, so that 2.117 has the permission to pull the binlog (replication slave, replication client );

9. 2.117 change maste to 1.112. for details about log file and log pos, see xtrabackup_slave_info;

10. 2.117 start slave to view the SLAVE database status;

11. the method for setting up 2.117 on 7939 is similar, but the configuration file must specify replicate-wild-do-table;

12. verify data consistency and verify account permissions together with developers to prevent access errors after business migration;

13. after completing the above steps, you can coordinate with R & D to migrate the corresponding services to 2.117 and 7938 instances of 7939. Observe the business status;

14. if there is no problem with the service, the migration is successful.

IV. Notes

After introducing the migration solutions for different scenarios, pay attention to the following points:

1. database migration. If an event is involved, remember to enable the event_scheduler parameter on the master node;

2. regardless of the migration scenario, you must always pay attention to the server status, such as disk space and network jitter. In addition, continuous monitoring of services is also essential;

3. do not find errors in log file and log pos of change master to. If an error is specified, data inconsistency is the consequence;

4. run the script in the $ HOME directory instead of the data directory;

5. the migration can be automated using scripts, but do not be self-defeating. any scripts must be tested;

6. think twice and think twice before each command is executed. the parameter meanings of each command must be understood;

7. in a multi-instance environment, MySQL is closed in the form of mysqladmin. do not close the instance in use;

8. add read_only = 1 to the slave database, which avoids many problems;

9. the server_id of each machine must be inconsistent; otherwise, synchronization exceptions may occur;

10. correctly configure replicate-ignore-db and replicate-wild-do-table;

11. when creating an instance, remember to set innodb_file_per_table to 1. in some of the above scenarios, because the previous instance has a parameter of 0, ibdata1 is too large, and backup and transmission consume a lot of time;

12. when using gzip to compress data, note that after the compression is complete, gzip will delete the source file.

13. all operations must be performed on slave or slave nodes. if you operate on the master node, the master node may be down;

14. xtrabackup backup will not lock the InnoDB table, but will lock the MyISAM table. Therefore, before performing the operation, check whether the tables in the current database use the MyISAM storage Engine. If yes, either independently or change the Engine of the table;

5. skills

In the practice of MySQL migration, you can use the following techniques:

1. for any migration log file, refer to relay_master_log_file (the binlog LOG name on the master is being synchronized). For log pos, refer to exec_master_log_pos (the POS point where the current binlog LOG is being synchronized;

2. using rsync to copy data can be used in combination with CT and nohup, which is definitely a wonderful combination;

3. innobackupex can be used for data backup and gzip compression;

4. when using innobackupex to back up data, you can add the -- slave-info parameter to facilitate the slave database;

5. when innobackupex is used to back up data, you can add the -- throttle parameter to restrict IO and reduce the impact on the business. You can also add the -- parallel = n parameter to speed up backup, but note that the -- parallel parameter is invalid when tar stream compression is used.

6. for data backup and recovery, you can list to-do items, draw a process, and prepare the commands to be executed in advance;

7. copy the folder locally quickly. there is a good way to use rsync and add the following parameter:-avhW -- no-compress -- progress;

8. data can be quickly copied between different partitions. dd can be used. Or back up the data to the hard disk in a more reliable way and put it on the server. There are even more powerful remote direct delivery of hard drives.

VI. Summary

This article explains why migration is required. Next, we will talk about the migration solution, the actual migration practices in different scenarios, and finally the precautions and practical skills. To sum up, the following points are shown:

First, the purpose of migration is to ensure smooth and continuous operation of the business;

Second, the core of migration is how to continue master-slave synchronization. we need to find a solution between different servers and different businesses;

Third, the permissions between different MySQL servers must be considered for service switching. the read/write splitting sequence and master-slave relationship of different machines must be considered. the impact of cross-data center calls on services must be considered.

Readers can refer to the ideas provided in this article during migration implementation. However, to ensure that each operation runs correctly, you still need to think twice.

To put an aside, the most important thing to prove your ability is to put everything under your control.