MySQL/MariaDB Server Setup - LearnLinuxOnline

MySQL/MariaDB Server Setup on Linux

MySQL and MariaDB are popular open-source relational database management systems. This tutorial covers installing and configuring both database servers on Ubuntu and RedHat-based systems.

Prerequisites

Ubuntu 20.04/22.04 LTS or RHEL/Rocky Linux 8/9
Root or sudo privileges
Minimum 1GB RAM (2GB+ recommended for production)
At least 10GB of free disk space
Basic knowledge of Linux command line

Ubuntu/Debian

RedHat/CentOS

1 Update System and Install MySQL/MariaDB

Update your package list and install the database server:

sudo apt update
sudo apt upgrade -y

Install MySQL Server:

sudo apt install mysql-server -y

Or install MariaDB Server:

sudo apt install mariadb-server -y

2 Start and Enable the Service

Start the database service and enable it to start on boot:

# For MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# For MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

Check the service status:

# For MySQL
sudo systemctl status mysql

# For MariaDB
sudo systemctl status mariadb

3 Run Security Script

MySQL and MariaDB include a security script to secure your installation:

# For MySQL
sudo mysql_secure_installation

# For MariaDB
sudo mariadb-secure-installation

Follow the prompts to:

Set a root password (if not set)
Remove anonymous users
Disallow remote root login
Remove test database
Reload privilege tables

4 Configure MySQL/MariaDB

Edit the main configuration file:

# For MySQL
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# For MariaDB
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Make basic configuration changes (adjust based on your server resources):

[mysqld]
# Basic settings
bind-address = 0.0.0.0  # Change to 127.0.0.1 for local only
max_connections = 100
wait_timeout = 600

# Memory settings
key_buffer_size = 256M
max_allowed_packet = 256M
thread_stack = 192K
thread_cache_size = 8

# InnoDB settings (if using InnoDB)
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2

Restart the service to apply changes:

# For MySQL
sudo systemctl restart mysql

# For MariaDB
sudo systemctl restart mariadb

5 Create Database and User

Access the MySQL/MariaDB shell:

sudo mysql -u root -p

Once in the MySQL shell, create a new database and user:

-- Create a new database
CREATE DATABASE example_db;

-- Create a new user
CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant privileges to the user on the database
GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

-- Exit the MySQL shell
EXIT;

6 Test the Database Connection

Test your connection with the new user:

mysql -u example_user -p -D example_db

Create a simple test table:

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (name) VALUES ('Test Record');

SELECT * FROM test_table;

7 Configure Firewall (If Enabled)

Allow MySQL/MariaDB through the firewall (default port 3306):

sudo ufw allow 3306/tcp
sudo ufw enable
sudo ufw status

Security Note: Only open port 3306 to trusted IP addresses in production environments. Consider using SSH tunneling for remote access instead.

8 Backup and Maintenance

Create a basic backup script:

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

Add the following content (adjust paths and credentials):

#!/bin/bash
# MySQL/MariaDB backup script
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_DIR="/backup/mysql"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="backup_password"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Get list of databases
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

# Backup each database
for DB in $DATABASES; do
    mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --routines --triggers $DB | gzip > "$BACKUP_DIR/$DB-$DATE.sql.gz"
done

# Delete backups older than 30 days
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +30 -delete

Make the script executable:

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

1 Update System and Install MySQL/MariaDB

Update your package list and install the database server:

# For RHEL 8/9, Rocky Linux, AlmaLinux
sudo dnf update -y

Enable the MySQL or MariaDB repository and install:

# For MySQL on RHEL 8/9
sudo dnf install mysql-server -y

# For MariaDB on RHEL 8/9
sudo dnf install mariadb-server -y

# For CentOS 7
sudo yum install mariadb-server -y

2 Start and Enable the Service

Start the database service and enable it to start on boot:

# For MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# For MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

Check the service status:

# For MySQL
sudo systemctl status mysqld

# For MariaDB
sudo systemctl status mariadb

3 Run Security Script

MySQL and MariaDB include a security script to secure your installation:

# For MySQL
sudo mysql_secure_installation

# For MariaDB
sudo mariadb-secure-installation

Follow the prompts to:

Set a root password (if not set)
Remove anonymous users
Disallow remote root login
Remove test database
Reload privilege tables

4 Configure MySQL/MariaDB

Edit the main configuration file:

# For MySQL
sudo nano /etc/my.cnf.d/mysql-server.cnf

# For MariaDB
sudo nano /etc/my.cnf.d/mariadb-server.cnf

Make basic configuration changes (adjust based on your server resources):

[mysqld]
# Basic settings
bind-address = 0.0.0.0  # Change to 127.0.0.1 for local only
max_connections = 100
wait_timeout = 600

# Memory settings
key_buffer_size = 256M
max_allowed_packet = 256M
thread_stack = 192K
thread_cache_size = 8

# InnoDB settings (if using InnoDB)
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2

# For RHEL-based systems, add this line to avoid socket issues
socket = /var/lib/mysql/mysql.sock

Restart the service to apply changes:

# For MySQL
sudo systemctl restart mysqld

# For MariaDB
sudo systemctl restart mariadb

5 Create Database and User

Access the MySQL/MariaDB shell:

sudo mysql -u root -p

Once in the MySQL shell, create a new database and user:

-- Create a new database
CREATE DATABASE example_db;

-- Create a new user
CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant privileges to the user on the database
GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

-- Exit the MySQL shell
EXIT;

6 Configure SELinux and Firewall

If SELinux is enabled, configure it for MySQL/MariaDB:

# Check SELinux status
sestatus

# If enforcing, set the appropriate context for database files
sudo semanage fcontext -a -t mysqld_db_t "/var/lib/mysql(/.*)?"
sudo restorecon -Rv /var/lib/mysql

Configure the firewall to allow MySQL connections:

# For firewalld
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

# For older systems with iptables
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
sudo service iptables save

Security Note: Only open port 3306 to trusted IP addresses in production environments. Consider using SSH tunneling for remote access instead.

7 Troubleshooting Common Issues

Check error logs if you encounter issues:

# For MySQL
sudo tail -f /var/log/mysqld.log

# For MariaDB
sudo tail -f /var/log/mariadb/mariadb.log

If you can't connect to the database server, check the bind address:

# Check if MySQL is listening on the correct interface
sudo netstat -tlnp | grep mysql

Reset root password if forgotten:

# Stop MySQL/MariaDB
sudo systemctl stop mysqld  # or mariadb

# Start without password checking
sudo mysqld_safe --skip-grant-tables &

# Connect to MySQL
mysql -u root

# Update root password
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
EXIT;

# Restart normally
sudo systemctl start mysqld  # or mariadb

Previous: Web Server Next: Cacti Monitoring