【MySQL】DB-Link 跨库访问

 

相关说明：

https://blog.csdn.net/qq_48721706/article/details/124088963

　　

DB-LINK以一个远程访问方式访问其他MYSQL实例

连接实例和被连接实例都需要开启FEDERATED引擎

 

 

Federated引擎配置

检查库是否支持FEDERATED

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

　　

检查是否安装了FEDERATED插件

INSTALL PLUGIN federated SONAME 'ha_federated.so';
1125 - Function 'federated' already exists

　

如果没有，打开MYSQL配置文件【my.ini】或者【my.cnf】配置FEDERATED参数

然后重启MYSQL服务

[mysqld]
# mysql所在的目录
basedir=D:\mysql-8.0.28-winx64\

# mysql 数据库存储的目录
datadir=D:\mysql-8.0.28-winx64\data\

# mysql服务端默认使用的字符集
character-set-server=utf8

# 默认使用的存储引擎
default-storage-engine=INNODB

# mysql服务端的端口号
port=3308

# 支持其它MYSQL以DBLINK方式访问本实例
federated

# 关闭SSL认证
skip_ssl

[mysql]
# mysql客户端默认使用的字符集
default-character-set=utf8

 

再次检查是否开启

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine             | Support | Comment                                                        | Transactions | XA  | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO  | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO  | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO  | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO  | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+

　　

DBLINK访问测试：

在A库创建一张测试表以及数据：

CREATE TABLE `grade` (
  `id` int NOT NULL,
  `job` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (1, 'C++', 11001);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (2, 'C++', 10000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (3, 'C++', 9000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (4, 'Java', 12000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (5, 'Java', 13000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (6, 'B', 12000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (7, 'B', 11000);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (8, 'B', 9999);
INSERT INTO `test`.`db-link-grade`(`id`, `job`, `score`) VALUES (9, 'C', 22344);

　　

创建完成之后，在B库创建LINK表：

CREATE TABLE `db-link-grade` (
  `id` int(11) NOT NULL,
  `job` varchar(32) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://root:123456@localhost:3308/nowcoder-sql/grade';

-- 指定表引擎为Federated
ENGINE=FEDERATED

-- 连接的目标表
CONNECTION='mysql://用户名:密码@目标Host:端口/库名/表名';

　　

查询LINK表检查是否能成功访问：

SELECT * FROM `db-link-grade`;
+----+------+-------+
| id | job  | score |
+----+------+-------+
|  1 | C++  | 11001 |
|  2 | C++  | 10000 |
|  3 | C++  |  9000 |
|  4 | Java | 12000 |
|  5 | Java | 13000 |
|  6 | B    | 12000 |
|  7 | B    | 11000 |
|  8 | B    |  9999 |
|  9 | C    | 22344 |
+----+------+-------+