mysql通过dblink访问远程实例
查看目标库是否启用federated插件mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine| Support | Comme
·
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>
无需安装部署，在线快速体验 Byzer
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)