Posts Tagged ‘datadir’

move Mysql’s datadir on ubuntu

March 8th, 2011

Mysql’s datadir (on ubuntu) by default is /var/lib/mysql. Sometimes, if your DB grows bigger and bigger, and finally exceeds the limit of /var partition, you may think of moving it to some other place. Here are the steps.

  1. stop the server
    sudo service mysql stop

    or

    sudo /etc/init.d/mysql stop
  2. copy data to the new folder
    For example, you want to set the new folder in /home2/: 

    sudo cp /var/lib/mysql /home2/

    Now /home2/mysql is the new datadir. Of course, you can change it to whatever name you want.

    Here I deleted the log file ib_logfile* by:

    sudo rm /home2/mysql/ib_logfile0

    Maybe you also want to change the original datadir to some other name to make sure it won’t be in effect any more.

    sudo mv /var/lib/mysql /var/lib/mysql.bak
  3. change the permission of the new directory
    Make sure the owner of /home2/mysql and all its subdirectories is mysql:mysql. When necessary, use chown to change the permission:sudo chown -R mysql:mysql /home2/mysql 

    Also make sure the permissions of the new datadir and its subdiretories are the same as those under /var/lib/mysql. In my experiment, the permission of table files in each database was changed to 400 when copying, so I had to use chmod to correct them:

    sudo chown -R me:me /home2/mysql;
    sudo chmod 660 /home2/mysql/*/*;
    sudo chown -R mysql:mysql /home2/mysql

    The reason why I changed the owner to me (my username on my computer) at the beginning and change it back to mysql at last is so that I can use the wildcast * to change all files’ permission at once.

  4. modify my.cnf and point datadir to the new directory
  5. sudo vi /etc/mysql/my.cnf

    Find datadir and change it to:

    datadir=/home2/mysql

    It seems in Mysql 4 you also need to change the basedir and socket accordingly, but I’m not sure.

  6. add the new diretory into the AppArmor configure file
  7. This part is special on some Linux distributions like Ubuntu >= 7.04. AppArmor is a Linux Security Module implementation of name-based access controls. It confines individual programs to a set of listed files and posix 1003.1e draft capabilities.

    You can use

    sudo aa-status

    to check whether mysql is confined by AppArmor. If so, use

    sudo vi /etc/apparmor.d/usr.sbin.mysqld

    to add the new datadir at the end of the file.

  8. restart apparmor service
    sudo /etc/init.d/apparmor reload
  9. start mysql server
    sudo service mysql start

    or

    sudo /etc/init.d/mysql start

    Refer to /var/log/mysql/error.log to see if there are any errors.

  10. do some tests.
    List all the database, select some records from a table, create a new table and see whether it’s in the new datadir, insert some records, delete them, and whatever.
    WARNING: you may need to run Mysql with the new datadir for a few days before deleting the old datadir.