View Information

It is generally hard to administer a database without the ability to view different bits of information about the server and the databases that are hosted on that server.

We will explore various options to be able to see information about MySQL server and components.

Note: This information covered in this lesson is more from Administrative perspective, versus that covered in other lessons, which relate more to development around MySQL.

Using MySQL show

With mysqlshow, you can quickly obtain an overview of the databases, tables, and columns managed by MySQL. Without parameters, the command returns a list of all databases managed by MySQL. With parameters, the command displays information on the specified database, table, or column.

mysqlshow - Syntax
mysqlshow [options] [databasename [tablename [columnname]]]
mysqlshow - Options

mysqlshow -u root -p
mysqlshow -u root -p mysql
mysqlshow -u sakilaadmin -psakila sakila customer

1. Displays list of databases.
2. Second command displays list of tables from sakila database.
3. Fourth command shows customer table structure from sakila database.

mysqlshow -usakilaadmin -psakila -i --status sakila

Displays additional status for tables - such as table type, auto-increment, create time, average row length.

The following example shows variables in a mysql session.

-- ShowSystemVars.sql

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

Show Global and Session variables.

The following example shows the setting of some variables and limitations.

-- SetVariables.sql: Modify system variables

SET GLOBAL QUERY_CACHE_LIMIT=1000000;
SET WAIT_TIMEOUT=600;
SELECT @@GLOBAL.WAIT_TIMEOUT, @@WAIT_TIMEOUT, @@GLOBAL.QUERY_CACHE_LIMIT;

-- Does not work, QUERY_CACHE_LIMIT is a GLOBAL variable only
-- SET QUERY_CACHE_LIMIT=500000;
-- SELECT @@QUERY_CACHE_LIMIT;

Set various variables.

Using MySQLadmin Tools

Various administrative tasks can be accomplished with mysqladmin tool, such as creating new databases and changing passwords. There are several commands that can be passed to mysqladmin tool, which are then executed sequentially.

mysqladmin [options] command1 command2 ...

• Most mysqladmin tool commands can also be executed as SQL commands, for example, by CREATE DATABASE, DROP DATABASE, FLUSH, KILL, SHOW:
• Tip: The names of mysqladmin commands can be abbreviated to the point where the name remains unique (e.g., flush-l instead of flush-logs, or k instead of kill).
• Many of the program options provide both the long and the short forms. For example, the --force option can also be specified as the -f option.
• When multiple values are supplied for an option, they're separated by a vertical pipe [|].
• You may also specify more than one command as needed.
• Note : Program options can vary from one release of MySQL to the next, so always check the applicable MySQL product documentation for best information about options available to mysqladmin.

mysqladmin --help

mysqladmin  Ver 8.40 Distrib 4.1.5-gamma, for Win95/Win98 on i32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name
                      Directory where character sets are.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -p, --password[=name]

Databases

View Server Status

mysqladmin -uroot -p291069kg --sleep=1800 variables

Shows variables every 30 minutes.

mysqladmin -uroot -p status

Output of status command is shown below with explanation:

Uptime: 6269  Threads: 4  Questions: 165  Slow queries: 0  Opens: 46
  Flush tables: 1  Open tables: 0  Queries per second avg: 0.026

The status-related information, which is described in the following table.

mysqladmin -uroot -p291069kg --sleep=30 --relative extended-status

Repeats extended status command every 30 seconds.

Shows only changes in status, relative to previous run.

Flush Caches and Settings

View and Kill Threads

Using mysqladmin command, we can view the list of processes that are running under MySQL. It is also possible to kill a given thread, say with hung or a long-running query.

mysqladmin processlist

mysqladmin -uroot -p291069kg processlist

Display list of processes running with information.

mysqladmin <auth> kill process-id, process-id-2 ...

show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    nn

Here is another example of PROCESSLIST output, with meaning of the columns:

SHOW FULL PROCESSLIST;

Display list of processes running inside of a mysql session.

Server Management

MySQL Administrator User Interface

MySQL Administrator tools product helps with administrative tasks and management of a MySQL server (loading, determining the number of open connections, determining the status, etc.) as well as carrying out a number of management tasks. The program can be used for administration both on a local server and over a network.

