Report this

What is the reason for this report?

How to Allow MySQL Remote Access Securely (2025 Guide)

Updated on September 4, 2025
How to Allow MySQL Remote Access Securely (2025 Guide)

Introduction

By default, MySQL only accepts local connections for security reasons. This setup is safe but limits scalability when your application and database need to run on separate servers. Enabling remote access to MySQL allows flexibility but introduces new security concerns.

In this guide, you’ll learn how to enable MySQL remote access securely, configure firewalls, grant appropriate permissions, and leverage AI-driven security checks to protect against misconfigurations and unauthorized logins.

Key Takeaways

MySQL Remote Access Basics:
To enable remote access, edit the MySQL configuration file (mysqld.cnf) and adjust the bind-address parameter to accept connections from external hosts. Next, create MySQL user accounts with host-specific access, such as 'user'@'client_ip', to restrict which clients can connect remotely. Always grant users only the minimum privileges they need, following the principle of least privilege to reduce security risks.

Security Risks and Mitigation:
Allowing MySQL to listen on all IP addresses (0.0.0.0 or '%') exposes your server to potential attacks and unauthorized access. To mitigate these risks, always restrict remote access to specific, trusted IP addresses or subnets using both MySQL user definitions and firewall rules. Employ firewalls like ufw, iptables, or cloud security groups to tightly control which IPs can reach port 3306. Disable remote root login and avoid anonymous accounts for added protection.

AI Tools and Automation:
Modern security practices recommend using AI-driven tools or scripts to continuously audit MySQL user grants and firewall configurations. These tools can monitor for suspicious login attempts or brute-force attacks, providing real-time alerts. Integrate automated security checks into your deployment pipeline to catch misconfigurations before they reach production, and use log analysis with anomaly detection to quickly identify and respond to threats.

Modern Best Practices:
Adopt Zero Trust principles by never assuming any network or user is inherently trusted. Require SSL/TLS encryption for all remote MySQL connections to safeguard data in transit. Regularly update MySQL and related software to patch vulnerabilities. Integrate security auditing and compliance checks into your CI/CD pipelines for ongoing protection, and maintain a robust backup and recovery plan, testing your backups periodically to ensure reliability.

Prerequisites

Before you begin configuring remote access to MySQL, make sure you have the following in place. Each prerequisite is critical for both functionality and security:

  • A Droplet with MySQL 8.0 or Newer Installed
    Ensure your server is running a supported version of MySQL. For best results, use the latest stable release to benefit from security patches and performance improvements.

  • Root or Sudo Privileges on the Database Server
    You must have administrative access to modify MySQL configuration files, manage users, and adjust firewall settings.

    • To check your privileges, run:

      sudo whoami
      
  • Related: Create a New MySQL User and Grant Permissions

  • Firewall Management Tools
    Proper firewall configuration is essential to restrict access to your MySQL server. You should be familiar with at least one of the following:

    • ufw (Uncomplicated Firewall) for Ubuntu-based systems
    • iptables for advanced or legacy setups
    • Ensure you know how to allow, restrict, and audit rules for port 3306 (the default MySQL port).
    • Note: If you’re not familiar with managing firewall rules, see UFW Essentials: Common Firewall Rules and Commands for a helpful guide.
  • (Recommended) AI-Driven Monitoring and Auditing Tools
    For enhanced security, consider integrating AI-powered monitoring solutions. These tools can:

    • Continuously audit MySQL user grants and privileges
    • Detect and alert on suspicious login attempts or brute-force attacks
    • Monitor firewall rule changes and flag misconfigurations
    • Examples include open-source log analyzers, cloud-based security platforms, or custom scripts leveraging machine learning.
  • (Optional but Strongly Advised) Secure Connection Tools

    • SSL/TLS Certificates: To encrypt traffic between clients and the MySQL server, especially over public networks.
    • Special Access: Restrict database connectivity to trusted channels only, use a site‑to‑site or client VPN, a Zero‑Trust access broker, or strictly allowlisted source IPs. Avoid exposing port 3306 to the public internet.
  • Backup and Recovery Plan
    Before making any changes, ensure you have recent backups of your MySQL databases and configuration files. This allows you to quickly recover in case of misconfiguration or unexpected issues.

