用户信息表水平拆表方案

squall · 发表于 2013-4-18 16:25:02

作者：贺春旸

方法就是利用用户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里增加