Three Methods to Connect to MySQL Database on Localhost

For security reasons, port 3306 and MySQL remote connections are typically disabled on servers, allowing connections only from localhost. This article shares how to connect to a MySQL database via localhost.

Using Web Tools

You can install web tools such as PHPMyAdmin or Adminer in the server's web environment to manage the database. These tools support both local (localhost) and remote connections, making them very convenient and flexible. Currently, the LNMP one-click package and OneinStack both integrate PHPMyAdmin management tools.

However, installing such web tools also means others can access PHPMyAdmin from the network. To prevent brute-force attacks, it is recommended to use htpasswd to implement Nginx access verification to add an extra layer of protection.

Command Line Management

If you are a professional and do not wish to use web tools, you can manage the database via the command line. This method requires familiarity with SQL statements; otherwise, operations can be difficult. Execute the following commands on the server to connect to the database:

# Connect to the database
mysql -u username -p password
# Show databases
show databases;
# Use the 'test' database
use test;
# Show tables
show tables;
# ... and more operations

Client Tool: Navicat

Navicat is an excellent client management tool that supports connecting to various databases. You might ask, "The article mentions connecting to the database via localhost; how do you achieve remote connection with Navicat?" Precisely because Navicat is so powerful, it can connect to a MySQL database on localhost via an SSH tunnel. This is the key point.

1. Open the Navicat tool.
2. Create a new MySQL connection.
3. Switch to the SSH tab and fill in your server account information, as shown in the screenshot below.

4. Switch to the General tab, fill in your MySQL information, and you can now connect to the database from your local computer, as shown in the screenshots below.

Navicat Connection (localhost) Database Flowchart

Summary

PHPMyAdmin, being a web-based management tool, carries relatively higher risks and does not support importing very large database files. The command-line method is more complex. A balanced approach is to use a client tool like Navicat, which is quite convenient. The above are some common methods for connecting to a local database compiled by Xiao Z. If you have better methods, please leave a message below to learn together.