MySQL - MySQL Administrator

Using MySQL Administrator to monitor server health:

Connection Usage displays the percentage of maximum allowed number of connections that are in use. You can use this to see whether the client connection load is approaching the limit imposed by max_connections system variable. If the usage is consistently near the limit, consider increasing the value of this variable.

Traffic displays a graph showing the number of bytes sent to clients in each measurement interval.

Number of SQL Queries displays a graph showing how many queries the server received in each measurement interval.

Query Cache Hitrate show the effectiveness of the query cache.

Key Efficiency show the effectiveness of the MyISAM key cache.

We can create our own graphs. Server monitoring graphs are based on formulas that can refer to status and system variables, so we can display whatever information we are interested in. Formulas can use the cumulative values of variables or the change in value relative to previous measurement interval. For each graph, you can select characteristics such as graph type (line or bar), captions, minimum and maximum of the graph range. Graphs can be organized into pages and groups.

The first step in creating custom health graphs is to create a new page in the Health section to store our graphs. To create a new page right click anywhere in the working area and choose the "Add a Page" option. We will be prompted for a page name and description and a new page will be created.

Once we have created a new page, we will need to create a group. All graphs are organized into groups. For example, the Key buffer usage and Key buffer hit rate graphs are both members of the group titled Key Efficiency. To create a group, right click within your newly created page and choose the Add a Group option.

To create a custom graph, right click within a group and choose the "Add a graph" option.

The Line-Graph is appropriate for showing trends over time, while the Bar-Graph option will be more appropriate for showing percentage information. If you choose to create a bar-graph, you will need to choose a caption for your graph. Captions are not allowed for line-graphs.

The data on your graph is set using the Value Formula. You can create a formula using any of the variables available in the Status Variables and System Variables tabs. To use the value of a variable, wrap the variable name in square brackets (for example, [com_select]), if you want the relative value of the variable prepend the square brackets with a ^ character.

For example, if we wanted to graph the percentage of temporary tables that were created on disk we could use the following formula:

 [created_tmp_disk_tables] / [created_tmp_tables]

If we wanted to track the number of temporary tables created on a continuous basis, we could use the following as our formula:

^[created_tmp_tables]

After creating your formula, select the Value Unit that best represents your data. You can choose from Percentage, Count, Byte, and Seconds. In our examples we would use Percentage for the first example, and Count for the second. You can also assign a caption to the value.

Once your formula is assigned you should configure the Max. Value and Min. Value for the graph, so that your data is spread evenly across your graph. You can set arbitrary values based on your estimates of how large the values will grow, and check the Autoextend Max. Value option to allow MySQL Administrator to automatically increase the Max Value setting automatically to prevent your data from extending off of your graph.

You can also set the Max. Value option by way of a formula assigned in the Max Formula field. The same syntax applies in this field as applies in the Value Formula field. For example, if you were creating a bar graph tracking the number of temporary disk tables created, you could use [created_tmp_disk_tables] as the Value Formula and [created_tmp_tables] as the Max. Formula.

See Creating Custom Health Graphs

MySQL Administrator is a graphical client designed specifically for administrative operations. The tasks that MySQL Administrator enables you to perform:

  1. Monitor server performance, load, and memory usage information.
  2. Display and set server configuration information.
  3. Start and stop servers.
  4. Display and set server configuration information.
  5. Monitor server status and performance
  6. Set up user accounts, grant and revoke privileges, and set passwords
  7. Display information about client connections or kill connections.
  8. Create and drop databases.
  9. Check, repair, and optimize tables.
  10. Perform backups and restores data from backups.
  11. Monitor replication servers.
  12. Display contents of general query, slow query, and error log files.
  13. Perform database backup and recovery operations.
  14. Create or drop databases and tables, and modify the structure of existing tables.

Some of these capabilities are available for local or remote servers. Others, such as configuring the server or displaying its logs, are available for local servers only.

MySQL Administrator is not included with MySQL distribution. It is available in pre-compiled form for Windows, Linux, and Mac OS X, and can be compiled from source.

MySQL Administrator requires a graphical environment such as Windows or X Windows. If the server is running on a host with no graphical environment, you can connect to it remotely by running MySQL Administrator on a client host that does have a graphical environment. Some functionality is available only when MySQL Administrator and the server are run on the same host.

If the server is configured to run as a Windows service, and you run MySQL Administrator on the same machine, you can use MySQL Administrator to start or stop the MySQL service.

None of the client program can shut down the server except for mysqladmin and MySQL Administrator. mysqladmin shuts down the server by using a special non-SQL capability of the client/server protocol. If you use an account that has the SHUTDOWN privilege, it can shutdown local or remote server. MySQL Administrator can shutdown a local server on Windows if the server is configured to run as a Windows service.

MySQL Administrator supports multiple server connections and opens a separate window for each connection.

