Home arrow Guides arrow Move MySQL Data Directory to a New Location on Other Partition
Move MySQL Data Directory to a New Location on Other Partition PDF
Thursday, 19 January 2012
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


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

Last Updated ( Thursday, 19 January 2012 )
< Prev   Next >

Other BSD Systems





Best BSD firewall?