|
MySQL Relication Manager FAQ Apache Log Utility
Author: Matt Simerson.
[ Install | FAQ | ChangeLog | Sample ]
Why did you write this?
Because I'm 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 = replication
pass = secret
slaves = sql1 sql2
dir_m = /var/db/mysql
dir_s = /var/db/mysql
autocommit = 1
backupdir = /var/backups/mysql
backupfile = mysql_full_dump
I want to replicate a MySQL server. I've built the slave servers and
configured root's ssh keys access to the slave(s), updated /etc/my.cnf
per the instructions at mysql.com. What now?
On the Master run: mysql_replicate_manager.pl -facx
That will lock the tables, flush the bin-log files, take a snapshot of
the database, record the masters bin-log position, unlock the tables,
scp two files to the slave(s) mysql dir, and extract the archive(s).
Now just configure /etc/my.cnf on the slave, start the mysqld process
and you're up and running.
Confirm by running mysql_replicate_manager.pl -i
I have an existing replicated setup and wish to add another slave.
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 your new slave server and run like this:
mysql_replicate_manager.pl -cx
That will copy the archive to the new slave, extract it and then it's
up to you to configure /etc/my.cnf and start up the slave.
I have a replication farm and 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
| 
