MySQL Programs - MariaDB - Databases - Software - Computers


MySQL Programs
Prev Next

MySQL Programs

Table of Contents

Overview of MariaDB Programs
Using MariaDB Programs
Invoking MariaDB Programs
Connecting to the MariaDB Server
Specifying Program Options
Setting Environment Variables
MySQL Server and Server-Startup Programs
mysqld - The MariaDB Server
mysqld_safe - MariaDB Server Startup Script
mysql.server - MariaDB Server Startup Script
mysqld_multi - Manage Multiple MariaDB Servers
MySQL Installation-Related Programs
comp_err - Compile MariaDB Error Message File
mysqlbug - Generate Bug Report
mysql_install_db - Initialize MariaDB Data Directory
mysql_plugin - Configure MariaDB Server Plugins
mysql_secure_installation - Improve MariaDB Installation Security
mysql_tzinfo_to_sql - Load the Time Zone Tables
mysql_upgrade - Check Tables for MariaDB Upgrade
MySQL Client Programs
mysql - The MariaDB Command-Line Tool
mysqladmin - Client for Administering a MariaDB Server
mysqlcheck - A Table Maintenance Program
mysqldump - A Database Backup Program
mysqlimport - A Data Import Program
mysqlshow - Display Database, Table, and Column Information
mysqlslap - Load Emulation Client
MySQL Administrative and Utility Programs
innochecksum - Offline InnoDB File Checksum Utility
myisam_ftdump - Display Full-Text Index information
myisamchk - MyISAM Table-Maintenance Utility
myisamlog - Display MyISAM Log File Contents
myisampack - Generate Compressed, Read-Only MyISAM Tables
mysqlaccess - Client for Checking Access Privileges
mysqlbinlog - Utility for Processing Binary Log Files
mysqldumpslow - Summarize Slow Query Log Files
mysqlhotcopy - A Database Backup Program
mysql_convert_table_format - Convert Tables to Use a Given Storage Engine
mysql_find_rows - Extract SQL Statements from Files
mysql_fix_extensions - Normalize Table File Name Extensions
mysql_setpermission - Interactively Set Permissions in Grant Tables
mysql_waitpid - Kill Process and Wait for Its Termination
mysql_zap - Kill Processes That Match a Pattern
MySQL Program Development Utilities
msql2mysql - Convert mSQL Programs for Use with MySQL
mysql_config - Get Compile Options for Compiling Clients
my_print_defaults - Display Options from Option Files
resolve_stack_dump - Resolve Numeric Stack Trace Dump to Symbols
Miscellaneous Programs
perror - Explain Error Codes
replace - A String-Replacement Utility
resolveip - Resolve Host name to IP Address or Vice Versa

This chapter provides a brief overview of the MariaDB command-line programs provided by Oracle Corporation. 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.

Overview of MariaDB Programs

There are many different programs in a MariaDB installation. This section provides a brief overview of them. Later sections provide a more detailed description of each one. Each program's description indicates its invocation syntax and the options that it supports.

Most MariaDB 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 , 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 MariaDB 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 MariaDB programs by specifying options on the command line or in an option file. See , "Using MariaDB Programs", for general information on invoking programs and specifying program options.

The MariaDB server, mysqld, is the main program that does most of the work in a MariaDB installation. The server is accompanied by several related scripts that assist you in starting and stopping the server:

Several programs perform setup operations during MariaDB installation or upgrading:

MySQL client programs that connect to the MariaDB server:

MySQL administrative and utility programs:

MySQL program-development utilities:

Miscellaneous utilities:

Oracle Corporation also provides several GUI tools for administering and otherwise working with MariaDB Server:

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 MariaDB client/server library use the following environment variables.

Environment Variable Meaning
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 MariaDB programs, see , "Environment Variables".

Use of MYSQL_PWD is insecure. See , "End-User Guidelines for Password Security".

Using MariaDB Programs

Invoking MariaDB Programs
Connecting to the MariaDB Server
Specifying Program Options
Setting Environment Variables

Invoking MariaDB Programs

To invoke a MariaDB program from the command line (that is, from your shell or command prompt), enter the program name followed by any options or other arguments needed to instruct the program what you want it to do. The following commands show some sample program invocations. "shell>" represents the prompt for your command interpreter; it is not part of what you type. The particular prompt you see depends on your command interpreter. Typical prompts are $ for sh, ksh, or bash, % for csh or tcsh, and C:\> for the Windows command.com or cmd.exe command interpreters. 

shell> mysql --user=root test
shell> mysqladmin extended-status variables
shell> mysqlshow --help
shell> mysqldump -u root personnel

Arguments that begin with a single or double dash ("-", "--") specify program options. Options typically indicate the type of connection a program should make to the server or affect its operational mode. Option syntax is described in , "Specifying Program Options".

Nonoption arguments (arguments with no leading dash) provide additional information to the program. For example, the mysql program interprets the first nonoption argument as a database name, so the command mysql --user=root test indicates that you want to use the test database.

Later sections that describe individual programs indicate which options a program supports and describe the meaning of any additional nonoption arguments.

Some options are common to a number of programs. The most frequently used of these are the --host (or -h), --user (or -u), and --password (or -p) options that specify connection parameters. They indicate the host where the MariaDB server is running, and the user name and password of your MariaDB account. All MariaDB client programs understand these options; they enable you to specify which server to connect to and the account to use on that server. Other connection options are --port (or -P) to specify a TCP/IP port number and --socket (or -S) to specify a Unix socket file on Unix (or named pipe name on Windows). For more information on options that specify connection options, see , "Connecting to the MariaDB Server".

You may find it necessary to invoke MariaDB programs using the path name to the bin directory in which they are installed. This is likely to be the case if you get a "program not found" error whenever you attempt to run a MariaDB program from any directory other than the bin directory. To make it more convenient to use MySQL, you can add the path name of the bin directory to your PATH environment variable setting. That enables you to run a program by typing only its name, not its entire path name. For example, if mysql is installed in /usr/local/mysql/bin, you can run the program by invoking it as mysql, and it is not necessary to invoke it as /usr/local/mysql/bin/mysql.

Consult the documentation for your command interpreter for instructions on setting your PATH variable. The syntax for setting environment variables is interpreter-specific. (Some information is given in , "Setting Environment Variables".) After modifying your PATH setting, open a new console window on Windows or log in again on Unix so that the setting goes into effect.

Connecting to the MariaDB Server

For a client program to be able to connect to the MariaDB server, it must use the proper connection parameters, such as the name of the host where the server is running and the user name and password of your MariaDB account. Each connection parameter has a default value, but you can override them as necessary using program options specified either on the command line or in an option file.

The examples here use the mysql client program, but the principles apply to other clients such as mysqldump, mysqladmin, or mysqlshow.

This command invokes mysql without specifying any connection parameters explicitly: 

shell> MariaDB

Because there are no parameter options, the default values apply:

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line: 

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

For password options, the password value is optional:

As just mentioned, including the password value on the command line can be a security risk. To avoid this problem, specify the --password or -p option without any following password value: 

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

When the password option has no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, mydb is not interpreted as a password because it is separated from the preceding password option by a space.)

On some systems, the library routine that MariaDB uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MariaDB does not have any limit for the length of the password. To work around the problem, change your MariaDB password to a value that is eight or fewer characters long, or put your password in an option file.

On Unix, MariaDB programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MariaDB programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example: 

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP

The --protocol option enables you to establish a particular type of connection even when the other options would normally default to some other protocol.

On Windows, you can force a MariaDB client to use a named-pipe connection by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. If named-pipe connections are not enabled, an error occurs. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

Connections to remote servers always use TCP/IP. This command connects to the server running on remote.example.com using the default port number (3306): 

shell> mysql --host=remote.example.com

To specify a port number explicitly, use the --port or -P option: 

shell> mysql --host=remote.example.com --port=13306

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix will use a socket file by default. You will need to force a TCP/IP connection as already described or any option that specifies a port number will be ignored.

For this command, the program uses a socket file on Unix and the --port option is ignored: 

shell> mysql --port=13306 --host=localhost

To cause the port number to be used, invoke the program in either of these ways: 

shell> mysql --port=13306 --host=127.0.0.1
shell> mysql --port=13306 --protocol=TCP

The following list summarizes the options that can be used to control how client programs connect to the server:

It is possible to specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:

Specifying Program Options

Using Options on the Command Line
Program Option Modifiers
Using Option Files
Using Options to Set Program Variables
Option Defaults, Options Expecting Values, and the = Sign

There are several ways to specify options for MariaDB programs:

Options are processed in order, so if an option is specified multiple times, the last occurrence takes precedence. The following command causes mysql to connect to the server running on localhost: 

shell> mysql -h example.com -h localhost

If conflicting or related options are given, later options take precedence over earlier options. The following command runs mysql in "no column names" mode: 

shell> mysql --column-names --skip-column-names

MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.

You can take advantage of the way that MariaDB programs process options by specifying default option values for a program in an option file. That enables you to avoid typing them each time you run the program while enabling you to override the defaults if necessary by using command-line options.

An option can be specified by writing it in full or as any unambiguous prefix. For example, the --compress option can be given to mysqldump as --compr, but not as --comp because the latter is ambiguous: 

shell> mysqldump --comp
mysqldump: ambiguous option '--comp' (compatible, compress)

Be aware that the use of option prefixes can cause problems in the event that new options are implemented for a program. A prefix that is unambiguous now might become ambiguous in the future.

Using Options on the Command Line

Program options specified on the command line follow these rules:

Option values that contain spaces must be quoted when given on the command line. For example, the --execute (or -e) option can be used with mysql to pass SQL statements to the server. When this option is used, mysql executes the statements in the option value and exits. The statements must be enclosed by quotation marks. For example, you can use the following command to obtain a list of user accounts: 

mysql> mysql -u root -p --execute='SELECT User, Host FROM mysql.user'
Enter password: ******
+------+-----------+
| User | Host |
+------+-----------+
| | gigan |
| root | gigan |
| | localhost |
| jon | localhost |
| root | localhost |
+------+-----------+
shell>

Note that the long form (--execute) is followed by an equals sign (=).

If you wish to use quoted values within a statement, you will either need to escape the inner quotation marks, or use a different type of quotation marks within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotation marks, you can use double quotation marks around the statement, and single quotation marks for any quoted values within the statement.

Multiple SQL statements may be passed in the option value on the command line, separated by semicolons: 

shell> mysql -u root -p -e 'SELECT VERSION();SELECT NOW()'
Enter password: ******
+-----------------+
| VERSION() |
+-----------------+
| 5.1.5-alpha-log |
+-----------------+
+---------------------+
| NOW() |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

Program Option Modifiers

Some options are "boolean" and control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms: 

--disable-column-names
--skip-column-names
--column-names=0

The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The "enabled" form of the option may be specified in any of these ways: 

--column-names
--enable-column-names
--column-names=1

As of MariaDB 5.6.2, the values ON, TRUE, OFF, and FALSE are also recognized for boolean options (not case sensitive).

If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning: 

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MariaDB on the same machine and list options in an option file, An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

mysqld enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a --maximum prefix with the variable name. For example, --maximum-query_cache_size=4M prevents any client from making the query cache size larger than 4MB.

Using Option Files

Command-Line Options that Affect Option-File Handling
Preconfigured Option Files

Most MariaDB programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MariaDB server, MariaDB provides a number of preconfigured option files.

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

On Windows, MariaDB programs read startup options from the following files, in the specified order (top items are used first).

File Name Purpose
WINDIR\my.ini, WINDIR\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
INSTALLDIR\my.ini, INSTALLDIR\my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file=path, if any

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable using the following command: 

C:\> echo %WINDIR%

INSTALLDIR represents the MariaDB installation directory. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.6 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MariaDB 5.6 has been installed using the installation and configuration wizards. See , "Starting the MariaDB Server Instance Configuration Wizard".

On Unix, Linux and Mac OS X, MariaDB programs read startup options from the following files, in the specified order (top items are used first).

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options

~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MariaDB was built. By default, this is the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld-safe program, mysqld_safe attempts to set MYSQL_HOME as follows:

In MariaDB 5.6, use of DATADIR as the location for my.cnf is deprecated.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.Note

On Unix platforms, MariaDB ignores configuration files that are world-writable. This is intentional as a security measure.

Any long option that may be given on the command line when running a MariaDB program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax (see , "Using Options on the Command Line"). However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

Empty lines in option files are ignored. Nonempty lines can take any of the following forms:

Leading and trailing spaces are automatically deleted from option names and values.

You can use the escape sequences "\b", "\t", "\n", "\r", "\\", and "\s" in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters. The escaping rules in option files are:

The preceding rules mean that a literal backslash can be given as "\\", or as "\" if it is not followed by a valid escape sequence character.

The rules for escape sequences in option files differ slightly from the rules for escape sequences in string literals in SQL statements. In the latter context, if "x" is not a value escape sequence character, "\x" becomes "x" rather than "\x". See , "String Literals".

The escaping rules for option file values are especially pertinent for Windows path names, which use "\" as a path name separator. A separator in a Windows path name must be written as "\\" if it is followed by an escape sequence character. It can be written as "\\" or "\" if it is not. Alternatively, "/" may be used in Windows path names and will be treated as "\". Suppose that you want to specify a base directory of C:\Program Files\MySQL\MySQL Server 5.6 in an option file. This can be done several ways. Some examples: 

basedir='C:\Program Files\MySQL\MySQL Server 5.6'
basedir='C:\\Program Files\\MySQL\\MySQL Server 5.6'
basedir='C:/Program Files/MySQL/MySQL Server 5.6'
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.6

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

Here is a typical global option file: 

[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=8M
[mysqldump]
quick

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

Here is a typical user option file: 

[client]
# The following password will be sent to all standard MariaDB clients password='my_password'
[mysql]
no-auto-rehash connect_timeout=2
[mysqlhotcopy]
interactive-timeout

If you want to create option groups that should be read by mysqld servers from a specific MariaDB release series only, you can do this by using groups with names of [mysqld-5.5], [mysqld-5.6], and so forth. The following group indicates that the --new option should be used only by MariaDB servers with 5.6.x version numbers: 

[mysqld-5.6]
new

It is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, use the following directive: 

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive: 

!includedir /home/mydir

There is no guarantee about the order in which the option files in the directory will be read.Note

Currently, any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

Write the contents of an included option file like any other option file. That is, it should contain groups of options, each preceded by a [group] line that indicates the program to which the options apply.

While an included file is being processed, only those options in groups that the current program is looking for are used. Other groups are ignored. Suppose that a my.cnf file contains this line: 

!include /home/mydir/myopt.cnf

And suppose that /home/mydir/myopt.cnf looks like this: 

[mysqladmin]
force
[mysqld]
key_buffer_size=16M

If my.cnf is processed by mysqld, only the [mysqld] group in /home/mydir/myopt.cnf is used. If the file is processed by mysqladmin, only the [mysqladmin] group is used. If the file is processed by any other program, no options in /home/mydir/myopt.cnf are used.

The !includedir directive is processed similarly except that all option files in the named directory are read.

Command-Line Options that Affect Option-File Handling

Most MariaDB programs that support option files handle the following options. They affect option-file handling, so they must be given on the command line and not in an option file. To work properly, each of these options must immediately follow the command name, with these exceptions:

When specifying file names, you should avoid the use of the "~" shell metacharacter because it might not be interpreted as you expect.

Preconfigured Option Files

MySQL provides a number of preconfigured option files that can be used as a basis for tuning the MariaDB server. Look for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which are sample option files for small, medium, large, and very large systems. On Windows, the extension is .ini rather than .cnf.Note

On Windows, the .ini or .cnf option file extension might not be displayed.

For a binary distribution, look for the files in or under your installation directory. If you have a source distribution, look in the support-files directory. You can rename a copy of a sample file and place it in the appropriate location for use as a base configuration file. Regarding names and appropriate location, see the general information provided in , "Using Option Files".

Using Options to Set Program Variables

Many MariaDB programs have internal variables that can be set at runtime using the SET statement. See , "SET Syntax", and , "Using System Variables".

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands: 

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes: 

[mysql]
max_allowed_packet=16777216

Or: 

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB: 

[mysqld]
key_buffer_size=512M
[mysqld]
key-buffer-size=512M

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the max_allowed_packet variable can be set for mysql as --max_a, but not as --max because the latter is ambiguous: 

shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)

Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambiguous now might become ambiguous in the future.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not: 

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not: 

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

