MySQL comes with quite a few utilities, or clients, each of which provides interfaces for carrying out various tasks pertinent to server administration. This chapter offers a general overview of the most commonly used clients, and provides an in-depth introduction to the most prominent two of the bunch, namely mysql
and mysqladmin
. Because the MySQL manual already does a fantastic job at providing a general overview of each client, this chapter instead focuses on those features that you're most likely to regularly use in your daily administration activities.
Of course, not all users are interested in managing databases from the command line; therefore, both the MySQL developers and third parties have been hard at work building GUI-based management solutions. This chapter concludes with an overview of several of the most prominent GUI-based administration applications.
MySQL is bundled with quite a few client programs, a total of 18 to be exact (as of 5.1.4-beta). This section offers extensive introductions to the two most prominent clients (mysql
and mysqladmin
), and concludes with a brief introduction to the others.
The mysql
client is an extremely useful SQL shell, capable of managing almost every conceivable aspect of a MySQL server, including creating, modifying, and deleting tables and databases, creating and managing users, viewing and modifying the server configuration, and querying table data. Although the majority of the time you'll likely be working with MySQL via a GUI or an API, this client is nonetheless invaluable for carrying out various administration tasks, particularly given its scriptable functionality within the shell environment. Its general usage syntax follows:
mysql [options] [database_name] [noninteractive_arguments]
The client can be used in interactive or noninteractive mode, both of which are introduced in this section. Regardless of which you use, you'll typically need to provide connection options. Although exactly which credentials are required depends upon your server configuration (a matter discussed in detail in Chapter 29), you typically need a hostname (--host=, -h)
, username (--user=, -u)
, and password (--password=, -p)
. Often you'll want to include the target database name (--database=
, -D
) to save the extra step of executing the USE
command once you've entered the client. Although order is irrelevant, the connection options are generally entered like so:
%>mysql -h yourhostname -u yourusername -p -D databasename
Note that the password is not included on the command line. For example, consider an attempt to connect to a MySQL server residing at www.example.com using the username jason
, the password secret
, and the database corporate
:
%>mysql -h www.example.com -u jason -p -D corporate
You might also include other options, many of which are introduced in the later section "Useful mysql Options," or press Enter to be prompted for the password. Once prompted, you would enter secret
. If your credentials are valid, you'll be granted access to the client interface, or permitted to execute whatever noninteractive arguments are included on the command line.
Using mysql in Interactive Mode
To use mysql
in interactive mode, you need to first enter the interface. As already explained, you do so by passing along appropriate credentials. Building on the previous example, suppose you want to interact with the corporate
database located on the www.example.com server:
%>mysql -h www.example.com -u jason -p -D corporate
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.14-beta-community-nt MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
Once connected via the mysql
client, you can begin executing SQL commands. For example, to view a list of all existing databases, use this command:
mysql>SHOW databases;
To switch to (or use) another database, the mysql
database for example, use this command:
mysql>USE mysql;
Note To switch to the mysql
database, you'll almost certainly require root access. If you don't have root access, and have no other databases at your disposal, you can switch to the test
database, created by MySQL at installation time, or create a new database. However, if you're relying on a third party to manage your MySQL installation, keep in mind that this database may have been previously removed for administrative reasons.
Once you've switched to the mysql
database context, you can view all tables with this command:
mysql>SHOW TABLES;
This returns the following:
+------------------------------------+
| Tables_in_mysql |
+------------------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| plugin |
| proc |
| procs_priv |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------+
21 rows in set (0.00 sec)
To view the structure of one of those tables, for instance, the host
table, use this command:
mysql>DESCRIBE host;
This returns:
+--------------------------+---------------+------+--------+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+--------+-----------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+--------------------------+---------------+------+--------+-----------+-------+
20 rows in set (0.13 sec)
You can also execute SQL queries such as INSERT
, SELECT
, UPDATE
, and DELETE
. For example, suppose you want to select all values residing in the Host, User, and password columns of the user
table, found in the mysql
database, and order it by the Host
:
mysql>SELECT Host, User, password FROM user ORDER BY Host;
In summary, you can execute any query via the mysql
client that MySQL is capable of understanding.
Note MySQL treats query keywords in a case-insensitive fashion. For the sake of consistency, the keywords are capitalized in this book. Keep in mind, however, that while on Windows and OS X the default is to treat table names and field names in a case-insensitive fashion, on Unix they are indeed case sensitive.
You can exit the mysql
client by executing any of the following commands: quit
, exit
, q
, or Ctrl-D.
Using mysql in Batch Mode
The mysql
client also offers batch mode capabilities, used for both importing schemas and data into a database and piping output to another destination. For example, you can execute SQL commands residing in a text file by having the mysql
client consume the contents of /path/to/file
using the <
operator, like so:
%>mysql [options] < /path/to/file
This feature has many uses. For instance, one possible use of this feature is to have server statistics sent via e-mail to a system administrator each morning. For example, suppose that you want to monitor the number of slow-executing queries that have taken place on the server. Start by creating a user with no password, named mysqlmonitor
, granting the user only usage
privileges on the mysql
database. Then, create a file named mysqlmon.sql
and add the following line to it:
SHOW STATUS LIKE "slow_queries";
Then, if you're running MySQL on Unix, you can place the following line into crontab
:
0 3 * * * mysql -u monitor < mysqlmon.sql | mail -s "Slow queries"
[email protected]
Each time this command executes, an e-mail titled "Slow queries" will be sent to [email protected] at 3 a.m. each morning. The e-mail body will contain a number consisting of the value of the status variable slow_query
.
If you're running Windows, you can use the Event Scheduler to similar ends.
Incidentally, you can also execute a file while already logged into the mysql
client, by using the source
command:
mysql>source mysqlmon.sql
Useful mysql Tips
This section enumerates a few useful tips that all MySQL users should know when starting out with the mysql
client.
Paging Output
You can page through output using your operating system's paging commands. For example:
%>mysql < queries.sql | more
Displaying Results Vertically
Use the G
option to display query results in a vertical output format. This renders the returned data in a significantly more readable fashion. Consider this example in which all rows are selected from the mysql.db
table by using the G
option:
mysql>use mysql;
mysql>select * from dbG
*************************** 1. row ***************************
Host: %
Db: test%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
...
*************************** 2. row ***************************
...
Logging Queries
When working interactively with the mysql
client, it can be useful to log all results to a text file so that you can review them later. You can initiate logging with the tee
or T
option, followed by a file name and, if desired, prepended with a path. For example, suppose you want to log the session to a file named session.sql
:
mysql>T session.sql
Logging to file 'session.sql'
mysql>show databases;
+--------------+
| Database |
+-------------+
| mysql |
| test |
+-------------+
Once logging begins, the output exactly as you see it here will be logged to session.sql. To disable logging at any time during the session, execute notee
, or
.
Getting Server Statistics
Executing the status
, or s
, command will retrieve a number of useful statistics regarding the current server status, including uptime, version, TCP port, connection type, total queries executed, average queries per second, and more.
Suppose that you manage a table consisting of 10,000 newsletter members, and one day decide to use the mysql
client to delete a now unneeded test account. It's been a long day, and without thinking you execute
mysql>DELETE FROM subscribers;
rather than
mysql>DELETE FROM subscribers WHERE email="[email protected]";
Whoops, you just deleted your entire subscriber base! Hopefully a recent backup is handy. The --safe-updates
option prevents such inadvertent mistakes by refusing to execute any DELETE
or UPDATE
query that is not accompanied with a WHERE
clause. Comically, you could also use the --i-am-a-dummy
switch for the same purpose!
Modifying the mysql Prompt
When simultaneously working with several databases residing on different servers, you can quickly become confused as to exactly which server you're using. To make the location obvious, modify the default prompt to include the hostname. You can do this in several ways.
First, you can modify the prompt on the command line when logging into mysql
:
%>mysql -u jason --prompt="(u@h) [d]> " -p corporate
Once you're logged into the console, the prompt will appear like so:
(jason@localhost) [corporate]>
Second, to render the change more permanent, you can also make the change in the my.cnf
file, under the [mysql]
section:
[mysql]
...
prompt=(u@h) [d]>
Finally, on Linux only, you can include the hostname on the prompt via the MYSQL_PS1
environment variable:
%>export MYSQL_PS1="(u@h) [d]>"
Note A complete list of flags available to the prompt are available in the MySQL manual.
Outputting Table Data in HTML and XML
This cool but largely unknown feature of the mysql
client allows you to output query results in XML and HTML formats, using the --xml (-X)
and --html (-H)
options, respectively. For example, suppose you want to create an XML file consisting of the databases found on a given server. You could place the command SHOW DATABASES
in a text file and then invoke the mysql
client in batch mode, like so:
%>mysql -X < showdb.sql > serverdatabases.xml
The result is that a file named serverdatabases.xml
is created that consists of output similar to the following:
<?xml version="1.0"?>
<resultset statement="show databases">
<row>
<field name="Database">information_schema</field>
</row>
<row>
<field name="Database">corporate</field>
</row>
<row>
<field name="Database">test</field>
</row>
</resultset>
Viewing Configuration Variables and System Status
You can view a comprehensive listing of all server configuration variables via the SHOW VARIABLES
command:
mysql>SHOW VARIABLES;
As of version 5.0.3, this command returns 234 different system variables. If you'd like to view just a particular variable, say the default table type, you can use this command in conjunction with LIKE
:
mysql>SHOW VARIABLES LIKE "table_type";
This returns:
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| table_type | MyISAM |
+------------------+----------+
Viewing system status information is equally as trivial:
mysql>SHOW STATUS;
This returns:
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 334 |
| Bytes_sent | 11192 |
...
| Threads_running | 1 |
| Uptime | 231243 |
+----------------------+---------+
213 rows in set (0.00 sec)
As of version 5.0.3, this returns 213 different status variables. To view just a single item from the status report, say the total amount of bytes sent, use this command:
mysql>SHOW STATUS LIKE "bytes_sent";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Bytes_sent | 11088 |
+------------------+-------+
1 row in set (0.00 sec)
If you'd like to retrieve groups of similarly named variables (which often imply similar purpose), you can use the %
wildcard. For example, the following command would retrieve all of the variables used to track statistics pertinent to MySQL's query caching feature:
mysql>SHOW STATUS LIKE "Qc%";
Useful mysql Options
Like all clients introduced in this chapter, mysql
offers a number of useful options. Many of the most important options are introduced here:
--auto-rehash:
By default, mysql
creates hashes of database, table, and column names to facilitate auto-completion (you can auto-complete database, table, and column names with the Tab key). You can disable this behavior with --no-auto-rehash
. If you'd like to re-enable it, use this option. If you don't plan to use auto-completion, consider disabling this option, which will slightly speed startup time.--column-names:
By default, mysql
includes the column names at the top of each result set. You can disable them with --no-column-names
. If you'd like to re-enable this behavior, use this option anew.--compress
, -C:
Enables data compression when communicating between the client and server.--database=
name
, -D:
Determines which database will be used. When using mysql interactively, you can also switch between databases as necessary with the USE
command.--default-character-set=
character_set:
Sets the character set.--disable-tee:
If you've enabled logging of all queries and the results with the option --tee or with the command tee, you can disable this behavior with this option.--execute=
query
, -e
query:
Executes a query without having to actually enter the client interface. You can execute multiple queries with this option by separating each with a semicolon. Be sure to enclose the query in quotes so that the shell does not misinterpret it as multiple arguments. For example:%>mysql -u root -p -e "USE corporate; SELECT * from product;"
--force
, -f:
When used in noninteractive mode, MySQL can read and execute queries found in a text file. By default, execution of these queries stops if an error occurs. This option causes execution to continue regardless of errors.--host=
name
, -h:
Specifies the connection host.--html
, -H:
Outputs all results in HTML format. See the corresponding tip in the section "Useful mysql Tips" for more information about this option.--no-beep
, -b:
When rapidly typing and executing queries, it's commonplace for errors to occur, resulting in the annoying beeping error. Use this option to disable the sound.--pager[=
pagername
]:
Many queries produce more information than can fit on a single screen. You can tell the client to present results one page at a time by assigning a pager. Examples of valid pagers include the Unix commands more
and less
. Presently, this command is only valid on the Unix platform. You can also set a pager while inside the mysql
client by using the P
command.--password
, -p:
Specifies the password. Note that you shouldn't supply the password on the command line, as you might the username or host, but rather should wait for the subsequent prompt so that the password isn't stored in plain text in your command history.--port=#
, -P:
Specifies the host connection port.--protocol=
name:
MySQL supports four connection protocols, including memory, pipe
, socket
, and tcp
. Use this option to specify which protocol you'd like to use:
port
). You need to use TCP if the client and server reside on different computers, although you can also use it when all communication is conducted locally.Note Neither of the preceding Windows-specific options is enabled by default (TCP is the default on Windows for both local and remote communication).
--safe-updates
, -U:
Causes mysql
to ignore all DELETE
and UPDATE
queries in which the WHERE
clause is omitted. This is a particularly useful safeguard for preventing accidental mass deletions or modifications. See the section "Useful mysql Tips" for more information about this option.--skip-column-names:
By default, mysql
includes headers containing column names at the top of each result set. You can disable inclusion of these headers with this option.--tee=
name:
Causes mysql to log all commands and the resulting output to the file specified by name
. This is particularly useful for debugging purposes. You can disable logging at any time while inside mysql by issuing the command notee, and can later re-enable it with the command tee
. See the corresponding tip in the section "Useful mysql Tips" for more information about this option.--vertical
, -E:
Causes mysql
to display all query results in a vertical format. This format is often preferable when you're working with tables that contain several columns. See the corresponding tip in the section "Useful mysql Tips" for more information about this option.--xml
, -X:
Causes all results to be output in XML format. See the corresponding tip in the section "Useful mysql Tips" for more information about this option.The mysqladmin
client is used to carry out a wide array of administrative tasks, perhaps most notably creating and destroying databases, monitoring server status, and shutting down the MySQL server daemon. Like mysql
, you need to pass in the necessary access credentials to use mysqladmin
.
For example, you can examine all server variables and their values, by executing:
%>mysqladmin -u root -p variables
Enter password:
If you've supplied valid credentials, a long list of parameters and corresponding values will scroll by. If you want to page through the results, you can pipe this output to more
or less
if you're using Linux, or more
if you're using Windows.
mysqladmin Commands
While mysql
is essentially a free-form SQL shell, allowing any SQL query recognized by MySQL, mysqladmin
's scope is much more limited, recognizing a predefined set of commands, many of which are introduced here:
create
databasename:
Creates a new database, the name of which is specified by databasename
. Note that each database must possess a unique name. Attempts to create a database using a name of an already existing database will result in an error.drop
databasename:
Deletes an existing database, the name of which is specified by databasename
. Once you submit a request to delete the database, you are prompted to confirm the request, to prevent accidental deletions.extended-status:
Provides extended information regarding the server status. This is the same as executing show status
from within the mysql
client.flush-hosts:
Flushes the host cache tables. You need to use this command if a host's IP address changes. Also, you need to use this command if the MySQL server daemon receives a number of failed connection requests from a specific host (the exact number is determined by the max_connect_errors
variable), because that host will be blocked from attempting additional requests. Executing this command removes the block.flush-logs:
Closes and reopens all logging files.flush-status:
Resets status variables, setting them to zero.flush-tables:
Closes all open tables and terminates all running table queries.flush-threads:
Purges the thread cache.flush-privileges:
Reloads the privilege tables. If you're using the GRANT and REVOKE commands rather than directly modifying the privilege tables using SQL queries, you do not need to use this command.kill id[,id2[,id
N
]]:
Terminates the process(es) specified by id
, id2
, through id
N
. You can view the process numbers with the processlist
command.old-password
new-password:
Changes the password of the user specified by -u
to new-password
using the pre-MySQL 4.1 password-hashing algorithm.password
new-password:
Changes the password of the user specified by -u
to new-password
using the post-MySQL 4.1 password-hashing algorithm.ping:
Verifies that the MySQL server is running by pinging it, much like a Web or mail server might be pinged.processlist:
Displays a list of all running MySQL server daemon processes.reload:
Alias of the command flush-privileges
.refresh:
Combines the tasks carried out by the commands flush-tables
and flush-logs
.shutdown:
Shuts down the MySQL server daemon. Note that you cannot restart the daemon using mysqladmin
. Instead, it must be restarted using the mechanisms introduced in Chapter 26.status:
Outputs various server statistics, such as uptime, total queries executed, open tables, average queries per second, and running threads.start-slave:
Starts a slave server. This is used in conjunction with MySQL's replication feature.stop-slave:
Stops a slave server. This is used in conjunction with MySQL's replication feature.variables:
Outputs all server variables and their corresponding values.version:
Outputs version information and server statistics.Like the mysql
and mysqladmin
clients, all utilities introduced in this section can be invoked with the --help
option.
mysqldump
The mysqldump
client is used to export existing table data, table structures, or both from the MySQL server. If requested, the exported data can include all necessary SQL statements required to re-create the dumped information. Furthermore, you can specify whether to dump one, some, or all databases found on the server, or even just specific tables in a given database.
You can invoke mysqldump
using any of the following three syntax variations:
%>mysqldump [options] database [tables]
%>mysqldump [options] --databases [options] database1 [database2...]
%>mysqldump [options] --all-databases [options]
Consider a few examples. The first example dumps just the table structures of all databases found on a local server to a file named output.sql
:
%>mysqldump -u root -p --all-databases --no-data > output.sql
Note that the output is being directed to a file; otherwise, the output would be sent to standard output, the screen. Also, keep in mind that the .sql
extension is not required. This extension is used here merely for reasons of convenience; you can use any extension you wish.
The next example dumps just the data of a single database, corporate
:
%>mysqldump -u root -p --no-create-info corporate > output.sql
The final example dumps both the structure and the data of two tables located in the corporate
database, including DROP TABLE
statements before each CREATE
statement. This is particularly useful when you need to repeatedly re-create an existing database, because attempting to create already existing tables results in an error; thus the need for the DROP TABLE
statements.
%>mysqldump -u root -p --add-drop-table corporate product staff > output.sql
Caution The mysqldump
client should be run only during times of relative inactivity, because of the way it locks tables during the backup process.
mysqlshow
The mysqlshow
utility offers a convenient means for determining which databases, tables, and columns exist on a given database server. Its usage syntax follows:
mysqlshow [options] [database [table [column]]]
For example, suppose you want to view a list of all available databases:
%>mysqlshow -u root -p
To view all tables in a particular database, such as mysql
:
%>mysqlshow -u root -p mysql
To view all columns in a particular table, such as the mysql
database's db
table:
%>mysqlshow -u root -p mysql db
Note that what is displayed depends entirely upon the furnished credentials. In the preceding examples, the root
user is used, which implies that all information will be at the user's disposal. However, other users will likely not have as wide-ranging access. Therefore, if you're interested in surveying all available data structures, use the root
user.
mysqlhotcopy
You can think of the mysqlhotcopy
utility as an optimized mysqldump
, but using Perl and its MySQL DBI module, and various optimization techniques to back up one or several databases, writing the data to a file (or files) of the same name as the database that is being backed up. Although optimized, this utility comes at somewhat of a disadvantage insofar as it can be run only on the same machine on which the target MySQL server is running. Further, it's not available for Windows, and only supports MyISAM and Archive tables. If you require remote backup capabilities, take a look at mysqldump
or MySQL's replication features.
Three syntax variations are available:
%>mysqlhotcopy [options] database1 [/path/to/target/directory]
%>mysqlhotcopy [options] database1...databaseN /path/to/target/directory
%>mysqlhotcopy [options] database./regular-expression/
As is the norm, numerous options are available for this utility, a few of which are demonstrated in the usage examples. In the first example, the corporate
and mysql
databases are copied to a backup directory:
%>mysqlhotcopy -u root -p corporate mysql /usr/local/mysql/backups
The following variation of the first example adds a default file extension to all copied database files:
%>mysqlhotcopy -u root -p --suffix=.sql corporate mysql /usr/local/mysql/backups
For the last example, a backup is created of all tables in the corporate
database that begin with the word sales
:
%>mysqlhotcopy -u root -p corporate./^sales/ /usr/local/mysql/backups
Like all other MySQL utilities, you must supply proper credentials to use mysqlhotcopy
's functionality. In particular, the invoking user needs to have SELECT
privileges for those tables being copied. In addition, you need write access to the target directory. Finally, the Perl DBI::mysql
module must be installed.
Tip Although, like all other utilities, you can learn more about mysqlhotcopy
by invoking it with the --help
option, more thorough documentation can be had via perldoc
. Execute perldoc mysqlhotcopy
for a comprehensive guide.
The mysqlimport
utility offers a convenient means for importing data from a delimited text file into a database. It is invoked using the following syntax:
%>mysqlimport [options] database textfile1 [textfile2...]
This utility is particularly useful when migrating to MySQL from another database product or legacy system, because the vast majority of storage solutions (MySQL included) are capable of both creating and parsing delimited data. An example of a delimited datafile follows:
Hemingway, Ernest The Sun Also Rises 1926
Steinbeck, John Of Mice and Men 1937
Golding, William Lord of the Flies 1954
In this example, each item (field) of data is delimited by a tab (
), and each row by a newline (
). Keep in mind that the delimiting characters are a matter of choice, because most modern storage solutions offer a means for specifying both the column and the row delimiters when creating and reading delimited files. Suppose these rows were placed in a file called books.txt
and you wanted to read this data from and write it to a database aptly called books
:
%>mysqlimport -u root -p --fields-terminated-by=
>--lines-terminated-by=
books books.sql
The executing user requires INSERT
permissions for writing the data to the given table, in addition to FILE
privileges to make use of mysqlimport
. See Chapter 29 for more information about setting user privileges.
myisamchk
Although it is widely acknowledged that MySQL is quite stable, certain conditions out of its control can result in corrupt tables. Such corruption can wreak all sorts of havoc, including preventing further insertions or updates, and even resulting in the temporary (and in extreme cases, permanent) loss of data. If you experience any table errors or oddities, you can use the myisamchk
utility to check MyISAM table indices for corruption, and repair them if necessary. It's invoked using the following syntax:
%>myisamchk [options] /path/to/table_name.MYI
In the absence of any options, myisamchk
just checks the designated table for corruption. For example, suppose you want to check the table named staff
that resides in the corporate
database:
%>myisamchk /usr/local/mysql/data/corporate/staff.MYI
Varying degrees of checks are also available, each of which requires additional time but more thoroughly reviews the table for errors. Although the default is simply check (--check
), there also exists a medium check (--medium-check
) and an extended check (--extend-check
). Only use the extended check for the most severe cases, because medium check will catch the overwhelming majority of errors, and consume considerably less time. You can also review extended information for each of these checks by supplying the -information (-i)
option, which offers various table-specific statistics.
If problems are identified with the table, you'll be notified accordingly. If an error is found, you can ask myisamchk
to attempt to repair it by supplying the -recover (-r)
option:
%>myisamchk -r /usr/local/mysql/data/corporate/staff.MYI
Note that what is presented here is just a smattering of the options available to this utility. Definitely consult the manual before using myisamchk
to check or repair tables. Also, you should only run myisamchk
when the MySQL server daemon is not running. If you don't have the luxury of taking your database server offline, take a look at the next utility, mysqlcheck
.
mysqlcheck
As of version 3.23.38, the mysqlcheck
utility offers users the means for checking and, if necessary, repairing corrupted tables while the MySQL server daemon is running. It can be invoked in any of the three following ways:
%>mysqlcheck [options] database [tables]
%>mysqlcheck [options] --databases database1 [database2...]
%>mysqlcheck [options] --all-databases
In addition to the typical user credentials and concerned databases and tables, you can specify whether you want to analyze (-a
), repair (-r
), or optimize (-o
) by passing in the appropriate parameter. So, for example, suppose the staff
table, located in the table corporate
, became corrupted due to sudden hard-drive failure. You could repair it by executing:
%>mysqlcheck -r corporate staff
Like myisamchk
, mysqlcheck
is capable of finding and repairing the overwhelming majority of errors. In addition, it offers a wide-ranging array of features. Therefore, before you use it to resolve any mission-critical problems, take some time to consult the MySQL manual to ensure that you're using the most effective set of options.
This section highlights several of the options shared by many of MySQL's clients, mysql
and mysqladmin
included. The options are divided into two categories: connection options and general options. Before moving on to a breakdown of the options falling under these two categories, take a moment to review a few simple rules that you should keep in mind when using these options:
-p
is representative of password, but -P
denotes a port number.--host=www.example.com
-h www.example.com
Connection Options
There are six commonly used connection options that you'll likely consider using when starting a mysql
client. The long and short forms are listed here:
--host=
name
, -h:
Specifies the target database host. If you're connecting to the localhost, you can omit this option.--password[=
name
], -p:
Specifies the connecting user's password. Although you can include the password on the command line, doing so is unadvisable, because it could be logged to a command history file, causing a considerable security risk. Instead, upon execution, you'll be prompted for the password, which will not be echoed back to the screen when you enter it. Regardless of which route you choose, keep in mind that neither protects against password sniffing through network monitoring when you connect to a remote host, because the password, along with all other connection information, is transmitted unencrypted unless MySQL's Secure Sockets Layer (SSL) capabilities are used. See Chapter 29 for more information about MySQL's SSL feature.--pipe
, -W:
Specifies that named pipes will be used to connect to the server.--port=
port_num
, -P:
Specifies the port to use when connecting to the MySQL server. Note that you can't just specify a nonstandard port (3306 is the default) without configuring the MySQL server daemon to listen on that port. You can do so simply by passing this same option to the mysqld daemon at startup time.--socket=
/path/to/socket
, -s:
For localhost connections, a socket file is required. By default this file is created in /tmp
on Unix machines. On Windows machines, this option determines the name of the pipe (by default this name is MySQL) used for local connections when named pipes are used.--user=
name
, -u:
Specifies the connecting user's username.General Options
The following list highlights many of the options available to all or most clients. You can verify whether a particular client supports a given option by outputting the client's help page with the option --help
.
--compress
, -C:
Enables compression for the protocol used for client/server communication.--defaults-file=
/path/to/configuration/file:
At startup, each client typically searches in several locations for configuration files and applies the settings accordingly. You can override this behavior by specifying the location of a configuration file with this option.--defaults-extra-file=
/path/to/configuration/file:
Reads this file after all other configuration files have been read. You might use such a file during application testing, for example.--help
, -?:
Outputs help information before exiting. You can pipe the results through a pager to facilitate reading. For example, the following command takes advantage of the Unix more
command to page output:%>mysql --help | more
--no-defaults:
Ignores all configuration files.--print-defaults:
Outputs the options that will be used by the client as defined within configuration files and environment variables.--silent
, -s:
Decreases client chatter, or output. Note that this option does not necessarily suppress all output.--
variable-name
=
value:
Sets a variable's value. Note that the option isn't actually called "variable-name." Rather, this is intended as a placeholder for the name of whatever variable you're trying to modify.--verbose
, -v:
Outputs more output than would occur by default.--version
, -V:
Exits after outputting client version information.Cognizant that not all users are particularly comfortable working from the command line, MySQL AB has been making great strides in developing graphically based management solutions. Four free products are currently available, and are released under MySQL AB's dual licensing model (see Chapter 25 for more about MySQL AB's licensing arrangements):
You have the choice of selectively downloading each as you see fit from the MySQL Web site, or downloading them in a single package called MySQL GUI Tools. I recommend downloading the package, giving you the opportunity to experiment with all of them.
Following an overview of the GUI Tools installation process, additional details about each program are offered.
The GUI Tools package is available on all of the standard platforms, Linux, Mac OS X, and Windows included. Source code is also available if you want to build it yourself. Head on over to http://dev.mysql.com/downloads/ and download the appropriate version for your platform. The installation process is easy enough so as not to warrant a thorough explanation here; just initiate the process, review the usage terms, and choose which components you'd like to install.
MySQL Administrator was first released to the public in January 2004. Despite being under development for more than three years, this product remains a moving target, with features being added and tweaked on a regular basis.
MySQL Administrator offers a number of compelling features:
Figure 27-1. Managing user privileges with MySQL Administrator
MySQL Administrator is great for maintaining all aspects of your database, and is capable of handling everything from executing backups to reviewing server logs. However, it's not possible to manage your data and table structures using this application. That's where MySQL Query Browser comes into the picture.
MySQL Query Browser offers an amazingly efficient means for interacting with your MySQL data. It is arguably the most capable MySQL-specific data administration interface released to date, offering a point-and-click means for inserting, modifying, and deleting data; query auto-completion; a tabbed interface, reminiscent of the Firefox browser, for simultaneously managing multiple database sessions; and a convenient interface for navigating the MySQL documentation. In addition to structure- and data-editing capabilities, it's also possible to compare result sets, review query efficiency, create and manage stored procedures and views, and much more.
A list of MySQL Query Browser's feature highlights follows:
SELECT
queries.Figure 27-2. Editing a table schema with MySQL Query Browser
Organizations around the globe increasingly view MySQL as a viable, cost-efficient alternative to the expensive databases that have drained corporate budgets for years. Yet the cost and time required to migrate often enormous data stores to another solution can be extravagant. Accordingly, the MySQL developers created the MySQL Migration Toolkit, a well-constructed seven-step wizard (a screenshot of which is shown in Figure 27-3) that you can use to both speed the process and reduce errors.
Currently the MySQL Migration Toolkit is capable of migrating schemas and data from Microsoft Access, MaxDB, Microsoft SQL Server, Oracle, and Sybase. You can also migrate data from any database supporting a generic JDBC driver, as well as from other MySQL installations.
Figure 27-3. MySQL Migration Toolkit wizard
Note On the MySQL Web site, you'll find several great screencasts demonstrating the process of migrating schemas and data from a variety of databases. The Microsoft SQL Server migration screencast is particularly compelling because it demonstrates the successful migration of the well-known Northwind database to MySQL.
Although not a product offered by MySQL, phpMyAdmin is such a valuable administration tool that it certainly bears mentioning here. A Web-based MySQL administration application written in PHP, phpMyAdmin is used by countless thousands of developers, and is practically a staple among Web hosting providers around the globe. It's not only very stable (it has been in development since 1998), but also feature-rich thanks to an enthusiastic development team and user community. Speaking as a longtime user of this product, it's difficult to fathom how one could get along without it.
phpMyAdmin offers a number of compelling features:
mysql
database is offered in Figure 27-4.Figure 27-4. Overview of the mysql database
phpMyAdmin is released under the GNU General Public License. The official phpMyAdmin Web site, http://www.phpmyadmin.net/, offers source downloads, news, mailing lists, a live demo, and more.
This chapter introduced MySQL's many clients, and provided special attention to what for many are the two most important of the bunch: mysql and mysqladmin. Several of the most prevalent GUI-based management solutions were also presented. Because administration is such a key aspect of maintaining a healthy database server, consider experimenting with all of them to determine which route best fits your specific database management situation.
The next chapter addresses another key aspect of MySQL: table structures and datatypes. You'll learn about the various table types and the supported datatypes and attributes, and will be presented with numerous examples regarding how to create, modify, and use databases, tables, and columns.
52.15.168.255