MySQL LAMP Stack Setup - Complete Guide

🔴 Important: MySQL on RHEL8/OEL8

MySQL is NOT available in the default RHEL8/OEL8 AppStream repositories. You must use the Oracle MySQL Community Repository to install MySQL 8.0 or 8.4.

MariaDB is the default in RHEL8 repos. If you need MySQL specifically, follow the repository setup below.

📦 MySQL Repository Setup

Step 1: Add MySQL Yum Repository

Install Oracle MySQL Repository
# Download and install MySQL repo package for RHEL8
wget https://dev.mysql.com/get/mysql80-community-release-el8-10.noarch.rpm

# Install the repository package
sudo dnf install -y mysql80-community-release-el8-10.noarch.rpm

# Verify repository is enabled
sudo dnf repolist enabled | grep mysql

# Optional: Enable MySQL 8.4 Innovation (instead of 8.0 LTS)
sudo dnf config-manager --disable mysql80-community
sudo dnf config-manager --enable mysql84-community

📘 MySQL Version Info

MySQL 8.0: Long Term Support (LTS) - recommended for production
MySQL 8.4: Innovation release - latest features, shorter support cycle
MySQL 9.x: Available but very new - check compatibility first

Step 2: Disable MySQL Module (RHEL8 Specific)

Disable Conflicting MySQL Module
# RHEL8/OEL8 includes a mysql module that conflicts with Oracle's repo
# You MUST disable it before installing MySQL
sudo dnf module disable mysql -y

⚠️ Critical Step

Failing to disable the mysql module will result in installation errors or wrong package versions. This is a common gotcha on RHEL8/OEL8!

⚡ Quick Start - Complete Install

                    # Complete installation sequence
wget https://dev.mysql.com/get/mysql80-community-release-el8-10.noarch.rpm
sudo dnf install -y mysql80-community-release-el8-10.noarch.rpm
sudo dnf module disable mysql -y
sudo dnf install -y mysql-community-server mysql-community-client php-mysqlnd python3-PyMySQL perl-DBD-MySQL
sudo systemctl enable --now mysqld
sudo grep 'temporary password' /var/log/mysqld.log
sudo mysql_secure_installation
                

📦 Required Packages

MySQL Core

mysql-community-server MySQL server daemon (mysqld)

mysql-community-client MySQL client tools (mysql, mysqldump, etc.)

mysql-community-common Common files for server and client

mysql-community-libs Shared libraries for MySQL

PHP Integration

php-mysqlnd MySQL Native Driver for PHP (preferred)

php-pdo PHP Data Objects (PDO) interface

Python Integration

python3-PyMySQL Pure Python MySQL client library

mysql-connector-python Oracle's official Python connector (alternative)

Perl Integration

perl-DBD-MySQL Perl DBI driver for MySQL

perl-DBI Database independent interface (dependency)

Optional but Recommended

mysql-community-devel Development files for compiling against MySQL

mysql-shell MySQL Shell (advanced administration tool)

mysql-router MySQL Router (for HA/clustering)

🔧 Installation Steps

Complete Installation

One-Line Install (After Repository Setup)

                        sudo dnf install -y \
    mysql-community-server \
    mysql-community-client \
    mysql-community-devel \
    php-mysqlnd \
    php-pdo \
    python3-PyMySQL \
    perl-DBD-MySQL \
    perl-DBI
                    

Start MySQL and Find Temporary Password

Service Startup
# Enable MySQL to start on boot
sudo systemctl enable mysqld

# Start MySQL service
sudo systemctl start mysqld

# Check status
sudo systemctl status mysqld

# MySQL 8.0 generates a temporary root password on first start
# Find it in the log file:
sudo grep 'temporary password' /var/log/mysqld.log

# Output will look like:
# [Note] A temporary password is generated for root@localhost: Abc123Def456

⚠️ Important: Temporary Password

MySQL 8.0+ generates a random temporary password for root on first installation. You MUST change this password before you can do anything else. Use the password shown in the log file to login initially.

Secure Installation & Change Root Password

