mysql如何使用dblink

环境:
OS:Centos 7
mysql:5.7

1.开启federated
在配置文件栏目[mysqld]下添加
federated

2.重启动
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql57/mysql.sock shutdown
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &

3.创建dblink
登录:
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -S /opt/mysql57/mysql.sock

创建指向dblink的表
create table tb_test_dblink
(
id int,
name varchar(32)
) ENGINE=FEDERATED CONNECTION='mysql://hxl:mysql@192.168.1.100:3306/db_test/tb_test';

注意这里hxl是目标端的数据库账号,该账号需要有访问tb_test表的权限

mysql> select * from tb_test_dblink;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
| 6 | name6 |
+------+-------+
6 rows in set (0.01 sec)

 

-- The End --