This lesson provides a quick reference for the key programs, options and functions of the key MySQL tools. We discuss the server mysqld, the command interpreter mysql, and the administration tools mysqladmin, myisamchk, etc.
MySQL server provides several varieties of programs:
- The MySQL server and server startup scripts:
- mysqld starts the MySQL database server.
- mysqld_safe, mysql.server, and mysqld_multi are server startup scripts.
- mysql_install_db initializes the data directory and the initial databases.
- MySQL Instance Manager monitors and manages MySQL server instances.
- Client programs that access the server:
- mysql is a command-line client for executing SQL statements interactively or in batch mode.
- mysqladmin is an administrative client.
- mysqlcheck performs table maintenance operations.
- mysqldump and mysqlhotcopy make database backups.
- mysqlimport imports data files.
- mysqlshow displays information about databases and tables.
- Utility programs that operate independently of the server:
- myisamchk performs table maintenance operations.
- myisampack produces compressed, read-only tables.
- mysqlbinlog is a tool for processing binary log files.
- my_print_defaults print option values from options files.
- perror displays the meaning of error codes.
Note: Generally, most MySQL distributions include all of these programs, except for programs that are platform-specific such as server startup scripts. The distributions for different operating systems do not look alike; for example, the RPM distributions are more specialized - for server, for client and so forth.
The following table provides an overview of the commands discussed in this section.
|MySQL Server and Included Administrative Tools|
|mysqld||Is the actual MySQL server. The program is usually not started directly, but under Windows as a system service and under Unix/Linux via an Init-V script with the help of mysqld_safe .|
|mysqld_safe||Should be used under Unix/Linux for a secure server launch.|
|mysql||Client-tool that enables interactive execution of SQL commands.|
|mysqladmin||Assists in various administrative tasks (display status, reinput privileges, execute shutdown, etc.).|
|mysqldump||Saves contents of a MySQL database in a text file.|
|mysqlimport||Inputs data into a table from a text file.|
|mysqlshow||Displays information on databases, tables, and columns.|
|myisamchk||Checks the integrity of MyISAM table files and repairs them as necessary.|
|myisampack||Compresses MyISAM table files for more efficient read-only access.|
Common Options to Commands
Various options can be passed to all commands when they are executed. As is usual with Unix/Linux, commands can be prefixed with a hyphen (short form) or two hyphens (full option name). Please note that the short forms of options are case-sensitive.
The common feature exhibited by the commands introduced in this section is that they are launched as external programs in a command window (Windows) or in a command shell (Unix/Linux). The entire operation of these programs is carried out in text mode and is therefore not what one would term excessively convenient. However, these commands are very well suited for execution in scripts in the automation of administrative tasks.
|Common Options of the MySQL Server (mysqld) and the MySQL Client Tools (mysql, mysqladmin, mysqldump, mysqlimport, etc.)|
|--help||Displays a brief operation introduction.|
|--print-defaults||Displays default values for options; default values can come from configuration files or system variables.|
|--nodefaults||Causes no configuration files to be read at startup.|
|--defaults-file=filename||Causes only this configuration file to be read at startup.|
|--defaults-extra-file=filename||First the global configuration file is read, and then filename, and finally (only under Unix/Linux), the user-specific configuration file.|
|--port=n||Specifies the TCP/IP port over which communication takes place (usually 3306).|
|--version||Displays the version number of the program.|
Here are some options that are common to various MySQL client-side programs:
|Common Options of the MySQL Client Tools|
|-u <username>||--user=username||Determines the user name for registration with MySQL.|
|-p||--password||Asks for input of password immediately after start of the command.|
|-p<password>||--password=xxx||Passes the password directly; in contrast to other options, there can be no space after - p; this is more convenient than interactive input of the password, but it represents a considerable security risk and thus should generally be avoided. Under some operating systems, any user can see the password by looking at the process list.|
|-h hostname||--host=hostname||Supply the name or IP number of the computer on which the server is running (assumed by default to be localhost, that is, the local computer).|
OS> mysql -h staginghr -u username -p
Enter Password: xxxxxx
For a connection to the MySQL server to be at all possible, the following two options are generally used at the start of each client command:
OS> mysql -u username -p
Enter Password: xxxxxx
If MySQL is not yet password-secured, then this will work without a password being specified. We discuss users and setting password for root in other lessons.
If you execute MySQL commands under Windows and create a directory with options, then instead of the backslash you should use the forward slash (/). If the file name contains space characters, then put the entire path in quotation marks, as in the following example: --basedir="C:/Programs/MySQL/MySQL Server 5.0/".
There are several ways to specify options for MySQL programs:
- Command Line: List the options on the command line following the program name. This allows for options to be appled to a specific invocation of the program.
- Options File: List the options in an option file that the program reads when it starts. This helps by setting options in one place, one time that we want programs to use each time they run.
- Environment Variables: Set the options via environment variables at the OS level. This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose.
Options on the Command Line
Program options specified on the command line follow these rules:
- Options are given after the command name.
- An option argument begins with one dash or two dashes, depending on whether it has a short name or a long name. Many options have both forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its help message.
- Option names are very case sensitive. -v and -V are both legal and have different meanings. (They are the corresponding short forms of the --verbose and --version options.)
- Some options follow their name = a value. For example, -h localhost or --host=localhost indicate the MySQL server host to a client program. The option value tells the program the name of the host where the MySQL server is running.
- For a long option that takes a value, separate the option name and the value by an = sign.
- For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as --password=pass_val or as --password. In the latter case (with no password value given), the program prompts you for the password. The password option also may be given in short form as -ppass_val or as -p. However, for the short form, if the password value is given, it must follow the option letter with no intervening space. The reason for this is that if a space follows the option letter, the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument. Consequently, the following two commands have two completely different meanings:
mysql -usakilaadmin -psakila
mysql -usakilaadmin -p sakila
The first command instructs mysql to use sakila as password and specifies no default database.
The second command causes mysql to prompt for the password value and to use sakila as the default database.
We will see more on setting options in later lessons.
my_print_defaults: Display options from files
The my_print_defaults program displays the options that are present in the various option groups of option files. The output indicates what options will be used by programs that read the specified option groups.
For example, the mysqlcheck program reads the [mysqlcheck] and [client] option groups. The output consists of options, one per line, in the form that they would be specified on the command line. To see what options are present in those groups in the standard option files, invoke my_print_defaults like this:
my_print_defaults mysqlcheck client
List options present in the client group:shell> my_print_defaults mysqlcheck client
Here are the options one can use with the my_print_defaults program:
- --help, -?:
Display a help message and exit.
- --config-file=file_name,--defaults-file=file_name,-c file_name:
Read only the given option file.
- --debug=debug_options, -#debug_options: Write a debugging log. The debug_options string often is 'd:t:o,file_name'.
The default is 'd:t:o,/tmp/my_print_defaults.trace'.
- --defaults-extra-file=file_name, --extra-file=file_name, -e file_name :
Read this option file after the global option file but (on Unix) before the user option file.
- --defaults-group-suffix=suffix,-g suffix:
In addition to the groups named on the command line, read groups that have the given suffix.
- --no-defaults, -n:
Return an empty string.
- --verbose, -?, -v:
Verbose mode. Print more information about what the program does.
- --version, -V:
Display version information and exit.
my_print_defaults --debug=d:t:o,my_print_defaults.trace --verbose mysqlcheck client
List options present in the client group in a trace file.
perror: Explain Error Codes
For most system errors, MySQL displays, in addition to an internal text message, the system error code in one of the following styles:
message ... (errno: #)
message ... (Errcode: #)
You can discover the meaning of the error code by viewing your system's documentation for or by using the perror utility. The perror program prints a description for a system error code or for a storage engine (table handler) error code.
The syntax of perror is shown below with an example:
shell> perror [options] errorcode(s)
perror 12 41 42;
This results in the following:
OS error code 12: Not enough space
OS error code 41: Directory not empty
OS error code 42: Illegal byte sequence
Note: The meaning of system error messages may be dependent on your operating system. It is possible for a given error code to have different meanings on different operating systems.
Here are the options supported by the perror command:
- --help, --info,-I, -?: Display a help message and exit.
- --ndb: Print the error message for a MySQL Cluster error code.
- --silent, -s: Silent mode. Print only the error message.
- --verbose, -v: Verbose mode. Print error code and message (default behavior).
- --version, -V: Display version information and exit.
MySQL Programs and Executables Conclusion
In this lesson of the MySQL tutorial, we took a quick tour of MySQL components.