By thoroughly preparing these prerequisites, you lay a strong foundation for a secure and reliable remote MySQL setup. Skipping any of these steps can expose your database to unnecessary risks or operational headaches.

Step 1 — Configure MySQL for Remote Connections

MySQL is bound to 127.0.0.1 by default. Update the bind address:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change:

bind-address = 127.0.0.1

Option A — Prefer binding to a private interface (recommended on cloud/private networks):

bind-address = 10.0.0.5

Replace 10.0.0.5 with your server’s private IP (e.g., VPC/VNet address). This limits exposure to your private network only.

Option B — Bind to all interfaces (only with strict firewalling):

bind-address = 0.0.0.0

Restart MySQL:

sudo systemctl restart mysql

Warning: Never leave MySQL globally exposed without firewall restrictions. If your host has a private IP, bind to that instead of 0.0.0.0.

Optional hardening in mysqld.cnf:

# Reduce DNS overhead and spoofing risk
skip_name_resolve = ON
# Disable loading local files by default
local_infile = OFF

Step 2 — Create a Remote MySQL User

Access MySQL shell:

sudo mysql -u root -p

Create a user tied to a specific IP:

CREATE USER 'appuser'@'203.0.113.10' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'203.0.113.10';
FLUSH PRIVILEGES;

Best Practice: Apply least privilege. Grant only the exact permissions required.

Authentication plugin notes (MySQL 8):

  • Default is caching_sha2_password (more secure). Prefer keeping it unless legacy clients cannot connect.
  • If older clients require it, switch a specific user to mysql_native_password:
ALTER USER 'appuser'@'203.0.113.10'
  IDENTIFIED WITH mysql_native_password BY 'StrongPassword!';

For more details on user management, see How to Create a New User and Grant Permissions in MySQL.

Step 3 — Secure Firewall Configuration

Allow connections to port 3306 only from trusted IPs:

sudo ufw allow from 203.0.113.10 to any port 3306

For multiple clients:

sudo ufw allow from 203.0.113.11 to any port 3306
sudo ufw allow from 198.51.100.25 to any port 3306

Avoid using sudo ufw allow 3306 without restrictions.

iptables example (legacy/advanced):

sudo iptables -A INPUT -p tcp -s 203.0.113.10 --dport 3306 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

Cloud firewall examples:

  • DigitalOcean Cloud Firewalls: Allow inbound TCP 3306 from selected droplet tags or fixed IPs; deny all others.
  • AWS Security Groups: Inbound rule TCP 3306 from a specific CIDR (e.g., 203.0.113.10/32) or from a peered VPC security group.

Step 4 — Test Remote Access

From the remote machine:

mysql -u appuser -h your_server_ip -p

If successful, you’ll connect securely to the remote MySQL instance.

Additional tests and troubleshooting:

# Basic reachability
mysqladmin -h your_server_ip -u appuser -p ping

# Scan open ports (run from a trusted host you control)
nmap -Pn -p 3306 your_server_ip

# Verify listening sockets on the server
sudo ss -ltnp | grep 3306 || sudo netstat -ltnp | grep 3306

Using an SSH tunnel (when direct DB exposure is not allowed):

ssh -L 3306:127.0.0.1:3306 user@your_server_ip -N
# Then connect locally
mysql -u appuser -h 127.0.0.1 -p

For production, prefer private networking or VPN over public exposure.

Step 5 — Enable SSL/TLS for Encrypted Connections

Why is this needed?
Enabling SSL/TLS ensures that all data exchanged between MySQL clients and the server is encrypted in transit. Without encryption, sensitive information including database credentials, queries, and returned data can be intercepted and read by attackers on the network. This is especially critical when allowing remote access over public or untrusted networks, as plaintext credentials and data are vulnerable to eavesdropping and man-in-the-middle attacks.

Notes:

  • SSL/TLS is strongly recommended for any remote MySQL connection, even within private networks, to guard against internal threats and accidental exposure.
  • Some cloud providers and compliance standards (like PCI DSS, HIPAA, GDPR) require encrypted database connections.
  • MySQL 5.7+ supports require_secure_transport to enforce SSL/TLS for all client connections.
  • You can use self-signed certificates for internal use, but for production environments, consider certificates signed by a trusted Certificate Authority (CA).
  • Always test SSL/TLS connectivity after enabling it, as misconfiguration can block legitimate access.
  • Enabling SSL/TLS does not replace the need for strong authentication and firewall rules; it complements them by protecting data in transit.