On Windows, MySQL Administrator also include a Windows System Tray monitor that provides quick access to server status information from the tray.

On Linux, MySQL Administrator is designed for Gnome, but can be run under KDE if GTK2 is installed.

MySQL Administrator is located in "C:\Program Files\MySQL\MySQL Administrator 1.0" and is named MySQLAdministrator.exe

RPM installation on Linux place MySQL Administrator in /usr/bin, and name it mysql-administrator.

Mac OS X distribution of MySQL Administrator is a disk image that, when mounted, contains a MySQL Administrator program that can be dragged to wherever you want to install it. To launch the program, double click it in the Finder.

To connect to a server, fill in the required connection parameters or select among the connection profiles. MySQL Administrator connect to the server, enter normal mode, and display its main window, which provide access to the various administrative capabilities.

To enter configure-service mode, hold down the CTRL key to cause the Cancel button in the dialog to change to Skip, and then click on this button. MySQL Administrator will display its main window, but only the Service Control, Startup Variables, and Server Logs sections are available.

The main window has a sidebar along the left edge that display the available sections from which you can select, and a work area to the right of the sidebar.

Selecting a section in the sidebar causes the work area to display an interface for that section.

The work area for some sections has multiple tabs.

The main window also contains several menus from which you can access additional features.

To open additional server connections in normal mode, select New Instance Connection … from the File menu.

Several sections of MySQL Administrator main window are devoted exclusively or primarily to monitoring aspects of server operation:

Server Information provides an overview of server status, information about your connection to the server(server host, and version of the server, the client host, and client version).

Server Connections display information about clients connected to the server. Connections are also known as threads, which is the term used by MySQL Administrator. The Server Connections section displays thread information in two formats. One format lists threads by thread ID. The other format lists them grouped by user, which makes it easier to see what a given user is doing when that user has multiple connections open. In either display format, clicking a column heading re-sorts threads information rows by that column. Clicking a thread line selects it and enable the Kill Thread button that you can use to terminate the connection. Clicking a user line selects all threads for that user and enables a Kill User button that you can use to terminate all connections for that user. In the Server Connections section, the privileges that you have determine what information you can see and the connections that you can terminate. If you have the PROCESS privilege, you can see all threads. Otherwise you can see only your own threads. If you have the SUPER privilege, you can terminate all threads. Otherwise, you can terminate only your own threads.

Health displays performance, load, and memory usage in graphical form. It display a predefined set of graphs by default, but is configurable and allows you to define your own graphs for monitoring the server. The following status-monitoring graphical displays are predefined: Connection Usage displays the percentage of the maximum allowed number of connections that are in use. You can use this to see whether the client connection load is approaching the limit imposed by the max_connections system variable. Traffic displays a graph showing the number of bytes sent to clients in each measurement interval. Number of SQL Queries displays a graph showing how many queries the server receives in each measurement interval. Query Cache Hitrate and Key Efficiency show the effectiveness of the query cache and the MyISAM key cache. You can create your own graphs. Server monitoring graphs are based on formulas that can refer to status and system variables, so you can display whatever information you're interested in monitoring. Formulas can use the cumulative values of variables or the change in the value relative to the previous measurement interval. For each graph, you can select characteristics such as graph type (line or bar), captions, and the minimum and maximum of the graph range. Graphs can be organized into pages and groups.

The Health section also displays server status and system variables. These variables are displayed in hierarchical category / subcategory fashion to make it easy to examine related variables together. Categories can be expanded or collapsed to display more or less information.

Health also allows you to examine status variables and to examine and set system variables. System variables that are dynamic and can be set at runtime are so marked with a distinctive icon. Double-clicking a settable variable brings up a dialog for changing the value. Changes made this way only persist until the server stops.

Server Logs display the contents of the error log, slow query log, and general query log. This capability is available for local servers only. This section has a tab for each kind of log file. Each tab contains controls that make it easy to move through the log. A page control selects "pages" of the log and two panels display summary and expanded views of the current page. To make it easier to see how the two views match up, clicking a summary line causes the corresponding expanded view lines to be highlighted. There is a button to bring up a dialog for opening other log files. You can search for a given string within a log. The current page of a log can be saved to a file.

Replication Status helps you monitor replication. It provides an overview of your replication setup, if the server to which you are connected to is acting as a master server. You can see the master's current replication status, and information about each slave.

Catalogs displays information about databases, tables, columns, and indexes. It also provides access to the MySQL Table Editor and can perform table maintenance operations. If you right-click in the database browser, you can create and drop databases. If you right-click a table name and select Edit Table Data, MySQL Administrator launches MySQL Query Browser so that you can edit the table's contents. You can perform optimization, checking, and repair table maintenance operations.

Several sections of MySQL Administrator main window are devoted to server configuration.

There is also an Options … item in the Tools menu that bring up the Options dialog. This dialog enables you to change general program settings, manage connection profiles, and set MySQL Table Editor preferences.

