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:
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.