Common MySQL Data splitting methods and mysql DATA Splitting Methods

Common MySQL Data splitting methods and mysql DATA Splitting Methods

In a production environment, DBA often needs to split databases because of business growth or business splitting. So what are our common methods of database splitting?

Here are several solutions:

1. Use mysqldump to reverse the table logic and then source to other places.
2. Use xtrabackup to back up the table or database logic, and then find an instance.
3. Use the table space transfer (Transport) provided by MySQL [This requires MySQL 5.6.6 or later]

I: Let's take a look at the features of MySQL's Transport tablespace.

For example, if we want to move tab_test1 from db_A to db_ B, we need to do the following actions:

Step 1: Use db_A; show create table tab_test1; (first, obtain the expected 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, delete the idb file from the target database)
Step 4:Use db_A; flush table test122 for export; (write the memory data of the related table to the database)
Step 5: Copy the tab_test1.ibd file to the db_ B/directory. (copy the ibd file at the operating system layer)
Step 6: Use db_A; unlock tables; (unlock table on the source database)
Step 7: Use db_ B; alter table tab_test1 import tablespace; (tablespace import is performed in the target database)

After performing the above steps, we have completed the migration of some tables between databases.

II: Let's take a look at the export of some tables in xtrabackup to achieve migration purpose.

Step 1: We need to use xtrabackup to back up some tables.

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: Application logs for recovery

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 and configure the replication relationship. Only some tables are copied.

My. cnf file configuration: Replicate_Wild_Do_Table/Replicate_Wild_Ignore_Table
 
After performing the above steps, we have completed the table migration and created a new instance to achieve the goal of database sharding.
 

III: Let's take a look at the position of mysqldump.

Well, that's not much to worry about. What mysqldump does is to export some logic. We can see the data. Note that the -- single-transcation Option
 
In terms of the above methods, we need to consider the server resources, memory capacity, and so on in our actual work. We need to use appropriate methods to split the database according to the actual situation.