Having your MySQL data on a separate partition is important, why?

Having your MySQL data on a separate partition is important because when your root partition fills up and no space is left, your system won’t be able to boot and might crash when running, you then have to boot from a recovery disk to remove files before you can boot on your normal system again.
This is critical for servers that you can’t allow to be down for an hour or two!

The solution

The solution is easy, you just move your MySQL data to a separate partition, and this problem won’t occur any longer, the only thing that happens when you run out of space, is that the MySQL can’t write any new data to the database, which is better than the entire system crashing and being unable to boot.
In this guide i will show you how to make the changes needed to get this problem resolved.

How to do it

Below I will show you the commands and screenshots of what you need to do, to make this change on your MySQL server.
In this guide I will demonstrate how to do it on a Debian Linux (Wheezy 7.1) server. But it should be the same for Ubuntu servers and other Debian versions, but on other Linux distro’s you might have to use different commands)

Add the hard disk

First you add the hard disk to your system, to do this you sadly have to turn off the server.
Turn off the server, and add the seconds hard disk, if you are running it in a virtual environment like VMware, you have to add a new disk to the virtual machine running your MySQL.
Now start your server up again

Find the new hard disk

Once your server is started up again, log in as root or another user with root privileges.
Run the following command to see what hard disks you have connected and the system has found:

ls /dev | grep sd

The output should look something like this:

You probably have two disks called “sda” and “sdb”, there will also be some called “sda1” and maybe “sda2” these are partitions, the ones without numbers are the disk’s
As you can see in the screenshot above, i have 2 disks, called “sda” and “sdb”
sda = Your main hard disk that contains your root partitions where your Debian, MySQL and other software is installed
sdb = The hard disk you just added, note that it does not have any numbers behind it
If you have more than two hard disks in your server, there will be more in this list, incremented by the last letter, e.g. sdc, sdd, sde and so on.
In this guide, you have to replace “sdX” with the hard disk’s letters you just added that contains no partitions, in this example it would be “sdb”.
You can also use the following command to view some more information about the hard disk

fdisk -l

It should output something like this:

As you can see, I have a lot of partitions on sda, but sdb is empty. The hard disk I added (sdb) is 10 GigaBytes in size – if this was for a production MySQL, I would recommend a little bigger because hard disk space is so cheap today. But since this is only for the guides purpose, 10GB is more than enough.

Create the partition on the new hard disk

Right now you only have a RAW hard disk on your system, with no data, no partition table and no partitions, so you have to create those things before you can use the hard disk.

Create the partition table and partition

Run the following command

fdisk /dev/sdX

You should see the following output, and you are not returned to the shell, since you just run a program and are still running it.

You now have to type “n” and press Enter to create a new partition,
Then the output should look like this:

Now type “p” and press Enter to choose that the partition has to be a primary partition.
Now type “1” and press enter, to choose the number for the partition. Since it’s the first partition on the harddisk you have to type “1”
Now press Enter again to verify the default “First sector” of “2048”.
Press Enter again to verify the default “Last Sector”. By choosing the default, the partition will get the space of the entire hard disk. Since we are only creating one partition this is the way we want it.
Now type “write” and press Enter to write the changes to disk.
At the end if you look up at the terminal history it should look this this:

If you type the command

ls /dev | grep sd

again you should now see that “sdb1” has been created, like this:

Create the filesystem

You now have your hard disk, partition table and partition, only a few more commands are needed until you can use the hard disk.
You have to create the filesystem, since the partition is still empty.
There are many file systems out there, and they all have their advantages and disadvantages. “ext4” seems to work out pretty well for me and I like it, so that’s what i’m going to use in this guide.
I also recommend ext4 for MySQL data, because it’s a little bit more reliable than some other file systems.
Run the following command to create the filesystem:

mkfs.ext4 /dev/sdX1

This might take a minute or two, depending on how big your partition is, mine is only 10GB so it took less than 5 seconds.
The output should look like this:

The filesystem has now been created, only one step left!

Create a mount point

To use the new partition, you have to “mount” it, this is what you do to tell the the server where and how to access the partition. You do this by first creating a new folder where the partition has to be mounted later on.
In this example, I will use “/mysql_data”.
Run the following command to create the directory

mkdir /mysql_data

Now you have a new empty directory with the name and path “/mysql_data”, so the next step is to tell the server to mount the partition on that folder.
You do this by editing this file: /etc/fstab
Edit the file by opening it in your favorite text editor, in my case this is “Nano” which is default on Debian servers, but you might have to use “Vi” on other servers. This guide will only cover how to do it using “Nano”.
Run the following command:

