Real Linux Troubleshooting Scenarios: MySQL/MariaDB Server Configuration and Management

Scenario 274: MySQL/MariaDB Service Fails to Start

Issue: MySQL/MariaDB service fails to start.

Solution:

Check the MySQL/MariaDB error log for details:

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

Verify if there are errors in the MySQL/MariaDB configuration file:

cat /etc/my.cnf    # or cat /etc/mysql/my.cnf

Check for port conflicts:

netstat -tulnp | grep 3306

Ensure there is enough disk space.

Scenario 275: Unable to Connect to MySQL/MariaDB Server

Issue: Applications or users cannot connect to the MySQL/MariaDB server.

Solution:

Confirm that the MySQL/MariaDB service is running:

systemctl status mariadb    # or systemctl status mysqld

Use <span>telnet</span> or <span>nc</span> to test network connectivity.

Verify MySQL/MariaDB user permissions and host restrictions.

Check firewall rules to ensure the MySQL/MariaDB port (default: 3306) is open.

Scenario 276: Slow Query Performance

Issue: Database queries are running slower than expected.

Solution:

Use the MySQL/MariaDB slow query log to identify slow queries.

Enable the slow query log in the configuration file (<span>slow_query_log = 1</span>).

Set the <span>long_query_time</span> parameter to an appropriate value.

Optimize slow queries, considering indexes, query structure, and table design.

Use <span>top</span> and <span>htop</span> to monitor server resources to identify bottlenecks.

Scenario 277: MySQL/MariaDB Authentication Issues

Issue: Users cannot authenticate to the MySQL/MariaDB server.

Solution:

Check MySQL/MariaDB user accounts and passwords.

Test authentication:

mysql -u <username> -p

Verify if the <span>mysql_native_password</span> plugin is being used for authentication.

If necessary, reset the password:

ALTER USER '<username>'@'<host>' IDENTIFIED BY 'new_password';

Scenario 278: Database Corruption

Issue: Tables or databases are marked as corrupted.

Solution:

Run MySQL/MariaDB’s <span>CHECK TABLE</span> and <span>REPAIR TABLE</span> commands on the affected tables.

CHECK TABLE <table_name>;REPAIR TABLE <table_name>;

If the corruption is severe, restore the database from a backup.

Investigate server logs for potential hardware issues.

Scenario 279: Outdated Database Software Version

Issue: The running MySQL/MariaDB version is outdated.

Solution:

Check the current MySQL/MariaDB version:

mysql --version

Use a package manager (<span>yum</span> or <span>dnf</span>) to update MySQL/MariaDB:

sudo yum update mariadb-server    # or sudo dnf update mysql-server

Verify that the upgrade was successful and restart the MySQL/MariaDB service.

Scenario 280: InnoDB or MyISAM Storage Engine Issues

Issue: Issues specific to the InnoDB or MyISAM storage engines.

Solution:

Check the storage engine status:

SHOW ENGINES;   # in MySQL/MariaDB shell

If using InnoDB, monitor InnoDB status:

SHOW ENGINE INNODB STATUS;   # in MySQL/MariaDB 

shell

If necessary, convert tables to another storage engine:

ALTER TABLE <table_name> ENGINE = InnoDB;

Scenario 281: Insufficient Disk Space Due to Binary Logs

Issue: Disk is full due to excessive growth of MySQL/MariaDB binary logs.

Solution:

Check the size of the binary logs:

SHOW BINARY LOGS;

Clean up old binary logs:

PURGE BINARY LOGS BEFORE 'YYYY-MM-DD';

Adjust MySQL/MariaDB configuration to limit the size or retention time of binary logs.

Scenario 282: Replication Issues

Issue: Replication between MySQL/MariaDB servers is not functioning correctly.

Solution:

Check replication status:

SHOW SLAVE STATUS;   # on the slave server

Verify the binary log position on the master server and the relay log position on the slave server.

If necessary, restart the MySQL/MariaDB replication process.

Scenario 283: Unable to Access MySQL/MariaDB Data Directory

Issue: MySQL/MariaDB cannot access or write to the data directory.

Solution:

Check the file and directory permissions of the MySQL/MariaDB data directory:

ls -ld /var/lib/mysql

Ensure the MySQL/MariaDB user has the necessary permissions.

If necessary, use <span>chown</span> and <span>chmod</span> to fix file ownership and permissions.