Server configuration in mysqld.cnf:

require_secure_transport = ON
# If using custom certificates, set the paths (PEM files)
# ssl-ca   = /etc/mysql/certs/ca.pem
# ssl-cert = /etc/mysql/certs/server-cert.pem
# ssl-key  = /etc/mysql/certs/server-key.pem

Restart MySQL after changes:

sudo systemctl restart mysql

Optionally require SSL per user:

ALTER USER 'appuser'@'203.0.113.10' REQUIRE SSL;

Client connection enforcing TLS:

mysql --ssl-mode=REQUIRED -u appuser -h your_server_ip -p

Verify TLS is negotiated:

mysql -u appuser -h your_server_ip -p -e "\s" | grep -i SSL

Common Pitfalls and Real-World Issues (and How to Fix Them)

When enabling MySQL remote access, teams often encounter a range of technical and operational challenges. Below are some of the most frequent real-world issues, their causes, and actionable solutions:

Problem / Symptom Fix / Solution Real-World Context & Root Cause
Access denied for user Double-check that the user is created with the correct host/IP (e.g., user@'203.0.113.10' or user@'%'). Use SELECT user, host FROM mysql.user; to verify. Reset the password if needed. The connecting client receives ERROR 1045 (28000): Access denied for user... even though credentials seem correct. This often happens when the MySQL user is only defined for localhost or a different host/IP, or the password is incorrect.
Still blocked after user setup Check local firewall status (sudo ufw status, sudo firewall-cmd --list-all) and ensure port 3306 is open to the correct IPs. In cloud environments, update security group rules to allow inbound MySQL traffic only from trusted sources. Even after creating the correct user, remote connections fail. This is frequently due to OS-level firewalls (like UFW, firewalld, or iptables) or cloud provider security groups (AWS, GCP, Azure) blocking port 3306.
Authentication plugin mismatch Prefer caching_sha2_password for modern clients. For legacy applications, set the user to mysql_native_password using ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';; upgrade client libraries where possible. Newer MySQL versions default to caching_sha2_password, but some clients (older MySQL clients, certain programming language drivers) only support mysql_native_password. This leads to authentication errors or failed connections.
Latency and Unstable Connections Use VPN tunnels or SSH port forwarding to create a secure, low-latency channel. For production, deploy MySQL close to your application servers or use private networking. Monitor network quality and consider TCP keepalive settings. Remote connections are slow, drop frequently, or time out. This is common over the public internet, especially with high-latency links or unreliable networks.
DNS Resolution Issues Ensure DNS records are up to date and propagate correctly. Use static IPs where possible, or update MySQL user grants if the server IP changes. Test with ping or nslookup from the client. MySQL hostnames (e.g., db.example.com) fail to resolve, or resolve to the wrong IP, causing connection failures. This is common in cloud or hybrid environments with dynamic DNS.
Bind Address Misconfiguration Edit mysqld.cnf to set bind-address = 0.0.0.0 (or a specific private IP for security). Restart MySQL and verify with ss -tlnp | grep 3306. MySQL is still listening only on 127.0.0.1 (localhost), even after user and firewall changes. This is a common oversight.
SSL/TLS Connection Failures Double-check certificate paths and permissions. Use openssl s_client to test connectivity. Ensure clients specify --ssl-mode=REQUIRED and trust the CA. Review MySQL logs for detailed errors. After enabling SSL/TLS, clients cannot connect, or connections fall back to unencrypted. Causes include certificate path errors, CA trust issues, or client misconfiguration.
Too-Permissive Grants Always restrict user grants to specific IPs or subnets. Regularly audit user privileges and remove unused accounts. Use AI-driven tools to detect and alert on overly broad grants. Granting access with user@'%' exposes the database to the entire internet, increasing risk of brute-force attacks and compliance violations.
Forgotten to Flush Privileges Run FLUSH PRIVILEGES; after modifying user accounts or grants to apply changes immediately. After making changes to user accounts or privileges, the changes do not take effect.

