Thursday, March 21, 2013

MySQL 5.5 Database directory change on Ubuntu 12.04 LTS

Once the MySQL server installed on Ubuntu server, the default database directory location is /var/lib/mysql. If you want to change the default database directory (for example to /mnt/data/db/mysql or any other directory of your choice) - just follow steps below:
  • Stop MySQL server:
sudo service mysql stop
  • I have completed the instructions below as root user - Create the new database directory:
mkdir /mnt/data/db/mysql
  • Modify the MySQL configuration file to point to the new database directory:
vi /etc/mysql/my.cnf 
#and change to: datadir = /mnt/data/db/mysql
  •  Change usr.sbin.mysqld file to reflect the new database directory:
vi /etc/apparmor.d/usr.sbin.mysqld
#and change from
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
to
/mnt/data/db/mysql/ r,
/mnt/data/db/mysql/** rwk,
#and run
/etc/init.d/apparmor restart
  • Initialize the new database directory:
mysql_install_db --user=mysql --datadir=/mnt/data/db/mysql
#change permissions as necessary
chown -R mysql:mysql /mnt/data/db/mysql 
chmod -R 700 /mnt/data/db/mysql 
  • Start MySQL server: 
sudo service mysql start

It is done!
You can change the new database directory to any directory of your choice, just modify the commands above to reflect it.
If you get "Access denied for user 'root'@'localhost'" after changes above, then run the following to update MySQL root user password:
sudo dpkg-reconfigure mysql-server-5.5

1 comment: