Databases & NoSQL

Ultimate Guide to Checking, Repairing, and Optimizing MySQL/MariaDB Databases for Peak Performance

Published · Updated 15 min read
Knowledge base article
Contents (47 sections)

MySQL/MariaDB databases require regular maintenance to ensure optimal performance, prevent corruption, and reduce downtime. This comprehensive guide covers the best practices for checking, repairing, and optimizing databases using MySQL/MariaDB.

Table of Contents

  1. Why Database Optimization is Important

  2. How to Check MySQL/MariaDB Databases for Errors

  3. How to Repair Corrupt MySQL/MariaDB Tables

  4. Optimizing Databases & Tables for Better Performance

  5. Automating Database Maintenance with Scripts & Cron Jobs

  6. Best MySQL Configurations for Optimization

  7. Troubleshooting & Recovery Strategies

  8. Conclusion & Best Practices

1 Why Database Optimization is Important

Keeping your MySQL/MariaDB databases optimized is critical for:

Faster Queries - Reduces response time, making applications more responsive
Better Storage Management - Reclaims unused space, reducing disk usage
Reduced Downtime - Prevents database corruption and minimizes recovery time
Improved User Experience - Ensures faster websites & applications, enhancing customer satisfaction

A well-optimized database ensures higher performance, better scalability, and minimal issues, making it a crucial aspect of system administration. Let's explore best practices to keep your MySQL/MariaDB databases running efficiently!

2 How to Check MySQL/MariaDB Databases for Errors

Regularly checking your MySQL/MariaDB databases for errors helps prevent corruption, data loss, and performance degradation. You can check for errors manually or automate the process using mysqlcheck.

A. Using SQL Commands

You can manually verify tables and databases for errors within MySQL/MariaDB.

Check a specific table:

code
CHECK TABLE database_name.table_name;

Check all tables within a database:

code
CHECK TABLE database_name.*;

These commands return OK if the table is fine or provide an error message if an issue is detected.

B. Using mysqlcheck Command-Line Tool

If you prefer a command-line method to check your databases, use mysqlcheck, which scans and reports errors.

Check all databases for issues:

code
mysqlcheck -u root -p --all-databases

Check a specific database:

code
mysqlcheck -u root -p database_name

Tip: Look for output messages like OK (healthy) or Table is corrupted (requires repair).

C. Automating Database Checks with Cron Jobs

To regularly check for errors without manual intervention, schedule a cron job.

Create a cron job to run checks daily at 3 AM:

code
0 3 * * * mysqlcheck -u root -p'yourpassword' --all-databases >> /var/log/mysql_check.log 2>&1

This ensures continuous monitoring and early detection of database issues, reducing downtime risks.

3 How to Repair Corrupt MySQL/MariaDB Tables

If corruption is detected in a MySQL/MariaDB database, you can repair tables using different methods depending on the storage engine.

A. Using SQL Commands (Manual Repair)

For quick repairs, use SQL commands directly inside MySQL/MariaDB.

Repair a specific table:

code
REPAIR TABLE database_name.table_name;

Force repair for MyISAM tables that fail to repair normally:

code
REPAIR TABLE database_name.table_name USE_FRM;

Note: The USE_FRM option is only applicable to MyISAM tables and forces table structure restoration.

B. Using mysqlcheck for Automatic Repair

If you need to repair multiple databases, use the mysqlcheck command.

Repair all databases at once:

code
mysqlcheck -u root -p --repair --all-databases

Repair a specific database:

code
mysqlcheck -u root -p --repair database_name

This method is safe and can be used without taking the database offline.

C. Restart MySQL with Recovery Mode (For InnoDB Corruption)

If corruption occurs in InnoDB tables, MySQL may fail to start, crash during access, or become unresponsive. In such cases, you can use forced recovery mode to attempt to bring MySQL online and recover data.

Step 1: Edit the MySQL Configuration File (my.cnf)

To enable forced recovery mode, edit the MySQL configuration file:

code
sudo nano /etc/mysql/my.cnf

Add or modify the following under [mysqld]:

code
[mysqld]
innodb_force_recovery = 1

Understanding innodb_force_recovery Levels (1-6)

The innodb_force_recovery setting ranges from 1 to 6, each level gradually increasing the recovery intensity.

