Mysql allows remote connection method _mysql

I. Overview

This article mainly introduces the establishment of remote connection table through federated storage Engine.

Test environment: MySQL 5.6.21

Second, the steps

Turn on the Federated storage Engine

First check to see if the Federated storage Engine storage engine is enabled

Show engines; 

The default MySQL is not open federated storage engine, I am here because it has been configured.

Opening the federated storage Engine only requires adding ' federated ' to the my.cnf file.

Create a Remote connection table

MySQL configuration remote connection must be created locally federated storage Engine table, configure remote connection parameters, the table created locally must be consistent with the definition of remote table, here I take a local another case database for testing, the effect and remote is the same.

Copy Code code as follows:

Show CREATE TABLE Sakila.actor;

To create a Remote Storage engine table

CREATE TABLE federated_actor (
  ' actor_id ' smallint (5) unsigned not NULL auto_increment,
  ' first_name ' varchar (45  Not NULL,
  ' last_name ' varchar is not NULL,
  ' last_update ' timestamp not null DEFAULT current_timestamp on update Current_timestamp,
  PRIMARY key (' actor_id '),
  key ' Idx_actor_last_name ' (' last_name ')
 ENGINE =federated connection= ' Mysql://root:123456@127.0.0.1:3306/sakila/actor '; 

Note: The table definitions created locally are consistent with the remote table, and the local storage engine chooses: ENGINE =federated

The general form of the connection string in the connection option is as follows: scheme://user_name[:p assword] @host_name [:p ort_num]/db_name/tbl_name

There are also some examples of connection strings:

connection= ' Mysql://username:password@hostname:port/database/tablename '
connection= ' Mysql://username@hostname/database/tablename '
connection= ' Mysql://username:password@hostname/database/tablename '
Note: There are security issues with configuring passwords as plain text, and running show create Table,show table status is visible

Copy Code code as follows:

SELECT * from Test.federated_actor;

Update the local surface

Use test;

Update Federated_actor
set last_name= ' GUINESS1 '
where actor_id=1;
SELECT * from Test.federated_actor;

SELECT * from Sakila.actor;

Local and remote data has been changed for local update operations.

Add Field

Copy Code code as follows:

ALTER TABLE federated_actor add column idtest INT DEFAULT 0;

Error code:1031. Table storage engine for ' federated_actor ' doesn ' t have this option

The federated storage Engine does not support ALTER TABLE operations

Delete Table

Copy Code code as follows:

drop table test.federated_actor;

Deleting a local table has no effect on the remote table

Third, summary

The Federated storage Engine is the Dblink feature that MySQL uses to solve types of other database systems, but the configuration federated is relatively cumbersome and requires a local creation of tables, as well as security factors, which generally avoid a lot of functionality, I hope MySQL will improve in the future version.

The above is the entire content of this article, I hope to help you learn.