Move MySQL data to a separate EBS volume

move-sql-data.md

Move MySQL data to new EBS volume (/mnt/data-01)

• mysql -u root -p
• mysql> select @@datadir;
• sudo service mysql stop
• sudo service mysql status
• Move files: sudo rsync -av /var/lib/mysql /mnt/data-01
• Create backup: sudo mv /var/lib/mysql /var/lib/mysql.bak
• Change datadir to point to the new volume:
  • sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
  • datadir=/mnt/data-01/mysql
• Add new alias in apparmor
  • sudo vim /etc/apparmor.d/tunables/alias
  • alias /var/lib/mysql/ -> /mnt/data-01/mysql/,
  • sudo service apparmor restart
• sudo mkdir /var/lib/mysql/mysql -p
• sudo service mysql start
• sudo service mysql status
• sudo rm -Rf /var/lib/mysql.bak

Alternative method for single MySQL instance

• Symlink directory ln -s /mnt/volume-nyc1-01/mysql /var/lib/mysql