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

top
htop

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

Using EXPLAIN in SQL

• Analyze query performance

EXPLAIN SELECT * FROM table_name;

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.

11. Database-Specific Errors:

• MySQL/MariaDB specific errors (duplicate entry, table doesn’t exist).
• PostgreSQL specific errors (division by zero, lock timeout).

Mohammad Gufran Jahangir