MySQL federated儲存引擎測試
環境：
172.16.1.137 遠端庫 172.16.1.138 本地庫 資料庫版本：MySQL 5.7.36
需求：
本地庫172.16.1.138，透過federated儲存引擎訪問遠端庫172.16.1.137部分表。
測試結論：
使用之前以為和Oracle dblink類似，測試發現限制一大堆。 1.遠端庫表執行insert,update,delete,truncate可以同步到本地表。 2.遠端庫執行alter新增欄位、修改欄位、刪除欄位等操作，不能同步到本地表，需要刪除重建本地表。 3.遠端庫表執行drop table不會同步到本地表，但本地表也無法執行查詢等所有操作。 4.本地表執行drop table只會刪除本地虛擬表，不會刪除遠端表。 5.本地表執行insert,update,delete,truncate也會反向同步到遠端表，需要特別注意。 6.本地表執行count,limit等操作效率差，很多場景下不走索引。 7.遠端表不支援事務。
測試過程如下：
一：本地庫啟用federated 二：新增測試資料 三：資料同步測試 四：本地庫效能如何？ 五：遠端表是否支援事務？
一：本地庫啟用federated
遠端庫不需要啟用
檢查引數
[mysql@cjc-db-01 ~]$ cat /etc/my.cnf|grep -i federated skip-federated
MySQL [(none)]> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | NULL | NULL | NULL | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
修改引數
172.16.1.138
[mysql@cjc-db-02 ~]$ vi /etc/my.cnf #skip-federated federated
[mysql@cjc-db-02 ~]$ cat /etc/my.cnf|grep feder #skip-federated federated
重啟資料庫生效
[mysql@cjc-db-02 ~]$ mysqladmin -uroot -p shutdown [mysql@cjc-db-02 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &
檢查引數
MySQL [(none)]> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | NULL | NULL | NULL | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
二：新增測試資料
遠端庫：172.16.1.137
create database test1; create user test1@'172.%' identified by "1"; grant all privileges on test1.* to test1@'172.%'; flush privileges;
use test1; create table t1(id int,time datetime); insert into t1 values(1,now()),(2,now()),(3,now()),(4,now()),(5,now());
本地庫：建立FEDERATED表，指向遠端庫
create database test2; create user test2@'172.%' identified by "1"; grant all privileges on test2.* to test2@'172.%'; flush privileges; use test2; create table t1(id int,time datetime) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
其中：建立FEDERATED表語法如下
The format of the connection string is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
引數說明：
1.scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point. 2.user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table. 3.password: (Optional) The corresponding password for user_name. 4.host_name: The host name or IP address of the remote server. 5.port_num: (Optional) The port number for the remote server. The default is 3306. 6.db_name: The name of the database holding the remote table. 7.tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.
查詢本地庫資料
MySQL [test2]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2023-01-11 17:23:48 | | 2 | 2023-01-11 17:23:48 | | 3 | 2023-01-11 17:23:48 | | 4 | 2023-01-11 17:23:48 | | 5 | 2023-01-11 17:23:48 | +------+---------------------+ 5 rows in set (0.05 sec)
三：資料同步測試
---insert 172.16.1.137 遠端庫 MySQL [test1]> insert into t1 values(6,now()); MySQL [test1]> insert into t1 values(7,now()); MySQL [test1]> insert into t1 values(8,now());
172.16.1.138 本地庫，正常同步
MySQL [test2]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2023-01-11 17:23:48 | | 2 | 2023-01-11 17:23:48 | | 3 | 2023-01-11 17:23:48 | | 4 | 2023-01-11 17:23:48 | | 5 | 2023-01-11 17:23:48 | | 6 | 2023-01-11 17:40:13 | | 7 | 2023-01-11 17:40:17 | | 8 | 2023-01-11 17:40:22 | +------+---------------------+ 8 rows in set (0.02 sec)
---update
172.16.1.137 遠端庫
MySQL [test1]> update t1 set id=500 where id>=5; 172.16.1.138 本地庫，正常同步 MySQL [test2]> select * from t1; MySQL [test2]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2023-01-11 17:23:48 | | 2 | 2023-01-11 17:23:48 | | 3 | 2023-01-11 17:23:48 | | 4 | 2023-01-11 17:23:48 | | 500 | 2023-01-11 17:23:48 | | 500 | 2023-01-11 17:40:13 | | 500 | 2023-01-11 17:40:17 | | 500 | 2023-01-11 17:40:22 | +------+---------------------+ 8 rows in set (0.01 sec)
---delete
172.16.1.137 遠端庫
MySQL [test1]> delete from t1 where id=4;
172.16.1.138 本地庫，正常同步
MySQL [test2]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2023-01-11 17:23:48 | | 2 | 2023-01-11 17:23:48 | | 3 | 2023-01-11 17:23:48 | | 500 | 2023-01-11 17:23:48 | | 500 | 2023-01-11 17:40:13 | | 500 | 2023-01-11 17:40:17 | | 500 | 2023-01-11 17:40:22 | +------+---------------------+ 7 rows in set (0.02 sec)
---alter
新增欄位
遠端庫：
alter table t1 add(name char(10)); MySQL [test1]> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | time | datetime | YES | | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) insert into t1 values(6,now(),'a');
本地庫：不同步
MySQL [test2]> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | time | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
本地庫，也不支援手動新增欄位
MySQL [test2]> alter table t1 add(name char(10)); ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
只能重建表
MySQL [test2]> drop table t1; MySQL [test2]> CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `time` datetime DEFAULT NULL, `name` char(10) DEFAULT NULL ) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
MySQL [test2]> select * from t1; +------+---------------------+------+ | id | time | name | +------+---------------------+------+ | 1 | 2023-01-11 17:23:48 | NULL | | 2 | 2023-01-11 17:23:48 | NULL | | 3 | 2023-01-11 17:23:48 | NULL | | 500 | 2023-01-11 17:23:48 | NULL | | 500 | 2023-01-11 17:40:13 | NULL | | 500 | 2023-01-11 17:40:17 | NULL | | 500 | 2023-01-11 17:40:22 | NULL | | 6 | 2023-01-11 17:59:01 | a | +------+---------------------+------+ 8 rows in set (0.05 sec)
修改欄位型別
遠端庫：
alter table t1 modify name char(15);
本地庫：不同步
MySQL [test2]> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | time | datetime | YES | | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
刪除欄位
遠端庫：
MySQL [test1]> alter table t1 drop column name;
本地庫:不同步
MySQL [test2]> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | time | datetime | YES | | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
---truncate
遠端庫：
MySQL [test1]> truncate table t1; Query OK, 0 rows affected (0.03 sec)
本地庫：表結構不一致時，無法執行查詢操作
MySQL [test2]> select * from t1; ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'name' in 'field list'' from FEDERATED
重建表
drop table t1; CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `time` datetime DEFAULT NULL ) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
遠端庫：
重新插入幾條新資料庫後，再次執行truncate操作。
本地庫：正常同步
MySQL [test2]> select * from t1; Empty set (0.01 sec)
---drop
遠端庫：
MySQL [test1]> drop table t1;
本地庫：不同步，查詢報錯
MySQL [test2]> select * from t1; ERROR 1430 (HY000): : 0 :
本地庫可以執行哪些操作
---select 可以查詢
---insert
本地庫：可以insert
MySQL [test2]> insert into t1 values(100,now());
遠端庫：同步
MySQL [test1]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ ...... | 100 | 2023-01-12 15:02:15 | +------+---------------------+ 7 rows in set (0.01 sec)
---update
本地庫：可以update
MySQL [test2]> update t1 set id=1000 where id=100;
遠端庫：同步
MySQL [test1]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ ...... | 1000 | 2023-01-12 15:02:15 | +------+---------------------+ 7 rows in set (0.00 sec)
---delete
本地庫：可以delete
MySQL [test2]> delete from t1 where id=1;
遠端庫：居然也刪除了資料
MySQL [test1]> select * from t1 where id=1;
---truncate
本地庫：可以truncate
MySQL [test2]> truncate table t1;
遠端庫：
MySQL [test1]> select * from t1; Empty set (0.01 sec)
---alter
本地庫：不能delete
MySQL [test2]> alter table t1 add(name char(10)); ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option MySQL [test2]> alter table t1 modify id int(12); ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option MySQL [test2]> alter table t1 drop column id; ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
---drop
本地庫：可以drop
MySQL [test2]> drop table t1; Query OK, 0 rows affected (0.00 sec)
遠端庫：沒有被刪除
MySQL [test1]> select * from t1; +------+---------------------+ | id | time | +------+---------------------+ | 100 | 2023-01-12 15:11:48 | | 100 | 2023-01-12 15:11:50 | | 100 | 2023-01-12 15:11:50 | +------+---------------------+ 3 rows in set (0.00 sec)
四：本地庫效能如何？
遠端庫，新建表，新增索引
CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `time` datetime DEFAULT NULL, KEY `i_t1_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into t1 values(1,now()),(1,now()),(1,now()),(1,now()),(1,now());
插入100萬資料。
MySQL [test1]> insert into t1 select * from t1; MySQL [test1]> insert into t1 select * from t1; ...
場景一：count彙總
查詢耗時0.6秒
MySQL [test1]> select count(*) from t1; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.60 sec)
走索引了
MySQL [test1]> explain select count(*) from t1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | i_t1_id | 5 | NULL | 1046760 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
本地庫
CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `time` datetime DEFAULT NULL, KEY `i_t1_id` (`id`) ) ENGINE=FEDERATED CONNECTION='mysql://test1:1@172.16.6.137:13309/test1/t1';
查詢耗時2.37秒
MySQL [test2]> select count(*) from t1; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (2.37 sec)
全表掃描，不走索引
MySQL [test2]> explain select count(*) from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1046760 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
場景2：limilt
遠端庫：
MySQL [test1]> select id from t1 limit 1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
MySQL [test1]> explain select id from t1 limit 1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | i_t1_id | 5 | NULL | 1046760 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
本地庫：
MySQL [test2]> select id from t1 limit 1; +------+ | id | +------+ | 100 | +------+ 1 row in set (2.08 sec) MySQL [test2]> explain select id from t1 limit 1; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1046760 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 1 warning (0.02 sec)
指定索引列才能走索引
MySQL [test2]> select id from t1 where id=0 limit 1; Empty set (0.01 sec) MySQL [test2]> explain select id from t1 where id=0 limit 1; +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | i_t1_id | i_t1_id | 5 | const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
五：遠端表是否支援事務？
本地表：
MySQL [test2]> select count(*) from t1; MySQL [test2]> start transaction; MySQL [test2]> insert into t1 values(1,now()); MySQL [test2]> rollback; MySQL [test2]> select count(*) from t1;
資料並沒有回滾，不支援事務。
參考：
https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html
###chenjuchao 20230113 12:00###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2932072/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 遠端連線（federated儲存引擎）MySql儲存引擎
- MySQL federated引擎試驗（DBLINK的作用）MySql
- FEDERATED儲存引擎實現oracle dblink功能及弊端儲存引擎Oracle
- MySQL 儲存引擎MySql儲存引擎
- mysql儲存引擎MySql儲存引擎
- MySQL系列-儲存引擎MySql儲存引擎
- MySQL Archive儲存引擎MySqlHive儲存引擎
- MySql 官方儲存引擎MySql儲存引擎
- MySQL MEMORY儲存引擎MySql儲存引擎
- MySQL InnoDB儲存引擎MySql儲存引擎
- 【Mysql 學習】Mysql 儲存引擎MySql儲存引擎
- MySQL入門--儲存引擎MySql儲存引擎
- 理解mysql的儲存引擎MySql儲存引擎
- MySQL之四 儲存引擎MySql儲存引擎
- (5)mysql 常用儲存引擎MySql儲存引擎
- MySQL-05.儲存引擎MySql儲存引擎
- federated儲存引擎實現跨伺服器的資料訪問儲存引擎伺服器
- MySQL下的DBlink--FEDERATED引擎MySql
- MySQL InnoDB 儲存引擎探祕MySql儲存引擎
- 2_mysql（索引、儲存引擎）MySql索引儲存引擎
- MySql 擴充套件儲存引擎MySql套件儲存引擎
- MySQL 5.5儲存引擎介紹MySql儲存引擎
- 【Mysql 學習】memory儲存引擎MySql儲存引擎
- MySQL 資料庫儲存引擎MySql資料庫儲存引擎
- MyISAM 儲存引擎,Innodb 儲存引擎儲存引擎
- MySQL2：四種MySQL儲存引擎MySql儲存引擎
- 聊一聊MySQL的儲存引擎MySql儲存引擎
- 如何選擇mysql的儲存引擎MySql儲存引擎
- 小談mysql儲存引擎優化MySql儲存引擎優化
- MySQL儲存引擎入門介紹MySql儲存引擎
- MySQL資料庫操作、儲存引擎MySql資料庫儲存引擎
- MySQL入門--設定儲存引擎MySql儲存引擎
- Mysql常見儲存引擎介紹MySql儲存引擎
- Linux MySQL 儲存引擎詳解LinuxMySql儲存引擎
- 【Mysql 學習】MyISAM儲存引擎(二)。MySql儲存引擎
- 【Mysql 學習】MyISAM儲存引擎(一)。MySql儲存引擎
- 【MySQL】MySQL（四）儲存引擎、索引、鎖、叢集MySql儲存引擎索引
- Mysql技術內幕InnoDB儲存引擎讀書筆記--《二》InnoDB儲存引擎MySql儲存引擎筆記