MySQL Federated host a multi-engine equipment

2011-09-06  来源:本站原创  分类:Database  人气:124 

Occasionally we need to do.

If a host on the copy number of the main library, can be very simple: the standby database host to start multiple MySQL instances (mysqld_muilt), each instance uses a port, copy more than one main library on it. But if you want to copy over the data in the same instance, things are complicated.

1 The problem and the open source community needs to try: the main library are copied to more than a standby database, the data are copied in one instance, so the application layer of transparent. Currently, MySQL does not provide such functionality itself (considered, but has not been achieved).

1.1 open source community, there are many programs and ideas, such as Tungsten replicator: can support heterogeneous data replication, JAVA implementation, data is large, poor performance, and MySQL version updated faster than the software support is slow;

Another example is P. Linux 1.2 an attempt: to pull more through the main library on the Binlog applications in a single standby database on the way, there is no invasion of MySQL, is also relatively simple to achieve.

1.3 High Performance MySQL is also multi-level replication can be achieved: for example there is the main repository DB1, DB2, all need to be copied to S1, the first configuration all the data D1, D2 copy, and then replicate from S1 D2 on all the data on it. Finishing on the note D2 log-slave-update will need to open, in order to reduce the pressure of D2, DB tables can be copied on all use Blackhole engine.

1.4 In addition, there are some less mature Patch achieve.

2. MySQL Federated engine "soft link" feature
Federated in the local database engine can create a remote table in the "soft link." In this way, access to remote data access local data table like the table. (This remote can be a different instance on different hosts)

3 Use Federated tables to achieve a preparation such as multi-master, master library DB1, DB2, all need to be copied to S1, where the DB1, DB2, S1, respectively, in host H1, H2, H3 on. First, the host H3 (S1 where the host) on the other two MySQL instances from S2, S3, respectively, the main library copy DB1 and DB2, and then create multiple instances of S1 associated with Federated table S2, S3 in the data table ( create the equivalent in S1 to S2, S3 soft connection).

At this time applications, connect to the S1, you can also transparent access to DB1, DB2 in real-time data table.

4 Some note
4.1 the method described above can also be set up directly on S1 to DB1, DB2 Connect all the soft table, but to the greatest degree of reduction of the impact of the main library, the best prepared as above and more with a layer of libraries.

4.2 The simplest can be configured directly on the standby database on multiple instances of the application to connect to multiple instances of like, so no need to copy the above configuration.

References:

1. MySQL Manual: The FEDERATED Storage Engine

2. Post of MySQL Forums: Multiple masters to single slave

3 more by tungsten replicator to achieve master-slave mysql backup architecture

4. Is it possible to do N-master => 1-slave replication with MySQL

5. MySQL Multi-Master - Single-Slave - Replication

