Databases grow over time, and sometimes it’s necessary to add disk space to a server to accommodate that growth. Fortunately, moving your database is a fairly simple process. The following is tested on Ubuntu 12.04LTS, but, by locating directories and files, should work on other flavors of Linux.
The first step is to stop any incoming data to your database. This might involve editing the crontab to stop scheduled processes that touch your database or stopping programs that are inserting or modifying data, such as a web server or Syslog software. This is the trickiest part, and will probably be the most time-consuming part of the process.
It’s always a good idea to back up your data when performing operations like this. Use the following to do that.
mysqldump -uroot -p[root_password] [database_name] > dumpfilename.sql
If you don’t have enough room for the backup, simply change the path to a drive or mount with sufficient space.
The next step is shutting down MySQL. Connect to your server using puTTY or SecureCRT, and run the following commands.
service mysql stop
Now we’re ready to move the database files. Just replace ‘new_vol’ portion of the following command with the name of your newly mounted volume, and ‘path’ with the directory you created (if any). On other operating systems, the directory may be different. You’ll just need to find the ‘ibdata’ files.
mv /var/lib/mysql /new_vol/path
Now that we have the files moved, we need to let Mysql know where their new home is. The following steps will edit the Mysql configuration file using the Vim text editor. Again, this may differ on other operating systems. Just use the find command to locate the file.
Search for the following line by typing in ‘:datadir’ and pressing enter.
datadir = /var/lib/mysql
Using the ‘l’ key, move the cursor to the right until it is over the ‘v’ in var. Typing in a capital ‘D’ will delete the text to the end of the line, and the letter ‘i’ will turn on insert mode, allowing you to type in your new path.
datadir = /new_vol/path
You will also need to let AppArmor know where the database files have been moved to.
Search for any occurrences of the MySQL path by typing ‘: MySQL’ and hitting enter. Pressing the letter ‘n’ will take you to the next occurrence of the string. Once you have located them, change any paths in the file from
/var/lib/mysql to /var/lib/mysql.
Restart MySQL and watch the /var/log/mysql/error.log to make sure there are no issues with the new database location. The command ‘tail -f /var/log/mysql/error.log’ will show the last several lines of the file and any incoming. Once you are satisfied that no more errors are coming in, hit ‘Ctrl-C’ to exit tail mode.