How to migrate MYSQL database
MySQL database is normally located in the System Disk of the Linux partition.
As you use JET, the data will grow and it may exceed the limits of the System Disk partition. As such, you should move the MySQL database to the data disk. The below steps is based on Ubuntu Linux servers but it should be more or less the same in other Linux servers as well. Do adjust for any differences and if you are not sure, you can contact the Cloud provider for further assistance.
Note: It is advisable to do a backup of the database before proceeding with the below. (SQL file backups are recommended)
Step 1: Verify the directory of MySQL database.
$ sudo mysql -u root -p
Enter the MySQL password.
mysql> select @@datadir;
The output will determine the MySQL database directory which is usually /var/lib/mysql/
Step 2: Stop MySQL service
$ sudo systemctl stop mysql
This will stop the service.
$ sudo systemctl status mysql
This will allow you to confirm the status of the service.
Step 3: Copy MySQL database to another location
$ sudo rsync -av /var/lib/mysql /data/database
In this case, /data/database folder is where I want to copy to.
Step 4: Rename the old database folder
$ sudo mv /var/lib/mysql /var/lib/mysql.bak
This will save the backup temporarily until we confirm that the MySQL migration is successful.
Step 5: Point the MySQL service to the new directory.
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Edit this file using vi.
$ datadir=/data/database/mysql
Locate the “datadir=” line and update it with the new path.
Step 6: Configure AppArmor Access Control Rules
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Edit this file using vi.
alias /var/lib/mysql/ -> /data/database/mysql/
Add this line and save.
$ sudo systemctl restart apparmor
Restart AppArmor service.
Step 7: Restart MySQL service
$ sudo mkdir /var/lib/mysql/mysql -p
Create a dummy folder in place of the default MySQL directory.
$ sudo systemctl start mysql
Start MySQL service.
$ sudo systemctl status mysql
Confirm that the service has started.
Step 8: Step 7 may sometimes fail if you took the chance to upgrade MySQL while the service is down.
In this case, you will need to re-initialize your database with this command:
mysql_install_db
before starting from step 3 again.
Step 9: Verify the directory of MySQL database again.
$ sudo mysql -u root -p
Enter the MySQL password.
mysql> select @@datadir;
The output will determine the MySQL database directory which is /data/database/
Step 10: Cleanup of temporary files/folders.
Do this after verifying the data integrity of existing data.
$ sudo rm -Rf /var/lib/mysql.bak
Remove the old MySQL folder we previously rename.
$ sudo systemctl restart mysql
Restart the service once more to confirm.
