How to move mysql database to another drive or partition

So you need to physically move around your mysql databases, typically because you want to put them on a another partition or hard drive, or on some network device ? This is how you can do it.

My box runs Ubuntu Server 7.10 (Gutsy Gibbon), but the following should apply to any recent Debian or Ubuntu distribution.
First stop the mysql service :

root@box:~/# /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]

Then go to your current mysql data directory, by default in Debian / Ubuntu it should be /var/lib/mysql. Check that your databases are there (in this example I have 2 bases – the default ‘mysql’ base and a user-created ‘wpdb’ base) :

root@box:~/# cd /var/lib/mysql
root@box:~/# ls
total 21M
-rw-rw—- 1 mysql 10M 2008-05-01 14:39 ibdata1
-rw-rw—- 1 mysql 5.0M 2008-05-01 14:39 ib_logfile0
-rw-rw—- 1 mysql 5.0M 2008-04-27 20:57 ib_logfile1
drwxr-xr-x 2 mysql 4.0K 2008-04-27 20:57 mysql
-rw——- 1 root 6 2008-04-27 20:57 mysql_upgrade_info
drwx—— 2 mysql 4.0K 2008-04-28 19:28 wpdb

Create a new directory for your data (in this example, the /var/www directory which is located on another partition) and give ownership on it to the mysql user :

root@box:~/# mkdir /var/www/mysql_datadir
root@box:~/# chown -R mysql:mysql /var/www/mysql_datadir

Copy your databases to the new dir and update ownership if needed. Only move the databases dirs, don’t touch the other files.

root@box:~/# cp -r mysql /var/www/mysql_datadir/
root@box:~/# cp -r wpdb /var/www/mysql_datadir/
root@box:~/# chown -R mysql:mysql /var/www/mysql_datadir/*

Then update your my.conf file to make it point to the new dir :

root@box:~/# nano /etc/mysql/my.conf

Find the following statement :

datadir = /var/lib/mysql

and update with the new location :

datadir = /var/www/mysql_datadir

And finally restart the mysql service

root@box:~/# /etc/init.d/mysql start
* Starting MySQL database server mysqld [ OK ]

When restarting, mysql re-created files ibdata1, ib_logfile0, etc. in the new data dir.
If everything went OK, you can now remove the original dir. Voilà !