Option Defaults, Options Expecting Values, and the = Sign

By convention, long forms of options that assign a value are written with an equals (=) sign, like this: 

shell> mysql --host=tonfisk --user=jon

For options that require a value (that is, not having a default value), the equals sign is not required, and so the following is also valid: 

shell> mysql --host tonfisk --user jon

In both cases, the mysql client attempts to connect to a MariaDB server running on the host named "tonfisk" using an account with the user name "jon".

Due to this behavior, problems can occasionally arise when no value is provided for an option that expects one. Consider the following example, where a user connects to a MariaDB server running on host tonfisk as user jon: 

shell> mysql --host 85.224.35.45 --user jon
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.6.6 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| jon@% |
+----------------+
1 row in set (0.00 sec)

Omitting the required value for one of these option yields an error, such as the one shown here: 

shell> mysql --host 85.224.35.45 --user
mysql: option '--user' requires an argument

In this case, mysql was unable to find a value following the --user option because nothing came after it on the command line. However, if you omit the value for an option that is not the last option to be used, you obtain a different error that you may not be expecting: 

shell> mysql --host --user jon
ERROR 2005 (HY000): Unknown MariaDB server host '--user' (1)

Because mysql assumes that any string following --host on the command line is a host name, --host --user is interpreted as --host=--user, and the client attempts to connect to a MariaDB server running on a host named "--user".

Options having default values always require an equals sign when assigning a value; failing to do so causes an error. For example, the MariaDB server --log-error option has the default value host_name.err, where host_name is the name of the host on which MariaDB is running. Assume that you are running MariaDB on a computer whose host name is "tonfisk", and consider the following invocation of mysqld_safe: 

shell> mysqld_safe &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var shell>

After shutting down the server, restart it as follows: 

shell> mysqld_safe --log-error &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var shell>

The result is the same, since --log-error is not followed by anything else on the command line, and it supplies its own default value. (The & character tells the operating system to run MariaDB in the background; it is ignored by MariaDB itself.) Now suppose that you wish to log errors to a file named my-errors.err. You might try starting the server with --log-error my-errors, but this does not have the intended effect, as shown here: 

shell> mysqld_safe --log-error my-errors &
[1] 31357
shell> 080111 22:53:31 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080111 22:53:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended
[1]+ Done ./mysqld_safe --log-error my-errors

The server attempted to start using /usr/local/mysql/var/tonfisk.err as the error log, but then shut down. Examining the last few lines of this file shows the reason: 

shell> tail /usr/local/mysql/var/tonfisk.err
080111 22:53:32 InnoDB: Started; log sequence number 0 46409
/usr/local/mysql/libexec/mysqld: Too many arguments (first extra is 'my-errors').
Use --verbose --help to get a list of available options
080111 22:53:32 [ERROR] Aborting
080111 22:53:32 InnoDB: Starting shutdown...
080111 22:53:34 InnoDB: Shutdown completed; log sequence number 0 46409
080111 22:53:34 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended

Because the --log-error option supplies a default value, you must use an equals sign to assign a different value to it, as shown here: 

shell> mysqld_safe --log-error=my-errors &
[1] 31437
shell> 080111 22:54:15 mysqld_safe Logging to '/usr/local/mysql/var/my-errors.err'.
080111 22:54:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var shell>

Now the server has been started successfully, and is logging errors to the file /usr/local/mysql/var/my-errors.err.

Similar issues can arise when specifying option values in option files. For example, consider a my.cnf file that contains the following: 

[mysql]
host user

When the mysql client reads this file, these entries are parsed as --host --user or --host=--user, with the result shown here: 

shell> MariaDB
ERROR 2005 (HY000): Unknown MariaDB server host '--user' (1)

However, in option files, an equals sign is not assumed. Suppose the my.cnf file is as shown here: 

[mysql]
user jon

Trying to start mysql in this case causes a different error: 

shell> MariaDB
mysql: unknown option '--user jon'

A similar error would occur if you were to write host tonfisk in the option file rather than host=tonfisk. Instead, you must use the equals sign: 

[mysql]
user=jon

Now the login attempt succeeds: 

shell> MariaDB
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.6.6 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT USER();
+---------------+
| USER() |
+---------------+
| jon@localhost |
+---------------+
1 row in set (0.00 sec)

This is not the same behavior as with the command line, where the equals sign is not required: 

shell> mysql --user jon --host tonfisk
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.6.6 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT USER();
+---------------+
| USER() |
+---------------+
| jon@tonfisk |
+---------------+
1 row in set (0.00 sec)

In MariaDB 5.6, specifying an option requiring a value without a value in an option file causes the server to abort with an error. Suppose that my.cnf contains the following: 

[mysqld]
log_error relay_log relay_log_index

This causes the server to fail on startup, as shown here: 

shell> mysqld_safe &
090514 09:48:39 mysqld_safe Logging to '/home/jon/bin/mysql-5.5/var/tonfisk.err'.
090514 09:48:39 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql-5.5/var
090514 09:48:39 mysqld_safe mysqld from pid file /home/jon/bin/mysql-5.5/var/tonfisk.pid ended

The --log-error option does not require an argument; however, the --relay-log option requires one, as shown in the error log (which in the absence of a specified value, defaults to datadir/hostname.err): 

shell> tail -n 3 ../var/tonfisk.err
090514 09:48:39 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql-5.5/var
090514 9:48:39 [ERROR] /home/jon/bin/mysql-5.5/libexec/mysqld: option '--relay-log' requires an argument
090514 9:48:39 [ERROR] Aborting

This is a change from previous behavior, where the server would have interpreted the last two lines in the example my.cnf file as --relay-log=relay_log_index and created a relay log file using "relay_log_index" as the basename. (Bug #25192)

Setting Environment Variables

Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. , "Environment Variables", lists all environment variables that affect MariaDB program operation.

To specify a value for an environment variable, use the syntax appropriate for your command processor. For example, on Windows, you can set the USER variable to specify your MariaDB account name. To do so, use this syntax: 

SET USER=your_name

The syntax on Unix depends on your shell. Suppose that you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable. Typical syntax (such as for sh, ksh, bash, zsh, and so on) is as follows: 

MYSQL_TCP_PORT=3306
export MYSQL_TCP_PORT

The first command sets the variable, and the export command exports the variable to the shell environment so that its value becomes accessible to MariaDB and other processes.

For csh and tcsh, use setenv to make the shell variable available to the environment: 

setenv MYSQL_TCP_PORT 3306

The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, use the interface provided by your system or place the appropriate command or commands in a startup file that your command interpreter reads each time it starts.

On Windows, you can set environment variables using the System Control Panel (under Advanced).

On Unix, typical shell startup files are .bashrc or .bash_profile for bash, or .tcshrc for tcsh.

Suppose that your MariaDB programs are installed in /usr/local/mysql/bin and that you want to make it easy to invoke these programs. To do this, set the value of the PATH environment variable to include that directory. For example, if your shell is bash, add the following line to your .bashrc file: 

PATH=${PATH}:/usr/local/mysql/bin

bash uses different startup files for login and nonlogin shells, so you might want to add the setting to .bashrc for login shells and to .bash_profile for nonlogin shells to make sure that PATH is set regardless.

If your shell is tcsh, add the following line to your .tcshrc file: 

setenv PATH ${PATH}:/usr/local/mysql/bin

If the appropriate startup file does not exist in your home directory, create it with a text editor.

After modifying your PATH setting, open a new console window on Windows or log in again on Unix so that the setting goes into effect.

MySQL Server and Server-Startup Programs

mysqld - The MariaDB Server
mysqld_safe - MariaDB Server Startup Script
mysql.server - MariaDB Server Startup Script
mysqld_multi - Manage Multiple MariaDB Servers

This section describes mysqld, the MariaDB server, and several programs that are used to start the server.

mysqld - The MariaDB Server

mysqld, also known as MariaDB Server, is the main program that does most of the work in a MariaDB installation. MariaDB Server manages access to the MariaDB data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.

When MariaDB server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.

The mysqld program has many options that can be specified at startup. For a complete list of options, run this command: 

shell> mysqld --verbose --help

MySQL Server also has a set of system variables that affect its operation as it runs. System variables can be set at server startup, and many of them can be changed at runtime to effect dynamic server reconfiguration. MariaDB Server also has a set of status variables that provide information about its operation. You can monitor these status variables to access runtime performance characteristics.

For a full description of MariaDB Server command options, system variables, and status variables, see , "The MariaDB Server". For information about installing MariaDB and setting up the initial configuration, see , Installing and Upgrading MySQL.

mysqld_safe - MariaDB Server Startup Script

mysqld-safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. A description of error logging is given later in this section.

mysqld-safe tries to start an executable named mysqld. To override the default behavior and specify explicitly the name of the server you want to run, specify a --mysqld or --mysqld-version option to mysqld_safe. You can also use --ledir to indicate the directory where mysqld_safe should look for the server.

Many of the options to mysqld-safe are the same as the options to mysqld. See , "Server Command Options".

Options unknown to mysqld-safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See , "Using Option Files".

mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe] sections in option files. For example, if you specify a [mysqld] section like this, mysqld_safe will find and use the --log-error option: 

[mysqld]
log-error=error.log

For backward compatibility, mysqld_safe also reads [safe_mysqld] sections, although you should rename such sections to [mysqld_safe] in MariaDB 5.6 installations.

mysqld_safe supports the following options. It also reads option files and supports the options for processing them described at , "Command-Line Options that Affect Option-File Handling".

Table 4.1. mysqld_safe Options

Format Option File Description Introduction Deprecated Removed
--basedir=path basedir The path to the MariaDB installation directory
--core-file-size=size core-file-size The size of the core file that mysqld should be able to create
--datadir=path datadir The path to the data directory
--defaults-extra-file=path defaults-extra-file The name of an option file to be read in addition to the usual option files
--defaults-file=file_name defaults-file The name of an option file to be read instead of the usual option files
--help Display a help message and exit
--ledir=path ledir Use this option to indicate the path name to the directory where the server is located
--log-error=file_name log-error Write the error log to the given file
--malloc-lib=[lib-name] malloc-lib Alternative malloc library to use for mysqld
--mysqld=prog_name mysqld The name of the server program (in the ledir directory) that you want to start
--mysqld-version=suffix mysqld-version This option is similar to the --mysqld option, but you specify only the suffix for the server program name
--nice=priority nice Use the nice program to set the server's scheduling priority to the given value
--no-defaults no-defaults Do not read any option files
--open-files-limit=count open-files-limit The number of files that mysqld should be able to open
--pid-file=file_name pid-file=file_name The path name of the process ID file
--port=number port The port number that the server should use when listening for TCP/IP connections
--skip-kill-mysqld skip-kill-mysqld Do not try to kill stray mysqld processes
--skip-syslog skip-syslog Do not write error messages to syslog; use error log file
--socket=path socket The Unix socket file that the server should use when listening for local connections
--syslog syslog Write error messages to syslog
--timezone=timezone timezone Set the TZ time zone environment variable to the given option value
--user={user_name|user_id} user Run the mysqld server as the user having the name user_name or the numeric user ID user_id

If you execute mysqld-safe with the --defaults-file or --defaults-extra-file option to name an option file, the option must be the first one given on the command line or the option file will not be used. For example, this command will not use the named option file: 

mysql> mysqld_safe --port=port_num --defaults-file=file_name

Instead, use the following command: 

mysql> mysqld_safe --defaults-file=file_name --port=port_num

The mysqld_safe script is written so that it normally can start a server that was installed from either a source or a binary distribution of MySQL, even though these types of distributions typically install the server in slightly different locations. (See , "Installation Layouts".) mysqld_safe expects one of the following conditions to be true:

Because mysqld_safe tries to find the server and databases relative to its own working directory, you can install a binary distribution of MariaDB anywhere, as long as you run mysqld_safe from the MariaDB installation directory: 

shell> cd mysql_installation_directory
shell> bin/mysqld_safe &

If mysqld_safe fails, even when invoked from the MariaDB installation directory, you can specify the --ledir and --datadir options to indicate the directories in which the server and databases are located on your system.

