MySQL/MariaDB Installation and Configuration: Complete Guide for Production Environments

Introduction

MySQL and MariaDB are two of the most popular open-source relational database management systems (RDBMS) in the world. MySQL, originally developed by MySQL AB and now owned by Oracle Corporation, has been the go-to choice for web applications for decades. MariaDB, created by the original developers of MySQL, emerged as a community-driven fork designed to remain free and open-source while maintaining compatibility with MySQL.

This comprehensive guide walks you through the complete installation and configuration process for both MySQL and MariaDB on Linux servers. Whether you're setting up a development environment or deploying a production database server, this guide covers everything from initial installation to advanced configuration options.

Why Choose MySQL or MariaDB?

Both database systems offer excellent performance, reliability, and extensive community support. MySQL powers some of the world's largest websites including Facebook, Twitter, and YouTube. MariaDB has gained significant traction with companies like Google, Wikipedia, and WordPress.com choosing it as their preferred database solution.

Key benefits include:

• High performance and scalability for handling millions of queries
• ACID compliance ensuring data integrity
• Rich feature set including stored procedures, triggers, and views
• Extensive ecosystem of tools and libraries
• Active community and professional support options

Prerequisites

Before proceeding with the installation, ensure you have:

• A Linux server running Ubuntu 20.04/22.04, Debian 11/12, CentOS 8/9, or Rocky Linux 8/9
• Root or sudo access to the server
• At least 1GB of RAM (2GB+ recommended for production)
• 10GB of available disk space minimum
• Basic understanding of command-line interface
• Firewall configured (we'll adjust rules as needed)

System Requirements

Minimum Requirements:

• CPU: 1 core
• RAM: 1GB
• Disk: 10GB
• Network: Basic connectivity

Recommended for Production:

• CPU: 4+ cores
• RAM: 8GB+
• Disk: SSD with 100GB+
• Network: Gigabit connection

Installation

Installing MySQL on Ubuntu/Debian

MySQL installation on Debian-based systems is straightforward using the APT package manager.

# Update package index
sudo apt update

# Install MySQL server
sudo apt install mysql-server -y

# Check MySQL service status
sudo systemctl status mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

For a specific MySQL version:

# Download MySQL APT repository configuration
wget https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb

# Install the repository configuration
sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb

# Update package index
sudo apt update

# Install MySQL 8.0
sudo apt install mysql-server -y

Installing MariaDB on Ubuntu/Debian

MariaDB installation follows a similar pattern:

# Update package index
sudo apt update

# Install MariaDB server
sudo apt install mariadb-server -y

# Start MariaDB service
sudo systemctl start mariadb

# Enable MariaDB to start on boot
sudo systemctl enable mariadb

# Verify installation
sudo systemctl status mariadb

For the latest MariaDB version:

# Add MariaDB repository
sudo apt install software-properties-common -y
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=11.2

# Install MariaDB
sudo apt update
sudo apt install mariadb-server -y

Installing MySQL on CentOS/Rocky Linux

For Red Hat-based distributions:

# Update system packages
sudo dnf update -y

# Install MySQL repository
sudo dnf install mysql-server -y

# Start MySQL service
sudo systemctl start mysqld

# Enable MySQL to start on boot
sudo systemctl enable mysqld

# Get temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

Installing MariaDB on CentOS/Rocky Linux

# Update system packages
sudo dnf update -y

# Install MariaDB server
sudo dnf install mariadb-server -y

# Start MariaDB service
sudo systemctl start mariadb

# Enable MariaDB to start on boot
sudo systemctl enable mariadb

# Check service status
sudo systemctl status mariadb

Verifying Installation

After installation, verify that the database server is running correctly:

# Check version
mysql --version

# For MySQL
mysqladmin --version

# For MariaDB
mariadb --version

# Test connection
sudo mysql -u root -p

Initial Configuration

Running the Security Script

Both MySQL and MariaDB include a security script that helps secure your installation by removing test databases, anonymous users, and setting up root password requirements.

# For MySQL
sudo mysql_secure_installation

# For MariaDB
sudo mysql_secure_installation

The script will prompt you for several security options:

1. Set root password: Choose a strong password (required for MySQL 5.7+)
2. Remove anonymous users: Select 'Y' to remove
3. Disallow root login remotely: Select 'Y' for security
4. Remove test database: Select 'Y' to clean up
5. Reload privilege tables: Select 'Y' to apply changes

Example interaction:

# Sample responses (recommended for production)
Would you like to setup VALIDATE PASSWORD component? [Y/n] Y
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
New password: [enter strong password]
Re-enter new password: [re-enter password]
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

Configuring Root Access

For MySQL 8.0+, the root user uses auth_socket plugin by default. To enable password authentication:

# Login as root
sudo mysql

# Change authentication method
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';
FLUSH PRIVILEGES;
EXIT;

For MariaDB:

# Login as root
sudo mysql

# Set root password if not set
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_strong_password';
FLUSH PRIVILEGES;
EXIT;

Creating Additional Database Users

Never use the root account for applications. Create dedicated users:

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

-- Create database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify user creation
SELECT User, Host FROM mysql.user;

For remote access (use with caution):

-- Allow access from specific IP
CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'192.168.1.100';

-- Allow access from any host (not recommended for production)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'%';

FLUSH PRIVILEGES;

Configuration Files

Understanding Configuration File Locations

MySQL:

• Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
• CentOS/Rocky: /etc/my.cnf or /etc/my.cnf.d/mysql-server.cnf

MariaDB:

• Ubuntu/Debian: /etc/mysql/mariadb.conf.d/50-server.cnf
• CentOS/Rocky: /etc/my.cnf.d/mariadb-server.cnf

Essential Configuration Parameters

Edit your configuration file:

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

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

# For CentOS/Rocky
sudo nano /etc/my.cnf

Basic configuration options:

[mysqld]
# Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection Settings
max_connections = 150
max_allowed_packet = 64M
connect_timeout = 10

# Buffer Settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Query Cache (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

# Binary Logging
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# Security
bind-address = 127.0.0.1
skip-name-resolve

Applying Configuration Changes

After modifying the configuration file:

# Test configuration for syntax errors
sudo mysqld --help --verbose | grep -A 1 'Default options'

# Restart MySQL
sudo systemctl restart mysql

# For MariaDB
sudo systemctl restart mariadb

# Verify service is running
sudo systemctl status mysql
# or
sudo systemctl status mariadb

# Check for errors
sudo tail -f /var/log/mysql/error.log

Security Hardening

Firewall Configuration

Configure firewall to allow MySQL connections only from trusted sources:

# UFW (Ubuntu/Debian)
# Allow from specific IP
sudo ufw allow from 192.168.1.100 to any port 3306

# Allow from specific subnet
sudo ufw allow from 192.168.1.0/24 to any port 3306

# firewalld (CentOS/Rocky)
# Allow from specific IP
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.100" port protocol="tcp" port="3306" accept'

# Allow from specific subnet
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept'

# Reload firewall
sudo firewall-cmd --reload

Disabling Remote Root Access

Ensure root cannot login remotely:

-- Check current root hosts
SELECT User, Host FROM mysql.user WHERE User='root';

-- Delete remote root access if exists
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

Implementing SSL/TLS Encryption

Generate SSL certificates:

# Create directory for certificates
sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# Generate CA key and certificate
sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

# Generate server certificate
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
sudo openssl rsa -in server-key.pem -out server-key.pem
sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Generate client certificate
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
sudo openssl rsa -in client-key.pem -out client-key.pem
sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Set proper permissions
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*-key.pem

Configure MySQL/MariaDB to use SSL:

[mysqld]
# SSL Configuration
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 the service and verify SSL:

sudo systemctl restart mysql

# Verify SSL is enabled
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

Implementing Strong Password Policies

For MySQL 8.0+:

-- Install password validation component
INSTALL COMPONENT 'file://component_validate_password';

-- Configure password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Make changes permanent
ALTER SYSTEM SET validate_password.policy = 'STRONG';

For MariaDB:

-- Install password validation plugin
INSTALL SONAME 'simple_password_check';

-- Configure password requirements
SET GLOBAL simple_password_check_minimal_length = 12;
SET GLOBAL simple_password_check_digits = 1;
SET GLOBAL simple_password_check_letters_same_case = 1;
SET GLOBAL simple_password_check_other_characters = 1;

Performance Optimization

Buffer Pool Configuration

The InnoDB buffer pool is the most critical parameter for MySQL/MariaDB performance:

[mysqld]
# Set to 70-80% of available RAM for dedicated database server
innodb_buffer_pool_size = 4G

# Number of buffer pool instances (1 per GB, max 64)
innodb_buffer_pool_instances = 4

# Load buffer pool state on startup
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

Query Cache Configuration (MySQL 5.7 and earlier)

Note: Query cache is removed in MySQL 8.0+

[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M
query_cache_min_res_unit = 4K

InnoDB Optimization

[mysqld]
# InnoDB Settings
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 0
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M

Connection Pool Optimization

[mysqld]
# Connection settings
max_connections = 500
max_connect_errors = 1000000
max_allowed_packet = 256M
wait_timeout = 300
interactive_timeout = 300

# Thread cache
thread_cache_size = 50
thread_stack = 256K

Table and Index Optimization

[mysqld]
# Table settings
table_open_cache = 4000
table_definition_cache = 2000

# Temporary tables
tmp_table_size = 256M
max_heap_table_size = 256M

# Sorting and grouping
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M

Monitoring Performance

Enable performance schema:

-- Check if performance_schema is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Enable in configuration file
[mysqld]
performance_schema = ON

Useful performance queries:

-- Show current connections
SHOW PROCESSLIST;

-- Show status variables
SHOW GLOBAL STATUS;

-- Show buffer pool usage
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS\G

-- Show table sizes
SELECT
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Show slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Backup Strategies

Logical Backups with mysqldump

Basic backup:

# Backup single database
mysqldump -u root -p database_name > backup_$(date +%Y%m%d).sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases_$(date +%Y%m%d).sql

# Backup with compression
mysqldump -u root -p --all-databases | gzip > all_databases_$(date +%Y%m%d).sql.gz

# Backup specific tables
mysqldump -u root -p database_name table1 table2 > tables_backup.sql

Advanced backup options:

# Backup with routines, triggers, and events
mysqldump -u root -p \
    --routines \
    --triggers \
    --events \
    --single-transaction \
    --quick \
    --lock-tables=false \
    --all-databases > complete_backup_$(date +%Y%m%d).sql

# Backup with master data for replication
mysqldump -u root -p \
    --master-data=2 \
    --single-transaction \
    --flush-logs \
    --all-databases > master_backup_$(date +%Y%m%d).sql

Physical Backups with Percona XtraBackup

Install Percona XtraBackup:

# Ubuntu/Debian
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt update
sudo apt install percona-xtrabackup-80 -y

# CentOS/Rocky
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install percona-xtrabackup-80 -y

Create backup:

# Full backup
sudo xtrabackup --backup \
    --user=root \
    --password=your_password \
    --target-dir=/backups/full_backup_$(date +%Y%m%d)

# Incremental backup
sudo xtrabackup --backup \
    --user=root \
    --password=your_password \
    --target-dir=/backups/incremental_$(date +%Y%m%d) \
    --incremental-basedir=/backups/full_backup_20260111

Automated Backup Script

Create a backup script:

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

Add the following content:

#!/bin/bash

# Configuration
BACKUP_DIR="/backups/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Perform backup
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --all-databases | gzip > $BACKUP_DIR/backup_$TIMESTAMP.sql.gz

# Remove old backups
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Log backup
echo "Backup completed: backup_$TIMESTAMP.sql.gz" >> $BACKUP_DIR/backup.log

Make executable and schedule:

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

# Add to crontab (daily at 2 AM)
sudo crontab -e

# Add this line:
0 2 * * * /usr/local/bin/mysql-backup.sh

Restoring from Backup

Restore mysqldump backup:

# Restore single database
mysql -u root -p database_name < backup_20260111.sql

# Restore all databases
mysql -u root -p < all_databases_20260111.sql

# Restore compressed backup
gunzip < backup_20260111.sql.gz | mysql -u root -p

Restore XtraBackup:

# Prepare backup
sudo xtrabackup --prepare --target-dir=/backups/full_backup_20260111

# Stop MySQL
sudo systemctl stop mysql

# Remove current data
sudo rm -rf /var/lib/mysql/*

# Copy backup
sudo xtrabackup --copy-back --target-dir=/backups/full_backup_20260111

# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Start MySQL
sudo systemctl start mysql

Troubleshooting

Common Connection Issues

Cannot connect to MySQL server:

# Check if service is running
sudo systemctl status mysql

# Check if MySQL is listening
sudo netstat -tulpn | grep mysql
sudo ss -tulpn | grep mysql

# Check error log
sudo tail -f /var/log/mysql/error.log

# Test connection
mysql -u root -p -h 127.0.0.1

# Verify socket file exists
ls -la /var/run/mysqld/mysqld.sock

Access Denied Errors

# Reset root password
# Stop MySQL
sudo systemctl stop mysql

# Start MySQL in safe mode
sudo mysqld_safe --skip-grant-tables &

# Login without password
mysql -u root

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

# Stop safe mode and start normally
sudo killall mysqld
sudo systemctl start mysql

Performance Issues

Identify slow queries:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Show current queries
SHOW FULL PROCESSLIST;

-- Kill problematic query
KILL QUERY process_id;

-- Analyze table
ANALYZE TABLE table_name;

-- Optimize table
OPTIMIZE TABLE table_name;

Check for locks:

-- Show locked tables
SHOW OPEN TABLES WHERE In_use > 0;

-- Show InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Show locks
SELECT * FROM performance_schema.data_locks;

Disk Space Issues

# Check disk usage
df -h

# Check MySQL data directory size
sudo du -sh /var/lib/mysql/

# Check individual databases
sudo du -sh /var/lib/mysql/*/

# Clean binary logs
mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

# Optimize tables to reclaim space
mysqlcheck -u root -p --optimize --all-databases

Repairing Corrupted Tables

# Check all tables
mysqlcheck -u root -p --all-databases --check

# Repair tables
mysqlcheck -u root -p --all-databases --repair

# For specific table
mysqlcheck -u root -p database_name table_name --repair

# InnoDB recovery
# Add to my.cnf and restart
[mysqld]
innodb_force_recovery = 1

# Values: 1-6 (increasing severity)
# After recovery, remove this setting

Common Error Codes

Error 1045: Access denied for user

• Check username and password
• Verify user exists and has proper privileges
• Check host permissions

Error 2002: Can't connect through socket

• Verify MySQL is running
• Check socket file location
• Verify file permissions

Error 1040: Too many connections

• Increase max_connections
• Check for connection leaks in application
• Implement connection pooling

Error 1064: SQL syntax error

• Review query syntax
• Check MySQL version compatibility
• Verify reserved keywords aren't used as identifiers

Conclusion

Properly installing and configuring MySQL or MariaDB is crucial for building reliable, high-performance applications. This guide has covered the complete process from installation through security hardening, performance optimization, and backup strategies.

Key Takeaways

1. Security First: Always run mysql_secure_installation and implement proper firewall rules
2. Optimize Configuration: Tune buffer pool and other parameters based on your workload
3. Regular Backups: Implement automated backup strategies with proper retention policies
4. Monitor Performance: Use built-in tools to track query performance and resource usage
5. Plan for Growth: Design your database architecture with scalability in mind

Next Steps

After completing this basic setup, consider exploring:

• Replication: Set up master-slave or master-master replication for high availability
• Clustering: Implement MySQL Cluster or Galera Cluster for distributed databases
• Advanced Security: Implement audit logging and encrypted connections
• Query Optimization: Learn to analyze and optimize slow queries
• Monitoring Tools: Deploy monitoring solutions like Percona Monitoring and Management

Best Practices Summary

• Keep MySQL/MariaDB updated with the latest security patches
• Use strong passwords and limit user privileges to minimum required
• Enable binary logging for point-in-time recovery
• Monitor slow queries and optimize regularly
• Test backups regularly to ensure recoverability
• Document your configuration and changes
• Use connection pooling in applications
• Implement proper indexing strategies
• Regular maintenance tasks (ANALYZE, OPTIMIZE)
• Keep detailed logs and monitor error logs

By following the guidelines in this comprehensive guide, you now have a solid foundation for running a secure, optimized, and reliable MySQL or MariaDB database server in production environments.

Additional Resources

• MySQL Official Documentation
• MariaDB Knowledge Base
• Percona Database Performance Blog
• MySQL Performance Tuning Guide
• MariaDB Server System Variables

Remember that database administration is an ongoing process. Continue learning, monitoring, and optimizing your database systems to ensure they meet your application's evolving needs.