MySQL learning notes (16): command line tools

Time:2021-2-23

This article is updated on June 14, 2020, using MySQL 5.7 and operating system deepin 15.4.

catalog
  • Myisamchk — MyISAM table maintenance tool
  • Myisampack — MyISAM table compression tool
  • MySQL — client connection tool
  • Mysqladmin — MySQL management tool
  • Mysqlbinlog — log management tool
  • Mysqlcheck — table maintenance tool
  • Mysqldump — data export tool
  • Mysqldumpslow — slow query log summary tool
  • Mysqlhotcopy — MyISAM hot backup tool
  • Mysqlimport — data import tool
  • Mysqlshow — a tool for viewing database objects
  • PERROR — error code checking tool
  • Replace — text replacement tool

There are two ways to specify parameter options:

  • “-” + single abbreviation character + value, the value can be preceded by a blank character (“- P” option is an exception, if it is added, the password will be input later, and the value after the blank is not considered as a password).
  • “–” + full word + “=” + value

All tools will use some common options, which can be viewed in the “MySQL” section.

Myisamchk — MyISAM table maintenance tool

myisamchk [options] tablename

The options are as follows:

  • -r. — repair: repair the table.
  • -o. — safe recover: a more secure repair table.

Myisampack — MyISAM table compression tool

High compression ratio is used to compress the table of MyISAM storage engine, but the compressed table becomes read-only.

myisampack [options] filename

MySQL — client connection tool

mysql [options] [database]

If you do not specify database, you need to useUSE databaseSpecifies the database to operate on.

Finally, input redirection is added< filenameCan execute file content.

The options are as follows:

  • Help options (common to all tools, not repeated)
    • -?, — help: view help.
  • Connection options (common to all tools, not repeated)
    • -u. — user = user: Specifies the user name.
    • -p. — password [= password]: Specifies the password.
    • -h. — host = host: Specifies the server IP or domain name, and the default is localhost.
    • -P. — port = port: Specifies the server port, which is 3306 by default.
    • –Protocol = protocol: Specifies the connection protocol, which can be TCP, socket, pipe and memory. If localhost is used as the host name, socket is used by default.
  • Client character set options (common to all tools, not repeated)
    • –Default character set = charset: sets the client character set. Equivalent to executionSET NAMES charset
  • Execution Options
    • -e. — execute = SQL: execute SQL statements. Multiple SQL statements can be executed continuously, separated by “;”.
  • Formatting options
    • -E. — vertical: arrange the fields of the record vertically, similar to the SQL statement\Gend.
    • -s. — silent: remove the line box, separate the fields with tab, one line for each record.
  • Error handling options
    • -f. — force: when executing SQL in batch, if there is an error, skip and force the subsequent SQL. If this option is not added, it will stop and exit.
    • -v. — verb: show more information.
    • –Show warnings: displays warning messages.
  • Other options
    • –Local infile [= 0|1]: whether to use the client local file import.

Mysqladmin — MySQL management tool

mysqladmin [options] {command [command_options]}[ ...]

The options option is similar to MySQL and will not be repeated.

The command that can be executed is as follows:

  • create databasename
  • debug
  • drop databasename
  • Extended status: view the server status, the same asSHOW STATUS
  • flush-hosts
  • flush-logs
  • flush-status
  • flush-tables
  • flush-threads
  • flush-privileges
  • kill id,id,…
  • password [new-password]
  • ping
  • processlist
  • reload
  • refresh
  • shutdown
  • status
  • start-slave
  • stop-slave
  • variables
  • version

Mysqlbinlog — log management tool

Each log has a location and time. In the following example, the location is 603 and the time is 18:05:15 on March 13, 2018. Because the log format is row and the – V option is added, the readable log content is also displayed.

# at 603
#180313 18:05:15 server id 1  end_log_pos 649 CRC32 0x5bba1b83 	Write_rows: table id 245 flags: STMT_END_F

BINLOG '
W6KnWhMBAAAAMQAAAFsCAAAAAPUAAAAAAAEABHRlc3QAA2VtcAACAw8CZAAAzYe0ig==
W6KnWh4BAAAALgAAAIkCAAAAAPUAAAAAAAEAAgAC//wFAAAABXdvcmxkgxu6Ww==
'/*!*/;
### INSERT INTO `test`.`emp`
### SET
###   @1=5
###   @2='world'
mysqlbinlog [options] log_file[, ...]

The options are as follows:

  • -d. — database = database: lists only the logs of the specified database.
  • -o. — offset = n: ignore the first n commands in the log.
  • -r. — result file = filename: output to the specified file.
  • -s. — short form: display simple format, omit some information.
  • -v: If the log is in row format, add the – V or – VV option to view it. Otherwise, the base64 string is displayed.
  • –Base64 output = value: if it is decode rows and – V is used, the log in row format only displays the corresponding SQL statement, not the base64 string.
  • –Set charset = charset: addSET NAMES charset
  • –Start datetime = datetime: specify the start time (inclusive).
  • –Start position = position: Specifies the start position (inclusive).
  • –Stop datetime = datetime: Specifies the end time (not included).
  • –Stop position = position: Specifies the end position (not included).