Recovery Level Purpose
1-2 Safe for minor issues. Prevents background purge and insert buffer merge.
3-4 Use if the database is unresponsive. Stops most background operations. Allows data exports.
5-6 Last resort. Can cause data loss. Use only if necessary to dump tables before full rebuild.

Warning: Higher levels (5-6) should only be used as a last resort, as they can result in irreversible data loss.

Step 2: Restart MySQL in Recovery Mode

After modifying my.cnf, restart MySQL:

code
systemctl restart mysql

Check the status to confirm if MySQL is running:

code
systemctl status mysql

If MySQL does not start, incrementally increase the recovery level (innodb_force_recovery = 2, then 3, etc.) and restart MySQL again.

Monitor the MySQL error log for issues:

code
tail -f /var/log/mysql/error.log

Step 3: Backup Data Immediately

If MySQL starts in recovery mode, backup your databases before proceeding with repairs:

code
mysqldump -u root -p --all-databases > /backup/mysql_backup.sql

If the database is too large, use Percona XtraBackup or mysqlpump to perform a non-locking backup.

Step 4: Attempt to Repair InnoDB Tables

Once MySQL is running, try repairing corrupted tables:

code
ALTER TABLE database_name.table_name FORCE;

If corruption persists, delete and recreate InnoDB system files:

code
rm -rf /var/lib/mysql/ib_logfile*
rm -rf /var/lib/mysql/ibdata1
rm -rf /var/lib/mysql/ibtmp1
rm -rf /var/lib/mysql/undo_001
rm -rf /var/lib/mysql/undo_002
systemctl restart mysql

Explanation of InnoDB System Files:

  • ib_logfile*: Redo log files, needed for crash recovery.

  • ibdata1: Stores metadata, undo logs, and shared tablespace.

  • ibtmp1: Temporary tablespace for temporary tables.

  • undo_001, undo_002: Undo logs for transactions.

Warning: Deleting ibdata1 will remove all InnoDB tables unless backups are available.

Step 5: Restore Data & Remove Recovery Mode

Once InnoDB corruption is resolved, restore data from backups if necessary and disable forced recovery mode.

1 Remove the Recovery Setting

Edit my.cnf and remove or comment out the innodb_force_recovery line:

code
[mysqld]
# Remove or comment out this line
# innodb_force_recovery = 1

2 Restart MySQL Normally

code
systemctl restart mysql

3 Verify MySQL is Running Properly

Check if MySQL is operating normally:

code
systemctl status mysql
mysql -u root -p -e "SHOW DATABASES;"

4 Optimize & Rebuild Tables

Run optimization to rebuild tables and indexes:

code
OPTIMIZE TABLE database_name.table_name;
ANALYZE TABLE database_name.table_name;

Best Practices

Start with innodb_force_recovery = 1 and increase only if needed.
Always take a full backup before using levels 3-6.
Monitor logs for warnings and errors:

code
tail -f /var/log/mysql/error.log

If recovery fails at level 6, a complete database restore is required.

By following these steps, you can safely recover from InnoDB corruption while minimizing data loss and downtime.

Best Practices for Preventing Corruption

Use InnoDB instead of MyISAM for better crash recovery. Regularly backup databases with mysqldump or automated scripts. Monitor logs (/var/log/mysql.log) for early warnings of corruption. Enable automated MySQL error checking with a scheduled cron job.

By following these methods, you can quickly repair corrupt MySQL/MariaDB tables and ensure your database remains stable and efficient!

</div>

4 Optimizing Databases & Tables for Better Performance

Regularly optimizing MySQL/MariaDB databases improves query execution speed, reduces disk space usage, and enhances overall system performance. The following methods help keep your databases in peak condition.

A. Optimize Tables to Reclaim Space

Over time, tables can become fragmented due to frequent updates, deletions, or insertions. Use the following commands to optimize tables:

Optimize a specific table:

code
OPTIMIZE TABLE database_name.table_name;

Optimize all databases at once:

code
mysqlcheck -u root -p --optimize --all-databases

This command restructures the table and reclaims unused space, reducing storage overhead.

B. Use Indexes Efficiently

Indexes help speed up data retrieval but can slow down insert, update, and delete operations if used inefficiently. Proper indexing ensures faster query performance.

Create an index on a column for faster searches:

code
CREATE INDEX idx_column ON database_name.table_name (column_name);

Remove an unused or redundant index:

code
DROP INDEX idx_column ON database_name.table_name;

