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:[email protected]: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没有库层面的映射。这种映射效率比较低下，应急可以用，真正的做法，还是做主从。

喜欢 (0)赏

[搞代码]

分享 (0)