mysql read slave to fetch entire database on start I am building a standart infrastructure, where writes are going to master db, and reads are going to slave dbs. The idea is that when I need more read power - I just spawn more MySQL slave servers.

Now I am aware of standart procedures of how to create a new slave. And they are a little to slow ... What I am looking for is to create an server image that I could spawn when I need more read power and that image would automatically sync to master and become its slave for reads.

At the moment I think of doing it like this.


Have a database backups every 4 hours or so/
Write a script that would run first time server is started.
The script would download the latest backup and import it in a regular way ... and bla bla bla ...


Maybe someone has done something similar and could share some resources ?
If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.

Here is a Topology

MAS --+--&gt; SLV1
      |
      +--&gt; SLV2
      |
      +--&gt; SLV0


Say you want to Spawn SLV3. You could use SLV0 as a Clone


STEP01) Install MySQL Binaries on SLV3 (Same version as all other Slaves)
STEP02) On SLV0, STOP SLAVE;
STEP03) On SLV0, service mysql stop
STEP04) scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
STEP05) scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
STEP06) On SLV0, service mysql start
STEP07) On SLV3, chown -R mysql:mysql /var/lib/mysql
STEP08) On SLV3, change server_id in /etc/my.cnf to be unique from all other Slaves
STEP09) On SLV3, service mysql start


That's it.

This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST (State Snapshot Transfer).

If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.