Installing and Securing MySQL: A Comprehensive Guide for Linux, Windows, and macOS

MySQL is one of the most popular open-source relational database management systems, powering countless applications and services. In this technical blog, we’ll walk through installing, configuring, and securing MySQL across three major platforms: general Linux (Debian- and RHEL-based), Windows, and macOS.

We’ll include step-by-step instructions, common pitfalls, and advanced hardening techniques—including SSL encryption and remote-access controls.

Introduction
Installing MySQL on General Linux
- Repository Setup (Debian/Ubuntu)
- Repository Setup (RHEL/CentOS/Fedora)
- Installing MySQL Server
- Managing the Service (systemd)
- Securing Installation (mysql_secure_installation)
- Creating Non-Root Users & Databases
- Remote-Access Configuration
- SSL Encryption Setup
- Workflow Script & Aliases
Installing MySQL on Windows
Installing MySQL on macOS
Advanced Security Best Practices
Conclusion

1. Introduction

MySQL’s default installation often includes conveniences—like anonymous users, the test database, and broad root access—that ease testing but pose security risks in production. This guide consolidates common questions and commands to help you:

Install MySQL server on popular Linux distributions
Configure and manage the service under systemd
Secure installations with mysql_secure_installation
Create and manage non-root users
Enable and restrict remote access
Encrypt connections using SSL
Harden server configurations and enforce policies

Let’s dive in.

2. Installing MySQL on Linux

2.1 Repository Setup (Debian- and Ubuntu-based)

Update package index
```
sudo apt update
```
Install the MySQL APT repository package
```
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
```
- When prompted, select MySQL 8.0 and press Enter.
- For latest version : https://dev.mysql.com/downloads/repo/apt/

Refresh and install

sudo apt update
sudo apt install mysql-server mysql-client

Note: On Ubuntu, the distro’s default mysql-server may already point to Oracle’s MySQL or to MariaDB. Installing the APT repo ensures you get the official Oracle release.

2.2 Repository Setup (RHEL/CentOS/Fedora)

Download the official YUM repo file

sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm

Enable the MySQL module (if disabled)

sudo dnf module disable mysql
sudo dnf config-manager --disable mysql57-community
sudo dnf config-manager --enable mysql80-community

Install MySQL

sudo dnf install mysql-community-server

2.3 Installing MySQL Server

After adding the repo:

# Debian/Ubuntu
sudo apt install mysql-server mysql-client

# RHEL/CentOS/Fedora
sudo dnf install mysql-community-server

2.4 Managing the Service (systemd)

Linux distros today standardize on systemd for service management. Use these commands:

sudo systemctl enable mysqld       # Enable auto-start at boot
sudo systemctl start mysqld        # Start the service now
sudo systemctl status mysqld       # Verify it’s running
sudo systemctl stop mysqld         # Stop the service
sudo systemctl disable mysqld      # Prevent auto-start
sudo systemctl restart mysqld      # Restart the service
sudo systemctl reload mysqld       # Reload configuration without full restart

Tip: On Debian/Ubuntu the service name is mysql instead of mysqld.

2.5 Securing Installation

Run MySQL’s built-in security script:

sudo mysql_secure_installation

When prompted, answer:

VALIDATE PASSWORD PLUGIN: (optional) choose a strength level
Remove anonymous users? → Yes
Disallow root remote login? → Yes
Remove the test database? → Yes
Reload privilege tables? → Yes

2.6 Creating Non-Root Users & Databases

Login as root:
```
mysql -u root -p
```

Create a database and user (local only):

CREATE DATABASE mydb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongP@ss!';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Create a remote user (SSL-required):

CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ss!';
GRANT ALL ON mydb.* TO 'appuser'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

Exit:
```
EXIT;
```

2.7 Remote-Access Configuration

Edit MySQL config (/etc/mysql/my.cnf or /etc/my.cnf, then include mysqld.cnf as needed). Set:
```
[mysqld]
bind-address = 0.0.0.0
```

Open firewall (for 3306):

# UFW (Debian/Ubuntu)
sudo ufw allow 3306/tcp

# firewalld (RHEL/Fedora)
sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

Restart MySQL:
```
sudo systemctl restart mysqld
```

2.8 SSL Encryption Setup

Generate CA, server, and client certificates:

sudo mkdir -p /etc/mysql/ssl && cd /etc/mysql/ssl
sudo openssl genpkey -algorithm RSA -out ca-key.pem
sudo openssl req -new -key ca-key.pem -out ca-req.pem -subj "/CN=MySQL-CA"
sudo openssl x509 -req -in ca-req.pem -signkey ca-key.pem -out ca-cert.pem

sudo openssl genpkey -algorithm RSA -out server-key.pem
sudo openssl req -new -key server-key.pem -out server-req.pem -subj "/CN=$(hostname)"
sudo openssl x509 -req -in server-req.pem -CA ca-cert.pem -CAkey ca-key.pem \
  -CAcreateserial -out server-cert.pem

sudo openssl genpkey -algorithm RSA -out client-key.pem
sudo openssl req -new -key client-key.pem -out client-req.pem -subj "/CN=client"
sudo openssl x509 -req -in client-req.pem -CA ca-cert.pem -CAkey ca-key.pem \
  -CAcreateserial -out client-cert.pem

sudo chown mysql:mysql *pem
sudo chmod 600 *-key.pem

Configure MySQL to use SSL (add to [mysqld] section):

ssl-ca        = /etc/mysql/ssl/ca-cert.pem
ssl-cert      = /etc/mysql/ssl/server-cert.pem
ssl-key       = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

