MySQL uses the federated engine to implement remote access to the database (two simultaneous operations across the network tables in a database)

Problem:

This assumes that I need to access the Table2 table in the IP2 database on the database1 on IP1

Method:

Here database needs to open the federated engine in order to be able to access the table in the remote DB as the local data base, the method is as follows:

Local IP1 Database DATABASE1 open Federated engine

http://blog.csdn.net/bravezhe/article/details/8269648

MySQL command line to see if the federated engine is turned on, default is not to turn on
>show engines;

Can be seen in the local database does not open the federated engine

2. If it is not turned on
Configure MY.CNF
[Mysqld]
feterated


3. Restart the MySQL server

Remote IP2 database DATABASE2 Open remote connection

When on, the local database database1 has permission to access the remote DATABASE2

Http://blog.chinaunix.net/uid-23215128-id-2951624.html

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 the discuz.* to [e-mail protected] ' 123.123.123.123 ' identified by ' 123456 ';
The above statement means that all permissions to the Discuz database are granted to the TED user, allowing the TED user to remotely log on to the 123.123.123.123 IP and set the TED user's password to 123456.
All parameters are analyzed below:
All privileges means giving all permissions to the specified user, which can also be replaced by assigning a specific permission, such as: Select,insert,update,delete,create,drop, and so on, with the "," half-width comma separated by the specific permissions.
Discuz.* means that the above permissions are for which table, discuz refers to the database, followed by the * for all tables, it can be inferred that: for all of the database is authorized as "*. *" For all tables, the entire table for a database is authorized as "database name. *", A table for a database is authorized as the database name. Table name.
Ted indicates which user you want to authorize, and this user can be either an existing user or a non-existent user. For the sake of ease of use you are best with the root user, because others will use the remote, password also need to use the root user's password, because others are so used. (It is important to ensure that everyone uses the same login information to access the same database.)
123.123.123.123 represents the IP address that allows remote connections, or "%" if you want to limit the IP of the link.
123456 is the user's password.
After executing the above statement, execute the following statement before it can take effect immediately.
CODE: [COPY]
> flush Privileges;

After you view the update of access permissions for the remote database:

You can see that there is already a virtual user scp_pm_154_54 can remotely access the IP2 on the Database2 (this scp_pm_154_54 is just a virtual user, only for remote connection use), Try this account below. Can I access this database on another IP3?

To create a remote database table in the local database Database1

Requires the table structure to be identical, so you can first

Find the statement that establishes the table table2 inside the Database2

SHOW CREATE TABLE Database2.table2

Get a statement of the build table

CREATE TABLE ' E_hostcomputer ' (
' ID ' bigint (a) not NULL COMMENT ' id ',
' Code ' varchar (+) DEFAULT NULL COMMENT ' encoded ',
' Name ' varchar (the DEFAULT NULL COMMENT ' names '),
' IP ' varchar (+) DEFAULT NULL COMMENT ' IP address ',
' Status ' char (1) DEFAULT NULL COMMENT ' state ',
' CRTR ' varchar DEFAULT NULL COMMENT ' creator ',
' CRTDT ' timestamp null DEFAULT null COMMENT ' creation time ',
' updr ' varchar DEFAULT NULL COMMENT ' modifier ',
' UPDDT ' timestamp null DEFAULT null COMMENT ' modified time ',
PRIMARY KEY (' ID ')
) Engine=innodb DEFAULT Charset=utf8 comment= ' upper machine table '

Table Table2 in database database2 on remote IP2

Establish the same table locally (remote table)

CREATE TABLE ' e_hostcomputer_link39 ' (  ' id ' bigint () NOT NULL COMMENT ' id ',  ' CODE ' varchar () DEFAULT NULL COMM ENT ' code ',  ' name ' varchar ($) default null COMMENT ' name ',  ' IP ' varchar (+) ' Default null COMMENT ' IP address ',  ' STATUS  ' char (1) default null COMMENT ' state ',  ' CRTR ' varchar () default null COMMENT ' creator ',  ' crtdt ' timestamp NULL default Null COMMENT ' creation time ',  ' updr ' varchar ($) default null COMMENT ' modifier ',  ' UPDDT ' timestamp null default NULL COMMENT ' Modify Time ',  PRIMARY KEY (' ID ')) engine=federated connection= "Mysql://scp_pm_154_54:[email protected]:13306/rzem/e_ Hostcomputer ";

You can see the contents of the remote table locally.

Modifying a local remote table

Can be remote to see the remote has also been modified

MySQL uses the federated engine to implement remote access to the database (two simultaneous operations across the network tables in a database)