mysql feterated_Mysql FEDERATED发动机_mysql

mysql FEDERATED引擎

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

http://zhangxugg-163-com.iteye.com/blog/1666673FEDERATED

Storage Engine Notes and Tips：http://dev.mysql.com/doc/refman/5.7/en/federated-usagenotes.html

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

1

[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 ;

1

SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructurefortest------------------------------DROPTABLEIFEXISTS`test`;CREATETABLE"test"("id"int(11)NOTNULLAUTO_INCREMENT,"name"varchar(255)DEFAULTNULL,PRIMARYKEY("id"))ENGINE=InnoDBDEFAULTCHARSET=utf8;

创建用户，授予权限

FLUSH PRIVILEGES; CREATE USER 'donald'@'%' IDENTIFIED BY '123456'; GRANT ALL PRIVILEGES ON *.* TO 'donald'@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;

1

FLUSHPRIVILEGES;CREATEUSER'donald'@'%'IDENTIFIEDBY'123456';GRANTALLPRIVILEGESON *.*TO'donald'@'%'IDENTIFIEDBY'123456';FLUSHPRIVILEGES;

在本地创建相应的表：

-- ---------------------------- -- 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';

1

--------------------------------Tablestructurefortest------------------------------DROPTABLEIFEXISTS`test`;CREATETABLE"test"("id"int(11)NOTNULLAUTO_INCREMENT,"name"varchar(255)DEFAULTNULL,PRIMARYKEY("id"))ENGINE=FEDERATEDCONNECTION='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  |

+----+--------+

欢迎大家阅读《Mysql FEDERATED发动机_mysql》,跪求各位点评，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~