通过FEDERATED存储引擎同步两实例间的表数据
需求情景:实例1中A库中的三个视图是实例2中的B库所依赖的,B需要A库中三个视图的实时数据。
方案:通过FEDERATED来完成跨势力的查询
FEDERATED存储引擎表只会创建表结构,不会存储表数据,可以通过建表语句中的CONNECTION [=] 'connect_string'来指定访问远端数据的连接方式connect_string:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_namehttp://dev.mysql.com/doc/refman/5.7/en/federated-create-connection.html
测试实施:
1.本地环境:
[root@test-01 ~]# uname -a
Linuxtest-01 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
[root@test-01 ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.17, for linux-glibc2.5 (x86_64) using EditLine wrapper
2.创建FEDERATED存储引擎并启动
mysql> show engines
-> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | 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 |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
默认是支持的,在配置文件中加入federated选项,并重启服务
3.实例1A上:
GRANT select ON test.t TO 'TEST'@'%' IDENTIFIED BY 'test';
4.实例B上:
mysql> CREATE TABLE test
.fd_test_t
( id
int(11) DEFAULT NULL ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:root@host:6666/test/t';
Query OK, 0 rows affected (0.00 sec)
到目前为止操作结束,现在在B上执行
mysql> select * from fd_test_t;
+------+
| id |
+------+
| 1 |
+------+
就可以看到A表中test.t的数据了
通过FEDERATED存储引擎跨实例访问数据
最新推荐文章于 2021-01-23 20:58:40 发布