One tool I've used for easy management of multiple MySQL instances on the same box is MySQL Sandbox. It's nothing fancy -- a Perl module which offers a collection of scripts -- but it does make your life much easier.
To install MySQL Sandbox, download it from its Launchpad page, then run 'perl Makefile.PL; make; make install'. You also need to download a MySQL binary tarball which will serve as a common base used by your MySQL instances.
Here's an example of a script I wrote which creates a new MySQL Sandbox instance under a common directory (/var/mysql_slaves in my case). The script takes 2 arguments: a database name, and the name of the MySQL master from which that database is replicated. The script automatically increments the port number that the new sandbox instance will listen on, then creates the instance via a call like this:
/usr/bin/make_sandbox /usr/local/src/mysql-5.1.32-linux-x86_64-glibc23.tar.gz \ --upper_directory=/var/mysql_slaves \ --sandbox_directory=$SLAVEDB_NAME --sandbox_port=$LAST_PORT_NUMBER \ --db_user=$SLAVEDB_NAME --db_password=PASSWORD \ --no_confirm
As a result, there will be a new directory called $SLAVEDB_NAME under /var/mysql_slaves, which serves as the sandbox for the newly created MySQL instance. The script also adds some lines related to replication to the new MySQL instance configuration file (which is /var/mysql_slaves/my.sandbox.cnf).
To start the instance, run
To stop the instance, run
To go to a MySQL prompt for this instance, run
At this point, you still don't have a functioning slave. You need to load the data from the master. One way to do this is to run mysqldump on the master with options such as ' --single-transaction --master-data=1'. This will include the master information (binlog name and position) in the DB dump.
The next step is to transfer the DB dump over to the box running MySQL Sandbox, and load it into the MySQL instance. I use a script similar to this.
You should now have a MySQL instance that acts as a replication slave to a specific master server. Repeat this process to set up other sandboxed MySQL instances that are slaves to other masters.
Note that MySQL Sandbox already includes some replication-related utilities (which I haven't used) and also an admin-type tool called sbtool. The documentation is pretty good.
This is really helpful. Thank you.
When I ran this, I had to insert "--" between the first two statements and the options, like:
/usr/bin/make_sandbox /usr/local/src/mysql-5.1.32-linux-x86_64-glibc23.tar.gz -- \
--sandbox_directory=$SLAVEDB_NAME --sandbox_port=$LAST_PORT_NUMBER \
--db_user=$SLAVEDB_NAME --db_password=PASSWORD \
Post a Comment