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

As the application complexity increases, database segmentation is constantly refined, resulting in complicated and messy database applications in applications. Most programmers may encounter this situation, and the application needs to connect to multiple database servers for corresponding operations. Over time, the connection logic of too many database servers appears in the program, which brings a great deal of work to the maintenance and expansion of the program.

As a result, some Distributed Database proxy layers emerged, such as the common MySQL proxy layer:

Mysql proxy: mainly implements read/write splitting and load balancing.

MySQL Amoeba: led by Chen siru, the development function is relatively complete, with in-depth application value.

HiveDB: HiveDB is an open-source framework used to split mysql Databases horizontally. It builds a high-performance and scalable mysql-based system, but currently only supports Java clients.

I think mysql proxy and MySQL Amoeba are excellent practical values and should be further understood.

The federated engine described in this article is a special MySQL engine that can map local data tables to remote MySQL Data Tables, the topology is shown below:

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:

1. log on to the remote MySQL database as the root user and create an appropriate access account.

Grant all on DB1. * to 'federated '@' % 'identified by 'federated ';

Flush privileges;

2. Find the CREATE command for the corresponding object table in remote MySQL. (if it is a new table, create a data table first and then execute this command)

Assume that the database name DB1 and table name tag are available on the remote mysql database. Run the following command to find the structure of the remote table:

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

3. 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. a tcp persistent connection is established between a local virtual table and a remote object table, and is used 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.

Notes:

1. modify the structure of the local virtual table without modifying the structure of the remote table.

2. The truncate command clears the remote table data.

3. The drop command only deletes virtual tables and does not delete remote tables.

4. The alter table command is not supported.

The biggest disadvantage of federated:

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, 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

2. 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.

3. For queries similar to where name like "str %" limit 1, even if an index is created on the name column, the query is slow because

The federated engine reads all records that meet the conditions to the current database and then performs limit processing.

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