[Original] MySQL migration scenario under different scenarios

Why do I want to migrate

MySQL migration is a task in the daily maintenance of DBAs. The original meaning of the migration is to remove the actual objects and ensure the integrity and continuity of the object . Like the soft sand, two innocent children, to move a pile of sand to other places, casting the heart of the castle of longing.

In the production environment, the following conditions need to be migrated, as follows:

1. There is not enough disk space. For example, some old projects, the chosen model does not necessarily apply to the database. Over time, hard drives are likely to be in short supply;

2. Bottlenecks in the business. For example, the project uses a single machine to undertake all the reading and writing business, business pressure increased, overwhelmed. If the IO pressure is in an acceptable range, a read-write separation scheme is used;

3. Machine bottlenecks. machine bottleneck mainly in the disk IO capability, memory, CPU, at this time in addition to the bottleneck to do some optimization, choose migration is a good solution;

4. Project Modification . Some projects of the database exist across the computer room situation, may be in different room to add nodes, or the machine from one computer room to another room. For example, different businesses share the same server, in order to alleviate the server pressure and ease of maintenance, will also do the migration.

In a word, migration is a last resort. Implement the migration effort to keep the business running smoothly and continuously.

Two MySQL Migration scenario Overview

MySQL migration is nothing more than work around the data, and then continue to extend, nothing more than to ensure that the business smooth and continuous operation of the premise of doing backup recovery. The problem is how to restore the backup quickly and safely.

On the one hand, backup. for each master node from the node or the standby node, there is a backup. This backup may be full, and may be an incremental backup. The way to back up online might be to use mysqldump, possibly xtrabackup, or maybe mydumper. We can use mysqldump for backups of small-capacity (below 10GB) databases. But for high-capacity databases (hundreds of GB or TB), we can't use mysqldump backups, which, on the one hand, generate locks; On the other hand, it takes too long. In this case, you can choose Xtrabackup or copy the data directory directly. Direct copy of Data Catalog method, different machine transmission can use rsync, time-consuming and network-related. Using Xtrabackup, time consuming is primarily in backup and network transmission. This is the best way to get backups if you have a backup file that is fully prepared or that specifies a library. If the standby can allow the service to be stopped, copying the data directory directly is the quickest method. If the repository does not allow the service to stop, we can use Xtrabackup (the InnoDB table is not locked), which is the best compromise for completing the backup.

On the other hand, recovery. for small-capacity (10GB or less) database backup files, we can import directly. Recovering from a large-capacity database (hundreds of GB or TB level) is not difficult when you get the backup file to the local machine. The specific recovery method can be referred to in section Iii.

three MySQL migration combat

We figure out why the migration is going to be done, and how the migration will be done, and then look at how the production environment operates. Different application scenarios, there are different solutions.

Before you read the actual practice, assume and the reader have the following conventions:

1. In order to protect privacy, the server IP information in this paper is processed;

2. If the server in the same room, with the server IP D segment instead of the server, the specific IP please refer to the architecture diagram;

3. If the server in a different room, with the server IP of the C segment and D segment instead of the server, the specific IP please refer to the architecture diagram;

4. Each scenario gives a method, but does not give details of what commands to execute at each step, because on the one hand, this leads to an excessive length of the article, on the other hand, I think that as long as the method is known, the specific approach will be head-on, only depends on the level of knowledge and the ability to access information;

5. Please refer to section fourth for precautions in the actual combat process.

3.1 Scene One by one master one migration from the library from the structure

Follow from easy to difficult thinking, we start from the simple structure. A project, originally a master one from the structure. 101 is the primary node, and 102 is the slave node. Migrating 102 from node to 103 due to business needs, architecture diagram I. 102 The data capacity from the node is too large to be backed up using the Mysqldump form. and research and development communication, the formation of a consistent plan.

Figure one by one primary one migration from the library schema diagram

This is done as follows:

1. Research and development to cut 102 of the reading business to the main library;

2. Confirm the 102 MySQL status (mainly see the PROCESS LIST), observe the machine traffic, confirm the error, stop 102 service from the node;

3.103 new MySQL instance, after completion, stop the MySQL service, and the entire data directory MV to other places to do backup;

4. Use rsync to copy 102 of the entire MySQL data directory to 103;

