Cross-database server, cross-instance access is a more common way of access, in Oracle can be achieved through the way of DB link. For MySQL, there is a federated storage engine that corresponds to it. It is also a way to access data on a remote server by creating a link form. This article briefly describes the federated storage Engine and demonstrates an example of cross instance access based on the Federated storage Engine.
1. Description of federated Storage Engine
Federated storage Engine enables remote Access database without replication or cluster technology
When creating a table based on the Federated storage Engine, the server creates only one table definition file in the database directory, that is, the. frm file that starts with the table name.
Federated Storage Engine Table No data is stored locally, that is, there is no. myd file
The operation of the table on the remote server is the same as the local table operation, where only the data resides on the remote server
The basic process is as follows:
2. Install and enable federated storage Engine
The source code installs the MySQL to use the Dwith_federated_storage_engine to configure
The RPM installation method is installed by default, and you only need to enable this feature
3. Prepare remote server environment
Copy Code code as follows:
--Multi-version multiple instances of the remote server and the local server on the same server in this demo
--Assuming the remote service is: 5.6.12 (instance 3406)
--Assuming local server: 5.6.21 (instance 3306)
--based on instance 3306 to create federated Storage engine table Test.federated_engine to access the instance 3406 database Tempdb.tb_engine
[root@rhel64a ~]# Cat/etc/issue
Red Hat Enterprise Linux Server release 6.4 (Santiago)
--Start the instance of 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 as
-> 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 that extracts the table is used to create the table for the Federated storage Engine
Root@localhost[tempdb]> Show CREATE TABLE Tb_engine \g
1. Row ***************************
Table:tb_engine
Create table:create Table ' Tb_engine ' (
' Engine ' varchar not NULL DEFAULT ',
' Support ' varchar (8) Not NULL DEFAULT ',
' Comment ' varchar not NULL DEFAULT '
) Engine=innodb DEFAULT Charset=utf8
--Create an account for remote access
Root@localhost[tempdb]> grant all privileges in 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
Copy Code code as follows:
[root@rhel64a ~]# Mysql-uroot-pxxx
root@localhost[(none)]> show variables like ' version ';
+---------------+--------+
| variable_name | Value |
+---------------+--------+
| Version | 5.6.21 |
+---------------+--------+
#查看是否支持FEDERATED引擎
root@localhost[(None)]> select * from Information_schema.engines where engine= ' federated ';
+-----------+---------+--------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | Transactions | XA | savepoints |
+-----------+---------+--------------------------------+--------------+------+------------+
| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |
+-----------+---------+--------------------------------+--------------+------+------------+
root@localhost[(none)]> exit
[root@rhel64a ~]# service MySQL Stop
Shutting down MySQL. [OK]
#配置启用FEDERATED引擎
[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 #添加该选项
[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 | NO | NO |
+-----------+---------+--------------------------------+--------------+------+------------+
root@localhost[(none)]> use test
--Create a table based on the Federated engine Federated_engine
root@localhost[test]> CREATE TABLE ' Federated_engine ' (
-> ' engine ' varchar () not NULL DEFAULT ',
-> ' support ' varchar (8) Not NULL DEFAULT ',
-> ' comment ' varchar 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 is the creation of a tabular file
root@localhost[test]> system Ls-hltr/var/lib/mysql/test
Total 12K
-RW-RW----1 mysql mysql 8.5K Oct 08:22 federated_engine.frm
--Query Table Federated_engine
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 table Federated_engine
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, the Creation Federated engine table link Way
Copy Code code as follows:
scheme://user_name[:p assword] @host_name [:p ort_num]/db_name/tbl_name
Scheme:a recognized connection protocol. Only MySQL are 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 to privileges the Perform 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 does not have to match.
Link Sample Sample:
connection= ' Mysql://username:password@hostname:port/database/tablename '
connection= ' Mysql://username@hostname/database/tablename '
connection= ' Mysql://username:password@hostname/database/tablename '
