User information table horizontal table sharding Solution

The following method is to use the user id for MOD, MOD (new. id, 3), and 3 represent being split into three tables. Step 1: create three split tables (t0, t1, t2createtablet0liket; creat) on the master database (M ).

The following method is to use the user id for MOD, MOD (new. id, 3), and 3 represent being split into three tables. Step 1: create three split tables (t0, t1, t2createtablet0liket; creat) on the master database (M ).

Below

The method is to use the user id for MOD. MOD (new. id, 3) and 3 represent splitting to three tables.

Follow these steps:

Step 1: create three split tables on the master database (M), t0, t1, t2

Step 2: Create Three triggers (insert, update, and delete) on the master database (M)

In this way, the t table data will be updated to the tables t0, t1, and t2 Based on the modulo result.

Step 3: When the synchronization replication latency is 0, stop the synchronization replication on the slave database (S) stop slave and show the slave status \ G records to the text for later use.

Step 4: Create an instance (S2) on the slave database and export the four tables t, t0, t1, and t2 (only data is needed during the export and table structure is not required)

-- Dump-slave is a feature of MySQL5.5. For details, refer to this article.

If you are 5.1, we need to use the show slave status \ G recorded in step 3 to find the point of change master to, so as to ensure the incremental data after the split.

Step 5: create tables t, t0, t1, and t2 on the S2 instance of the slave database. The Three triggers are also created, during the import, data is distributed to tables t0, t1, and t2 through the trigger modulo operation, and the master database M is synchronized and copied.

Step 6: After completing the above five steps, check the data growth on the Hong Kong server and check whether the synchronization is normal. If there is no problem, data can be distributed to the new three servers.

On the S2 instance, dump the t0 table and import it to the M_new1 machine. Then, on the M_new1 machine, add

Ignore synchronous replication of tables t1 and t2.

Dump table t1 and import it to machine M_new2. Then add it to machine my. cnf on machine M_new2.

Ignore the synchronization replication of tables t0 and t2.

Dump table t2 and import it to machine M_new3. Then add it to machine my. cnf on machine M_new3.

Ignore the synchronization replication of tables t0 and t1.

Step 7: The U.S. server. So far, we have basically finished it. The rest of the work is to develop and modify their code, and tell them the sharding rules, modulo by user ID, split several tables, to which machine, and IP address.

At a.m., the development required O & M to restart the front-end application, so that the user information table could be split smoothly.

The process may be complicated. If you are interested, you can perform the test according to the above operations.

This article is from the "hechun's technical column" blog. Be sure to keep this source