使用mysql federated引擎构建MySQL分布式数据库访问层

最新推荐文章于 2023-07-05 17:26:18 发布

freedom5112

最新推荐文章于 2023-07-05 17:26:18 发布

阅读量2.8k

点赞数

分类专栏： MYSQL 文章标签： mysql 分布式

MYSQL 专栏收录该内容

121 篇文章 0 订阅

订阅专栏

转自：http://zhangxugg-163-com.iteye.com/blog/1666673

使用mysql federated 引擎构建 MySQL 分布式数据库访问层

前言：随着应用复杂度的增加，数据库不断细化切分，导致应用程序中数据库应用就得复杂，凌乱。绝大部分程序人员可能都遇到这种情况，应用程序中需要连接多台数据库服务器，进行相应的操作。随着时间积累，太多的数据库服务器的连接逻辑出现在程序之中，这给程序的维护扩展，数据库维护工作带来极大的工作量。

于是一些分布式数据库代理层应运而生，如常见 MySQL 代理层 :

mysql proxy : 主要实现读写分离和负载均衡

MySQL Amoeba : 由陈思儒主导开发　功能比较完善，用深入应用的价值，参考网站http://docs.hexnova.com/amoeba/

HiveDB : HiveDB是一个用来横向切分 mysql 数据库的开源框架，构建一个高性能和可扩展的基于 mysql 的系统，但目前仅支持 Java 客户端。 http://www.hivedb.org/

我认为mysql proxy, MySQL Amoeba 都是极好的实用价值，应该多深入了解之。

而本文所描述的 federated属于 MySQL的一种特殊引擎，利用它可将本地数据表映射至远程 MySQL 数据表，从而就可以解决应用程序中繁多的跨机器连接数据库问题，拓扑图如下：

如此就可以构造出一个统一的数据访问入口，就大大提高了整个数据库系统的可维护性。

Federated引擎是基于表级别的，只能将本地数据表定义为 Federated 引擎并映射至远程实体表，无法实现基于库级别的整体映射。

在本文中，我们将启用Federated 引擎的数据库访问入口服务器称为本地数据库，而将本地数据表对应的远程数据表，称之为实体表。

本地数据库需要启用Federated 引擎支持，而远程数据表无须 Federated 引擎支持。 Federated 引擎表使用标准的MySQL 客户端协议与远程数据库建立 TCP 连接。

创建Federated 表的过程：

1. 　以root 登录远程 MySQL ，上创建合适的访问账号
grant all on DB1.* to 'federated'@'%' identified by 'federated';

flush privileges;

2. 在远程MySQL 找到对应实体表的创建命令（如果是新表，请先建立好数据表，再执行此命令）
假设在远程mysql 上有库名 DB1, 表名 tag, 执行以下命令找到远程表的结构：
show create table DB1.tag

输出：
CREATE TABLE `tag` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) NOT NULL,

`frequency` int(10) unsigned NOT NULL DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

3. 假设我们要将远程的DB1.tag 映射至本地 DB.TableA 表上。那么我们应该保持本地虚拟表与远程实体表结构一致（结构可以有所差异，但会造成使用，管理上的麻烦）。根据远程实体表的创建命令，创建本地虚拟表 ( 结构部分完全一样，创建表选项有所差异 ) ：

登录本地Mysql 服务器，创建相应的数据库及表：
create database DB;

use DB;
CREATE TABLE `TableA` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(128) NOT NULL,

`frequency` int(10) unsigned NOT NULL DEFAULT '1',

PRIMARY KEY (`id`)

) ENGINE=federated connection="mysql://federated:federated@127.0.0.1:3306/DB1/tag";

这时，即建立好了federated 虚拟表，实际上本地 MySQL 只创建了表定义文件 , 而没有数据文件。我们对本地虚拟表的数据修改，均会发送到远程机器上执行。

本地虚拟表名与远程表名，可不相同。

