Method for mysql to allow remote connection _ MySQL

This article mainly introduces how mysql allows remote connection. the content is very detailed. For more information, see I. Overview

This article describes how to create a remote connection table through the federated storage engine.

Test Environment: mysql 5.6.21

Step 2

Enable federated storage engine

Check whether the federated Storage Engine is enabled

SHOW ENGINES ;

By default, the federated Storage Engine is not enabled in mysql. I have already configured it here.

To enable the federated storage engine, you only need to add 'Federated 'to the my. cnf file.

The code is as follows:

show create table sakila.actor;

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(45) 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 locally created table must be consistent with the remote table. select ENGINE = FEDERATED as the local storage ENGINE.

The CONNECTION string in the CONNECTION option is generally in the following format:

scheme://user_name[:password]@host_name[:port_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: If the password is configured as plain text, security issues may occur. run show create table and show table status.

The code is as follows:

SELECT * FROM test.federated_actor;

Update local table

USE test;update federated_actorset last_name='GUINESS1'where actor_id=1;SELECT * FROM test.federated_actor;SELECT * FROM sakila.actor;

Update the data locally and remotely.

Add Field

The code is 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

Federated storage engine does not support alter table operations

Delete table

The code is as follows:

drop table test.federated_actor;

Deleting a local table does not affect the remote table.

III. Summary

The federated storage engine is used by mysql to solve the dblink function of other database systems. However, configuring federated requires you to create a table locally and ensure security, in general, there are still many features to avoid, and we hope mysql can be improved in later versions.

The above is the method for mysql to allow remote connection _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!