共计 5564 个字符,预计需要花费 14 分钟才能阅读完成。
作者:马文斌
MySQL OCP 认证,PostgresSQL PGCA 认证,善于 MySQL、PostgreSQL、dble 等开源数据库相干产品的备份复原、读写拆散、SQL 调优、监控运维、高可用架构设计等。目前任职于月亮小屋 (中国) 有限公司。
本文起源:原创投稿
* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
1. 背景
- 本地 MySQL 数据库要拜访近程 MySQL 数据库的表中的数据,又不想用数据同步形式去实现,所以就思考 FEDERATED 存储引擎来实现。
- 要容许这个存储引擎,当构建 MySQL 时应用 –with-federated-storage-engine 来 configure。
2. FEDERATED 引擎架构:
当客户端收回援用 FEDERATED 表的 SQL 语句时,本地服务器(执行 SQL 语句的中央)和近程服务器(理论存储数据的中央)之间的信息流如下:
- 存储引擎将遍历 FEDERATED 表具备的每一列,并结构一个援用近程表的适当 SQL 语句。
- 该语句应用 MySQL 客户端 API 发送到近程服务器。
- 近程服务器解决该语句,本地服务器检索该语句产生的任何后果(受影响的行数或后果集)。
- 如果该语句产生后果集,则每一列都将转换为 FEDERATED 引擎冀望的外部存储引擎格局,并可用于将结果显示给收回原始语句的客户端。
- 架构图如下:
3. 相干个性
- 反对的语法:
- 在外部,实现应用 SELECT,INSERT,UPDATE,和 DELETE,但不会 HANDLER。
- 该 FEDERATED 存储引擎反对 SELECT,INSERT,UPDATE,DELETE,TRUNCATE TABLE,和索引。除之外,它不反对 ALTER TABLE、DROP TABLE 或任何间接影响表构造的数据定义语言语句。以后实现不应用筹备好的语句。
- FEDERATED 承受 INSERT … ON DUPLICATE KEY UPDATE 语句,然而如果产生反复键抵触,该语句将失败并显示谬误。
- 不反对的语法:
- FEDERATED 执行批量插入解决,以便将多行批量发送到近程表,从而进步了性能。另外,如果近程表是事务性的,则它能够使近程存储引擎在产生谬误时正确执行语句回滚。此性能具备以下限度:
1)插入的大小不能超过服务器之间的最大数据包大小。如果插入超过此大小,它将被分成多个数据包,并可能产生回滚问题。
2)不会进行批量插入解决 INSERT … ON DUPLICATE KEY UPDATE。
- 如果近程表曾经扭转,对 FEDERATED 引擎而言是没有方法晓得的。这样做的起因是,该表必须像数据文件一样工作,除了数据库系统以外,其余任何文件都不能写入。如果近程数据库产生任何更改,则可能会毁坏本地表中数据的完整性。
- 应用 CONNECTION 字符串时,不能在明码中应用“@”字符。您能够通过应用 CREATE SERVER 语句创立服务器连贯来解决此限度。
- 在 insert_id 和 timestamp 选项都不会流传到数据提供者。
- DROP TABLE 针对 FEDERATED 表收回的任何语句仅删除本地表,而不删除近程表。
- FEDERATED 表不适用于查问缓存。
- FEDERATED 表不反对用户定义的分区。
4. 环境
两个 mysql-5.7.26 实例
ip | 端口 | 备注 |
---|---|---|
192.168.234.204 | 3306 | server-1 |
192.168.234.204 | 3310 | server-2 |
5. 引擎设置
- 在配置文件制订开启 Federated 存储引擎
配置文件中增加 federated 引擎就能够,两个实例都要增加
vim /etc/my.cnf
[mysqld]
federated
- 查看 Federated 是否开启 [FEDERATED 中 Support 状态 NO 表明引擎未开启]
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 引擎
6. 部署
- 在 server-1 上有一个数据库 db1,在 server-2 上有数据库 db2,要在 server-2 的数据库 db2 上建设 server-1 的数据库 db1 上的表 tb1 的数据表链接 remote_tb1,通过普通用户 test 连贯。
- 在 server-1 上建设测试的数据库数据库、表
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)
- 在 server-1 上建设测试的账号
create user 'test'@'192.168.%' IDENTIFIED BY '123456';
grant select,update,insert,delete on db1.* to 'test'@'192.168.%';
flush privileges;
- 在 server-2 上建设近程拜访的数据库和表
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)
- 在 server-2 中查问 server-1 中的 db1.tb1 是否有数据
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)
阐明本地表和近程表关联也是能够的。
- 测试 server-2 操作下 DML,察看 binlog 是否会写 GTID 事务
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)
- /usr/local/mysql-5.7.26/bin/mysqlbinlog -vvv mysql-bin.000884 |grep -C 10 remote_tb1
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 事务暗藏了。
-
server-2 服务器察看 remote_tb2 相干文件
.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 跨库查问会显轻便一些,搭建起来很不便。
正文完