Mysqlcheck — table maintenance tool

Mysqlcheck is actually integrated with MySQLANALYZECHECKOPTIMIZEREPAIRFunction.

mysqlcheck [options] dbname [tablename[, ...]]
mysqlcheck [options] --database dbname[, ...]
mysqlcheck [options] --all-database

The options are as follows:

  • -a. — analyze: analysis table.
  • -c. — check: check list, the default option.
  • -o. — optimize: optimize the table.
  • -r. — repair: repair the table.

Mysqldump — data export tool

mysqldump [options] dbname [tablename[ ...]]
mysqldump [options] --databases dbname[ ...]
mysqldump [options] --all-databases

It’s usually added at the end> filenameRedirect the export results to a file.

The options are as follows:

  • Data source options
    • -A. — all databases: export all databases.
    • -B. — databases dbname […]: specify a database, and multiple databases can be specified. Will includeCREATE DATABASE
    • –ignore-table= dbname.tablename : does not export the specified table, can be used more than once.
  • Output content options
    • -d. — no data: does not contain table data.
    • -n. — no create DB: not includedCREATE DATABASE
    • -R. — routes: contains stored procedures and functions.
    • -t. — no create Info: not includedCREATE TABLE
    • –Add drop database: in eachCREATE DATABASEAdd beforeDROP DATABASE. This option is added by default.
    • –Add drop table: in eachCREATE TABLEAdd beforeDROP TABLE. This option is added by default.
    • –Triggers: contains triggers. This option is added by default.
  • Output format options
    • -c. — complete insert: make the outputINSERTStatement contains the field name.
    • -T. — tab = dirname: back up to create table file (. SQL) and data file (. Txt), actuallySELECT INTO OUTFILEThe command line interface of is often used with the following options:
    • –Compact: make the output concise without comments.
    • –Extended insert: uses multiple rows that contain multiple recordsINSERTGrammar.
    • –Fields enclosed by = name: field reference. Use with – T.
    • –Fields escaped by = name: escape character. Use with – T.
    • –Fields optically enclosed by = name: optional references for fields, only for string type fields. Use with – T.
    • –Fields terminated by = name: field separator. Use with – T.
    • –Lines terminated by = name: line terminator. Use with – T.
  • Other options
    • -F. — flush logs: refresh logs before backup. That is, close the old log and generate a new log, so that the recovery can start from the new log directly.
    • -l. — lock tables: add read locks to all tables.
    • –Quick: used to export large tables. Instead of retrieving all the rows in the table one row at a time, it caches them in memory before output.
    • –Single transaction: make the transactional storage engine get a snapshot to ensure the consistency of backup data. Transactional storage engines use this option better than – L.

Mysqldumpslow — slow query log summary tool

mysqldumpslow [options] log_file[, ...]

Mysqlhotcopy — MyISAM hot backup tool

Mysqlhotcopy is a Perl script. You need to install the MySQL database interface package of Perl. It is only suitable for MyISAM storage engine and needs to run in Linux environment.

mysqlhotcopy [options] dbname[, ...] [dirname]

The options are as follows:

  • –Allow old: if there is a backup with the same name (also a directory) in the backup directory, change the old one to “backup name”_ old”。
  • –Addtodest: if there is a backup with the same name (also a directory) in the backup directory, add the file to it.
  • –No indexes: do not back up index files.
  • –Flushlog: refresh the log after the table is locked.

Mysqlimport — data import tool

Mysqlimport is used to importmysqldump -TThe exported data file (. Txt) is actuallyLOAD DATA INFILECommand line interface for.

mysqlimport [options] [--local] dbname txtfile[, ...]

The options are as follows:

  • -50. — local: use the client local file to import.
  • –Fields enclosed by = name: field reference. Use with – T.
  • –Fields escaped by = name: escape character. Use with – T.
  • –Fields optically enclosed by = name: optional references for fields, only for string type fields. Use with – T.
  • –Fields terminated by = name: field separator. Use with – T.
  • –Lines terminated by = name: line terminator. Use with – T.
  • –Ignore lines = n: ignore the first n lines.

The data is imported into the table specified by the txtfile file name.

Mysqlshow — a tool for viewing database objects

mysqlshow [options] [dbname [tablename [colname]]]

The options are as follows:

If no options are added, all databases will be displayed.

  • –Count: displays the statistics of the database or table. You can not specify a database, specify a database, or specify a table.
  • -k. — keys: displays the table structure and index information of the specified table, andSHOW FULL COLUMNSAndSHOW INDEXThe results are consistent.
  • -i. — status: displays the status information of the table, andSHOW TABLE STATUSThe results are consistent.

PERROR — error code checking tool

perror [options] errorcode[, ...]

Errorcode is the error code, after “error” in the error message.

Replace — text replacement tool

replace {from to}[...] -- file[ ...]
replace {from to}[...] < fromfile [> tofile]

“-” is used to indicate the end of string pair and the beginning of file name. After replacement, the original file will be covered.

Using redirection does not override the original file.