使用mysql federated引擎构建MySQL分布式数据库访问层:
[url]http://zhangxugg-163-com.iteye.com/blog/1666673FEDERATED[/url]
Storage Engine Notes and Tips:[url]http://dev.mysql.com/doc/refman/5.7/en/federated-usagenotes.html[/url]
Federated引擎是基于表级别的,只能将本地数据表定义为 Federated 引擎并映射至远程实体表,无法实现基于库级别的整体映射。
进入MYSQL命令行,查看FEDERATED引擎是否开启,默认是不开启
>show engines;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED | YES | Federated MySQL 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 |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set
如果没有开启,配置my.cnf
重启
在远程机192.168.32.128上创建数据库log,创建表test,如下:
创建用户,授予权限
在本地创建相应的表:
插入数据:
INSERT INTO `log`.`test` (`id`, `name`) VALUES ('1', 'donald');
INSERT INTO `log`.`test` (`id`, `name`) VALUES ('2', 'jamel');
查询数据:
mysql> use test;
Database changed
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | donald |
| 2 | jamel |
+----+--------+
[url]http://zhangxugg-163-com.iteye.com/blog/1666673FEDERATED[/url]
Storage Engine Notes and Tips:[url]http://dev.mysql.com/doc/refman/5.7/en/federated-usagenotes.html[/url]
Federated引擎是基于表级别的,只能将本地数据表定义为 Federated 引擎并映射至远程实体表,无法实现基于库级别的整体映射。
进入MYSQL命令行,查看FEDERATED引擎是否开启,默认是不开启
>show engines;
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED | YES | Federated MySQL 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 |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set
如果没有开启,配置my.cnf
[mysqld]
feterated
重启
在远程机192.168.32.128上创建数据库log,创建表test,如下:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE "test" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"name" varchar(255) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
创建用户,授予权限
FLUSH PRIVILEGES;
CREATE USER 'donald'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'donald'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
在本地创建相应的表:
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE "test" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"name" varchar(255) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=FEDERATED CONNECTION='mysql://donald:123456@192.168.32.128:3306/log/test';
插入数据:
INSERT INTO `log`.`test` (`id`, `name`) VALUES ('1', 'donald');
INSERT INTO `log`.`test` (`id`, `name`) VALUES ('2', 'jamel');
查询数据:
mysql> use test;
Database changed
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | donald |
| 2 | jamel |
+----+--------+