How to Install MySQL on a VM and Connect from Anywhere

Update package lists and install MySQL:

sudo apt update && sudo apt install mysql-server

Start MySQL service:

sudo systemctl start mysql.service

Verify it's running:

sudo systemctl status mysql.service

Generate a strong password for the root user:
```
openssl rand -hex 32
```
Example output: aa2ca8b15fa6cc5e83c00576ffaeb31e6651fb228e29b316e6b1e3d616a7ad7e
Access MySQL as root:
```
sudo mysql
```

Set the root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password_here';
FLUSH PRIVILEGES;

Exit MySQL and try logging in with the new password:
```
mysql -u root -p
```
Enter the password when prompted.

(Optional) To revert to socket authentication for root:

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
FLUSH PRIVILEGES;

Create a remote user (replace 'averagedev' and 'secure_password' with your chosen username and password):
```
CREATE USER 'averagedev'@'%' IDENTIFIED BY 'secure_password';
```
Explanation: The '%' in 'averagedev'@'%' is used as a wildcard for the host. This allows the user to connect from any IP address. Here's why this is important:
- In MySQL, the host part of a user account specifies which machines are allowed to connect using that account.
- Using '%' as the host allows connections from any IP address.
- This is useful because the IP address of the machine you're connecting from may change, especially if you're connecting from different locations or networks.
- If you specified a single IP address instead (e.g., 'averagedev'@'192.168.1.100'), you would only be able to connect from that specific IP address.
For enhanced security, you can restrict the IP range if you know the specific network or range of IP addresses you'll be connecting from. For example:
- 'averagedev'@'192.168.1.%' would allow connections from any IP in the 192.168.1.0/24 network.
- 'averagedev'@'10.0.%' would allow connections from any IP in the 10.0.0.0/16 network.
Choose the appropriate level of restriction based on your security requirements and connection needs.

Grant privileges to the remote user:

GRANT ALL PRIVILEGES ON *.* TO 'averagedev'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Configure MySQL to allow remote connections:
```
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
Find the line with bind-address and change it to:
```
bind-address = 0.0.0.0
```
Save and exit the editor.
Restart MySQL to apply changes:
```
sudo systemctl restart mysql
```
(Important) Configure firewall to allow MySQL connections:
```
sudo ufw allow mysql
```
If using a cloud provider, ensure to open port 3306 in your network security settings.
From your local machine, connect to the MySQL server:
```
mysql -u averagedev -h your_vm_ip -p
```
Or use a database management tool like DBeaver, MySQL Workbench, etc.

Remember to replace placeholders like 'your_strong_password_here', 'secure_password', and 'your_vm_ip' with actual values.
Set up automated backups: AWS Backup Bash Script Azure Backup Bash Script a. Create a backup script (e.g., backup_script.sh) in your home directory or preferred location. Ensure the script includes commands to: b. Set appropriate permissions for the script:
```
chmod 755 ~/backup_script.sh
```
Ensure your chosen cloud CLI (AWS CLI or Azure CLI) is installed and configured with the necessary credentials and required storage services are set up.
Set up a cron job to run the backup script:

a. Open the crontab editor:
```
crontab -e
```
b. Add a line to run the backup script daily at midnight:
```
0 0 * * * ~/backup_script.sh
```
This cron schedule (0 0 * * *) means:
- 0 minutes past the hour
- 0 hour (midnight)
- Every day of the month
- Every month
- Every day of the week
Test the backup process:
- Run the script manually to ensure it works as expected.
- Verify that the backup file is created and uploaded to your cloud storage.
Implement a backup retention policy:
- Modify the backup script to delete old backups from cloud storage.
- Current script given in video descriptions can be used to delete backups older than 7 days.

These steps will help ensure your MySQL data is regularly backed up and stored securely in the cloud. Remember to customize the backup script and cron job according to your specific needs and cloud storage setup.

chirag-chhajed/instructions.md

How to Install MySQL on a VM and Connect from Anywhere