Pro Tip:
In production, always test remote access from a non-localhost machine after each configuration change. Use verbose client flags (e.g., mysql -h server_ip -u user -p --verbose) and check both MySQL and system logs for troubleshooting.

If issues persist, consider enabling MySQL general and error logs for deeper diagnostics, and leverage AI-based monitoring tools to proactively detect misconfigurations and suspicious access patterns.

AI-Assisted Security Auditing (2025)

Modern DevOps integrates AI to monitor MySQL remote access:

  • Permissions Check: Detects overly permissive grants (user@'%').
  • Firewall Audits: Alerts if port 3306 is exposed globally.
  • Log Monitoring: Identifies abnormal login attempts or brute-force attacks.
  • CI/CD Integration: Prevents deployment if insecure configs (bind-address=0.0.0.0) are detected.

Sample AI audit alert:

[ALERT] MySQL remote access misconfiguration detected.
  User: appuser@'%'
  Risk: Global access enabled
  Recommendation: Restrict to known IP addresses only.

Firewall Configuration Best Practices

Proper firewall configuration is essential for securing MySQL remote access. A misconfigured firewall can expose your database to the public internet, making it vulnerable to attacks. Follow these in-depth best practices to minimize risk:

1. Allow Only Specific IPs or VPN Subnets

  • Principle of Least Privilege: Configure your firewall to accept inbound MySQL (TCP port 3306) connections only from trusted IP addresses or VPN subnets. Avoid using 0.0.0.0/0 (anywhere) unless absolutely necessary for testing, and never in production.

  • VPN Recommendation: For added security, require remote users to connect via a VPN (e.g., WireGuard, OpenVPN) before accessing MySQL. This hides the database from the public internet and limits exposure.

2. Disable Remote Root Login

  • Why: The root account is a common target for brute-force attacks. Disabling remote root login ensures that even if port 3306 is exposed, attackers cannot use the default superuser account.

  • How to Implement:

    ALTER USER 'root'@'%' ACCOUNT LOCK;
    
    • Alternatively, remove all non-localhost root entries:

      DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
      FLUSH PRIVILEGES;
      
  • Verification: Run SELECT user, host FROM mysql.user WHERE user='root'; to confirm only root@localhost remains active.

3. Monitor Logs Continuously

  • Why: Continuous log monitoring helps detect unauthorized access attempts, port scans, or brute-force attacks in real time.
  • How to Implement:
    • MySQL Logs: Monitor /var/log/mysql/error.log or the system journal for failed login attempts and suspicious activity.
    • Firewall Logs: Enable and review firewall logs (e.g., /var/log/ufw.log or firewalld logs) to detect repeated connection attempts from unknown IPs.
    • Automated Alerts: Set up log monitoring tools (e.g., Fail2ban, OSSEC, or cloud-native solutions) to trigger alerts or block IPs after repeated failed attempts.

4. Automate Firewall Audits with AI-Based Scripts

  • Why: Manual firewall audits are error-prone and may miss subtle misconfigurations. AI-driven tools can continuously analyze firewall rules, detect anomalies, and recommend or enforce best practices.
  • How to Implement:
    • Open-Source Tools: Integrate tools like CrowdSec or Wazuh to analyze logs and firewall rules, using machine learning to spot suspicious patterns.
    • Custom AI Scripts: Use Python scripts with libraries such as nmap, iptables, and AI/ML frameworks (e.g., scikit-learn) to scan for open ports, compare against a baseline, and flag unexpected changes.
    • Sample Workflow:
      1. Script scans firewall rules and open ports.
      2. AI model evaluates exposure risk based on known safe IPs/subnets and historical access patterns.
      3. If a rule exposes port 3306 to the public or an unknown IP, the system sends an alert or automatically blocks the rule.
    • CI/CD Integration: Incorporate firewall audits into your deployment pipeline. For example, use GitHub Actions or GitLab CI to run firewall checks before deploying infrastructure changes.

Pro Tip: Regularly review and update firewall rules as your infrastructure evolves. Document all exceptions and ensure that only authorized personnel can modify firewall configurations.

By following these comprehensive firewall best practices, you significantly reduce the attack surface of your MySQL server and ensure that only trusted clients can access your database remotely.

