MySQL-FEDERATED引擎 跨主机联表查询（2）-实践

MySQL FEDERATED 跨实例联表查询（2）-实践

环境说明：

• 一台 CentOS 7.7.1908 服务器 IP: 192.168.110.233；
• 使用docker ，模拟 本地、远程两个不同的 MySQL 服务器。

问题：

• 不同实例间如何实现联表查询？

1、启动Mysql

# 第一台 MySQL （作为本地MySQL服务器）
$ docker run -itd \
--name test-mysql01 \
--restart=always \
-v /data/mysql01:/var/lib/mysql \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=123456 \
-e MYSQL_DATABASE=test_db \
-e MYSQL_USER=test \
-e MYSQL_PASSWORD=123456 \
-p 3316:3306 \
-d mysql:5.7

# 第二台 MySQL （作为远程MySQL服务器）
$ docker run -itd \
--name test-mysql02 \
--restart=always \
-v /data/mysql02:/var/lib/mysql \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=123456 \
-e MYSQL_DATABASE=test_db \
-e MYSQL_USER=test \
-e MYSQL_PASSWORD=123456 \
-p 3326:3306 \
-d mysql:5.7

确保MySQL 顺利启动

[root@jiangjw-test ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
cadc379c04a3        mysql:5.7           "docker-entrypoint.s…"   8 seconds ago       Up 7 seconds        33060/tcp, 0.0.0.0:3326->3306/tcp   test-mysql02
91b91237da2e        mysql:5.7           "docker-entrypoint.s…"   53 seconds ago      Up 52 seconds       33060/tcp, 0.0.0.0:3316->3306/tcp   test-mysql01

2、远程服务器（test-mysql02）建表，插入数据

[root@jiangjw-test ~]# docker exec -it cad bash
root@cadc379c04a3:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
Database changed
mysql> 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=innodb
    -> DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table        |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into test_table(name,other) values(
    -> 'zhangshan',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table(name,other) values( 'lisi',22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+-----------+-------+
| id | name      | other |
+----+-----------+-------+
|  1 | zhangshan |     1 |
|  2 | lisi      |    22 |
+</