Best Mysql Replication Solution?

Discussion in 'Server Operation' started by davefrooney, May 21, 2013.

  1. davefrooney

    davefrooney Member

    I am running 3 am running 3 web/db servers. I originally setup master master replication so that all my webservers could use localhost as this was speeding up the sites considerably. The problem was I was getting mysql errors from some of the table and I had to setup the replication from scratch again and again which is not ideal. I also noticed that the mysql database wasn't replicating properly itself. This was a real pain when adding user. It meant I had to add the user manually to all the servers

    I tried mysql-proxy using read/write splitting which would run fine for a short time but once an error occurred the system would crash and have to be restarted manually.

    I'm wondering what the most reliable system is to keep my 3 databases synchronized?

    Many thanks in advance for your help
  2. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    If i add a new user to mysql using ispconfig, the user is created on all sql-servers. But i skip the mysql-db in the replication since i won´t the same data for i.e. the root-user on all systems.
  3. davefrooney

    davefrooney Member

    Thanks for getting back to me Florian. I was originally set up this way too, however, if a database was added through ISPConfig and replication was set up,MySQL would crash as the database was being added twice.

    How are you replicating your databases?
  4. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    That´s a known problem. Mysql can stop the replication with error 1007 (Can’t create database). I played around with some possibilities to use the mysql-replication with ispconfig and finally i´m now skipping the errors 1007 and 1008 in mysql.

    For more see
  5. davefrooney

    davefrooney Member

    Nice blog! There are some really good posts in there. I will test out this configuration and hopefully get a nice reliable system.

    Would you recommend setting these servers up in a ring configuration or what do you find to be the most resilient topology?

    Thanks for your help
  6. florian030

    florian030 ISPConfig Developer ISPConfig Developer

Share This Page