Many functions of the program are limited to:

• Administration of local servers
• Users with sufficient access privileges (root)
• OS users with sufficient operating system access privileges

For example, you can launch or halt a local MySQL server only if you are logged in as root (Unix/Linux) or system administrator (Windows).

The large number of functions of MySQL Administrator are dispersed among several modules. These modules are selected via a sidebar, and then make their functions available in one or more dialog sheets. These modules will be briefly introduced in the following sections.

Server Information

This module collects status information on connections to the MySQL server, its version, and the client library of MySQL Administrator.

Service Control

With this module you can start and stop the MySQL server. This works only for local MySQL servers and not for MySQL servers on remote computers. MySQL Administrator must be launched by someone with system administrator privileges.

In the Windows version of MySQL Administrator you can set a number of options in the dialog tab CONFIGURE SERVICE.

Startup Variables

This module enables, in a well-divided set of input tabs, a visual editing of the MySQL configuration file. Changes to the file take effect only after a restart of the MySQL server.

If the file does not yet exist, it will be automatically created after an update on these screens. MySQL Administrator needs to have write privileges to the file.

MySQL Administrator tools displays a small button before each option that tells whether the option is active (red X symbol) as Inactive options are not stored in the configuration file.

User Administration

This module helps in setting up new MySQL users and in changing the access privileges of existing users. This part of MySQL Administrator assumes that you understand the MySQL security system.

You can set up a NEW USER specifying a user name and password, where % is used as the host name. This allows the user to access MySQL from every location in the network.

To limit access to certain hosts, click with the right mouse button on the user name, execute ADD HOST, and provide the desired host name (e.g., localhost). Then click on the user name again with the right mouse button and execute REMOVE HOST to delete the wildcard host name %.

Setting Privileges

MySQL distinguishes between global privileges for all databases, and object privileges for select databases, tables, and columns. By default, MySQL Administrator shows object privileges only at the database level on dialog tab SCHEMA PRIVILEGES.

To see global privileges as well as object privileges at the table and column level, set the corresponding options in the configuration dialog (TOOLS | OPTIONS | ADMINISTRATOR under Windows or FILE | PREFERENCES | ADMINISTRATOR under Linux).

To assign or deny privileges to a user, select the desired object in the relevant dialog tab (e.g., sakila database), then move the desired privileges between columns AVAILABLE PRIVILEGES and ASSIGNED PRIVILEGES.

Server Connections

This module consists of two dialog tabs:

• THREADS shows a list of all active MySQL threads (corresponds to the SQL command SHOW PROCESSLIST).
• USER CONNECTION contains a list of all currently active MySQL users together with their threads and the list is grouped by user name. You may indeed find users with a large number of open connections.

Health

This module gives an overview of the load and usage of the MySQL server in four dialog tabs .

• CONNECTION HEALTH uses three charts the rate of the number of active connections, the amount of network traffic, and the number of SQL queries per second. The charts are useful when the MySQL server is under load.
• MEMORY HEALTH shows the use of two important memory locations: Query Cache and Key Buffer).
• STATUS VARIABLES and SERVER VARIABLES use tables to manage countless additional status values.

Tip: You can customize the graphical display by adding new graphs and even new dialog tabs. The Windows version of MySQL Administrator allows the customization of existing graphs and is more convenient to use than the Linux version.

To design custom graphs you can use all the variables from the dialog tabs STATUS VARIABLES and SERVER VARIABLES, using variable names in brackets. If you prefix the optional caret character (^) say in control charts, then instead of the absolute value of the variables, you get relative values since the previous refreshing of the graphs. For example, the formula for the Query Cache Hitrate looks like this:

(^[Qcache_hits] /
 (^[Qcache_hits] + ^[QCache_inserts] + ^[QCache_not_cached])) * 100

In this way, the relation between the number of cache hits and that of all cache inserts is calculated as a percentage (value between 0 and 100).

Server Logs

With this module you can conveniently read the logging data of the MySQL server. However, this functions only when the various logging functions have been activated. The relevant logging options can be changed in the dialog tab LOG FILES of the module STARTUP PARAMETERS.

