MySQL remote connection and mysql connection

MySQL remote connection and mysql connection

Overview

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

Test environment: mysql 5.6.21

 

Procedure

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.

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.

 

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

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

Drop table test. federated_actor;

Deleting a local table does not affect the remote table.

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.

 

If the article is helpful to you, please recommend it. Thank you !!!

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly provide the link at the beginning of the article. Otherwise, you will be held accountable. Welcome to discussion

 

--- Restore content end ---