MySQL uses the FREDATED engine for cross-database server and cross-instance access

Cross-database server and cross-instance access is a common access method, which can be implemented through DBLINK in Oracle. For MySQL, there is a FEDERATED storage engine. You can also create a link to access data on the remote server. This article briefly describes the FEDERATED storage engine and

Cross-database server and cross-instance access is a common access method, which can be implemented through db link in Oracle. For MySQL, there is a FEDERATED storage engine. You can also create a link to access data on the remote server. This article briefly describes the FEDERATED storage engine and

Cross-database server and cross-instance access is a common access method, which can be implemented through db link in Oracle. For MySQL, there is a FEDERATED storage engine. You can also create a link to access data on the remote server. This article briefly describes the FEDERATED storage engine and demonstrates examples of cross-instance access based on the FEDERATED storage engine.

1. Description of FEDERATED storage engine

FEDERATED storage engine allows remote database access without using replication or cluster technology
When creating a FEDERATED storage engine-based table, the server creates only one table definition file in the database directory, that is, the. frm file starting with the table name.

FEDERATED storage engine tables do not store any data locally, that is, there is no. myd File
The operations in the remote server table are the same as those in the local table, except that the data is on the remote server.
The basic process is as follows:

2. Install and enable the FEDERATED storage engine

Use DWITH_FEDERATED_STORAGE_ENGINE to configure the source code when installing MySQL.
By default, the rpm installation method has been installed. You only need to enable this function.

3. Prepare a remote server environment

-- In this demonstration, the remote server and the local server are multiple instances of multiple versions on the same server.
-- Assume that the remote service is 5.6.12 (instance 3406)
-- Assume that the local server: 5.6.21 (instance 3306)
-- Create the FEDERATED storage engine table test. federated_engine Based on instance 3306 to access instance 3406 database tempdb. tb_engine

[Root @ rhel64a ~] # Cat/etc/issue
Red Hat Enterprise Linux Server release 6.4 (Santiago)

-- Start instance 3406
[Root @ rhel64a ~] #/U01/app/mysql/bin/mysqld_multi start 3406
[Root @ rhel64a ~] # Mysql-uroot-pxxx-P3406 -- protocol = tcp

Root @ localhost [(none)]> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server ID | 3406 |
+ --------------- + ------- +

-- Version number of instance 3406
Root @ localhost [tempdb]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.6.12-log |
+ --------------- + ------------ +

-- Create a database
Root @ localhost [(none)]> create database tempdb;
Query OK, 1 row affected (0.00 sec)

-- Author: Leshami
-- Blog: http://blog.csdn.net/leshami

Root @ localhost [(none)]> use tempdb
Database changed

-- Create a table for access
Root @ localhost [tempdb]> create table tb_engine
-> Select engine, support, comment from information_schema.engines;
Query OK, 9 rows affected (0.10 sec)
Records: 9 Duplicates: 0 Warnings: 0

-- The SQL statement used to extract a table is used to create a FEDERATED storage engine table.
Root @ localhost [tempdb]> show create table tb_engine \ G
* *************************** 1. row ***************************
Table: tb_engine
Create Table: create table 'tb _ engine '(
'Engine 'varchar (64) not null default '',
'Support 'varchar (8) not null default '',
'Comment' varchar (80) not null default''
) ENGINE = InnoDB default charset = utf8

-- Create an account for remote access
Root @ localhost [tempdb]> grant all privileges on tempdb. * to 'remote _ user' @ '192. 168.1.131 'identified by 'xxx ';
Query OK, 0 rows affected (0.00 sec)

Root @ localhost [tempdb]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4. Demo FEDERATED storage engine cross-instance access

[Root @ rhel64a ~] # Mysql-uroot-pxxx

Root @ localhost [(none)]> show variables like 'version ';
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Version | 5.6.21 |
+ --------------- + -------- +

# Check whether the FEDERATED engine is supported
Root @ localhost [(none)]> select * from information_schema.engines where engine = 'federated ';
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +
| FEDERATED | NO | Federated MySQL storage engine | NULL |
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +

Root @ localhost [(none)]> exit
[Root @ rhel64a ~] # Service mysql stop
Shutting down MySQL... [OK]
# Enable the FEDERATED Engine
[Root @ rhel64a ~] # Vi/etc/my. cnf
[Root @ rhel64a ~] # Tail-7/etc/my. cnf
[Mysqld]
Socket =/tmp/mysql3306.sock
Port = 3306
Pid-file =/var/lib/mysql/my3306.pid
User = mysql
Server-id = 3306/
Federated # Add this option
[Root @ rhel64a ~] # Service mysql start
Starting MySQL. [OK]
[Root @ rhel64a ~] # Mysql-uroot-pxxx
Root @ localhost [(none)]> select * from information_schema.engines where engine = 'federated ';
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +
| FEDERATED | YES | Federated MySQL storage engine | NO |
+ ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ +

Root @ localhost [(none)]> use test

-- Create a FEDERATED engine-based table federated_engine
Root @ localhost [test]> create table 'federated _ engine '(
-> 'Engine' varchar (64) not null default '',
-> 'Support' varchar (8) not null default '',
-> 'Comment' varchar (80) not null default''
->) ENGINE = federated default charset = utf8
-> CONNECTION = 'mysql: // remote_user: xxx@192.168.1.131: 3406/tempdb/tb_engine ';
Query OK, 0 rows affected (0.00 sec)

-- The following figure shows the created table file.
Root @ localhost [test]> system ls-hltr/var/lib/mysql/test
Total 12 K
-Rw ---- 1 mysql 8.5 K Oct 24 federated_engine.frm

-- Query the federated_engine table
Root @ localhost [test]> select * from federated_engine limit 2;
+ ------------ + --------- + ----------------------------------------- +
| Engine | support | comment |
+ ------------ + --------- + ----------------------------------------- +
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| CSV | YES | CSV storage engine |
+ ------------ + --------- + ----------------------------------------- +

-- Update the federated_engine table
Root @ localhost [test]> update federated_engine set support = 'no' where engine = 'csv ';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- View the updated results
Root @ localhost [test]> select * from federated_engine where engine = 'csv ';
+ -------- + --------- + -------------------- +
| Engine | support | comment |
+ -------- + --------- + -------------------- +
| CSV | NO | CSV storage engine |
+ -------- + --------- + -------------------- +

5. How to Create a FEDERATED engine table

Scheme: // user_name [: password] @ host_name [: port_num]/db_name/tbl_name
Scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
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.
Password: (Optional) The corresponding password for user_name.
Host_name: The host name or IP address of the remote server.
Port_num: (Optional) The port number for the remote server. The default is 3306.
Db_name: The name of the database holding the remote table.
Tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.
Sample link:
CONNECTION = 'mysql: // username: password @ hostname: port/database/tablename'
CONNECTION = 'mysql: // username @ hostname/database/tablename'
CONNECTION = 'mysql: // username: password @ hostname/database/tablename'