Mysql Replication Automation Script

Question

I am looking for something that will help us to create a lot of mysql slaves connected to the specified master, let's say kind of mass-replication.

If there are any existing solution for this purpose. In simple case it can be just bash script, that locks the master DB, copies table files to the new slave, restore position and start the replication.

From you knowledge if something described above exists?

Answer 1

I would like to suggest something radical. I got this idea from StarTrek : Deep Space 9 (Call to Arms)

A minefield was set up around a wormhole to prevent the Dominion Forces and the Jemhadar from passing through. Each explosive mine was armed with a replicator that allowed a mine to recreate another mine after it explodes. Hence, the minefield stays up indefinitely.

With that DS9 analogy I bring your an interesting idea.

You set up 2 initial read slaves with MySQL Slave with innodb disabled

[mysqld]
skip-innodb

This is optional. My preference is an all-MyISAM slave to do reads because it is faster for reads than InnoDB for small datasets. Should you choose to go with InnoDB, make sure you relax the ACID compliance with this

[mysqld]
innodb_flush_log_at_trx_commit = 0

In the event of a crash, just destroy the Slave and spin up a new one

We'll call the Slaves S0 and S1

Here is something else: have this in /etc/my.cnf in S0

[mysqld]
innodb_max_dirty_pages_pct = 0;
innodb_fast_shutdown = 0

These will help S0 shutdown fast with completely flushed data.

The following is what you must script in the replicator process

When you need to generate a new slave (we will call it S2), here is what you must do

STEP 01) On S0, run service mysql stop

STEP 02) Install the same version of MySQL that S0 has into S2

STEP 03) On S0, scp /etc/my.cnf S2:/etc/.

STEP 04) On S2, you need to change the server-id of /etc/my.cnf in S2 to a Unique Value (suggestion : use the 2nd,3rd, and 4th octet [without the dots] of the private IP of S2)

STEP 05) On S2, either remove or comment out the innodb_max_dirty_pages_pct = 0 from /etc/my.cnf

STEP 06) On S0, run rsync -av /var/lib/mysql S2:/var/lib/mysql (Note: If you have to spin up 5 Slaves, run the 5 rsyncs at this point)

STEP 07) On S2, service mysql start (MySQL Replication start immediately where S0 had left off)

STEP 08) On S0, run service mysql start

Once you create the replicator script, you can use it spin up MySQL on new Slaves.

Meanwhile, S1 is available for SELECT queries and continues replicating.

S0 is used to recreate a new Slave.

If you are doing this in conjunction with spinning up Amazon EC2 or some other Cloud DB Servers, check with your System Administrators on any Linux commands/API that allows you to spin up a DB Server. Then, you apply the replicator to the newly generated DB Server. Even better, you can incorporate the Db Server creation API in your Replicator Script.

CAVEAT

If you have to spin up dozens or hundreds of Slaves, all you need to do is have 10 Replicator Slave Servers (S0 - S9) and have 10 copies of the replicator script operator on different Replicator Slaves.

• com: Thanks for the idea, why we should disable innodb?
• RolandoMySQLDBA: An all MyISAM Read Slave is faster that InnoDB for smaller sets of data. In reality, skip-innodb is optional. The process works regardless of the Slave's main storage engine. You would just be responsible for configuring the S0 Slave. I'll update my answer regarding this.

Answer 2

I wrote this blog post which explains how to create the bash script to automate Master-Slave replication on MySQL:

http://blog.ditullio.fr/2016/04/30/initialize-mysql-master-slave-replication-script/

It gives a shell script (see end of the post) where you simply enter in variables:

• The Master hostname or IP
• An array of Slave hostnames or IPs
• The Database name

Then it automatically starts replication of the master on those slaves.

It is optimized for a big number of slaves because it locks and exports the master dump and log position only once. Then it distributes the dump file to all slaves and initializes replication on each slave.

Answer 3

Yes, it's called mysqldump:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You can use it to copy data to a slave and start replication in one command. It's all there.

Also see:

http://dev.mysql.com/doc/refman/5.1/en/replication-howto-existingdata.html

• Lifeboy: For this answer to be valuable you should quote the relevant part of the reference and probably give an example. Just linking to the documentation is not of much value since the OP probably knows of it's existence already anyway.
• Aaron: +1 for helping me learn that mysqldump has replication features. -1 for making me read mysql's terrible documentation (seriously its the worst)