乐趣区

关于mysql:技术分享-mysql-Federated-引擎最佳实战

jiezi

作者：马文斌

MySQL OCP 认证，PostgresSQL PGCA 认证，善于 MySQL、PostgreSQL、dble 等开源数据库相干产品的备份复原、读写拆散、SQL 调优、监控运维、高可用架构设计等。目前任职于月亮小屋 (中国) 有限公司。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

1. 背景

  • 本地 MySQL 数据库要拜访近程 MySQL 数据库的表中的数据，又不想用数据同步形式去实现，所以就思考 FEDERATED 存储引擎来实现。
  • 要容许这个存储引擎，当构建 MySQL 时应用 –with-federated-storage-engine 来 configure。

2. FEDERATED 引擎架构：

当客户端收回援用 FEDERATED 表的 SQL 语句时，本地服务器（执行 SQL 语句的中央）和近程服务器（理论存储数据的中央）之间的信息流如下：

  1. 存储引擎将遍历 FEDERATED 表具备的每一列，并结构一个援用近程表的适当 SQL 语句。
  2. 该语句应用 MySQL 客户端 API 发送到近程服务器。
  3. 近程服务器解决该语句，本地服务器检索该语句产生的任何后果（受影响的行数或后果集）。
  4. 如果该语句产生后果集，则每一列都将转换为 FEDERATED 引擎冀望的外部存储引擎格局，并可用于将结果显示给收回原始语句的客户端。
  • 架构图如下：

3. 相干个性

  • 反对的语法：
  1. 在外部，实现应用 SELECT，INSERT，UPDATE，和 DELETE，但不会 HANDLER。
  2. 该 FEDERATED 存储引擎反对 SELECT，INSERT，UPDATE，DELETE，TRUNCATE TABLE，和索引。除之外，它不反对 ALTER TABLE、DROP TABLE 或任何间接影响表构造的数据定义语言语句。以后实现不应用筹备好的语句。
  3. FEDERATED 承受 INSERT … ON DUPLICATE KEY UPDATE 语句，然而如果产生反复键抵触，该语句将失败并显示谬误。
  • 不反对的语法：
  1. FEDERATED 执行批量插入解决，以便将多行批量发送到近程表，从而进步了性能。另外，如果近程表是事务性的，则它能够使近程存储引擎在产生谬误时正确执行语句回滚。此性能具备以下限度：

1）插入的大小不能超过服务器之间的最大数据包大小。如果插入超过此大小，它将被分成多个数据包，并可能产生回滚问题。

2）不会进行批量插入解决 INSERT … ON DUPLICATE KEY UPDATE。

  1. 如果近程表曾经扭转，对 FEDERATED 引擎而言是没有方法晓得的。这样做的起因是，该表必须像数据文件一样工作，除了数据库系统以外，其余任何文件都不能写入。如果近程数据库产生任何更改，则可能会毁坏本地表中数据的完整性。
  2. 应用 CONNECTION 字符串时，不能在明码中应用“@”字符。您能够通过应用 CREATE SERVER 语句创立服务器连贯来解决此限度。
  3. 在 insert_id 和 timestamp 选项都不会流传到数据提供者。
  4. DROP TABLE 针对 FEDERATED 表收回的任何语句仅删除本地表，而不删除近程表。
  5. FEDERATED 表不适用于查问缓存。
  6. 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 跨库查问会显轻便一些，搭建起来很不便。

退出移动版