nano /etc/fstab

You should see the following in fullscreen:

Note that because I have a lot of partitions on my first hard disk, I probably have a lot more lines than you.
Move the cursor to the bottom by using the arrow keys, until you are on a empty line.
Now type the following (the spaces between the words are tabs, or space it does not matter which one as long as you use one of them):

/dev/sdX1 /mysql_data ext4 defaults,errors=remount-ro 0 1

The “errors=remount-ro” makes sure that if any errors occur the partition will be remounted in read-only mode, this makes sure that you almost always are able to read your data.
Press CTRL+o(letter left of “p”) on your keyboard and press Enter to save the file, then press CTRL+x to exit nano
By adding the mount point to “etc/fstab” you make sure that the server remounts it upon reboot.
You are now back in your terminal, so type the following command to mount the newly created mountpoint:

mount /mysql_data

There is no output on this command, unless there are any errors.
You now have your harddisk partitioned, and mounted, and only need to move your MySQL data in the next steps!
You can view the files on your new partition by running the following command:

ls /mysql_data

There should not be anything else than “lost+found” since you have not created any data on the partition yet.

Move MySQL data

You now have to move the data, and tell the MySQL server where the new location is.

Stop MySQL server

Before you do anything below, you have to stop the MySQL server – You MySQL server will be offline and unavailable in this period and I can’t tell you how long it will take.
If you have any webpages or other public content that rely on your MySQL server, you have to plan ahead before you do the next steps.
Depending on how big your database is, this can take a long time, it will take the amount of time i takes your hard disks to copy the data.
When you are ready to stop the MySQL server, run the following command:

service mysql stop

It should output “OK” like in the picture below:

Locate the current MySQL data directory

First you have to find where your MySQL data is now, you can do this by running the following command

cat /etc/mysql/my.cnf | grep datadir

The output should only be one line showing you where your MySQL data is located, for me it’s at “/var/lib/mysql” as you can see in the screenshot below:

You have to remember this path, since you will need it in the next steps.

Copy all the MySQL data to the new disk

I always choose to copy, and not move. This way I can always go back in case something goes wrong!
I then delete the old data, when i have tested that everything is working.
To copy the data, run the following command. This is the part that might take a long time to finish depending on the size of your database:

cp -rp /var/lib/mysql/* /mysql_data

When it’s done, you should be returned to the terminal, and you are allowed to write commands again. The command above does not give any output unless there are errors.
Run the following command to verify that the data is on the new harddisk:

ls /mysql_data

It should look something like this:

Tell MySQL that the data has moved

You have to use “Nano” or “Vi” again to edit the MySQL configuration file. Run the following command:

nano /etc/mysql/my.cnf

You should now have the following image in fullscreen:

There are a lot of lines in this file, and you have to find the ones that look like this:

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

Here you have the line that starts with “datadir”, this is the one we need to change.
Change it from:
datadir = /var/lib/mysql
To:
datadir = /mysql_data
If you use InnoDB you might have to change other lines in this file, containing “/var/lib/mysql” to “/mysql_data”
Now press CTRL+o (the letter left of “p”) and Enter to save, and CTRL+x to exit nano.

Move the old data

Move the old data, to make sure that it is not used by mistake.
Run the following command:

mv /var/lib/mysql /var/lib/mysql.backup

Start the MySQL server

The data has been moved, mysql have been informed of the new location and everything should be ready to start up the MySQL server again.
Run the following command to start the MySQL server

service mysql start

It should output “OK” like in the screenshot below:

If there were no errors, and it did not fail, then everything went like expected, and you are now up and running on your new hard disk!

Remove old data

When you are 100% sure all your data has been copied to the new location, and everything is running OK including your websites or other services that use your MySQL database, then you can delete the old database data with the following command:

rm -r /var/lib/mysql.backup

That’s it.. You’re done 🙂

“start: Job failed to start” error

If you get the “start: Job failed to start” when trying to start the MySQL service using the command

service mysql start

Marco was so kind and post a solution for this problem in the comments, and I will include it here too.
The reason for this error is that AppArmor is running, this is default on some Ubuntu installations, SLES and other Linux distributions (not on Debian or CentOS)

How to fix it

Edit the AppArmor file using nano or your favorite text editor, the file is located at

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

You need to modify any /var/lib/mysql reference (the old directory your MySQL data was located, if this was different in the previous steps, then it will be different than this example too) to your new /mysql_data (If you mounted the new harddrive elsewhere, you should change it to the correct new path for your MySQL data like in the previous steps)
Now try starting the MySQL service again, and it should work.

service mysql start