5. Copy at the same time, in 101 authorization, so that 103 has the permission to pull Binlog (REPLICATION SLAVE, REPLICATION CLIENT);

6. To be copied, modify the server_id in the 103 configuration file, and be careful not to agree with 102;

7. Start the MySQL instance in 103, note the data file path in the configuration file and the permissions of the data directory;

8. Enter 103 MySQL instance, use SHOW SLAVE status check from the library state, you can see seconds_behind_master in descending;

9.seconds_behind_master becomes 0, indicating that the synchronization is complete, at this time can be used pt-table-checksum Check 101 and 103 of the data is consistent, but time-consuming, but also has an impact on the primary node, you can work with the development of data consistency validation;

10. And research and development communication, in addition to do data consistency verification, but also need to verify the account permissions, in order to prevent the return of business access error;

11. Complete the above steps, can and research and development coordination, 101 of the portion of the reading business to 103, observe the business status;

12. If the business is not a problem, prove the migration is successful.

3.2 Scene 21 Primary One migrating the specified library from the structure

We know how to move from one master to the next, and then see how to migrate the master and slave nodes at the same time. A single library is too stressful to manage due to the simultaneous access of the same server to different businesses. Therefore, the intention is to migrate the primary node 101 and from node 102 simultaneously to the new machines 103 and 104,103 to act as the master node, and 104 to act as slave node, schema two. This migration only requires migrating the specified libraries, which are not too large and can guarantee that the data is not real-time.

Figure 21 Primary one migration from structure to specified library schema diagram

The specific practice is as follows:

1, 103 and 104 new instances, build the master-slave relationship, at this time the main node and from the node is no load;

2, 102 export data, the correct way is to configure the timing task, in the business of low peak to do export operations, here the choice is mysqldump;

3, 102 to collect the required accounts and permissions for the specified library;

4, 102 export data, using rsync transmission to 103, when necessary to do the compression operation;

5, 103 Import data, the data will be automatically synchronized to 104, monitoring Server status and MySQL status;

6, 103 import completed, 104 synchronization completed, 103 according to 102 collected account authorization, completed, notify the research and development inspection data and account permissions;

7, after the completion of the above, can research and development collaboration, 101 and 102 of the business migration to 103 and 104, observing the business status;

8, if the business is not a problem, prove the migration success.

3.3 Scene 31 primary one from the structure of the bilateral migration of the specified library

Next look at a master one from the structure of the bilateral migration of the specified library how to do. The same is because of business sharing, resulting in server pressure, management confusion. Therefore, the intention is to migrate the primary node 101 and from node 102 to the new Machine 103, 104, 105, 106,103 as the primary node of 104, 104 as the slave node of 103, 105 as the primary node of 106, 106 as the slave node of 105, the schema diagram three. This migration only requires migrating the specified libraries, which are not too large and can guarantee that the data is not real-time. As we can see, this migration is similar to scenario two, with two migrations.

Figure 31 The primary one from the structure of the bilateral migration of the specified library architecture diagram

The specific practice is as follows:

1, 103 and 104 new instances, build the master-slave relationship, at this time the main node and from the node is no load;

2, 102 Export 103 required to specify the library data, the correct way is to configure the scheduled task, in the business of low peak to do export operations, here is the choice of mysqldump;

3, 102 collect 103 required accounts and permissions for the specified library;

4, 102 Export 103 required to complete the specified library data, using rsync transmission to 103, when necessary to do the compression operation;

5, 103 Import data, the data will be automatically synchronized to 104, monitoring Server status and MySQL status;

6, 103 import completed, 104 synchronization completed, 103 according to 102 collected account authorization, completed, notify the research and development inspection data and account permissions;

7, after the completion of the above, and research and development collaboration, 101 and 102 of the business migration to 103 and 104, observing the business status;

8, 105 and 106 new instances, build the master-slave relationship, at this time the main node and from the node is no load;

9, 102 Export 105 required to specify the library data, the correct way is to configure the scheduled task, in the business of low peak to do export operations, here is the choice of mysqldump;

10, 102 collect 105 required accounts and permissions for the specified library;

11, 102 export 105 required to complete the specified library data, using rsync transmission to 105, when necessary to do the compression operation;

12, 105 Import data, the data will be automatically synchronized to 106, monitoring Server status and MySQL status;

