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?
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 :
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
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/
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
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;
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
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="email@example.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
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.
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:
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)
scp -l 400000in order not to starve the master of network bandwidth for live clients)
service mysql stop
mv /var/lib/mysql /var/lib/mysql2(or compress it somewhere if you're short on disk space)
mkdir /var/lib/mysql; cd /var/lib/mysql
tar xvzif /path/to/backup/mysql.tgz. Note the
ioption on the tar operation - it will not work without it. This will take a while if you have a big DB.
/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.
rm /path/to/backup/mysql.tgz; chown -R mysql:mysql /var/lib/mysql
service mysql start
cat xtrabackup_binlog_info. It will say something like
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)
SHOW SLAVE STATUS\G
Your slave is now all set up. If needed, you can now set up circular replication:
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;Note the log file name and position (something like mysql-bin.000031 and 17244785).
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.
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.