Setup Guide

Install MySQL

Installing MySQL requires configuring the correct repository and installing necessary dependencies. We use MySQL 8.4 LTS for long-term stability and support.

1. Add MySQL user:

   sudo useradd -r -g mysql -s /bin/false mysql
   

   Copy to clipboard
2. Download MySQL repository configuration:

   wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
   

   Copy to clipboard
3. Install MySQL config:

   sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb
   

   Copy to clipboard
   
   Select: MySQL Server & Cluster > mysql-8.4-lts > OK
4. Update system:

   sudo apt update
   sudo apt upgrade
   

   Copy to clipboard
5. Install MySQL:

   sudo apt install mysql-server
   

   Copy to clipboard
6. Set correct ownership:

   sudo chown -R mysql:mysql /var/lib/mysql
   sudo chown -R mysql:mysql /var/log/mysql
   

   Copy to clipboard
7. Enable and start MySQL:

   sudo systemctl enable mysql
   sudo systemctl start mysql
   

   Copy to clipboard
8. Secure installation:

   sudo mysql_secure_installation
   

   Copy to clipboard

Change MySQL Default Port

Changing the default port enhances security by making it harder for automated attacks targeting the standard MySQL port.

1. Edit MySQL config:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

   Copy to clipboard
2. Add under mysqld:

   port = 34567
   

   Copy to clipboard
3. Restart MySQL:

   sudo systemctl restart mysql
   

   Copy to clipboard

MySQL User Security Best Practices

User Creation Guidelines

Never use wildcard (%) for host when creating MySQL users. Always specify exact IP addresses to maintain strict access control.

   -- AVOID: ❌ Creating user with wildcard host
   CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';

   -- CORRECT: ✅ Specify exact IP
   CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'password';

Privilege Management

Grant minimum required privileges based on user roles:

1. Application User (Restricted access):

   -- Create user with specific IP
   CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'password';
   
   -- Grant limited privileges
   GRANT SELECT, INSERT, UPDATE, DELETE 
   ON database_name.* 
   TO 'app_user'@'192.168.1.100';
   

   Copy to clipboard
2. Supervisor User (Local access only, access over SSH):

   -- Create supervisor user for localhost only
   CREATE USER 'spv_user'@'localhost' IDENTIFIED BY 'password';
   
   -- Grant elevated privileges
   GRANT ALL PRIVILEGES 
   ON database_name.* 
   TO 'spv_user'@'localhost';
   

   Copy to clipboard
3. Maintenance User (Specific operations -- read only):

   -- Create maintenance user for specific server
   CREATE USER 'maint_user'@'10.0.0.5' IDENTIFIED BY 'password';
   
   -- Grant specific maintenance privileges
   GRANT SELECT, LOCK TABLES, SHOW VIEW 
   ON database_name.* 
   TO 'maint_user'@'10.0.0.5';
   

   Copy to clipboard

Always finish with:

FLUSH PRIVILEGES;

Firewall

2. Configure firewall rules:

   sudo ufw allow from your.ip.v4 to any port 34567 proto tcp
   sudo ufw allow from [your:ipv6:address::1] to any port 34567 proto tcp
   

   Copy to clipboard

Cleanup and Firewall Setup

Final steps include cleaning up installation files and securing access through firewall rules.

1. Remove installation package:

   rm -rf mysql*.deb
   

   Copy to clipboard