Server Hardening Recommendations

Apply defense-in-depth beyond the basics:

  • Keep MySQL updated to the latest stable minor release.
  • Disable anonymous accounts:
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;
  • Enforce strong passwords (Password Validation Component):
# mysqld.cnf
validate_password.length = 12
validate_password.check_user_name = ON
  • Limit host access in user definitions (avoid @'%').
  • Backups: test restores periodically; encrypt backups at rest.
  • Logging: monitor /var/log/mysql/error.log (or system journal) for auth failures.

Troubleshooting Checklist

  • Verify bind-address: grep bind /etc/mysql/mysql.conf.d/mysqld.cnf.
  • Check firewall rules: sudo ufw status.
  • Check cloud firewalls: confirm security groups or platform firewalls allow your client IP.
  • Inspect MySQL grants:
SHOW GRANTS FOR 'appuser'@'203.0.113.10';

For a step‑by‑step walkthrough of creating users and assigning roles, see How to Create a New User and Grant Permissions in MySQL.

  • Test with telnet:
telnet your_server_ip 3306
  • Verify TLS: mysql -e "\\s" | grep -i SSL should show an active cipher when required.

Implementation Examples

Example 1 — Connect with Python (mysql-connector)

import mysql.connector

conn = mysql.connector.connect(
    host="203.0.113.10",
    user="appuser",
    password="StrongPassword!",
    database="mydb"
)

cursor = conn.cursor()
cursor.execute("SELECT NOW();")
print(cursor.fetchone())

Example 2 — Connect with PHP (PDO)

<?php
$host = '203.0.113.10';
$db   = 'mydb';
$user = 'appuser';
$pass = 'StrongPassword!';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

