MySQL Databases to Separate Disks

You may have a database server which started out small, with all its databases stored on the same disks, that is now experiencing severe storage I/O bottlenecks. With so many heavily accessed databases on the same storage device your queries are timing out while waiting for response from disk. And despite all your efforts in optimizing the databases and queries, there has come a time where the disks just can’t keep up. For this type of scenario, you need to spread your load across more storage devices.

Sadly, MySQL doesn’t have an option to configure separate storage paths for each database like more enterprise database servers do. The solution is to symbolicly link your databases from the new storage device to the MySQL data home directory

As long as the new location has the proper ownership and SELINUX context, this fools MySQL into believing your migrated databases still exist in the data home directory.

Objectives

1. Prepare new storage for databases.
2. Moving I/O heavy databases to separate storage.

Scenario

We have a MySQL 5.1 server hosting five databases on a single disk. One of the five databases is flooding the disk with I/O due to its work profile and needs to be moved to separate storage. The databases information is shown below.

The storage for the the database.

Preparing Your new Storage

1. Attach the new storage devices to the server.
2. Create a single partition and format it with a filesystem.Create a root directory which will be used to contain mount points for your new storage.

   mkdir /Databases

3. For each database being migrated, create a folder for its storage device to mount to.

   mkdir /Databases/webapp02

4. Set the SELINUX context type of the new directories to mysqld_db_t to allow MySQL access to them.

   chcon -r -t mysqld_db_t /Database

5. Modify fstab so that

Copying Databases to New Storage

1. Copy your database’s files to the new location, using cp and -preserve=all to maintain ownership and SELINUX contexts.

   cp -r -preserve=all /var/lib/mysql/mydb1 /new-mydb1-location

2. Verify the SELINUX context is applied correctly to the directories and files.

   ls -lZ /Databases && ls -lZ /Databases/*

Point MySQL to New Database Locations

1. Stop the MySQL daemon.

   service mysqld stop

2. Navigate to the MySQL data home directory, which is /var/lib/mysql by default.
3. Delete the databases directories for the databases being migrated, making note of the directory names. They’ll be needed in the next step.
4. Create soft links to the storage of each database being migrated. The link file names must must the name of the database’s directory name.

   ln -s /Databases/myappdb1 myappdb1

5. Repeat the process for every databases being moved.
6. After all databases have been migrated, restart the MySQL daemon.
7. If all goes well, MySQL should start correctly. If it does not, check the system logs for Selinux context errors.