Sometimes, you install MySQL on a relatively small partition. Then you discover that its size is no longer sufficient. It happened to me on a small AWS EC2 instance.

The solution was to mount another partition and move MySQL data files to this location. As the performance wasn’t very important on my test server, I used an AWS EFS partition, mounted on (/efs), for that purpose. In general, you need a high-speed SSD partition instead. Here is the process:

Step 1 – Stop MySQL service

Login to your Linux machine, and acquire root permissions:

$  sudo -i

Login to MySQL using command line interface:

$  mysql -u root -p

Display the path to the data files:

mysql>  select @@datadir;

The result would be (/var/lib/mysql) in general.

Type (Exit) to log out of MySQL, then stop its service:

$  systemctl stop mysql

Step 2 – Copy the files to the new location

$  rsync -av /var/lib/mysql  /efs

You may change (/efs) to the new location where you want to transfer the data files to.

Step 3 – Modify MySQL configuration

The name of MySQL configuration file is (my.cnf) or (mysqld.cnf) somewhere in (/etc/mysql/) directory. Edit this file:

$  vi /etc/mysql/my.cnf

Search for a line that containes (datadir=) option. Make it like this:

datadir=/efs/mysql

Again, you may replace (/efs) by your own location.

You may now empty the original location (/var/lib/mysql). I recommend deleting files only and keep the directory structure. This structure is tested is some of MySQL scripts and you may encounter some error messages if you delete the directory (/var/lib/mysql) entirely.

Step 4 – Start MySQL service

$  systemctl start mysql

You may check that MySQL now uses the new location, just as we did in Step 1.