Chapter 4 MySQL Programs

Chapter 4 MySQL Programs

4.1 Overview of MySQL Programs

Most programs provide a --help option.

• mysqld
  The SQL daemon (the MySQL server).
• mysqld_safe
  A server startup script.
• mysql.server
  A server startup script.
  Use System V-style run directories containing scripts. it invokes mysqld_safe to start the MySQL server.

System V-style 是什么意思

• mysqld_multi
  A server startup script that can start or stop multiple servers.

Several programs perform setup operations during MySQL installation or upgrading:

• comp_err
  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
  Creates the SSL certificate and key files and RSA key-pair files required to support secure connections.
• mysql_tzinfo_to_sql
  Loads the time zone tables in the mysql database using the contents of the host system zoneinfo database.
• mysql_upgrade
  Used after a MySQL upgrade operation.
  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.
• mysqlcheck
  A table-maintenance client.
• mysqldump
  A client that dumps a MySQL database into a file.
• 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.
• mysqlshow
  A client that displays information about databases, tables, columns, and indexs.
• 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
  Describe, check, optimize, and repair MyISAM tables.
• myisamlog
  Processes the contents of a MyISAM log file.
• myisampack
  Compresses MyISAM tables to produce smaller read-only tables.
• mysql_config_editor
  Enables you to store authentication credentials in a secure, encrypted login path file named .mylogin.cnf.
• mysqlbinlog
  Reading statements from a binary log.
• mysqldumpslow
  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
  Shows which options are present in option groups of option files.
  Miscellaneous utilities:
• lz4_decompress
  Decompresses mysqlpump output that was created using LZ4 compression.
• perror
  Displays the meaning of system or MySQL error codes.
• zlib_decompress
  Decompresses mysqlpump output that was created using ZLIB compression.
  MySQL Workbench GUI tool administer MySQL servers and databases.

Environment variables.

Environment Variable	Meaning
MYSQL_UNIX_PORT	Default Unix socket file;used for connections to localhost
MYSQL_TCP_PORT	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

4.2 Using MySQL Programs

4.2.1 Invoking MySQL Programs

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

4.2.2 Specifying Program Options

Several ways to specify options:

• List the options on the command line.
• List the options in an option file.
• List the options in environment variables.
  Options are processed in order.
  mysqld --user is used as a security precaution.
  First by examining environment variables, then by processing option files, and then by checking the command line.
  mysqld-auto.cnf option file in the data directory is processed last.

4.2.2.1 Using Options on the Command line

• After the command name.
• One dash is a short form, two dashes is long form.
• Case-sensitive.
• Some options take a value following the option name.
• For a long option that takes a value, separate the option name and the value by an = sign.
  For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. 密码不能有空格
• Within option names, dash(-) and underscore(_) may be used interchangeably.
• The MySQL server has a set of system variables may be set at startup, at runtime, or both.
• For options that take a numberic value, the value can be given with a suffix of k, m, g, t, p, e.
• When specifying file names as option values, avoid the use of the ~ shell metacharacter.
  Option values that contain spaces must be quoted when given on the command line.

mysql -uroot -p -e "select version();select now();"

4.2.2.2 Using Option Files

To determine whether a program reads option files, invoke it with the --help option.
Many option files are plain text files, The exceptions are:

• The .mylogin.cnf file that contains login path options.
• The mysqld-auto.cnf file in the data directory.

####### Option File Processing Order

File Name	Purpose
/etc/my.cnf	global options
/etc/mysql/my.cnf	global options
SYSCONFDIR/my.cnf	global options
$MYSQL_HOME/my.cnf	server only
defaults-extra-file	The file specified with --defaults-extra-file
~/.my.cnf	user-specific options
~/.mylogin.cnf	user-specific login path options
DATADIR/mysqld-auto.cnf	system variables persisted with set persist or se persist_only

####### Option File Syntax

In an option file, you omit the leading two dashes from the option name and you specify only one option per line.

• #comment, ;comment
  Comment lines start with # or ;.
• [group]
  group is the name of the program or group for which you want to set options.
• opt_name
• opt_name=value