In MariaDB 5.6.5 and later, mysqld_safe tries to use the sleep and date system utilities to determine how many times it has attempted to start this second, and-if these are present and this is greater than 5 times-is forced to wait 1 full second before starting again. This is intended to prevent excessive CPU usage in the event of repeated failures. (Bug #11761530, Bug #54035)

When you use mysqld-safe to start mysqld, mysqld_safe arranges for error (and notice) messages from itself and from mysqld to go to the same destination.

There are several mysqld_safe options for controlling the destination of these messages:

If none of these options is given, the default is --skip-syslog.

If --syslog and --log-error are both given, a warning is issued and --log-error takes precedence.

When mysqld_safe writes a message, notices go to the logging destination (syslog or the error log file) and stdout. Errors go to the logging destination and stderr.

Normally, you should not edit the mysqld-safe script. Instead, configure mysqld_safe by using command-line options or options in the [mysqld_safe] section of a my.cnf option file. In rare cases, it might be necessary to edit mysqld_safe to get it to start the server properly. However, if you do this, your modified version of mysqld_safe might be overwritten if you upgrade MariaDB in the future, so you should make a copy of your edited version that you can reinstall.

mysql.server - MariaDB Server Startup Script

MySQL distributions on Unix include a script named mysql.server. It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services. It is also used by the Mac OS X Startup Item for MySQL.

mysql.server can be found in the support-files directory under your MariaDB installation directory or in a MariaDB source distribution.

If you use the Linux server RPM package (MySQL-server-VERSION.rpm), the mysql.server script will be installed in the /etc/init.d directory with the name MariaDB. You need not install it manually. See , "Installing MariaDB from RPM Packages on Linux", for more information on the Linux RPM packages.

Some vendors provide RPM packages that install a startup script under a different name such as mysqld.

If you install MariaDB from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. Instructions are provided in , "Starting and Stopping MariaDB Automatically".

mysql.server reads options from the [mysql.server] and [mysqld] sections of option files. For backward compatibility, it also reads [mysql_server] sections, although you should rename such sections to [mysql.server] when using MariaDB 5.6.

mysql.server supports the following options.

mysqld_multi - Manage Multiple MariaDB Servers

mysqld-multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.

mysqld_multi searches for groups named [mysqldN] in my.cnf (or in the file named by the --config-file option). N can be any positive integer. This number is referred to in the following discussion as the option group number, or GNR. Group numbers distinguish option groups from one another and are used as arguments to mysqld_multi to specify which servers you want to start, stop, or obtain a status report for. Options listed in these groups are the same that you would use in the [mysqld] group used for starting mysqld. (See, for example, , "Starting and Stopping MariaDB Automatically".) However, when using multiple servers, it is necessary that each one use its own value for options such as the Unix socket file and TCP/IP port number. For more information on which options must be unique per server in a multiple-server environment, see , "Running Multiple MariaDB Instances on One Machine".

To invoke mysqld_multi, use the following syntax: 

shell> mysqld_multi [options] {start|stop|report} [GNR[,GNR] ...]

start, stop, and report indicate which operation to perform. You can perform the designated operation for a single server or multiple servers, depending on the GNR list that follows the option name. If there is no list, mysqld_multi performs the operation for all servers in the option file.

Each GNR value represents an option group number or range of group numbers. The value should be the number at the end of the group name in the option file. For example, the GNR for a group named [mysqld17] is 17. To specify a range of numbers, separate the first and last numbers by a dash. The GNR value 10-13 represents groups [mysqld10] through [mysqld13]. Multiple groups or group ranges can be specified on the command line, separated by commas. There must be no whitespace characters (spaces or tabs) in the GNR list; anything after a whitespace character is ignored.

This command starts a single server using option group [mysqld17]: 

shell> mysqld_multi start 17

This command stops several servers, using option groups [mysqld8] and [mysqld10] through [mysqld13]: 

shell> mysqld_multi stop 8,10-13

For an example of how you might set up an option file, use this command: 

shell> mysqld_multi --example

mysqld_multi searches for option files as follows:

Option files read are searched for [mysqld_multi] and [mysqldN] option groups. The [mysqld_multi] group can be used for options to mysqld_multi itself. [mysqldN] groups can be used for options passed to specific mysqld instances.

The [mysqld] or [mysqld_safe] groups can be used for common options read by all instances of mysqld or mysqld_safe. You can specify a --defaults-file=file_name option to use a different configuration file for that instance, in which case the [mysqld] or [mysqld_safe] groups from that file will be used for that instance.

mysqld_multi supports the following options.

Some notes about mysqld_multi:

The following example shows how you might set up an option file for use with mysqld_multi. The order in which the mysqld programs are started or stopped depends on the order in which they appear in the option file. Group numbers need not form an unbroken sequence. The first and fifth [mysqldN] groups were intentionally omitted from the example to illustrate that you can have "gaps" in the option file. This gives you more flexibility. 

# This file should probably be in your home dir (~/.my.cnf)
# or /etc/my.cnf
# Version 2.1 by Jani Tolonen
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe mysqladmin = /usr/local/bin/mysqladmin user = multi_admin password = multipass
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
language = /usr/local/share/mysql/english user = john
[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/var3/hostname.pid3
datadir = /usr/local/mysql/var3
language = /usr/local/share/mysql/swedish user = monty
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /usr/local/mysql/var4/hostname.pid4
datadir = /usr/local/mysql/var4
language = /usr/local/share/mysql/estonia user = tonu
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /usr/local/mysql/var6/hostname.pid6
datadir = /usr/local/mysql/var6
language = /usr/local/share/mysql/japanese user = jani

See , "Using Option Files".

MySQL Installation-Related Programs

comp_err - Compile MariaDB Error Message File
mysqlbug - Generate Bug Report
mysql_install_db - Initialize MariaDB Data Directory
mysql_plugin - Configure MariaDB Server Plugins
mysql_secure_installation - Improve MariaDB Installation Security
mysql_tzinfo_to_sql - Load the Time Zone Tables
mysql_upgrade - Check Tables for MariaDB Upgrade

The programs in this section are used when installing or upgrading MySQL.

comp_err - Compile MariaDB Error Message File

comp_err creates the errmsg.sys file that is used by mysqld to determine the error messages to display for different error codes. comp_err normally is run automatically when MariaDB is built. It compiles the errmsg.sys file from the plaintext file located at sql/share/errmsg.txt in MariaDB source distributions.

comp_err also generates mysqld_error.h, mysqld_ername.h, and sql_state.h header files.

For more information about how error messages are defined, see the MariaDB Internals Manual, available at http://forge.mysql.com/wiki/MySQL_Internals.

Invoke comp_err like this: 

shell> comp_err [options]

comp_err supports the following options.

mysqlbug - Generate Bug Report

This program is obsolete.

The normal way to report bugs is to visit https://jira.mariadb.org/, which is the address for our bugs database. This database is public and can be browsed and searched by anyone. If you log in to the system, you can enter new reports.

mysql_install_db - Initialize MariaDB Data Directory

mysql_install_db initializes the MariaDB data directory and creates the system tables that it contains, if they do not exist.

To invoke mysql_install_db, use the following syntax: 

shell> mysql_install_db [options]

Because the MariaDB server, mysqld, needs to access the data directory when it runs later, you should either run mysql_install_db from the same account that will be used for running mysqld or run it as root and use the --user option to indicate the user name that mysqld will run as. It might be necessary to specify other options such as --basedir or --datadir if mysql_install_db does not use the correct locations for the installation directory or data directory. For example: 

shell> scripts/mysql_install_db --user=mysql \
 --basedir=/opt/mysql/mysql \
 --datadir=/opt/mysql/mysql/data

mysql-install-db needs to invoke mysqld with the --bootstrap and --skip-grant-tables options. If MariaDB was configured with the DISABLE_GRANT_OPTIONS compiler flag, --bootstrap and --skip-grant-tables will be disabled (see , "MySQL Source-Configuration Options"). To handle this, set the MYSQLD_BOOTSTRAP environment variable to the full path name of a server that has all options enabled. mysql_install_db will use that server.Note

If you have set a custom TMPDIR variable when performing the installation, and the specified directory is not accessible, the execution of mysql_install_db may fail. You should unset TMPDIR, or set TMPDIR to point to the system temporary directory (usually /tmp).

mysql_install_db supports the following options, which can be specified on the command line or in the [mysql_install_db] and (if they are common to mysqld) [mysqld] groups of an option file. Other options are passed to mysqld. For information about option files, see , "Using Option Files". mysql_install_db also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

mysql_plugin - Configure MariaDB Server Plugins

The mysql_plugin utility enables MariaDB administrators to manage which plugins a MariaDB server loads. It provides an alternative to manually specifying the --plugin-load option at server startup or using the INSTALL PLUGIN and UNINSTALL PLUGIN statements at runtime. mysql_plugin is available as of MariaDB 5.6.3.

Depending on whether mysql_plugin is invoked to enable or disable plugins, it inserts or deletes rows in the mysql.plugin table that serves as a plugin registry. (To perform this operation, mysql_plugin invokes the MariaDB server in bootstrap mode. This means that the server must not already be running.) For normal server startups, the server loads and enables plugins listed in mysql.plugin automatically. For additional control over plugin activation, use --plugin_name options named for specific plugins, as described in , "Installing and Uninstalling Plugins".

Each invocation of mysql_plugin reads a configuration file to determine how to configure the plugins contained in a single plugin library object file. To invoke mysql_plugin, use this syntax: 

mysql_plugin [options] plugin {ENABLE|DISABLE}

plugin is the name of the plugin to configure. ENABLE or DISABLE (not case sensitive) specify whether to enable or disable components of the plugin library named in the configuration file. The order of the plugin and ENABLE or DISABLE arguments does not matter.

For example, to configure components of a plugin library file named myplugins.so on Linux or myplugins.dll on Windows, specify a plugin value of myplugins. Suppose that this plugin library contains three plugins, plugin1, plugin2, and plugin3, all of which should be configured under mysql_plugin control. By convention, configuration files have a suffix of .ini and the same basename as the plugin library, so the default configuration file name for this plugin library is myplugins.ini. The configuration file contents look like this: 

myplugins plugin1
plugin2
plugin3

The first line in the myplugins.ini file is the name of the library object file, without any extension such as .so or .dll. The remaining lines are the names of the components to be enabled or disabled. Each value in the file should be on a separate line. Lines on which the first character is '#' are taken as comments and ignored.

To enable the plugins listed in the configuration file, invoke mysql_plugin this way: 

shell> mysql_plugin myplugins ENABLE

To disable the plugins, use DISABLE rather than ENABLE.

An error occurs if mysql_plugin cannot find the configuration file or plugin library file, or if mysql_plugin cannot start the MariaDB server.

mysql_plugin supports the following options, which can be specified on the command line or in the [mysqld] group of any option file. For options specified in a [mysqld] group, mysql_plugin recognizes the --basedir, --datadir, and --plugin-dir options and ignores others. For information about option files, see , "Using Option Files".

mysql_plugin Options

mysql_secure_installation - Improve MariaDB Installation Security

This program enables you to improve the security of your MariaDB installation in the following ways:

mysql_secure_installation helps you implement security recommendations similar to those described at , "Securing the Initial MariaDB Accounts".

Invoke mysql_secure_installation without arguments: 

shell> mysql_secure_installation

The script will prompt you to determine which actions to perform.

mysql_secure_installation is not available on Windows.

mysql_tzinfo_to_sql - Load the Time Zone Tables

The mysql_tzinfo_to_sql program loads the time zone tables in the MariaDB database. It is used on systems that have a zoneinfo database (the set of files describing time zones). Examples of such systems are Linux, FreeBSD, Solaris, and Mac OS X. One likely location for these files is the /usr/share/zoneinfo directory (/usr/share/lib/zoneinfo on Solaris). If your system does not have a zoneinfo database, you can use the downloadable package described in , "MySQL Server Time Zone Support".

mysql_tzinfo_to_sql can be invoked several ways: 

shell> mysql_tzinfo_to_sql tz_dir
shell> mysql_tzinfo_to_sql tz_file tz_name
shell> mysql_tzinfo_to_sql --leap tz_file

For the first invocation syntax, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example: 

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.

The second syntax causes mysql_tzinfo_to_sql to load a single time zone file tz_file that corresponds to a time zone name tz_name: 

shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql

If your time zone needs to account for leap seconds, invoke mysql_tzinfo_to_sql using the third syntax, which initializes the leap second information. tz_file is the name of your time zone file: 

shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql

After running mysql_tzinfo_to_sql, it is best to restart the server so that it does not continue to use any previously cached time zone data.

mysql_upgrade - Check Tables for MariaDB Upgrade

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MariaDB Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

mysql_upgrade should be executed each time you upgrade MySQL. It supersedes the older mysql_fix_privilege_tables script, which has been removed in MariaDB 5.6.

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see , "Rebuilding or Repairing Tables or Indexes" for manual table repair strategies.Note

On Windows Server 2008, Vista, and newer, you must run mysql_upgrade with administrator privileges. You can do this by running a Command Prompt as Administrator and running the command. Failure to do so may result in the upgrade failing to execute correctly.Caution

You should always back up your current MariaDB installation before performing an upgrade. See , "Database Backup Methods".

Some upgrade incompatibilities may require special handling before you upgrade your MariaDB installation and run mysql_upgrade. See , "Upgrading MySQL", for instructions on determining whether any such incompatibilities apply to your installation and how to handle them.

To use mysql_upgrade, make sure that the server is running, and then invoke it like this: 

shell> mysql_upgrade [options]

After running mysql_upgrade, stop the server and restart it so that any changes made to the system tables take effect.

mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables: 

mysqlcheck --all-databases --check-upgrade --auto-repair mysql < fix_priv_tables
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

Notes about the preceding commands:

All checked and repaired tables are marked with the current MariaDB version number. This ensures that next time you run mysql_upgrade with the same version of the server, it can tell whether there is any need to check or repair the table again.

mysql_upgrade also saves the MariaDB version number in a file named mysql_upgrade_info in the data directory. This is used to quickly check whether all tables have been checked for this release so that table-checking can be skipped. To ignore this file and perform the check regardless, use the --force option.

If you install MariaDB from RPM packages on Linux, you must install the server and client RPMs. mysql_upgrade is included in the server RPM but requires the client RPM because the latter includes mysqlcheck. (See , "Installing MariaDB from RPM Packages on Linux".)

mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see , "Server-Side Help".

mysql_upgrade supports the following options, which can be specified on the command line or in the [mysql_upgrade] and [client] groups of an option file. Other options are passed to mysqlcheck. For example, it might be necessary to specify the --password[=password] option. mysql_upgrade also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

MySQL Client Programs

mysql - The MariaDB Command-Line Tool
mysqladmin - Client for Administering a MariaDB Server
mysqlcheck - A Table Maintenance Program
mysqldump - A Database Backup Program
mysqlimport - A Data Import Program
mysqlshow - Display Database, Table, and Column Information
mysqlslap - Load Emulation Client

This section describes client programs that connect to the MariaDB server.

mysql - The MariaDB Command-Line Tool

mysql Options
mysql Commands
mysql History File
mysql Server-Side Help
Executing SQL Statements from a Text File
mysql Tips

mysql is a simple SQL shell with input line editing capabilities. It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.

If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result().

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: 

shell> mysql db_name

Or: 

shell> mysql --user=user_name --password=your_password db_name

Then type an SQL statement, end it with ";", \g, or \G and press Enter.

Typing Control+C causes mysql to attempt to kill the current statement. If this cannot be done, or Control+C is typed again before the statement is killed, mysql exits. Previously, Control+C caused mysql to exit in all cases.

You can execute SQL statements in a script file (batch file) like this: 

shell> mysql db_name < script.sql > output.tab

On Unix, the mysql client writes a record of executed statements to a history file. See , "mysql History File".

mysql Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file. mysql also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.2. MariaDB Options

Format Option File Description Introduction Deprecated Removed
--auto-rehash auto-rehash Enable automatic rehashing
--auto-vertical-output auto-vertical-output Enable automatic vertical result set display
--batch batch Don't use history file
--binary-mode binary-mode Disable \r\n - to - \n translation and treatment of \0 as end-of-query
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--character-sets-dir=path character-sets-dir Set the default character set
--column-names column-names Write column names in results
--column-type-info column-type-info Display result set metadata
--comments comments Whether to retain or strip comments in statements sent to the server
--compress compress Compress all information sent between the client and the server
--connect_timeout=value connect_timeout The number of seconds before connection timeout
--database=dbname database The database to use
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--delimiter=str delimiter Set the statement delimiter
--execute=statement execute Execute the statement and quit
--force force Continue even if an SQL error occurs
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--html html Produce HTML output
--ignore-spaces ignore-spaces Ignore spaces after function names
--init-command=str init-command SQL statement to execute after connecting
--line-numbers line-numbers Write line numbers for errors
--local-infile[={0|1}] local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE
--max_allowed_packet=value max_allowed_packet The maximum packet length to send to or receive from the server
--max_join_size=value max_join_size The automatic limit for rows in a join when using --safe-updates
--named-commands named-commands Enable named mysql commands
--net_buffer_length=value net_buffer_length The buffer size for TCP/IP and socket communication
--no-auto-rehash Disable automatic rehashing
--no-beep no-beep Do not beep when errors occur
--one-database one-database Ignore statements except those for the default database named on the command line
--pager[=command] pager Use the given command for paging query output
--password[=password] password The password to use when connecting to the server
--plugin-dir=path plugin-dir=path The directory where plugins are located
--port=port_num port The TCP/IP port number to use for the connection
--prompt=format_str prompt Set the prompt to the specified format
--protocol=type protocol The connection protocol to use
--quick quick Do not cache each query result
--raw raw Write column values without escape conversion
--reconnect reconnect If the connection to the server is lost, automatically try to reconnect
--safe-updates safe-updates Allow only UPDATE and DELETE statements that specify key values
--secure-auth secure-auth Do not send passwords to the server in old (pre-4.1.1) format
--select_limit=value select_limit The automatic limit for SELECT statements when using --safe-updates
--show-warnings show-warnings Show warnings after each statement if there are any
--sigint-ignore sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C)
--silent silent Silent mode
--skip-auto-rehash skip-auto-rehash Disable automatic rehashing
--skip-column-names skip-column-names Do not write column names in results
--skip-line-numbers skip-line-numbers Skip line numbers for errors
--skip-named-commands skip-named-commands Disable named mysql commands
--skip-pager skip-pager Disable paging
--skip-reconnect skip-reconnect Disable reconnecting
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--table table Display output in tabular format
--tee=file_name tee Append a copy of output to the given file
--unbuffered unbuffered Flush the buffer after each query
--user=user_name user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit
--vertical vertical Print query output rows vertically (one line per column value)
--wait wait If the connection cannot be established, wait and retry instead of aborting
--xml xml Produce XML output

You can also set the following variables by using --var_name=value.

mysql Commands

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt: 

mysql> help
List of all MariaDB commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given
 outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing
 binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'

If mysql is invoked with the --binary-mode option, all mysql commands are disabled except charset and delimiter in non-interactive mode (for input piped to mysql or loaded using the source command).

Each command has both a long and short form. The long form is not case sensitive; the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not.

The use of short-form commands within multi-line /* ... */ comments is not supported.

Here are a few tips about the pager command:

You can also combine the tee and pager functions. Have a tee file enabled and pager set to less, and you are able to browse the results using the less program and still have everything appended into a file the same time. The difference between the Unix tee used with the pager command and the mysql built-in tee command is that the built-in tee works even if you do not have the Unix tee available. The built-in tee also logs everything that is printed on the screen, whereas the Unix tee used with pager does not log quite that much. Additionally, tee file logging can be turned on and off interactively from within mysql. This is useful when you want to log some queries to a file, but not others.

The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can contain the following special sequences.

Option Description
\c A counter that increments for each statement you issue
\D The full current date
\d The default database
\h The server host
\l The current delimiter
\m Minutes of the current time
\n A newline character
\O The current month in three-letter format (Jan, Feb, …)
\o The current month in numeric format
\P am/pm
\p The current TCP/IP port or socket file
\R The current time, in 24-hour military time (0-23)
\r The current time, standard 12-hour time (1-12)
\S Semicolon
\s Seconds of the current time
\t A tab character
\U Your full user_name@host_name account name
\u Your user name
\v The server version
\w The current day of the week in three-letter format (Mon, Tue, …)
\Y The current year, four digits
\y The current year, two digits
\_ A space
\ A space (a space follows the backslash)
\' Single quote
\' Double quote
\\ A literal "\" backslash character
\x x, for any "x" not listed above

You can set the prompt in several ways:

mysql History File

On Unix, the mysql client writes a record of executed statements to a history file. By default, this file is named .mysql_history and is created in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable.

The .mysql_history should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. See , "End-User Guidelines for Password Security".

It is possible to suppress logging of statements to the history file by using the --batch or --execute option.

If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques:

mysql Server-Side Help

mysql> help search_string

If you provide an argument to the help command, mysql uses it as a search string to access server-side help from the contents of the MariaDB Reference Manual. The proper operation of this command requires that the help tables in the MariaDB database be initialized with help topic information (see , "Server-Side Help").

If there is no match for the search string, the search fails: 

mysql> help me
Nothing found Please try to run 'help contents' for a list of all accessible topics

Use help contents to see a list of the help categories: 

mysql> help contents
You asked for help about help category: 'Contents'
For more information, type 'help <item>', where <item> is one of the following categories:
 Account Management
 Administration
 Data Definition
 Data Manipulation
 Data Types
 Functions
 Functions and Modifiers for Use with GROUP BY
 Geographic Features
 Language Structure
 Plugins
 Storage Engines
 Stored Routines
 Table Maintenance
 Transactions
 Triggers

If the search string matches multiple items, mysql shows a list of matching topics: 

mysql> help logs
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
 SHOW
 SHOW BINARY LOGS
 SHOW ENGINE
 SHOW LOGS

Use a topic as the search string to see the help entry for that topic: 

mysql> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS SHOW MASTER LOGS Lists the binary log files on the server. This statement is used as part of the procedure described in [purge-binary-logs], that shows how to determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+

Executing SQL Statements from a Text File

The mysql client typically is used interactively, like this: 

shell> mysql db_name

However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute. Then invoke mysql as shown here: 

shell> mysql db_name < text_file

If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the command line: 

shell> mysql < text_file

If you are already running mysql, you can execute an SQL script file using the source command or \. command: 

mysql> source file_name
mysql> \. file_name

Sometimes you may want your script to display progress information to the user. For this you can insert statements like this: 

SELECT '<info_to_display>' AS ' ';

The statement shown outputs <info_to_display>.

You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces.

mysql ignores Unicode byte order mark (BOM) characters at the beginning of input files. Previously, it read them and sent them to the server, resulting in a syntax error. Presence of a BOM does not cause mysql to change its default character set. To do that, invoke mysql with an option such as --default-character-set=utf8.

For more information about batch mode, see , "Using mysql in Batch Mode".

mysql Tips

Unicode Support on Windows
Displaying Query Results Vertically
Using the --safe-updates Option
Disabling mysql Auto-Reconnect

This section describes some techniques that can help you use mysql more effectively.

Unicode Support on Windows

Windows provides APIs based on UTF-16LE for reading from and writing to the console. As of MariaDB 5.6.2, the mysql client for Windows is able to use these APIs. As of 5.6.3, the Windows installer creates an item in the MariaDB menu named MySQL command line client - Unicode. This item invokes the mysql client with properties set to communicate through the console to the MariaDB server using Unicode.

To take advantage of this support manually, run mysql within a console that uses a compatible Unicode font and set the default character set to a Unicode character set that is supported for communication with the server:

  1. Open a console window.
  2. Go to the console window properties, select the font tab, and choose Lucida Console or some other compatible Unicode font. This is necessary because console windows start by default using a DOS raster font that is inadequate for Unicode.
  3. Execute mysql.exe with the --default-character-set=utf8 (or utf8mb4) option. This option is necessary because utf16le is not supported as a connection character set.

With those changes, mysql will use the Windows APIs to communicate with the console using UTF-16LE, and communicate with the server using UTF-8. (The menu item mentioned previously sets the font and character set as just described.)

To avoid those steps each time you run mysql, you can create a shortcut that invokes mysql.exe. The shortcut should set the console font to Lucida Console or some other compatible Unicode font, and pass the --default-character-set=utf8 (or utf8mb4) option to mysql.exe.

Alternatively, create a shortcut that only sets the console font, and set the character set in the [mysql] group of your my.ini file: 

[mysql]
default-character-set=utf8
Displaying Query Results Vertically

Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output: 

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
 msg_nro: 3068
 date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
 reply: monty@no.spam.com
 mail_to: 'Thimble Smith' <tim@no.spam.com>
 sbj: UTF-8
 txt: >>>>> 'Thimble' == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
 file: inbox-jani-1
 hash: 190402944
1 row in set (0.09 sec)
Using the --safe-updates Option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

When you use the --safe-updates option, mysql issues the following statement when it connects to the MariaDB server: 

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

See , "Server System Variables".

The SET statement has the following effects:

To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the --select_limit and --max_join_size options: 

shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
Disabling mysql Auto-Reconnect

If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it: 

mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MariaDB server has gone away No connection. Trying to reconnect...
Connection id: 1
Current database: test Query OK, 1 row affected (1.30 sec)
mysql> SELECT * FROM t;
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the --skip-reconnect option.

For more information about auto-reconnect and its effect on state information when a reconnection occurs, see , "Controlling Automatic Reconnection Behavior".

mysqladmin - Client for Administering a MariaDB Server

mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.

Invoke mysqladmin like this: 

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

mysqladmin supports the following commands. Some of the commands take an argument following the command name.

All commands can be shortened to any unique prefix. For example: 

shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624 Threads: 1 Questions: 39487
Slow queries: 0 Opens: 541 Flush tables: 1
Open tables: 19 Queries per second avg: 0.0268

The mysqladmin status command result displays the following values:

If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

mysqladmin supports the following options, which can be specified on the command line or in the [mysqladmin] and [client] groups of an option file. mysqladmin also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.3. mysqladmin Options

Format Option File Description Introduction Deprecated Removed
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--compress compress Compress all information sent between the client and the server
--connect_timeout=seconds connect_timeout The number of seconds before connection timeout
--count=# count The number of iterations to make for repeated command execution
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--force force Continue even if an SQL error occurs
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--no-beep no-beep Do not beep when errors occur
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located
--port=port_num port The TCP/IP port number to use for the connection
--protocol=type protocol The connection protocol to use
--relative relative Show the difference between the current and previous values when used with the --sleep option
--shutdown_timeout=seconds shutdown_timeout The maximum number of seconds to wait for server shutdown
--silent silent Silent mode
--sleep=delay sleep Execute commands repeatedly, sleeping for delay seconds in between
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit
--vertical vertical Print query output rows vertically (one line per column value)
--wait wait If the connection cannot be established, wait and retry instead of aborting

You can also set the following variables by using --var_name=value.

mysqlcheck - A Table Maintenance Program

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only (see , "LOCK TABLES and UNLOCK TABLES Syntax", for more information about READ and WRITE locks). Table maintenance operations can be time-consuming, particularly for large tables. If you use the --databases or --all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time. (This is also true for mysql_upgrade because that program invokes mysqlcheck to check all tables and repair them if necessary.)

mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to perform table maintenance.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. For details about which storage engines each statement works with, see the descriptions for those statements in , "Table Maintenance Statements".

The MyISAM storage engine supports all four maintenance operations, so mysqlcheck can be used to perform any of them on MyISAM tables. Other storage engines do not necessarily support all operations. In such cases, an error message is displayed. For example, if test.t is a MEMORY table, an attempt to check it produces this result: 

shell> mysqlcheck test t
test.t note : The storage engine for the table doesn't support check

If mysqlcheck is unable to repair a table, see , "Rebuilding or Repairing Tables or Indexes" for manual table repair strategies. This will be the case, for example, for InnoDB tables, which can be checked with CHECK TABLE, but not repaired with REPAIR TABLE.Caution

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.

There are three general ways to invoke mysqlcheck: 

shell> mysqlcheck [options] db_name [tbl_name ...]
shell> mysqlcheck [options] --databases db_name ...
shell> mysqlcheck [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are checked.

mysqlcheck has a special feature compared to other client programs. The default behavior of checking tables (--check) can be changed by renaming the binary. If you want to have a tool that repairs tables by default, you should just make a copy of mysqlcheck named mysqlrepair, or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke mysqlrepair, it repairs tables.

The names shown in the following table can be used to change mysqlcheck default behavior.

Command Meaning
mysqlrepair The default option is --repair
mysqlanalyze The default option is --analyze
mysqloptimize The default option is --optimize

mysqlcheck supports the following options, which can be specified on the command line or in the [mysqlcheck] and [client] groups of an option file. mysqlcheck also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.4. mysqlcheck Options

Format Option File Description Introduction Deprecated Removed
--all-databases all-databases Check all tables in all databases
--all-in-1 all-in-1 Execute a single statement for each database that names all the tables from that database
--analyze analyze Analyze the tables
--auto-repair auto-repair If a checked table is corrupted, automatically fix it
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--character-sets-dir=path character-sets-dir The directory where character sets are installed
--check check Check the tables for errors
--check-only-changed check-only-changed Check only tables that have changed since the last check
--check-upgrade check-upgrade Invoke CHECK TABLE with the FOR UPGRADE option
--compress compress Compress all information sent between the client and the server
--databases databases Process all tables in the named databases
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.6.2
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--extended extended Check and repair tables
--fast fast Check only tables that have not been closed properly
--fix-db-names fix-db-names Convert database names to 5.1 format
--fix-table-names fix-table-names Convert table names to 5.1 format
--force force Continue even if an SQL error occurs
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--medium-check medium-check Do a check that is faster than an --extended operation
--optimize optimize Optimize the tables
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.6.2
--port=port_num port The TCP/IP port number to use for the connection
--protocol=type protocol The connection protocol to use
--quick quick The fastest method of checking
--repair repair Perform a repair that can fix almost anything except unique keys that are not unique
--silent silent Silent mode
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--tables tables Overrides the --databases or -B option
--use-frm use-frm For repair operations on MyISAM tables
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit
--write-binlog write-binlog Log ANALYZE, OPTIMIZE, REPAIR statements to binary log. --skip-write-binlog adds NO_WRITE_TO_BINLOG to these statements.

mysqldump - A Database Backup Program

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MariaDB database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Performance and Scalability Considerations

mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on.

For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly:

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MariaDB server, use the --skip-opt option instead of the --opt or --extended-insert option.

For additional information about mysqldump, see , "Using mysqldump for Backups".

Syntax

There are three general ways to invoke mysqldump, to dump progressively more data: 

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

To see a list of the options your version of mysqldump supports, issue the command mysqldump --help.

Option Syntax - Alphabetical Summary

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] groups of an option file. mysqldump also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.5. mysqldump Options

Format Option File Description Introduction Deprecated Removed
--add-drop-database add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement
--add-drop-trigger add-drop-trigger Add a DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locks add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases all-databases Dump all tables in all databases
--allow-keywords allow-keywords Allow creation of column names that are keywords
--apply-slave-statements apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--comments comments Add comments to the dump file
--compact compact Produce more compact output
--compatible=name[,name,...] compatible Produce output that is more compatible with other database systems or with older MariaDB servers
--complete-insert complete-insert Use complete INSERT statements that include column names
--create-options create-options Include all MySQL-specific table options in CREATE TABLE statements
--databases databases Dump several databases
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--delayed-insert delayed-insert Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
--disable-keys disable-keys For each table, surround the INSERT statements with statements to disable and enable keys
--dump-date dump-date Include dump date as 'Dump completed on' comment if --comments is given
--dump-slave[=value] dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master
--events events Dump events from the dumped databases
--extended-insert extended-insert Use multiple-row INSERT syntax that include several VALUES lists
--fields-enclosed-by=string fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by fields-escaped-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string fields-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--flush-logs flush-logs Flush the MariaDB server log files before starting the dump
--flush-privileges flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql database
--help Display help message and exit
--hex-blob hex-blob Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)
--host host Host to connect to (IP address or hostname)
--ignore-table=db_name.tbl_name ignore-table Do not dump the given table
--include-master-host-port include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave
--insert-ignore insert-ignore Write INSERT IGNORE statements rather than INSERT statements
--lines-terminated-by=string lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables lock-all-tables Lock all tables across all databases
--lock-tables lock-tables Lock all tables before dumping them
--log-error=file_name log-error Append warnings and errors to the named file
--master-data[=value] master-data Write the binary log file name and position to the output
--max_allowed_packet=value max_allowed_packet The maximum packet length to send to or receive from the server
--net_buffer_length=value net_buffer_length The buffer size for TCP/IP and socket communication
--no-autocommit no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db no-create-db This option suppresses the CREATE DATABASE statements
--no-create-info no-create-info Do not write CREATE TABLE statements that re-create each dumped table
--no-data no-data Do not dump table contents
--no-set-names no-set-names Same as --skip-set-charset
--no-tablespaces no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located
--port=port_num port The TCP/IP port number to use for the connection
--quick quick Retrieve rows for a table from the server a row at a time
--quote-names quote-names Quote identifiers within backtick characters
--replace replace Write REPLACE statements rather than INSERT statements
--result-file=file result-file Direct output to a given file
--routines routines Dump stored routines (procedures and functions) from the dumped databases
--set-charset set-charset Add SET NAMES default_character_set to the output
--single-transaction single-transaction This option issues a BEGIN SQL statement before dumping data from the server
--skip-add-drop-table skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks skip-add-locks Do not add locks
--skip-comments skip-comments Do not add comments to the dump file
--skip-compact skip-compact Do not produce more compact output
--skip-disable-keys skip-disable-keys Do not disable keys
--skip-extended-insert skip-extended-insert Turn off extended-insert
--skip-opt skip-opt Turn off the options set by --opt
--skip-quick skip-quick Do not retrieve rows for a table from the server a row at a time
--skip-quote-names skip-quote-names Do not quote identifiers
--skip-set-charset skip-set-charset Suppress the SET NAMES statement
--skip-triggers skip-triggers Do not dump triggers
--skip-tz-utc skip-tz-utc Turn off tz-utc
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--tab=path tab Produce tab-separated data files
--tables tables Override the --databases or -B option
--triggers triggers Dump triggers for each dumped table
--tz-utc tz-utc Add SET TIME_ZONE='+00:00' to the dump file
--user=user_name user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit
--where='where_condition' where Dump only rows selected by the given WHERE condition
--xml xml Produce XML output

