Troubleshooting a database server in Linux

Posted by

Troubleshooting a database server in Linux involves a systematic approach to identify and resolve issues related to configuration, performance, connectivity, security, and more. Below are steps and commands to help you troubleshoot common database server issues:

1. Check Database Server Status

Using systemctl

  • Check if the database server is running:
sudo systemctl status mysql    # For MySQL/MariaDB
sudo systemctl status postgresql  # For PostgreSQL

Using ps

  • Verify the server process
ps aux | grep mysql    # For MySQL/MariaDB
ps aux | grep postgres  # For PostgreSQL

2. Check Database Logs

MySQL/MariaDB Logs

  • Error log
sudo less /var/log/mysql/error.log
sudo less /var/log/mysqld.log   # Alternative log file

3. Check Configuration Files

MySQL/MariaDB Configuration

  • Main configuration file:
sudo less /etc/mysql/my.cnf    # Debian/Ubuntu
sudo less /etc/my.cnf          # CentOS/RHEL

4. Check Database Connectivity

Using mysql Command-Line Client

  • Test connection to MySQL/MariaDB:
mysql -u root -p

Using telnet or nc

  • Check if the database port is open
telnet localhost 3306    # For MySQL/MariaDB
telnet localhost 5432    # For PostgreSQL

# Or using nc (netcat)
nc -zv localhost 3306    # For MySQL/MariaDB
nc -zv localhost 5432    # For PostgreSQL

5. Check Resource Usage

Using top and htop

  • Monitor resource usage

Using free

  • Check memory usage

Using df

  • Check disk space usage

6. Check Network Configuration

Using ping

  • Test network connectivity:

Using netstat or ss

  • Check listening ports and network connections

7. Check Permissions and Access

Using ls and chmod

  • Verify file permissions:
ls -l /path/to/database/file
sudo chmod 755 /path/to/database/file

Using chown

  • Change file ownership
sudo chown mysql:mysql /path/to/database/file    # For MySQL/MariaDB
sudo chown postgres:postgres /path/to/database/file    # For PostgreSQL

8. Check and Optimize Performance


  • Analyze query performance

9. Common Errors and Troubleshooting Steps

Configuration Errors

  • Error Message
mysqld: unrecognized option '--innodb-foo'

Cause: Invalid configuration option.Troubleshooting: Check and correct configuration options in my.cnf.

Connection Errors

  • Error Message:
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Cause: Incorrect username/password.Troubleshooting: Verify credentials and user privileges.

Error Message:

psql: FATAL: role "username" does not exist

Cause: User does not exist in the database.Troubleshooting: Create the user or correct the username

Resource Limitation Errors

  • Error Message:
InnoDB: Error: log file ./ib_logfile0 is of different size

Cause: Incorrect configuration after resizing InnoDB log files.Troubleshooting: Adjust the innodb_log_file_size parameter and restart the server.

Disk Space Errors

  • Error Message:
ERROR 28 (HY000): Out of disk space

Cause: Disk is full.Troubleshooting: Free up space or increase disk capacity.

10. Backups and Recovery

MySQL/MariaDB Backups

  • Using mysqldump
mysqldump -u root -p database_name > backup.sql

Summary of Common Errors

  1. Configuration Errors:
    • Invalid configuration option.
    • Syntax error in configuration file.
  2. Connection Errors:
    • Access denied.
    • Role does not exist.
    • Connection refused.
    • Network unreachable.
  3. Resource Limitation Errors:
    • Out of memory.
    • Disk space full.
  4. Performance Issues:
    • Slow queries.
    • High CPU usage.
  5. Disk I/O Errors:
    • Disk I/O error.
  6. Backup and Recovery Errors:
    • Backup failure.
    • Restore failure.
  7. Permissions and Access Errors:
    • Permission denied.
    • File permission error.
  8. Security Issues:
    • SSL/TLS errors.
  9. Logging Issues:
    • Log rotation issues.
  10. Scheduled Task Errors:
  • Cron job failures.
  1. Database-Specific Errors:
  • MySQL/MariaDB specific errors (duplicate entry, table doesn’t exist).
  • PostgreSQL specific errors (division by zero, lock timeout).
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x