关于MYSQL的远程数据库表间同步的构思与测试
前沿：由于业务需求，客户需要同步一个数据库部分表到另外一台服务器上。使用mysql日志复制方案行不通。只能从mysql触发器+mysql的 federated引擎来解决。
第一， 打开Mysql的federated引擎.
在my.cnf文件内的[mysqld]加入federated，重启mysql. 查询
mysql> show engines ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
操作成功。
第二， 建库表的图示
数据源：192.168.1.1
Federated:192.168.1.1
远程：192.168.1.2
第三， 除了源码，创建一个触发器维护的federated引擎库。
CREATE TABLE `db_admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_id` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '角色组',
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '密码',
`realname` varchar(50) NOT NULL COMMENT '真实姓名',
`notepad` text NOT NULL COMMENT '备忘录',
`sex` char(5) NOT NULL DEFAULT '男' COMMENT '性别',
`telephone` varchar(50) NOT NULL COMMENT '电话',
`mobile_telephone` varchar(50) NOT NULL COMMENT '手机',
`fax` varchar(50) NOT NULL COMMENT 'FAX',
`web_url` varchar(100) NOT NULL COMMENT '网址',
`email` varchar(50) NOT NULL COMMENT '电子邮件',
`qq` varchar(50) NOT NULL COMMENT 'QQ',
`address` varchar(100) NOT NULL DEFAULT '' COMMENT '地址',
`login_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '登录次数',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`last_login_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '最后登录时间',
`country_tag_id` int(11) NOT NULL COMMENT '国家标签',
`disable_login` tinyint(1) NOT NULL COMMENT '是否可以登录',
`mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '媒体id',
PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:root@192.168.1.2/remote_db/db_admin'
说明：mysql://root:root@192.168.1.2/remote_db/admin 格式是：用户名：密码@IP/数据库/表名
第四， 在原表192.168.1.1创建触发器。以admin表为示例。
1,插入
DELIMITER $$
USE `dbforex`$$
DROP TRIGGER /*!50032 IF EXISTS */ `t_db_admin_insert`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `t_db_admin_insert` AFTER INSERT ON `db_admin`
FOR EACH ROW BEGIN
INSERT INTO dbforex_remote.`db_admin` (`id`, `role_id`, `username`, `password`, `realname`, `notepad`, `sex`, `telephone`, `mobile_telephone`, `fax`, `web_url`, `email`, `qq`, `address`, `login_count`, `create_time`, `update_time`, `last_login_time`, `country_tag_id`, `disable_login`, `mid`) VALUES (NEW.id, NEW.role_id, NEW.username, NEW.password, NEW.realname, NEW.notepad, NEW.sex, NEW.telephone, NEW.mobile_telephone, NEW.fax, NEW.web_url, NEW.email, NEW.qq, NEW.address, NEW.login_count, NEW.create_time, NEW.update_time, NEW.last_login_time, NEW.country_tag_id, NEW.disable_login, NEW.mid); END; $$
DELIMITER ;
2，修改
DELIMITER $$
USE `dbforex`$$
DROP TRIGGER /*!50032 IF EXISTS */ `t_update_db_admin`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `t_update_db_admin` AFTER UPDATE ON `db_admin`
FOR EACH ROW BEGIN
UPDATE `dbforex_remote`.db_admin SET `id`=new.id,`role_id`=new.role_id, `username`=new.username,`password`=new.password,`realname`=new.realname, `notepad`=new.notepad,`sex`=new.sex,`telephone`=new.telephone, `mobile_telephone`=new.mobile_telephone, `fax`=new.fax, `web_url`=new.web_url, `email`=new.email, `qq`=new.qq, `address`=new.address, `login_count`=new.login_count, `create_time`=new.create_time, `update_time`=new.update_time,`last_login_time`=new.last_login_time, `country_tag_id`=new.country_tag_id, `disable_login`=new.disable_login, `mid`=new.mid WHERE id=OLD.id;
END;
$$
DELIMITER ;
3，删除
DELIMITER $$
USE `dbforex`$$
DROP TRIGGER /*!50032 IF EXISTS */ `tr_delete_db_admin`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `tr_delete_db_admin` AFTER DELETE ON `db_admin`
FOR EACH ROW BEGIN
-- 检查当前 环境，避免递归.
IF @disable_trigger IS NULL THEN
-- 设置禁用触发器标志.
SET @disable_trigger = 1;
-- 删除目标表
DELETE FROM dbforex_remote.db_admin WHERE id=old.id;
-- 恢复禁用触发器标志.
SET @disable_trigger = NULL;
END IF;
END;
$$
以上的触发器完成，对需要操作的表都是跟以上操作。(但是维护表字段，暂时只能不能用此方案)。
第五， 在远程服务器创建数据库，也创建表db_admin，表字段跟源服务器一致。
第六， 测试
在源表192.168.1.1内插入一条记录：
INSERT INTO `dbforex`.`db_admin` (`id`, `role_id`, `username`, `password`, `realname`, `notepad`, `sex`, `telephone`, `mobile_telephone`, `fax`, `web_url`, `email`, `qq`, `address`, `login_count`, `create_time`, `update_time`, `last_login_time`, `country_tag_id`, `disable_login`, `mid`) VALUES (NULL, '19', 't_test05_q19', '', 'wx1', '', '男', '', '', '', '', '', '', '', '0', '1466488294', '1466488294', '0', '8', '0', '134');
查看192.168.1.2
SELECT * FROM `dbforex`.`db_admin` ; //结果有记录，操作成功。结果略。
同样操作修改和删除，都正常。还有未考虑批量数据的维护。
