Install MySQL or MariaDB and Secure It

MySQL and MariaDB are popular open-source relational databases. This guide covers installation, security hardening, and basic user/database setup on Ubuntu/Debian or RHEL/CentOS.

Step 1: Update your package manager

sudo apt update

On RHEL/CentOS:

sudo dnf update -y

Step 2: Install MySQL or MariaDB

For MySQL 8 on Ubuntu/Debian:

sudo apt install mysql-server -y

For MariaDB on Ubuntu/Debian:

sudo apt install mariadb-server -y

For RHEL/CentOS (automatically uses MariaDB in newer versions):

sudo dnf install mysql-server -y

Step 3: Start and enable the service

sudo systemctl start mysql
sudo systemctl enable mysql
sudo systemctl status mysql

(Use mariadb instead of mysql if you installed MariaDB.)

Step 4: Run the security hardening script

MySQL/MariaDB includes a script to remove insecure defaults:

sudo mysql_secure_installation

You'll be prompted with several questions. Recommended answers:

• Enter current password: Press Enter (no password set yet).
• Switch to unix_socket authentication: y (for local root access without password).
• Remove anonymous users: y.
• Disable remote root login: y.
• Remove test database: y.
• Reload privileges table: y.

Step 5: Verify installation

Connect to the database server:

sudo mysql

You should see the mysql> prompt. Exit with:

exit

Step 6: Create a database and user

Log in as root:

sudo mysql

Create a new database:

CREATE DATABASE your_app_db;

Create a user and grant privileges:

CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON your_app_db.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;

Replace:
- your_app_db with your database name
- your_user with your database username
- strong_password_here with a secure password

Exit:

exit

Step 7: Test the new user login

mysql -u your_user -p your_app_db

Enter your password when prompted. If you see the mysql> prompt, the user was created successfully.

Step 8: Restrict remote access (important for security)

By default, mysql_secure_installation disables remote root logins. However, verify that the service only listens on localhost:

Edit the MySQL configuration:

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

Look for the line bind-address and ensure it's set to:

bind-address = 127.0.0.1

This prevents external hosts from connecting to your database. If you need remote access from a specific IP (e.g., an app server), change it to:

bind-address = 0.0.0.0

And restrict at the firewall level instead.

Save and exit, then restart the service:

sudo systemctl restart mysql

Step 9: Open firewall port (only if needed for remote access)

If you need remote database access, open port 3306 only to trusted IPs:

sudo ufw allow from 192.0.2.1 to any port 3306
sudo ufw allow from 198.51.100.2 to any port 3306

Replace the IPs with your app server(s). For cloud providers (AWS, etc.), use Security Groups instead of ufw.

Step 10: Create database backups

Regular backups are critical. Create a backup script:

sudo nano /usr/local/bin/backup-mysql.sh

Paste:

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

sudo mysqldump -u root -p$(grep password /root/.my.cnf | cut -d= -f2) --all-databases > "$BACKUP_DIR/all_databases_$TIMESTAMP.sql"

# Keep only the last 7 days of backups
find $BACKUP_DIR -type f -mtime +7 -delete

echo "Backup completed: $BACKUP_DIR/all_databases_$TIMESTAMP.sql"

Make it executable:

sudo chmod +x /usr/local/bin/backup-mysql.sh

Schedule it with cron (daily at 2 AM):

sudo crontab -e

Add the line:

0 2 * * * /usr/local/bin/backup-mysql.sh

Tips

• Reset root password: If you lose the root password, see the MySQL documentation for recovery mode (mysqld_safe --skip-grant-tables).
• Check databases: Run SHOW DATABASES; at the mysql> prompt.
• View users: Run SELECT user, host FROM mysql.user;
• Remove a user: DROP USER 'username'@'localhost';
• Change password: ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
• Monitor queries: Enable the slow query log to identify performance issues: SET GLOBAL slow_query_log = 'ON';
• Performance tuning: The default config is conservative; adjust max_connections, innodb_buffer_pool_size, and other settings based on your workload.