KND Web Design Brisbane | Brisbane Web Designer, Web design Sydney, Web development, web hosting

Using Replication as Backup

This is just a quick run down on getting replication going as a mild form of backup. It WILL NEVER ever replace a good backup strategy, but it at least ensures that most of your SQL data is in two places.

Install MySQL

Not all builds are the same, even if the revision numbers are. Also, compiling it yourself may not produce the best build. Replication is pretty important. You are usually doing it for reasons other than fun or avoiding the mowing, so it’s important to get a build of MySQL that is optimized and stabilized for replication. I recommend at least 5.0.55+. Ok, lets go get the packages from OurDelta. Just for the Hell of it

Let’s pretend we are using Fedora 8 (I really did and flock me it worked!).

Install the YUM ProtectBase plug-in, get the initial sign key and then the OurDelta release RPM containing YUM repo/mirror files and GPG key:

yum install yum-plugin-protectbase

yum install yum-protectbase

rpm --import http://mirror.ourdelta.org/deb/ourdelta.gpg

rpm -Uvh http://mirror.ourdelta.org/yum/CentOS/OurDelta-release-0.0.4-1.noarch.rpm

Note:You must add protect=0 to all other repos in all .repo files or any repos that you have in /etc/yum.conf if you want them unprotected, otherwise they belong to the protect=1 group.

Now that you have the package repository installed you can install the OurDelta builds through the gui or from the command line. Make sure you remove existing distro mysql-server, mysql, mysql-lib first.

I thought I would cheat a little, because the packages are actually for RHEL and CentOS but what the hey!

You need to edit two files to fool yum:
vim /etc/yum.repos.d/OurDelta.repo

And everywhere you see $releasever change it to 5, eg:

baseurl = http://mirror.ourdelta.org/yum/CentOS/$releasever

change to

baseurl = http://mirror.ourdelta.org/yum/CentOS/5

You need to make this change in one other file:

vim /etc/yum.repos.d/mirrors-OurDelta

Now you should be right to go:

yum install MySQL-OurDelta-shared

yum install MySQL-OurDelta-server

yum install MySQL-OurDelta-client

and while you’re at it also run:
/usr/bin/mysql_secure_installation

This removes test databases, anonymous users and helps you set the mysql root password to something other than blank

Prepare for Replication

Ok there a couple of things we need to do before we start replication:

1. Setup a Replication user.
GRANT REPLICATION SLAVE ON *.* TO 'replix'@'%.knd.com.au' IDENTIFIED BY 'A Complex Slave Password';

2. Setup master replications settings in my.cnf on the MASTER

#replication on master
log-bin=mysql-bin
server-id=1010 //make sure this is a unique number
innodb_flush_log_at_trx_commit=1 //if you are innodb freaks like us, use these settings
sync_binlog=1 //innodb setting
max_allowed_packet=64M

3.  Setup slave replication settings in my.cnf on the SLAVE

#replication on slave
server_id=1011

4. ok shift current data from master to slave

 FLUSH TABLES WITH READ LOCK;
mysqldump --all-databases --master-data >dbdump.db

5.

UNLOCK TABLES;

Replicate or die!

References

OurDelta – Replication How-to

Tags: Tech // Add Comment »