====== Changing MySQL data directory ====== ===== Why changing MySQL data dir ? ===== Under Ubuntu server, MySQL data are located under **/var/lib/mysql** which coud cause a problem whether I have a huge database to cope with : on default debian installation, much of the disk space is left for the **/home/** directory. As there already is a mysql user, it is a better solution to store its data to a **/home/mysql** folder Filesystem Size Used Avail Use% Mounted on /dev/sda1 2.9G 1.4G 1.4G 51% / varrun 493M 60K 493M 1% /var/run varlock 493M 12K 493M 1% /var/lock udev 493M 20K 493M 1% /dev devshm 493M 0 493M 0% /dev/shm /dev/sda3 226G 94G 122G 44% /home ===== How to change MySQL data dir ? ===== ==== Stopping mysql daemon ==== First, su on your machine. Then, stop mysql daemon, this could be useful to prevent anybody doing any modification during the process. This action will stop all mysql jobs running i.e. if you are hosting any applications on your server, they will be unreachable during the operation (about 5 minutes). To stop mysql daemon, simply drop the command line /etc/init.d/mysql stop ==== /home/mysql directory ==== Create a /home/mysql directory and give clearance to mysql mkdir /home/mysql chown -R mysql /home/mysql chgrp -R mysql /home/mysql Copy data and preserve permissions, etc. cp -Rp /var/lib/mysql/* /home/mysql/ ====Change MySQL configuration ==== MySQL configuration is basically stored into the my.cnf file. If you have any exotic mysql configuration such as clustering or if you are making use of the /etc/mysql/conf.d/ folder for specific user tweaking, this could be dangerous and think of making a backup of this config file before modifying it. When you are ready, just edit it vi /etc/mysql/my.cnf Find the line datadir = /var/lib/mysql And change it to datadir = /home/mysql ==== Re-enable MySQL and test ==== Okay, all went fine ? let's start mysql back /etc/init.d/mysql start Test MySQL, great, it works, now how do we get sure that it is now operating in the new **/home/mysql/** directory ? To be sure, simply create a new database (via command line or phpMyAdmin) and check in the filesystem if a folder has been created. ls -la /home/mysql/ ==== Cleaning previous mysql data files ==== After all, you may desire to clean up the **/var/lib/mysql** folder. My advice would be to keep the files debian-5.0.flag and mysql_upgrade_info (because i don't know what they stand for) and the mysql folder which could be a backup directory to restore your database permissions (can be useful some days, trust me). Have it been benefic to your filesystem ? check that with a new df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 2.9G 798M 2.0G 29% / varrun 493M 60K 493M 1% /var/run varlock 493M 12K 493M 1% /var/lock udev 493M 20K 493M 1% /dev devshm 493M 0 493M 0% /dev/shm /dev/sda3 226G 101G 115G 47% /home ===== Known bugs ===== ==== Apparmor ==== Whereas this runs smoothly under Ubuntu Server, on some Ubuntu desktop distribs, you may notice one of those lines in your logs : Feb 18 20:01:11 gaspard-desktop mysqld[25096]: 090218 20:01:11 InnoDB: Operating system error number 13 in a file operation. Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: The error means mysqld does not have the access rights to Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: the directory. Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: File name ./ibdata1 Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: File operation call: 'open'. Feb 18 20:01:11 gaspard-desktop mysqld[25096]: InnoDB: Cannot continue operation. Feb 18 20:01:11 gaspard-desktop mysqld[25096]: 090218 20:01:11 [Warning] Can't create test file /home/mysql/gaspard-desktop.lower-test This comes from the Apparmor configuration Edit /etc/apparmor.d/usr.sbin.mysqld and add the following two lines: /home/mysql/ r, /home/mysql/** rwk, somewhere in the middle of the file. After the /var/lib/mysql lines would be fine. Restart AppArmor /etc/init.d/apparmor restart Restart MySQLd /etc/init.d/mysql restart