0

I have a specific need to create a single linux server which can host 4 MySQL instances with read replication. Here are a few questions I cannot seem to find answers to:

  1. Can you setup read replication through MySQL similar to Amazon RDS, or is Amazon using a propriety application to handle this read/write replication? If not, are there standalone packages for this kind of replication?
  2. Can you set individual settings for each MySQL installation? For instance I want a memory limit for MySQL running on port 64506 of 2GB, but MySQL installation on port 3306 should have a 16GB memory limit?
Improve this question

1 Answer 1

Reset to default
0

  1. Yes, Amazon uses standard replication for its instances. Not only it is documented here, but also is proven with the fact that you can replicate from and to MySQL instances outside of RDS. No need for any extra packages/software, a standard MySQL server can be a master and/or a slave with the appropriate configuration and preparation.

  2. Yes, if you run several MySQL instances on the same node (assuming you have changed its port, data directory, pid and socket), you can configure them with different buffer sizes, max_connections, etc. However, make sure to understand that there is no way to hard limit the maximum memory or CPU usage on MySQL configuration - if you want real isolation, you have to go to virtualization or other operating system limits (this is what Amazon actually does).

I recommend you to have a look at MySQL Sandbox, which makes easier to setup MySQL and replication among them on the same machine.

Improve this answer
4
  • I understand that MySQL has replication out of the box, but I want a write/read master/slave situation where all of the reads will come from slave, and all of the writes will go to master. I can't find documentation on this functionality save for stuff about Amazon RDS (same link you mentioned). Thank you for other information!
    – David Allen
    Commented Aug 5, 2014 at 14:48
  • @David Allen Doing read/write split is very easy, depending on your stack/programming language I can provide you with specific examples (PHP: php.net/manual/en/mysqlnd-ms.rwsplit.php Java: blog.yannickjaquier.com/mysql/… Rails: github.com/taskrabbit/makara). The problem is that many times, the results are wrong if handled fully-automatically because application should be replication-aware.
    – jynus
    Commented Aug 5, 2014 at 15:14
  • So it appears that this is an application level feature, and not something MySQL will handle. Thanks!
    – David Allen
    Commented Aug 5, 2014 at 21:18
  • @David Allen Well, it can be done at middleware level (MySQL Proxy), but usually it is done at least at connector/pool level.
    – jynus
    Commented Aug 6, 2014 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.