Best Practices for Indexing: Index columns used in WHERE, ORDER BY, and JOIN clauses.
Avoid excessive indexing, as it consumes memory and slows down writes.
Use composite indexes for queries involving multiple columns.

C. Query Optimization Techniques

Inefficient queries can slow down databases and degrade performance. Use EXPLAIN to analyze how MySQL executes a query and find ways to improve it.

Analyze a query execution plan:

code
EXPLAIN SELECT * FROM orders WHERE user_id = 5;

This command returns execution details like indexes used, table scan type, and estimated query cost, helping identify performance bottlenecks.

Optimize queries for efficiency:

  • Avoid using SELECT *, instead select only required columns:

code
SELECT id, name FROM users WHERE status = 'active';

  • Use LIMIT for large queries to reduce load:

code
SELECT * FROM logs ORDER BY created_at DESC LIMIT 1000;

  • Rewrite inefficient subqueries using JOINs:

code
SELECT orders.id, users.name FROM orders 
JOIN users ON orders.user_id = users.id;

5 Automating Database Maintenance with Scripts & Cron Jobs

Automating MySQL/MariaDB maintenance ensures database integrity, reduced downtime, and improved performance without manual intervention. You can automate checking, repairing, and optimizing databases by using scripts and cron jobs.

A. Create a Database Check & Repair Script

The following Bash script automatically checks and repairs databases. Save it as /usr/local/bin/mysql_maintenance.sh:

code
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
LOGFILE="/var/log/mysql_maintenance.log"

echo "Starting MySQL database maintenance: $(date)" >> $LOGFILE

databases=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

for db in $databases; do
 echo "Checking database: $db" >> $LOGFILE
 mysqlcheck -u$MYSQL_USER -p$MYSQL_PASS --check --databases $db >> $LOGFILE 2>&1
 
 echo "Repairing database: $db" >> $LOGFILE
 mysqlcheck -u$MYSQL_USER -p$MYSQL_PASS --repair --databases $db >> $LOGFILE 2>&1

done

echo "MySQL maintenance completed: $(date)" >> $LOGFILE

Steps to Use the Script:

  1. Save the script as /usr/local/bin/mysql_maintenance.sh

  2. Grant execution permissions:

code
chmod +x /usr/local/bin/mysql_maintenance.sh

  • Run manually to test:

    • code
    /usr/local/bin/mysql_maintenance.sh

    B. Schedule with Cron Job

    To run the script automatically every day at 3 AM, add the following entry to the crontab:

    code
    crontab -e

    Then, add this line at the end:

    code
    0 3 * * * /usr/local/bin/mysql_maintenance.sh >> /var/log/mysql_maintenance.log 2>&1

    This ensures that database maintenance runs daily without manual intervention, keeping your MySQL/MariaDB databases healthy and optimized.

    </div>

    6 Best MySQL Configurations for Optimization

    Fine-tuning MySQL/MariaDB configurations ensures optimal performance, better resource utilization, and enhanced stability. Below are key settings to optimize in the MySQL configuration file (my.cnf).

    A. Optimize MySQL Configuration in /etc/mysql/my.cnf

    Step 1: Edit the MySQL Configuration File

    code
    sudo nano /etc/mysql/my.cnf

    Step 2: Modify or Add the Following Settings Under the [mysqld] Section

    code
    [mysqld]

# Enable Query Cache (Not available in MySQL 8+)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M # Caches small queries efficiently

# Optimize InnoDB Performance
innodb_buffer_pool_size = 2G # Adjust based on available RAM (50-70% of total memory)
innodb_log_file_size = 512M # Increases transaction log efficiency
innodb_flush_log_at_trx_commit = 2 # Improves write performance
innodb_flush_method = O_DIRECT # Reduces disk I/O overhead
innodb_io_capacity = 2000 # Improves disk write performance
innodb_thread_concurrency = 16 # Optimizes InnoDB for multi-core CPUs
innodb_read_io_threads = 8 # Improves read performance
innodb_write_io_threads = 8 # Enhances write operations
innodb_log_buffer_size = 16M # Increases transaction log buffer

# Connection Handling
max_connections = 500 # Adjust based on expected traffic
thread_cache_size = 64 # Reuse threads to reduce overhead
wait_timeout = 28800 # Time before closing idle connections
interactive_timeout = 28800 # Timeout for interactive connections
max_allowed_packet = 64M # Prevents large packet errors
back_log = 80 # Handles incoming connection requests efficiently

