Mysql – How to sync the databases of a load-balanced drupal site

drupal7MySQL

I have a production environment setup where my drupal site is hosted on two servers, mapped to the same domain, each with a copy of code and database, running on XAMPP. So basically, they are two independent entities.

Now I have users accessing the site through the domain, making updates, which go to either one of the servers and I find that keeping the databases in sync is a real problem.

What are my options? Is there a way to 'merge' the databases and retain all data?

Best Answer

You can resolve this problem in several way:

Use only one database server. Disable one mysql server and connect drupal on both servers to one mysql server. This is not good solution if your database is havely loaded.
U can set up master to master mysql replication. In this scenario you will be able to use both mysql servers and all updates will be replicated to both of mysql servers. This will nicely balance load to mysql database, without any changes to drupal code.
You can use mysql replication master slave. Here you must modify drupal code that all updates on database will be done on master server and than replicated to slave. That is good solution if you have small amount of updates on database because this solution can't balance input and updates on database on both mysql. But master slave replication is much more simpler to configure and maintance.
U can set up third server for mysql database only and connect both sites to the same mysql server. You can than configure you websevers to be slaves of master database only for backup purposes

Related Solutions

Mysql – How to load a sql.gz file to the database? (importing)

zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database

> will write the output of the mysql command on stdout into the file myfile.sql.gz which is most probably not what you want. Additionally, this command will prompt you for the password of the MySQL user "root".

Mysql – What method of MySQL mirroring should I use for this

Solution 1 is close to sharding but the entire architecture needs to be considered and designed to accomplish that in the best manner. Sharding typically comes up with large scale installations pushing limits of the technology platforms.

Solution 2 or dual master replication would be applicable but as your links are physically separate it would be risky to have the application dynamically point to either database. You would want to pick one database and if the database failed, manually repoint the application to the new database. Automatic failover of the application introduces the risk of split brain. You could take nightly snapshots of the secondary databases for backups.

As described in solution 3, replication is often used to distribute the readonly load to different database servers. It also lets you use different engines and configurations for the read queries. For example, MyISAM can be quicker for readonly queries.

Replication is typically only subject to the physical hardware limitations be it network or system resources. Unless you are storing binary data in the database on a large scale, I would not worry about replication delays under normal load.

Being as that your main requirement is speed, I would first focus on the local system configuration and resources. Chances are substantial optimizations can be made there.

Automatic high availability solutions are typically best localized to a single physical environment and in case of extreme failure manual solutions can be applied to enable the physically separate site.

I'm generalizing based on a LAMP stack and focusing on Web applications. Different applications, protocols, and technologies change thing a bit but in regards to Web servers and databases what I describe is more generally applicable.

Best Answer

Related Solutions

Mysql – How to load a sql.gz file to the database? (importing)

Mysql – What method of MySQL mirroring should I use for this

Related Topic