MySQL跨库访问方法总结

Jerry_Shang_0

已于 2023-02-28 18:08:53 修改

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-02-27 17:18:50 首次发布

本文链接：https://blog.csdn.net/Jerry_Shang_0/article/details/129244927

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

总结一下MySQL跨库访问的方法：

类DB-Link方法
Oracle实现跨库访问非常简单，因为有现成的db-link可用，MySQL虽然没有DB-Link，但使用特殊的存储引擎“FEDERATED”就可以实现同样的功能。具体操作如下：

# 登录本地MySQL（使用端口为3338的实例做示例）
mysql -uroot -p -h 127.0.0.1 -P 3338
-- 查看“FEDERATED”引擎是否启用(Support)
MySQL [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | 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         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
-- 这里的Support=NO，说明MySQL启动时没有加载，需要在配置文件内配置加载，重启MySQL生效。
MySQL [(none)]> exit
Bye
# 修改my.ini文件，添加 federated = ON 后保存退出，重启MySQL
# 进入MySQL确认 federated引擎 已启动
mysql -uroot -p -h 127.0.0.1 -P 3338
MySQL [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | 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         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
-- 建立远端Server链接，这里使用另一端口为3337的MySQL实例作为远端机器
MySQL [(none)]> CREATE SERVER mysql_remote FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root',PASSWORD 'oracle', HOST '192.168.56.217', PORT 3337, DATABASE 'share_db');
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> create database query_remote;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> use query_remote;
Database changed
-- 建立远端table
MySQL [query_remote]> create table share_table(id int,comms varchar(200)) ENGINE = FEDERATED CONNECTION = 'mysql_remote/share_table';
Query OK, 0 rows affected (0.00 sec)

MySQL [query_remote]> show tables;
+------------------------+
| Tables_in_query_remote |
+------------------------+
| share_table            |
+------------------------+
1 row in set (0.00 sec)
-- 像访问本地表一样，访问远端表
MySQL [query_remote]> select * from share_table;
+------+------------+
| id   | comms      |
+------+------------+
|    1 | test share |
+------+------------+
1 row in set (0.05 sec)
MySQL [query_remote]> insert into share_table(id,comms) values(2,'test for insert');
Query OK, 1 row affected (0.01 sec)

#登录远端MySQL（3337）
mysql -uroot -p -h 127.0.0.1 -P 3337
MySQL [(none)]> use share_db;
Database changed
-- 发现数据已在远端MySQL库里插入
MySQL [share_db]> select * from share_table;
+------+-----------------+
| id   | comms           |
+------+-----------------+
|    1 | test share      |
|    2 | test for insert |
+------+-----------------+
2 rows in set (0.00 sec)

ProxySQL代理中转

这个方法我用的比较多，主要因为ProxySQL小巧、简单、对配置要求较低。这个方法其实破坏了网闸对内网数据库的保护，重新将数据库暴露在了外网上，但现实中有些业务场景需要调试，运维又不能接触内网，只能用这种无奈的办法。
架构类似这样：

MySQL应用 →<自定义端口，如3306>→ ProxySQL（路由规则）→<网闸开放端口，如26001>→[网闸]→内网MySQL

ProxySQL在这里起到：端口、路径(IP地址)、用户的转发功能，以便屏蔽引入网闸对原有应用造成的影响。

具体实现步骤记录如下：

## 先计划好整体的架构：
# 部署在外网的1台ProxySQL：IP地址 192.168.56.110  端口3306
# 部署在内网的第1台MySQL：IP地址 192.168.52.111   端口6111
# 部署在内网的第2台MySQL：IP地址 192.168.52.112   端口6112
# 外网应用通过share_proxy用户访问内网(只读)
# ProxySQL通过database名称实现自动路由：db_111→第1台MySQL；db_112→第2台MySQL
------------------------------------------------------
# 内网MySQL建立对应DB的只读用户，user_111、user_112、share_proxy
# 内网MySQL建立监控用户
# 外网ProxySQL登录Admin管理界面开始操作(注意内网的IP和端口可能需要根据网闸调整)
mysql -u radmin -pradmin -h 127.0.0.1 -P 16032  --default auth=mysql_native_password --prompt 'ProxySQL Admin> '
# 添加 Server
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.52.111',6111);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.52.112',6112);
# 添加监控
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
# 查看监控的DB联通性
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
# 添加后端用户
insert into mysql_users(username,active,default_hostgroup,backend,frontend) values('user_111', 1, 1, 1, 0);
insert into mysql_users(username,active,default_hostgroup,backend,frontend) values('user_112', 1, 2, 1, 0);
# 添加前端用户
insert into mysql_users(username,active,default_hostgroup,backend,frontend) values('share_proxy', 1, 1, 0, 1);
# 添加用户密码
update mysql_users set password = 'XXXXX' where username = 'user_111';
update mysql_users set password = 'XXXXX' where username = 'user_112';
update mysql_users set password = 'XXXXX' where username = 'share_proxy';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
# 加密用户密码（默认输入为明文保存，为了安全需要进行加密）
LOAD MYSQL USERS TO RUNTIME;
SELECT username,password FROM mysql_users;
SAVE MYSQL USERS FROM RUNTIME;
SELECT username,password FROM mysql_users;
SAVE MYSQL USERS TO DISK;
# 添加路由规则
INSERT INTO mysql_query_rules (rule_id, active, schemaname,destination_hostgroup, apply) VALUES(1, 1, 'db_111', 1, 1),
(2, 1, 'db_112', 2, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
## 测试：
mysql -ushare_proxy -p -h 127.0.0.1 -P 3306
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_111           |
| db_112|
+--------------------+
3 rows in set (0.00 sec)
MySQL [(none)]> use db_111;
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 [db_111]> show tables;
+--------------------+
| Tables_in_share_db |
+--------------------+
| db_111_table        |
+--------------------+
1 row in set (0.00 sec)
MySQL [(none)]> use db_112;
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 [db_112]> show tables;
+--------------------+
| Tables_in_share_db |
+--------------------+
| db_112_table        |
+--------------------+
1 row in set (0.00 sec)