Security Hardening
# Run the secure installation wizard
sudo mysql_secure_installation

# You will be prompted for:
# 1. Current root password (use the temporary one from log)
# 2. New root password (must meet complexity requirements)
# 3. Remove anonymous users? YES
# 4. Disallow root login remotely? YES (unless needed)
# 5. Remove test database? YES
# 6. Reload privilege tables? YES

🔒 MySQL 8.0 Password Requirements

By default, MySQL 8.0 enforces strong password policies:

Minimum 8 characters
At least 1 uppercase letter
At least 1 lowercase letter
At least 1 number
At least 1 special character

Example valid password: MyPass123!

Verify Installation

Version Check
# Check MySQL version
mysql --version

# Login and check from SQL
mysql -u root -p -e "SELECT VERSION();"

# Check if MySQL is listening on port 3306
sudo ss -tlnp | grep 3306

# Verify MySQL is running
ps aux | grep mysqld

⚙️ Configuration Files

Main Configuration File /etc/my.cnf

                    # MySQL Server Configuration
# Best practices for web applications

[mysqld]
# Basic Settings
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Server ID (required for replication, should be unique)
server-id=1

# Character Set (UTF-8 for international support)
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Connection Settings
max_connections=150
max_allowed_packet=64M
connect_timeout=10
wait_timeout=28800
interactive_timeout=28800

# Buffer Pool (adjust based on available RAM)
# Rule of thumb: 70-80% of RAM for dedicated DB server
# For shared LAMP server: 25-40% of RAM
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1

# InnoDB Settings
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=256M
innodb_log_buffer_size=16M
innodb_flush_method=O_DIRECT

# MySQL 8.0 Default Authentication Plugin
# Use mysql_native_password for compatibility with older clients/apps
# Or use caching_sha2_password (default) for better security
default-authentication-plugin=mysql_native_password

# Binary Logging (for replication and point-in-time recovery)
log_bin=/var/log/mysql/mysql-bin
binlog_expire_logs_seconds=604800  # 7 days
max_binlog_size=100M
binlog_format=ROW

# Slow Query Log
slow_query_log=1
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time=2
log_queries_not_using_indexes=0

# General Log (disable in production for performance)
general_log=0
general_log_file=/var/log/mysqld.log

# Performance Schema
performance_schema=ON

# Table Cache
table_open_cache=2000
table_definition_cache=1000

# Thread Cache
thread_cache_size=50

# Temporary Tables
tmp_table_size=64M
max_heap_table_size=64M

# Networking
bind-address=127.0.0.1    # Change to 0.0.0.0 for remote access
port=3306
mysqlx-port=33060         # X Protocol port (for MySQL Shell)

# SQL Modes (can affect compatibility)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# Disable symbolic links (security)
symbolic-links=0

[mysql]
# Client defaults
default-character-set=utf8mb4

[client]
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8mb4
                

⚠️ MySQL 8.0 Authentication Changes

MySQL 8.0 introduced caching_sha2_password as the default authentication plugin, which is more secure but can cause compatibility issues with older applications. If you experience connection issues:

Change to mysql_native_password in config (shown above)
Or alter existing users: ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

💾 Initial Database Setup

Create Database and User

SQL Commands
-- Login as root
mysql -u root -p

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

-- Create user with mysql_native_password (for compatibility)
CREATE USER 'webapp_user'@'localhost' 
  IDENTIFIED WITH mysql_native_password 
  BY 'StrongPass123!';

-- Or with caching_sha2_password (default, more secure)
CREATE USER 'webapp_user'@'localhost' 
  IDENTIFIED WITH caching_sha2_password 
  BY 'StrongPass123!';

-- Grant privileges
GRANT ALL PRIVILEGES ON webapp_db.* 
  TO 'webapp_user'@'localhost';

-- For remote access (specific IP or network)
CREATE USER 'webapp_user'@'192.168.1.%' 
  IDENTIFIED WITH mysql_native_password 
  BY 'StrongPass123!';

