Linux Mastery 100 #56

Intermediate

Tutorial

15 min read

October 24, 2025

luc

MySQL Administration Linux: Database Management Guide

Reading Progress 0%

Quick Actions

All Guides

Knowledge Overview

Prerequisites

Linux command line proficiency, Relational database concepts, linux system administration, bash scripting basics, text editor skills

Time Investment

15 minutes reading time
30-45 minutes hands-on practice

Guide Content

What is MySQL Administration on Linux?

MySQL administration Linux involves comprehensive database server management including installation, user privilege control, automated backup implementation, replication configuration, and performance tuning. Therefore, database administrators can maintain secure, highly available MySQL/MariaDB instances that ensure data integrity and optimal query performance across production environments.

Quick Command Overview:

Bash

# Check MySQL service status
sudo systemctl status mysql

# Access MySQL shell
mysql -u root -p

# Create database and user
CREATE DATABASE production_db;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON production_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

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

How to Install MySQL/MariaDB on Linux?
What is MySQL User Management and Privilege Control?
How to Implement MySQL Backup Strategies?
What is MySQL Replication Configuration?
How to Optimize MySQL Performance?
What are MySQL Security Best Practices?
How to Monitor MySQL Server Health?
FAQ
Troubleshooting Common Issues
Additional Resources

How to Install MySQL/MariaDB on Linux?

Installing MySQL administration Linux begins with selecting between MySQL Community Server and MariaDB. Consequently, both database systems provide robust relational database management with slightly different feature sets and licensing models.

Installation on Ubuntu/Debian

Bash

# Update package repository
sudo apt update

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

# Install MariaDB (alternative)
sudo apt install mariadb-server -y

# Secure the installation
sudo mysql_secure_installation

Installation on RHEL/CentOS/Rocky Linux

Bash

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

# Install MariaDB
sudo dnf install mariadb-server -y

# Enable and start service
sudo systemctl enable mysqld
sudo systemctl start mysqld

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

Post-Installation Configuration

Bash

# Set root password and security options
sudo mysql_secure_installation

# Configuration prompts:
# - Set root password: YES
# - Remove anonymous users: YES
# - Disallow root login remotely: YES
# - Remove test database: YES
# - Reload privilege tables: YES

Initial Connection:

Bash

# Connect as root user
mysql -u root -p

# Verify installation
mysql> SELECT VERSION();
mysql> SHOW DATABASES;
mysql> STATUS;

For comprehensive MySQL installation documentation, visit the official MySQL installation guide and MariaDB documentation.

What is MySQL User Management and Privilege Control?

Effective MySQL administration Linux requires granular user access control. Moreover, implementing the principle of least privilege ensures database security and operational integrity.

Creating Database Users

Bash

# Access MySQL shell
mysql -u root -p

# Create application user
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd';

# Create user with remote access
CREATE USER 'remote_user'@'192.168.1.%' IDENTIFIED BY 'RemoteP@ss';

# Create user accessible from any host (use cautiously)
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'AdminP@ss';

Granting Privileges

Bash

# Grant all privileges on specific database
GRANT ALL PRIVILEGES ON production_db.* TO 'webapp_user'@'localhost';

# Grant read-only access
GRANT SELECT ON production_db.* TO 'readonly_user'@'localhost';

# Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.orders 
TO 'order_manager'@'localhost';

# Grant administrative privileges
GRANT RELOAD, PROCESS, SHOW DATABASES, REPLICATION CLIENT 
ON *.* TO 'monitoring_user'@'localhost';

# Apply privilege changes
FLUSH PRIVILEGES;

Viewing and Revoking Privileges

Bash

# Show user privileges
SHOW GRANTS FOR 'webapp_user'@'localhost';

# List all users
SELECT user, host, authentication_string FROM mysql.user;

# Revoke privileges
REVOKE INSERT, UPDATE ON production_db.* FROM 'webapp_user'@'localhost';

# Remove user completely
DROP USER 'old_user'@'localhost';
FLUSH PRIVILEGES;

Password Management

Bash

# Change user password
ALTER USER 'webapp_user'@'localhost' IDENTIFIED BY 'NewSecureP@ss';

# Set password expiration
ALTER USER 'webapp_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

