Feb 13, 2012

MySQL Replication Complete Guide

This is probably my shortest post

Mats Kindahl MySQL Replication guide is short, concise and includes (almost) everything you need.

Keep Perfomring,
Moshe Kaplan

Feb 11, 2012

Do You Need More Disk Space for the MySQL Server at Amazon Web Services (AWS/EC2)?

If you deal with large databases, you will probably need more than the provisioned storage of the default Amazon AMI (8GB).

UPDATE: Since in many cases you may need to swap between the current disk and a new larger one, I've added in green comments regarding this case.

The following 10 steps guide will help you expand your disk meet your business needs:
  1. Create a new storage volume at the Amazon console (or using AWS API). Make sure it is big enough to accommodate storage, log files, bin files and future growth. Notice! verify the new drive is in the same availability zone as the target instance.
  2. Write down the mount name you chosen. For example: /dev/sdf.
  3. Mount the disk to your system according to Chris Dean post:
    1. sudo mkfs.ext4 /dev/sdf where /dev/sdf is the mount name you chose.
  4. Shutdown the MySQL daemon: sudo /etc/init.d/mysql stop
  5. Unmount the current disk umount /dev/sdf
  6. Move your current MySQL data directory to a temporary directory: sudo mv /var/lib/mysql  /var/lib/mysql.old or mount the previous disk to mysql.old by creating a new folder and changing the /etc/fstab as described bellow.
  7. Recreate the MySQL data directory and provide the needed permissions:
    1. sudo mkdir /var/lib/mysql
    2. sudo chown mysql:mysql  /var/lib/mysql
  8. Mount the new disk to the new directory:
    1. Add another line to /etc/fstab (sudo vi /etc/fstab): /dev/sdf /var/lib/mysql ext4 defaults 1 2
    2. Mount the disk: sudo mount /var/lib/mysql
    3. Verify everything is Okay with df
  9. Move the files from the temporary directory back to the new data directory sudo mv /var/lib/mysql.old/* /var/lib/mysql
  10. Remove the old directory: sudo rmdir /var/lib/mysql.old and unmount the old disk umount /dev/sdf
  11. Restart you MySQL: sudo /etc/init.d/mysql start
xfs today is the new kid in block w/ better performance w/ SSD disks and already stable.
Use the following commands if you choose it:
sudo apt-get install -y xfsprogs
sudo mkfs.xfs -f /dev/xvdf
vi /etc/fstab
/dev/xvdf /var/lib/mysql/ xfs defaults 0 2

Bottom Line
A combination of cloud knowledge, Linux sysadmin, MySQL expertise and careful work will help you go through the scale and growth challenges.

Keep Performing,

Feb 5, 2012

MySQL Replication Deadlock Detection and Recovery (The code inside)

The best practice to handle deadlocks (after eliminating them from the code) is recovering from them. Unfortunately, if you ever came across the "SHOW SLAVE STATUS\G" Error 'Deadlock found when trying to get lock: try restarting transaction', you probably know that MySQL Replication does not recover automatically from deadlocks.

Therefore, I implemented a Nagios plugin. This Nagios plugin detects the MySQL Replication deadlock and recover from it by restart the slave ("STOP SLAVE; START SLAVE;"). 

Brief: This a shell script (/bin/sh) that connects to a specified MySQL slave database, check the replication status for Deadlock errors + Automatically recover from the state by performing "SLAVE STOP; SLAVE START;"
Required parameters: host, user, password.
If no Deadlock is detected, "OK" is returned
O/w, a message is printed, slave restarted and Warning state is returned.

Bottom Line
Fast, Simple and very effective and now available from Nagios Exchange.

Download the MySQL Replication Deadlock Recovery Nagios Plugin.

Suggestions are welcome,

Keep Performing,
Moshe Kaplan


Intense Debate Comments

Ratings and Recommendations