GRANT ALL PRIVILEGES ON webapp_db.* 
  TO 'webapp_user'@'192.168.1.%';

-- Flush privileges
FLUSH PRIVILEGES;

-- Verify users and authentication methods
SELECT User, Host, plugin FROM mysql.user;

-- Show current authentication plugin for a user
SELECT User, Host, plugin 
FROM mysql.user 
WHERE User = 'webapp_user';

exit;

🔌 Language Integration Examples

PHP Connection Examples

MySQLi (Object-Oriented) db_connect_mysqli.php

                    <?php
// MySQLi Object-Oriented Connection
$servername = "localhost";
$username = "webapp_user";
$password = "StrongPass123!";
$database = "webapp_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully to MySQL " . $conn->server_info . "\n";

// Set charset to UTF-8
$conn->set_charset("utf8mb4");

// Example query
$sql = "SELECT VERSION() as version";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $row = $result->fetch_assoc();
    echo "MySQL Version: " . $row["version"] . "\n";
}

// Example prepared statement (secure against SQL injection)
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$email = "user@example.com";
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "User: " . $row['name'] . "\n";
}

// Close connections
$stmt->close();
$conn->close();
?>
                

PDO (Recommended - Best Practice) db_connect_pdo.php

                    <?php
// PDO Connection (Best Practice for Modern PHP)
$host = 'localhost';
$db = 'webapp_db';
$user = 'webapp_user';
$pass = 'StrongPass123!';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully using PDO\n";
    
    // Example query
    $stmt = $pdo->query('SELECT VERSION() as version');
    $version = $stmt->fetch();
    echo "MySQL Version: " . $version['version'] . "\n";
    
    // Example prepared statement (secure)
    $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
    $stmt->execute(['email' => 'user@example.com']);
    $users = $stmt->fetchAll();
    
    foreach ($users as $user) {
        echo "User: " . $user['name'] . "\n";
    }
    
    // Transaction example
    $pdo->beginTransaction();
    try {
        $pdo->exec("INSERT INTO users (name, email) VALUES ('John', 'john@example.com')");
        $pdo->exec("INSERT INTO log (action) VALUES ('user_created')");
        $pdo->commit();
        echo "Transaction committed\n";
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "Transaction failed: " . $e->getMessage() . "\n";
    }
    
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
                

Python Connection Examples

PyMySQL Example db_connect_pymysql.py

                    #!/usr/bin/env python3
# PyMySQL Connection Example for MySQL 8.0

import pymysql
from pymysql.cursors import DictCursor

# Connection parameters
config = {
    'host': 'localhost',
    'user': 'webapp_user',
    'password': 'StrongPass123!',
    'database': 'webapp_db',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor,
    # For MySQL 8.0 with caching_sha2_password:
    'ssl': None,  # Or configure SSL for secure auth
}

try:
    # Create connection
    connection = pymysql.connect(**config)
    print(f"Connected successfully using PyMySQL")
    
    with connection:
        with connection.cursor() as cursor:
            # Example query
            cursor.execute("SELECT VERSION() as version")
            result = cursor.fetchone()
            print(f"MySQL Version: {result['version']}")
            
            # Example SELECT with parameters (secure)
            sql = "SELECT * FROM users WHERE email = %s"
            cursor.execute(sql, ('user@example.com',))
            user = cursor.fetchone()
            if user:
                print(f"Found user: {user['name']}")
            
            # Example INSERT with parameters (secure)
            sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
            cursor.execute(sql, ('Jane Doe', 'jane@example.com'))
            print(f"Inserted user with ID: {cursor.lastrowid}")
        
        # Commit changes
        connection.commit()
        
except pymysql.Error as e:
    print(f"Database Error: {e}")
except Exception as e:
    print(f"Error: {e}")
finally:
    if connection:
        connection.close()
        print("Connection closed")
                

MySQL Connector/Python (Oracle Official) db_connect_connector.py

                    #!/usr/bin/env python3
# MySQL Connector/Python - Oracle's Official Connector
# Install: pip3 install mysql-connector-python --break-system-packages

