MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Administering MySQL Administering MySQL

Administering MySQL

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.

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

Code Sample:

mysqlshow -u root -p
mysqlshow -u root -p mysql
mysqlshow -u sakilaadmin -psakila sakila customer
Code Explanation
  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.

Code Sample:

mysqlshow -usakilaadmin -psakila -i --status sakila
Code Explanation

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.

Code Sample:

-- ShowSystemVars.sql

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
Code Explanation

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;
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;
Code Explanation

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.

Syntax
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 tool- Options
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 --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

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.

Code Sample:

mysqladmin -uroot -p291069kg --sleep=1800 variables
Code Explanation

Shows variables every 30 minutes.

Code Sample:

mysqladmin -uroot -p status
Code Explanation

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.

Status variable Description
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.

Code Sample:

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

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
Syntax
mysqladmin processlist

Code Sample:

mysqladmin -uroot -p291069kg processlist
Code Explanation

Display list of processes running with information.

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

Exercise: View and Kill Threads

Duration: 10 to 20 minutes.

In this exercise, you will manage running threads.

  1. Start a MySQL session.
  2. Discover your thread's ID.
  3. Kill that particular thread using commands above.

If you execute any command in the MySQL session you just killed, you will see something like:

Syntax
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:

Code Sample:

SHOW FULL PROCESSLIST;
Code Explanation

Display list of processes running inside of a mysql session.

Column Description
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.

Server Management

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.

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.

Administering MySQL Conclusion

This lesson covered the administrative options in MySQL.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 
mysql tools

Selection options GUI tool for mysql for purchase.

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...