How to mirror a MySQL server

With disk sizes so enormous it's good to keep a few database around just for safety. Here is a way to setup a mirror (a slave in MySQL parlance) of a database. This can be replicated with numerous other slaves... or your slave could be a master for another slave. Here we presume you have a large MySQL database and want to get a slave up and running quickly.
  1. add the following to the Master MySQL server config file: usually to be found in /etc/my.cnf or C:\Program Files\MySQL\MySQL Server X.Y\my.ini on Windows
    log-bin=mysql-bin
    server-id=1
    
  2. create a user backupslave and give it "replication slave" privileges. I used a free version of Navicat but here are the simple MySQL commands:
    create user 'backupslave'@'%' identified by 'somepassword';
    grant replication slave on *.* to 'backupslave'@'%';
    flush privileges;
    
  3. To ensure that all buffer sizes and data locations etc. are in compatible, copy the Master /etc/my.cnf file to the Slave machine. Now add the following to the Slave /etc/my.cnf
    log-bin=mysql-bin
    server-id=2 # NOT 1
    relay-log=mysqld-relay-bin
    
    # this is deprecated MySQL want you to use the 'CHANGE MASTER' command instead
    # e.g. 
    # mysql> CHANGE MASTER TO master_host='168.95.222.XX',master_user='backupslave',
    # mysql>                master_password='somepassword',master_port=3306;
    #
    master-host=168.95.222.XX # these are written to master.info file in 
                              # the data directory the first time it is created
    master-user=backupslave
    master-password=somepassword
    master-port=3306
    
    
    This is somewhat deprecated... MySQL want the master-* keys entered using a CHANGE MASTER command viz:
    mysql -u root -p <<EOF
    STOP SLAVE;
    CHANGE MASTER TO master_host='168.95.222.XX',master_user='backupslave',
                   master_password='somepassword',master_port=3306;
    START SLAVE;
    EOF
  4. on the Slave machine copy the Master data files over using rsync:
    rsync -avz --delete Master-Machine:/var/lib/mysql /var/lib/mysql
    You can keep the Master MySQL server running while you do this - at least under Unix - don't worry about updates going on at the same time (see the next step) we just want to get the bytes across the ether.
  5. Now we must really must sync the data files. If you can suffer a downtime of a minute or so stop both Master and Slave mysqld daemons with /etc/init.d/mysqld stop; sync. rsync again to clean up any changes ... this should be quick! Now remove any of the following files you find on the Master machine (in the data directory):
    • mysql-bin.NNNNNN files
    • mysql-bin.index

    If you can't bring down the Master (but can bring down the Slave) server you might want to 'lock tables' instead see here. A post by Daniel Kadosh on the mysql site gives this remedy which we adapt here:

    #!/bin/bash
    /usr/bin/mysql -u root -p <<EOF
    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;
    SYSTEM sync; 
    SYSTEM rsync --password-file=/path/to/file -avz --delete /var/lib/mysql user@Slave:/var/lib/mysql
    PURGE BINARY LOGS;
    UNLOCK TABLES;
    EXIT;
    EOF
    
    Note: We are purging the binary logs because we are copying the raw data files with rsync. If you don't remove the binary logs from the Master then the Slave will try and update an already updated table and will fail. The mysql log file (/var/log/mysqld.log) will contain an entry like (say):
    100203 13:52:48 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
    100203 13:52:48 [Note] Slave I/O thread: connected to master 'backupslave@130.95.207.4:3306',  replication started in log 'FIRST' at position 4
    100203 13:52:53 [ERROR] Slave: Error 'Duplicate entry '1008' for key 1' on query. Default database: 'BHM_chemicals'. Query: 'INSERT INTO chemicals (catalogue, comments, company) 
     VALUES (null, null, 'Pierce')', Error_code: 1062
    
    Check that you don't see anything like this in the log!
  6. You can now restart the Master... but not the Slave - yet!
  7. Remove from the Slave data directory all
    • mysql-bin.NNNNNN files
    • mysql-bin.index
    • master.info
    • and error/pid (e.g www.borg.org.{err,pid} etc.) files
    • mysqld-relay-bin.NNNNN files
    • mysqld-relay-bin.index
    • relay-log.info
    that have come from the Master. Check also in the /var/run/mysqld/ directory!
  8. Start the Slave mysqld
  9. Ensure that the communication is working with show slave status\G (see here also) on the Slave. There should be at least 2 processes running...
    mysql> show slave status\G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 130.95.207.XXX
                    Master_User: backupslave
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000003
            Read_Master_Log_Pos: 335
                 Relay_Log_File: mysqld-relay-bin.000004
                  Relay_Log_Pos: 472
          Relay_Master_Log_File: mysql-bin.000003
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                Replicate_Do_DB:
    			..........................
          Seconds_Behind_Master: 0
    1 row in set (0.00 sec)
    
    Check Slave_IO_Running is Yes and Slave_SQL_Running is Yes!

    On the Master use show processlist\G

    mysql> show processlist\G
    *************************** 1. row ***************************
         Id: 1
       User: backupslave
       Host: borg.org:56445
         db: NULL
    Command: Binlog Dump
       Time: 138
      State: Has sent all binlog to slave; waiting for binlog to be updated
       Info: NULL
    *************************** 2. row ***************************
         Id: 2
       User: root
       Host: localhost
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    2 rows in set (0.00 sec)
    
    
  10. Test creating a junk table and editing a row in the Master and watching for it on the Slave (I use - the free version of-- Navicat so I can view both database at once. The MySQL GUI is good too.)
  11. Lastly: ensure that the mysql-bin.NNNNN files are not too ancient by purging them periodically with a quick script like (remember to quote the date!)
    fromdate=python -c 'from datetime import *;\
    	print (datetime.now()-timedelta(days=100)).strftime("%Y-%m-%d"),'
    echo purge binary logs before '${fromdate}' | mysql -u root -p
    on both Master and Slave. And DON'T rsync the data files again!!! And remember that the backupslave password is in *both* /etc/my.cnf and data/master.info files!!! on the Slave machine.
