8 MySQL Tools That Every Admin Must Have

MySQL is the world's most extensively used open source RDBMS (relational database management system)- it is used by the giants in the technological world like Google, Facebook, Twitter, Flickr and Youtube, as well as by various applications like Joomla, Wordpress and Drupal. Many developers have developed excellent open source tools to manage your website or application that runs on a MySQL database. These tools are essential in properly managing, repairing, diagnosing and optimizing your MySQL website. Here is a list of the top 8 MySQL tools that you as an admin, must make use of -

1. Tcprstat  is a TCP analysis tool, which monitors TCP requests - it is a free and open source tool. It also gives statistics about low-level response times, after calculating the delay between request and response. This tool is essential in evaluating the traffic in a network. The basic aim is to reduce the abovementioned period so that more number of requests can be entertained in a shorter time period - think along the lines of how many more requests you could serve in a full working day if only a quarter of the response time were to be reduced. Tcprstat is similar to Unix's iostat, mpstat and vmstat.
2. Mydumper - If you are getting a slow job out of mysqldump, then maybe it is time to jump ship to mydumper, which will help you create data dumps quickly. To create backups so as not to spend hundreds of dollars to a data recovery agency, mydumper does a fast job of it using multiple threads, unlike mysqldumper which uses a single thread. Though mydumper is a free tool that also helps you during server cloning, it locks tables, thus rendering it problematic to be used during operational hours. Through mydumper you can create a quick clone of a website for test-runs.
3. Shard-query - If you have a handful of queries against partitioned data sets, shard-query will help you parallel-process them - it enhances performance and offers transparent sharding. Sub-queries in the FROM clauses, UNION, UNION ALL, IN and BETWEEN queries can benefit from the intra-query parallelism offered by shard-query. Shard-Query reduces the time required to process sharded queries by nearly 85%, i.e. from 21 seconds to 3! However, shard-query cannot work alone, it needs programs like Gearman. Apart from the queries of the abovementioned constructs, aggregate functions SUM, COUNT, MAX and MIN can also be used.
4. Oak security audit -Though agencies offer services that audit your security, you should set up a regular checking process yourself, and that is where oak security audit comes in. This free open source tool can be used to guard yourself against both external and internal threats in the form of accidental logins, violation of confidential information like medical reports, etc. It is a simple enough program that requires no installation.
5. Mycheckpoint - They say prevention is better than cure - lightweight, SQL-oriented mycheckpoint acts as your prevention before something goes wrong with your servers, You certainly don't need to wait till the last moment for something big to blow up in your face - this simple SQL monitoring tool will help you find that problem before it finds you. Though there are other similar MySQL-specific monitoring tools, mycheckpoint is free and open source. This Web-based tool records MySQL status and variables and can either generate human-readable reports or give out charts reviewing InnoDB buffer pool flushes, swap activity, temporary tables created, OS load, memory usage etc. It can also act as a web server and provide live HTML reports.
6. MySQL Workbench - This visual database tool is free and available for Windows, Mac OS X and Linux. It provides data modeling, SQL development and comprehensive administration tools which help you with server configuration, user administration, backup, etc. Workbench uses a DBA, developer or data architect to visually design, create and manage databases. A visual console is provided for easily administering MySQL environments and for better visibility. If you want migrate other RDBMS tables, objects and data to MySQL, Workbench offers assistance in that as well. To improve the performance of your MySQL-powered application, there is a visual performance dashboard.
7. DBVisualizer - A Java-based database tool for admins, DBVisualizer helps to manage and develop servers easily. It can be used on all major operating systems like Windows, Linux and Mac OS X. And it is a free database tool. It has many great features, like database browser which uses tree based navigation system to search through database objects and multiple objects can be displayed side by side. There are visual tools to manage database objects; edit compile and run procedures, functions, triggers, etc. It also makes developing database queries extremely easy through a point and click interface. You can also export objects and data as CREATE/INSERT statements. Table data management tools include spreadsheet-like table-data editor including binary/BLOB and CLOB data types.
8. Sequel Pro - Though this is restricted to Mac users, it is a great MySQL database management tool that is free. It offers direct access to your MySQL databases on both local as well as remote servers. It also supports Gatekeeper and Full Screen modes. It has a powerful query editor, with built-in popular text editor features, automatic syntax highlighting for your custom queries, custom query placeholders, query formatter for beautifying your queries, etc. With this management tool you can create backup with SQL/CSV files, save your connection and share it. It also has full MySQL support - monitor server processes, table triggers, full printing support, full table management including indexes, easy user management, etc.

Author: Rahul Sharma

Image Courtesy: MySQL.com

By Team FileCloud