MENU
Utilities
MySQL ships with a set of command-line programs for running the server, checking and repairing data files, performing backup and recovery, managing security, and routing connections. On Windows, first add the MySQL binary directory to the path (run the command prompt as Administrator):| path=%path%;"c:\progra~1\MySQL\MySQL Server 8.0\bin" |
Adds the MySQL bin directory to the current session's search path on Windows.
Instance & Server
- mysql – starts the command-line client program.
- mysqld – starts the MySQL server service (daemon).
- mysqladmin – a client for administrative operations: checking the server's configuration and current status, creating and dropping databases, and more.
- mysql_tzinfo_to_sql – loads the time zone tables in the mysql database, for systems that have a zoneinfo database (Linux, FreeBSD, Solaris, macOS).
Checking
- my_print_defaults – displays the options present in option groups of option files, indicating what options are used by programs that read the specified groups (e.g. mysqlcheck reads the [mysqlcheck] and [client] groups).
- mysqlbinlog – displays the contents of the server's binary log (written in binary format) in text form; also works on relay log files, which share the same format.
- mysqlcheck – performs table maintenance: checks, repairs, optimizes, or analyzes tables. See also Table Maintenance Commands.
- mysqlshow – a command-line interface to several SHOW statements, for quickly seeing which databases exist, their tables, or a table's columns or indexes.
- mysqlslap – a diagnostic program that emulates client load against a server (as if multiple clients were connecting) and reports timing for each stage.
- perror – displays the error message text for MySQL or operating system error codes.
Backup & Recovery
- mysqldump – performs logical backups, producing SQL statements that reproduce database object definitions and table data; can also output CSV, other delimited text, or XML. See Backup and Recovery.
- mysqlimport – a command-line interface to LOAD DATA; most of its options correspond directly to LOAD DATA clauses.
Security
- mysql_config_editor – stores authentication credentials in an obfuscated login path file, .mylogin.cnf (in %APPDATA%\MySQL on Windows, or the current user's home directory otherwise), which client programs can read later to connect without a password prompt.
- mysql_migrate_keyring – migrates keys from one keyring component to another.
- mysql_secure_installation – improves the security of a MySQL installation: setting a password for root accounts, removing root accounts accessible from outside the local host, removing anonymous-user accounts, and removing the test database and its associated privileges.
InnoDB & MyISAM
- ibd2sdi – extracts serialized dictionary information (SDI) from InnoDB tablespace files; works on file-per-table tablespaces (*.ibd), general tablespaces, system tablespace files (ibdata*), and the data dictionary tablespace (mysql.ibd).
- innochecksum – prints checksums for InnoDB files, calculating a checksum per page, comparing it to the stored checksum, and reporting mismatches that indicate damaged pages.
- myisam_ftdump – displays information about FULLTEXT indexes in MyISAM tables by reading the index file directly, so it must run on the server host.
- myisamchk – gets information about MyISAM tables, or checks, repairs, or optimizes them.
- myisamlog – processes the contents of a MyISAM log file. See also Logging.
- myisampack – compresses MyISAM tables by compressing each column separately, typically packing the data file 40% to 70% smaller.
MySQL Router
mysqlrouter is part of InnoDB Cluster: lightweight middleware providing transparent routing between an application and back-end MySQL servers, used for high availability and scalability by routing traffic to the appropriate back-end server.| mysqlrouter --bootstrap localhost:3310 --directory /opt/myrouter --user snoopy |
Bootstraps a Router instance against a target and writes its configuration to the given directory.
mysqlrouter_keyring is a command-line application for managing MySQL Router key rings; mysqlrouter_plugin_info is a debugging tool that inspects a Router plugin for potential conflicts and general problems.# Add MySQL's bin directory to PATH (Windows, run as Administrator)
path=%path%;"c:\progra~1\MySQL\MySQL Server 8.0\bin"
# Check the server's status and variables
mysqladmin -uroot -p status
mysqladmin -uroot -p variables
# Read a binary log in text form
mysqlbinlog mysql-bin.000001 > mysql-bin.000001.txt
# Check, repair, and optimize every table in a database
mysqlcheck --check --repair --optimize -uroot -p mydb
# Quickly inspect a database's tables/columns without a SQL client
mysqlshow mydb
mysqlshow mydb tbl
# Load test/benchmark load against a server
mysqlslap --auto-generate-sql --concurrency=50 --iterations=10
# Look up the meaning of an error code
perror 1146
# Store credentials so client tools do not need -p on every call
mysql_config_editor set --login-path=local --host=localhost --user=root --password
# Harden a fresh installation
mysql_secure_installation
# Verify InnoDB page checksums
innochecksum /var/lib/mysql/mydb/mytable.ibd
# Bootstrap MySQL Router against a target instance
mysqlrouter --bootstrap localhost:3310 --directory /opt/myrouter --user snoopy