Mysql allows remote connection,

Mysql allows remote connection,

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.

Create a remote connection table

To configure remote connection for mysql, you must create a federated storage engine table locally and configure remote connection parameters. The locally created table must be consistent with the definition of the remote table, here I will use another local case database for testing, and the effect is the same as that of remote applications.

Copy codeThe 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.

Copy codeThe 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

Copy codeThe 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

Copy codeThe 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 all the content of this article, hoping to help you learn.

Articles you may be interested in:

• Method set for mysql remote database connection
• How to enable MySQL database remote connection
• Slow remote connection to mysql (mysql_connect slow connection)
• MYSQL cannot be remotely connected from a solution (s not allowed to connect to this MySQL server)
• How to remotely connect to mysql
• How to configure mysql to allow remote connection
• How does navicat fail to remotely connect to mysql?
• How to solve the problem of MySQL remote connection loss (Lost connection to MySQL server)