Use the FREDATED engine for cross-instance access, and the fredated engine for cross-instance access

Use the FREDATED engine for cross-instance access, and the fredated engine for cross-instance access

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 multi-version and multi-instance on the same server -- assume that the remote service is 5.6.12 (instance 3406) -- assume that the local server is 5.6.21 (instance 3306) -- create the FEDERATED storage engine Table test Based on instance 3306. federated_engine is used to access the instance's database tempdb 3406. tb_engine objective [root @ rhel64a ~] # Cat/etc/issueRed Hat Enterprise Linux Server release 6.4 (Santiago) -- start the 3406 instance [root @ rhel64a ~] #/U01/app/mysql/bin/mysqld_multi start 3406 [root @ rhel64a ~] # Mysql-uroot-pxxx-P3406 -- protocol = tcproot @ localhost [(none)]> show variables like 'server _ id '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | server_id | 3406 | + --------------- + ------- + -- version of instance 3406 root @ localhost [tempdb]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + -- create database root @ localhost [(none)]> create database tempdb; Query OK, 1 row affected (0.00 sec) -- Author: Leshami -- Blog: http://blog.csdn.net/leshamiroot@localhost[ (none)]> use tempdbDatabase changed -- create the table 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 used to extract the table is used to create the FEDERATED storage engine table root @ localhost [tempdb]> show create table tb_engine \ G ************* * ************** 1. row ************************** Table: tb_engineCreate 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-pxxxroot @ localhost [(none)]> show variables like 'version '; + --------------- + -------- + | Variable_name | Value | + --------------- + -------- + | version | 5.6.21 | + --------------- + -------- + # Check whether the FEDERATED engine root @ localhost [(none)]> select * from information_schema.engines where engine = 'federated '; + ----------- + --------- + -------------------------------- + ------------ + ------ + ------------ + | ENG INE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | + ----------- + --------- + hour + -------------- + ------ + ------------ + | FEDERATED | NO | Federated MySQL storage engine | NULL | + ----------- + --------- + -------------------------------- + -------------- + ------ + ------------ + root @ localhost [(none)]> exit [root @ rhel64a ~] # Service mysql stopShutting down MySQL... [OK] # configure to enable the FEDERATED engine [root @ rhel64a ~] # Vi/etc/my. cnf [root @ rhel64a ~] # Tail-7/etc/my. cnf [mysqld] socket =/tmp/mysql3306.sockport = 3306pid-file =/var/lib/mysql/my3306.piduser = mysqlserver-id = 3306/federated # Add this option [root @ rhel64a ~] # Service mysql startStarting MySQL. [OK] [root @ rhel64a ~] # Mysql-uroot-pxxxroot @ localhost [(none)]> select * from information_schema.engines where engine = 'federated '; + ----------- + --------- + hour + -------------- + ------ + ------------ + | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | + ----------- + --------- + hour + -------------- + | FEDERATED | YES | Federated MySQL storage engine | NO | + ------------- + --------- + -------------------------------- + ------------ + root @ localhost [(none)]> use test -- CREATE a FEDERATED engine-based TABLE federated_engineroot @ 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 table file root @ localhost [test]> system ls-hltr/var/lib/mysql/testtotal 12K-rw-rw ---- 1 mysql 8.5 K Oct 24 federated_engine.frm -- query the table federated_engineroot @ localhost [test]> select * from federated_engine limit 2; + ------------ + --------- + region + | engine | support | comment | + ------------ + --------- + region + | MRG_MYISAM | YES | Collection of identical MyISAM tables | CSV | YES | CSV storage engine | + ------------ + --------- + ------------------------------------- + -- update the federated_engineroot @ 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 result 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'

 

6. related references
MySQL multi-instance configuration (1)
MySQL multi-instance configuration (2)
Install the full version of MySQL source code in Linux
MySQL user and permission management