13, 105 import completed, 106 synchronization completed, 105 according to 102 collected account authorization, completed, notify the research and development inspection data and account permissions;

14, after the completion of the above, and research and development collaboration, 101 and 102 of the business migration to 105 and 106, observing the business status;

15. If all business is not a problem, prove the migration is successful.

3.4 Scene 41 master one from the structure complete migration master and slave

Next look at a master one from the structure of the complete migration master-slave how to do. Similar to scenario two, but here is the migration of all libraries. Because of the bottleneck in the 101 master IO, it is intended to migrate the primary node 101 and from node 102 to the new machine 103 and 104,103 as the primary node, and 104 to act as the slave node. After the migration is complete, the previous master node and the slave node are deprecated, and the schema is shown in Figure four. This migration is a full-library migration with large capacity and needs to be guaranteed in real time. This time the migration is special, because the strategy is to replace the new from the library, and then replace the new Main library. So the approach is slightly more complicated.

Figure 41 Master-one migration from the structure to the master-slave architecture diagram

The specific approach is this:

1, research and development will be 102 of the reading business cut to the main library;

2, confirm 102 MySQL status (mainly see PROCESS list,master status), observe the machine flow, confirm the error, stop 102 from the node service;

3, 104 new MySQL instance, after completion, stop the MySQL service, and the entire data directory MV to other places to do backup, note that the operation here is 104, that is, the future from the library;

4, 102 of the entire MySQL data directory using rsync copy to 104;

5, the copy at the same time, in 101 authorization, so that 104 have the permission to pull Binlog (REPLICATION SLAVE, REPLICATION CLIENT);

6, to be copied to complete, modify the 104 configuration file in the server_id, pay attention to do not and 102 on the same;

7, start the MySQL instance in 104, note the data file path in the configuration file and the permissions of the data directory;

8, enter 104 MySQL instance, use SHOW SLAVE status check from the library state, you can see seconds_behind_master in descending;

9, Seconds_behind_master changed to 0, indicating that the synchronization is complete, at this time can be used pt-table-checksum Check 101 and 104 of the data consistent, but time-consuming, but also has an impact on the main node, can be developed together with the validation of data consistency;

10, in addition to do data consistency verification, but also need to verify the account permissions, in order to prevent the operation of access errors after the move;

11, and research and development collaboration, the previous 102 from the node's reading business cut to 104;

12, the use of 102 of data, the 103 into 101 from the node, the same way;

13, next to the key place, we need to turn 104 into 103 from the library;

-104 STOP SLAVE;
-103 STOP SLAVE Io_thread;
-103 STOP SLAVE Sql_thread, remembering Master_log_file and Master_log_pos;
-104 START SLAVE UNTIL to the aforementioned master_log_file and Master_log_pos;
-104 STOP SLAVE again;
-104 RESET SLAVE All clears the configuration information from the library;
-103 SHOW MASTER STATUS, remembering Master_log_file and Master_log_pos;
-103 Authorization to 104 access to the Binlog;
-104 Change MASTER to 103;
-104 Restart MySQL, since reset SLAVE all, view SLAVE status,master_server_id is still 101 instead of 103;
-104 MySQL Restart, SLAVE back automatically restart, at this time to see if Io_thread and Sql_thread is YES;
-103 START SLAVE;
-When you look at the status of 103 and 104, you can see that the previous 104 is 101 from the node and now becomes 103 from the node.

14, before the business migration, disconnected 103 and 101 of the synchronization relationship;

15, to finish the above steps, can and research and development coordination, 101 of the Read and write business back to 102, read business cut to 104. It is important to note that at this point both 101 and 103 can be written, you need to ensure that 101 is cut to 103 without writing, you can lock 101 with the FLUSH TABLES with READ Lock, and then the business is cut to 103. Attention, must be business low peak execution, remember;

16, after the handover is completed, observe the business status;

17, if the business is not a problem, prove the migration success.

3.5 Scene Five double main structure transfer across engine room

Let's take a look at how the dual main structure migrates across the engine room. A project for disaster-tolerant consideration, the use of the cross-room, using a double-main structure, both sides can write. Because of disk space problems, you need to replace the machine A. The intention is to migrate the primary node 1.101 and from node 1.102 to the new machine 1.103 and 1.104,1.103 as the master node, and 1.104 to act as slave node. The 2.101 and 2.102 of the B-ground remain unchanged, but after the migration is complete, 1.103 and 2.101 are mutually dual masters. Architecture Figure Five. Because it is a double-main structure, both sides write at the same time, if you want to replace the primary node, you must have a node stop service.

