作者:马文斌
MySQL OCP 认证,PostgresSQL PGCA 认证,擅长 MySQL、PostgreSQL、dble 等开源数据库相关产品的备份恢复、读写分离、SQL 调优、监控运维、高可用架构设计等。目前任职于月亮小屋(中国)有限公司。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
当客户端发出引用 FEDERATED 表的 SQL 语句时,本地服务器(执行 SQL 语句的地方)和远程服务器(实际存储数据的地方)之间的信息流如下:
两个 mysql-5.7.26 实例
ip | 端口 | 备注 |
---|---|---|
192.168.234.204 | 3306 | server-1 |
192.168.234.204 | 3310 | server-2 |
配置文件中添加 federated 引擎就可以,两个实例都要添加
vim /etc/my.cnf
[mysqld]
federated
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | 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 |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
FEDERATED | YES 说明已经开启了FEDERATED 引擎
create database db1;
use db1;
create table tb1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=INNODB;
插入数据:
insert into tb1 select NULL;
insert into tb1 select NULL;
insert into tb1 select NULL;
查看数据
mysql> select * from tb1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
create user 'test'@'192.168.%' IDENTIFIED BY '123456';
grant select,update,insert,delete on db1.* to 'test'@'192.168.%';
flush privileges;
create database db2;
use db2;
create table remote_tb1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=FEDERATED
CONNECTION='mysql://test:123456@192.168.234.204:3306/db1/tb1';
create table tb2(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(20)
)ENGINE=INNODB;
插入数据:
insert into tb2(name) select 'a';
insert into tb2(name) select 'b';
insert into tb2(name) select 'c';
mysql> select * from db2.tb2;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from db2.remote_tb1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
说明是关联上了,
测试下join:
select t2.* from db2.remote_tb1 as t1 join
db2.tb2 as t2 on t1.id=t2.id
where t2.name='c';
mysql> select t2.* from db2.remote_tb1 as t1 join
-> db2.tb2 as t2 on t1.id=t2.id
-> where t2.name='c';
+----+------+
| id | name |
+----+------+
| 3 | c |
+----+------+
1 row in set (0.00 sec)
说明本地表和远程表关联也是可以的。
mysql> delete from db2.remote_tb1 where id =3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from db2.remote_tb1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 40057515
#210415 14:25:53 server id 2342042 end_log_pos 40057586 CRC32 0x82abe215 Query thread_id=53 exec_time=0 error_code=0
SET TIMESTAMP=1618467953/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 40057586
#210415 14:25:53 server id 2342042 end_log_pos 40057638 CRC32 0xddbc9dfc Table_map: `db2`.`remote_tb1` mapped to number 416
# at 40057638
#210415 14:25:53 server id 2342042 end_log_pos 40057678 CRC32 0x5c28d0d0 Delete_rows: table id 416 flags: STMT_END_F
BINLOG '
cdx3YBOavCMANAAAACY7YwIAAKABAAAAAAEAA2RiMgAKcmVtb3RlX3RiMQABAwAA/J283Q==
cdx3YCCavCMAKAAAAE47YwIAAKABAAAAAAEAAgAB//4DAAAA0NAoXA==
'/*!*/;
### DELETE FROM `db2`.`remote_tb1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
# at 40057678
#210415 14:25:53 server id 2342042 end_log_pos 40057750 CRC32 0xb37fe7b3 Query thread_id=53 exec_time=0 error_code=0
SET TIMESTAMP=1618467953/*!*/;
COMMIT
/*!*/;
从 binlog SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; 是可以看出,链接端操作DML是把GTID 事务隐藏了。
.frm 表定义文件 [ Federated链接库本地不产生数据文件 ]
[root@EOS_DB04 db2]# pwd
/mysqlData/3310_data/data/db2
[root@EOS_DB04 db2]# ll
total 128
-rw-r----- 1 mysql mysql 67 Apr 15 14:11 db.opt
-rw-r----- 1 mysql mysql 8556 Apr 15 14:11 remote_tb1.frm
-rw-r----- 1 mysql mysql 8586 Apr 15 14:18 tb2.frm
-rw-r----- 1 mysql mysql 98304 Apr 15 14:18 tb2.ibd
对比其他的数据同步产品,这种建立 链接 跨 ip 跨库查询会显轻便一些,搭建起来很方便。