GUI and Command Line Tools for MySQL Users

The MySQL server distribution doesn’t include tools for many common tasks, such as monitoring the server or comparing data between servers. Fortunately, Oracle’s commercial offerings extend these tools, and MySQL’s active open source community and third-party companies also provide a wide variety of tools, reducing the need to roll your own.

Also Read: [Top 60] List of MySQL Repositories, GUI, Backup and Analysis Tools

GUI Tools

Interface tools help you run queries, create tables and users, and perform other routine tasks. This section gives a brief description of some of the most popular tools for these purposes.

You can generally do all or most of the jobs they’re used for with SQL queries or commands—the tools we discuss here just add convenience, help you avoid mistakes, and speed up your work:

1. MySQL Workbench

MySQL Workbench is an all-in-one tool for tasks such as managing your server, writing queries, developing stored procedures, and working with schema diagrams. It features a plugin interface that lets you write your own tools and integrate them into the workbench, and there are Python scripts and libraries that use this plugin interface.

MySQL Workbench is available in both community and commercial editions, with the commercial editions adding in some more advanced features. The free version is more than adequate for most needs, though. You can learn more at https://www.mysql.com/products/workbench/.

2. SQLyog

SQLyog is one of the most popular visual tools for MySQL, with many nice features. It’s in the same class as MySQL Workbench, but both tools have some checkboxes in their feature matrices that the other doesn’t have. It is available only for Microsoft Windows, in a full-featured edition for a price and in a limited-functionality edition for free. More information about SQLyog is available at https://webyog.com/.

3. phpMyAdmin

phpMyAdmin is a popular administration tool that runs on a web server and gives you a browser-based interface to your MySQL servers. Although browser-based access is nice sometimes, phpMyAdmin is a large and complex tool, and it has been accused of having a lot of security problems. Be extremely careful with it. We recommend not installing it anywhere that’s accessible from the Internet. More information is available at https://sourceforge.net/projects/phpmyadmin/.

4. Adminer

Adminer is a lightweight, secure browser-based administration tool that’s in the same category as phpMyAdmin. The developer positions it as a better replacement for phpMyAdmin. Although it does seem to be more secure, we still recommend being cautious about installing it in any publicly accessible place. More information is available at https://www.adminer.org/.

Command-Line Utilities

MySQL comes with some command-line utilities, such as mysqladmin and mysqlcheck. These are listed and documented in the MySQL manual. The MySQL community has also created a wide range of high-quality toolkits with good documentation to supplement these utilities:

1. Percona Toolkit

Percona Toolkit is the must-have toolkit for MySQL administrators. It is the successor to Baron’s earlier toolkits, Maatkit and Aspersa, which many people regarded as mandatory for anyone running a serious MySQL deployment. It includes many tools for purposes such as log analysis, replication integrity checking, data synchronization, schema and indexing analysis, query advice, and data archiving.

If you’re just getting started with MySQL, we suggest that you learn these essential tools first: pt-mysql-summary, pt-table-checksum, pt-table-sync, and pt-querydigest. More information is available at https://www.percona.com/software.

2. Maatkit and Aspersa

These two toolkits have been around since 2006 in one form or another, and both came to be widely regarded as essential for MySQL users. They have now been merged into Percona Toolkit.

3. The openark kit

Shlomi Noach’s openark kit (https://code.google.com/archive/p/openarkkit/) contains Python scripts that you can use for a wide variety of administrative tasks.

4. MySQL Workbench utilities

Some of the MySQL Workbench utilities are usable as standalone Python scripts. They are available from https://launchpad.net/mysql-utilities.

In addition to these tools, there are a variety of others that are less formally packaged and maintained. Many of the prominent MySQL community members have contributed tools at one time or another, mostly hosted on their own websites. You can find a great deal of information by watching the Planet MySQL blog aggregator over time (https://planet.mysql.com/), but unfortunately there is no single central directory for these tools.

You may also like: