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
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.
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?
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 http://blog.schaal-24.de/?p=1921&lang=en
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
I´m running master-master-slave-setup for ISPConfig. Maybe you find some hints here: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-multi-master.html
