mysql有dblink_mysql也有dblink

mysql也有dblink哟，小姐姐，你没有听过吧，看小哥哥给你测试。

先看看mysql有哪些引擎，凯美瑞是双擎混动，mysql是10个引擎，并且还可以加

先看看mysql版本

mysql> select version();

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

| version() |

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

| 5.7.16 |

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

1 row in set (0.00 sec)

mysql> show engines;

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

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

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

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

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

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

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

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

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

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

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

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

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

这里我们用federated就是我们说的dblink类似的功能。远程映射。当前不支持。需要我们修改配置文件。

在/etc/my.cnf 添加

[mysqld]

federated

如下图

[root@slave02 ~]# more /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[mysqld]

federated

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M重启

[root@slave02 ~]# /etc/init.d/mysqld stop

Shutting down MySQL...... [ OK ]

[root@slave02 ~]# /etc/init.d/mysqld start

Starting MySQL.. [ OK ]

查看是否成功， YES

mysql> show engines;

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

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

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

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

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

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

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

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

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

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

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

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

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+下面看看，144主机没有ml_1， 然后建立142的映射(类似oracle  dblink， 又类似sql server的透明网关)。

mysql> select count(*) from ml_1;

ERROR 1146 (42S02): Table 'iot.ml_1' doesn't exist

mysql>

mysql> create table ml_1(

-> id int

-> ,age int

-> ,school varchar(10)

-> ,city varchar(10)

-> ,memo varchar(50)

-> ) ENGINE=FEDERATED

-> CONNECTION='mysql://root:mengliang@192.168.2.142:3306/job_admin/ml_1';

Query OK, 0 rows affected (0.12 sec)

mysql> select * from ml_1;

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

| id | age | school | city | memo |

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

| 7 | 31 | 本科 | 重庆 | 孟梁,数据库架构师,项目经理,团队管理2年,工作9年 |

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

1 row in set (0.01 sec)

mysql>

--end--

qq:271866215

tel:18983846756