Restart MySQL:
```
sudo systemctl restart mysqld
```

Create SSL-only user:

CREATE USER 'ssluser'@'%' IDENTIFIED BY 'P@ssw0rd' REQUIRE SSL;
GRANT ALL ON mydb.* TO 'ssluser'@'%';
FLUSH PRIVILEGES;

Test from client:

mysql -u ssluser -p \
  --host=your.server.ip \
  --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
  --ssl-cert=/etc/mysql/ssl/client-cert.pem \
  --ssl-key=/etc/mysql/ssl/client-key.pem

2.9 Workflow Script & Aliases

Aliases (add to `~/.bashrc` or `~/.zshrc`)

alias mysql-enable='sudo systemctl enable mysqld'
alias mysql-start='sudo systemctl start mysqld'
alias mysql-stop='sudo systemctl stop mysqld'
alias mysql-restart='sudo systemctl restart mysqld'
alias mysql-disable='sudo systemctl disable mysqld'
alias mysql-status='sudo systemctl status mysqld'

Reload your shell:

source ~/.bashrc

Toggle Script (`~/mysqlctl.sh`)

#!/usr/bin/env bash
# Usage: mysqlctl {enable|start|stop|restart|disable|status}

CMD=$1
case "$CMD" in
  enable)   sudo systemctl enable mysqld ;;
  start)    sudo systemctl start mysqld ;;
  stop)     sudo systemctl stop mysqld ;;
  restart)  sudo systemctl restart mysqld ;;
  disable)  sudo systemctl disable mysqld ;;
  status)   sudo systemctl status mysqld ;;
  *) echo "Usage: $0 {enable|start|stop|restart|disable|status}" ;;
esac

Make it executable:

chmod +x ~/mysqlctl.sh

Now you can run, for example:

~/mysqlctl.sh status

3. Installing MySQL on Windows

3.1 Download & Installer Options

MySQL Installer (web or full) from dev.mysql.com.

3.2 Running MySQL Installer

Choose setup type: Developer Default, Server only, Client only, Full, or Custom.
Select MySQL Server 8.0.x, tools, connectors.
Pick authentication method (use strong password encryption).

3.3 Initial Configuration Wizard

Config Type: Development vs. Server machine.
Connectivity: Default port 3306; open in Windows Firewall.
Accounts & Roles: Set root password; optionally add users.
Windows Service: Configure MySQL to run as a service.

3.4 Securing the Instance

Via MySQL Installer > Configuration > MySQL Secure Installation:

Remove anonymous users
Disable remote root login
Drop the test database

3.5 User and Remote-Access Setup

Use MySQL Workbench or MySQL Shell:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ss!';
GRANT SELECT, INSERT ON mydb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Ensure Windows Firewall allows 3306/TCP.

3.6 SSL Configuration on Windows

Generate certs via OpenSSL (Win) or MySQL Shell.
Place ca.pem, server-cert.pem, server-key.pem in:
```
C:\ProgramData\MySQL\MySQL Server 8.0\ssl
```

Edit my.ini under [mysqld]:

ssl-ca=ssl\ca.pem
ssl-cert=ssl\server-cert.pem
ssl-key=ssl\server-key.pem
require_secure_transport=ON

Restart the MySQL service via services.msc.

4. Installing MySQL on macOS

4.1 Using Homebrew

brew update
brew install mysql
brew services start mysql   # launchd service
mysql_secure_installation

4.2 Native DMG Installer

Download DMG from MySQL site and run the installer.
Include the Startup Item for launch at system boot.
Use System Preferences pane to start/stop MySQL.

4.3 Initialization & Service Management

brew services start mysql
brew services stop mysql
brew services restart mysql

4.4 Secure Setup

mysql_secure_installation

Remove anonymous/test users and enforce local-only root by default.

4.5 Users, Privileges, and Remote-Access

Edit /usr/local/etc/my.cnf:
```
[mysqld]
bind-address = 0.0.0.0
```
Restart and then create remote users as in Section 2.6.

4.6 SSL Encryption

Generate SSL certs via OpenSSL (same steps as Linux) into /usr/local/etc/mysql/ssl.
Configure my.cnf to point at the CA, server, and key files.
Restart and test with SSL-enforced user.

5. Advanced Security Best Practices

Least Privilege Principle
Grant only required privileges (e.g., SELECT instead of ALL PRIVILEGES).

Disable Unsafe Features

[mysqld]
local_infile = 0
symbolic-links = 0
secure_file_priv = /var/lib/mysql-files

Enforce SSL
```
require_secure_transport = ON
```

Password Policy Enforcement

INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;

Auditing and Logging

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql.log
log_error = /var/log/mysql/mysql_error.log

Regular Updates

# Debian/Ubuntu
sudo apt update && sudo apt upgrade

# RHEL/CentOS/Fedora
sudo dnf update

# macOS
brew update && brew upgrade mysql

6. Conclusion

Securing MySQL across Linux, Windows, and macOS involves a consistent set of best practices:

Remove insecure defaults (anonymous users, test database).
Use strong credentials and limit root access.
Grant minimal privileges following the least-privilege principle.
Enable and enforce SSL for all remote connections.
Harden configuration settings and monitor logs continuously.
Automate service management with aliases or scripts for operational efficiency.

Following this guide ensures you have a robust, secure MySQL deployment—whether on your favorite Linux distro, on Windows servers, or on macOS workstations. Happy databasing!

Search This Blog

David's blog