Most small to medium-sized websites and web applications use MySQL as the database of choice for back-end storage. In fact, many of the world’s largest and fastest-growing organizations, including Facebook, Google, and Adobe rely on the stability and maturity of MySQL to power their high-volume websites, business-critical systems and packaged Software as a Service (SaaS) platforms. MySQL is a great choice for a number of reasons, primary among them are its high-availability features, reliable scalability, cost-effectiveness, and the huge community of active developers.
At RunCloud, we use MariaDB for your database. It is an enhanced drop-in replacement for MySQL, made by the original developers of MySQL with the promise of maintaining ongoing and future compatibility with MySQL. This means every tool created for MySQL is also compatible with MariaDB, and this solution is future-proof.
Some developers or database administrators do not need any tools when it comes to managing the MySQL database. However, for users with limited knowledge, or those who just want to perform some quick administration tasks on the database, a database administration tool can be of great help and a major time-saver. There are a lot of choices when it comes to managing cloud-hosted MySQL and MariaDB databases.
A quick Google search shows that phpMyAdmin is one of the most popular choices and easily recommended. But did you know that there are other FREE RunCloud-compatible database managers that are worth looking into?
phpMyAdmin
First, only a little introduction to PHPMyAdmin is needed as it is one of the most famous database management tools around. Launched in 1998, phpMyAdmin was written primarily in PHP. It is a free and open-source administration tool for MySQL and MariaDB. Over the years, its popularity has grown, especially for web hosting services, due to its portability, easily accessible web interface, ease of use (even for someone with minimal skills) and functionality. This has led to it becoming the tool of choice provided by most shared hosting companies in their control panel for managing MySQL or MariaDB databases.
Another contributing factor to its success is that, unlike other database manager tools, phpMyAdmin has live charts to monitor MySQL server activity such as connections, processes, CPU/memory usage, etc. It also includes a debugger and can generate Entity Relationship Diagram (ERD) in PDF format so large databases can be looked at as a whole, unfortunately, on the downside, it does not have syntax highlighting or code completion, but in general, it has an impressive feature set.
phpMyAdmin monitoring dashboard
Since phpMyAdmin is a public-facing Web Application and is only accessible using a web browser, some developers have reservations about using it, considering it a potential security vulnerability.
Previously RunCloud did not recommend using phpMyAdmin for database management due to these potential security risks, but after receiving many requests from users who insisted on using it, the developers at RunCloud included it in the script installer. At RunCloud, we listen to our clients.
In the RunCloud dashboard, users can install phpMyAdmin using One Click phpMyAdmin install in less than 5 minutes. Instead of accessing the database from a web interface through an open port, it is recommended to create another web app within the RunCloud dashboard and install phpMyAdmin in that web app and enable SSL there so that the connection is encrypted and secured.
HeidiSQL
HeidiSQL is a Windows-based platform launched in 2002. Unlike phpMyAdmin, HeidiSQL users connect to MySQL or MariaDB database via SSH tunnel. There is no need to establish a separate SSH tunnel connection using Putty or Terminal. So all communications to and from are encrypted. Among database managers, HeidiSQL’s interface is considered very pleasant not to mention comprehensive and intuitive.
Furthermore, there is also a portable version that does not require administrative privileges and installation.
Although it has no built-in debugger, it does have syntax checks and auto-completion features, alongside powerful user management features where the administrator has granular control to easily set privileges on a per-database level or give access to only certain commands.
HeidiSQL’s comprehensive and intuitive interface
Built-in tunnelling option in HeidiSQL using plink.exe
MySQL Workbench
Any MySQL database management tool list would be incomplete without mentioning the software developed by the company which owns MySQL, Oracle Corporation. MySQL Workbench was released in 2005 and replaced a previous software package called MySQL GUI Tools Bundle. This is the only SQL client which is supported and developed by MySQL, so you can be sure that it always contains all the functions to match the most recent updates for MySQL. It is also a cross-platform software that can be used on Windows, Linux, and Mac OS. So the experience is consistent from one OS to another.
In a nutshell, MySQL Workbench is more of a unified visual tool for database architects, developers, and database administrators. It has enhanced entity-relationship modelling support and it is one of the few software packages that can do both forward engineering (updating the database schema with changes made to its entities and relationships via the ERD visual designer) and reverse engineering (producing an ERD from a database).
Extensive ERD in MySQL Workbench
However, it can be resource-intensive and some say it is overkill when all you need is to perform simple tasks. And as everyone knows, if the software is resource-intensive, it can tend to crash more often.
Due to the many features it provides, some regard the interface as cluttered and unintuitive. Hence, this tool is not recommended for beginners. So, unless the user is an advanced database administrator and there is ample client and server resources, it is not recommended. If, however, its needs are met, then MySQL Workbench can be a powerful tool to have since nearly every SQL command can be utilized in a GUI.
Sequel Pro
Among users who are on Mac OS machines, Sequel Pro is a top choice due to its comprehensive features, where the GUI provides many powerful functions instead of having to keep resorting to terminal commands. Sequel Pro was built specifically for Mac OS machines to manage MySQL / MariaDB. With Sequel Pro, users can easily create and edit databases, manage users and easily do a full database export for backup (where multiple formats are supported like SQL, CSV and XML). Even imported CSV files can be easily converted to tables.
Sequel Pro’s connection interface
Sequel Pro allows users to connect to their MySQL/MariaDB through standard, socket or SSH, either locally or remotely. It even allows users to group and colour code the servers into staging or production sites, just like how a Mac user can colour code the files in the Finder app on a Mac machine.
For a user who manages multiple projects at one time, Sequel Pro is practical as it allows multiple open connections to different databases. The connections remain connected until the user decides to disconnect them. The user management feature also allows for user creation with user privilege and connection management.
Since it does not have a visual query editor, running a query requires a little bit of MySQL syntax knowledge. Fortunately, it does have highly flexible and customisable filters that can be applied on a table. And these filters can be used across different tables with ease. The queries can be saved and there is a history for queries that have been run, but the results of the queries themselves are not saved.
All in all, this tool is very powerful when it comes to digging for information from the database as complex queries and filters can be done through the control panel instead of relying on terminal commands.
Adminer
Last but not least, we have Adminer. Previously known as phpMinAdmin, it was initially released as a light alternative to phpMyAdmin. It is famously known for being lightweight and the installer is just a single file. It is good to go once it is uploaded.
Being lightweight means that it had to forgo some features like auto-completion and a debugger but it does have syntax highlighting as well as ERD design, which is the ability to draw entity-relationship diagrams for the database.
The Adminer interface is more functional rather than being focused on aesthetics, but nevertheless, it is fast and easy to use.
Adminer’s lightweight and simple interface
ERD visualisation in Adminer
Conclusion
So there you go, the recommended tools to administer your MariaDB in RunCloud (or any other cloud service provider). The best practice would be to not use a public-facing web tool when managing your server especially the database which has sensitive data. Rather, use a tool that can support SSH connection so that the communication between you and your servers are encrypted and less prone to be intercepted by irresponsible parties.
Not including Sequel Pro is a mistake – I imagine it is the only tool that any of your Mac Users use.
