Move MySQL Data Directory to a New Location on Other Partition

Move MySQL Data Directory to a New Location on Other Partition

Sometimes /var partition is to small and we need a bigger one to store our MySQL databases. In that case the best way if we cannot create a new partition is to move MySQL data directory from our FreeBSD server to another partition.

In order to do that follow next steps.

Step 1. Backup MySQL Databases
------------------------------------------------
First we will backup our MySQL databases using mysqldump command:

mysqldump --opt --user=mysql_user --password=mysql_password --default-character-set=latin1 --add-drop-table db_name > db_name.sql

where our variables are:
mysql_user - replace this with your mysql username
mysql_password  - replace this with your mysql password for your mysql_user user
db_name - replace this with your database name.
db_name.sql - replace this with file name where you will save your database backup.

Note: If you have multiple databases run this command for all your databases replacing db_name and db_name.sql.


Step 2. Stop the MySQL Database Server
--------------------------------------------------------

/usr/local/etc/rc.d/mysql-server stop


Step 3. Edit your my.cnf file and at section [mysqld] add the following line
-------------------------------------------------------------------------------------------------

edit /usr/local/etc/my.cnf

Go to [mysqld] section and there add:

datadir = /home/mysql-data

where:

mysql-data is your new data dir.

If you do not have a /usr/local/etc/my.cnf file you can copy one from /usr/local/share/mysql choosing from:
my-large.cnf, my-small.cnf, my-medium.cnf or any .cnf file, according to your needs.

For example you could use my-medium.cnf

  cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf


Step 3. Copy MySQL Databases from /var/db/mysql to /home/mysql-data dir
------------------------------------------------------------------------------------------------------

If you do not have rsync installed you can install it first from ports with:

cd /usr/ports/net/rsync
make install clean ; rehash

Then backup your mysql databases with:

rsync -avc /var/db/mysql/ /home/mysql-data/


Step 4. Rename your old database directory
-----------------------------------------------------------

mv /var/db/mysql /var/db/mysql.old


Step 5. Create a symlink /var/db/mysql to our new location
-----------------------------------------------------------------------------
Please note that this is useful to not break functionality of some scripts. We will also set ownership of this symlink to mysql.

ln -s /home/mysql-data /var/db/mysql
chown mysql:mysql /var/db/mysql


Step 6. Start MySQL Server
-------------------------------------

 /usr/local/etc/rc.d/mysql-server start