Moving MySQL data and log files to separate disk drives

I’ve setup a new production system for a department here on campus. The back-end system is running MySQL and I wanted to have the OS, MySQL data and MySQL log files all on separate disks. The system I’m running consists of the following:

• Ubuntu Server 10.04 LTS
• MySQL 5.1

I was able to accomplish this by doing the following procedure.

Install the new hard drives

I’m running the system on a VMWare vSphere 4.x cluster, however this should work if you’re running on a physical system as well. You’ll first need to physically (or virtually in my case) install the hard disk drives. Next we’ll need to setup the disks on the system. I followed the instructions for installing a new hard drive on the Ubnutu documentation site. I ended up with the following:

/media/mysql_data = for my data files
/media/mysql_logs = for my log files

Configure MySQL for the different drives

First we’ll need to stop the MySQL database daemon sudo service mysql stop

Give the mysql user ownership of the new directories:

sudo chown -R mysql:mysql /media/mysql_data
sudo chown -R mysql:mysql /media/mysql_logs

Copy (just in case something goes awry) the database data files and directories to the new directory, then make sure the mysql user has ownership of these files:

sudo cp -rp /var/lib/mysql/* /media/mysql_data
sudo chown -R mysql:mysql /media/mysql_data/*

Remove the log files from the data directory

sudo rm -f /media/mysql_data/*log*

Copy (just in case something goes awry) the database log files and directories to the new directory, then make sure the mysql user has ownership of these files:

sudo cp -rp /var/lib/mysql/*log* /media/mysql_logs
sudo chown -R mysql:mysql /media/mysql_logs/*

Update the MySQL daemon configuration with the changed location

sudo nano /etc/mysql/my.cnf

Find the following statement “datadir” and change it to the new location:

datadir = /media/mysql_data

I also added the following in InnoDB area under the [mysqld] section # Default data directory for data files

innodb_data_home_dir = /media/mysql_data

# Set buffer pool size to 50-80% of your computer’s memory

innodb_buffer_pool_size=896M
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /media/mysql_logs

Save the file and exit.

Configure Ubuntu AppArmor protection for MySQL

Next we need to update the apparmor file to tell the system about these new file locations

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Add the new location:

/media/mysql_data/ r,
/media/mysql_data/* rwk,
/media/mysql_logs/ r,
/media/mysql_logs/* rwk,

Modify the following file:

sudo nano /etc/apparmor.d/abstractions/mysql

Add the following line

/media/mysql_data/mysql/mysql.sock rw,

Restart the services

Now we just need to restart the AppArmor and MySQL services

sudo /etc/init.d/apparmor restart
sudo service mysql start