import mysql.connector
from mysql.connector import Error

config = {
    'host': 'localhost',
    'user': 'webapp_user',
    'password': 'StrongPass123!',
    'database': 'webapp_db',
    'charset': 'utf8mb4',
    'use_unicode': True,
    'autocommit': False,
}

try:
    # Create connection
    connection = mysql.connector.connect(**config)
    
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Connected to MySQL Server version {db_info}")
        
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT DATABASE();")
        record = cursor.fetchone()
        print(f"Connected to database: {record['DATABASE()']}")
        
        # Example prepared statement
        query = "SELECT * FROM users WHERE email = %s"
        cursor.execute(query, ('user@example.com',))
        
        for row in cursor:
            print(f"User: {row['name']}")
        
        cursor.close()

except Error as e:
    print(f"Error connecting to MySQL: {e}")
finally:
    if connection and connection.is_connected():
        connection.close()
        print("MySQL connection closed")
                

Context Manager Pattern (Best Practice) db_context.py

                    #!/usr/bin/env python3
# Context Manager for Database Connections

import pymysql
from contextlib import contextmanager

@contextmanager
def get_db_connection():
    """Context manager for database connections"""
    connection = pymysql.connect(
        host='localhost',
        user='webapp_user',
        password='StrongPass123!',
        database='webapp_db',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )
    try:
        yield connection
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    finally:
        connection.close()

# Usage example
def get_user_by_email(email):
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
            return cursor.fetchone()

# Transaction example
def create_user_with_log(name, email):
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s)",
                (name, email)
            )
            user_id = cursor.lastrowid
            
            cursor.execute(
                "INSERT INTO audit_log (action, user_id) VALUES (%s, %s)",
                ('user_created', user_id)
            )
            return user_id

# Main
if __name__ == '__main__':
    user = get_user_by_email('test@example.com')
    if user:
        print(f"Found user: {user['name']}")
    
    new_id = create_user_with_log('Test User', 'test@example.com')
    print(f"Created user with ID: {new_id}")
                

Perl Connection Examples

DBI Connection Example db_connect.pl

                    #!/usr/bin/perl
# Perl DBI MySQL Connection Example

use strict;
use warnings;
use DBI;

# Connection parameters
my $host = "localhost";
my $database = "webapp_db";
my $user = "webapp_user";
my $password = "StrongPass123!";
my $dsn = "DBI:mysql:database=$database;host=$host;charset=utf8mb4";

# Additional connection attributes for MySQL 8.0
my %attr = (
    RaiseError => 1,
    AutoCommit => 1,
    PrintError => 0,
    mysql_enable_utf8mb4 => 1,
);

# Connect to database
my $dbh = DBI->connect($dsn, $user, $password, \%attr) 
    or die "Could not connect: $DBI::errstr";

print "Connected successfully using DBI\n";

# Get MySQL version
my $sth = $dbh->prepare("SELECT VERSION() as version");
$sth->execute();

while (my $row = $sth->fetchrow_hashref()) {
    print "MySQL Version: $row->{version}\n";
}

# Example prepared statement (secure against SQL injection)
my $select = $dbh->prepare("SELECT * FROM users WHERE email = ?");
$select->execute('user@example.com');

while (my $row = $select->fetchrow_hashref()) {
    print "User: $row->{name}\n";
}

# Example INSERT with prepared statement
my $insert = $dbh->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$insert->execute('John Doe', 'john@example.com');
print "Inserted user with ID: " . $dbh->{mysql_insertid} . "\n";

# Cleanup
$sth->finish();
$select->finish();
$insert->finish();
$dbh->disconnect();
print "Connection closed\n";
                

Transaction Handling Example db_transaction.pl

                    #!/usr/bin/perl
# Robust Transaction and Error Handling Example

use strict;
use warnings;
use DBI;

my $dsn = "DBI:mysql:database=webapp_db;host=localhost;charset=utf8mb4";
my $user = "webapp_user";
my $password = "StrongPass123!";

