Master – Slave Replication

There are many reasons to use master-slave replication including but not limited to:

1) Offload some of the queries from one server to another thus helping spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to a slave (or even a series of slaves). This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware-  This is especially handy if your using some off the shelf application such as WebEmpoweredChurch.com / Typo3,  Magento , WordPress, Drupal, etc . . .

2) Backups can hurt a DB server @ times.   Here @ Typo3USA – We always Do backups from slave.  Most techs simply just overlook this simple but highly effective advantage.   We have some databases that are quite large – super large and others that are just a few megs – to a few gigs.   Sadly – when the size of a database becomes a big deal is when it has grown to multiple gigs and mysqldump causes the site to lag when it locks tables.  For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (we have built this into our backup scripts now) and run backups off the slave. You can even stop slave MySQL instance and copy the /etc/mysql/mysql or /var/lib/mysql  (or whatever your database directory ) folder instead of doing mysqldump…

Recently – we moved forward into using the R1Soft.com Backup System (in fact we also had a good hand with the Vine Staff and Ron Hall in building up their new website – check it out @ www.R1soft.com ) WE ABSOLUTELY LOVE THIS SYSTEM BECAUSE IT IS SO FAST IN ITS BACKUPS FOR MYSQL

Ok let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up. I will just touch on very basic ones to get the replication to work. Here are some assumptions:

Master server ip: 10.100.1.1
Slave server ip: 10.100.1.2
Slave username: slavemysqluser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/

Put the following in your master my.cnf file under [mysqld] section:

# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master

Copy the following to slave’s my.cnf under [mysqld] section:

# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup

Create user on master:
mysql> grant replication slave on *.* to slavemysqluser@'10.100.1.2' identified by 'slavepw';

Do a dump of data to move to slave
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql

import dump on slave
mysql < masterdump.sql

After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='10.100.1.1', MASTER_USER='slaveuser', MASTER_PASSWORD='slavepw';

Let us start the slave:
mysql> start slave;

You can check the status of the slave by typing
mysql> show slave status\G

The last command will let you know how up to date the slave is from the master in seconds.  I have had many folks worry about this being behind the master – it is okay if it does not say ZERO right away – if you check it you should see the number going down over time until it finally catchs up with the master.

NOTE* if it shows NULL, – it could be that the slave has not started or that it has an error:  (if this is the case it should show up in Last_errno; and Last_error under the: show slave status\G )

We suggest a number of different tools as well for MySQL including:

Maatkit
Maatkit is a great set of tools for MySQL Performance Analyses and maintainence. Must know and have for any MySQL User.

mysqladmin
mysqladmin extended -i100 -r is very nice way to look how MySQL performance counters increment and it can tell you a lot about server run status.

mysqlreport
MySQL Report is a tool which would look at status variables same as mysqladmin extended but will group them together nicely and provide some hints on what are good and bad values.

mysqlsla
This is nice tool to analyze slow query log. It reads bunch of different log formats and has various stats, and it was there before mk-log-parser appeared.

innotop
Innotop is great top like tool for MySQL and is helpful even if you do not use Innodb tables. Very nice to watch what happens to server in the real time.

oprofile
oprofile is usually used for advanced MySQL tuning when the load is CPU bound – it will tell you where exactly inside MySQL or Kernel CPU time is spent.

iohist
iohist is a little tool to show histogram for IO response time. The main use for it is to see how response time is split for read and write request rather than seeing the average reported by iostat. These can be very different.

sysbench
sysbench is a tool to check performance of system and MySQL. Helpful to check different hardware and OS characteristic.