Connection Options

The mysqldump command logs into a MariaDB server to extract information. The following options specify how to connect to the MariaDB server, either on the same machine or a remote system.

You can also set the following variables by using --var_name=value syntax:

DDL Options

Usage scenarios for mysqldump include setting up an entire new MariaDB instance (including database tables), and replacing data inside an existing instance with existing databases and tables. The following options let you specify which things to tear down and set up when restoring a dump, by encoding various DDL statements within the dump file.

Debug Options

The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems.

Help Options

The following options display information about the mysqldump command itself.

Internationalization Options

The following options change how the mysqldump command represents character data with national language settings.

Replication Options

The mysqldump command is frequently used to create an empty instance, or an instance including data, on a slave server in a replication configuration. The following options apply to dumping and restoring data on replication master and slave servers.

Format Options

The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Prior to MariaDB 5.6.5, this option prevented the --routines option from working correctly-that is, no stored routines, triggers, or events could be dumped in XML format. (Bug #11760384, Bug #52792)

Filtering Options

The following options control which kinds of schema objects are written to the dump file: by category, such as triggers or events; by name, for example, choosing which databases and tables to dump; or even filtering rows from the table data using a WHERE clause.

Performance Options

The following options are the most relevant for the performance particularly of the restore operations. For large data sets, restore operation (processing the INSERT statements in the dump file) is the most time-consuming part. When it is urgent to restore data quickly, plan and test the performance of this stage in advance. For restore times measured in hours, you might prefer an alternative backup and restore solution, such as MySQL Enterprise Backup for InnoDB-only and mixed-use databases, or mysqlhotcopy for MyISAM-only databases.

Performance is also affected by the transactional options, primarily for the dump operation.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

Option Groups

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

Examples

To make a backup of an entire database: 

shell> mysqldump db_name > backup-file.sql

To load the dump file back into the server: 

shell> mysql db_name < backup-file.sql

Another way to reload the dump file: 

shell> mysql -e 'source /path-to-backup/backup-file.sql' db_name

mysqldump is also very useful for populating databases by copying data from one MariaDB server to another: 

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

You can dump several databases with one command: 

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option: 

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup: 

shell> mysqldump --all-databases --single-transaction > all_databases.sql

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MariaDB server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MariaDB server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as "roll-forward," when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see , "The Binary Log") or at least know the binary log coordinates to which the dump corresponds: 

