Use the mysqlfederated engine to build the MySQL distributed database access layer _ MySQL

Use the mysqlfederated engine to build the MySQL distributed database access layer bitsCN.com

Build a mysql distributed database access layer using the MySQL federated Engine

The federated Engine described in this article is a special MySQL engine. it can be used to map local data tables to remote MySQL data tables, so that you can solve a wide range of cross-machine database connection problems in applications, the topology is as follows:

In this way, a unified data access portal can be constructed, which greatly improves the maintainability of the entire database system. The Federated Engine is table-level-based. it can only define local data tables as Federated engines and map them to the remote real-world surface. Therefore, it cannot implement overall database-level-based ING.

In this article, the database access entry server that enables the Federated Engine is called a local database, and the remote data table corresponding to the local data table is called an entity table.

The Federated engine must be enabled for the local database, and remote data tables do not need to be supported by the Federated Engine. The Federated Engine table uses the standard MySQL client protocol to establish a TCP connection with a remote database.

The process of creating a Federated table:

Log on to remote MySQL as a root user and create a suitable access account grant all on DB1. * to 'Federated '@' % 'identified by 'Federated'; flush privileges;

In remote MySQL, find the creation command for the corresponding object table (if it is a new table, create a data table first, and then execute this command). assume that database name DB1 and table name tag are available on remote mysql, run the following command to find the remote table structure: show create TABLE DB1.tag output: CREATE table tag (id int (10) unsigned not null AUTO_INCREMENT, name varchar (128) not null, frequency int (10) unsigned not null default '1', primary key (id) ENGINE = MyISAM AUTO_INCREMENT = 6 default charset = utf8

Suppose we want to map the remote DB1.tag to the local DB. TableA table. So we should keep the structure of the local virtual table consistent with that of the remote object table (the structure can be different, but it may cause usage and management troubles ). Create a local virtual table based on the remote object table creation command. The table creation option varies depending on the structure ):

Log on to the local Mysql server and create the corresponding database and TABLE: CREATE database DB; use DB; create TABLE TableA (id int (10) unsigned not null AUTO_INCREMENT, name varchar (128) not null, frequency int (10) unsigned not null default '1', primary key (id) ENGINE = federated connection = "mysql: // federated: federated@127.0.0.1: 3306/DB1/tag ";

In this case, the federated virtual table is created. In fact, the local MySQL only creates the table definition file without the data file. All data modifications to the local virtual table will be sent to the remote machine for execution.

The local virtual table name is different from the remote table name.

Some additional features of this engine have been tested: 1. TCP persistent connections are established between the local virtual table and the remote object table, and are exploited by multiple clients. Therefore, you do not have to worry about the network overhead caused by frequent connections. 2. after the network connection between the virtual table and the remote entity table is disconnected, when querying the virtual table, it tries to reconnect to the remote entity table, so we don't have to worry about the permanent interruption caused by network disconnection. 3. if no time is available for any operation on the local virtual table, the connection between the virtual table and the Object table will be automatically disconnected after wait_timeout seconds on the remote host. when querying the virtual table, the connection is established again.

Some notes: 1. modify the structure of the local virtual table without modifying the structure of the remote table. the truncate command clears the remote table data. the drop command only deletes virtual tables and does not delete remote tables. the alter table command is not supported. The biggest disadvantage of federated is: 1. select count (*), select * from limit M, N, and other statements have low execution efficiency and a large amount of data has serious problems. However, queries by primary key or index column are very fast, for example, the query below is very slow (assuming the id is the primary index) select id from db. tablea where id> 100 limit 10; the following query is very fast: select id from db. tablea where id> 100 and id <150

If fields in the virtual table are not indexed and the physical table is indexed, the performance is also quite poor. However, when an index is created for a virtual table, the performance returns to normal.

For queries similar to where name like "str %" limit 1, even if an index is created on the name column, the query will be slow, it is because the federated Engine will read all the records that meet the conditions to the current, and then perform limit processing.

These problems have seriously affected federated's application in the actual environment.

BitsCN.com