Fabian Tech Tips
The MariaDB extended document offers comprehensive information on troubleshooting MariaDB within a Linux environment. This training guide helps you gain familiarity with the process.
Meanwhile, the shorter version is your go-to document for those 3 AM troubleshooting emergencies. Together, they ensure you're well-equipped, no matter the time or complexity of the issue.
MariaDB Triage Extended Document
The Triage document is the first point of call when troubleshooting MariaDB issues.
A related document called MariaDB extended is the same document with an in-depth explanation.
####### Mariadb online checks #########
Elavate to root
sudo su
To elevate to root using sudo su, follow these detailed steps:
Open Terminal: Launch your terminal application.
Enter Command: Type the following command and press Enter:
sudo su
Authenticate: You will be prompted to enter your user password. Type your password and press Enter. Note that the password will not be visible as you type.
Root Access: Once authenticated, your terminal prompt will change to indicate that you are now operating as the root user. It typically changes from $ to #.
Example:
user@hostname:~$ sudo su
[sudo] password for user:
root@hostname:/home/user#
Important: Use root access with caution, as it grants you full control over the system, including the ability to make critical changes.
####################################
Check Mariadb is running
ps –ef | grep mysqld
To check if MariaDB is running using the ps -ef | grep mysqld command, follow these steps:
Open Terminal: Launch your terminal application.
Enter Command: Type the following command and press Enter:
ps -ef | grep mysqld
Review Output: The command will display a list of processes related to mysqld, which is the MariaDB server daemon. Look for entries that indicate the MariaDB server is running.
Example Output:
user 1234 1 0 12:34 ? 00:00:01 /usr/sbin/mysqld
user 5678 1234 0 12:35 ? 00:00:00 /usr/sbin/mysqld --daemonize
user 9101 5678 0 12:36 pts/0 00:00:00 grep --color=auto mysqld
The first column shows the user running the process.
The second column is the process ID (PID).
The command /usr/sbin/mysqld indicates that the MariaDB server is running.
If you see entries like /usr/sbin/mysqld, it means MariaDB is active. If no such entries are found, MariaDB is not running.
####################################
Disk I/O status
iostat –x 5
To check the Disk I/O status using the iostat -x 5 command, follow these steps:
Open Terminal: Launch your terminal application.
Enter Command: Type the following command and press Enter:
iostat -x 5
Review Output: The command will display extended I/O statistics every 5 seconds. Key metrics to look for include:
Device: The name of the device.
rrqm/s: The number of read requests merged per second.
wrqm/s: The number of write requests merged per second.
r/s: The number of read requests per second.
w/s: The number of write requests per second.
rsec/s: The number of sectors read per second.
wsec/s: The number of sectors written per second.
avgrq-sz: The average size (in sectors) of the requests that were issued to the device.
avgqu-sz: The average queue length of the requests that were issued to the device.
await: The average time (in milliseconds) for I/O requests issued to the device to be served.
svctm: The average service time (in milliseconds) for I/O requests that were issued to the device.
%util: The percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device).
Example Output:
Linux 5.4.0-42-generic (hostname) 09/19/2024 x8664_ (4 CPU)
Device rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 1.00 0.00 1.00 0.00 8.00 8.00 0.00 0.00 0.00 0.00
sdb 0.00 2.00 1.00 2.00 8.00 16.00 8.00 0.01 0.50 0.50 0.50
%util close to 100% indicates the device is fully utilized.
await and svctm values help identify if there are delays in processing I/O requests.
This command helps monitor and diagnose disk performance issues effectively.
##################################
Verify Connection to Master
netstat –a
To verify network connections using the netstat -acommand, follow these steps:
Open Terminal: Launch your terminal application.
Enter Command: Type the following command and press Enter:
netstat -a
Review Output: The command will display all active connections and listening ports. Key columns to look for include:
Proto: The protocol used (TCP or UDP).
Recv-Q: The count of bytes not copied by the user program connected to this socket.
Send-Q: The count of bytes not acknowledged by the remote host.
Local Address: The address and port number of the local end of the socket.
Foreign Address: The address and port number of the remote end of the socket.
State: The state of the socket (e.g., LISTEN, ESTABLISHED, CLOSE_WAIT).
Example Output:
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 localhost:domain : LISTEN
tcp 0 0 :ssh :* LISTEN
tcp 0 0 localhost:ipp : LISTEN
tcp 0 0 :http :* LISTEN
tcp 0 0 192.168.1.2:ssh 192.168.1.3:53245 ESTABLISHED
udp 0 0 :bootpc :*
udp 0 0 :domain :*
LISTEN: Indicates the server is waiting for incoming connections.
ESTABLISHED: Indicates an active connection.
CLOSE_WAIT: Indicates the remote end has shut down, waiting for the socket to close.
This command helps monitor and troubleshoot network connections effectively.
##################################
Login to MariaDB
mysql
Login to MariaDB on Linux using MySQL
Open Terminal:
Press Ctrl + Alt + T to open the terminal.
Login Command:
Type the following command and press Enter:
mysql -u [username] -p
Replace [username] with your actual MariaDB username.
Enter Password:
You will be prompted to enter your password. Type it and press Enter. Note that the password will not be visible as you type.
Successful Login:
If the credentials are correct, you will see the MariaDB prompt:
MariaDB [(none)]>
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will log you into MariaDB using the MySQL client on a Linux system.
######################################
Check Replication status
SHOW SLAVE STATUS\G
Check Replication Status in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check Replication Status:
Once logged in, execute the following command:
SHOW SLAVE STATUS\G
Interpret the Output:
The command will display detailed information about the replication status. Key fields to look for include:
Slave_IO_State: Current status of the I/O thread.
Master_Host: The hostname of the master server.
Master_User: The user used for replication.
Seconds_Behind_Master: The lag in seconds between the slave and the master.
Last_Error: Any errors encountered during replication.
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check the replication status in MariaDB using the SHOW SLAVE STATUS\G command.
########################################
Check mariaDB uptime
SHOW GLOBAL STATUS LIKE 'uptime';
Check MariaDB Uptime
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check Uptime:
Once logged in, execute the following command:
SHOW GLOBAL STATUS LIKE 'uptime';
Interpret the Output:
The command will display the uptime of the MariaDB server in seconds. The output will look something like this:
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 123456 |
+---------------+--------+
The Value column shows the uptime in seconds.
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check the uptime of your MariaDB server using the SHOW GLOBAL STATUS LIKE 'uptime'; command.
#########################################
List Long running Process
SHOW PROCESSLIST\G
List Long Running Processes in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
List Processes:
Once logged in, execute the following command to list all running processes:
SHOW PROCESSLIST\G
Identify Long Running Processes:
The command will display a list of all active processes. Look for processes with high values in the Time column, which indicates how long the process has been running. The output will look something like this:
*************************** 1. row ***************************
Id: 12345
User: root
Host: localhost
db: mydatabase
Command: Query
Time: 120
State: Sending data
Info: SELECT * FROM mytable
*************************** 2. row ***************************
Id: 12346
User: root
Host: localhost
db: mydatabase
Command: Query
Time: 300
State: Sending data
Info: UPDATE mytable SET column='value'
The Time column shows the duration in seconds that each process has been running.
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you identify long-running processes in your MariaDB server using the SHOW PROCESSLIST\G command.
##########################################
Check status of INNODB
SHOW ENGINE INNODB SATUS \G
Check Status of InnoDB in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check InnoDB Status:
Once logged in, execute the following command to check the status of the InnoDB storage engine:
SHOW ENGINE INNODB STATUS\G
Interpret the Output:
The command will display detailed information about the InnoDB engine, including:
Transaction Information: Details about active transactions.
Lock Information: Information about locks held by transactions.
Buffer Pool and Memory Usage: Statistics on buffer pool usage and memory allocation.
Insert Buffer and Adaptive Hash Index: Information on insert buffer and adaptive hash index.
Log Information: Details about the InnoDB log files.
File I/O: Information on file input/output operations.
Row Operations: Statistics on row operations like inserts, updates, and deletes.
The output will look something like this:
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
2024-09-19 21:10:50 0x7f8b1c0b0700 INNODB MONITOR OUTPUT
Per second averages calculated from the last 1 seconds
BACKGROUND THREAD
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 0 srv_idle srv_master_thread log flush and writes: 0 …
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check the status of the InnoDB storage engine in your MariaDB server using the SHOW ENGINE INNODB STATUS\G command.
############################################
Check all Engine Status
SHOW ENGINES\G
Check Status of All Storage Engines in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check All Engine Status:
Once logged in, execute the following command to check the status of all storage engines:
SHOW ENGINES\G
Interpret the Output:
The command will display a list of all storage engines available in your MariaDB server, along with their status. The output will include:
Engine: The name of the storage engine.
Support: Indicates whether the engine is supported (YES), not supported (NO), or supported but not enabled (DISABLED).
Comment: A brief description of the engine.
Transactions: Indicates whether the engine supports transactions.
XA: Indicates whether the engine supports XA transactions.
Savepoints: Indicates whether the engine supports savepoints.
The output will look something like this:
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO …
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check the status of all storage engines in your MariaDB server using the SHOW ENGINES\G command.
##############################################
Check Warning Errors
SHOW WARNINGS;
SHOW WARNINGS\G
Check Warning Errors in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check Warning Errors:
Once logged in, execute the following command to check for warning errors:
SHOW WARNINGS;
For a more detailed view, use:
SHOW WARNINGS\G
Interpret the Output:
The command will display a list of warnings, errors, and notes generated by the last executed statement. The output will include:
Level: The severity level (Warning, Error, or Note).
Code: The error code.
Message: A description of the warning or error.
The output will look something like this:
+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1265 | Data truncated for column 'age' at row 1 |
+---------+------+--------------------------------------+
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check for any warning errors in your MariaDB server using the SHOW WARNINGS; and SHOW WARNINGS\G commands.
###############################################
Check SQL Mode
SELECT @@sql_mode;
Check SQL Mode in MariaDB
Login to MariaDB:
Open the terminal and log in to MariaDB:
mysql -u [username] -p
Enter your password when prompted.
Check SQL Mode:
Once logged in, execute the following command to check the current SQL mode:
SELECT @@sql_mode;
Interpret the Output:
The command will return the current SQL mode settings. The output will look something like this:
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
The SQL mode settings control the behavior of MariaDB in various situations, such as handling invalid data or enforcing strict data integrity.
Exit MariaDB:
To exit, type:
exit;
Press Enter.
This process will help you check the current SQL mode settings in your MariaDB server using the SELECT @@sql_mode; command.
#################################################
To exit mariadb
Exit
Exiting MariaDB
Exit Command:
While in the MariaDB command-line interface, simply type:
exit;
Press Enter.
Alternative Command:
You can also use:
quit;
Press Enter.
Both commands will terminate your MariaDB session and return you to the terminal prompt.
##################################################
######### MariaDB offline checks #################
Check error logs
journalctl -xeu mariadb.service
systemctl status mariadb.service
To check the error logs and status of the MariaDB service, follow these steps:
Check Error Logs:
journalctl -xeu mariadb.service
This command displays the error logs for the MariaDB service, providing detailed information about any issues.
Check Service Status:
systemctl status mariadb.service
This command shows the current status of the MariaDB service, including whether it is active, inactive, or failed, along with recent log entries.
These commands will help you diagnose and troubleshoot any problems with the MariaDB service.
#############################################
Check Stop start Mariadb
systemctl status mariadb.service
systemctl stop mariadb.service
systemctl start mariadb.service
To check, stop, and start the MariaDB service, follow these steps:
Check Service Status:
systemctl status mariadb.service
This command shows the current status of the MariaDB service, including whether it is active, inactive, or failed, along with recent log entries.
Stop the Service:
systemctl stop mariadb.service
This command stops the MariaDB service.
Start the Service:
systemctl start mariadb.service
This command starts the MariaDB service.
These commands will help you manage the MariaDB service effectively.
###################################################
Check Configaration
cat /etc/my.cnf
cat /etc/my.cnf.d/mariadb-server.cnf
To check the configuration of MariaDB, you can use the following commands:
Check the main configuration file:
cat /etc/my.cnf
This command displays the contents of the main MariaDB configuration file, which typically includes global settings for the database server.
Check the server-specific configuration file:
cat /etc/my.cnf.d/mariadb-server.cnf
This command shows the contents of the server-specific configuration file, which may contain additional settings specific to the MariaDB server instance.
These commands will help you review the configuration settings for your MariaDB installation.
###################################################
#### Missing Drives ###############
cat /etc/fstab
sudo mount -a
To manage and verify your filesystem mounts, you can use the following commands:
View the filesystem table:
cat /etc/fstab
This command displays the contents of the /etc/fstab file, which lists all the filesystems that should be mounted at boot time. Each line in this file represents a filesystem and includes details such as the device, mount point, filesystem type, and mount options.
Mount all filesystems:
sudo mount -a
This command mounts all filesystems mentioned in /etc/fstab that are not currently mounted. It is useful for testing changes made to the /etc/fstab file without rebooting the system.
These commands help ensure that your filesystems are correctly configured and mounted.
#### MariaDB replication communication failed check #######
Firwal configaration
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
sudo firewall-cmd --zone=public --add-port=4567/tcp --permanent
sudo firewall-cmd –reload
To configure your firewall using firewall-cmd, follow these steps:
Add Port 3306 (MySQL) to the Public Zone:
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
This command opens port 3306 for TCP traffic in the public zone permanently. Port 3306 is commonly used by MySQL databases.
Add Port 4567 to the Public Zone:
sudo firewall-cmd --zone=public --add-port=4567/tcp --permanent
This command opens port 4567 for TCP traffic in the public zone permanently. Ensure you know the service using this port to avoid security risks.
Reload the Firewall Configuration:
sudo firewall-cmd --reload
This command reloads the firewall configuration to apply the changes made. It ensures that the new rules are active without needing to restart the system.
These steps help secure your system by controlling network traffic through specified ports.
############################################################
Issue: Slave_IO_State: Reconnecting after a failed master event read
It may happen when Master was not up at the time when Slave tried to connect.
You may ignore it and just wait. Make sure that both of them are up.
To force reconnection use:
Stop slave;
Start slave;
Issue: Slave_IO_State: Reconnecting after a failed master event read
This issue occurs when the slave server attempts to connect to the master server, but the master server is not available at that time. Here’s a detailed description and steps to address it:
Possible Cause
The master server was down or unreachable when the slave tried to connect.
Solution
Ensure Both Servers Are Up:
Verify that both the master and slave servers are running and can communicate with each other.
Force Reconnection:
If the issue persists, you can force the slave to reconnect to the master by stopping and starting the slave process:
STOP SLAVE;
START SLAVE;
Steps to Verify and Resolve
Check Master and Slave Status:
On the slave server, run:
SHOW SLAVE STATUS\G;
Look for the Slave_IO_State and other relevant fields to diagnose the issue.
Restart Slave Process:
Execute the following commands on the slave server:
STOP SLAVE;
START SLAVE;
Monitor Logs:
Check the MySQL error logs on both the master and slave servers for any additional information that might indicate the cause of the issue.
Network Connectivity:
Ensure there are no network issues between the master and slave servers. You can use tools like ping or telnet to verify connectivity.
By following these steps, you can address the Slave_IO_State: Reconnecting after a failed master event read issue and ensure stable replication between your master and slave servers.
############################################
If you need more information, go check file:
less /var/lib/mysql/<hostname>.err
and you may find useful:
less /var/log/messages
To troubleshoot MySQL issues, you can check the following log files for detailed error descriptions:
MySQL Error Log:
less /var/lib/mysql/<hostname>.err
This file contains error messages and diagnostics related to MySQL server operations.
System Log:
less /var/log/messages
This file includes general system messages, which can provide additional context or related system-level issues.
These logs will help you identify and resolve specific problems with your MySQL server.
###########################################