30 Replies to “How to move mysql database to another drive or partition”

  1. I have a problem relocating MySQL’s DB location. Although I followed the directions in your How-to, the mysql daemon won’t start. Neither will a sym link to the new database directory work. I’m running Ubuntu Hardy 8.04 Linux version 2.6.24-18-generic and MySQL Ver 14.12 Distribution 5.0.51a, for debian-linux-gnu (x86_64).

  2. I have the same problem with this procedure. I am running the latest ubuntu and apache2 with mysql.

    I have yet to find any procedure that works on ubuntu. I may have to fall back to another linux that has a normal layout.

    The only error I get is it states failed from the /etc/init.d/mysql start

  3. The users that have been having lots of problems with the moving need to follow the following procedure:

    stop mysql

    mkdir

    chown mysql:mysql

    cp -r -p /var/lib/mysql/*

    edit my.conf and update the datadir to your new location

    check to see if you have this file /etc/apparmor.d/usr.sbin.mysql

    If you have this file you must also update this file to reflect your new location. You should find several lines that had the old location in this file, change them to point to your new location. I also found that I needed to reboot after this to have them take effect.

    NOTE: Dealing with your pid file and port file. If you modify these in your my.conf you will also need to look at debian.cnf and also update again the /etc/apparmor.d/usr.sbin.mysql file for this.

    Remember reboot first.

    I am running Ubuntu 8.04 server and the above worked out for me.

    This all gets into the security of files (access rights). If you are running another OS you may be having problems with access via the new linux security subsystem, I can not help you on this one just the Ubuntu’s method for this security.

    Have a nice day

    Don Long

  4. I’ve the same problem like skramstad. In the syslog, I found this:

    Oct 14 13:26:24 lnv-90034 kernel: [ 8569.506008] audit(1223983584.497:61): type=1503 operation=”inode_create” requested_mask=”w::” denied_mask=”w::” name=”/local/mysql/data/ibdata1″ pid=8679 profile=”/usr/sbin/mysqld” namespace=”default”
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: 081014 13:26:24 InnoDB: Operating system error number 13 in a file operation.
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: InnoDB: The error means mysqld does not have the access rights to
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: InnoDB: the directory.
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: InnoDB: File name ./ibdata1
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: InnoDB: File operation call: ‘create’.
    Oct 14 13:26:24 lnv-90034 mysqld[8681]: InnoDB: Cannot continue operation.
    Oct 14 13:26:24 lnv-90034 mysqld_safe[8687]: ended

    The fact is, that the mysql user HAS full rights on /local/mysql/data. Even if I give chmod 777 to this directory, the same error occurs.

  5. remember to update apparmor on hardy:
    nano /etc/apparmor.d/usr.sbin.mysqld
    add the new location:
    /var/www/mysql_datadir/ r,
    /var/www/mysql_datadir/** rwk,

    restart apparmor:
    /etc/init.d/apparmor restart

  6. I’m using an NAS cifs mounted as /var/www/mysql_datadir

    When I get to the part:

    root@box:~/# chown -R mysql:mysql /var/www/mysql_datadir

    I get these returns:

    chown: changing ownership of `/var/www/mysql_datadir/trashbox’: Permission denied
    chown: changing ownership of `/var/www/mysql_datadir’: Permission denied

    I was under the impression that the chmod -R would copy the current mysql permissions to the new dir – is this incorrect?

    Thanks…

    1. The chown command is meant to grant full permissions to mysql on the new data dir.
      Apparently you are not authorized to grant those permissions. First make sure you actually issued the command as root (no offense intended but hey, that would be the most obvious problem). Also, what filesystem do you use on your NAS ? It should be one that supports permission setting (no FAT32 crap)

  7. THANK YOU EVER SO MUCH!! I have been fighting with this issue for MONTHS. Your directions, including the configuration of Apparmor finally worked! Every other source I have looked at on this have missed the steps for Apparmor, which caused mysqld to not work. I was able to verify this by starting mysql after the Apparmor steps, then going into phpMyAdmin and creating a table, then comparing the new and old directories to verify it was in the right place. THANK YOU THANK YOU THANK YOU!!

  8. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    emember to update apparmor on hardy:
    nano /etc/apparmor.d/usr.sbin.mysqld
    add the new location:
    /var/www/mysql_datadir/ r,
    /var/www/mysql_datadir/** rwk,

    restart apparmor:
    /etc/init.d/apparmor restart
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    needed this for ubuntu 8.10

  9. Don Long’s advice worked for me. Thanks

    @imwaltraud
    “You should also move the ibdata1, ib_logfile0 and ib_logfile1 files. If you don’t all your InnoDB tables will be gone.”

    Those files get recreated at the new location on rebooting.

  10. root@ks368546:/home/admin/mysqldb# /etc/init.d/apparmor restart
    grep: /proc/modules: No such file or directory
    ls: nie ma dost\u0119pu do /sys/module/apparmor: No such file or directory
    grep: /proc/modules: No such file or directory
    ls: nie ma dost\u0119pu do /sys/module/apparmor: No such file or directory
    grep: /proc/modules: No such file or directory
    ls: nie ma dost\u0119pu do /sys/module/apparmor: No such file or directory
    FATAL: Could not load /lib/modules/2.6.28.1-xxxx-std-ipv4-32/modules.dep: No such file or directory
    Loading AppArmor module: Failed.

  11. no need to tar the files

    why do you go the complex way if you have the simple way?

    sudo cp -rp anything to/new/location

    the key is the -p parameter in the cp command, that preserve file permissions and the r made it recursive, so each file is copied recursively preserving its own permissions

    but, i have to admit to tar the files will give you an extra backup

  12. what meaning `wpdb’ ?

    root@smm:/var/lib/mysql# ls -l
    total 4130784
    drwx—— 2 mysql mysql 4096 2009-11-02 19:53 backup
    -rw-r–r– 1 root root 0 2009-07-19 19:47 debian-5.0.flag
    -rw-rw—- 1 mysql mysql 3491758080 2009-12-03 09:30 ibdata1
    -rw-rw—- 1 mysql mysql 367001600 2009-12-03 09:30 ib_logfile0
    -rw-rw—- 1 mysql mysql 367001600 2009-12-03 09:30 ib_logfile1
    drwxr-xr-x 2 mysql root 4096 2009-07-20 02:49 mysql
    -rw——- 1 root root 8 2009-07-30 03:53 mysql_upgrade_info
    drwxr-xr-x 2 root root 4096 2009-12-03 21:23 smm_server
    root@smm:/var/lib/mysql# mkdir /var/www/mysql_datadir
    root@smm:/var/lib/mysql# chown -R mysql:mysql /var/www/mysql_datadir
    root@smm:/var/lib/mysql# cp -r mysql /var/www/mysql_datadir/
    root@smm:/var/lib/mysql# cp -r wpdb /var/www/mysql_datadir/
    cp: cannot stat `wpdb’: No such file or directory

  13. Worked like a charm, especially when moving the database(s) to a RAID cluster. Thank you very much. A link to this is going to be put up on my blog!

  14. I moved the databases over, but none of the user privileges came over. What files need to me moved to get the old permissions?

  15. Thomas, what are your thoughts of creating a symlink back to the /var/lib/ directory. Ex: If I created new mount called /sql and just copied /var/lib/mysql to /sql changed ownership and created a simlink back from /sql/mysql to /var/lib/mysql. This way you wont need to update the my.cnf.

  16. I had to make a change in /etc/apparmor.d/abstractions/mysql too, otherwise the admin client couldn’t connect on localhost. This is with Ubuntu 10.04 Lucid Lynx desktop, not server. YMMV.

    @Don: tried that, couldn’t make it work, probably because apparmor wouldn’t have any (the messages in the log indicates that it sees the raw directory, not the symlink).

  17. “imwaltraud says:
    February 3, 2009 at 9:01 pm

    You should also move the ibdata1, ib_logfile0 and ib_logfile1 files. If you don’t all your InnoDB tables will be gone.”

    I did have to do this on RHEL4. Very helpful tip, thanks.

  18. On Ubuntu 10.10 server, I also had to modify the script used by the “service start mysql”/”start mysql” command. The script is /etc/init/mysql.conf. The changes were
    1. Replaced the only instance of the default data dir (/var/lib/mysql) with my new location
    2. Changed the line “exec /usr/sbin/mysqld” to “exec /usr/sbin/mysqld –basedir=/usr”

  19. I have to give thanks for and second the INNODB files advice. I copied just the database directories (i.e. mysql, wpdb, drupalMain, etc) and had my innodb tables show up in “SHOW TABLES” statements, but any query (select, describe) would result in “table db.tablename doesn’t exist”, even though the .frm file was there in the db directory. When I copied over ibdata1, ib_logfile0, and ib_logfile1, everything worked.

  20. Had to update apparmor, per Don’s reply (August 15, 2008 at 12:43 am) on Ubuntu 11.04 Server (command line) using MySQL 5.1.54.

    Also, I always make sure, one more time, to chmod -R mysql:mysql mysql_datadir (or whatever your folder name is) at the end of it all, before restarting MySQL.

    Thanks to all, and especially “thomas’ – keeper of this blog.

    David

  21. READ THIS:

    Make sure to move all the ibdataN / ib_logfileN files or you WILL LOSE ALL YOU InnoDB DATA!

    Damn… yes, they get recreated… and if you don’t use InnoDB you won’t notice… but if you do (like me) you will face a LOT of problems.

    I guess it serves me right for relying on an old source like this.

    /rant

Leave a Reply

Your email address will not be published. Required fields are marked *