Mysql uses the federated engine for remote database access (cross-network operation of tables in two databases at the same time)

Mysql uses the federated engine for remote database access (cross-network operation of tables in two databases at the same time)
Problem:

Assume that I need to access table 2 in the database of IP2 on database1 on IP1.

Method:

Here, as a local database, you must enable the federated engine to access the tables in the remote database. The specific enabling method is as follows:

Enable FEDERATED engine for local IP1 database DATABASE1

Run the MYSQL command line to check whether the FEDERATED engine is enabled. The FEDERATED engine is disabled by default.

> Show engines;

We can see that the federated engine is not enabled for the local database.

2. If not enabled
Configure my. cnf
[Mysqld]
Feterated

3. Restart the MYSQL server

Enable remote connection for the remote IP2 database database2

After it is enabled, the local database database1 has the permission to access the remote database2

2) create a remote login user and authorize

USE mysql;SELECT host,user,PASSWORD from user;GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY "yourpassward";FLUSH PRIVILEGES;USE mysql;SELECT host,user,PASSWORD from user;

CODE: [COPY]
> Grant all PRIVILEGES on discuz. * to ted @ '192. 123.123.123 'identified by '20140901 ';
The preceding statement grants all permissions of the discuz database to the ted user, allows the ted user to remotely log on to the IP address 123.123.123.123, and sets the ted user password to 123456.
The following describes all the parameters one by one:
All PRIVILEGES indicates that all permissions are granted to the specified user. Here, you can also replace it with a specific permission, such as select, insert, update, delete, create, and drop, separate specific permissions with commas.
Discuz. * Indicates the table to which the preceding Permission applies. discuz indicates the database, and * indicates all the tables. Therefore, we can infer that: authorize "*. * ", authorize all tables of a database to" database name. * ", grant permissions to a table in a database as" database name. table name ".
Ted indicates the user you want to authorize. This user can be an existing user or a non-existing user. For ease of use, you 'd better use the root user, because others will also use the password remotely, and also use the root user password, because others also use the password. (It is very important to ensure that everyone accesses the same database with the same login information)
123.123.123.123 indicates the IP address that allows remote connection. If you want to restrict the IP address, set it to "%.
123456 is the user's password.
After the preceding statement is executed, the following statement can take effect immediately.
CODE: [COPY]
> Flush privileges;

View the updated access permissions of the remote database:

We can see that a virtual user scp_pm_154_54 can remotely access database2 on ip2. (this scp_pm_154_54 is just a virtual user and is only used for remote connection ), next, let's try to use this account to access this database on another IP3.

Create a remote database table in the local database database1

The table structure must be the same, so you can first

In database2, find the statement used to create table 2.

Show create table database2.table2

Obtain the table creation statement.

Create table 'e _ hostcomputer '('id' bigint (20) not null comment 'id', 'code' varchar (30) default null comment 'code ', 'name' varchar (50) default null comment' name', 'IP' varchar (15) default null comment 'IP address', 'status' char (1) default null comment 'status', 'crtr' varchar (50) default null comment' creators ', 'crtdt 'timestamp null default null comment' creation time ', 'updr' varchar (50) default null comment' modifier ', 'upddt' timestamp null default null comment' modification time', primary key ('id ')) ENGINE = InnoDB default charset = utf8 COMMENT = 'host machine table'

Table 2 in database database2 on remote IP2

Create the same table locally (Remote table)

 

Create table 'e _ hostcomputer_link39 '('id' bigint (20) not null comment 'id', 'code' varchar (30) default null comment' Encoding ', 'name' varchar (50) default null comment' name', 'IP' varchar (15) default null comment 'IP address', 'status' char (1) default null comment 'status', 'crtr' varchar (50) default null comment' creators ', 'crtdt 'timestamp null default null comment' creation time ', 'updr' varchar (50) default null comment' modifier ', 'upddt' timestamp null default null comment' modification time', primary key ('id ')) ENGINE = federated connection = "mysql: // scp_pm_154_54: scp_pm_154_54@112.219.11.139: 13306/rzem/e_hostcomputer ";

You can see the content in the remote table locally.

 

 

Modify local remote table

You can see remotely that the remote connection has also been modified.