How to change mysql data directory in Ubuntu
In this tutorial we will learn how to change the default path of mysql data directory.
The mysql server bydefault keep the data in /var/lib/mysql . If you want to change its path follow the given below steps.
Lets have an overview for changing mysql data directory method:
(1) Copy the new mysql directory to new location in server
(2) edit the my.cnf file and give new mysql data directory path
(3) edit /etc/apparmor.d/usr.sbin.mysqld file and add 2 lines for new mysql data directory.
(4) Restart the apparmor service
(5) Restart the mysql server service.
For reference purpose I will copy the /var/lib/mysql to /var/lib/mysqlnew .
Note:
(a) Inside /var/lib/mysql . the new database will be in directory with same name. for eg. If my database name is linuxdb then same name of directory you will find i.e /var/lib/mysql/linuxdb . Hence same databases should be exist in new mysql data dir.
(b) Given below are the important files that should be present in new mysql data dir.
ibdata1,ib_logfileN,mysql_upgrade_info,debian-5.5.flag
-rw-r–r– 1 mysql mysql 0 May 1 14:54 debian-5.5.flag
-rw-rw—- 1 mysql mysql 27262976 May 18 12:58 ibdata1
-rw-rw—- 1 mysql mysql 5242880 May 18 12:58 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Mar 5 11:07 ib_logfile1
-rw-rw—- 1 mysql mysql 6 May 1 14:55 mysql_upgrade_info
(c) The ownership and group of entire /var/lib/mysql is mysql:mysql . Just as u can see in above box.
Now follow the given below steps to change mysql data directory
Step1: Copy the /var/lib/mysql to new name /var/lib/mysqlnew
cp -prvf /var/lib/mysql /var/lib/mysqlnew
check the ownsership and group of new dir mysqlnew. It should mysql:mysql
root@sharad-sapplica:/var/lib# ls -ld mysqlnew/
drwx—— 7 mysql mysql 4096 May 18 13:00 mysqlnew/
root@sharad-sapplica:/var/lib# ls -la mysqlnew/
total 36896
drwx—— 7 mysql mysql 4096 May 18 13:00 .
drwxr-xr-x 83 root root 4096 May 18 12:56 ..
-rw-r–r– 1 mysql mysql 0 May 1 14:54 debian-5.5.flag
-rw-rw—- 1 mysql mysql 27262976 May 17 19:31 ibdata1
-rw-rw—- 1 mysql mysql 5242880 May 18 13:00 ib_logfile0
-rw-rw—- 1 mysql mysql 5242880 Mar 5 11:07 ib_logfile1
drwx—— 2 mysql mysql 4096 May 1 14:55 mysql
-rw-rw—- 1 mysql mysql 6 May 1 14:55 mysql_upgrade_info
drwx—— 2 mysql mysql 4096 May 1 14:55 performance_schema
drwx—— 2 mysql mysql 4096 Mar 5 11:07 test
root@sharad-sapplica:/var/lib#
Step 2: Now edit my.cnf file
and change the value of datadir with new path of mysql data directory.
Then save and exit
vi /etc/mysql/my.cnf
#datadir = /var/lib/mysql
datadir = /var/lib/mysqlnew
Note: After doing step 2, if you try to restart the mysql service it will not start and give some error in /var/log/syslog .
For reference below is the syslog details.
# tail -3 /var/log/syslog
May 18 12:58:19 mypc kernel: [ 7780.046897] type=1400 audit(1368862099.538:51): apparmor=”STATUS” operation=”profile_replace” name=”/usr/sbin/mysqld” pid=8171 comm=”apparmor_parser”
May 18 12:58:19 mypc kernel: [ 7780.065904] type=1400 audit(1368862099.558:52): apparmor=”DENIED” operation=”mknod” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mypc.lower-test” pid=8175 comm=”mysqld” requested_mask=”c” denied_mask=”c” fsuid=0 ouid=0
May 18 12:58:19 mypc kernel: [ 7780.065975] type=1400 audit(1368862099.558:53): apparmor=”DENIED” operation=”mknod” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mypc.lower-test” pid=8175 comm=”mysqld” requested_mask=”c” denied_mask=”c” fsuid=0 ouid=0
May 18 12:58:19 mypc kernel: [ 7780.069767] type=1400 audit(1368862099.562:54): apparmor=”DENIED” operation=”open” parent=1 profile=”/usr/sbin/mysqld” name=”/var/lib/mysqlnew/mysql/plugin.frm” pid=8175 comm=”mysqld” requested_mask=”r” denied_mask=”r” fsuid=121 ouid=121
Step 3: After reading the syslog we get to know apparmor is denying the new configuration for mysql. To rectify this issue we will edit the file /etc/apparmor.d/usr.sbin.mysqld. And paste the new path of mysql data directory.
And restart the apparmor service.
See the below method
# vi /etc/apparmor.d/usr.sbin.mysqld
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/lib/mysqlnew/ r,
/var/lib/mysqlnew/** rwk,Note: I kept the 2 lines for /var/lib/mysql in file as it is.Only added new 2 lines.(see the Bolded 2 lines)
# /etc/init.d/apparmor restart
Step 4: Now restart the mysql service .And try to connect the mysql-server.
#/etc/init.d/mysql restart
# mysql -u root -p
Note: Do not apply the steps directly in Live Production servers. First test the method in staging or test machines.
It depend upon the scenario on which you are working either you are copying the same data dir within same system or copying it from another server to new server.
The above given eg. is just for an reference.
Dude,
This is seriously the best resource on the net for this particular procedure.
Crisp and to the point.
Thank you
Thanks of lot Dhruv,Loved the feedback.
Regards
Sharad