This article lets you change the mysql data directory to a new directory. We might want to do this to move the data folder to a different paritition where there's enough size to hold the data.
Step 1 : Stop the My Sql server.
Before we do any changes to the data directory we should stop the mysql server if it is already running.
service mysql stop
Step 2: Create the new data directory
Create the new folder where you want to migrate the data folder to..
mkdir -p /new_partition/mysql
Step 3: Change ownership of the new data directory
chown -R mysql:mysql /new_partition/mysql
Step 4: Change the /etc/mysql/my.cnf file
Open the /etc/mysql/my.cnf file using root access
sudo vi /etc/mysql/my.cnf
Find the attribute with name datadir and replace its value to the new folder that we created in step 2
datadir = /new_partition/mysql
Step 5: Copy the required databases to the new data directory
For example we want to migrate databases of phpmyadmin, you'll find a folder inside the /var/lib/mysql/ folder. Copy it to the new partition directory.
cp -r -p /var/lib/mysql/phpmyadmin /new_partition/mysql/
-p option would ensure that the ownerships are preserved.
Step 6: Modifying the apparmour property file /etc/apparmour.d/usr.sbin.mysqld
Edit the usr.sbin.mysqld file inside /etc/apparmour.d/ folder and add following lines.
/new_partition/mysql/ r,
/new_partition/mysql/** rwk,
Step 7: Restart App Armour
Restart apparmour in ubuntu with following command
sudo /etc/init.d/apparmour restart
Step 8: Start MySql server
service mysql start
Check the error logs of mysql to see if the server has started fine or not. If you still see errors, then its most propably due to the permission issues in the new partition.