try {
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Example 3 — WordPress wp-config.php

In WordPress, set your remote DB details:

define( 'DB_NAME', 'mydb' );
define( 'DB_USER', 'appuser' );
define( 'DB_PASSWORD', 'StrongPassword!' );
define( 'DB_HOST', '203.0.113.10:3306' );

Restart WordPress, and it will use the remote MySQL server.

FAQ

Q: What port should I use for remote MySQL access, and how can I secure it?
A: MySQL uses port 3306/TCP by default for remote connections. To keep your database secure, never leave this port open to the entire internet. Instead, configure your firewall to allow access only from trusted IP addresses or networks that require remote access. Regularly audit your firewall rules and consider using AI-driven tools to monitor for unauthorized attempts or misconfigurations. This approach helps prevent unwanted access and reduces the risk of attacks targeting your MySQL server.

Q: Is it safe to set MySQL’s bind-address to 0.0.0.0 for remote access?
A: Setting the bind-address to 0.0.0.0 allows MySQL to accept connections from any network interface, which can be risky if not properly secured. Only use this setting if you have strict firewall rules in place that limit access to specific, trusted IPs. Ideally, bind MySQL to a private or internal IP address whenever possible. Additionally, implement continuous monitoring, Preferably with AI-powered tools to detect and respond to suspicious connection attempts in real time.

Q: How can AI help detect unauthorized MySQL logins or suspicious activity?
A: AI-powered log analyzers can automatically scan MySQL access logs to identify patterns of suspicious activity, such as brute-force login attempts, repeated failed logins, or access from unfamiliar IP addresses. These tools can alert administrators in real time, enabling rapid response to potential threats. Integrating AI-driven monitoring into your security strategy helps maintain compliance, reduces manual oversight, and provides an extra layer of protection against evolving attack methods.

Q: What is the recommended way to disable remote root login in MySQL?
A: Disabling remote root login is a critical security step. You can do this by running the SQL command: ALTER USER 'root'@'%' ACCOUNT LOCK;. This locks the root account for all remote connections, ensuring that only local users can access root privileges. Always use a dedicated, least-privilege user account for remote access, and regularly review user permissions to minimize the risk of unauthorized access or privilege escalation.

Q: What are the best practices for securing MySQL remote access in 2025?
A: To secure MySQL remote access, follow these best practices: restrict access using IP whitelisting, grant users only the minimum privileges they need, and use VPN tunnels for encrypted connections. Enable SSL/TLS to protect data in transit, and leverage AI-driven monitoring to detect unusual activity. Integrate security checks into your CI/CD pipeline to prevent misconfigurations during deployment. Regularly audit firewall rules and rotate credentials to maintain a strong security posture.

Q: How does MySQL compare to other databases for remote access scenarios?
A: MySQL is a popular choice for remote database access due to its robust security features, scalability, and wide support. Compared to alternatives like SQLite (which is file-based and not designed for remote access) and PostgreSQL (which offers advanced features and similar remote capabilities), MySQL strikes a balance between ease of use and security. For a detailed comparison, see SQLite vs MySQL vs PostgreSQL.

Q: How can I quickly enable SSL for MySQL remote connections?
A: To enable SSL, set require_secure_transport=ON in your MySQL configuration file. When connecting, use the --ssl-mode=REQUIRED option to enforce encrypted connections. If you have custom certificates, provide the CA, certificate, and key file paths as needed. Enabling SSL ensures that all data transmitted between your client and server is encrypted, protecting sensitive information from interception or tampering during transit.

Q: My client can’t connect due to caching_sha2_password. How do I resolve this?
A: The caching_sha2_password authentication plugin is the default in recent MySQL versions and may not be supported by older clients. The best solution is to upgrade your client software to a version that supports this plugin. If upgrading isn’t possible, you can change the affected user’s authentication method to mysql_native_password using the SQL command: ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';. Use this workaround cautiously and only when necessary.

Q: How do I verify that MySQL is listening on the correct network interface?
A: To check which interface MySQL is listening on, run sudo ss -ltnp | grep 3306 on your server. This command displays the listening address and port for MySQL. Confirm that it matches your intended configuration—ideally a private IP or localhost for security. Regularly auditing this setting helps prevent accidental exposure of your database to the public internet and supports compliance with security best practices.

MySQL Remote Access Security Checklist (2025)

Security Step Why It Matters Implementation Example
Restrict bind-address Prevents global exposure of MySQL bind-address = 10.0.0.5 in mysqld.cnf
Use firewall whitelisting Blocks unauthorized IPs sudo ufw allow from 203.0.113.10 to any port 3306
Grant least privilege Minimizes attack surface GRANT SELECT, INSERT ON mydb.* TO 'appuser'@'203.0.113.10';
Disable remote root login Protects from root-level exploitation ALTER USER 'root'@'%' ACCOUNT LOCK;
Use strong passwords Defends against brute-force attacks e.g., StrongPassword!123
Enable SSL/TLS connections Encrypts traffic between client & server Configure require_secure_transport = ON in MySQL
Monitor logs with AI Detects abnormal login attempts AI log audit alert for brute force detection
Integrate CI/CD checks Prevents insecure deployments Security scans for bind-address=0.0.0.0
Rotate credentials regularly Reduces long-term credential leaks Update MySQL user passwords quarterly
Audit firewall rules Ensures ongoing compliance Automated AI firewall audit scripts

Conclusion

Allowing remote access to MySQL unlocks powerful capabilities for distributed applications and teams, but it also introduces significant security responsibilities. The best approach is a layered one: start by configuring MySQL to listen only on trusted interfaces, and always restrict access at the firewall level to specific, authorized IP addresses. Grant users only the minimum privileges they need, and never allow remote root logins. Enforce strong, unique passwords and enable SSL/TLS to encrypt all data in transit.

By combining robust MySQL configuration, strict firewall policies, least-privilege user management, and proactive AI-powered auditing, you can confidently enable remote access while minimizing risk.

For a managed, worry-free experience, consider DigitalOcean Managed MySQL Databases, which handle scaling, backups, and secure configurations automatically.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

Tutorial Series: How To Troubleshoot Issues in MySQL

This guide is intended to serve as a troubleshooting resource and starting point as you diagnose your MySQL setup. We’ll go over some of the issues that many MySQL users encounter and provide guidance for troubleshooting specific problems. We will also include links to DigitalOcean tutorials and the official MySQL documentation that may be useful in certain cases.

About the author(s)

Mark Drake
Mark Drake
Author
Manager, Developer Education
See author profile

Former Technical Writer at DigitalOcean. Focused on SysAdmin topics including Debian 11, Ubuntu 22.04, Ubuntu 20.04, Databases, SQL and PostgreSQL.

Vinayak Baranwal
Vinayak Baranwal
Editor
See author profile

Building future-ready infrastructure with Linux, Cloud, and DevOps. Full Stack Developer & System Administrator @ DigitalOcean | GitHub Contributor | Passionate about Docker, PostgreSQL, and Open Source | Exploring NLP & AI-TensorFlow | Nailed over 50+ deployments across production environments.

Still looking for an answer?

Was this helpful?


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Thank you, it worked.

Thank you, the only thing that might be missing is that by default port 3306 should be blocked by your firewall.

sudo ufw allow 3306

should do the trick ;)

