What can we do in MySQL 5.0 replication to address bandwidth concerns?

 

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of schemas, but I only need one per client so I supply the replication-do-db setting in my.ini to only replicate the schema that the client needs. The replication works, but when our clients get into regions of the world where internet access is only available via 3G wireless, which charge by data usage, they quickly exceed their data plan limits and run into expensive problems.

As I understand it, MySQL writes all transactions for all schemas into a single binlog file which means that each client has to download all of the transactions that are performed on every schema on the master, then once downloaded, apply the database filter per replication-do-db settings in the client's my.ini file.

To minimize this inefficiency I have employed the slave_compressed_protocol = 1 setting, which seems to reduce the transmitted data by 50%, but still causes our client's to quickly exceed their data limit rack up the 3G bill.

I can't imagine I'm the only one facing this, so I'm sure I'll get a ton of answers on how to achieve this by setting x = y. However, I can't find any documentation of such a setting, nor a recommended approach to take.

So far, here's my thought to a possible solution, please provide feedback or alternate routes:

  • Set up a "proxy" slave for each schema (on different box, or same box with a different MySQL instance/port)
  • Configure the proxy slave to replicate-do-db only the one database that the clients wish to replicate.
  • Configure the client's MySQL instance as slaves to the appropriate proxy slave.

    • This should result in the client only pulling the binlog data for their schema. The downside (as far as I can tell) is that it dramatically increases the complexity of our setup, likely making it more fragile.

    Thoughts? Will this approach even work?

    Note, we are running the MySQL 5.0 server on RedHat, but we could upgrade to 5.5 if it produces a solution.

    I addressed this question already in the DBA Stack Exchange : https://dba.stackexchange.com/questions/3106/what-can-we-do-in-mysql-5-0-replication-to-address-bandwidth-concerns/3107#3107

    I do not want to double dip in the Stack Exchange. Moderators, please close this !!!

    Two suggestions

    1.) Try using the --replicate-do-table option and specify only the tables you want

    2.) Try using the --replicate-wild-do-table=name ..you should be able to filter all your tables this way if your tables have a unique name

    MySQL docs on table replication

    链接地址: http://www.djcxy.com/p/83260.html

    上一篇: 在CakePHP中使用不同的MySql DB进行写入操作

    下一篇: 我们可以在MySQL 5.0复制中做什么来解决带宽问题？