my $dbh;
eval {
    # Connect with transaction support
    $dbh = DBI->connect($dsn, $user, $password, {
        RaiseError => 1,
        AutoCommit => 0,  # Disable auto-commit for transactions
        PrintError => 0,
        mysql_enable_utf8mb4 => 1,
    });
    
    print "Connected successfully\n";
    
    # Begin transaction (implicit with AutoCommit => 0)
    
    # Execute multiple statements as a transaction
    my $insert_user = $dbh->prepare(
        "INSERT INTO users (name, email) VALUES (?, ?)"
    );
    $insert_user->execute('Jane Smith', 'jane.smith@example.com');
    my $user_id = $dbh->{mysql_insertid};
    
    my $insert_log = $dbh->prepare(
        "INSERT INTO audit_log (user_id, action, timestamp) VALUES (?, ?, NOW())"
    );
    $insert_log->execute($user_id, 'user_created');
    
    # Commit transaction
    $dbh->commit();
    print "Transaction committed successfully\n";
    print "Created user with ID: $user_id\n";
};

if ($@) {
    # Error occurred, rollback transaction
    print "Error: $@\n";
    eval { $dbh->rollback() } if $dbh;
    print "Transaction rolled back\n";
}

# Always disconnect
$dbh->disconnect() if $dbh;
print "Connection closed\n";
                

🧪 Test Connection Scripts

Quick Connection Test

test_all_mysql_connections.sh
#!/bin/bash
# Test all language connections to MySQL 8.0

echo "Testing MySQL 8.0 Connections..."
echo "=================================="
echo

# Test MySQL CLI
echo "Testing MySQL CLI..."
mysql -u webapp_user -pStrongPass123! -e "SELECT 'CLI Connection: OK' as status;" 2>/dev/null
if [ $? -eq 0 ]; then
    echo "✓ MySQL CLI: Connected"
else
    echo "✗ MySQL CLI: Failed"
fi
echo

# Test PHP MySQLi
echo "Testing PHP MySQLi..."
php -r '
$conn = new mysqli("localhost", "webapp_user", "StrongPass123!", "webapp_db");
if ($conn->connect_error) {
    echo "✗ PHP MySQLi Failed: " . $conn->connect_error . "\n";
    exit(1);
}
echo "✓ PHP MySQLi: Connected (MySQL " . $conn->server_info . ")\n";
$conn->close();
'

# Test PHP PDO
echo "Testing PHP PDO..."
php -r '
try {
    $pdo = new PDO("mysql:host=localhost;dbname=webapp_db", "webapp_user", "StrongPass123!");
    $version = $pdo->query("SELECT VERSION()")->fetch();
    echo "✓ PHP PDO: Connected (MySQL " . $version[0] . ")\n";
} catch (PDOException $e) {
    echo "✗ PHP PDO Failed: " . $e->getMessage() . "\n";
}
'

# Test Python PyMySQL
echo "Testing Python PyMySQL..."
python3 -c "
import pymysql
try:
    conn = pymysql.connect(
        host='localhost',
        user='webapp_user',
        password='StrongPass123!',
        database='webapp_db'
    )
    cursor = conn.cursor()
    cursor.execute('SELECT VERSION()')
    version = cursor.fetchone()[0]
    print(f'✓ Python PyMySQL: Connected (MySQL {version})')
    conn.close()
except Exception as e:
    print(f'✗ Python Failed: {e}')
"

# Test Perl DBI
echo "Testing Perl DBI..."
perl -MDBI -e '
my $dbh = DBI->connect(
    "DBI:mysql:database=webapp_db;host=localhost",
    "webapp_user",
    "StrongPass123!",
    {RaiseError=>1, PrintError=>0}
);
if ($dbh) {
    my $version = $dbh->selectrow_array("SELECT VERSION()");
    print "✓ Perl DBI: Connected (MySQL $version)\n";
    $dbh->disconnect();
} else {
    print "✗ Perl DBI Failed\n";
}
'

echo
echo "=================================="
echo "Connection tests complete!"

🔥 Firewall Configuration

