MENU

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

April 16, 2020 • Linux运维工作

MySQL FEDERATED 跨实例联表查询实践

环境说明：

  • 一台 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 |
+----+-----------+-------+
2 rows in set (0.00 sec)

3、查看本地数据库（test-mysql01）是否满足 FEDERATED 引擎条件；

# 进入 test-mysql02 查看
docker exec -it cad bash

代码窗显示不全，所以用图片展示

4、开启 FEDERATED 引擎

1、因为使用的官方 MySQL 镜像，所以配置文件位置有所不同，需要根据自身情况确定文件路径，如未做变动一遍为 /etc/my.cnf ，以及容器里面没有vi vim 所以我直接重定向将 federated 追加到 MySQL 配置文件 [mysqld] 下方。

root@91b91237da2e:/# cat /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# 追加 federated 到 [mysqld] 下方
root@91b91237da2e:/# echo federated >>  /etc/mysql/mysql.conf.d/mysqld.cnf

root@91b91237da2e:/# cat /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated

2、重启 MySQL 服务，我这里就直接重启容器即可；

[root@jiangjw-test ~]# docker restart 91b
91b

3、再次进入容器查看 FEDERATED 引擎是否开启；

show2

5、创建 FEDERATED 表

show create table {table_name}; 可查看，表创建时的信息。

1、确认能否连接远程数据库，并拿到该表创建时的结构信息；

root@91b91237da2e:/# mysql -h 192.168.110.233 -P 3326 -u test -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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 |
| test_db            |
+--------------------+
2 rows in set (0.00 sec)

mysql> show create table test-db.test-table;
ERROR 1046 (3D000): No database selected
mysql> show create table test_db.test-table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-table' at line 1
mysql> show create table test_db.test_table;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                            |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | 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`),
  KEY `name` (`name`),
  KEY `other_key` (`other`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

2、组合出 FEDERATED 表的sql语句，并在本地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`),
  KEY `name` (`name`),
  KEY `other_key` (`other`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://test:123456@192.168.110.233:3326/test_db/test_table';
-- 本地MySQL服务器，执行
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

-- 为了易于辨识，这里特地创建了一个不同名的 image 库，在该库下操作
mysql> create database image default charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use image;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| image      |
+------------+
1 row in set (0.00 sec)

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`),
    ->   KEY `name` (`name`),
    ->   KEY `other_key` (`other`)
    -> )
    -> ENGINE=FEDERATED
    -> DEFAULT CHARSET=utf8mb4
    -> CONNECTION='mysql://test:123456@192.168.110.233:3326/test_db/test_table';
Query OK, 0 rows affected (0.00 sec)

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

-- 创建该表以后，我们并未做任何插入数据操作，但是可以看到与远程MySQL服务器中该表内容完全一致
mysql> select * from test_table;
+----+-----------+-------+
| id | name      | other |
+----+-----------+-------+
|  1 | zhangshan |     1 |
|  2 | lisi      |    22 |
+----+-----------+-------+
2 rows in set (0.02 sec)

mysql>

6、验证

这里我们来通过文件验证一下本地MySQL 该表到底是怎样的存储；

# 本地MySQL image 库下，test_table 表只有一个 .frm 表结构文件
[root@jiangjw-test ~]# ls /data/mysql01/image/
db.opt  test_table.frm

# 远程MySQL 该 test_table 表有 InnoDB 引擎所需的两个文件，.frm .ibd
[root@jiangjw-test ~]# ls /data/mysql02/test_db/
db.opt  test_table.frm  test_table.ibd

所以我的个人理解，其实这就是个软链接！

7、测试

本地MySQL插入数据

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

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

mysql> select * from test_table;
+----+-----------+-------+
| id | name      | other |
+----+-----------+-------+
|  1 | zhangshan |     1 |
|  2 | lisi      |    22 |
|  3 | wanger    |   100 |
|  4 | mazi      |   200 |
+----+-----------+-------+
4 rows in set (0.00 sec)

mysql>

远程MySQL查看

-- 远程MySQL服务器无 image 库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> select * from test_table;
+----+-----------+-------+
| id | name      | other |
+----+-----------+-------+
|  1 | zhangshan |     1 |
|  2 | lisi      |    22 |
|  3 | wanger    |   100 |
|  4 | mazi      |   200 |
+----+-----------+-------+
4 rows in set (0.00 sec)

mysql>

通过本地服务器操作该表，远程服务器与本地服务器该表数据一致。

既然现在远程服务器上的数据表相当于在本服务器上了，那么我们创建一个简单的表，试一下联表查询

-- 进入本地docker创建时，指定的 test_db 库
mysql> use test_db;
Database changed
-- 创建一个 salary 表，就两个字段
mysql> create table salary(
    -> id int(1),
    -> money int(1)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| salary            |
+-------------------+
1 row in set (0.00 sec)
-- 插入数据
mysql> insert into salary values(1,8888);
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values(2,9999);
Query OK, 1 row affected (0.00 sec)

-- 联表查询
mysql> select a.name,b.money from image.test_table as a,test_db.salary as b where a.id=b.id;                                                                                +-----------+-------+
| name      | money |
+-----------+-------+
| zhangshan |  8888 |
| lisi      |  9999 |
+-----------+-------+
2 rows in set (0.01 sec)

mysql>

通过FEDERATED引擎，我们将远程实例的表可以轻松的链接到本地数据库，从而可以实现轻松的联表查询。

试一下本地删除该表会怎样

-- 本地MySQL
mysql> show tables;
+-----------------+
| Tables_in_image |
+-----------------+
| test_table      |
+-----------------+
1 row in set (0.00 sec)

mysql> drop test_table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test_table' at line 1
mysql> drop table test_table;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>

远程MySQL查看

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

mysql> select * from test_table;
+----+-----------+-------+
| id | name      | other |
+----+-----------+-------+
|  1 | zhangshan |     1 |
|  2 | lisi      |    22 |
|  3 | wanger    |   100 |
|  4 | mazi      |   200 |
+----+-----------+-------+
4 rows in set (0.00 sec)

mysql>

远程MySQL 该表并未受影响！

再来查看一下 表存储文件变化

# 本地MySQL中，该表所有文件消失
[root@jiangjw-test ~]# ls /data/mysql01/image/
db.opt
# 远程MySQL，该表并未受到影响
[root@jiangjw-test ~]# ls /data/mysql02/test_db/
db.opt  test_table.frm  test_table.ibd

8、总结

可以通过FEDERATED引擎将远程服务器上的表，同步该表的表结构到本服务器上，从而实现本地联表查询。实则远程那一步FEDERATED引擎帮我们做了。

mysql
Last Modified: May 8, 2024