经过测试，这个引擎的一些额外特点：
1.　本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。
2. 本虚拟表表与远程实体表之间的网络连接断开后，当对虚拟表发起查询时，它会尝试重新连接远程实体表，所以我们不用担心网络连接断开造成的永久中断问题。
3. 如果无时间未对本地虚拟表作任何操作，虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开，当对虚拟表发起查询时，连接又会重新建立。

一些注意事项：
1.　对本地虚拟表的结构修改，并不会修改远程表的结构
2.　 truncate 命令，会清除远程表数据
3. drop命令只会删除虚拟表，并不会删除远程表

4. 不支持 alter table 命令

目前使用federated 最大的缺点：
1. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题。但是按主键或索引列进行条件查询，则很快，如以下查询就非常慢（假设 id 为主索引）
select id from db.tablea where id >100 limit 10 ;

而以下查询就很快：
select id from db.tablea where id >100 and id<150

2. 如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。

3. 类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为

federated引擎会将所有满足条件的记录读取到本，再进行 limit 处理。

原表记录：

mysql> select count(1) from jifei_shbd_1 force index(ind_calling);
+----------+
| count(1) |
+----------+
| 1683322 |
+----------+

fedxx表情况：select count(1) from jifei_shbd_1 a where a.calling like '010%' ;
+----------+
| count(1) |
+----------+
| 43371 |
+----------+
1 row in set (0.63 sec)

原始表情况： select count(1) from jifei_shbd_1 a where a.calling like '010%' ;
+----------+
| count(1) |
+----------+
| 43371 |
+----------+
1 row in set (0.10 sec)

效率相差5倍，不过没有想象中的那么慢。

这几个问题已经严重影响了federated 在实际环境中的应用。

安装：

安装federated存储引擎

由于编译时没有选择federated，所以打算通过INSTALL PLUGIN的方式安装，正常情况下，federated是支持动态安装的：

=== Federated Storage Engine ===

Plugin Name: federated

Description: Connects to tables on remote MySQL servers

Supports build: static and dynamic

Configurations: max, max-no-ndb

可是执行以下命令时报错：

SQL>install plugin federated soname 'ha_federated.so';

ERROR 1126 (HY000): Can't open shared library '/usr/local/mysql/lib/mysql/plugin/ha_federated.so' (errno: 2 undefined symbol: dynstr_append_mem)

搜了一下，发现是个老问题，竟然到现在都没解决，可见MySQL团队的效率和管理的混乱。http://bugs.mysql.com/bug.php?id=40942

没有办法了，只有重新编译MySQL源码了, 加上--with-plugins=federated。从5.1.26开始，默认MySQL不启用federated存储引擎，所以需要在my.cnf中加入federated选项或是在命令行用--federated选项启动mysqld。

原理：

当你创建一个FEDERATED表的时候，服务器在数据库目录创建一个表定义文件。文件由表的名字开始，并有一个.frm扩展名。无其它表被创建，因为实际的数据在一个远程数据库上。这不同于为本地表工作的存储引擎的方式。

对于本地的数据录表，数据文件是本地的。例如，如果你创建一个名为user的MyISAM表，MyISAM处理器创建一个名为users.MYD的数据文件。对本地表读，插入，删除和更新在本地数据文件里的数据的处理器和记录被以对处理器的特定格式存储。为了读记录，处理器必须把数据分解进列。为了写记录，列值必须被转换到被处理器使用的行格式并且被写进本地的数据文件。

使用MySQL FEDERATED存储引擎，没有对表的本地数据文件（比如，没有.MYD文件）。取而代之地，一个远程数据库存储那些正常地应该在表中的数据。这使得MySQL客户端API来读，删除，更新和插入数据的使用成为必要。数据取回被通过SELECT * FROM tbl_name SQL语句来初始化。要读这个结果，通过使用mysql_fetch_row() C API函数，行被一次取一个，然后从SELECT结果包中的列转换成FEDERATED处理器期望的格式。

基本流程如下：

1. SQL调用被本地发布

2. MySQL处理器API （数据以处理器格式）

3. MySQL客户端API （数据被转换成SQL调用）

4. 远程数据库-> MySQL客户端API

