MySQL - Moving datadir

For the last few hours, I had been piddling around with the folder hierarchy on the host that runs this blog. I use an Amazon EBS backed instance for this job. I started off with a simple setup, where I had all the data on one single EBS volume, which included all the MySQL databases along with the webroot. If I borked this setup, there was no way I could painlessly restart a new instance and have the website up and running within half an hour. Switching the web root directories for Apache is pretty easy, and I had already done that a few times. However, I had never tried switching the data directory for MySQL before. I always used the default location in Ubuntu, /var/lib/mysql. I also had to consider the space issue. ThinkUp’s database turns into a beast if you’ve been running it for a few months. With my paranoid backup policy, there was a high chance I would run out of space on the root EBS volume pretty soon.

I decided to move the data directory to a dedicated EBS volume. I Googled for the procedure to do that, and came across multiple blog posts, which explained the same thing ( 1 and 2 ). The steps are roughly the same, but they missed one important step. InnoDB database files need special attention. Here are the steps that worked for me:

• Stop the MySQL server:

  sudo service mysql stop

  view raw step-1.sh hosted with ❤ by GitHub

• Prepare the new location for the data directory. In my case, it meant creating a new EBS volume, attaching it to an instance, formatting it to ext3 and then mounting it to /mnt/data.
• Replicate the entire MySQL data directory to the new location. This includes the folders for each of your databases as well as the ibdata* and ib_logfile* files. Being a Ubuntu instance, I also had a Debian specific file in the folder. Choose a tool of your choice. I rsync’d the two locations with

  sudo rsync -av /var/lib/mysql /mnt/data/mysql

  view raw step-3.sh hosted with ❤ by GitHub
• Ensure that the /mnt/data/mysql folder is owned by the mysql user and group and has the correct permissions. Rsync normally takes care of this.
• Open /etc/mysql/my.cnf and update the datadir variable to point to the new location:

  	sudo vim /etc/mysql/my.cnf
  	datadir=/mnt/data/mysql

  view raw step-5.sh hosted with ❤ by GitHub
• Update the AppArmor profile for MySQL. Edit /etc/apparmor.d/usr.sbin.mysqld and modify every line beginning with /var/lib/mysql to /mnt/data/mysql .
• Reload AppArmod profiles with

  sudo /etc/init.d/apparmor reload

  view raw step-7.sh hosted with ❤ by GitHub
• Start the MySQL server:

  sudo service mysql start

  view raw step-8.sh hosted with ❤ by GitHub

This set of steps worked for me. YMMV, check the log files in /var/log/mysql/ if it refuses to start at the last step.