User information table horizontal table sharding Solution

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131228/225U043W-0.jpg "/>

Below 650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131228/225U05603-1.jpg "/>

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

 
 

  
  
create table t0 like t; 
  
  
create table t1 like t; 
  
  
create table t2 like t; 
 
 

Step 2: Create Three triggers on the master database (M) for insertion, update, and deletion)

 
 

  
  
DELIMITER $$ 
  
  
 
  
  
USE `test`$$ 
  
  
 
  
  
DROP TRIGGER /*!50032 IF EXISTS */ `t_insert`$$ 
  
  
 
  
  
CREATE 
  
  
    /*!50017 DEFINER = 'admin'@'%' */ 
  
  
    TRIGGER `t_insert` AFTER INSERT ON `t`  
  
  
    FOR EACH ROW BEGIN 
  
  
    DECLARE v_result INT; 
  
  
    SET v_result=MOD(new.id,3); 
  
  
    IF v_result = 0 THEN 
  
  
      INSERT INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    ELSEIF v_result = 1 THEN 
  
  
      INSERT INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    ELSE 
  
  
      INSERT INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    END IF; 
  
  
    END; 
  
  
$$ 
  
  
 
  
  
DELIMITER ; 
  
  
 
  
  
 
  
  
/*============================================*/ 
  
  
 
  
  
DELIMITER $$ 
  
  
 
  
  
USE `test`$$ 
  
  
 
  
  
DROP TRIGGER /*!50032 IF EXISTS */ `t_update`$$ 
  
  
 
  
  
CREATE 
  
  
    /*!50017 DEFINER = 'admin'@'%' */ 
  
  
    TRIGGER `t_update` AFTER UPDATE ON `t`  
  
  
    FOR EACH ROW BEGIN 
  
  
    DECLARE v_result INT; 
  
  
    SET v_result=MOD(new.id,3); 
  
  
    IF v_result = 0 THEN 
  
  
      REPLACE INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    ELSEIF v_result = 1 THEN 
  
  
      REPLACE INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    ELSE 
  
  
      REPLACE INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address); 
  
  
    END IF; 
  
  
    END; 
  
  
$$ 
  
  
 
  
  
DELIMITER ; 
  
  
 
  
  
 
  
  
/*============================================*/ 
  
  
 
  
  
DELIMITER $$ 
  
  
 
  
  
USE `test`$$ 
  
  
 
  
  
DROP TRIGGER /*!50032 IF EXISTS */ `t_delete`$$ 
  
  
 
  
  
CREATE 
  
  
    /*!50017 DEFINER = 'admin'@'%' */ 
  
  
    TRIGGER `t_delete` AFTER DELETE ON `t`  
  
  
    FOR EACH ROW BEGIN 
  
  
    DECLARE v_result INT; 
  
  
    SET v_result=MOD(old.id,3); 
  
  
    IF v_result = 0 THEN 
  
  
      DELETE FROM t0 WHERE id = OLD.id; 
  
  
    ELSEIF v_result = 1 THEN 
  
  
      DELETE FROM t1 WHERE id = OLD.id; 
  
  
    ELSE 
  
  
      DELETE FROM t2 WHERE id = OLD.id; 
  
  
    END IF; 
  
  
    END; 
  
  
$$ 
  
  
 
  
  
DELIMITER ; 
 
 

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 a new instance (S2) from the database and export the tables t, t0, t1, and t2 as long as the data is exported, instead of the table structure)

 
 

  
  
/usr/local/mysql/bin/mysqldump -uroot -p123456 --dump-slave=2 -nt --skip-triggers -q --single-transaction test t t0 t1 t2 > t_all.sql 
 
 

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

Http://hcymysql.blog.51cto.com/blog/5223301/889971

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 preceding five steps, check the data growth and check whether the synchronization is normal. If there is no problem, you can distribute the data 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

 
 

  
  
replicate-ignore-table=test.t1 
  
  
replicate-ignore-table=test.t2 
 
 

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.

 
 

  
  
replicate-ignore-table=test.t0  
  
  
replicate-ignore-table=test.t2  
 
 

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.

 
 

  
  
replicate-ignore-table=test.t0   
  
  
replicate-ignore-table=test.t1  
 
 

Ignore the synchronization replication of tables t0 and t1.

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

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, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1179880