Backup

With the backup module you can make backups of your databases. The setup process for a backup is somewhat involved, so it is best suited for defining regular, periodic backups.

Create a new backup project with a name using NEW PROJECT. The project contains all the settings for the backup: which databases are to be backed up, which options hold for the backup, if and when the backup is to be regularly executed.

Setting the Scope of the Backup

The backup project can include one or more databases. By default, all tables from the selected database are backed up. In the list box BACKUP CONTENT select individual tables.

Backup Options

In the ADVANCED OPTIONS dialog tab you can set some more details:

• LOCK ALL TABLES: At the beginning of a backup all tables are blocked with the command LOCK. This makes possible a consistent backup of MyISAM tables. However, the entire database is locked during the backup. In addition, the start time of the backup depends on when the program can obtain a LOCK for all tables.
• SINGLE TRANSACTION: The entire backup is executed as a single large transaction. This ensures a consistent backup of InnoDB tables. However, such expansive transactions are likely to affect the database performance when backup executes.
• NORMAL BACKUP: Each table is backed up individually. This is the most efficient method, but it has the disadvantage that changes can be made to other tables in the database during the backup. This can lead to inconsistencies among tables.
• COMPLETE BACKUP: All tables of the selected databases are always backed up. The selection of individual tables in the dialog tab BACKUP PROJECT is ignored.
• NO CREATES: Backup will not add CREATE TABLES commands.
• NO EXTENDED INSERTS: An individual INSERT command is used for each row in a table. Normally, several records are collected into a single INSERT command, making the backup smaller and faster.
• ADD DROP TABLE: Before each CREATE TABLE command, a DROP TABLE command is executed.
• COMPLETE INSERTS: INSERT commands will contain the column names as in INSERT INTO tablename (colname1, colname2) VALUES ....
• ANSI QUOTES: Table and column names are set between ANSI-compatible quotation marks ("name") instead of the MySQL-typical single quotes ('name').
• DISABLE KEYS: Table indexes will be temporarily deactivated when the data are read in. This saves considerable time with large tables, especially with joins.

Executing a Backup

To execute a backup, save the settings with SAVE PROJECT and then click on EXECUTE BACKUP NOW. You will then be requested to select a name for the file into which the backup is to be written. This is a text file with UTF8 encoding containing SQL commands.

The backup file looks similar to the result of mysqldump. MySQL Administrator does not use this command, instead it uses its own backup program.

Automating a Backup

The SCHEDULE dialog tab in MySQL Administrator allows for executing backups on a regular basis. The resulting backup files are written into a chosen directory. To achieve this, MySQL Administrator is automatically started up by the operating system with a special option.

For regular backups, it is not required that MySQL Administrator be up and running all the time. Under Windows, the TASKPLANNER that deals with this PROGRAMS | ACCESSORIES | SYSTEM TOOLS | SCHEDULED TASKS, while under Unix/Linux it is the cron system. It is best to have an alerting system along with regular backups to ensure backups are running as scheduled.

Restore

With this module you can read in previously backed up databases. In the dialog tab RESTORE CONTENT you can specify after the selection of a backup file which table should be restored. By default all the tables are restored.

Normally, the tables will be read into the database that was used in the backup. To read the tables into another database: In the Linux version of MySQL Administrator you give the name of the desired database in the field RESTORE SELECTED TABLES IN, while in the Windows version it is the field TARGET SCHEMA.

Warning: You should use this module only for backup files that you created with MySQL Administrator, not for backup files from other tools (phpMyAdmin, mysqldump, etc.). The RESTORE BACKUP module relies on the backup file following the conventions of MySQL Administrator. That is not always the case with other types of backups.

Replication Status

When the MySQL server is part of a replication system, this module shows the replication status.

Catalogs

Catalogs is a module for managing databases and tables. With it you can get a quick look at existing databases, tables, indices, etc.

This module was designed for creating and modifying tables. It is not used often, and it may be easier to use CREATE TABLE and ALTER TABLE commands or to use phpMyAdmin for database design. MySQL Workbench is another option.