How to use HeidiSQL to admin manage MySQL on remote servers and how to configure the IP so that you can connect to the server using the cPanel control panel.
I’m sure I’m not the only one in the world who works with MySQL and wants to be able to make things faster and easier. If your server is managed by a control panel like cPanel, perhaps you have literally fallen asleep, like me, clicking links and waiting over and over until you reach your PhpMyAdmin administrator.
All this can be much shorter!! and for this we are going to help you by offering you a tool called HeidiSQL, which is very good to be able to manage the administration of a MySQL database that is on a remote server.
HeidiSQL is a light program for Windows that offers us a friendly interface to administer MySQL, but also Microsoft’s SQL Server management systems. It allows browsing databases and tables, editing any information, creating records, modifying tables, views, procedures, triggers and in general everything that we will need in the day to day administration of databases.
In this article we will tell you more about HeidiSQL, but we will also help you configure remote access to your server, validating the IP from which you connect so that your MySQL allows you access from other networks, without losing security.
But before we get down to it, I want to explain why it’s a good idea to use a system like HeidiSQL to quickly manage a MySQL database.
The tortuous path to PhpMyAdmin
I have the server managed with the cPanel control panel, but I know that this same problem that I am going to report is very similar with other control panels such as Plesk. I am saturated with always doing the same actions to be able to manage the MySQL database and that is why I want to share with you some shortcuts so that you can access the administration of your database more easily and also in a safe way!
The story repeats itself, over and over again, when accessing MySQL from cPanel:
We type our domain.com/cpanel
We wait…
It asks us for the username and password, once entered we wait…
Once inside cPanel we have dozens of options and we lose sight of where PhpMyAdmin is…
The first time, we drag the “Database” box to the first position, so it doesn’t get lost among the multiple options.
We click on PhpMy Admin and wait…
On properly configured servers, PhpMyAdmin can be accessed from cPanel using https and with a port number other than 80, for security reasons. Hence the reason for entering phpMyAdmin from cPanel…
Once the page is loaded
We click on the database to use and wait…
It really is tortuous and cumbersome editing tables and records because it takes another 2-3 more clicks to reach an object in our database.
Let’s count the “Waiting” in this article and multiply by the loading time and except for very fast or local accesses, we have wasted a lot of time if we repeat this work pattern several times a day.
Get to know HeidiSQL
At the beginning of MySQL’s popularization on the web, a fever had begun to build clients for said database manager, which unfortunately were in many cases incompatible with each new version of MySQL.
MySqlFront made its way through the crowd because it is fast, error-free and free. What would be the surprise (a long time ago) when trying to download from the official page and reading that the project had been sold…
Fortunately, the developer had continued the project with another tool that is still free to use and free:
And this wonderful client can eliminate the “waiting” and leave us with the whole issue of editing with two clicks. We don’t need to say how grateful we can be with the use of this tool, which besides being free, allows us to do the same things that we have been doing with other administrators like PhpMyAdmin.
Among other possibilities, HeidiSQL allows us to:
- Connect to multiple servers at once in a single window.
- Connect to MySQL servers by command line.
- Connect with SSH or make SSL connections.
- Edit tables, views, stored procedures, triggers, scheduled events…
- Create SQL reports.
- Export or import data from or to other sources or databases. For example, import data from text files or export data from tables to text files with various formats such as CSV, HTML, XML, SQL, PHP arrays, etc.
- Manage user privileges.
- Write queries with SQL highlighting and code completion and preformat SQL code for better reading.
- Monitor client processes and kill them if we need to.
- Searches for a text, not only in one table, but in multiple ones, in case we don’t know where it was.
- Optimization and repair of tables, etc.
Configure remote access to your database server with cPanel
To use it, the only condition is that your server accepts remote connections to MySQL, which is not available on all Linux servers.
If your server allows these connections, you should find out what your public IP is in pages like and write down what is the IP number that you have in Internet access.
Note: I must be emphatic on the matter because many times the IP address of our computer is not the public address assigned by our Internet provider, but a local IP when we are connected to a router, etc. On the other hand, it should also be said that many times our IP changes over time, which is known as dynamic IP. In these cases we can try to talk to our provider so that they provide us with a static IP, which is always the same, which will save us from having to repeatedly configure the IP from which access to our server is allowed.
We go to our cpanel: In our database box we click on Remote Mysql.
On the next page we must enter our public IP address:
We are going to use the regular user for our connection to MySQL. In some cases it is necessary to re-assign permissions after entering an IP in the Remote access option.
And once all these steps have been completed, we will proceed to install HeidiSQL.
When the program starts, it will show us a connection form and we must configure our connection.
It is as simple as writing the Hostname, User, Password (in some cases we must write the name of the database).
Note: I must point out that the repeated use of “in some cases” is because after experimenting with many hosting companies with shared and dedicated servers I have noticed that there are configuration differences depending on the version of Linux, cpanel and others that drive us to Try before giving up if we can’t connect to MySQL remotely.
For example, some Hosting assign you another name to access MySQL.
Some of us must add the combination of access username plus database user.
Example “username_usernamemysq”
When creating the user, cPanel tells us the exact name anyway.
With an intuitive interface, HeidiSQL allows us to quickly modify the properties of a table or field database or the content of a record.
As we are editing the database directly, in some cases (if we navigate through the content of a record), changing the focus for example, the content is updated automatically.
So we must be careful because there is no “Undo” or Undo.
I hope you can take advantage of this tool and reduce editing time to your MySQL database.
Facebook Comments
Disqus