# Disable password expiration
ALTER USER 'webapp_user'@'localhost' PASSWORD EXPIRE NEVER;

# Force password change on next login
ALTER USER 'webapp_user'@'localhost' PASSWORD EXPIRE;

Privilege Verification Script:

Bash

#!/bin/bash
# Check user privileges across all databases

mysql -u root -p -e "
SELECT user, host, 
GROUP_CONCAT(DISTINCT db SEPARATOR ', ') AS databases
FROM mysql.db
WHERE user != ''
GROUP BY user, host;
"

How to Implement MySQL Backup Strategies?

Database backup strategies represent critical MySQL administration Linux responsibilities. Similarly, implementing automated backup solutions protects against data loss and enables point-in-time recovery.

Logical Backups with mysqldump

Bash

# Single database backup
mysqldump -u root -p database_name > backup_database.sql

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

# Compressed backup
mysqldump -u root -p database_name | gzip > backup_$(date +%Y%m%d).sql.gz

# Backup with routines and triggers
mysqldump -u root -p --routines --triggers --events database_name > full_backup.sql

# Exclude specific tables
mysqldump -u root -p database_name \
--ignore-table=database_name.logs \
--ignore-table=database_name.temp_data > partial_backup.sql

Automated Backup Script

Bash

#!/bin/bash
# MySQL Backup Automation Script

# Configuration
DB_USER="backup_user"
DB_PASS="BackupP@ssw0rd"
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup_log.txt"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Start backup process
echo "[$(date)] Starting MySQL backup..." >> "$LOG_FILE"

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

# Backup each database
for DB in $DATABASES; do
    echo "[$(date)] Backing up database: $DB" >> "$LOG_FILE"
    
    mysqldump -u "$DB_USER" -p"$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    
    if [ $? -eq 0 ]; then
        echo "[$(date)] Successfully backed up $DB" >> "$LOG_FILE"
    else
        echo "[$(date)] ERROR: Failed to backup $DB" >> "$LOG_FILE"
    fi
done

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Removed backups older than $RETENTION_DAYS days" >> "$LOG_FILE"

echo "[$(date)] Backup process completed" >> "$LOG_FILE"

Physical Backups with Percona XtraBackup

Bash

# Install Percona XtraBackup
sudo apt install percona-xtrabackup-80 -y  # Ubuntu/Debian
sudo dnf install percona-xtrabackup-80 -y  # RHEL/CentOS

# Full backup
xtrabackup --backup --target-dir=/var/backups/mysql/full

# Incremental backup
xtrabackup --backup --target-dir=/var/backups/mysql/inc1 \
--incremental-basedir=/var/backups/mysql/full

# Prepare backup for restore
xtrabackup --prepare --target-dir=/var/backups/mysql/full

# Restore backup
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/var/backups/mysql/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Point-in-Time Recovery

Bash

# Enable binary logging in /etc/mysql/my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

# Restart MySQL
sudo systemctl restart mysql

# Restore from full backup
mysql -u root -p < full_backup.sql

# Apply binary logs for point-in-time recovery
mysqlbinlog --start-datetime="2025-10-24 09:00:00" \
--stop-datetime="2025-10-24 11:30:00" \
/var/log/mysql/mysql-bin.000001 | mysql -u root -p

Backup Verification Script

Bash

#!/bin/bash
# Verify MySQL backup integrity

BACKUP_FILE="$1"
TEST_DB="backup_test_$(date +%s)"

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file.sql>"
    exit 1
fi

echo "Creating test database: $TEST_DB"
mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

echo "Restoring backup to test database..."
if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip -c "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"
else
    mysql -u root -p "$TEST_DB" < "$BACKUP_FILE"
fi