shell> mysqldump --all-databases --master-data=2 > all_databases.sql

Or: 

shell> mysqldump --all-databases --flush-logs --master-data=2
 > all_databases.sql

The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.

For more information on making backups, see , "Database Backup Methods", and , "Example Backup and Recovery Strategy".

Restrictions

mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the --skip-lock-tables option.

mysqldump never dumps the performance_schema database.

If you encounter problems backing up views due to insufficient privileges, see "Restrictions on Views" for a workaround.

mysqlimport - A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax. See , "LOAD DATA INFILE Syntax".

Invoke mysqlimport like this: 

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

mysqlimport supports the following options, which can be specified on the command line or in the [mysqlimport] and [client] groups of an option file. mysqlimport also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.6. mysqlimport Options

Format Option File Description Introduction Deprecated Removed
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--columns=column_list columns This option takes a comma-separated list of column names as its value
--compress compress Compress all information sent between the client and the server
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.6.2
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--delete delete Empty the table before importing the text file
--fields-enclosed-by=string fields-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by fields-escaped-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string fields-terminated-by -- This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--force force Continue even if an SQL error occurs
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--ignore ignore See the description for the --replace option
--ignore-lines=# ignore-lines Ignore the first N lines of the data file
--lines-terminated-by=string lines-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--local local Read input files locally from the client host
--lock-tables lock-tables Lock all tables for writing before processing any text files
--low-priority low-priority Use LOW_PRIORITY when loading the table.
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.6.2
--port=port_num port The TCP/IP port number to use for the connection
--protocol=type protocol The connection protocol to use
--replace replace The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--silent silent Produce output only when errors occur
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--use-threads=# use-threads The number of threads for parallel file-loading
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit

Here is a sample session that demonstrates use of mysqlimport: 

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q shell> od -c imptest.txt
0000000 1 0 0 \t M a x S y d o w \n 1 0
0000020 1 \t C o u n t D r a c u l a \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+

mysqlshow - Display Database, Table, and Column Information

The mysqlshow client can be used to quickly see which databases exist, their tables, or a table's columns or indexes.

mysqlshow provides a command-line interface to several SQL SHOW statements. See , "SHOW Syntax". The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program.

Invoke mysqlshow like this: 

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]

The output displays only the names of those databases, tables, or columns for which you have some privileges.

If the last argument contains shell or SQL wildcard characters ("*", "?", "%", or "_"), only those names that are matched by the wildcard are shown. If a database name contains any underscores, those should be escaped with a backslash (some Unix shells require two) to get a list of the proper tables or columns. "*" and "?" characters are converted into SQL "%" and "_" wildcard characters. This might cause some confusion when you try to display the columns for a table with a "_" in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra "%" last on the command line as a separate argument.

mysqlshow supports the following options, which can be specified on the command line or in the [mysqlshow] and [client] groups of an option file. mysqlshow also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.7. mysqlshow Options

Format Option File Description Introduction Deprecated Removed
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--compress compress Compress all information sent between the client and the server
--count count Show the number of rows per table
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.6.2
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--keys keys Show table indexes
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.6.2
--port=port_num port The TCP/IP port number to use for the connection
--protocol=type protocol The connection protocol to use
--show-table-type Show a column indicating the table type
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--status status Display extra information about each table
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit

mysqlslap - Load Emulation Client

mysqlslap is a diagnostic program designed to emulate client load for a MariaDB server and to report the timing of each stage. It works as if multiple clients are accessing the server.

Invoke mysqlslap like this: 

shell> mysqlslap [options]

Some options such as --create or --query enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; mysqlslap does not understand them.

mysqlslap runs in three stages:

  1. Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.
  2. Run the load test. This stage can use many client connections.
  3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

Examples:

Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each: 

mysqlslap --delimiter=';' \
 --create='CREATE TABLE a (b int);INSERT INTO a VALUES (23)' \
 --query='SELECT * FROM a' --concurrency=50 --iterations=200

Let mysqlslap build the query SQL statement with a table of two INT columns and three VARCHAR columns. Use five clients querying 20 times each. Do not create the table or insert the data (that is, use the previous test's schema and data): 

mysqlslap --concurrency=5 --iterations=20 \
 --number-int-cols=2 --number-char-cols=3 \
 --auto-generate-sql

Tell the program to load the create, insert, and query SQL statements from the specified files, where the create.sql file has multiple table creation statements delimited by ';' and multiple insert statements delimited by ';'. The --query file will have multiple queries delimited by ';'. Run all the load statements, then run all the queries in the query file with five clients (five times each): 

mysqlslap --concurrency=5 \
 --iterations=5 --query=query.sql --create=create.sql \
 --delimiter=';'

mysqlslap supports the following options, which can be specified on the command line or in the [mysqlslap] and [client] groups of an option file. mysqlslap also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.8. mysqlslap Options

Format Option File Description Introduction Deprecated Removed
--auto-generate-sql auto-generate-sql Generate SQL statements automatically when they are not supplied in files or using command options
--auto-generate-sql-add-autoincrement auto-generate-sql-add-autoincrement Add AUTO_INCREMENT column to automatically generated tables
--auto-generate-sql-execute-number=# auto-generate-sql-execute-number Specify how many queries to generate automatically
--auto-generate-sql-guid-primary auto-generate-sql-guid-primary Add a GUID-based primary key to automatically generated tables
--auto-generate-sql-load-type=type auto-generate-sql-load-type Specify how many queries to generate automatically
--auto-generate-sql-secondary-indexes=# auto-generate-sql-secondary-indexes Specify how many secondary indexes to add to automatically generated tables
--auto-generate-sql-unique-query-number=# auto-generate-sql-unique-query-number How many different queries to generate for automatic tests.
--auto-generate-sql-unique-write-number=# auto-generate-sql-unique-write-number How many different queries to generate for --auto-generate-sql-write-number
--auto-generate-sql-write-number=# auto-generate-sql-write-number How many row inserts to perform on each thread
--commit=# commit How many statements to execute before committing.
--compress compress Compress all information sent between the client and the server
--concurrency=# concurrency The number of clients to simulate when issuing the SELECT statement
--create=value create The file or string containing the statement to use for creating the table
--create-and-drop-schema=value create-and-drop-schema The schema in which to run the tests; dropped at the end of the test run 5.6.3
--create-schema=value create-schema The schema in which to run the tests
--csv=[file] csv Generate output in comma-separated values format
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.6.2
--delimiter=str delimiter The delimiter to use in SQL statements
--detach=# detach Detach (close and reopen) each connection after each N statements
--engine=engine_name engine The storage engine to use for creating the table
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--iterations=# iterations The number of times to run the tests
--number-char-cols=# number-char-cols The number of VARCHAR columns to use if --auto-generate-sql is specified
--number-int-cols=# number-int-cols The number of INT columns to use if --auto-generate-sql is specified
--number-of-queries=# number-of-queries Limit each client to approximately this number of queries
--only-print only-print Do not connect to databases. mysqlslap only prints what it would have done
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.6.2
--port=port_num port The TCP/IP port number to use for the connection
--post-query=value post-query The file or string containing the statement to execute after the tests have completed
--post-system=str post-system The string to execute using system() after the tests have completed
--pre-query=value pre-query The file or string containing the statement to execute before running the tests
--pre-system=str pre-system The string to execute using system() before running the tests
--protocol=type protocol The connection protocol to use
--query=value query The file or string containing the SELECT statement to use for retrieving data
--silent silent Silent mode
--socket=path socket For connections to localhost
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit

MySQL Administrative and Utility Programs

innochecksum - Offline InnoDB File Checksum Utility
myisam_ftdump - Display Full-Text Index information
myisamchk - MyISAM Table-Maintenance Utility
myisamlog - Display MyISAM Log File Contents
myisampack - Generate Compressed, Read-Only MyISAM Tables
mysqlaccess - Client for Checking Access Privileges
mysqlbinlog - Utility for Processing Binary Log Files
mysqldumpslow - Summarize Slow Query Log Files
mysqlhotcopy - A Database Backup Program
mysql_convert_table_format - Convert Tables to Use a Given Storage Engine
mysql_find_rows - Extract SQL Statements from Files
mysql_fix_extensions - Normalize Table File Name Extensions
mysql_setpermission - Interactively Set Permissions in Grant Tables
mysql_waitpid - Kill Process and Wait for Its Termination
mysql_zap - Kill Processes That Match a Pattern

This section describes administrative programs and programs that perform miscellaneous utility operations.

innochecksum - Offline InnoDB File Checksum Utility

innochecksum prints checksums for InnoDB files. This tool reads an InnoDB tablespace file, calculates the checksum for each page, compares the calculated checksum to the stored checksum, and reports mismatches, which indicate damaged pages. It was originally developed to speed up verifying the integrity of tablespace files after power outages but can also be used after file copies. Because checksum mismatches will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

If checksum mismatches are found, you would normally restore the tablespace from backup or start the server and attempt to use mysqldump to make a backup of the tables within the tablespace.

Invoke innochecksum like this: 

shell> innochecksum [options] file_name

innochecksum supports the following options. For options that refer to page numbers, the numbers are zero-based.

myisam_ftdump - Display Full-Text Index information

myisam_ftdump displays information about FULLTEXT indexes in MyISAM tables. It reads the MyISAM index file directly, so it must be run on the server host where the table is located. Before using myisam_ftdump, be sure to issue a FLUSH TABLES statement first if the server is running.

myisam_ftdump scans and dumps the entire index, which is not particularly fast. On the other hand, the distribution of words changes infrequently, so it need not be run often.

Invoke myisam_ftdump like this: 

shell> myisam_ftdump [options] tbl_name index_num

The tbl_name argument should be the name of a MyISAM table. You can also specify a table by naming its index file (the file with the .MYI suffix). If you do not invoke myisam_ftdump in the directory where the table files are located, the table or index file name must be preceded by the path name to the table's database directory. Index numbers begin with 0.

Example: Suppose that the test database contains a table named mytexttablel that has the following definition: 

CREATE TABLE mytexttable
(
 id INT NOT NULL,
 txt TEXT NOT NULL,
 PRIMARY KEY (id),
 FULLTEXT (txt)
) ENGINE=MyISAM;

The index on id is index 0 and the FULLTEXT index on txt is index 1. If your working directory is the test database directory, invoke myisam_ftdump as follows: 

shell> myisam_ftdump mytexttable 1

If the path name to the test database directory is /usr/local/mysql/data/test, you can also specify the table name argument using that path name. This is useful if you do not invoke myisam_ftdump in the database directory: 

shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1

You can use myisam_ftdump to generate a list of index entries in order of frequency of occurrence like this: 

shell> myisam_ftdump -c mytexttable 1 | sort -r

myisam_ftdump supports the following options:

myisamchk - MyISAM Table-Maintenance Utility

myisamchk General Options
myisamchk Check Options
myisamchk Repair Options
Other myisamchk Options
Obtaining Table Information with myisamchk
myisamchk Memory Usage

The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes).

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See , "CHECK TABLE Syntax", and , "REPAIR TABLE Syntax".

