Troubleshoot Common MySQL Issues on a VPS

This guide walks you through diagnosing and fixing the most frequent MySQL problems you’ll encounter on a Virtual Private Server (VPS). Whether your database won’t start, connections are refused, or queries are painfully slow, you’ll learn practical, step-by-step solutions that get your server back online quickly. You’ll need SSH access to your VPS and a user account with sudo privileges.

What You’ll Need

• SSH client (e.g., Terminal on macOS/Linux, PuTTY on Windows)
• Root or sudo access to your VPS
• Basic familiarity with the command line

Step-by-Step

1. Check if MySQL is Running

First, confirm whether the MySQL service is actually active. On most modern Linux distributions (Ubuntu, Debian, CentOS 7+), run:

sudo systemctl status mysql

If the service is not running, you’ll see an “inactive (dead)” status. Start it with:

sudo systemctl start mysql

If it fails to start, continue to the next step to see why.

2. Examine MySQL Error Logs

The error log is your best friend. It usually lives at /var/log/mysql/error.log on Debian/Ubuntu or /var/log/mysqld.log on RHEL/CentOS. View the last 50 lines:

sudo tail -n 50 /var/log/mysql/error.log

Look for lines containing [ERROR]. Common messages include:

• Can't connect to local MySQL server through socket – the daemon isn’t running.
• Table './db/table' is marked as crashed – a table needs repair.
• Out of memory – the server ran out of RAM.
• Access denied for user – permission issues.

3. Verify Disk Space and Permissions

MySQL needs disk space to write data and logs. Check with:

df -h

If the partition holding /var/lib/mysql is 100% full, MySQL will refuse to start. Free up space by removing old logs or backups. Also ensure the MySQL data directory has correct ownership:

sudo chown -R mysql:mysql /var/lib/mysql

4. Test the Configuration File

A syntax error in my.cnf or my.ini can prevent MySQL from starting. Validate the config without starting the service:

sudo mysqld --validate-config

If there’s an error, it will print the offending line. Fix it and try starting again.

5. Connect via the MySQL Client

Once the service is running, test a local connection:

mysql -u root -p

If you get Access denied, you may need to reset the root password. To do that, stop MySQL, start it in safe mode, and update the password:

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
sudo mysql -u root

Then run:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
EXIT;

Kill the safe-mode process and restart normally.

6. Check Network Binding

If you can connect locally but not remotely, MySQL might be bound only to localhost. Check the bind-address in your config file. To allow remote connections, set it to 0.0.0.0 (or a specific IP) and restart MySQL. Also ensure your firewall (ufw, iptables) allows port 3306.

7. Repair Corrupted Tables

If you see table corruption errors, run the repair utility:

sudo mysqlcheck -u root -p --auto-repair --all-databases

Alternatively, use the REPAIR TABLE command in the MySQL client for a specific table.

8. Increase Memory Limits

If MySQL crashes due to out-of-memory errors, adjust the innodb_buffer_pool_size in your config. A common rule of thumb is to set it to 70% of your available RAM. For a 2GB VPS, try:

[mysqld]
innodb_buffer_pool_size = 1G

Restart MySQL and monitor memory usage with htop.

9. Slow Queries? Enable the Slow Query Log

To find performance bottlenecks, enable the slow query log in your config:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Restart MySQL, then review the log after some time to see which queries take over 2 seconds.

10. Restart and Verify

After making changes, always restart MySQL:

sudo systemctl restart mysql

Then check the status to ensure it’s running:

sudo systemctl status mysql

Common Mistakes

• Editing the config file without backing it up. Always copy my.cnf before making changes.
• Forgetting to restart after config changes. Many settings only take effect on restart.
• Using the wrong socket path. If you changed the socket location in the config, the client must use the same path.
• Ignoring permission errors on the data directory. MySQL will fail to start if it can’t read/write its own files.
• Setting innodb_buffer_pool_size too high. This can cause swapping and OOM kills on small VPS.

Tips

• Set up a monitoring tool like mysqltuner to get recommendations for MySQL tuning.
• Use SHOW PROCESSLIST; to see live queries and spot long-running ones.
• Keep MySQL updated to get bug fixes and security patches.
• Schedule regular backups with mysqldump to avoid data loss.
• If you’re on a managed VPS, check your provider’s documentation for specific MySQL paths.

Troubleshooting

What if MySQL still won’t start? Try starting it in the foreground to see the exact error:

sudo mysqld --console

This prints errors directly to the terminal. If you see InnoDB: Operating system error number 13, it’s a permission problem – fix the ownership of the data directory.

What if I can’t connect via socket? The socket file might be missing. Check if /var/run/mysqld/mysqld.sock exists. If not, create the directory and set the right permissions:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

What if the error log is empty? Sometimes MySQL logs to syslog. Check /var/log/syslog or run journalctl -u mysql on systemd systems.

What if I forgot the root password? Follow the safe-mode procedure in Step 5. If that fails, you may need to reinstall MySQL, but that should be a last resort.

Frequently Asked Questions

Why does MySQL use so much memory?

MySQL caches data in memory to speed up queries. The innodb_buffer_pool_size is the main consumer. Reduce it to lower memory usage, but expect slower performance.

How do I allow remote connections to MySQL?

Edit my.cnf, set bind-address = 0.0.0.0, restart MySQL, and create a user with a host of '%' (e.g., CREATE USER 'user'@'%' IDENTIFIED BY 'pass';). Also open port 3306 in your firewall.

What is the default MySQL port?

3306. Make sure it’s not blocked by a firewall and that MySQL is listening on it (check with ss -tlnp | grep 3306).

How do I reset the MySQL root password?

Stop MySQL, start it with --skip-grant-tables, connect without a password, update the root password with ALTER USER, then restart normally. See Step 5 for full commands.

Why do I get ‘Table doesn’t exist’ even though I can see it?

This often happens after a crash. The table may be corrupted or the database name is case-sensitive on Linux. Check the exact table name and run CHECK TABLE and REPAIR TABLE.

MySQL is slow, what should I check first?

Enable the slow query log (Step 9), check your indexes, and ensure your innodb_buffer_pool_size is appropriate for your RAM. Also look at the number of connections – too many can cause contention.

Related

• Office Tab Feature Demo: What It Does
• How to Merge Two iTunes Libraries
• Document an Endangered Language with Community Consent