The sections in the Option dialog:

  1. The Browser section appears only when you are running MySQL Query Browser. It allows you to set options that affect MySQL Query Browser general defaults.
  2. The Administrator section appears only when you are running MySQL Administrator. It allows you to set options that affect MySQL Administrator general defaults.
  3. The General Options section customizes program behavior. It controls setting such as font, language selection, and whether to save passwords in connection profiles. Passwords can be saved as plain text or in obscured format. The later is weak encryption that is unsophisticated and will not defeat a determined attack, but does make stored passwords not directly visible via simple inspection.
  4. The Connections section allows you to create, edit, and delete connection profiles. It also has a browser that provides information about recent connections.
  5. The Editors section configures defaults for MySQL Table Editor for creating tables, such as the default storage engine and data type, whether to define column as NOT NULL by default, and whether integer columns should be UNSIGNED by default. It also gives you control over conventions used when naming indexes and foreign keys.

In configure-service mode, the main window displays only the Service Control, Startup Variables, and Server Logs sections. In normal modes, those sections are displayed along with all other sections.

In configure-service mode, MySQL Administrator displays a panel in the main window that list all available MySQL services. You can select any of them to indicate which service to configure. In normal mode, you can configure only the first MySQL service and the service-list panel is not displayed.

The capabilities provided by the Service Control section currently only work for local servers, and those provided by the Configure Service tab are designed to control servers that have been installed to run as Windows services.

Service Control section has two tabs. The Start/Stop Service tab display status information about the service, such as the service name and whether the server is running. A button enables you to start or stop the service and a display area show status messages resulting from such actions. The Configure Service tab is displayed only on Windows. It allows you to change the configuration of a MySQL Windows service. Changes to most of these settings require a server restart to take effect.

Startup Variables section presents an interface to the settings present in the server's option file. This capability is available only for local servers. If the file is writable, you can change the settings. The server must be restarted before the changes take effect. There are many configuration options, so this section has several tabs.

User Administration section enables you to manage user accounts. You can create new accounts, edit accounts, or delete accounts. An account browser displays a list of existing accounts. Selecting an account allows you to examine and modify its characteristics, such as username, the password, the privileges held by that account, and its resource limits. Privileges can be granted at the global, database, table, and column levels. Accounts are defined by a combination of username and hostname, which means that you can have different accounts that have the same username. The account browser in MySQL Administrator provides a hierarchical view that groups accounts by username. A Clone User feature enables you to create one user from another, which is an easy way to set up new users that differs only slightly from existing users. User Administration section has a User Information tab that enables you to associate descriptive information with each user, such as real name, telephone number, email address and icon. MySQL Administrator creates a user_info table in the mysql database to store this information.

Backup section provides interface for making backups. MySQL Administrator creates backup files that contain SQL statements such as CREATE TABLE and INSERT that can be reloaded. These files are similar to the SQL-format backup files generated by mysqldump. Backups are based on projects. A project is named set of specifications that you can execute to perform a backup based on those specifications. Projects can be browsed and selecting a project allows you to examine and modify its specifications. The project approach enables you to easily select from among multiple types of backups. A database browser allows you to specify which databases to use for a project. By default, all tables in a selected database are selected for backup, but you can include or exclude individual tables. Backup projects include a name for the output file. By default, the same name is used every time you execute the project, which cause the file to be overwritten each time. To prevent this, there is an option for adding the date and time to the end of backup files. The option to enable date-tagging is accessed from the Administrator section of the Options dialog. You can control several aspects of backup operation, such as whether to use locking that is more appropriate for MyISAM or InnoDB tables, and the style to use for INSERT statements (single-row vs multi-row, ANSI-style identifier quoting, etc). Projects can be executed on demand or scheduled for periodic execution at daily, weekly, or monthly intervals. For weekly backups, you can select which day or days of the week on which to execute the project. A monthly backup can be executed on any single day of the month.

The Restore section provides interface for data-recovery. MySQL Administrator can reload SQL-format dump files. The destination where tables are created can be chosen as each table's original database, or you can restore all tables to an existing database or to a new database. To enable you to restore only part of a dump file, MySQL Administrator provides a dump file analysis feature: Select a dump file, and MySQL Administrator reads it to determine what tables it will restore and presents a dialog showing you what the tables are. You can selectively include or exclude each table to control which ones to reload. This is useful when you want to restore only certain tables from a full-database dump.

On Windows, MySQL Administrator distributions include a Windows System Tray monitor. This monitor is designed to provide quick access to server status information and program-launching capability. When the Tray Monitor is running, its icon in the tray indicates whether the server is running or stopped. Right-clicking the icon displays a pop-up menu with items that list the status of all installed MySQL Windows services. You can start of stop each service. The menu also provides items that launch MySQL Administrator in configure-service or normal mode, or launch MySQL Query Browser.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License