MySQL error messages are short, but they almost always tell you which layer failed: the server process, the socket or network, authentication, a lock, or the storage underneath. In this tutorial you will learn where MySQL records its errors on Ubuntu 24.04 and work through the errors you are most likely to meet in production, each with a way to confirm the cause and a fix.
Prerequisites
To follow this tutorial you need:
- A server running Ubuntu 24.04 LTS with MySQL 8.0 installed from the Ubuntu repositories (
mysql-serverpackage), for example a CubePath VPS. - A non-root user with
sudoprivileges. - A recent backup of your databases before you try any of the repair steps in Step 7.
On Ubuntu, the MySQL root account authenticates with the auth_socket plugin by default, so you log in as root with sudo mysql and no password. The commands below use that method. MariaDB uses the same error codes for most of these cases, but its service is called mariadb and its paths differ slightly.
Step 1 - Checking the service and the error log
Most serious problems are recorded in the MySQL error log before users notice them. Check whether the service is running first:
sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Fri 2026-09-25 08:12:40 UTC; 3h ago
On Ubuntu, the error log is /var/log/mysql/error.log. Confirm the path and read the most recent entries:
sudo mysql -e "SELECT @@log_error;"
sudo tail -n 50 /var/log/mysql/error.log
If the service fails to start, the journal usually shows the reason together with the error log:
sudo journalctl -u mysql -n 50 --no-pager
Keep the error log open in a second terminal with sudo tail -f /var/log/mysql/error.log while you work through the following steps.
Step 2 - Fixing connection errors
ERROR 2002: Can't connect through socket
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
The client connects to localhost through a Unix socket, and the socket does not exist. In almost every case the server is not running. Start it and check whether it stays up:
sudo systemctl start mysql
sudo systemctl status mysql
If it fails again, the reason is in the error log (Step 1). Frequent causes are a full disk (Step 6), a typo in a configuration file, or a memory setting larger than the server's RAM. To find configuration mistakes, validate the configuration without starting the server:
sudo -u mysql mysqld --validate-config
If the server is running but the error persists, the client is looking for the socket in the wrong place. Compare the server's socket with the one in your application's configuration:
sudo mysql -e "SELECT @@socket;"
ERROR 2003: Can't connect to MySQL server on a remote host
ERROR 2003 (HY000): Can't connect to MySQL server on '203.0.113.10:3306' (111)
Error 111 means connection refused. By default MySQL on Ubuntu listens only on 127.0.0.1. Check where it listens:
sudo ss -ltnp | grep 3306
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=1182,fd=23))
If a remote application server really needs access, open /etc/mysql/mysql.conf.d/mysqld.cnf, set bind-address to the server's private IP, restart MySQL with sudo systemctl restart mysql, and allow only that client in the firewall:
sudo ufw allow from 10.0.0.5 to any port 3306 proto tcp
Replace 10.0.0.5 with your application server's IP. Never expose port 3306 to the whole Internet. Error (110) instead of (111) means the connection timed out, which points to a firewall dropping the packets.
Step 3 - Fixing authentication errors
ERROR 1698: Access denied for root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
This appears when you run mysql -u root without sudo on Ubuntu. The root account uses auth_socket, which only accepts the operating system's root user. Use sudo mysql instead. For applications, create a dedicated user rather than changing how root authenticates.
ERROR 1045: Access denied (using password: YES)
ERROR 1045 (28000): Access denied for user 'app'@'localhost' (using password: YES)
Either the password is wrong, or no account matches the user and host combination. MySQL identifies accounts by both, so 'app'@'localhost' and 'app'@'%' are different accounts with different passwords. List the accounts that exist:
sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user = 'app';"
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| app | 10.0.0.% | caching_sha2_password |
+------+-----------+-----------------------+
Here the account only exists for the 10.0.0.% network, so a connection from localhost is refused. Create the missing account, or reset the password of the existing one:
sudo mysql
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'your_strong_password';
Replace your_strong_password with a strong, unique password and update it in the application's configuration. Then check that the account has privileges on the database it needs:
SHOW GRANTS FOR 'app'@'10.0.0.%';
Resetting a lost root password
If someone changed root to password authentication and the password is lost, sudo mysql no longer works. Start MySQL temporarily without privilege checks and with networking disabled so nobody else can connect:
sudo systemctl stop mysql
sudo install -d -o mysql -g mysql /run/mysqld
sudo -u mysql mysqld --skip-grant-tables --skip-networking &
Connect and restore Ubuntu's default socket authentication for root. FLUSH PRIVILEGES reloads the grant tables so that ALTER USER works:
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
EXIT;
Shut down the temporary instance and start the service normally:
sudo mysqladmin -u root shutdown
sudo systemctl start mysql
sudo mysql -e "SELECT CURRENT_USER();"
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
Step 4 - Fixing ERROR 1040: Too many connections
ERROR 1040 (HY000): Too many connections
The server reached max_connections (151 by default). MySQL keeps one extra connection for administrative accounts, so sudo mysql usually still works. Check the limit, current usage, and the historical peak:
sudo mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Max_used_connections');"
Then see who holds the connections and what they are doing:
sudo mysql -e "SELECT user, host, db, command, time, state FROM information_schema.processlist ORDER BY time DESC LIMIT 20;"
Two patterns are common:
- Hundreds of connections in
Sleepstate: the application opens connections and does not close them, or its connection pool is larger than it needs to be. Fix the pool size in the application. Loweringwait_timeoutalso closes idle connections sooner. - Many connections running or waiting on the same query: the database is slow, requests pile up, and each one holds a connection. Raising the limit only makes the pile bigger. Find the slow query or the lock (Step 5).
If you have confirmed that the load is legitimate and the server has memory to spare, raise the limit. SET PERSIST applies immediately and survives restarts:
sudo mysql -e "SET PERSIST max_connections = 300;"
Step 5 - Fixing lock wait timeouts and deadlocks
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
A query waited longer than innodb_lock_wait_timeout (50 seconds by default) for a row locked by another transaction. The other transaction is usually a long-running one, or one the application opened and never committed. While the problem is happening, find the blocking session with the sys schema:
sudo mysql -e "SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, sql_kill_blocking_connection FROM sys.innodb_lock_waits\G"
*************************** 1. row ***************************
waiting_pid: 3121
waiting_query: UPDATE orders SET status = 'paid' WHERE id = 5512
blocking_pid: 3087
blocking_query: NULL
sql_kill_blocking_connection: KILL 3087
A blocking_query of NULL means the blocking session is idle inside an open transaction, a typical application bug. List transactions that have been open for more than a minute:
sudo mysql -e "SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query FROM information_schema.innodb_trx WHERE trx_started < NOW() - INTERVAL 1 MINUTE;"
To release the lock immediately, run the KILL statement from the output. The killed session's transaction is rolled back:
sudo mysql -e "KILL 3087;"
For a lasting fix, keep transactions short and make sure the application commits or rolls back on every code path.
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
A deadlock is different: two transactions each waited for the other, and InnoDB rolled one back immediately. The application should retry the transaction. Inspect the most recent deadlock in the LATEST DETECTED DEADLOCK section of:
sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | less
Frequent deadlocks usually come from transactions that update the same rows in different orders, or from updates that scan many rows because of a missing index.
Step 6 - Fixing storage errors
Disk full
ERROR 3 (HY000): Error writing file '/tmp/MYfd=12' (OS errno 28 - No space left on device)
Errno 28 means the filesystem is full. Check both space and inodes, then find what is using space in the data directory:
df -h /var/lib/mysql /tmp
sudo du -sh /var/lib/mysql/* | sort -rh | head
MySQL 8.0 enables binary logging by default, and binary logs (binlog.000123 files) are often the largest item. List them:
sudo mysql -e "SHOW BINARY LOGS;"
If the server has no replicas, or all replicas have already read past them, purge old logs. Never delete binary log files with rm, which leaves MySQL's index out of sync:
sudo mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"
To keep them from growing again, set a shorter retention period (in seconds; the default is 30 days):
sudo mysql -e "SET PERSIST binlog_expire_logs_seconds = 604800;"
Packet too large
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
A single query or row, often an import with large INSERT statements or a big BLOB, exceeds max_allowed_packet (64 MB by default in MySQL 8.0). Raise it on the server, and on the client when importing:
sudo mysql -e "SET PERSIST max_allowed_packet = 268435456;"
mysql --max-allowed-packet=256M -u app -p your_database < dump.sql
Step 7 - Checking and recovering damaged tables
InnoDB repairs itself automatically after a crash by replaying its redo log, so most crashes need no action. Check tables when the error log reports corruption, or when a MyISAM table reports:
ERROR 145 (HY000): Table './your_database/sessions' is marked as crashed and should be repaired
Check all tables with mysqlcheck:
sudo mysqlcheck --all-databases --check
For MyISAM tables that show errors, repair them:
sudo mysqlcheck --repair your_database sessions
REPAIR does not apply to InnoDB. If InnoDB is damaged so badly that MySQL crashes on startup, you can start it in forced recovery mode only to dump the data. Open the configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add this line under [mysqld]:
innodb_force_recovery = 1
Start MySQL and dump everything to a file:
sudo systemctl start mysql
sudo mysqldump --all-databases --single-transaction --routines --events > /root/recovery-dump.sql
If MySQL still does not start, raise the value one step at a time up to 3. Values 4 to 6 can permanently damage data files, so use them only after copying /var/lib/mysql somewhere safe. Once you have a dump, remove the innodb_force_recovery line, and restore the data into a clean data directory or a new server. Restoring from your latest backup is often faster and more reliable.
WarningWhile
innodb_force_recoveryis set, the data may be inconsistent (from value 4 upward InnoDB is also read-only). Keep applications disconnected until you have restored the data.
Step 8 - Checking replication errors
On a replica, errors stop replication silently while the server itself keeps running. Check its status:
sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_IO_Running|Replica_SQL_Running|Last_IO_Error|Last_SQL_Error|Seconds_Behind_Source'
Replica_IO_Running: Yes
Replica_SQL_Running: No
Last_IO_Error:
Last_SQL_Error: Could not execute Write_rows event on table shop.orders; Duplicate entry '5512' for key 'orders.PRIMARY'
Seconds_Behind_Source: NULL
Both threads must say Yes. An IO thread error is about connectivity or credentials to the source. An SQL thread error like this one means the replica's data has drifted from the source, usually because something wrote directly to the replica. Skipping the failing event hides the drift rather than fixing it. The reliable fix is to rebuild the replica from a fresh backup of the source, and to set super_read_only = ON on replicas so it cannot happen again.
Conclusion
You now know where MySQL logs its errors on Ubuntu 24.04 and how to handle the ones that cause most outages: a stopped server, host and password mismatches, connection exhaustion, lock contention, full disks and damaged tables. In most cases the error code plus the error log points directly at the cause. As next steps, enable the slow query log to catch performance problems early, and schedule automated mysqldump or physical backups that you test restoring regularly.