What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

Question

I am very new to Database Administration.

I face a lot of problems while setting up mysql master-slave replication.

I also face regular mysql replication troubleshooting issues.

Can anybody helps to understand how should i deal with all these?

Answer 1

I provided links to tutorials. Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial. In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master. If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up. If you use FLUSH TABLES WITH READ LOCK;, then after your backup, you will want to run UNLOCK TABLES. If you run into any problems, let me know.

Here is the tutorial that I found on howto forge, made for Redhat/CentOS : http://www.howtoforge.com/mysql_database_replication

Another tutorial that looked ok for Ubuntu http://www.srcnix.com/2010/10/14/simple-mysql-replication-with-ubuntu-master-to-slave/

Here is the configuration I used :

On the MASTER Server

Configure the master server:

vi /etc/mysql/my.cnf

[mysqld]

# bind-address = 127.0.0.1 (comment this out)
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
max_binlog_size     = 100M
expire_logs_days    = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to mysql's console: mysql -u root -ppassword

Create and grant permissions to replication user.

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'ipaddressofslave' IDENTIFIED BY 'replicationuserpassword';

Make sure to copy this information somewhere or leave it visible

SHOW MASTER STATUS \G;
mysql> show master status \G;
            File: mysql-bin.000001
        Position: 100
    Binlog_Do_DB: 
Binlog_Ignore_DB:

mysql> quit 

Dump the database to a file:

mysqldump -u root -p databasename > /tmp/databasename-backup.sql

Copy the database dump to the slave server using scp or use ftp if you like:

scp /tmp/databasename-backup.sql root@ipaddressofslave:/tmp/

On the SLAVE Server

Edit the mysql configuration:

vi /etc/mysql/my.cnf
[mysqld]

# slave server configuration
server_id           = 2

# this is optional, but I find it useful to specify where the relay logs go to control.  
# Don't forget to create the /var/log/mysql directory and give mysql rights to it.  
# chown mysql:mysql -R /var/log/mysql
# disk space
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
relay_log_space_limit = 2000M

Restart MySQL: /etc/init.d/mysql restart

Restore the backup:

mysql -u root -ppassword nameofthedatabase < /tmp/databasename-backup.sql

Connect to MySQL:

mysql -u root -ppassword

stop slave;

# master log file and master_log_pos taken from show master status above
CHANGE MASTER TO master_host='ipaddressmaster', master_port=3306, master_user='replication', master_password='replicationuserpassword', master_log_file='mysql-bin.000001', master_log_pos=100;

start slave;

Run SHOW SLAVE STATUS\G:

mysql> show slave status\G;
             Slave_IO_State: Waiting for master to send event
                Master_Host: ipaddressmaster
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.0000001
        Read_Master_Log_Pos: 100
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 1
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 17324288
            Relay_Log_Space: 17324425
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
          Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.02 sec)

Afterwards, keep in mind that replication can fail for various reasons. On the slave, you can monitor the status by running the command SHOW SLAVE STATUS \G; Or setting up a cron job to monitor the status and send emails if it fails. Get familar with the output from this command. If replication is running correctly, you should see "Slave_IO_State: Waiting for master to send event".

Once you get this setup correctly, I can provide you with a script to monitor that replication.

Here is a script to monitor the error log in MySQL. If you add the line

[mysqld]

log-error = /var/log/mysql/mysql.err

restart mysql : /etc/init.d/mysql restart

Then you can use the following script to monitor the log file. If the log changes in any way, you will receive an email notifying you that an error occured on the slave server. If you want the error log checked on a regular basis, you will need to add this script to your crontab.

Here is a sample script : /somepath/monitor_mysql_log.sh

#! /bin/sh
MAIL_TO="addressemail@something.com"

# This is the log that will be monitored.
# If any changes occur to this, then take appropriate action.
MONITORED_LOG=/var/log/mysql/mysql.err

# We will need this log to see whether any changes occured to /tmp/goreb.log
TEMP_LOG=/tmp/.mysql.err.1

# This is a 1-time command i.e. create the log file if it does nto exist.
[ ! -f $TEMP_LOG ] && touch -r $MONITORED_LOG $TEMP_LOG

[ $MONITORED_LOG -nt $TEMP_LOG ] && echo "an error occurred in mysql" | mail -s "Error on MySQL" $MAILTO

