How to Set Up MySQL on Linux: A Comprehensive Guide

Introduction

MySQL is a powerful, open-source relational database management system (RDBMS) widely used in web applications and enterprise environments. Installing MySQL on a Linux server is a fundamental skill for developers and system administrators. This guide provides a detailed, step-by-step walkthrough on how to install and configure MySQL on various Linux distributions, including Ubuntu, CentOS, and Fedora.

Prerequisites

Before proceeding, ensure you have:

A Linux server: This guide covers Ubuntu 20.04/22.04, CentOS 7/8, and Fedora 33/34.
Root or sudo privileges: Necessary for installation and configuration.
Internet connectivity: Required for downloading packages and updates.

How to Set Up MySQL on Linux: A Comprehensive Guide

1. Updating System Packages

Before installing new software, update your system packages to the latest versions.

Ubuntu

sudo apt update
sudo apt upgrade -y

CentOS

sudo yum update -y

Fedora

sudo dnf update -y

2. Installing MySQL

On Ubuntu

Ubuntu’s default repositories include MySQL packages.

Install MySQL Server:
Terminal window
```
sudo apt install mysql-server -y
```
Verify Installation:
Terminal window
```
mysql --version
```

On CentOS and Fedora

CentOS and Fedora use the YUM and DNF package managers, respectively.

Add the MySQL Repository:

Download the MySQL Community RPM package:

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm   # For CentOS 7
wget https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm   # For CentOS 8

Install the RPM package:

sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm   # CentOS 7
sudo rpm -Uvh mysql80-community-release-el8-3.noarch.rpm   # CentOS 8

Install MySQL Server:

sudo yum install mysql-server -y   # CentOS
sudo dnf install mysql-server -y   # Fedora

Verify Installation:
Terminal window
```
mysql --version
```

3. Securing the MySQL Installation

Run the mysql_secure_installation script to enhance security.

Run the Security Script:
Terminal window
```
sudo mysql_secure_installation
```
Follow the Prompts:
- Set the root password: Enter a strong password.
- Remove anonymous users: Yes.
- Disallow root login remotely: Yes.
- Remove test database and access to it: Yes.
- Reload privilege tables now: Yes.

4. Managing the MySQL Service

Ensure that MySQL starts on boot and is currently running.

Start MySQL Service:

sudo systemctl start mysql    # Ubuntu
sudo systemctl start mysqld   # CentOS/Fedora

Enable MySQL to Start on Boot:

sudo systemctl enable mysql    # Ubuntu
sudo systemctl enable mysqld   # CentOS/Fedora

Check Service Status:

sudo systemctl status mysql    # Ubuntu
sudo systemctl status mysqld   # CentOS/Fedora

5. Testing the MySQL Installation

Log In to MySQL Shell:
Terminal window
```
sudo mysql -u root -p
```
- Enter the root password when prompted.
Run a Test Query:
```
SHOW DATABASES;
```
Exit the MySQL Shell:
```
EXIT;
```

6. Configuring Remote Access (Optional)

By default, MySQL listens only on localhost. To allow remote connections:

Edit MySQL Configuration File:

Open the file in a text editor.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf    # Ubuntu
sudo nano /etc/my.cnf                           # CentOS/Fedora

Modify Bind Address:
- Locate the line:
```
bind-address = 127.0.0.1
```
- Change it to:
```
bind-address = 0.0.0.0
```

Create a Remote User:

sudo mysql -u root -p

In the MySQL shell:

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Adjust Firewall Settings:

Ubuntu with UFW:
Terminal window
```
sudo ufw allow 3306/tcp
sudo ufw reload
```

CentOS/Fedora with firewalld:

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

Restart MySQL Service:

sudo systemctl restart mysql    # Ubuntu
sudo systemctl restart mysqld   # CentOS/Fedora

7. Setting Up a Sample Database (Optional)

Log In to MySQL Shell:
Terminal window
```
sudo mysql -u root -p
```
Create a New Database:
```
CREATE DATABASE testdb;
```

Create a New User and Grant Permissions:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'user_password';
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;

Use the New Database:
```
USE testdb;
```

Create a Table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

Insert Data:

INSERT INTO users (name, email) VALUES ('Alice Smith', 'alice@example.com');

Query the Data:
```
SELECT * FROM users;
```
Exit the MySQL Shell:
```
EXIT;
```

8. Scheduling Regular Backups

Regular backups are essential for data recovery.

Create a Backup Directory:

sudo mkdir /var/backups/mysql
sudo chown $(whoami):$(whoami) /var/backups/mysql

Create a Backup Script:

nano ~/mysql_backup.sh

Add the following content:

#!/bin/bash
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/var/backups/mysql/$TIMESTAMP"
mkdir -p "$BACKUP_DIR"
mysqldump -u root -p'root_password' --all-databases > "$BACKUP_DIR/all_databases.sql"

Replace 'root_password' with your MySQL root password.

Make the Script Executable:
Terminal window
```
chmod +x ~/mysql_backup.sh
```
Schedule the Script with Cron:
Terminal window
```
crontab -e
```
- Add the following line to run the backup daily at 2 AM:
  Terminal window
```
0 2 * * * /home/your_username/mysql_backup.sh
```
- Replace /home/your_username/ with your actual username.

9. Updating MySQL

Keep MySQL updated for security and performance improvements.

Update Package Lists and Upgrade Packages:
- Ubuntu:
  Terminal window
```
sudo apt update
sudo apt upgrade -y
```
- CentOS:
  Terminal window
```
sudo yum update -y
```
- Fedora:
  Terminal window
```
sudo dnf upgrade -y
```
Check MySQL Version:
Terminal window
```
mysql --version
```

10. Conclusion

You have successfully installed and configured MySQL on your Linux server. This guide covered the essential steps, from installation and securing the server to managing databases and scheduling backups. Regular maintenance and updates will keep your MySQL server running smoothly and securely.

Note: Always ensure that your MySQL server is properly secured, especially if it’s accessible over a network. Use strong passwords, restrict user privileges, and keep your software up to date to protect against vulnerabilities.