How to Create a MySQL Snapshot using EBS on AWS (Without using RDS!)

Target:

Make a snapshot for MySQL and store it on AWS EBS

Steps:

Create and Mount EBS

1. Create a new EBS volume within the same subnet as the EC2 instance running MysQL, and attach that EBS to the instance.
2. Mount newly created EBS to MySQL’s default data directory, /var/lib/mysql.
   1. Using lsblk to see the name of the new EBS volume.
   2. sudo file -s /dev/SSD_Name to check if the SSD has data in it. Not necessary since we just created the SSD.
   3. sudo mkfs -t ext4 /dev/SSD_Name to create a file system in that SSD.
   4. sudo mkdir /var/lib/mysql create the directory since we haven’t install MySQL yet.
      1. Creating this directory before installing MySQL is crucial. If install MySQL first, might run into permission and other problems when mount the SSD or writing data to it.
   5. sudo mount /dev/SSD_Name /var/lib/mysql mount the SSD there.
   6. sudo rm -rf /var/lib/mysql/ A file called lost+found will be created after mounting the SSD. Remove it.

Install and Start MySQL

3. sudo apt install mysql-server install MySQL.
4. Grant permission to MySQL.
   1. sudo chown mysql:mysql /var/lib/mysql
   2. sudo chmod 777 /var/lib/mysql 777 is dangerous, change the number accordingly.
5. sudo mysqld --initialize
   1. sudo vim /var/log/mysql/error.log to check the log and see if the initialization is successful.
6. sudo systemctl start mysql start MySQL.

Log into MySQL as root user

7. sudo grep 'temporary password' /var/log/mysql/error.log to see the temporary password for MySQL’s root user.
8. Set MysQL users.
   1. mysql -u root -p to log in MySQL with temporary password.
   2. In MySQL terminal, ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; to change password.

Log into MySQL Remotely

9. Change AWS security group inbound rules to open MySQL port 3306 for connections.
10. Change MySQL configuration file to allow remote connections.
    1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf.
    2. Change bind-address to 0.0.0.0 or specified IP address.
    3. sudo systemctl restart mysql restarting MySQL to make the change effective.
11. MySQL root user does not support remote log in

CREATE USER 'junxihe'@'71.182.163.25' IDENTIFIED BY 'super_super_secret_password;
GRANT ALL PRIVILEGES ON *.* TO 'junxihe'@'71.182.163.25' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Remember to change the IP address accordingly. Change to % to allow access from anywhere.

Data Migration to MySQL

12. Personally recommend Navicat to migrate data
13. late double check: df -h to see if the SSD is mounted as we expected.

Create Snapshot

14. Easiest step