# Update $TEMP_LOG with the new modified date of $MONITORED_LOG
touch -r $MONITORED_LOG $TEMP_LOG

To add to crontab.

Make the script executable:

chmod +x /somepath/monitor_mysql_log.sh

Update crontab:

crontab -e

* * * * * /somepath/monitor_mysql_log.sh

And the script will be run every minute.

The script I provided is a script that I just quickly put together. Also, in order for your server to be able to send emails, you'd have to install something like postfix or sendmail.

Answer 2

Mysqldump is fast, but restoring dumps can be very slow for a big DB, and locking tables is not acceptable on a live site. A much better and faster way of setting up slaves is to use Percona's XtraBackup. XtraBackup imposes little load on the master, requires no locks and the restore on the slave is very fast. This mechanism does produce a complete clone of the whole database, including things like user tables, which will break some things that are set up by a stock install, such as the debian-sys-maint user, which isn't necessarily a bad thing!

As a bonus, once you know how to do this, you can use exactly the same mechanism for your daily backups. Backups are slower than mysqldump, but restores are way faster, which is just what you need if you're in a situation where you're in a panic and need to restore a backup! If you ever get a major replication error, just use this procedure to trash the slave and rebuild it; it really doesn't take long.

You will need to set up Percona's apt/yum repo for your distro, then install the xtrabackup package on both master and slave. I also strongly recommend the use of the pigz compression utility (parallel gzip, available in most standard repos) as it makes a massive difference to backup speed.

The process goes like this (on Ubuntu, other distros may vary slightly), and assumes you've already installed MySQL on your slave:

1. First, take a backup on the master: mkdir -p /var/xtrabackup; /usr/bin/innobackupex --slave-info --stream=tar --throttle=1500 /var/xtrabackup 2> /tmp/xtrabackup.out | /usr/bin/pigz -p 4 -c --best -q > /var/backups/mysql.tgz (tweak the throttle value to limit the impact of backup on live service)
2. Copy the backup file to the slave (use scp -l 400000 in order not to starve the master of network bandwidth for live clients)
3. Stop mysql on the slave: service mysql stop
4. Move the old MySQL data directory out of the way: mv /var/lib/mysql /var/lib/mysql2 (or compress it somewhere if you're short on disk space)
5. Make a new data directory and move into it: mkdir /var/lib/mysql; cd /var/lib/mysql
6. Untar the backup file into the new folder: tar xvzif /path/to/backup/mysql.tgz. Note the i option on the tar operation - it will not work without it. This will take a while if you have a big DB.
7. Run the Innobackupex tool on the extracted files: /usr/bin/innobackupex --apply-log --use-memory=6G --ibbackup=xtrabackup /var/lib/mysql. This effectively runs a crash-recovery on the files from the binary logs. This only takes a few seconds; use a smaller memory amount if on a smaller server.
8. Assuming that completes successfully, delete the backup and set ownership of the files: rm /path/to/backup/mysql.tgz; chown -R mysql:mysql /var/lib/mysql
9. Start mysql: service mysql start
10. Get the master log file name and position of the backup (note NOT the info in xtrabackup_slave_info) : cat xtrabackup_binlog_info. It will say something like mysql-bin.000916 13889427
11. Connect to MySQL and check that stuff is there.
12. Reset the replication settings using the details you got about the logs: CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='replica', MASTER_PASSWORD='r3plica', MASTER_LOG_FILE='mysql-bin.000916', MASTER_LOG_POS=13889427; (Change to match real DB server details)
13. Restart the slave: START SLAVE;
14. Check the status of the slave as it catches up with the master until 'seconds_behind_master' is 0: SHOW SLAVE STATUS\G

Your slave is now all set up. If needed, you can now set up circular replication:

1. On the slave: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; Note the log file name and position (something like mysql-bin.000031 and 17244785).
2. On the master: CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='replica', MASTER_PASSWORD='r3plica', MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=17244785;, inserting values from the slave we just looked at.
3. On the master: START SLAVE;
4. On the slave: UNLOCK TABLES;

You should now be all set with a circular replication.

As far as troubleshooting goes, Percona's toolkit has all kinds of things to help such as checksumming to spot silent corruption, lag measurement and more. The most common forms of replication corruption can be avoided by setting binlog_format = MIXED in your my.cnf. That said, in my experience replication is not generally troublesome.