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