If an option group name is the same as a program name, options in the group apply specifically to that program.
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=128m
[mysqldump]
quick

Here is a typical user optin file:

[client]
password="my password"
[mysql]
no-auto-rehash
connect_timeout=2

To create option groups to be read only by mysqld servers from specific MySQL release series.

[mysql-8.0]
sql_mode=traditional

没有测试

####### Option File Inclusions

Use !include directives in option files to include other option files and !includedir to search specific directories for option files.

!include /home/mydir/myopt.cnf
!includedir /home/mydir

4.2.2.3 Command-Line Options that Affect Option-File Handling

• –print-defaults may be used immediately after --defaults-file, --defaults-extra-file, or --login-path.
• –defaults-extra-file=file_name
  Read this option after the global option file before the user option file and before the login path file.
• –defaults-file=file_name
  Read only the given option file.
• –defaults-group-suffix=str
  Read not only the usual option groups, but also groups with the usual names and a suffix of str.
• –login-path=name
  Read options from the named login path in the .mylogin.cnf login path file.
• –no-defaults
  Do not read any option files.
• –print-defaults
  print the program name and all options that it gets from option files.

4.2.2.4 Program Option Modifiers

To disable column names:

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

The “enabled” form of the option:

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

The values on, true, and false are also recognized.
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:

mysql --loose-no-such-option

The --maximum prefix is available for mysqld only and permits a limit to be placed on how large client programs can set session variables.

--maximum-max_heap_table_size=32M

4.2.2.5 Using Options to Set Program Variables

mysql --max_allowed_packet=16m

In an option file

[mysql]
max_allowed_packet=16m

正确做法

mysql --max_allowed_packet=16m
set global max_allowed_packet=16*1024*1024;

4.2.2.6 Option Defaults, Options Expecting Values, and the = sign

mysql --host=tonfisk --user=jon
select current_user();

mysqld_safe --log-err &

The & character tells the OS to run MySQL in the background.

select user();

4.2.3 Command Options for Connecting to the Server

Command Options for Connection Establishment

Table Connection-Establishment Option Summary

Option Name	Description
–default-auth	Authentication plugin to use
–host	Host on which MySQL server is located
–password	Password to use when connecting to server
–pipe	windows only
–plugin-dir	Directory where plugins are installed
–port	TCP/IP port number for connection
–protocol	Transport protocol to use
–shard-memory-base-name	windows only
–socket	Unix socket file or windows named pipe to use
–user	MySQL user name to use when connecting to server

Command Options for Encrypted Connections

Table Connection-Encryption Option Summary

Option Name	Description
–get-server-public-key	Request RSA public key from server
–server-public-key-path	Path name to file containing RSA public key
–ssl-ca	File that contains list of trusted SSL Certificate Authorities
–ssl-capath	Directory that contains files
–ssl-cert	File that contains X.509 certificate
–ssl-cipher	Permissible ciphers for connection encryption
–ssl-crl	File that contains certificate revocation lists
–ssl-crilpath	Directory
–ssl-fips-mode	Whether to enable FIPS mode on client side
–ssl-key	File that contains X.509 key
–ssl-mode	Desired security state of connection to server
–tls-ciphersuites	Permissible TLSv1.3 ciphersuites for encrypted connections
–tls-version	Permissible TLS protocols for encrypted connections

感觉这个加密有点多, 细节不想看了

Command Options for Connection Compression

Table Connection-Compression Option Summary

Option name	Description
–compression-algorithms	Permitted compression algorithms for connections to server
–zstd-compression-level	Compression level for connections to server that use zstd compression

4.2.4 Connecting to the MySQL Server Using Command Options

mysql
mysql --host=localhost --user=myname --password=password mydb
mysql -h localhost -u myname -ppassword mydb
mysql --host=localhost --user=myname --password mydb
mysql -h localhost -u myname -p mydb
mysql --host=remote.example.com --port=3306
mysql --port=13306 --host=127.0.01
mysql --port=13306 --protocol=TCP

• in option file.

[client]
host=host_name

• environment variables.

MYSQL_HOST