MySQL Common Data-splitting method _mysql

In a production environment, DBAs often need to split the library operation because of business growth or business splitting. So what are our common methods of dismantling?

I offer several solutions here:

1. Use mysqldump to pour out the logic of the table and then source it to another place
2. Use Xtrabackup to back up the table, or library logic, and then recovery an instance
3. Use MySQL with the table space Transfer (transport) [this requires MySQL 5.6.6 Version Support]

I: Let's take a look at the features of MySQL's transport table space.

For example, we want to move tab_test1 from Db_a to Db_b, then we need to do a series of actions:

Step 1: Use db_a; Show CREATE Table Tab_test1 (first, get the desired table structure)
Step 2: Use Db_b; CREATE TABLE Tab_test1; Alter tale TAB_TEST1 discard tablespace; (Discard tablespace is to delete the IBD file, leaving only the. frm file) (second, remove the IDB file to the target library)
Step 4: use db_a; Flush table test122 for export; (write memory data from related tables to database)
Step 5: Copy tab_test1.ibd file to db_b/directory; (operating system layer performs copy IBD file operation)
Step 6: Use db_a; Unlock tables; (Unlock table on source library)
Step 7: Use Db_b; ALTER TABLE TAB_TEST1 import tablespace (destination library performs table space import)

After performing the above steps, we are done with migrating some tables between libraries.

II: Take a look at the Xtrabackup Export Section table for migration purposes

Step 1: We need to use the Xtrabackup backup part of the table

Nohup perl/home/ddb/tmp/xtrabackup/bin/innobackupex-1.5.1--ibbackup=/home/tmp/xtrabackup/bin/xtrabackup_55-- User=root--password=123--defaults-file=/home/my.cnf--slave-info--tables-file=/mnt/back_tables.txt/mnt/backup_1

Step 2: Apply log, restore operation

Nohup perl/home/tmp/percona-xtrabackup-2.1.9-linux-x86_64/bin/innobackupex-1.5.1--ibbackup=/home/tmp/ percona-xtrabackup-2.1.9-linux-x86_64/bin/xtrabackup_55--export--apply-log--defaults-file=/home/node-1-1/ backup-my.cnf/home/node-1-1/>/tmp/r.log 2>&1 &

Step 3: Restart the instance, configure the replication relationship, and copy only some of the tables

MY.CNF file configuration: replicate_wild_do_table/replicate_wild_ignore_table

After performing the above steps, we have completed the migration of the table, and we have a new example to achieve the purpose of the sub-Library.
 

III: Finally, let's take a look at Mysqldump's posture.

    amount, this is not much long-winded, mysqldump do is some of the logic of the export, poured out the data we can be seen with human flesh.     this need to pay attention to is the  --single-transcation option
 
regarding the above methods, we need to consider the server's resources, memory capacity, etc. in the practical work. You need to combine the actual situation to use the appropriate method for the library operation.