mrm-docs

home : internet : sql : mrm : mrm-faq

mrm-sample

• Why did you write this?
• What assumptions does your script make?
• What do I add to my ~/.my.cnf file?
• How do I replicate a MySQL server?
• How do I add a slave to a replicated config?
• Help, a slave de-synced.
• How do I control the size of my bin-logs?
• How do I use this program to make backups of my database?

Why did you write this?

Because I was annoyed by going back to MySQL.com to read the docs every time I have to muddle with replication. On the bright side, that means that replication is pretty stable and I don't have to fuss with it often enough to remember.

What assumptions does your script make?

1. That your databases on are located in the directory that is defined as dir_m or dir_s as defined in ~/.my.cnf.

2. The permissions of the files on the master are the same as the permissions you want on the slave.

3. If you aren't running this as root, you'll have sudo installed for operations that require root privileges.

What do I add to my ~/.my.cnf file?

 MySQL database parameters
 #
 [mysql_replicate_manager]
 driver = mysql
 db = mysql
 host = sql
 port = 3306
 user = root
 pass = secret
 user_r = replication
 pass_r = secret
 slaves = sql1 sql2
 dir_m = /var/db/mysql
 dir_s = /var/db/mysql
 autocommit = 1
 backupdir = /var/backups/mysql
 backupfile = mysql_full_dump

How do I replicate a MySQL server?

First, build the slave servers and configured root ssh key access to the slave(s). Update /etc/my.cnf on each system per the instructions at mysql.com (unique binlog numbers, etc). Then run this command on the master:

 mysql_replicate_manager.pl -facx

That will lock the tables, flush the bin-log files, take a snapshot (tarball) of the database, record the masters bin-log position, unlock the tables, scp the snapshot and txt file with the log position to the slave(s) mysql dir, and extract the archive(s). Configure /etc/my.cnf on the slave, start the mysqld process and you're up and running.

Confirm once your slaves are running by:

 mysql_replicate_manager.pl -i

If you don't have permission to connect to the remote slave, add entries to the master server, flush priveleges on each slave and then you should be able to connect.

How do I add a slave to a replicated config?

Before adding the new slave to this script, purge the binlogs and make a fresh snapshot archive of the database.

   mysql_replicate_manager.pl -pa

Now edit .my.cnf and configure ``slaves'' with only the name of your new slave server. Then run like this:

   mysql_replicate_manager.pl -cx

That will copy the archive to the new slave and extract it. From there it's up to you to configure /etc/my.cnf on the slave and start it up. Once you're got the slave up and running, re-edit .my.cnf on the master and add put the names of the rest of your slaves back in.

Confirm that the new slave is in sync by running:

   mysql_replicate_manager.pl -i

Help, a slave de-synced.

Manually fix it (rtfm) or proceed as if adding a new slave to your replication farm. Edit @arrays to only contain the de-synced server and then something like this ought to do:

mysql_replicate_manager.pl -s slave (shutdown slave(s)) mysql_replicate_manager.pl -acx

Start up MySQL on the slave and run this command:

CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='', MASTER_LOG_FILE='', MASTER_LOG_POS=

Fill in the master log parameters from the .txt file in mysql dir

How do I control the size of my bin-logs?

Create a cron entry that runs ``mysql_replicate_manager.pl -p'' every day. That will rotate the bin-log file, and purge any files older than yesterdays.

How do I use this program to make backups of my database?

Add the following entry to roots crontab:

10 1 * * * /usr/local/sbin/mysql_replicate_manager.pl -b -q

Last modified on 4/27/05.