if [ $? -eq 0 ]; then
    echo "✓ Backup verification successful"
    TABLE_COUNT=$(mysql -u root -p -s -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB';")
    echo "✓ Tables restored: $TABLE_COUNT"
else
    echo "✗ Backup verification failed"
fi

# Cleanup
mysql -u root -p -e "DROP DATABASE $TEST_DB;"

Schedule automated backups with cron as discussed in our Cron Jobs and Task Scheduling guide.

What is MySQL Replication Configuration?

MySQL replication enables data redundancy and load distribution. Furthermore, implementing master-slave replication provides high availability for production environments requiring minimal downtime.

Master Server Configuration

Bash

# Edit MySQL configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add replication settings
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = production_db
binlog_format = ROW
max_binlog_size = 100M
expire_logs_days = 10

# Restart MySQL
sudo systemctl restart mysql

Create Replication User

Bash

# On master server
mysql -u root -p

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'ReplicationP@ss';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

# Get master status
SHOW MASTER STATUS;
# Note: File name and Position values

Slave Server Configuration

Bash

# Edit slave configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = production_db
read_only = 1

# Restart MySQL
sudo systemctl restart mysql

Configure Slave Replication

Bash

# On slave server
mysql -u root -p

# Stop slave if running
STOP SLAVE;

# Configure master connection
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='ReplicationP@ss',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

# Start slave
START SLAVE;

# Check slave status
SHOW SLAVE STATUS\G

# Verify replication
# Look for:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0

Replication Monitoring Script

Bash

#!/bin/bash
# Monitor MySQL replication health

MYSQL_USER="monitoring_user"
MYSQL_PASS="MonitorP@ss"
ALERT_EMAIL="dba@company.com"

# Check slave status
SLAVE_STATUS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G")

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

echo "=== MySQL Replication Status ==="
echo "IO Thread: $IO_RUNNING"
echo "SQL Thread: $SQL_RUNNING"
echo "Lag: $SECONDS_BEHIND seconds"

# Alert if replication issues detected
if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "CRITICAL: Replication threads stopped" | \
    mail -s "MySQL Replication Alert" $ALERT_EMAIL
    exit 1
fi

if [ "$SECONDS_BEHIND" -gt 60 ]; then
    echo "WARNING: Replication lag exceeds 60 seconds" | \
    mail -s "MySQL Replication Lag Warning" $ALERT_EMAIL
    exit 1
fi

echo "✓ Replication healthy"

Semi-Synchronous Replication

Bash

# Install plugin on master
mysql -u root -p
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

# Install plugin on slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# Restart slave IO thread
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

# Verify semi-sync status
SHOW STATUS LIKE 'Rpl_semi_sync%';

How to Optimize MySQL Performance?

Performance optimization transforms MySQL administration Linux from functional to exceptional. Additionally, systematic tuning of configuration parameters, queries, and indexes dramatically improves database response times.

Configuration Tuning (/etc/mysql/my.cnf)

Bash

[mysqld]
# Connection Settings
max_connections = 200
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600

# Buffer Pool Settings (InnoDB)
innodb_buffer_pool_size = 4G  # 70-80% of available RAM
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

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

# Table Cache
table_open_cache = 4000
table_definition_cache = 2000

# Thread Settings
thread_cache_size = 50
thread_stack = 256K

# Temp Tables
tmp_table_size = 64M
max_heap_table_size = 64M

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

Query Performance Analysis

Bash

# Enable slow query log
mysql -u root -p
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# Analyze slow queries
sudo pt-query-digest /var/log/mysql/slow-query.log

# Explain query execution plan
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 12345 
AND order_date > '2025-01-01';

# Analyze table for optimization
ANALYZE TABLE orders;

# Check table status
SHOW TABLE STATUS LIKE 'orders'\G

Index Optimization

Bash

# Create single column index
CREATE INDEX idx_customer_id ON orders(customer_id);

# Create composite index
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

# Create unique index
CREATE UNIQUE INDEX idx_email ON customers(email);

# Show indexes on table
SHOW INDEX FROM orders;

# Drop unused index
DROP INDEX idx_old_column ON orders;

# Identify missing indexes
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size_MB"
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Performance Monitoring Queries

Bash

# Check current connections
SHOW PROCESSLIST;

# Show connection statistics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

# Buffer pool hit ratio (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

# Table lock statistics
SHOW STATUS LIKE 'Table_locks%';

# Check for table fragmentation
SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS "Data_MB",
    ROUND(DATA_FREE / 1024 / 1024, 2) AS "Free_MB",
    ROUND(DATA_FREE / DATA_LENGTH * 100, 2) AS "Fragmentation_%"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'production_db'
AND DATA_FREE > 0
ORDER BY Fragmentation_% DESC;

# Optimize fragmented tables
OPTIMIZE TABLE orders;

Performance Tuning Script

Bash

#!/bin/bash
# MySQL Performance Tuning Advisor

echo "=== MySQL Performance Analysis ==="
echo

# Check buffer pool efficiency
mysql -u root -p -e "
SELECT 
    ROUND(100 * (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)), 2) 
    AS buffer_pool_hit_ratio