This almost worked for me lol. I’m trying to set up a development LAMP server in a virtual machine. I finally managed to get most of it set up. The VM is running the server stuff (duh) but I’m using the host machine (Windows 10) to do the testing. I got Windows to access the server; I created a “test.php” file with a simple echo command to test if it would behave as expected and it did.

Now I’m trying to set up MySQL to allow remote access, which is how I ended up here. I want to use a graphical interface to set up databases and tables and everything. As far as the VM and Windows itself is concerned, the server is just another real PC on the same network. At first, MySQL Workbench was throwing an error that it was unable to connect to the server. Following the instructions here, the error became that it wasn’t allowed to access the server.

I don’t know where my issue is. When I installed MySQL, I wasn’t asked to make a password and I wasn’t made aware of one being created. One website pointed me towards a file that didn’t exist, saying a random generated password was there. Another website said it was in the error log. The error log said it created a “root” user with a blank password. What should I do next?

|–EDIT–| Turns out I just needed to access MySQL from the server (VM) itself and create a new user. If anyone else has this issue, here’s what I did:

From Linux’s command line, run, without quotes, “mysql” This starts the utility where you input SQL commands to be ran. You know you’re here when the command line leads with “mysql>”

Type in, also without double quotes “CREATE USER ‘<USERNAME>’@‘<SERVER_HOSTNAME>’ IDENTIFIED BY ‘<PASSWORD>’;” <USERNAME> will be the name of the new user. <SERVER_HOSTNAME> should be the host name of the remote computer. I used the IP address of my host machine. And of course, <PASSWORD> will be the new users password. The single quotes (aka: apostrophes) around these three items is required, I believe. I left them in and it worked just fine.

You missed to mention about opening port in firewall.

To allow connection from any IP address to port 3306:

ufw allow 3306

To allow connection from one particular IP address to port 3306

ufw allow from **IP_ADDRESS** to any port 3306

I followed this steps, but I still cannot connect to my MySQL database running on my digitalocean server. I did these steps:

  • Installed MySQL Server + adding a new user
  • Activated the firewall: sudo ufw enable
  • Allowed the MySQL port: sudo ufw allow 3306
  • Reloaded the Firewall: sudo ufw reload

Am I missing something?

forget it.

mysql>GRANT ALL ON . to root@‘123.123.123.123’ IDENTIFIED BY ‘put-your-password’; mysql>FLUSH PRIVILEGES;

When I change the bind_address to my (APACHE+php) IP server and I restart the MySQL server he refuses to restart and showed me an error. It worked just if I set the IP address to 0.0.0.0 and that’s not good for security I just want my (apache+php) server to connect to MySQL.Do you guys have a problem if you change the bind_address to a specific address.

When i checked /var/log/mysql/error.log it say Can’t start server: Bind on TCP/IP port: Cannot assign requested address .

Hi,

I applied all steps 2 times but it is not working for me. I can’t access the database from other servers or apps. Mysql is in a droplet server, the port is open and the user was created for the public IP. Another idea?

Actually i did all the procedures, even after creating a new user with my server ip, i cannot access it through my local machine

Error: connect ETIMEDOUT

this is what i get when i try to connect, can someone help?

CREATE USER 'sammy'@'remote_server_ip'

In this example remote_server_ip this is a server from user connected to DB? In case remote_server_ip changed user can’t connect? How to change remote_server_ip for any IP?

Creative CommonsThis work is licensed under a Creative Commons Attribution-NonCommercial- ShareAlike 4.0 International License.
Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Get started for free

Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

*This promotional offer applies to new accounts only.