# Table Cache Optimization
table_open_cache = 4000 # Helps with frequently accessed tables
open_files_limit = 65535 # Adjust based on system file limits
table_definition_cache = 2000 # Caches table definitions for faster access

# Temporary Tables & Sorting
tmp_table_size = 64M # Increases temporary table size
max_heap_table_size = 64M # Enhances in-memory table performance
sort_buffer_size = 4M # Improves ORDER BY performance
read_rnd_buffer_size = 4M # Optimizes random read operations
join_buffer_size = 2M # Speeds up complex joins

# General Performance Tweaks
key_buffer_size = 256M # Optimizes MyISAM index cache (useful for legacy systems)
query_prealloc_size = 64K # Improves memory management for queries
query_alloc_block_size = 128K # Reduces memory fragmentation
binlog_cache_size = 1M # Improves binary log storage efficiency
log_bin = /var/log/mysql/mysql-bin.log # Enables binary logging for replication
expire_logs_days = 7 # Keeps binary logs for 7 days
slow_query_log = 1 # Enable slow query logging
long_query_time = 2 # Logs queries taking longer than 2 seconds
log_error = /var/log/mysql/error.log # Logs MySQL errors for troubleshooting

# Replication Settings (If Using Master-Slave Replication)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
sync_binlog = 1

# Performance Schema & Monitoring
performance_schema = ON # Enables performance monitoring
innodb_status_file = 1 # Logs InnoDB performance metrics

    B. Restart MySQL for Changes to Take Effect

    Once you've made changes, restart MySQL to apply the optimizations:

    code
    systemctl restart mysql

    Verify Configurations: Check if MySQL is running smoothly after changes:

    code
    systemctl status mysql

    Monitor MySQL Performance:

    code
    mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

    By applying these optimized configurations, you can enhance MySQL's performance, reduce query execution time, and ensure efficient resource management.

    7 Troubleshooting & Recovery Strategies

    Even with regular maintenance, issues can arise in MySQL/MariaDB. Here are common troubleshooting steps to identify and resolve performance problems, crashes, or connectivity issues.

    A. Identifying Slow Queries & Performance Bottlenecks

    Check Running Queries

    code
    SHOW PROCESSLIST;

    This command displays active database queries and helps detect slow or stuck operations.

    Kill a Problematic Query

    code
    KILL query_id;

    Replace query_id with the actual ID of the slow or stuck query.

    Enable the Slow Query Log (for diagnosing slow queries)

    code
    SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; # Logs queries taking longer than 2 seconds

    B. Fixing Table Corruption & Crashes

    Database corruption can lead to unexpected crashes, data inconsistency, and performance issues. Below are effective steps to check, repair, and recover corrupt MySQL/MariaDB tables.

    Check for Table Corruption

    If you suspect table corruption, use the CHECK TABLE command to verify its integrity.

    Check a specific table for corruption:

    code
    CHECK TABLE database_name.table_name;

    Check all tables in a database:

    code
    CHECK TABLE database_name.*;

    If MySQL reports issues like Table is marked as crashed and should be repaired, proceed with repairing the affected tables.

    Repair Corrupt Tables

    For MyISAM tables, use REPAIR TABLE to attempt a fix.

    Repair a specific table:

    code
    REPAIR TABLE database_name.table_name;

    Repair all tables in a database:

    code
    mysqlcheck -u root -p --repair database_name

    Repair all databases at once:

    code
    mysqlcheck -u root -p --repair --all-databases

    Note: REPAIR TABLE only works for MyISAM tables. For InnoDB corruption, follow the recovery steps below.

    Restart MySQL in Recovery Mode (For InnoDB Corruption)

    If InnoDB corruption prevents MySQL from starting, use forced recovery mode to access and fix the database.

    Step 1: Edit MySQL Configuration (/etc/mysql/my.cnf)

    Open the MySQL configuration file:

    code
    sudo nano /etc/mysql/my.cnf

    Add or modify the following under [mysqld]:

    code
    [mysqld]