相关文章
  • MySQL Federated host a multi-engine equipment 2011-09-06

    Occasionally we need to do. If a host on the copy number of the main library, can be very simple: the standby database host to start multiple MySQL instances (mysqld_muilt), each instance uses a port, copy more than one main library on it. But if you

  • Mysql Federated Storage Engine Open Source installation 2010-11-15

    There are many online when introduced open source Mysql Federated engine installation methods, such as: . / Confiure - with-plugin-federated . / Confiure - with-federated-storage-engine However, I test all errors reported unrecognized options, google

  • 使用mysql federated引擎构建MySQL分布式数据库访问层(转) 2013-08-28

    使用mysql federated 引擎构建 MySQL 分布式数据库访问层 前言:随着应用复杂度的增加,数据库不断细化切分,导致应用程序中数据库应用就得复杂,凌乱.绝大部分程序人员可能都遇到这种情况,应用程序中需要连接多台数据库服务器,进行相应的操作.随着时间积累,太多的数据库服务器的连接逻辑出现在程序之中,这给程序的维护扩展,数据库维护工作带来极大的工作量. 于是一些分布式数据库代理层应运而生,如常见 MySQL 代理层 : mysql proxy : 主要实现读写分离和负载均衡 MySQL

  • Remote connection MYSQL prompt Host is not allowed to connect to this MySQL server problem-solving 2011-09-29

    If this happens when mysql connection error: ERROR 1130: Host '192 .168.1.3 'is not allowed to connect to this MySQL server 1. Change table method. Your account may be allowed from a remote landing, only localhost. This time as long as that computer

  • 远程连接MYSQL提示Host is not allowed to connect to this MySQL server... 2014-12-07

    1.改表法 可能是你的帐号不允许从远程登陆,只能在localhost.这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%" mysql -uroot -ppwd mysql>use mysql; mysql>update user set host = '%' where use

  • MySQL federated engine test (from david_yeung's BLOG) 2011-01-10

    If you write the wrong place, welcome comments. Data can play a very large sub-delivered or when the library to a different server. Reduce each server IO. First, see if there is federated engine. mysql> show engines; +------------+----------+--------

  • mysql federated storage engine application 2010-04-13

    FEDERATED storage engine to access the remote database table data, not the local table. This feature brings to certain development and application of traverse, you can directly build a federated table to the local connect to a remote data table, afte

  • MySQL Database host two simultaneous combat 2010-05-05

    MySQL supports one-way, asynchronous replication, replication master server acting as a server, and one or more other servers act as from the server. Master server writes binary log files will be updated, and maintaining an index of log files to trac

  • MySQL FEDERATED引擎使用示例, 类似Oracle DBLINK. 2013-08-21

    1 引擎说明 本地MySQL数据库要访问远程MySQL数据库的表中的数据, 必须通过FEDERATED存储引擎来实现. 有点类似Oracle中的 数据库链接(DBLINK). 要允许这个存储引擎, 当构建MySQL时使用--with-federated-storage-engine来configure. 当创建一个FEDERATED表的时候, 服务器在数据库目录创建一个表定义文件. 文件由表的名字开始, 并有一个.frm扩展名. 无其它文件被创建, 因为实际的数据在一个远程数据库上. 使用FED

  • Choice of MySQL InnoDB or MyISAM storage engine 2010-10-26

    Core Tip: MyISAM is the default MySQL storage engine, is generally not too many people care about this stuff. Decide what kind of storage engine is a very tricky thing, but still we went to look at the value, where the paper only consider the two MyI

  • On the MySQL MyISAM or InnoDB storage engine Choose 2010-10-26

    Core Tip: MyISAM is the default storage engine in MySQL, in general, not too many people care about this stuff. Decide what kind of storage engine is a very tricky thing, but still we have to look at the value, consider this article only two MyISAM a

  • mysql innodb and myisam database engine of knowledge 2011-09-13

    added a few new mysql database table, the results only frm file exists, if copied directly to another computer, which prompted a few tables are wrong, why? Check the following information: frm, MYI, MYD MyISAM tables corresponding to the table struct

  • FEDERATED storage engine description - MySql database [z] 2010-09-27

    From: http://www.wang48.com/jishubaodianview/?jsd_id=13240 When you create a FEDERATED table, the server creates a table in the database directory definition file. File name from the table began, and there is a. Frm extension. No other table is creat

  • Mysql installation on Federated Storage Engine code 2010-11-15

    There are many online when introduced open source Mysql Federated engine installation methods, such as: . / Confiure - with-plugin-federated . / Confiure - with-federated-storage-engine But I test all errors reported unrecognized options, google for

  • About MySQL database storage engine 2010-03-24

    What storage engine? Data in MySQL using different technologies stored in the file (or memory) in the. Each of these technologies use different storage technologies mechanism, indexing techniques, lock level and ultimately provide a wide range of dif

  • mysql does not allow remote users to access the host server 1130 2010-07-12

    After installing connections to use mysql admin error ERROR 1130: Host ***.***.***.*** is not allowed to connect to this MySQL server Be found in the following Baidu, tried to Description of the user account is not connected to a remote connection pe

  • MySQL Storage Engine - InnoDB and MyISAM comparison 2010-09-14

    The following article introduces the MySQL InnoDB and MyISAM storage engine that is the difference between, including the pros and cons of its evaluation, testing and related performance tests, the following is the article in the details of the intro

  • Inside MySQL: InnoDB Storage Engine 2010-12-14

    Inside MySQL: InnoDB Storage Engine Jiang CHENG Yao the 69.00 yuan Published December 2010 Buy Interactive: http://www.china-pub.com/197220 Abstract: This book is the only one currently works on InnoDB, MySQL experts personally authored by a senior C

  • # 1130 - Host 'localhost' is not allowed to connect to this MySQL server 2011-03-01

    Transfer CSDN: http://blog.csdn.net/T_Ren1988/archive/2010/10/18/5949311.aspx Of the first people who regularly use mysql the following error "# 1130 - Host 'localhost' is not allowed to connect to this MySQL server", or only worked on mysql can

  • MySQL storage engine comparison 2010-09-26

    MySQL storage engine used for the MyISAM, InnoDB, MEMORY, MERGE, InnoDB which provides transaction-safe tables, other storage engines are non-transaction-safe tables. MyISAM is the default MySQL storage engine. MyISAM does not support transactions, d