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 :
On the master server
Configure the master server
Restart MySQL /etc/init.d/mysql restart
Connect to mysql's console: mysql -u root -ppassword
Create and grant permissions to replication user.
Make sure to copy this information somewhere or leave it visible
SHOW MASTER STATUS \G;
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
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
show slave status. You should see something like this
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
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
To add to crontab, you would type
make the script executable
chmod +x /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
The process goes like this (on Ubuntu, other distros may vary slightly), and assumes you've already installed MySQL on your slave:
Your slave is now all set up. If needed, you can now set up circular replication:
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