There is a system variable "expire_logs_days" see here.

Extras

Here is a script I use to ensure that I get any warning that the slave is not updating: It's written in Python the World's Greatest LanguageTM :). I place this in /etc/cron.daily/:

#!/usr/bin/python
import sys,os
import smtplib
import MySQLdb
def getslavestatus(password):
	''' get a dictionary of the slave status '''
    conn=MySQLdb.connect('localhost',user='root',passwd=password)
    cursor=conn.cursor()
    nrows=cursor.execute('show slave status')
    try:
        ret={}
        for val,desc in zip(cursor.fetchone(),cursor.description):
            ret[desc[0]]=val
        return ret
    finally:
        conn.close()


def sendwarning(subject,msg):
    ''' send a warning message to the sysadmin '''
    s=smtplib.SMTP()
    s.connect('localhost') # or mail host if sendmail is not working
    machine=os.popen('uname -n').read().strip()
    fromaddr='mysqld-slave@'+machine
    toaddr='sysadmin@borg.org'
    header='''From: %s\r\nTo: %s\r\nSubject: %s\r\n''' % (fromaddr,toaddr,subject)
    s.sendmail(fromaddr,[toaddr],header+msg)
    s.close()

MSG='''
Slave is %d seconds behind Master and
Slave IO is running? %s
Slave SQL is running? %s
'''
def check_slave(password):
	''' check that the slave status is OK
    and send an email if it isn't '''
    slave =  getslavestatus(password)
    secs     =slave['Seconds_Behind_Master']
    iostatus =slave['Slave_IO_Running']
    sqlstatus=slave['Slave_SQL_Running']
    # this is the check
    if iostatus != 'Yes' or sqlstatus != 'Yes' or int(secs) > 0:
        msg= MSG  % (secs,iostatus,sqlstatus)
        sendwarning('mysqld slave is not working',msg)
def getpw():
    ''' keep the password in a root readonly file '''
    return open('/root/.mysqlpw').read().strip()

check_slave(getpw())

Or here is something similar using well known programs:
#!/bin/sh
[ `echo show slave status | mysql -B -u root -p  | tail -1 | awk -F'\t' '{print $11":"$12":"$33 }'` == "Yes:Yes:0" ] || {
    echo "mysqld slave failed" | /usr/lib/sendmail myadmin@borg.org
}

Other links

mysql mirroring and master master replication To fix a replication problem HERE
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
mysql> show slave status \G
# check from Slave_IO_Running and Slave_SQL_Running are set to Yes .
logos The Government of Western Australia The University of Western Australia Australian Research Council Centre of Excellence in Plant Energy Biology