HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   Best Mysql Replication Solution? (http://www.howtoforge.com/forums/showthread.php?t=61909)

davefrooney 21st May 2013 01:15

Best Mysql Replication Solution?
 
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

florian030 21st May 2013 07:41

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 wont the same data for i.e. the root-user on all systems.

davefrooney 21st May 2013 13:08

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?

florian030 21st May 2013 13:14

Thats a known problem. Mysql can stop the replication with error 1007 (Cant create database). I played around with some possibilities to use the mysql-replication with ispconfig and finally im now skipping the errors 1007 and 1008 in mysql.

For more see http://blog.schaal-24.de/?p=1921&lang=en

davefrooney 21st May 2013 13:23

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

florian030 21st May 2013 13:28

Im running master-master-slave-setup for ISPConfig.

Maybe you find some hints here: http://dev.mysql.com/doc/refman/5.1/...ti-master.html


All times are GMT +2. The time now is 00:07.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.