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
- Displays list of databases.
- Second command displays list of tables from sakila database.
- 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.
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
Show Global and Session variables.
The following example shows the setting of some variables and limitations.
Code Sample: Admin/Demos/SetVariables.sql
-- SetVariables.sql: Modify system variables
SET GLOBAL QUERY_CACHE_LIMIT=1000000;
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.
|Short Form||Long Form||Description|
|-c N||--count=N||The number of iterations to make for repeated command execution. Generally used with sleep command below.|
|-f||--force||No warnings are displayed (e.g., with drop database); further commands are executed even after errors.|
|-i N||--sleep=N||Repeats the Specifies that the mysqladmin command along with any defined options every N seconds.
Under Unix/Linux, the command can be terminated with Ctrl+C, under Windows, only by closing the command window.
The specified number of seconds represents the length of delay between executions. The --sleep command can be used in conjunction with the --count option.
|-r||--relative||Displays, in combination with -i and the command extended-status, a change in the previous status.|
|-E||--vertical||Has the same effect as --relative, but changes are displayed in a single, very long, line.|
|-t n||--timeout=n||Sets the timeout time for establishing a connection to the server; if after n seconds no connection has been established, then mysqladmin is terminated.|
|-w n||--wait=n||Attempts n times to establish a connection to the MySQL server.|
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
-#, --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.
Directory where character sets are.
-?, --help Display this help and exit.
-h, --host=name Connect to host.
|mysqladmin tool commands||Description||Command in MySQL session|
|create dbname||Create a new database||CREATE DATABASE|
|drop dbname||Drop an existing database||DROP DATABASE|
View Server Status
|mysqladmin commands||Description||Command in MySQL session|
|status||Create a new mysql database||STATUS|
|extended-status||Displays a long list of status variables of the server||SHOW STATUS|
|flush-status||Resets many status variables to 0||FLUSH STATUS|
|variables||Displays the system variables of the SQL server||SHOW variables|
|version||Determines MySQL server version|
|password <new-password>||Changes the password of the current user; under Windows, this command may cause problems; one could use instead the SQL command SET PASSWORD.|
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.
|Threads||The number of currently open connections.|
|Questions||Number of queries that have been sent to the server since its start.|
|Slow queries||Number of queries that have taken more time to execute than specified long_query_time system variable.|
|Opens||Number of tables that have been opened since the server's start.|
|Flush tables||Number of flush, refresh, and reload commands that have been executed since the server's start.|
|Open tables||Number of current open tables, currently being accessed.|
|Queries per second avg||average number of queries per second.|
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
|mysqladmin commands||Description||Command in MySQL session|
|flush-hosts||Empties the host cache table||FLUSH HOSTS)|
|flush-logs||Closes all logging files and then reopens them; with update logs, a new file is created, where the number of the file terminator is increased by 1 (e.g., file.002 to file.003). For some log file types, MySQL creates a new file.||FLUSH LOGS|
|flush-status||Clears the status variables and resets several counters to zero.||FLUSH STATUS)|
|flush-tables||Closes all open tables||FLUSH TABLES|
|flush-threads||Empties the thread cache.|
|flush-privileges||Reloads the grant tables, equivalent to the reload command.||FLUSH PRIVILEGES).|
|reload||Reinputs the privileges database mysql.|
|refresh||Closes all tables and log files and then reopens them.|
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 commands||Description||Command in MySQL session|
|processlist||Displays all MySQL threads||SHOW THREADS|
|kill id1, id2||Terminates the specified thread(s) via thread-ID||KILL|
|flush-threads||Empties the thread cache.||FLUSH THREADS|
mysqladmin -uroot -p291069kg processlist
Display list of processes running with information.
mysqladmin <auth> kill process-id, process-id-2 ...
Exercise: View and Kill Threads
In this exercise, you will manage running threads.
- Start a MySQL session.
- Discover your thread's ID.
- Kill that particular thread using commands above.
If you execute any command in the MySQL session you just killed, you will see something like:
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.
|Id||The connection identifier.|
|User||The MySQL user who issued the statement. If this is system user, it refers to a non-client thread spawned by the server to handle tasks internally. This could be the I/O or SQL thread used on replication slaves or a delayed-row handler. unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done. For system user, there is no host specified in the Host column.|
|Host||The hostname of the client issuing the statement (except for system user where there is no host). SHOW PROCESSLIST reports the hostname for TCP/IP connections in host_name : client_port format to make it easier to determine which client is doing what.|
|db||The default database, if one is selected, otherwise NULL.|
|Command||The type of command the thread is executing. Descriptions for thread commands can be found in MySQL documentation. The value of this column corresponds to the COM_ xxx commands of the client/server protocol.|
|Time||The time in seconds that the thread has been in its current state.|
|State||An action, event, or state that indicates what the thread is doing. Descriptions for State values can be found in documentation.
Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated. For the SHOW PROCESSLIST statement, the value of State is NULL.
|Info||The statement that the thread is executing, or NULL if it is not executing any statement. The statment might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL p1() statement executes a stored procedure p1(), and the procedure is executing a SELECT statement, the Info value shows the SELECT statement.|
|mysqladmin commands||Description||Command in MySQL session|
|ping||Tests whether a connection to the server can be established.|
|shutdown||Terminates the SQL server.
--shutdown_timeout=N, allows to set the maximum number of seconds to wait for server shutdown. The default value is 1 hour or 3600 seconds.
|start-slave||Starts a slave process for replication.|
|stop-slave||Stops a slave process.|
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.
This module collects status information on connections to the MySQL server, its version, and the client library of MySQL Administrator.
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.
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.
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 %.
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.
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.
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_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).
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.
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.
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.
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.
When the MySQL server is part of a replication system, this module shows the replication status.
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.
Administering MySQL Conclusion
This lesson covered the administrative options in MySQL.