MariaDB社区

标题: 用户信息表水平拆表方案 [打印本页]
作者: squall    时间: 2013-4-18 16:25
标题: 用户信息表水平拆表方案
作者：贺春旸








方法就是利用用户ID进行取模，MOD(new.id,3)，3就代表拆分到3张表里。


以下是步骤：

第一步、在主库上(M)建立拆分的三张表，t0,t1,t2
  1.     create table t0 like t;
  2.     create table t1 like t;
  3.     create table t2 like t;
复制代码
第二步、在主库上(M)建立三个触发器（插入、更新、删除）
  1.     DELIMITER $
  2.      
  3.     USE `test`$
  4.      
  5.     DROP TRIGGER /*!50032 IF EXISTS */ `t_insert`$
  6.      
  7.     CREATE
  8.         /*!50017 DEFINER = 'admin'@'%' */
  9.         TRIGGER `t_insert` AFTER INSERT ON `t`  
  10.         FOR EACH ROW BEGIN
  11.         DECLARE v_result INT;
  12.         SET v_result=MOD(new.id,3);
  13.         IF v_result = 0 THEN
  14.           INSERT INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  15.         ELSEIF v_result = 1 THEN
  16.           INSERT INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  17.         ELSE
  18.           INSERT INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  19.         END IF;
  20.         END;
  21.     $
  22.      
  23.     DELIMITER ;
  24.      
  25.      
  26.     /*============================================*/
  27.      
  28.     DELIMITER $
  29.      
  30.     USE `test`$
  31.      
  32.     DROP TRIGGER /*!50032 IF EXISTS */ `t_update`$
  33.      
  34.     CREATE
  35.         /*!50017 DEFINER = 'admin'@'%' */
  36.         TRIGGER `t_update` AFTER UPDATE ON `t`  
  37.         FOR EACH ROW BEGIN
  38.         DECLARE v_result INT;
  39.         SET v_result=MOD(new.id,3);
  40.         IF v_result = 0 THEN
  41.           REPLACE INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  42.         ELSEIF v_result = 1 THEN
  43.           REPLACE INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  44.         ELSE
  45.           REPLACE INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  46.         END IF;
  47.         END;
  48.     $
  49.      
  50.     DELIMITER ;
  51.      
  52.      
  53.     /*============================================*/
  54.      
  55.     DELIMITER $
  56.      
  57.     USE `test`$
  58.      
  59.     DROP TRIGGER /*!50032 IF EXISTS */ `t_delete`$
  60.      
  61.     CREATE
  62.         /*!50017 DEFINER = 'admin'@'%' */
  63.         TRIGGER `t_delete` AFTER DELETE ON `t`  
  64.         FOR EACH ROW BEGIN
  65.         DECLARE v_result INT;
  66.         SET v_result=MOD(old.id,3);
  67.         IF v_result = 0 THEN
  68.           DELETE FROM t0 WHERE id = OLD.id;
  69.         ELSEIF v_result = 1 THEN
  70.           DELETE FROM t1 WHERE id = OLD.id;
  71.         ELSE
  72.           DELETE FROM t2 WHERE id = OLD.id;
  73.         END IF;
  74.         END;
  75.     $
  76.      
  77.     DELIMITER ;
复制代码
这样t表数据变更时，就会按照取模的结果更新到t0,t1,t2这三张表里


第三步、确保同步复制延时为0时，在从库上(S)stop slave关闭同步复制，并show slave status\G记录到文本里，以便后面时用到。


第四步、在从库上新建一个实例(S2)，并导出t,t0,t1,t2四张表（导出时只要数据，不要表结构）
  1. /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里增加
  1.     replicate-ignore-table=test.t1
  2.     replicate-ignore-table=test.t2
复制代码
忽略t1,t2表的同步复制。


把t1表dump出来并导入到M_new2机器上，然后在M_new2机器上，在my.cnf里增加
  1.     replicate-ignore-table=test.t0  
  2.     replicate-ignore-table=test.t2  
复制代码
忽略t0,t2表的同步复制。


把t2表dump出来并导入到M_new3机器上，然后在M_new3机器上，在my.cnf里增加
  1.     replicate-ignore-table=test.t0   
  2.     replicate-ignore-table=test.t1  
复制代码
第七步，到目前为止，基本上我们已经做完了，剩下的就要开发改他们的代码，告诉他们拆分的规则，按用户ID取模，拆分几个表，拆分到哪台机器上，IP是什么。

等凌晨0:00点，开发让运维重启前端应用，这样就顺利实现了用户信息表的拆分。

过程可能有些复杂，有兴趣的朋友，可以按照我上面的操作，做下测试。





作者: kider    时间: 2013-4-18 16:51
高端又详细！
作者: 末猪    时间: 2013-4-19 06:10




欢迎光临 MariaDB社区 (http://123.56.88.72/) Powered by Discuz! X3.2