Table of Contents
This chapter provides a brief overview of the command-line programs provided by MySQL AB. It also discusses the general syntax for specifying options when you run these programs. Most programs have options that are specific to their own operation, but the option syntax is similar for all of them. Finally, the chapter provides more detailed descriptions of individual programs, including which options they recognize.
There are many different programs in a MySQL installation. This section provides a brief overview of them. Later sections provide a more detailed description of each one, with the exception of MySQL Cluster programs. Each program's description indicates its invocation syntax and the options that it understands. Chapter 16, MySQL Cluster, describes programs specific to MySQL Cluster.
Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL, for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install an additional package.
Each MySQL program takes many different options. Most programs
provide a --help option that you can use to get a
description of the program's different options. For example, try
mysql --help.
You can override default option values for MySQL programs by specifying options on the command line or in an option file. See Section 4.2, “Using MySQL Programs”, for general information on invoking programs and specifying program options.
The MySQL server, mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that assist you in starting and stopping the server:
The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running, because clients gain access to databases by connecting to the server. See Section 5.1, “The MySQL Server”.
A server startup script. mysqld_safe attempts to start mysqld. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
A server startup script. This script is used on systems that use System V-style run directories containing scripts that start system services for particular run levels. It invokes mysqld_safe to start the MySQL server. See Section 4.3.3, “mysql.server — MySQL Server Startup Script”.
A server startup script that can start or stop multiple
servers installed on the system. See
Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”. As of MySQL 5.0.3 (Unix-like
systems) or 5.0.13 (Windows), an alternative to
mysqld_multi is
mysqlmanager, the MySQL Instance Manager.
See Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”.
There are several programs that perform setup operations during MySQL installation or upgrading:
This program is used during the MySQL build/installation process. It compiles error message files from the error source files. See Section 4.4.1, “comp_err — Compile MySQL Error Message File”.
This program makes a binary release of a compiled MySQL. This
could be sent by FTP to
/pub/mysql/upload/ on
ftp.mysql.com for the convenience of other
MySQL users.
This program is used on Windows. It packages a MySQL distribution for installation after the source distribution has been built. See Section 4.4.2, “make_win_bin_dist — Package MySQL Distribution as ZIP Archive”.
This program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.
Note: As of MySQL 5.0.19, this program has been superseded by mysql_upgrade.
This script creates the MySQL database and initializes the grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system. See Section 2.4.16.2, “Unix Post-Installation Procedures”, and Section 4.4.6, “mysql_install_db — Initialize MySQL Data Directory”.
This program enables you to improve the security of your MySQL installation. SQL. See Section 4.4.7, “mysql_secure_installation — Improve MySQL Installation Security”.
This program loads the time zone tables in the
mysql database using the contents of the
host system zoneinfo database (the set
of files describing time zones). SQL. See
Section 4.4.8, “mysql_tzinfo_to_sql — Load the Time Zone Tables”.
This program is used after a MySQL upgrade operation. It checks tables for incompatibilities and repairs them if necessary, and updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
This program is used on Unix or Unix-like systems to create a MySQL source distribution that can be compiled on Windows. See Section 2.4.15.6.5, “Creating a Windows Source Package from the BitKeeper Repository”, and Section 4.4.3, “make_win_src_distribution — Create Source Distribution for Windows”.
MySQL client programs:
The command-line tool for interactively entering SQL statements or executing them from a file in batch mode. See Section 4.5.1, “mysql — The MySQL Command-Line Tool”.
A client that performs administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files. mysqladmin can also be used to retrieve version, process, and status information from the server. See Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.
A table-maintenance client that checks, repairs, analyzes, and optimizes tables. See Section 4.5.3, “mysqlcheck — A Table Maintenance and Repair Program”.
A client that dumps a MySQL database into a file as SQL, text, or XML. See Section 4.5.4, “mysqldump — A Database Backup Program”.
A client that imports text files into their respective tables
using LOAD DATA INFILE. See
Section 4.5.5, “mysqlimport — A Data Import Program”.
A client that displays information about databases, tables, columns, and indexes. See Section 4.5.6, “mysqlshow — Display Database, Table, and Column Information”.
MySQL administrative and utility programs:
An offline InnoDB offline file checksum
utility. See Section 4.6.1, “innochecksum — Offline InnoDB File Checksum Utility”.
A utility that displays information about full-text indexes in
MyISAM tables. See
Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.
A utility to describe, check, optimize, and repair
MyISAM tables. See
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
A utility that processes the contents of a
MyISAM log file. See
Section 4.6.4, “myisamlog — Display MyISAM Log File Contents”.
A utility that compresses MyISAM tables to
produce smaller read-only tables. See
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
A script that checks the access privileges for a hostname, username, and database combination. See Section 4.6.6, “mysqlaccess — Client for Checking Access Privileges”.
A utility for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash. See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
A utility that quickly makes backups of
MyISAM tables while the server is running.
See Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.
The MySQL Instance Manager, a program for monitoring and managing MySQL servers. See Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”.
A utility that converts tables in a database to use a given storage engine. See Section 4.6.12, “mysql_convert_table_format — Convert Tables to Use a Given Storage Engine”.
A utility that analyzes queries in the MySQL query log using
EXPLAIN See
Section 4.6.13, “mysql_explain_log — Use EXPLAIN on Statements in Query Log”.
A utility that reads files containing SQL statements (such as update logs) and extracts statements that match a given regular expression. See Section 4.6.14, “mysql_find_rows — Extract SQL Statements from Files”.
A utility that converts the extensions for
MyISAM table files to lowercase. This can
be useful after transferring the files from a system with
case-insensitive filenames to a system with case-sensitive
filenames. See Section 4.6.15, “mysql_fix_extensions — Normalize Table Filename Extensions”.
A utility for interactively setting permissions in the MySQL grant tables. See Section 4.6.16, “mysql_setpermission — Interactively Set Permissions in Grant Tables”.
A utility that generates database metadata. Section 4.6.17, “mysql_tableinfo — Generate Database Metadata”.
A utility that kills the process with a given process ID. See Section 4.6.18, “mysql_waitpid — Kill Process and Wait for Its Termination”.
A utility that kills processes that match a pattern. See Section 4.6.19, “mysql_zap — Kill Processes That Match a Pattern”.
MySQL program-development utilities:
msql2mysql
A shell script that converts mSQL programs
to MySQL. It doesn't handle every case, but it gives a good
start when converting.
mysql_config
A shell script that produces the option values needed when compiling MySQL programs.
A utility that shows which options are present in option groups of option files. See Section 4.7.3, “my_print_defaults — Display Options from Option Files”.
A utility program that resolves a numeric stack trace dump to symbols. See Section 4.7.4, “resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols”.
Miscellaneous utilities:
A utility that displays the meaning of system or MySQL error codes. See Section 4.8.1, “perror — Explain Error Codes”.
A utility program that performs string replacement in the input text. See Section 4.8.2, “replace — A String-Replacement Utility”.
A utility program that resolves a hostname to an IP address or vice versa. See Section 4.8.3, “resolveip — Resolve Hostname to IP Address or Vice Versa”.
MySQL AB also provides several GUI tools for administering and otherwise working with MySQL Server:
MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and user accounts.
MySQL Query Browser: This graphical tool is provided by MySQL AB for creating, executing, and optimizing queries on MySQL databases.
MySQL Migration Toolkit: This tool helps you migrate schemas and data from other relational database management systems for use with MySQL.
These GUI programs are available at http://dev.mysql.com/downloads/. Each has its own manual that you can access at http://dev.mysql.com/doc/.
MySQL client programs that communicate with the server using the MySQL client/server library use the following environment variables:
MYSQL_UNIX_PORT | The default Unix socket file; used for connections to
localhost |
MYSQL_TCP_PORT | The default port number; used for TCP/IP connections |
MYSQL_PWD | The default password |
MYSQL_DEBUG | Debug trace options when debugging |
TMPDIR | The directory where temporary tables and files are created |
For a full list of environment variables used by MySQL programs, see Section 2.4.20, “Environment Variables”.
Use of MYSQL_PWD is insecure. See
Section 5.5.6, “Keeping Your Password Secure”.