The use of myisamchk with partitioned tables is not supported.Caution

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.

Invoke myisamchk like this: 

shell> myisamchk [options] tbl_name ...

The options specify what you want myisamchk to do. They are described in the following sections. You can also get a list of options by invoking myisamchk --help.

With no options, myisamchk simply checks your table as the default operation. To get more information or to tell myisamchk to take corrective action, specify options as described in the following discussion.

tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than in the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk does not actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.

You can name several tables on the myisamchk command line if you wish. You can also specify a table by naming its index file (the file with the .MYI suffix). This enables you to specify all tables in a directory by using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this: 

shell> myisamchk *.MYI

If you are not in the database directory, you can check all the tables there by specifying the path to the directory: 

shell> myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a wildcard with the path to the MariaDB data directory: 

shell> myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all MyISAM tables is: 

shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI

If you want to check all MyISAM tables and repair any that are corrupted, you can use the following command: 

shell> myisamchk --silent --force --fast --update-state \
 --key_buffer_size=64M --sort_buffer_size=64M \
 --read_buffer_size=1M --write_buffer_size=1M \
 /path/to/datadir/*/*.MYI

This command assumes that you have more than 64MB free. For more information about memory allocation with myisamchk, see , "myisamchk Memory Usage".

For additional information about using myisamchk, see , "MyISAM Table Maintenance and Crash Recovery".Important

You must ensure that no other program is using the tables while you are running myisamchk. The most effective means of doing so is to shut down the MariaDB server while running myisamchk, or to lock all tables that myisamchk is being used on.

Otherwise, when you run myisamchk, it may display the following error message: 

warning: clients are using or haven't closed the table properly

This means that you are trying to check a table that has been updated by another program (such as the mysqld server) that hasn't yet closed the file or that has died without closing the file properly, which can sometimes lead to the corruption of one or more MyISAM tables.

If mysqld is running, you must force it to flush any table modifications that are still buffered in memory by using FLUSH TABLES. You should then ensure that no one is using the tables while you are running myisamchk

However, the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. See , "CHECK TABLE Syntax".

myisamchk supports the following options, which can be specified on the command line or in the [myisamchk] group of an option file. myisamchk also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.9. myisamchk Options

Format Option File Description Introduction Deprecated Removed
--analyze analyze Analyze the distribution of key values
--backup backup Make a backup of the .MYD file as file_name-time.BAK
--block-search=offset block-search Find the record that a block at the given offset belongs to
--check check Check the table for errors
--check-only-changed check-only-changed Check only tables that have changed since the last check
--correct-checksum correct-checksum Correct the checksum information for the table
--data-file-length=len data-file-length Maximum length of the data file (when re-creating data file when it is full)
--debug[=debug_options] debug Write a debugging log
decode_bits=# decode_bits Decode_bits
--description description Print some descriptive information about the table
--extend-check extend-check Do very thorough table check or repair that tries to recover every possible row from the data file
--fast fast Check only tables that haven't been closed properly
--force force Do a repair operation automatically if myisamchk finds any errors in the table
--force force-recover Overwrite old temporary files. For use with the -r or -o option
ft_max_word_len=# ft_max_word_len Maximum word length for FULLTEXT indexes
ft_min_word_len=# ft_min_word_len Minimum word length for FULLTEXT indexes
ft_stopword_file=value ft_stopword_file Use stopwords from this file instead of built-in list
--HELP Display help message and exit
--help Display help message and exit
--information information Print informational statistics about the table that is checked
key_buffer_size=# key_buffer_size The size of the buffer used for index blocks for MyISAM tables
--keys-used=val keys-used A bit-value that indicates which indexes to update
--max-record-length=len max-record-length Skip rows larger than the given length if myisamchk cannot allocate memory to hold them
--medium-check medium-check Do a check that is faster than an --extend-check operation
myisam_block_size=# myisam_block_size Block size to be used for MyISAM index pages
--parallel-recover parallel-recover Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta)
--quick quick Achieve a faster repair by not modifying the data file.
read_buffer_size=# read_buffer_size Each thread that does a sequential scan allocates a buffer of this size for each table it scans
--read-only read-only Don't mark the table as checked
--recover recover Do a repair that can fix almost any problem except unique keys that aren't unique
--safe-recover safe-recover Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found
--set-auto-increment[=value] set-auto-increment Force AUTO_INCREMENT numbering for new records to start at the given value
--set-collation=name set-collation Specify the collation to use for sorting table indexes
--silent silent Silent mode
sort_buffer_size=# sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE
--sort-index sort-index Sort the index tree blocks in high-low order
sort_key_blocks=# sort_key_blocks sort_key_blocks
--sort-records=# sort-records Sort records according to a particular index
--sort-recover sort-recover Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large
stats_method=value stats_method Specifies how MyISAM index statistics collection code should treat NULLs
--tmpdir=path tmpdir Path of the directory to be used for storing temporary files
--unpack unpack Unpack a table that was packed with myisampack
--update-state update-state Store information in the .MYI file to indicate when the table was checked and whether the table crashed
--verbose Verbose mode
--version Display version information and exit
write_buffer_size=# write_buffer_size Write buffer size

myisamchk General Options

The options described in this section can be used for any type of table maintenance operation performed by myisamchk. The sections following this one describe options that pertain only to specific operations, such as table checking or repairing.

You can also set the following variables by using --var_name=value syntax:

Variable Default Value
decode_bits 9
ft_max_word_len version-dependent
ft_min_word_len 4
ft_stopword_file built-in list
key_buffer_size 523264
myisam_block_size 1024
read_buffer_size 262136
sort_buffer_size 2097144
sort_key_blocks 16
stats_method nulls_unequal
write_buffer_size 262136

The possible myisamchk variables and their default values can be examined with myisamchk --help:

sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover.

key_buffer_size is used when you are checking the table with --extend-check or when the keys are repaired by inserting keys row by row into the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the key_buffer_size and sort_buffer_size variables to about 25% of your available memory. You can set both variables to large values, because only one of them is used at a time.

myisam_block_size is the size used for index blocks.

stats_method influences how NULL values are treated for index statistics collection when the --analyze option is given. It acts like the myisam_stats_method system variable. For more information, see the description of myisam_stats_method in , "Server System Variables", and , "InnoDB and MyISAM Index Statistics Collection".

ft_min_word_len and ft_max_word_len indicate the minimum and maximum word length for FULLTEXT indexes on MyISAM tables. ft_stopword_file names the stopword file. These need to be set under the following circumstances.

If you use myisamchk to perform an operation that modifies table indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt using the default full-text parameter values for minimum and maximum word length and the stopword file unless you specify otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by the server. They are not stored in MyISAM index files. To avoid the problem if you have modified the minimum or maximum word length or the stopword file in the server, specify the same ft_min_word_len, ft_max_word_len, and ft_stopword_file values to myisamchk that you use for mysqld. For example, if you have set the minimum word length to 3, you can repair a table with myisamchk like this: 

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

To ensure that myisamchk and the server use the same values for full-text parameters, you can place each one in both the [mysqld] and [myisamchk] sections of an option file: 

[mysqld]
ft_min_word_len=3
[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, or ALTER TABLE. These statements are performed by the server, which knows the proper full-text parameter values to use.

myisamchk Check Options

myisamchk supports the following options for table checking operations:

myisamchk Repair Options

myisamchk supports the following options for table repair operations (operations performed when an option such as --recover or --safe-recover is given):

Other myisamchk Options

myisamchk supports the following options for actions other than table checks and repairs:

Obtaining Table Information with myisamchk

To obtain a description of a MyISAM table or statistics about it, use the commands shown here. The output from these commands is explained later in this section.

The tbl_name argument can be either the name of a MyISAM table or the name of its index file, as described in , "myisamchk - MyISAM Table-Maintenance Utility". Multiple tbl_name arguments can be given.

Suppose that a table named person has the following structure. (The MAX_ROWS table option is included so that in the example output from myisamchk shown later, some values are smaller and fit the output format more easily.) 

CREATE TABLE person
(
 id INT NOT NULL AUTO_INCREMENT,
 last_name VARCHAR(20) NOT NULL,
 first_name VARCHAR(20) NOT NULL,
 birth DATE,
 death DATE,
 PRIMARY KEY (id),
 INDEX (last_name, first_name),
 INDEX (birth)
) MAX_ROWS = 1000000;

Suppose also that the table has these data and index file sizes: 

-rw-rw---- 1 mysql mysql 9347072 Aug 19 11:47 person.MYD
-rw-rw---- 1 mysql mysql 6066176 Aug 19 11:47 person.MYI

Example of myisamchk -dvv output: 

MyISAM file: person Record format: Packed Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2009-08-19 16:47:41
Recover time: 2009-08-19 16:47:56
Status: checked,analyzed,optimized keys Auto increment key: 1 Last value: 306688
Data records: 306688 Deleted blocks: 0
Datafile parts: 306688 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 9347072 Keyfile length: 6066176
Max datafile length: 4294967294 Max keyfile length: 17179868159
Recordlength: 54
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 1 99328 1024
2 6 20 multip. varchar prefix 512 3563520 1024
 27 20 varchar 512
3 48 3 multip. uint24 NULL 306688 6065152 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 4 no zeros
3 6 21 varchar
4 27 21 varchar
5 48 3 1 1 no zeros
6 51 3 1 2 no zeros

Explanations for the types of information myisamchk produces are given here. "Keyfile" refers to the index file. "Record" and "row" are synonymous, as are "field" and "column."

The initial part of the table description contains these values:

The table description part of the output includes a list of all keys in the table. For each key, myisamchk displays some low-level information:

The last part of the output provides information about each column:

The Huff tree and Bits fields are displayed if the table has been compressed with myisampack. See , "myisampack - Generate Compressed, Read-Only MyISAM Tables", for an example of this information.

Example of myisamchk -eiv output: 

Checking MyISAM file: person Data records: 306688 Deleted blocks: 0
- check file-size
- check record delete-chain No recordlinks
- check key delete-chain block_size 1024:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 3
- check data record references index: 2
Key: 2: Keyblocks used: 99% Packed: 97% Max levels: 3
- check data record references index: 3
Key: 3: Keyblocks used: 98% Packed: -14% Max levels: 3
Total: Keyblocks used: 98% Packed: 89%
- check records and index references*** LOTS OF ROW NUMBERS DELETED ***
Records: 306688 M.recordlength: 25 Packed: 83%
Recordspace used: 97% Empty space: 2% Blocks/Record: 1.00
Record blocks: 306688 Delete blocks: 0
Record data: 7934464 Deleted data: 0
Lost space: 256512 Linkdata: 1156096
User time 43.08, System time 1.68
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0
Blocks in 0 out 7, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0
Maximum memory usage: 1046926 bytes (1023k)

myisamchk -eiv output includes the following information:

myisamchk Memory Usage

Memory allocation is important when you run myisamchk. myisamchk uses no more memory than its memory-related variables are set to. If you are going to use myisamchk on very large tables, you should first decide how much memory you want it to use. The default is to use only about 3MB to perform repairs. By using larger values, you can get myisamchk to operate faster. For example, if you have more than 512MB RAM available, you could use options such as these (in addition to any other options you might specify): 

shell> myisamchk --sort_buffer_size=256M \
 --key_buffer_size=512M \
 --read_buffer_size=64M \
 --write_buffer_size=64M ...

Using --sort_buffer_size=16M is probably enough for most cases.

Be aware that myisamchk uses temporary files in TMPDIR. If TMPDIR points to a memory file system, out of memory errors can easily occur. If this happens, run myisamchk with the --tmpdir=path option to specify a directory located on a file system that has more space.

When performing repair operations, myisamchk also needs a lot of disk space:

If you have a problem with disk space during repair, you can try --safe-recover instead of --recover.

myisamlog - Display MyISAM Log File Contents

myisamlog processes the contents of a MyISAM log file.

Invoke myisamlog like this: 

shell> myisamlog [options] [log_file [tbl_name] ...]
shell> isamlog [options] [log_file [tbl_name] ...]

The default operation is update (-u). If a recovery is done (-r), all writes and possibly updates and deletes are done and errors are only counted. The default log file name is myisam.log for myisamlog and isam.log for isamlog if no log_file argument is given. If tables are named on the command line, only those tables are updated.

myisamlog supports the following options:

myisampack - Generate Compressed, Read-Only MyISAM Tables

The myisampack utility compresses MyISAM tables. myisampack works by compressing each column in the table separately. Usually, myisampack packs the data file 40% to 70%.

When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.

MySQL uses mmap() when possible to perform memory mapping on compressed tables. If mmap() does not work, MariaDB falls back to normal read/write file operations.

Please note the following:

Invoke myisampack like this: 

shell> myisampack [options] file_name ...

Each file name argument should be the name of an index (.MYI) file. If you are not in the database directory, you should specify the path name to the file. It is permissible to omit the .MYI extension.

After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes. , "myisamchk - MyISAM Table-Maintenance Utility".

myisampack supports the following options. It also reads option files and supports the options for processing them described at , "Command-Line Options that Affect Option-File Handling".

The following sequence of commands illustrates a typical table compression session: 

shell> ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell> myisamchk -dvv station
MyISAM file: station Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text 10240 1024 1
Field Start Length Type
1 1 1
2 2 4
3 6 4
4 10 1
5 11 20
6 31 1
7 32 30
8 62 35
9 97 35
10 132 35
11 167 4
12 171 16
13 187 35
14 222 4
15 226 16
16 242 20
17 262 20
18 282 20
19 302 30
20 332 4
21 336 4
22 340 1
23 341 8
24 349 8
25 357 8
26 365 2
27 367 2
28 369 4
29 373 4
30 377 1
31 378 2
32 380 8
33 388 4
34 392 4
35 396 4
36 400 4
37 404 1
38 405 4
39 409 4
40 413 4
41 417 4
42 421 4
43 425 4
44 429 20
45 449 30
46 479 1
47 480 1
48 481 79
49 560 79
50 639 79
51 718 79
52 797 8
53 805 1
54 806 1
55 807 20
56 827 4
57 831 4
shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables shell> ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell> myisamchk -dvv station
MyISAM file: station Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text 54272 1024 1
Field Start Length Type Huff tree Bits
1 1 1 constant 1 0
2 2 4 zerofill(1) 2 9
3 6 4 no zeros, zerofill(1) 2 9
4 10 1 3 9
5 11 20 table-lookup 4 0
6 31 1 3 9
7 32 30 no endspace, not_always 5 9
8 62 35 no endspace, not_always, no empty 6 9
9 97 35 no empty 7 9
10 132 35 no endspace, not_always, no empty 6 9
11 167 4 zerofill(1) 2 9
12 171 16 no endspace, not_always, no empty 5 9
13 187 35 no endspace, not_always, no empty 6 9
14 222 4 zerofill(1) 2 9
15 226 16 no endspace, not_always, no empty 5 9
16 242 20 no endspace, not_always 8 9
17 262 20 no endspace, no empty 8 9
18 282 20 no endspace, no empty 5 9
19 302 30 no endspace, no empty 6 9
20 332 4 always zero 2 9
21 336 4 always zero 2 9
22 340 1 3 9
23 341 8 table-lookup 9 0
24 349 8 table-lookup 10 0
25 357 8 always zero 2 9
26 365 2 2 9
27 367 2 no zeros, zerofill(1) 2 9
28 369 4 no zeros, zerofill(1) 2 9
29 373 4 table-lookup 11 0
30 377 1 3 9
31 378 2 no zeros, zerofill(1) 2 9
32 380 8 no zeros 2 9
33 388 4 always zero 2 9
34 392 4 table-lookup 12 0
35 396 4 no zeros, zerofill(1) 13 9
36 400 4 no zeros, zerofill(1) 2 9
37 404 1 2 9
38 405 4 no zeros 2 9
39 409 4 always zero 2 9
40 413 4 no zeros 2 9
41 417 4 always zero 2 9
42 421 4 no zeros 2 9
43 425 4 always zero 2 9
44 429 20 no empty 3 9
45 449 30 no empty 3 9
46 479 1 14 4
47 480 1 14 4
48 481 79 no endspace, no empty 15 9
49 560 79 no empty 2 9
50 639 79 no empty 2 9
51 718 79 no endspace 16 9
52 797 8 no empty 2 9
53 805 1 17 1
54 806 1 3 9
55 807 20 no empty 3 9
56 827 4 no zeros, zerofill(2) 2 9
57 831 4 no zeros, zerofill(1) 2 9

myisampack displays the following kinds of information:

After a table has been compressed, the Field lines displayed by myisamchk -dvv include additional information about each column:

After you run myisampack, you must run myisamchk to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MariaDB optimizer to work more efficiently: 

shell> myisamchk -rq --sort-index --analyze tbl_name.MYI

After you have installed the packed table into the MariaDB database directory, you should execute mysqladmin flush-tables to force mysqld to start using the new table.

To unpack a packed table, use the --unpack option to myisamchk.

mysqlaccess - Client for Checking Access Privileges

mysqlaccess is a diagnostic tool that Yves Carlier has provided for the MariaDB distribution. It checks the access privileges for a host name, user name, and database combination. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table, column, or routine privileges specified in the tables_priv, columns_priv, or procs_priv tables.

Invoke mysqlaccess like this: 

shell> mysqlaccess [host_name [user_name [db_name]]] [options]

mysqlaccess supports the following options.

Table 4.10. mysqlaccess Options

Format Option File Description Introduction Deprecated Removed
--brief brief Generate reports in single-line tabular format
--commit commit Copy the new access privileges from the temporary tables to the original grant tables
--copy copy Reload the temporary grant tables from original ones
--db=db_name db Specify the database name
--debug=# debug Specify the debug level
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--howto howto Display some examples that show how to use mysqlaccess
--old_server old_server Assume that the server is an old MariaDB server (prior to MariaDB 3.21)
--password[=password] password The password to use when connecting to the server
--plan plan Display suggestions and ideas for future releases
--preview preview Show the privilege differences after making changes to the temporary grant tables
--relnotes relnotes Display the release notes
--rhost=host_name rhost Connect to the MariaDB server on the given host
--rollback rollback Undo the most recent changes to the temporary grant tables.
--spassword[=password] spassword The password to use when connecting to the server as the superuser
--superuser=user_name superuser Specify the user name for connecting as the superuser
--table table Generate reports in table format
--user=user_name, user The MariaDB user name to use when connecting
--version Display version information and exit

If your MariaDB distribution is installed in some nonstandard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the mysqlaccess script at approximately line 18. Search for a line that looks like this: 

$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

mysqlbinlog - Utility for Processing Binary Log Files

mysqlbinlog Hex Dump Format
mysqlbinlog Row Event Display
Using mysqlbinlog to Back Up Binary Log Files
Specifying the mysqlbinlog Server ID

The server's binary log consists of files containing "events" that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs. The binary log and relay log are discussed further in , "The Binary Log", and , "Replication Relay and Status Logs".

Invoke mysqlbinlog like this: 

shell> mysqlbinlog [options] log_file ...

For example, to display the contents of the binary log file named binlog.000003, use this command: 

shell> mysqlbinlog binlog.0000003

The output includes events contained in binlog.000003. For statement-based logging, event information includes the SQL statement, the ID of the server on which it was executed, the timestamp when the statement was executed, how much time it took, and so forth. For row-based logging, the event indicates a row change rather than an SQL statement. See , "Replication Formats", for information about logging modes.

Events are preceded by header comments that provide additional information. For example: 

# at 141
#100309 9:28:36 server id 123 end_log_pos 245
 Query thread_id=3350 exec_time=11 error_code=0

In the first line, the number following at indicates the starting position of the event in the binary log file.

The second line starts with a date and time indicating when the statement started on the server where the event originated. For replication, this timestamp is propagated to slave servers. server id is the server_id value of the server where the event originated. end_log_pos indicates where the next event starts (that is, it is the end position of the current event + 1). thread_id indicates which thread executed the event. exec_time is the time spent executing the event, on a master server. On a slave, it is the difference of the end execution time on the slave minus the beginning execution time on the master. The difference serves as an indicator of how much replication lags behind the master. error_code indicates the result from executing the event. Zero means that no error occurred.

The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to redo the statements in the log. This is useful for recovery operations after a server crash. For other usage examples, see the discussion later in this section and in , "Point-in-Time (Incremental) Recovery Using the Binary Log".

Normally, you use mysqlbinlog to read binary log files directly and apply them to the local MariaDB server. It is also possible to read binary logs from a remote server by using the --read-from-remote-server option. To read remote binary logs, the connection parameter options can be given to indicate how to connect to the server. These options are --host, --password, --port, --protocol, --socket, and --user; they are ignored except when you also use the --read-from-remote-server option.

mysqlbinlog supports the following options, which can be specified on the command line or in the [mysqlbinlog] and [client] groups of an option file. mysqlbinlog also supports the options for processing option files described at , "Command-Line Options that Affect Option-File Handling".

Table 4.11. mysqlbinlog Options

Format Option File Description Introduction Deprecated Removed
--base64-output[=value] base64-output Print binary log entries using base-64 encoding
--bind-address=ip_address bind-address Use the specified network interface to connect to the MariaDB Server
--binlog-row-event-max-size=# binlog-row-event-max-size Binary log max event size
--character-sets-dir=path character-sets-dir The directory where character sets are installed
--database=db_name database List entries for just this database
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.6.2
--disable-log-bin disable-log-bin Disable binary logging
--force-read force-read If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning
--help Display help message and exit
--hexdump hexdump Display a hex dump of the log in comments
--host=host_name host Connect to the MariaDB server on the given host
--local-load=path local-load Prepare local temporary files for LOAD DATA INFILE in the specified directory
--offset=# offset Skip the first N entries in the log
--password[=password] password The password to use when connecting to the server
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.6.2
--port=port_num port The TCP/IP port number to use for the connection
--protocol=type protocol The connection protocol to use
--raw raw Write events in raw (binary) format to output files
--read-from-remote-server read-from-remote-server Read the binary log from a MariaDB server rather than reading a local log file
--result-file=name result-file Direct output to the given file
--server-id=id server-id Extract only those events created by the server having the given server ID
--set-charset=charset_name set-charset Add a SET NAMES charset_name statement to the output
--short-form short-form Display only the statements contained in the log
--socket=path socket For connections to localhost
--start-datetime=datetime start-datetime Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument
--start-position=# start-position Start reading the binary log at the first event having a position equal to or greater than the argument
--stop-datetime=datetime stop-datetime Stop reading the binary log at the first event having a timestamp equal to or greater than the datetime argument
--stop-never stop-never Stay connected to server after reading last binary log file
--stop-never-slave-server-id=# stop-never-slave-server-id Slave server ID to report when connecting to server
--stop-position=# stop-position Stop reading the binary log at the first event having a position equal to or greater than the argument
--to-last-log to-last-log Do not stop at the end of the requested binary log from a MariaDB server, but rather continue printing until the end of the last binary log
--user=user_name, user The MariaDB user name to use when connecting to the server
--verbose Reconstruct row events as SQL statements
--version Display version information and exit

You can also set the following variable by using --var_name=value syntax:

You can pipe the output of mysqlbinlog into the mysql client to execute the events contained in the binary log. This technique is used to recover from a crash when you have an old backup (see , "Point-in-Time (Incremental) Recovery Using the Binary Log"). For example: 

shell> mysqlbinlog binlog.000001 | mysql -u root -p

Or: 

shell> mysqlbinlog binlog.[0-9]* | mysql -u root -p

If the statements produced by mysqlbinlog may contain BLOB values, these may cause problems when mysql processes them. In this case, invoke mysql with the --binary-mode option.

You can also redirect the output of mysqlbinlog to a text file instead, if you need to modify the statement log first (for example, to remove statements that you do not want to execute for some reason). After editing the file, execute the statements that it contains by using it as input to the mysql program: 

shell> mysqlbinlog binlog.000001 > tmpfile
shell> ... edit tmpfile ...
shell> mysql -u root -p < tmpfile

When mysqlbinlog is invoked with the --start-position option, it displays only those events with an offset in the binary log greater than or equal to a given position (the given position must match the start of one event). It also has options to stop and start when it sees an event with a given date and time. This enables you to perform point-in-time recovery using the --stop-datetime option (to be able to say, for example, "roll forward my databases to how they were today at 10:30 a.m.").

If you have more than one binary log to execute on the MariaDB server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe: 

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using multiple connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports "unknown table."

To avoid problems like this, use a single mysql process to execute the contents of all binary logs that you want to process. Here is one way to do so: 

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file: 

shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e 'source /tmp/statements.sql'

mysqlbinlog can produce output that reproduces a LOAD DATA INFILE operation without the original data file. mysqlbinlog copies the data to a temporary file and writes a LOAD DATA LOCAL INFILE statement that refers to the file. The default location of the directory where these files are written is system-specific. To specify a directory explicitly, use the --local-load option.

Because mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA LOCAL INFILE statements (that is, it adds LOCAL), both the client and the server that you use to process the statements must be configured with the LOCAL capability enabled. See , "Security Issues with LOAD DATA LOCAL".Warning

The temporary files created for LOAD DATA LOCAL statements are not automatically deleted because they are needed until you actually execute those statements. You should delete the temporary files yourself after you no longer need the statement log. The files can be found in the temporary file directory and have names like original_file_name-#-#.

mysqlbinlog Hex Dump Format

The --hexdump option causes mysqlbinlog to produce a hex dump of the binary log contents: 

shell> mysqlbinlog --hexdump master-bin.000001

The hex output consists of comment lines beginning with #, so the output might look like this for the preceding command: 

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051024 17:24:13 server id 1 end_log_pos 98
# Position Timestamp Type Master ID Size Master Pos Flags
# 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00
# 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
# 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............|
# 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
# 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......|
# 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...|
# Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
# at startup ROLLBACK;

Hex dump output currently contains the elements in the following list. This format is subject to change. (For more information about binary log format, see http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log.)

mysqlbinlog Row Event Display

The following examples illustrate how mysqlbinlog displays row events that specify data modifications. These correspond to events with the WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT type codes. The --base64-output=DECODE-ROWS and --verbose options may be used to affect row event output.

Suppose that the server is using row-based binary logging and that you execute the following sequence of statements: 

CREATE TABLE t
(
 id INT NOT NULL,
 name VARCHAR(20) NOT NULL,
 date DATE NULL
) ENGINE = InnoDB;
START TRANSACTION;
INSERT INTO t VALUES(1, 'apple', NULL);
UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
DELETE FROM t WHERE id = 1;
COMMIT;

By default, mysqlbinlog displays row events encoded as base-64 strings using BINLOG statements. Omitting extraneous lines, the output for the row events produced by the preceding statement sequence looks like this: 

shell> mysqlbinlog log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;

To see the row events as comments in the form of "pseudo-SQL" statements, run mysqlbinlog with the --verbose or -v option. The output will contain lines beginning with ###: 

shell> mysqlbinlog -v log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'

Specify --verbose or -v twice to also display data types and some metadata for each column. The output will contain an additional comment following each column change: 

shell> mysqlbinlog -vv log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */

You can tell mysqlbinlog to suppress the BINLOG statements for row events by using the --base64-output=DECODE-ROWS option. This is similar to --base64-output=NEVER but does not exit with an error if a row event is found. The combination of --base64-output=DECODE-ROWS and --verbose provides a convenient way to see row events only as SQL statements: 

shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'
Note

You should not suppress BINLOG statements if you intend to re-execute mysqlbinlog output.

The SQL statements produced by --verbose for row events are much more readable than the corresponding BINLOG statements. However, they do not correspond exactly to the original SQL statements that generated the events. The following limitations apply:

Proper interpretation of row events requires the information from the format description event at the beginning of the binary log. Because mysqlbinlog does not know in advance whether the rest of the log contains row events, by default it displays the format description event using a BINLOG statement in the initial part of the output.

If the binary log is known not to contain any events requiring a BINLOG statement (that is, no row events), the --base64-output=NEVER option can be used to prevent this header from being written.

Using mysqlbinlog to Back Up Binary Log Files

By default, mysqlbinlog reads binary log files and displays their contents in text format. This enables you to examine events within the files more easily or be re-execute them (for example, by using the output as input to mysql). mysqlbinlog can read log files directly from the local file system, or, with the --read-from-remote-server option, it can connect to a server and request binary log contents from that server. mysqlbinlog writes text output to its standard output, or to the file named as the value of the --result-file=file_name option if that option is given.

As of MariaDB 5.6, mysqlbinlog can read binary log files and write new files containing the same content-that is, in binary format rather than text format. This capability enables you to easily back up a binary log in its original format. mysqlbinlog can make a static backup, backing up a set of log files and stopping when the end of the last file is reached. It can also make a continuous ("live") backup, staying connected to the server when it reaches the end of the last log file and continuing to copy new events as they are generated. In continuous-backup operation, mysqlbinlog runs until the connection ends (for example, when the server exits) or mysqlbinlog is forcibly terminated. When the connection ends, mysqlbinlog does not wait and retry the connection, unlike a slave replication server. To continue a live backup after the server has been restarted, you must also restart mysqlbinlog.

Binary log backup requires that you invoke mysqlbinlog with two options at minimum:

Along with --read-from-remote-server, it is common to specify other options: --host indicates where the server is running, and you may also need to specify connection options such as --user and --password.

Several other options are useful in conjunction with --raw:

