mysql通过dblink访问远程实例

mysql通过dblink访问远程实例

环境

	源库	目标库
IP地址	110.53.119.178	110.53.119.190
数据库版本	mysql 5.7.31	mysql 5.7.31
测试表	card	card
数据库	database	database

一、mysql可以通过federated引擎访问其它的mysql实例

说明：
         本地MySQL数据库要访问远程MySQL数据库的表中的数据, 必须通过FEDERATED存储引擎来实现. 有点类似Oracle中的
数据库链接(DBLINK). 要允许这个存储引擎, 当构建MySQL时使用–with-federated-storage-engine来configure. 当创建一个FEDERATED表的时候, 服务器在数据库目录创建一个表定义文件. 文件由表的名字开始, 并有一个.frm扩展名.

federated安装

1、查看目标库是否启用federated插件

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | 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         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> 

2、可以看到没有使用federated，通过install plugin federated soname 'ha_federated.so';进行安装，如果已经安装会提示ERROR 1125 (HY000): Function 'federated' already exists
3、启动federated。

[root@host-190 ~]# /u01/mysql5.7/bin/mysqld_safe --federated &
[1] 18907
[root@host-190 ~]#  2020-08-01T10:04:19.245834Z mysqld_safe Logging to '/u01/mysql5.7/log/mysql.err'.
2020-08-01T10:04:19.311713Z mysqld_safe A mysqld process already exists

[1]+  閫€鍑1                /u01/mysql5.7/bin/mysqld_safe --federated
[root@host-190 ~]# 
[root@host-190 ~]#  service mysql stop 
Shutting down MySQL.............. SUCCESS! 
[root@host-190 ~]#  service mysql status
 ERROR! MySQL is not running
[root@host-190 ~]#  /u01/mysql5.7/bin/mysqld_safe --federated &
[1] 19831
[root@host-190 ~]# 2020-08-01T10:13:50.050941Z mysqld_safe Logging to '/u01/mysql5.7/log/mysql.err'.
2020-08-01T10:13:50.092727Z mysqld_safe Starting mysqld daemon with databases from /u01/mysql5.7/data

[root@host-10-253-234-33 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31-log 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 engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | 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         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> 

4、配置/etc/my.conf，在配置文件中加放federated参数，设置federated默认启动。然后重启mysql服务

[root@host-190 ~]#  more /etc/my.cnf
[client]
socket=/u01/mysql5.7/mysql.sock
[mysqld]
user=mysql
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet=20M
max_connections=1000
basedir=/u01/mysql5.7
datadir=/u01/mysql5.7/data
socket=/u01/mysql5.7/mysql.sock
lower_case_table_names=1
character_set_server=utf8
federated
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

server_id=2
log_bin=mysql-bin
binlog_format=MIXED
relay_log_recovery=1

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/u01/mysql5.7/log/mysql.err
pid-file=/u01/mysql5.7/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@host-190 ~]# 
[root@host-190 ~]# 
[root@host-190 ~]# 
[root@host-190 ~]#  service mysql restart
Shutting down MySQL.......2020-08-01T10:18:37.440227Z mysqld_safe mysqld from pid file /u01/mysql5.7/mysql.pid ended
 SUCCESS! 
Starting MySQL. SUCCESS! 
[1]+  瀹屾                 /u01/mysql5.7/bin/mysqld_safe --federated
[root@host-190 ~]# 
[root@host-190 ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31-log 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 engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | 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         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> 

配置link连接，创建federated表

1、在target端定义基于federated存储引擎的表links

查看源端card表的建表sql

mysql> show create table card;
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                         |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| card | CREATE TABLE `card` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `band_card_no` varchar(32) NOT NULL DEFAULT '' COMMENT '卡号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1975 DEFAULT CHARSET=utf8mb4 COMMENT='黑名单表'              |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

2、修改建表sql，并在目标库执行建表

CREATE TABLE `card` 
(`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
band_card_no` varchar(32) NOT NULL DEFAULT '' COMMENT '银行卡号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=FEDERATED AUTO_INCREMENT=1975 DEFAULT CHARSET=utf8mb4 COMMENT='银行卡黑名单表'
CONNECTION='mysql://username:password@110.53.119.178:3306/database/card';

主要是修改ENGINE和添加CONNECTION两部份，其中：
usrname为源主机中MySQL的用户名
password为相应的密码
110.53.119.178为源主机IP地址
3306为端口号
database为源端数据库名称
card为源端表名称

3、测试访问:

mysql> select count(1) card;
+------+
| user |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> 

二、通过创建CREATE SERVER，访问远程实例表

1、创建mysql server

mysql> CREATE SERVER pafedlink
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'username', PASSWORD 'passowrd', HOST '110.53.119.178', PORT 3306, DATABASE 'database');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mysql.servers;
+-------------+---------------+---------------+-----------+------------+------+--------+---------+-------+
| Server_name | Host          | Db            | Username  | Password   | Port | Socket | Wrapper | Owner |
+-------------+---------------+---------------+-----------+------------+------+--------+---------+-------+
| pafedlink   | 110.53.119.178 | database| username| password | 3306 |        | mysql   |       |
+-------------+---------------+---------------+-----------+------------+------+--------+---------+-------+
1 row in set (0.00 sec)

mysql>

2、目标端创建FEDERATED表

CREATE TABLE `card` 
(`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
band_card_no` varchar(32) NOT NULL DEFAULT '' COMMENT '银行卡号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=FEDERATED AUTO_INCREMENT=1975 DEFAULT CHARSET=utf8mb4 COMMENT='银行卡黑名单表'
CONNECTION='pafedlink/card';

也是ENGINE和CONNECTION两个地方修改。

3、测试

mysql> select count(1) card;
+------+
| user |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>