Sometimes, you install MySQL on a relatively\u00a0small partition. Then you discover that its size is no longer sufficient. It happened to me on a small AWS EC2 instance.<\/p>\n
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:<\/p>\n
Login to your Linux machine, and acquire\u00a0root permissions:<\/p>\n
$\u00a0\u00a0<\/strong>sudo -i<\/strong><\/p>\n Login to MySQL using command line interface:<\/p>\n $\u00a0\u00a0<\/strong>mysql -u root -p<\/strong><\/p>\n Display the path to the data files:<\/p>\n mysql>\u00a0\u00a0select @@datadir;<\/strong><\/p>\n The result would be (\/var\/lib\/mysql)\u00a0in general.<\/p>\n Type (Exit) to log out of MySQL, then stop its service:<\/p>\n $\u00a0\u00a0<\/strong>systemctl stop <\/strong>mysql<\/p>\n $\u00a0\u00a0<\/strong>rsync -av \/var\/lib\/<\/strong>mysql\u00a0\u00a0\/efs<\/span><\/strong><\/p>\n You may change (\/efs) to the new location where you want to transfer the data files to.<\/p>\n The name of MySQL configuration file is (my.cnf) or (mysqld.cnf) somewhere in (\/etc\/mysql\/) directory. Edit this file:<\/p>\n $\u00a0 vi \/etc\/mysql\/my.cnf<\/strong><\/p>\n Search for a line that containes (datadir=) option. Make it like this:<\/p>\n datadir=\/efs\/mysql<\/strong><\/p>\n Again, you may replace (\/efs) by your own location.<\/p>\n 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\u00a0directory (\/var\/lib\/mysql) entirely.<\/p>\n $\u00a0\u00a0<\/strong>systemctl start <\/strong>mysql<\/p>\n You may check that MySQL now uses the new location, just as we did in Step 1.<\/p>\n","protected":false},"excerpt":{"rendered":" Sometimes, you install MySQL on a relatively\u00a0small 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 … <\/p>\nStep 2 – Copy the files to the new location<\/h1>\n
Step 3 – Modify MySQL configuration<\/h1>\n
Step 4 – Start MySQL service<\/h1>\n