To back up a server's binary log files with mysqlbinlog, you must specify file names that actually exist on the server. If you do not know the names, connect to the server and use the SHOW BINARY LOGS statement to see the current names. Suppose that the statement produces this output: 

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000130 | 27459 |
| binlog.000131 | 13719 |
| binlog.000132 | 43268 |
+---------------+-----------+

With that information, you can use mysqlbinlog to back up the binary log to the current directory as follows (enter each command on a single line):

Output File Naming

Without --raw, mysqlbinlog produces text output and the --result-file option, if given, specifies the name of the single file to which all output is written. With --raw, mysqlbinlog writes one binary output file for each log file transferred from the server. By default, mysqlbinlog writes the files in the current directory with the same names as the original log files. To modify the output file names, use the --result-file option. In conjunction with --raw, the --result-file option value is treated as a prefix that modifies the output file names.

Suppose that a server currently has binary log files named binlog.000999 and up. If you use mysqlbinlog --raw to back up the files, the --result-file option produces output file names as shown in the following table. You can write the files to a specific directory by beginning the --result-file value with the directory path. If the --result-file value consists only of a directory name, the value must end with the pathname separator character. Output files are overwritten if they exist.

--result-file Option Output File Names
--result-file=x xbinlog.000999 and up
--result-file=/tmp/ /tmp/binlog.000999 and up
--result-file=/tmp/x /tmp/xbinlog.000999 and up
Example: mysqldump + mysqlbinlog for Backup and Restore

The following example describes a simple scenario that shows how to use mysqldump and mysqlbinlog together to back up a server's data and binary log, and how to use the backup to restore the server if data loss occurs. The example assumes that the server is running on host host_name and its first binary log file is named binlog.000999. Enter each command on a single line.

Use mysqlbinlog to make a continuous backup of the binary log: 

mysqlbinlog --read-from-remote-server --host=host_name --raw
 --stop-never binlog.000999

Use mysqldump to create a dump file as a snapshot of the server's data. Use --all-databases, --events, and --routines to back up all data, and --master-data=2 to include the current binary log coordinates in the dump file. 

mysqldump --host=host_name --all-databases --events --routines --master-data=2> dump_file

Execute the mysqldump command periodically to create newer snapshots as desired.

If data loss occurs (for example, if the server crashes), use the most recent dump file to restore the data: 

mysql --host=host_name -u root -p < dump_file

Then use the binary log backup to re-execute events that were written after the coordinates listed in the dump file. Suppose that the coordinates in the file look like this: 

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284;

If the most recent backed-up log file is named binlog.001004, re-execute the log events like this: 

mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004
 | mysql --host=host_name -u root -p

You might find it easier to copy the backup files (dump file and binary log files) to the server host to make it easier to perform the restore operation, or if MariaDB does not allow remote root access.

Specifying the mysqlbinlog Server ID

When invoked with the --read-from-remote-server option, mysqlbinlog connects to a MariaDB server, specifies a server ID to identify itself, and requests binary log files from the server. You can use mysqlbinlog to request log files from a server in several ways:

With --read-from-remote-server only, mysqlbinlog connects using a server ID of 0, which tells the server to disconnect after sending the last requested log file.

With --read-from-remote-server and --stop-never, mysqlbinlog connects using a nonzero server ID, so the server does not disconnect after sending the last log file. The server ID is 65535 by default, but this can be changed with --stop-never-slave-server-id.

Thus, for the first two ways of requesting files, the server disconnects because mysqlbinlog specifies a server ID of 0. It does not disconnect if --stop-never is given because mysqlbinlog specifies a nonzero server ID.

mysqldumpslow - Summarize Slow Query Log Files

The MariaDB slow query log contains information about queries that take a long time to execute (see , "The Slow Query Log"). mysqldumpslow parses MariaDB slow query log files and prints a summary of their contents.

Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It "abstracts" these values to N and 'S' when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.

Invoke mysqldumpslow like this: 

shell> mysqldumpslow [options] [log_file ...]

mysqldumpslow supports the following options.

Table 4.12. mysqldumpslow Options

Format Option File Description Introduction Deprecated Removed
-a Do not abstract all numbers to N and strings to S
-n num Abstract numbers with at least the specified digits
--debug debug Write debugging information
-g pattern Only consider statements that match the pattern
--help Display help message and exit
-h name Host name of the server in the log file name
-i name Name of the server instance
-l Do not subtract lock time from total time
-r Reverse the sort order
-s value How to sort output
-t num Display only first num queries
--verbose verbose Verbose mode

Example of usage: 

shell> mysqldumpslow
Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1
Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

mysqlhotcopy - A Database Backup Program

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix.

To use mysqlhotcopy, you must have read access to the files for the tables that you are backing up, the SELECT privilege for those tables, the RELOAD privilege (to be able to execute FLUSH TABLES), and the LOCK TABLES privilege (to be able to lock the tables). 

shell> mysqlhotcopy db_name [/path/to/new_directory]

shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Back up tables in the given database that match a regular expression: 

shell> mysqlhotcopy db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde ("~"): 

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy supports the following options, which can be specified on the command line or in the [mysqlhotcopy] and [client] groups of an option file. For information about option files, see , "Using Option Files".

Table 4.13. mysqlhotcopy Options

Format Option File Description Introduction Deprecated Removed
--addtodest addtodest Do not rename target directory (if it exists); merely add files to it
--allowold allowold Do not abort if a target exists; rename it by adding an _old suffix
--checkpoint=db_name.tbl_name checkpoint Insert checkpoint entries
--chroot=path chroot Base directory of the chroot jail in which mysqld operates
--debug debug Write a debugging log
--dryrun dryrun Report actions without performing them
--flushlog flushlog Flush logs after all tables are locked
--help Display help message and exit
--host=host_name host Connect to the MariaDB server on the given host
--keepold keepold Do not delete previous (renamed) target when done
--noindices noindices Do not include full index files in the backup
--old_server old_server Connect to server that does not support FLUSH TABLES tbl_list WITH READ LOCK
--password[=password] password The password to use when connecting to the server
--port=port_num port The TCP/IP port number to use for the connection
--quiet quiet Be silent except for errors
--regexp regexp Copy all databases with names that match the given regular expression
--resetmaster resetmaster Reset the binary log after locking all the tables
--resetslave resetslave Reset the master.info file after locking all the tables
--socket=path socket For connections to localhost
--tmpdir=path tmpdir The temporary directory
--user=user_name, user The MariaDB user name to use when connecting to the server

Use perldoc for additional mysqlhotcopy documentation, including information about the structure of the tables needed for the --checkpoint and --record_log_pos options: 

shell> perldoc mysqlhotcopy

mysql_convert_table_format - Convert Tables to Use a Given Storage Engine

mysql_convert_table_format converts the tables in a database to use a particular storage engine (MyISAM by default). mysql_convert_table_format is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see , "Perl Installation Notes").

Invoke mysql_convert_table_format like this: 

shell> mysql_convert_table_format [options]db_name

The db_name argument indicates the database containing the tables to be converted.

mysql_convert_table_format supports the options described in the following list.

mysql_find_rows - Extract SQL Statements from Files

mysql_find_rows reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements. The utility was written for use with update log files (as used prior to MariaDB 5.0) and as such expects statements to be terminated with semicolon (;) characters. It may be useful with other files that contain SQL statements as long as statements are terminated with semicolons.

Invoke mysql_find_rows like this: 

shell> mysql_find_rows [options] [file_name ...]

Each file_name argument should be the name of file containing SQL statements. If no file names are given, mysql_find_rows reads the standard input.

Examples: 

mysql_find_rows --regexp=problem_table --rows=20 < update.log mysql_find_rows --regexp=problem_table update-log.1 update-log.2

mysql_find_rows supports the following options:

mysql_fix_extensions - Normalize Table File Name Extensions

mysql_fix_extensions converts the extensions for MyISAM (or ISAM) table files to their canonical forms. It looks for files with extensions matching any lettercase variant of .frm, .myd, .myi, .isd, and .ism and renames them to have extensions of .frm, .MYD, .MYI, .ISD, and .ISM, respectively. This can be useful after transferring the files from a system with case-insensitive file names (such as Windows) to a system with case-sensitive file names.

Invoke mysql_fix_extensions like this, where data_dir is the path name to the MariaDB data directory. 

shell> mysql_fix_extensions data_dir

mysql_setpermission - Interactively Set Permissions in Grant Tables

mysql_setpermission is a Perl script that was originally written and contributed by Luuk de Boer. It interactively sets permissions in the MariaDB grant tables. mysql_setpermission is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see , "Perl Installation Notes").

Invoke mysql_setpermission like this: 

shell> mysql_setpermission [options]

options should be either --help to display the help message, or options that indicate how to connect to the MariaDB server. The account used when you connect determines which permissions you have when attempting to modify existing permissions in the grant tables.

mysql_setpermissions also reads options from the [client] and [perl] groups in the .my.cnf file in your home directory, if the file exists.

mysql_setpermission supports the following options:

mysql_waitpid - Kill Process and Wait for Its Termination

mysql_waitpid signals a process to terminate and waits for the process to exit. It uses the kill() system call and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_waitpid like this: 

shell> mysql_waitpid [options] pid wait_time

mysql_waitpid sends signal 0 to the process identified by pid and waits up to wait_time seconds for the process to terminate. pid and wait_time must be positive integers.

If process termination occurs within the wait time or the process does not exist, mysql_waitpid returns 0. Otherwise, it returns 1.

If the kill() system call cannot handle signal 0, mysql_waitpid() uses signal 1 instead.

mysql_waitpid supports the following options:

mysql_zap - Kill Processes That Match a Pattern

mysql_zap kills processes that match a pattern. It uses the ps command and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_zap like this: 

shell> mysql_zap [-signal] [-?Ift] pattern

A process matches if its output line from the ps command contains the pattern. By default, mysql_zap asks for confirmation for each process. Respond y to kill the process, or q to exit mysql-zap. For any other response, mysql_zap does not attempt to kill the process.

If the -signal option is given, it specifies the name or number of the signal to send to each process. Otherwise, mysql_zap tries first with TERM (signal 15) and then with KILL (signal 9).

mysql_zap supports the following additional options:

MySQL Program Development Utilities

msql2mysql - Convert mSQL Programs for Use with MySQL
mysql_config - Get Compile Options for Compiling Clients
my_print_defaults - Display Options from Option Files
resolve_stack_dump - Resolve Numeric Stack Trace Dump to Symbols

This section describes some utilities that you may find useful when developing MariaDB programs.

In shell scripts, you can use the my_print_defaults program to parse option files and see what options would be used by a given program. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups: 

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MariaDB clients to see how to do this.

Several other language interfaces to MariaDB are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.

msql2mysql - Convert mSQL Programs for Use with MariaDB

Initially, the MariaDB C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MariaDB by changing the names of the C API functions.

The msql2mysql utility performs the conversion of mSQL C API function calls to their MariaDB equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this: 

shell> cp client-prog.c client-prog.c.orig
shell> msql2mysql client-prog.c
client-prog.c converted

Then examine client-prog.c and make any post-conversion revisions that may be necessary.

msql2mysql uses the replace utility to make the function name substitutions. See , "replace - A String-Replacement Utility".

mysql_config - Get Compile Options for Compiling Clients

mysql_config provides you with useful information for compiling your MariaDB client and connecting it to MariaDB.

mysql_config supports the following options.

If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values: 

shell> mysql_config
Usage: /usr/local/mysql/bin/mysql_config [options]
Options:
 --cflags [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]
 --include [-I/usr/local/mysql/include/mysql]
 --libs [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz
 -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
 --libs_r [-L/usr/local/mysql/lib/mysql -lmysqlclient_r
 -lpthread -lz -lcrypt -lnsl -lm -lpthread]
 --socket [/tmp/mysql.sock]
 --port [3306]
 --version [4.0.16]
 --libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz
 -lcrypt -lnsl -lm -lpthread -lrt]

You can use mysql_config within a command line to include the value that it displays for a particular option. For example, to compile a MariaDB client program, use mysql_config as follows: 

shell> CFG=/usr/local/mysql/bin/mysql_config
shell> sh -c 'gcc -o progname `$CFG --include` progname.c `$CFG --libs`'

When you use mysql_config this way, be sure to invoke it within backtick ("`") characters. That tells the shell to execute it and substitute its output into the surrounding command.

my_print_defaults - Display Options from Option Files

my_print_defaults displays the options that are present in 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. To see what options are present in those groups in the standard option files, invoke my_print_defaults like this: 

shell> my_print_defaults mysqlcheck client
--user=myusername
--password=secret
--host=localhost

The output consists of options, one per line, in the form that they would be specified on the command line.

my_print_defaults supports the following options.

resolve_stack_dump - Resolve Numeric Stack Trace Dump to Symbols

resolve_stack_dump resolves a numeric stack dump to symbols.

Invoke resolve_stack_dump like this: 

shell> resolve_stack_dump [options] symbols_file [numeric_dump_file]

The symbols file should include the output from the nm --numeric-sort mysqld command. The numeric dump file should contain a numeric stack track from mysqld. If no numeric dump file is named on the command line, the stack trace is read from the standard input.

resolve_stack_dump supports the following options.

Miscellaneous Programs

perror - Explain Error Codes
replace - A String-Replacement Utility
resolveip - Resolve Host name to IP Address or Vice Versa

perror - Explain Error Codes

For most system errors, MariaDB displays, in addition to an internal text message, the system error code in one of the following styles: 

message ... (errno: #)
message ... (Errcode: #)

You can find out what the error code means by examining the documentation for your system or by using the perror utility.

perror prints a description for a system error code or for a storage engine (table handler) error code.

Invoke perror like this: 

shell> perror [options] errorcode ...

Example: 

shell> perror 13 64
OS error code 13: Permission denied OS error code 64: Machine is not on the network

To obtain the error message for a MariaDB Cluster error code, invoke perror with the --ndb option: 

shell> perror --ndb errorcode

Note that the meaning of system error messages may be dependent on your operating system. A given error code may mean different things on different operating systems.

perror supports the following options.

replace - A String-Replacement Utility

The replace utility program changes strings in place in files or on the standard input.

Invoke replace in one of the following ways: 

shell> replace from to [from to] ... -- file_name [file_name] ...
shell> replace from to [from to] ... < file_name

from represents a string to look for and to represents its replacement. There can be one or more pairs of strings.

Use the -- option to indicate where the string-replacement list ends and the file names begin. In this case, any file named on the command line is modified in place, so you may want to make a copy of the original before converting it. replace prints a message indicating which of the input files it actually modifies.

If the -- option is not given, replace reads the standard input and writes to the standard output.

replace uses a finite state machine to match longer strings first. It can be used to swap strings. For example, the following command swaps a and b in the given files, file1 and file2: 

shell> replace a b b a -- file1 file2 ...

The replace program is used by msql2mysql. See , "msql2mysql - Convert mSQL Programs for Use with MySQL".

replace supports the following options.

resolveip - Resolve Host name to IP Address or Vice Versa

The resolveip utility resolves host names to IP addresses and vice versa.

Invoke resolveip like this: 

shell> resolveip [options] {host_name|ip-addr} ...

resolveip supports the following options.

Copyright 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Next
Tutorial Home Chapter 5. MariaDB Server Administration