使用者資訊表水平拆表方案

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

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

方法就是利用使用者ID進行模數，MOD(new.id,3)，3就代表拆分到3張表裡。

以下是步驟：

第一步、在主庫上(M)建立拆分的三張表，t0,t1,t2

 

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

第二步、在主庫上(M)建立三個觸發器插入、更新、刪除）

 

  
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 ; 
 

這樣t表資料變更時，就會按照模數的結果更新到t0,t1,t2這三張表裡

第三步、確保同步複製延時為0時，在從庫上(S)stop slave關閉同步複製，並show slave status\G記錄到文本裡，以便後面時用到。

第四步、在從庫上建立一個執行個體(S2)，並匯出t,t0,t1,t2四張表匯出時只要資料，不要表結構）

 

  
/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是MySQL5.5的一個特性，具體你可參考我這一篇文章

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

如果你是5.1的，就要用到第三步我們記錄的show slave status\G到文本，找到change master to的那個點，這樣就保證了拆分後的增量資料。

第五步，在從庫上S2執行個體上，建立表t,t0,t1,t2，也同樣建立那三個觸發器，這樣做的目的是，當我們匯入的時候，就會通過觸發器模數，把資料分散到t0,t1,t2這三張表裡，然後開啟同步複製主庫M。

第六步，上面五步做完以後，請檢查下資料的增長情況，並觀察同步是否正常，都沒問題的話，就可以把資料分散到新的3台伺服器裡。

在S2執行個體上，把t0表dump出來並匯入到M_new1機器上，然後在M_new1機器上，在my.cnf裡增加

 

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

忽略t1,t2表的同步複製。

把t1表dump出來並匯入到M_new2機器上，然後在M_new2機器上，在my.cnf裡增加

 

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

忽略t0,t2表的同步複製。

把t2表dump出來並匯入到M_new3機器上，然後在M_new3機器上，在my.cnf裡增加

 

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

忽略t0,t1表的同步複製。

第七步，到目前為止，基本上我們已經做完了，剩下的就要開發改他們的代碼，告訴他們拆分的規則，按使用者ID模數，拆分幾個表，拆分到哪台機器上，IP是什麼。

等淩晨0:00點，開發讓營運重啟前端應用，這樣就順利實現了使用者資訊表的拆分。

過程可能有些複雜，有興趣的朋友，可以按照我上面的操作，做下測試。

 

本文出自 “賀春暘的技術專欄” 部落格，請務必保留此出處http://hcymysql.blog.51cto.com/5223301/1179880