1 Overview of MySQL Programs
[root@MaxwellDBA ~]# mysql --help
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
(Defaults to on; use --skip-auto-rehash to disable.)
-A, --no-auto-rehash
No automatic rehashing. One has to use 'rehash' to get
table and field completion. This gives a quicker start of
mysql and disables rehashing on reconnect.
--auto-vertical-output
Automatically switch to vertical output mode if the
result is wider than the terminal width.
-B, --batch Don't use history file. Disable interactive behavior.
(Enables --silent.)
--bind-address=name IP address to bind to.
--binary-as-hex Print binary data as hex. Enabled by default for
interactive terminals.
--character-sets-dir=name
Directory for character set files.
--column-type-info Display column type information.
-c, --comments Preserve comments. Send comments to the server. The
default is --skip-comments (discard comments), enable
with --comments.
-C, --compress Use compression in server/client protocol.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
-T, --debug-info This is a non-debug version. Catch this and exit.
-D, --database=name Database to use.
--default-character-set=name
Set the default character set.
--delimiter=name Delimiter to be used.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
-e, --execute=name Execute command and quit. (Disables --force and history
file.)
-E, --vertical Print the output of a query (rows) vertically.
-f, --force Continue even if we get an SQL error.
--histignore=name A colon-separated list of patterns to keep statements
from getting logged into syslog and mysql history.
-G, --named-commands
Enable named commands. Named commands mean this program's
internal commands; see mysql> help . When enabled, the
named commands can be used from any line of the query,
otherwise only from the first line, before an enter.
Disable with --disable-named-commands. This option is
disabled by default.
-i, --ignore-spaces Ignore space after function names.
--init-command=name SQL Command to execute when connecting to MySQL server.
Will automatically be re-executed when reconnecting.
--local-infile Enable/disable LOAD DATA LOCAL INFILE.
-b, --no-beep Turn off beep on error.
-h, --host=name Connect to host.
--dns-srv-name=name Connect to a DNS SRV resource
-H, --html Produce HTML output.
-X, --xml Produce XML output.
--line-numbers Write line numbers for errors.
(Defaults to on; use --skip-line-numbers to disable.)
-L, --skip-line-numbers
Don't write line number for errors.
-n, --unbuffered Flush buffer after each query.
--column-names Write column names in results.
(Defaults to on; use --skip-column-names to disable.)
-N, --skip-column-names
Don't write column names in results.
--sigint-ignore Ignore SIGINT (CTRL-C).
-o, --one-database Ignore statements except those that occur while the
default database is the one named at the command line.
--pager[=name] Pager to use to display results. If you don't supply an
option, the default pager is taken from your ENV variable
PAGER. Valid pagers are less, more, cat [> filename],
etc. See interactive help (\h) also. This option does not
work in batch mode. Disable with --disable-pager. This
option is disabled by default.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--prompt=name Set the mysql prompt to this value.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't cache result, print it row by row. This may slow
down the server if the output is suspended. Doesn't use
history file.
-r, --raw Write fields without conversion. Used with --batch.
--reconnect Reconnect if the connection is lost. Disable with
--disable-reconnect. This option is enabled by default.
(Defaults to on; use --skip-reconnect to disable.)
-s, --silent Be more silent. Print results with a tab as separator,
each row on new line.
-S, --socket=name The socket file to use for connection.
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
--ssl-mode=name SSL connection mode.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2, TLSv1.3
--ssl-fips-mode=name
SSL FIPS mode (applies only for OpenSSL); permitted
values are: OFF, ON, STRICT
--tls-ciphersuites=name
TLS v1.3 cipher to use.
-t, --table Output in table format.
--tee=name Append everything into outfile. See interactive help (\h)
also. Does not work in batch mode. Disable with
--disable-tee. This option is disabled by default.
-u, --user=name User for login if not current user.
-U, --safe-updates Only allow UPDATE and DELETE that uses keys.
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
-v, --verbose Write more. (-v -v -v gives the table output format).
-V, --version Output version information and exit.
-w, --wait Wait and retry if connection is down.
--connect-timeout=# Number of seconds before connection timeout.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--select-limit=# Automatic limit for SELECT when using --safe-updates.
--max-join-size=# Automatic limit for rows in a join when using
--safe-updates.
--show-warnings Show warnings after every statement.
-j, --syslog Log filtered interactive commands to syslog. Filtering of
commands depends on the patterns supplied via histignore
option besides the default patterns.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
--binary-mode By default, ASCII '\0' is disallowed and '\r\n' is
translated to '\n'. This switch turns off both features,
and also turns off parsing of all clientcommands except
\C and DELIMITER, in non-interactive mode (for input
piped to mysql or loaded using the 'source' command).
This is necessary when processing output from mysqlbinlog
that may contain blobs.
--connect-expired-password
Notify the server that this client is prepared to handle
expired password sandbox mode.
--network-namespace=name
Network namespace to use for connection via tcp with a
server.
--compression-algorithms=name
Use compression algorithm in server/client protocol.
Valid values are any combination of
'zstd','zlib','uncompressed'.
--zstd-compression-level=#
Use this compression level in the client/server protocol,
in case --compression-algorithms=zstd. Valid range is
between 1 and 22, inclusive. Default is 3.
--load-data-local-dir=name
Directory path safe for LOAD DATA LOCAL INFILE to read
from.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
auto-rehash TRUE
auto-vertical-output FALSE
bind-address (No default value)
binary-as-hex FALSE
character-sets-dir (No default value)
column-type-info FALSE
comments FALSE
compress FALSE
database (No default value)
default-character-set auto
delimiter ;
enable-cleartext-plugin FALSE
vertical FALSE
force FALSE
histignore (No default value)
named-commands FALSE
ignore-spaces FALSE
init-command (No default value)
local-infile FALSE
no-beep FALSE
host (No default value)
dns-srv-name (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 0
prompt mysql>
quick FALSE
raw FALSE
reconnect TRUE
socket (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
tls-ciphersuites (No default value)
table FALSE
user (No default value)
safe-updates FALSE
i-am-a-dummy FALSE
connect-timeout 0
max-allowed-packet 16777216
net-buffer-length 16384
select-limit 1000
max-join-size 1000000
show-warnings FALSE
plugin-dir (No default value)
default-auth (No default value)
binary-mode FALSE
connect-expired-password FALSE
network-namespace (No default value)
compression-algorithms (No default value)
zstd-compression-level 3
load-data-local-dir (No default value)
[root@MaxwellDBA ~]#
The MySQL server,mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that assist you in starting and stopping the server.
- mysqld (The SQL daemon(the MySQL server). To use client programs,mysqld must be running,because clients gains access to databases by connecting to the server.)
- mysqld_safe (a server startup script.mysqld_safe attempts to start mysqld.)
- mysql.server (A server startup script)
- mysqld_multi (A server startup script that can start or stop multiple servers installed on the system)
- comp_err (This program is used during the MySQL build/installation process. It compiles error message files from the error source files.)
- mysql_secure_installation (This program enables you to improve the security of your MySQL installation.)
- mysql_ssl_rsa_setup (This program creates the SSL certificate and key files and RSA key-pair files required to support secure connections, if those files are missing.)
- mysql_tzinfo_to_sql (This program loads the time zone tables in the
mysql
database using the contents of the host system zoneinfo database (the set of files describing time zones).) - mysql_upgrade (Prior to MySQL 8.0.16, this program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL, and checks tables for incompatibilities and repairs them if necessary.)
MySQL client programs that connect to the MySQL server.
- mysql (The command-line tool for interactively entering SQL statements or executing them from a file in batch mode)
- mysqladmin (A client that performs administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files.)
- mysqlcheck (A table-maintenance client that checks, repairs, analyzes, and optimizes tables.)
- mysqldump (A client that dumps a MySQL database into a file as SQL, text, or XML.)
- mysqlimport (A client that imports text files into their respective tables using LOAD DATA )
- mysqlpump (A client that dumps a MySQL database into a file as SQL.)
- mysqlsh (MySQL Shell is an advanced client and code editor for MySQL Server.)
- mysqlshow (A client that displays information about databases, tables, columns, and indexes.)
- mysqlslap (A client that is designed to emulate client load for a MySQL server and report the timing of each stage.)
MySQL administrative and utility programs:
- innochecksum (An offline
InnoDB
offline file checksum utility.) - myisam_ftdump (A utility that displays information about full-text indexes in
MyISAM
tables.) - myisamchk (A utility to describe, check, optimize, and repair
MyISAM
tables.) - myisamlog (A utility that processes the contents of a
MyISAM
log file. ) - myisampack (A utility that compresses
MyISAM
tables to produce smaller read-only tables.) - mysql_config_editor (A utility that enables you to store authentication credentials in a secure, encrypted login path file named
.mylogin.cnf
.) - mysql_migrate_keying (A utility for migrating keys between one keyring component and another.)
- mysqlbinlog (A utility for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash.)
- mysqldumpslow (A utility to read and summarize the contents of a slow query log.)
MySQL program-development utilities:
- mysql_config (A shell script that produces the option values needed when compiling MySQL programs.)
- my_print_defaults (A utility that shows which options are present in option groups of option files.)
Miscellaneous utilities:
- lz4_decompress (A utility that decompresses mysqlpump output that was created using LZ4 compression.)
- perror (A utility that displays the meaning of system or MySQL error codes.)
- zlib_decompress (A utility that decompresses mysqlpump output that was created using ZLIB compression. )
MySQL client programs that communicate with the server using the MySQL 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_DEBUG | Debug trace options when debugging |
TMPDIR | The directory where temporary tables and files are created |
Specifying Program Options
1.Using Options on the Command Line
[root@MaxwellDBA ~]# mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password:
+-----------+
| VERSION() |
+-----------+
| 8.0.26 |
+-----------+
+---------------------+
| NOW() |
+---------------------+
| 2022-10-12 10:34:42 |
+---------------------+
[root@MaxwellDBA ~]#
2 Using Option Files
On Unix and Unix-like systems, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).
Table 4.2 Option Files Read on Unix and Unix-Like Systems
File Name | Purpose |
---|---|
/etc/my.cnf | Global options |
/etc/mysql/my.cnf | Global options |
| Global options |
$MYSQL_HOME/my.cnf | Server-specific options (server only) |
defaults-extra-file | The file specified with --defaults-extra-file, if any |
~/.my.cnf | User-specific options |
~/.mylogin.cnf | User-specific login path options (clients only) |
| System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only) |
mysqld — The MySQL Server
The mysqld program has many options that can be specified at startup.
mysqld --verbose --help
mysqld_safe — MySQL Server Startup Script
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.
Table 4.6 mysqld_safe Options
Option Name | Description |
---|---|
--basedir | Path to MySQL installation directory |
--core-file-size | Size of core file that mysqld should be able to create |
--datadir | Path to data directory |
--defaults-extra-file | Read named option file in addition to usual option files |
--defaults-file | Read only named option file |
--help | Display help message and exit |
--ledir | Path to directory where server is located |
--log-error | Write error log to named file |
--malloc-lib | Alternative malloc library to use for mysqld |
--mysqld | Name of server program to start (in ledir directory) |
--mysqld-safe-log-timestamps | Timestamp format for logging |
--mysqld-version | Suffix for server program name |
--nice | Use nice program to set server scheduling priority |
--no-defaults | Read no option files |
--open-files-limit | Number of files that mysqld should be able to open |
--pid-file | Path name of server process ID file |
--plugin-dir | Directory where plugins are installed |
--port | Port number on which to listen for TCP/IP connections |
--skip-kill-mysqld | Do not try to kill stray mysqld processes |
--skip-syslog | Do not write error messages to syslog; use error log file |
--socket | Socket file on which to listen for Unix socket connections |
--syslog | Write error messages to syslog |
--syslog-tag | Tag suffix for messages written to syslog |
--timezone | Set TZ time zone environment variable to named value |
--user | Run mysqld as user having name user_name or numeric user ID user_id |
mysqld_safe --port=port_num --defaults-file=file_name
mysqld_safe --defaults-file=file_name --port=port_num
mysql.server — MySQL Server Startup Script
mysql.server is the script name as used within the MySQL source tree.
To start or stop the server manually using the mysql.server script, invoke it from the command line with start
or stop
arguments:
mysql.server start
mysql.server stop
You can add options for mysql.server in a global /etc/my.cnf
file. A typical my.cnf
file might look like this:
[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql
[mysql.server]
basedir=/usr/local/mysql
Table 4.7 mysql.server Option-File Options
Option Name | Description | Type |
---|---|---|
basedir | Path to MySQL installation directory | Directory name |
datadir | Path to MySQL data directory | Directory name |
pid-file | File in which server should write its process ID | File name |
service-startup-timeout | How long to wait for server startup |
mysqld_multi — Manage Multiple MySQL 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.
To invoke mysqld_multi, use the following syntax:
mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]
This command starts a single server using option group [mysqld17]
:
mysqld_multi start 17
This command stops several servers, using option groups [mysqld8]
and [mysqld10]
through [mysqld13]
:
mysqld_multi stop 8,10-13
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
password = my_password
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/data2/hostname.pid2
datadir = /usr/local/mysql/data2
language = /usr/local/mysql/share/mysql/english
user = unix_user1
[mysqld3]
mysqld = /path/to/mysqld_safe
ledir = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/data3/hostname.pid3
datadir = /usr/local/mysql/data3
language = /usr/local/mysql/share/mysql/swedish
user = unix_user2
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /usr/local/mysql/data4/hostname.pid4
datadir = /usr/local/mysql/data4
language = /usr/local/mysql/share/mysql/estonia
user = unix_user3
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /usr/local/mysql/data6/hostname.pid6
datadir = /usr/local/mysql/data6
language = /usr/local/mysql/share/mysql/japanese
user = unix_user4
mysql Client Server-Side Help
mysql>
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql>
mysql> help
For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
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.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
For server side help, type 'help contents'
mysql> \c
mysql> clear
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID(3);
+-------------------+
| LAST_INSERT_ID(3) |
+-------------------+
| 3 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
mysql> resetconnection;
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
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
Components
Compound Statements
Contents
Data Definition
Data Manipulation
Data Types
Functions
Geographic Features
Help Metadata
Language Structure
Loadable Functions
Plugins
Prepared Statements
Replication Statements
Storage Engines
Table Maintenance
Transactions
Utility
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:
FLUSH
PURGE BINARY LOGS
PURGE MASTER LOGS
SHOW
SHOW BINARY LOGS
SHOW MASTER LOGS
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 [HELP PURGE BINARY LOGS], that shows
how to determine which logs can be purged. SHOW BINARY LOGS requires
the REPLICATION CLIENT privilege (or the deprecated SUPER privilege).
Encrypted binary log files have a 512-byte file header that stores
information required for encryption and decryption of the file. This is
included in the file size displayed by SHOW BINARY LOGS. The Encrypted
column shows whether or not the binary log file is encrypted. Binary
log encryption is active if binlog_encryption=ON is set for the server.
Existing binary log files are not encrypted or decrypted if binary log
encryption is activated or deactivated while the server is running.
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000015 | 724935 | Yes |
| binlog.000016 | 733481 | Yes |
+---------------+-----------+-----------+
URL: https://dev.mysql.com/doc/refman/8.0/en/show-binary-logs.html
mysql> HELP rep%
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:
REPAIR TABLE
REPEAT FUNCTION
REPEAT LOOP
REPLACE
REPLACE FUNCTION
categories:
Replication Statements
mysql>