mysql 多服务器 查询_mysql 跨服务器查询

二个mysql分布二台机器上，要能在二个mysql间进行联表查询。方案有多种，常见的就是二台mysql做个主从，这样可以在一台机器进行联表查询了。

还有一种更快的方法

1，看一下FEDERATED是否开启

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

2，开启FEDERATED引擎

# vim /etc/my.cnf

[mysqld]

federated //增加该行

# systemctl restart mysqld //重启

mysql> show engines;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 13

Current database: crm

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (1.27 sec)

3，创建表

MariaDB [test]> CREATE TABLE `e_industry` (

-> `id` int(10) NOT NULL AUTO_INCREMENT,

-> `industry` varchar(64) NOT NULL,

-> `id_order` int(10) NOT NULL DEFAULT '10000',

-> `is_deleted` int(10) NOT NULL DEFAULT '0',

-> PRIMARY KEY (`id`)

-> ) ENGINE=FEDERATED CHARSET=utf8 connection = 'mysql://report_select:123456@10.0.10.88:3306/crm/e_industry';

Query OK, 0 rows affected (0.06 sec)

MariaDB [test]> select * from e_industry;

+----+----------+----------+------------+

| id | industry | id_order | is_deleted |

+----+----------+----------+------------+

| 1 | 网服 | 0 | 0 |

| 2 | 电商 | 0 | 0 |

| 4 | 金融 | 0 | 0 |

| 6 | 游戏 | 0 | 0 |

| 8 | 广告 | 0 | 0 |

| 10 | 其它 | 0 | 0 |

+----+----------+----------+------------+

6 rows in set (0.00 sec)

注意：这种映射只是表层面，mysql没有库层面的映射。这种映射效率比较低下，应急可以用，真正的做法，还是做主从。