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

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

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

Copy codeThe Code is as follows:
-- 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

Copy codeThe Code is as follows:
[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

Copy codeThe Code is as follows:
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'

Transfer Between mysql Databases: How to import one mysql database server to another mysql server?

1. Back up data using tools. Many tools. You should know your level.
2. mysqldump in liunx
3. there is another way to copy the data file ,. in windows, it is in the data folder where mysql is installed. the folder is created by database name .; it is similar in linux. copy it directly. it is much faster than importing and exporting data. I have tried

In php, access is denied when the mysql database is opened. wamp is used. phpMyAdmin tries to connect to the MySQL server, but the server rejects access.

Start the mysql service in the service, right-click my computer, choose "manage"> "services and applications"> "find mysql"> "right-click" properties ">" start"

Can you go in to the command line? Your password is incorrect.

Regardless of the path used for remote access, the system may be deemed to have firewall restrictions. The solution is to add a startup parameter in the [mysqld] section of my. cnf.

Skip-name-resolve