Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I currently have 1 mysql server running and want to install 2 seperate mysql VPS'es. Maybe one master for inserts/updates and one slave for selects. What is the best setup for this if I have the following requirements:

  • Run backups on machine
  • When my mysql backups run on the slave machine it generates a high load. Can the master decide to take over some selects to reduce the loads?

Is there a way that the master decides where to execute the query (load balancing)? Is it possible to send all queries to one master and the master decides if he sends it to the slave, so I don't have to make 2 connections in my php code (1 for select, 1 for updates)?

Thanks, Ward

share|improve this question
add comment

migrated from serverfault.com Aug 26 '11 at 17:41

This question came from our site for professional system and network administrators.

2 Answers

You might look into MySQL Proxy. Features include:

  • load balancing
  • failover
  • query analysis
  • query filtering and modification

I haven't run it myself, but it seems to cover what you want.

UPDATE

Apparently the Read/Write splitting is a proof of concept and not for production use

share|improve this answer
add comment

If you are have all InnoDB data, I have good news for you.

You can use Percona XtraDB Cluster. It performs synchronous replication using MySQL Galera architecture. Because the expected storage engine for use is InnoDB, the MySQL Galera library performs all InnoDB DML transactionally across the Cluster. In order for an UPDATE to take place, the UPDATE must execute and commit on all nodes in the Cluster or else rollback.

It has multiple techniques for creating a new Cluster node

  1. mysqldump
  2. rsync + crash recovery
  3. xtrabackup (best way for InnoDB)

It does not work with MyISAM tables except on creating new Cluster node.

You can execute GRANT command and it will replicate. Running INSERTs, UPDATEs, and DELETEs against mysql schema tables will not replicate because they are MyISAM.

Please read about this wonderful product : http://www.percona.com/software/percona-xtradb-cluster/

As for your question, you could setup another server to be a MySQL Slave to one of the Cluster nodes and then perform backups from that Slave as follows:

  • STOP SLAVE;
  • mysqldump
  • START SLAVE;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.