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à !
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
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).
skramstad : did you check file permissions ? Also do you get any specific error message when starting mysql ?
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
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
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.
Well, found the source of this trouble:
apparmor
See /etc/apparmor.d/usr.sbin.mysqld.
Robert
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
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…
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)
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!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
You should also move the ibdata1, ib_logfile0 and ib_logfile1 files. If you don’t all your InnoDB tables will be gone.
i done this without any issue
thanks
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.
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.
Using tar is better than copy when moving files like this. That way you leave file perms the same.
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
[...] How to move mysql database to another drive or partition [...]
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
Thanks, using RH5 and worked perfect. Thanks again!