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

Using the mysql federated engine to build the MySQL distributed database access layer the federated described in this article is a special MySQL engine that can be used to 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. Create a Federated table: log on to MySQL remotely as the 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 KE Y (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"; then, the federated virtual table is created. In fact, only the table definition file is created in the local MySQL database, but no data file is created. 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 the field in the virtual table is not indexed and the object 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.