总结一下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)
- ShardingSphere-Proxy代理中转
待续…
版权声明：本文为Jerry_Shang_0原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。