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

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

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

Other BSD Systems

OpenBSD

Misc

Solaris

Polls

Best BSD firewall?