innodb_force_recovery = 1

    Understanding Recovery Levels (1-6)

    The innodb_force_recovery option accepts values from 1 to 6. Each level increases the recovery intensity, but higher levels risk data loss.

    1-2: Safe for Minimal Recovery

    • Recommended first step.

    • Prevents background purge and insert buffer merge.

    • Allows MySQL to start while limiting InnoDB background processes.

    3-4: Use If Tables Are Unreadable

    • Stops most background operations, allowing data exports.

    • Useful if SHOW TABLES fails or queries hang indefinitely.

    • Tables can be dumped using mysqldump to save data before full restoration.

    5-6: Last Resort, Risk of Data Loss

    • Use only if necessary to dump tables before complete database rebuild.

    • Prevents almost all InnoDB operations, allowing data extraction before full reset.

    • Level 6 disables most InnoDB safety features, making it dangerous for production.

    Step 2: Restart MySQL in Recovery Mode

    After setting innodb_force_recovery, restart MySQL:

    code
    systemctl restart mysql

    Check the MySQL status:

    code
    systemctl status mysql

    If MySQL does not start, increase the recovery level (innodb_force_recovery = 2, then 3, etc.).

    Step 3: Backup Important Data

    Once MySQL starts, immediately backup your databases to avoid further data loss:

    code
    mysqldump -u root -p --all-databases > /backup/mysql_backup.sql

    If the database is too large, consider using Percona XtraBackup or mysqlpump.

    Step 4: Repair InnoDB Tables

    Try to recover InnoDB tables:

    code
    ALTER TABLE database_name.table_name FORCE;

    If corruption persists, rebuild InnoDB by deleting log files:

    code
    rm -rf /var/lib/mysql/ib_logfile*
rm -rf /var/lib/mysql/ibdata1
rm -rf /var/lib/mysql/ibtmp1
rm -rf /var/lib/mysql/undo_001
rm -rf /var/lib/mysql/undo_002
systemctl restart mysql

    Explanation of InnoDB Files:

    • ib_logfile*: InnoDB redo log files. Deleting these forces MySQL to recreate them.

    • ibdata1: Stores table metadata and undo logs. Deleting requires restoring from backup.

    • ibtmp1: Temporary tablespace file, usually safe to delete.

    • undo_001, undo_002: Undo logs for uncommitted transactions. Can be deleted if MySQL fails to start.

    Warning: Deleting ibdata1 results in the loss of InnoDB tables unless a backup is available.

    Step 5: Remove Recovery Mode & Restart MySQL

    Once fixed, remove innodb_force_recovery from my.cnf:

    code
    [mysqld]
# Remove or comment out this line
# innodb_force_recovery = 1

    Restart MySQL normally:

    code
    systemctl restart mysql

    Best Practices

    Always start with innodb_force_recovery = 1 and increase gradually if needed.
    Backup your data before applying higher recovery levels.
    Monitor logs for errors:

    code
    tail -f /var/log/mysql/error.log

    If recovery fails at level 6, consider a full database restoration from backup.

    By following these steps, you can recover corrupt MySQL/MariaDB tables and restore database functionality with minimal downtime.

    C. Resolving Connection Issues

    Check MySQL Service Status

    code
    systemctl status mysql

    Restart MySQL

    code
    systemctl restart mysql

    Check MySQL Logs for Errors

    code
    tail -f /var/log/mysql/error.log

    Fix 'Too Many Connections' Error Increase max_connections in /etc/mysql/my.cnf:

    code
    max_connections = 1000

    Restart MySQL:

    code
    systemctl restart mysql

    8 Conclusion & Best Practices

    To ensure smooth database operations, follow these best practices:

    Check Databases Regularly (mysqlcheck)

    code
    mysqlcheck -u root -p --all-databases

    Repair Tables if Needed (REPAIR TABLE)

    code
    REPAIR TABLE database_name.table_name;

    Optimize for Performance (OPTIMIZE TABLE)

    code
    OPTIMIZE TABLE database_name.table_name;

    Automate Maintenance with Cron Jobs

    code
    0 3 * * * /usr/local/bin/mysql_maintenance.sh >> /var/log/mysql_maintenance.log 2>&1

    Tune MySQL Settings for Better Speed (Modify my.cnf for optimal performance)

    code
    innodb_buffer_pool_size = 2G
query_cache_size = 64M
max_connections = 500

    By following this guide, your MySQL/MariaDB databases will stay optimized, corruption-free, and lightning-fast!

    </div>

    Was this article helpful?

    Your answer helps us decide what to improve next.

    Still need help? Open a support ticket and our team will reply.

    Related articles

    Prefer an app? Add this site to your home screen.Get the app
    Need Help?
    Live chatNew TicketTrack TicketKnowledge BaseContact UsWhatsApp