How to Set Up MySQL / MariaDB

Installing MySQL

# Ubuntu/Debian
apt update
apt install mysql-server -y
systemctl start mysql
systemctl enable mysql

# CentOS/AlmaLinux
dnf install mysql-server -y
systemctl start mysqld
systemctl enable mysqld

Securing the Installation

1. Run the security script:

   mysql_secure_installation

2. Follow the prompts:
   • Set a root password (strong, unique password recommended).
   • Remove anonymous users: Yes
   • Disallow root login remotely: Yes (recommended)
   • Remove test database: Yes
   • Reload privilege tables: Yes

Creating a Database and User

mysql -u root -p

CREATE DATABASE myapp_db;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Allowing Remote Access (if needed)

1. Edit MySQL config and change bind-address from 127.0.0.1 to 0.0.0.0.
2. Restart MySQL:

   systemctl restart mysql

3. Grant remote access and open firewall port 3306.

Security note: Only allow remote MySQL access if absolutely necessary. Use SSH tunneling or a VPN for secure remote database connections.

MariaDB (Alternative)

MariaDB is a drop-in replacement for MySQL:

# Ubuntu/Debian
apt install mariadb-server -y

# CentOS/AlmaLinux
dnf install mariadb-server -y

The same mysql_secure_installation and SQL commands work for MariaDB.