MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问

-- 此演示中远程服务器与本地服务器为同一服务器上的多版本多实例 
-- 假定远程服务为：5.6.12(实例3406) 
-- 假定本地服务器：5.6.21(实例3306)    
-- 基于实例3306创建FEDERATED存储引擎表test.federated_engine以到达访问实例3406数据库tempdb.tb_engine的目的 
 
[root@rhel64a ~]# cat /etc/issue 
Red Hat Enterprise Linux Server release 6.4 (Santiago)  
 
--启动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  | 
+---------------+-------+ 
 
--实例3406的版本号 
root@localhost[tempdb]> show variables like 'version'; 
+---------------+------------+ 
| Variable_name | Value      | 
+---------------+------------+ 
| version       | 5.6.12-log | 
+---------------+------------+ 
 
--创建数据库 
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 
 
--创建用于访问的表 
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 
 
--提取表的SQL语句用于创建为FEDERATED存储引擎表 
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 
 
--创建用于远程访问的账户 
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) 

[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 
 
-- 创建基于FEDERATED引擎的表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) 
 
-- 下面是创建后表格式文件 
root@localhost[test]> system ls -hltr /var/lib/mysql/test 
total 12K 
-rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm 
 
--查询表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                    | 
+------------+---------+---------------------------------------+ 
 
--更新表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 
 
--查看更新后的结果 
root@localhost[test]> select * from federated_engine where engine='CSV'; 
+--------+---------+--------------------+ 
| engine | support | comment            | 
+--------+---------+--------------------+ 
| CSV    | NO      | CSV storage engine | 
+--------+---------+--------------------+