Fig. Five The migration architecture diagram of double main structure across the machine room

The specific practice is as follows:

1, 1.103 and 1.104 new instances, build the master-slave relationship, at this time the main node and from the node is no load;

2, confirm 1.102 MySQL status (mainly see the PROCESS LIST), pay attention to observe the MASTER status no longer change. Observe the machine flow, confirm the error, stop 1.102 service from the node;

3, 1.103 new MySQL instance, after completion, stop the MySQL service, and the entire data directory MV to other places to do backup;

4, 1.102 of the entire MySQL data directory using rsync copy to 1.103;

5, the copy at the same time, in 1.101 authorization, so that 1.103 have the permission to pull Binlog (REPLICATION SLAVE, REPLICATION CLIENT);

6, to be copied to complete, modify the 1.103 configuration file in the server_id, pay attention to do not and 1.102 on the same;

7, start the MySQL instance in 1.103, note the data file path in the configuration file and the permissions of the data directory;

8, enter 1.103 MySQL instance, use SHOW SLAVE status check from the library state, you can see seconds_behind_master in descending;

9, Seconds_behind_master changed to 0, indicating that the synchronization is complete, at this time can be used pt-table-checksum check 1.101 and 1.103 of the data consistent, but time-consuming, but also has an impact on the main node, can be developed together with the validation of data consistency;

10, we use the same method, so that 1.104 into 1.103 from the library;

11, and research and development communication, in addition to do data consistency verification, but also need to verify the account permissions, in order to prevent the operation of access error after the move;

12, at this time, we have to do is to 1.103 into 2.101 from the library, the specific practice can refer to scene four;

13, it should be noted that 1.103 of the odd configuration needs and 1.101 consistent;

14, to finish the above steps, can and research and development coordination, 1.101 of the reading and writing business cut to 1.103, 1.102 of the reading business cut to 1.104. Observing business status;

15, if the business is not a problem, prove the migration success.

3.6 Scene Six multi-instance cross-room migration

Next we look at the multi-instance cross-room migration certificate to do. For each machine instance relationship, we can refer to figure six. The purpose of this migration is to do data repair. Create 7938 and 7939 instances on 2.117, replacing instances of previous data exceptions. For business reasons, some libraries are only written in A, and some libraries are only written in B, so there is a case of synchronous filtering.

Figure six multi-instance cross-room migration architecture diagram

The specific practice is as follows:

1, 1.113 for 7936 instances using Innobackupex for data backup, note the need to specify the database, and add slave-info parameters;

2, after the backup completes, copies the compressed file to 2.117;

3, 2.117 create the data directory and the relevant directory related to the configuration file;

4, 2.117 use Innobackupex recovery log;

5, 2.117 use Innobackupex copy data;

6, 2.117 Modify the configuration file, note the following parameters: replicate-ignore-db, innodb_file_per_table = 1, read_only = 1, server_id;

7, 2.117 change the data directory permissions;

8, 1.112 authorization, so that 2.117 have the permission to pull Binlog (REPLICATION SLAVE, REPLICATION CLIENT);

9, 2.117 change Maste to 1.112,log FILE and LOG POS reference xtrabackup_slave_info;

10, 2.117 start SLAVE, view from the library status;

11, 2.117 on the method of establishing 7939 is similar, but the configuration file needs to specify replicate-wild-do-table;

12, and the development of data consistency verification and verification of account permissions, in case of business relocation after access error;

13, complete the above steps, can and research and development coordination, the corresponding business migration to 2.117 of 7938 instances and 7939 instances. Observing business status;

14, if the business is not a problem, prove the migration success.

Four Precautions

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

1, database migration, if the event is involved, remember the main node open Event_scheduler parameters;

2, regardless of the scene of the migration, should always pay attention to the server state, such as disk space, network jitter, in addition, continuous monitoring of the business is also essential;

3, change MASTER to the log FILE and log POS remember not to find fault, if the designation is wrong, the result is inconsistent data;

Full content point this view

[Original] MySQL migration scenario under different scenarios