5. 转换结果包（如果有的话）到处理器格式

6. 处理器 API -> 结果行或受行影响的对本地的计数

CREATE TABLE federated_table (

    id     int(20) NOT NULL auto_increment,

    name   varchar(32) NOT NULL default '',

    other  int(20) NOT NULL default '0',

    PRIMARY KEY  (id),

    KEY name (name),

    KEY other_key (other)

)

ENGINE=FEDERATED

DEFAULT CHARSET=latin1

CONNECTION='mysql://root@remote_host:9306/federated/test_table';

（注意: CONNECTION 替代用在先前版本的MySQL里的COMMENT）。

除了ENGINE表选项应该是FEDERATED，并且CONNECTION表选项是给FEDERATED指明如何连接到远程服务器上的连接字符串之外，这个表的结构必须完全与远程表的结构相同。

FEDERATED引擎仅创建在已联盟数据库中的test_table.frm文件。

远程主机信息指明本地服务器要连接到的远程服务器，数据库和表信息指明哪一个远程表要被作为数据文件来用。在这个例子中。远程服务器被指定来作为远程主机在9306端口上运行，所以你要启动服务器，让它监听9306端口。

在CONNECTION选项中的连接字符串的一般形式如下：

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

只有mysql在这一点被支持为scheme，密码和端口号时可选的。

这里有一些连接字符串的例子：

CONNECTION='mysql://username:password@hostname:port/database/tablename'

CONNECTION='mysql://username@hostname/database/tablename'

CONNECTION='mysql://username:password@hostname/database/tablename'

为指定连接字符串使用CONNECTION是非可选，并且在将来可能会改变。当你使用FEDERATED表的时候，要记得这个，因为这意味着当将来发生那种改变之时，可能被要求。

因为任何被用的密码作为纯文本被存在连接字符串中，它可以被任何使对FEDERATED表使用SHOW CREATE TABLE或SHOW TABLE STATUS的用户，或者在INFORMATION_SCHEMA数据库中查询TABLES表的用户看见。

对于FEDERATED存储引擎，在http://forums.mysql.com/list.php?105上有一个专门的论坛。

另外：

为了避免没创建一张表都要指定一次远程数据库的账号、密码、ip等你可以创建一个或多个server来简化具体操作如下:

the CREATE SERVER statement to define the server connection parameters, just as you would with the CONNECTION string.

The format of the CREATE SERVER statement is:

CREATE SERVERserver_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

The server_name is used in the connection string when creating a newFEDERATED table.

For example, to create a server connection identical to the CONNECTION string:

CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

You would use the following statement:

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');

To create a FEDERATED table that uses this connection, you still use the CONNECTION keyword, but specify the name you used in the CREATE SERVER statement.

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';

实例2：

CREATE SERVER fedlink2
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'test',PASSWORD 'test', HOST '192.31.196.74', PORT 3306,DATABASE 'li_test');

CREATE TABLE `testfed` (
`id` int(11) DEFAULT NULL,
`name` varchar(15) DEFAULT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='fedlink2/testfed';

注释：

1，一般表结构本地和远端有差异（包括字段属性)那么在执行一些查询的时候可能报错：

ERROR 1030 (HY000): Got error 1430 from storage engine

但是如果原数据库表没有索引，而fedxx表又索引，那么在fedxx端explain 会显示使用到所有

但原始数据库并没有用到该索引

2，fedaxx引擎不支持create index ，alter table等操作，所以如果原始表新增index为了不影响

fedaxx表的查询效率需要重建fedaxx对应的表

freedom5112

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用mysql federated引擎构建MySQL分布式数据库访问层

转自：http://zhangxugg-163-com.iteye.com/blog/1666673使用mysql federated 引擎构建 MySQL 分布式数据库访问层前言：随着应用复杂度的增加，数据库不断细化切分，导致应用程序中数据库应用就得复杂，凌乱。绝大部分程序人员可能都遇到这种情况，应用程序中需要连接多台数据库服务器，进行相应的操作。随着时间积
复制链接

扫一扫