For Remote Access (if needed)

Firewall Rules
# Allow MySQL through firewall (port 3306)
sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

# Or allow specific port
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# Verify
sudo firewall-cmd --list-all

# For MySQL X Protocol (port 33060) - if using MySQL Shell
sudo firewall-cmd --permanent --add-port=33060/tcp
sudo firewall-cmd --reload

⚠️ Security Warning

Only open port 3306 if you absolutely need remote database access. For web applications, the database should typically only be accessible locally (bind-address=127.0.0.1). Use SSH tunneling for remote administration instead.

🔒 SELinux Configuration

SELinux Settings for MySQL

SELinux Configuration
# Check SELinux status
getenforce

# Allow httpd to connect to MySQL (for PHP web apps)
sudo setsebool -P httpd_can_network_connect_db on

# If using custom data directory, set context
sudo semanage fcontext -a -t mysqld_db_t "/custom/mysql/datadir(/.*)?"
sudo restorecon -Rv /custom/mysql/datadir

# Allow MySQL to bind to non-standard ports (if needed)
sudo semanage port -a -t mysqld_port_t -p tcp 3307

# Check MySQL SELinux booleans
getsebool -a | grep mysql

# View SELinux denials (if experiencing issues)
sudo ausearch -m avc -ts recent | grep mysql

💡 Common SELinux Issue

If PHP can't connect to MySQL, it's often because httpd_can_network_connect_db is disabled. Enable it with the setsebool command above.

💾 Backup & Maintenance

Backup Scripts

mysql_backup.sh
#!/bin/bash
# MySQL 8.0 Backup Script

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="webapp_db"
DB_USER="webapp_user"
DB_PASS="StrongPass123!"

# MySQL 8.0 requires mysql_config_editor or password on command line
# For production, use mysql_config_editor for secure password storage

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup using mysqldump
mysqldump -u $DB_USER -p$DB_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --set-gtid-purged=OFF \
    $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Alternative: Backup all databases
# mysqldump -u $DB_USER -p$DB_PASS --all-databases \
#     --single-transaction --routines --triggers --events \
#     | gzip > $BACKUP_DIR/all_databases_${DATE}.sql.gz

# Keep only last 7 days of backups
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
ls -lh $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

Secure Password Storage (MySQL 8.0)

Setup mysql_config_editor
# Store credentials securely for backup scripts
# This is the recommended way for MySQL 8.0

# Setup login-path (encrypted password storage)
mysql_config_editor set \
    --login-path=backup \
    --host=localhost \
    --user=webapp_user \
    --password

# Enter password when prompted

# Verify (won't show password)
mysql_config_editor print --all

# Use in backup script:
mysqldump --login-path=backup \
    --single-transaction \
    webapp_db | gzip > backup.sql.gz

Automated Backup with Cron

Setup Daily Backup
# Create backup script
sudo vi /usr/local/bin/mysql_backup.sh
sudo chmod +x /usr/local/bin/mysql_backup.sh

# Edit root's crontab
sudo crontab -e

# Add line for daily 2 AM backup
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

⚡ Performance Tuning

Monitor Performance

Performance Monitoring Commands
# Show current connections and queries
mysql -u root -p -e "SHOW PROCESSLIST;"

# Show full processlist (not truncated)
mysql -u root -p -e "SHOW FULL PROCESSLIST;"

# Show global status variables
mysql -u root -p -e "SHOW GLOBAL STATUS;"

# Show InnoDB status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

# Check slow queries
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

# View current configuration
mysql -u root -p -e "SHOW VARIABLES;"

# MySQL 8.0 Performance Schema queries
mysql -u root -p -e "
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;"

📊 Key Performance Metrics

Metric	Description
`Threads_connected`	Current active connections
`Threads_running`	Currently executing queries
`Questions`	Total queries executed
`Slow_queries`	Queries exceeding long_query_time
`Innodb_buffer_pool_reads`	Disk reads (should be low)
`Innodb_buffer_pool_read_requests`	Read requests (mostly from cache)

🔍 Troubleshooting

