Mysql多主一从方案

需求来源是开发想把多个库放置到一个中心库中，实现统计分析的需求。因此就有了多主一从的构想，而mysql不提供这样的原生方案（最新的mysql版本支持，但是新版本谁敢用呢），只能通过几种变种来实现，以下是集中方案的介绍：

1） mysql多实例+myISAM方案

a) 设置主库，启用Binlog。

b) 设置从库，在从库使用mysqld_multi启动多库实例，每个实例对应一个主库，并分别设置好主从复制。

限制：1、从库只安装MyISAM 引擎，这样不管主库是用什么引擎，从库都使用MyISAM。

2、从库的几个实例的数据目录使用同一个，这样每个实例都可以访问其他实例的表。

3、使用 Crontab在从库每分钟执行一次Flush tables;实例之间可以看到彼此的数据变化。

在现网搭建了这样的环境，最后证明是失败的，也来源于我们的现实环境情况。在mysql中，索引长度是不能超过1000个字符，在创建索引时，innodb作为warning来处理，myISAM就直接作为error来处理。而我们很多库表，都是超过了这个限制，因此在搭建从库的时候，根本不能成功。

另外我们在主库上如果新建的表是innodb的，后面在从库上还要做转库处理，或者在主库建库的时候，需要注意create table的写法，不要指定引擎。

因为诸多限制，我们放弃了这个想法。

2） Mysqldump导入方案

这是一个中间方案，我们定时把冷备的数据导出，生成sql文件，然后传到从库上，在从库上用mysqldump导入处理，这个方案只能做成一天一次的方案，否则系统处理的代价非常大。但是在成百上千个库的方案中，会有该方案的应用场景。

方案缺点：实时性不高

方案优点：对主库无任何压力

3） mysqlbinlog同步方案

其实这个地方是利用mysqlbinlog的远程复制功能，也是模拟了mysql slave IO线程功能。形如：

mysqlbinglog -read-from-remote-server -u repl -p -h target_node –start-datetime=’2010-09-01 00:00:00′–stop-datetime=’2010-09-01 23:59:59′

这个语句可以获取这段时间内所有的更新sql，然后模拟sql线程，一次把sql语句在从库上执行即可。

方案的最大缺点：实时性不高，并且需要自己去实现SQL处理部分。

方案优点：对主库无任何侵入

4） mysql同步方案

maakit-sync和percona-tookit方案，这是开源mysql里面非常著名的mysql同步工具了，可以利用这个工具做多库之间的同步，这个方案类似于mysqlbinlog的方案，对主库有一定的压力和实时性不高的。

方案缺点：实时性不高。

5） mysql dblink方案---federated引擎方案

这个方案类似与oracle的dblink方案。在mysql中，可以实现表级的关联，在从库系统中创建federated的表指向主库，从库中指记录表结构，不记录真实的数据，所有的数据存放在远端，另外主从的表结构一定要一致。创建方法：

a）在主库上创建一个表

CREATE TABLE `federated_user` (

`ID` varchar(36) NOT NULL COMMENT '预约码(员工、小组、部门编号)',

`USER_NAME` varchar(128) NOT NULL,

`PASS_WORD` varchar(256) NOT NULL COMMENT '员工密码(MD5)',

`USER_TYPE` varchar(36) NOT NULL COMMENT '人员类型编号',

`FULL_NAME` varchar(50) DEFAULT NULL,

`STATUS` int(11) NOT NULL COMMENT '0(默认)，不能登录\n1，允许登录',

`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`ID`),

UNIQUE KEY `USER_NAME_UNIQUE` (`USER_NAME`)

) ENGINE=innodb;

b）主库对从库进行授权(可以再库级)

grant all on *.* to 'federated'@'IP' identified by 'federated';

c）在从库上创建federated表

CREATE TABLE `federated_user` (

`ID` varchar(36) NOT NULL COMMENT '预约码(员工、小组、部门编号)',

`USER_NAME` varchar(128) NOT NULL,

`PASS_WORD` varchar(256) NOT NULL COMMENT '员工密码(MD5)',

`USER_TYPE` varchar(36) NOT NULL COMMENT '人员类型编号',

`FULL_NAME` varchar(50) DEFAULT NULL,

`STATUS` int(11) NOT NULL COMMENT '0(默认)，不能登录\n1，允许登录',

`CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`ID`),

UNIQUE KEY `USER_NAME_UNIQUE` (`USER_NAME`)

) ENGINE=federated DEFAULT CHARSET=utf8 COMMENT='员工' connection="mysql://federated:federated@127.0.0.1:3308/crmdb/federated_user";

方案缺点：

1）主从表结构必须一致，但真实情况下，主库的表结构有时会变化

2）相当于查询代理，所有的访问压力都传递给主库

方案优点：在小数据量，小压力的库情况，该方案的部署简单

6） 中间件方案----- Tungsten Replicator

该产品以前是一个商业产品，后来做了开源。从周边资料来看，percona和amazon aws平台都提供了资料链接，该方案的稳定性是有的，但是这个环境搭建以后需要对主库增加一些配置处理。

具体的配置处理参数如下：

default-storage-engine 必须是innodb

innodb_buffer_pool_size 最低是512M，我们很多库都达不到这个条件

max_allowed_packet 最低48M，保证批量获取数据的时候，能够对大包量进行处理。

innodb_flush_log_at_trx_commit 设置成2，事务更新的时候有限更新日志，只更新缓存，不刷磁盘

sync_binlog 1 必须设置成1，保证每次事务提交的时候，能够刷新binlog到磁盘

7） Mysql multi master replication方案（补丁方案）

该方案来源于淘宝的一个补丁，实现非常巧妙，给每个主数据库的binlog做了标签，标识来源哪个主库，这样在从机上执行的时候，就能够对相应的库进行操作。具体的见：http://www.cnblogs.com/likyzh/archive/2012/11/13/2768775.html

最终我们采用了该方案，当前该方案运行良好。