MySQL在星型拓扑中 [英] MySQL in star topology
问题描述
我有一个中央数据库，其中包含MySQL 5.1中的所有数据 - lastest-stable。
我想在主 - 主关系中连接多个客户端。
I have one central database with all the data in MySQL 5.1-lastest-stable.
I want to hook up multiple clients in a master-master relationship.
如何设置一个星形拓扑，中间有一个中央服务器，有多个客户端数据库，以便首先传播一个客户端中的更改到中央服务器，从那里到所有其他客户端数据库？
How do I setup a star topology with 1 central server in the middle with multiple client-databases so that changes in one client get propagated first to the central server and from there to all the other client-databases?
对所有表使用inno-db，并启用了二进制日志。
除此之外，我学习了如何在数据库之间进行master-master。
All表具有主键主整数自动增量。其中自动增量偏移和启动调整到不同的客户端数据库从来没有主键冲突。
I'm using inno-db for all the tables and I've enabled the binary-log.
Other than that I've learned how to do master-master between to databases.
All tables have primary keys primary integer autoincrement. Where the autoincrements offset and start is tuned to different client-databases never have primary key conflicts.
我有一个客户端软件（不是一个网站或php）连接到笔记本电脑上的本地MySQL数据库，这需要同步到中央数据库，以便所有的人在他们的笔记本电脑上使用该程序看到所有其他的更改
我不想直接连接中央数据库，因为如果笔记本电脑和中央数据库之间的互联网连接断开，我的应用程序会死机。
在此设置中应用程序继续，笔记本电脑不会从其他人获取更新，直到与中央数据库的连接重新建立。
I have client software (not a website or php) that connects to a local MySQL database on the laptop, this needs to sync to a central database, so that all folks using the program on their laptop see all the other changes that other folks make.
I do not want to connect directly against the central database because if the internet connection drops between the laptop and the central database my application dies.
In this setup the application continues, the laptop just does not get updates from other people until the connection to the central database is reestablished.
推荐答案
使用MySQL循环复制作为同步DB的手段的要求，这里是一个解决方案：
Given the requirement to use MySQL Circular Replication against floating slaves as a means to Synchronize DBs, here is a solution:
1 DB Master
4个DB从属
1 DB Master
4 DB Slaves
SETUP OF DB MASTER
- 安装MySQL 5.1.x
- 确保/etc/my.cnf
; server-id = 1
log-bin = mysql-bin
; expire-logs-days = 14
default-storgae-engine = InnoDB
- 启动MySQL
- RESET MASTER; （从DB主数据清除二进制日志）
- 将数据加载到主数据库
- GRANT SELECT，REPLICATION SLAVE ON replicator @'％'IDENTIFIED BY'repl-password';
- Install MySQL 5.1.x
- Make sure /etc/my.cnf
server-id=1
log-bin=mysql-bin
expire-logs-days=14
default-storgae-engine=InnoDB - Startup MySQL
- RESET MASTER; (Clear Binary Logs From DB Master)
- Load in Data into Master
- GRANT SELECT,REPLICATION SLAVE ON . TO replicator@'%' IDENTIFIED BY 'repl-password';
设置数据库SLAVES
- 安装MySQL 5.1.x
- /etc/my.cnf
server-id =（唯一服务器ID）
log-bin = mysql-bin
default-storage-engine = InnoDB
- 启动MySQL
- 更改MASTER TO MASTER_HOST ='DB Master的IP地址'，MASTER_PORT = 3306，MASTER_USER ='replicator'，MASTER_PASSWORD ='repl-password'，MASTER_LOG_FILE = 'mysql-bin.000001'，MASTER_LOG_POS = 106;
- 开始SLAVE; （允许复制追赶，检查SHOW SLAVE STATUS\G）
- 停止SLAVE;
- 更改MASTER TO MASTER_HOST = Master'，MASTER_PORT = 3306，MASTER_USER ='replicator'，MASTER_PASSWORD ='repl-password'，MASTER_LOG_FILE ='mysql-bin.000001'，MASTER_LOG_POS = 106;
- START SLAVE; / li>
- Install MySQL 5.1.x
- Make sure /etc/my.cnf
server-id=(unique server id)
log-bin=mysql-bin
default-storage-engine=InnoDB - Startup MySQL
- CHANGE MASTER TO MASTER_HOST='IP Address of DB Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
- START SLAVE; (let replication catch up, check SHOW SLAVE STATUS\G)
- STOP SLAVE;
- CHANGE MASTER TO MASTER_HOST='IP Address of DB Distribution Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
- START SLAVE;
IMPORT PROCESS
每个DB从器件准备就绪后，现在可以按照以下步骤进行DB主器件和DB从器件之间的数据迁移（DB用于DB主站，DS用于DB从站）：
Once every DB Slave is prepared, now the migration of Data between the DB Master and DB Slave can proceed as follows (DM for DB Master, and DS for DB Slave):
- 在DS上，执行SHOW MASTER STATUS;
- 在DS上，记录二进制日志文件名和位置
- 在DM上，运行CHANGE MASTER TO MASTER_HOST ='DS的IP地址，MASTER_PORT = 3306，MASTER_USER ='replicator'，MASTER_PASSWORD ='repl-password'，MASTER_LOG_FILE ='LogFile From Step2'，MASTER_LOG_POS =从步骤2）;
- 在DM上，运行START SLAVE;
- 在DM上，停止SLAVE;
- 在DS上，记录第2行/ var / lib / mysql / master.info（log file）
- 在DS上，记录/var/lib/mysql/master.info（日志位置）的第3行
- 在DS上，运行CHANGE MASTER TO MASTER_HOST ='DM的IP地址，MASTER_PORT = 3306，MASTER_USER ='replicator'，MASTER_PASSWORD ='repl-password'，MASTER_LOG_FILE ='LogFile From Step6'，MASTER_LOG_POS = （LogPos From Step7）;
- 在DS上，运行START SLAVE;
- 在DS上，停止SLAVE;
- On DS, Run SHOW MASTER STATUS;
- On DS, Record binary log filename and position
- On DM, Run CHANGE MASTER TO MASTER_HOST='IP Address of DS', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='LogFile From Step2', MASTER_LOG_POS=(LogPos From Step2);
- On DM, Run START SLAVE; (let replication catch up; port changes introduced by DS to DM)
- On DM, STOP SLAVE;
- On DS, record line 2 of /var/lib/mysql/master.info (log file)
- On DS, record line 3 of /var/lib/mysql/master.info (log position)
- On DS, Run CHANGE MASTER TO MASTER_HOST='IP Address of DM', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='LogFile From Step6', MASTER_LOG_POS=(LogPos From Step7);
- On DS, Run START SLAVE; (let replication catch up; port changes introduced by DM to DS)
- On DS, STOP SLAVE;
CAVEAT
汇入程序至关重要！您必须每次都正确记录确切的日志文件和日志位置。
The import process is crucial !!! You must record the exact log file and log position correctly each and every time.
给它一个Try !!!
Give it a Try !!!
让我知道它怎么回事。
这篇关于MySQL在星型拓扑中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