Common Issues and Solutions

Issue	Solution
Can't find temporary password	`sudo grep 'temporary password' /var/log/mysqld.log`
Password doesn't meet requirements	Use at least 8 chars with upper, lower, number, special Or adjust: `SET GLOBAL validate_password.length=6;`
Authentication plugin error	Change user to mysql_native_password: `ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'pass';`
Can't connect from PHP	Check SELinux: `sudo setsebool -P httpd_can_network_connect_db on`
Too many connections	Increase max_connections in /etc/my.cnf Check for connection leaks in application
Slow queries	Enable slow query log, analyze with pt-query-digest Add indexes, optimize queries with EXPLAIN
MySQL won't start	Check logs: `sudo tail -f /var/log/mysqld.log` Check disk space, permissions on datadir

Log Files Location

Important Log Files
# Error log
/var/log/mysqld.log

# Slow query log (if enabled)
/var/log/mysqld-slow.log

# Binary logs (if enabled)
/var/log/mysql/mysql-bin.*

# View recent errors
sudo tail -f /var/log/mysqld.log

# Search for errors
sudo grep -i error /var/log/mysqld.log

# Check log file sizes
sudo du -sh /var/log/mysql*

🆕 MySQL 8.0 Specific Features

New in MySQL 8.0

📢 Major Changes from MySQL 5.7

Default Authentication: caching_sha2_password (was mysql_native_password)
Data Dictionary: Transactional, crash-safe data dictionary
Roles: Native role support for privilege management
Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), etc.
CTEs: Common Table Expressions (WITH clause)
JSON: Enhanced JSON functions and indexing
Invisible Indexes: Test index removal without dropping
Descending Indexes: True descending index support
Atomic DDL: DDL statements are atomic and crash-safe

Using Roles (MySQL 8.0 Feature)

Role-Based Access Control
-- Create roles
CREATE ROLE 'app_readonly', 'app_readwrite', 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON webapp_db.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'app_readwrite';
GRANT ALL PRIVILEGES ON webapp_db.* TO 'app_admin';

-- Create users and assign roles
CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'ReportPass123!';
GRANT 'app_readonly' TO 'reporter'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_readonly' TO 'reporter'@'localhost';

-- View roles
SHOW GRANTS FOR 'reporter'@'localhost';

📚 Additional Resources

Useful Commands Reference

                    # Service management
sudo systemctl start mysqld
sudo systemctl stop mysqld
sudo systemctl restart mysqld
sudo systemctl status mysqld
sudo systemctl enable mysqld

# Reset root password (if forgotten)
sudo systemctl stop mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';
exit;
sudo systemctl restart mysqld

# Import/Export databases
# Export
mysqldump -u username -p database_name > backup.sql

# Import
mysql -u username -p database_name <  backup.sql

# Optimize all tables
mysqlcheck -u root -p --optimize --all-databases

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

# Analyze tables (update statistics)
mysqlcheck -u root -p --analyze --all-databases

# Check MySQL variables
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
                

💡 Best Practices Summary

Always use prepared statements to prevent SQL injection
Use UTF-8 (utf8mb4) character encoding for full Unicode support
Regular backups are essential - automate with cron and test restores
Monitor slow query log and optimize problematic queries
Keep MySQL updated with security patches
Use connection pooling in production applications
Limit remote access; use SSH tunneling for remote admin
Configure appropriate buffer pool size (not too large!)
Use InnoDB engine (default in MySQL 8.0) for ACID compliance
For MySQL 8.0, understand caching_sha2_password authentication
Use mysql_config_editor for secure password storage in scripts
Enable binary logging for point-in-time recovery

📦 MySQL Versions & Repositories

Available MySQL Versions from Oracle Repository:

MySQL 8.0 (LTS): Long Term Support - recommended for production
MySQL 8.4 (Innovation): Latest features, quarterly updates
MySQL 9.x: Future releases - check compatibility

Official MySQL Downloads: https://dev.mysql.com/downloads/

MySQL Documentation: https://dev.mysql.com/doc/