FROM 
    (SELECT variable_value AS Innodb_buffer_pool_reads 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_reads') reads,
    (SELECT variable_value AS Innodb_buffer_pool_read_requests 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_read_requests') requests;
"

echo
echo "Buffer Pool Hit Ratio should be > 99%"
echo

# Check for tables without primary keys
mysql -u root -p -e "
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc 
    ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA 
    AND t.TABLE_NAME = tc.TABLE_NAME 
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND tc.CONSTRAINT_NAME IS NULL
ORDER BY t.TABLE_ROWS DESC;
"

echo
echo "Tables without primary keys shown above"

Learn more about comprehensive performance monitoring in our System Performance Monitoring article.

What are MySQL Security Best Practices?

Security hardening represents essential MySQL administration Linux practices. Likewise, implementing defense-in-depth strategies protects sensitive data from unauthorized access and malicious attacks.

Secure Installation Checklist

Bash

# 1. Run security script
sudo mysql_secure_installation

# 2. Remove anonymous users
DELETE FROM mysql.user WHERE User='';

# 3. Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

# 4. Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

# 5. Flush privileges
FLUSH PRIVILEGES;

Network Security Configuration

Bash

# Bind to localhost only (in /etc/mysql/my.cnf)
[mysqld]
bind-address = 127.0.0.1

# Enable SSL/TLS connections
[mysqld]
require_secure_transport = ON
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

# Verify SSL connection
mysql -u root -p --ssl-mode=REQUIRED
STATUS;
# Look for: SSL: Cipher in use

Firewall Configuration

Bash

# Ubuntu/Debian (UFW)
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw enable

# RHEL/CentOS (firewalld)
sudo firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  port protocol="tcp" port="3306" accept'
sudo firewall-cmd --reload

# Verify listening ports
sudo ss -tulpn | grep 3306

Password Security Policies

Bash

# Install validate_password plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

# Configure password policy
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;
SET GLOBAL validate_password.policy = STRONG;

# Show password policy settings
SHOW VARIABLES LIKE 'validate_password%';

Audit Logging

Bash

# Enable general query log (use carefully in production)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

# Install audit plugin (Enterprise/Percona)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

# Configure audit in my.cnf
[mysqld]
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON
audit_log_policy = ALL

# Review audit logs
tail -f /var/log/mysql/audit.log

Security Hardening Script

Bash

#!/bin/bash
# MySQL Security Audit Script

echo "=== MySQL Security Audit ==="
echo

# Check for users with empty passwords
echo "Users with empty passwords:"
mysql -u root -p -e "
SELECT user, host FROM mysql.user 
WHERE authentication_string = '' OR authentication_string IS NULL;
"

# Check for users with old password format
echo
echo "Users with old password format:"
mysql -u root -p -e "
SELECT user, host FROM mysql.user 
WHERE LENGTH(authentication_string) < 40;
"

# Check for wildcardhost access
echo
echo "Users with wildcard host access:"
mysql -u root -p -e "
SELECT user, host FROM mysql.user WHERE host = '%';
"

# Check SSL requirements
echo
echo "Users without SSL requirement:"
mysql -u root -p -e "
SELECT user, host, ssl_type FROM mysql.user 
WHERE ssl_type = '' OR ssl_type IS NULL;
"

# Check file permissions
echo
echo "MySQL data directory permissions:"
ls -la /var/lib/mysql/ | head -5

echo
echo "=== Audit Complete ==="

Implement comprehensive security measures as outlined in our Linux Security Essentials guide.

How to Monitor MySQL Server Health?

Continuous monitoring enables proactive MySQL administration Linux management. Subsequently, tracking key performance indicators prevents service degradation and identifies optimization opportunities.

Real-Time Monitoring Commands

Bash

# Show current queries
SHOW PROCESSLIST;

# Show full query text
SHOW FULL PROCESSLIST;

# Kill specific query
KILL QUERY 12345;
KILL CONNECTION 12345;

# Show server variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';

# Show server status
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';

# Show InnoDB status
SHOW ENGINE INNODB STATUS\G

# Check table locks
SHOW OPEN TABLES WHERE In_use > 0;

Key Metrics to Monitor

Bash

# Connection metrics
mysql -u root -p -e "
SELECT 
    variable_value AS current_connections 
FROM performance_schema.global_status 
WHERE variable_name = 'Threads_connected';

SELECT 
    variable_value AS max_used_connections 
FROM performance_schema.global_status 
WHERE variable_name = 'Max_used_connections';
"

# Uptime and queries per second
mysql -u root -p -e "
SELECT 
    CONCAT(ROUND(uptime/86400), ' days, ', 
           MOD(ROUND(uptime/3600), 24), ' hours') AS uptime,
    ROUND(questions/uptime, 2) AS queries_per_second
FROM 
    (SELECT variable_value AS uptime 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Uptime') up,
    (SELECT variable_value AS questions 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Questions') q;
"

# InnoDB buffer pool usage
mysql -u root -p -e "
SELECT 
    ROUND(buffer_pool_pages_data * 16384 / 1024 / 1024 / 1024, 2) AS data_gb,
    ROUND(buffer_pool_pages_free * 16384 / 1024 / 1024 / 1024, 2) AS free_gb,
    ROUND(buffer_pool_pages_total * 16384 / 1024 / 1024 / 1024, 2) AS total_gb
FROM 
    (SELECT variable_value AS buffer_pool_pages_data 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_pages_data') data,
    (SELECT variable_value AS buffer_pool_pages_free 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_pages_free') free,
    (SELECT variable_value AS buffer_pool_pages_total 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_pages_total') total;
"

Monitoring Script with Alerts

Bash

#!/bin/bash
# MySQL Health Monitoring Script

MYSQL_USER="monitoring_user"
MYSQL_PASS="MonitorP@ss"
ALERT_EMAIL="dba@company.com"
WARNING_THRESHOLD=80
CRITICAL_THRESHOLD=90

# Get connection usage percentage
MAX_CONN=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -s -N -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}')
CURR_CONN=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -s -N -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
CONN_PERCENT=$(( (CURR_CONN * 100) / MAX_CONN ))

echo "=== MySQL Health Check $(date) ==="
echo "Connections: $CURR_CONN / $MAX_CONN ($CONN_PERCENT%)"

if [ $CONN_PERCENT -gt $CRITICAL_THRESHOLD ]; then
    echo "CRITICAL: Connection usage above ${CRITICAL_THRESHOLD}%" | \
    mail -s "MySQL Critical Alert" $ALERT_EMAIL
elif [ $CONN_PERCENT -gt $WARNING_THRESHOLD ]; then
    echo "WARNING: Connection usage above ${WARNING_THRESHOLD}%" | \
    mail -s "MySQL Warning Alert" $ALERT_EMAIL
fi

# Check for long-running queries
LONG_QUERIES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -s -N -e "
SELECT COUNT(*) FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 60;")

echo "Long running queries (>60s): $LONG_QUERIES"

if [ $LONG_QUERIES -gt 0 ]; then
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e "
    SELECT id, user, host, db, command, time, state, 
           LEFT(info, 100) AS query 
    FROM information_schema.processlist 
    WHERE command != 'Sleep' AND time > 60;" | \
    mail -s "MySQL Long Running Queries" $ALERT_EMAIL
fi

# Check replication lag (if slave)
IS_SLAVE=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -s -N -e "SHOW SLAVE STATUS\G" | wc -l)

if [ $IS_SLAVE -gt 0 ]; then
    SECONDS_BEHIND=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -s -N -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
    echo "Replication lag: $SECONDS_BEHIND seconds"
    
    if [ "$SECONDS_BEHIND" != "NULL" ] && [ $SECONDS_BEHIND -gt 300 ]; then
        echo "WARNING: Replication lag exceeds 5 minutes" | \
        mail -s "MySQL Replication Lag" $ALERT_EMAIL
    fi
fi

echo "=== Health Check Complete ==="

Integration with Monitoring Tools

Bash

# Install MySQL exporter for Prometheus
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.14.0.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.14.0.linux-amd64/mysqld_exporter /usr/local/bin/

# Create exporter user
mysql -u root -p -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'ExporterP@ss' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
"

# Create exporter configuration
cat > /etc/.mysqld_exporter.cnf << EOF
[client]
user=exporter
password=ExporterP@ss
EOF

chmod 600 /etc/.mysqld_exporter.cnf

# Create systemd service
sudo tee /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
Type=simple
User=prometheus
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/.mysqld_exporter.cnf \
  --collect.global_status \
  --collect.info_schema.innodb_metrics \
  --collect.auto_increment.columns \
  --collect.binlog_size \
  --collect.perf_schema.tableiowaits \
  --collect.perf_schema.indexiowaits \
  --collect.perf_schema.tablelocks \
  --web.listen-address=:9104

[Install]
WantedBy=multi-user.target
EOF

# Enable and start exporter
sudo systemctl daemon-reload
sudo systemctl enable mysqld_exporter
sudo systemctl start mysqld_exporter

Explore advanced monitoring techniques in our Setting up Prometheus and Grafana guide.

FAQ

How do I reset MySQL root password if forgotten?

Stop MySQL service, start in safe mode without privilege checking, and reset the password:

Bash

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootP@ss';
exit
sudo systemctl restart mysql

What is the difference between MySQL and MariaDB?

MariaDB is a community-developed fork of MySQL with enhanced performance features, additional storage engines, and better open-source licensing. Both systems share compatible syntax and tools for MySQL administration Linux environments.

How do I check MySQL database size?

Bash

SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables 
GROUP BY table_schema
ORDER BY size_mb DESC;

Can I run multiple MySQL instances on one server?

Yes, configure separate data directories, port numbers, and socket files in distinct configuration files for each instance.

How do I migrate MySQL to another server?

Use mysqldump for logical migration or physical file copy with Percona XtraBackup. Ensure both servers run compatible MySQL versions and configurations.

What causes "Too many connections" error?

This occurs when current connections exceed max_connections setting. Increase the parameter in my.cnf or optimize application connection pooling.

How do I enable MySQL slow query log?

Bash

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Is MySQL replication synchronous or asynchronous?

MySQL replication is asynchronous by default. Semi-synchronous replication is available as a plugin for improved data consistency.

Troubleshooting Common Issues

Problem: MySQL Service Fails to Start

Symptoms:

Bash

sudo systemctl start mysql
Job for mysql.service failed because the control process exited with error code.

Diagnostic Steps:

Bash

# Check service status
sudo systemctl status mysql -l

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

# Check disk space
df -h /var/lib/mysql

# Verify data directory permissions
ls -la /var/lib/mysql/

# Test configuration file
mysqld --validate-config

Solution:

Bash

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

# Remove problematic InnoDB files (last resort)
sudo systemctl stop mysql
sudo mv /var/lib/mysql/ib_logfile* /tmp/
sudo systemctl start mysql

Problem: Replication Broken or Lagging

Symptoms:

Bash

mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Error: Error 'Duplicate entry...'

Diagnostic Steps:

Bash

# Check slave status
SHOW SLAVE STATUS\G

# Review replication error
SELECT * FROM performance_schema.replication_connection_status;

# Check binary log position
SHOW MASTER STATUS;

Solution:

Bash

# Skip problematic query (use cautiously)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

# Rebuild slave from master backup
# 1. Stop slave
STOP SLAVE;
RESET SLAVE;

# 2. Restore master backup
mysql -u root -p < master_backup.sql

# 3. Reconfigure replication
CHANGE MASTER TO ...;
START SLAVE;

Problem: High CPU Usage from MySQL

Diagnostic Steps:

Bash

# Identify resource-intensive queries
SELECT * FROM information_schema.processlist 
WHERE time > 10 AND command != 'Sleep';

# Check slow query log
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Analyze query execution
EXPLAIN SELECT * FROM problematic_table WHERE conditions;

Solution:

Bash

# Add missing indexes
CREATE INDEX idx_column ON table_name(column);

# Optimize queries
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;

# Adjust configuration
[mysqld]
innodb_buffer_pool_size = 2G
max_connections = 100

Problem: Table Corruption Detected

Symptoms:

Bash

ERROR 145 (HY000): Table './database/table' is marked as crashed

Diagnostic Steps:

Bash

# Check table status
CHECK TABLE table_name;

# Check MyISAM tables
myisamchk /var/lib/mysql/database/table.MYI

# Check InnoDB tables
mysqlcheck -u root -p --check database table_name

Solution:

Bash

# Repair MyISAM table
REPAIR TABLE table_name;

# Or use myisamchk
sudo systemctl stop mysql
myisamchk --recover /var/lib/mysql/database/table.MYI
sudo systemctl start mysql

# Repair InnoDB table
mysqlcheck -u root -p --repair database table_name

# Rebuild table (last resort)
ALTER TABLE table_name ENGINE=InnoDB;

Problem: Cannot Connect Remotely

Diagnostic Steps:

Bash

# Test connection
mysql -h remote_server -u username -p

# Check bind address
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

# Check firewall
sudo ufw status
sudo iptables -L -n | grep 3306

# Verify user host access
SELECT user, host FROM mysql.user WHERE user = 'username';

Solution:

Bash

# Allow remote connections
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Comment out or change:
# bind-address = 0.0.0.0

# Grant remote access
GRANT ALL PRIVILEGES ON database.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

# Configure firewall
sudo ufw allow from 192.168.1.0/24 to any port 3306

# Restart MySQL
sudo systemctl restart mysql

Review comprehensive troubleshooting methodologies in our Linux Performance Troubleshooting article.

Additional Resources

Next in Series: Continue your database administration journey with PostgreSQL Setup and Optimization (Post #57) for advanced database alternatives.

Previous Article: Master web server load balancing in Load Balancing with HAProxy (Post #55).

Last Updated: October 24, 2025 | Reading Time: 28 minutes | Difficulty: Intermediate to Advanced

Knowledge Overview

Prerequisites

Time Investment

Guide Content

What is MySQL Administration on Linux?

Table of Contents

How to Install MySQL/MariaDB on Linux?

Installation on Ubuntu/Debian

Installation on RHEL/CentOS/Rocky Linux

Post-Installation Configuration

What is MySQL User Management and Privilege Control?

Creating Database Users

Granting Privileges

Viewing and Revoking Privileges

Password Management

How to Implement MySQL Backup Strategies?

Logical Backups with mysqldump

Automated Backup Script

Physical Backups with Percona XtraBackup

Point-in-Time Recovery

Backup Verification Script

What is MySQL Replication Configuration?

Master Server Configuration

Create Replication User

Slave Server Configuration

Configure Slave Replication

Replication Monitoring Script

Semi-Synchronous Replication

How to Optimize MySQL Performance?

Configuration Tuning (/etc/mysql/my.cnf)

Query Performance Analysis

Index Optimization

Performance Monitoring Queries

Performance Tuning Script

What are MySQL Security Best Practices?

Secure Installation Checklist

Network Security Configuration

Firewall Configuration

Password Security Policies

Audit Logging

Security Hardening Script

How to Monitor MySQL Server Health?

Real-Time Monitoring Commands

Key Metrics to Monitor

Monitoring Script with Alerts

Integration with Monitoring Tools

FAQ

How do I reset MySQL root password if forgotten?

What is the difference between MySQL and MariaDB?

How do I check MySQL database size?

Can I run multiple MySQL instances on one server?

How do I migrate MySQL to another server?

What causes "Too many connections" error?

How do I enable MySQL slow query log?

Is MySQL replication synchronous or asynchronous?

Troubleshooting Common Issues

Problem: MySQL Service Fails to Start

Problem: Replication Broken or Lagging

Problem: High CPU Usage from MySQL

Problem: Table Corruption Detected

Problem: Cannot Connect Remotely

Additional Resources

Official Documentation

Tools and Utilities

Related LinuxTips.pro Articles

Community Resources

Professional Certifications