MySQL CLI and mysqldump reference
Complete reference for MySQL client flags, mysqldump options, and common SQL administration commands.
Complete reference for MySQL command-line client connection flags, mysqldump backup options, and essential SQL administration commands.
MySQL Client Connection Flags
| Flag | MySQL client behavior | Default | Example |
|---|---|---|---|
-u {user} | MySQL connects as the specified username | Current OS user | mysql -u root |
-p | MySQL prompts for the password interactively | No password | mysql -u root -p |
-h {host} | MySQL connects to the specified hostname or IP address | localhost | mysql -h 203.0.113.50 |
-P {port} | MySQL connects to the specified TCP port | 3306 | mysql -P 3307 |
--socket {path} | MySQL connects through the specified Unix socket file | /var/run/mysqld/mysqld.sock | mysql --socket=/tmp/mysql.sock |
-e "{query}" | MySQL executes the specified SQL query and exits without entering interactive mode | Interactive mode | mysql -u root -p -e "SHOW DATABASES;" |
--skip-column-names | MySQL omits column headers from query output, useful for scripting | Headers shown | mysql -u root -p -e "SELECT user FROM mysql.user;" --skip-column-names |
mysqldump Backup Flags
| Flag | mysqldump behavior | Default | Example |
|---|---|---|---|
--all-databases | mysqldump exports every database on the server | Single database | mysqldump --all-databases > all.sql |
--single-transaction | mysqldump uses a consistent snapshot for InnoDB tables without locking | Table locks | mysqldump --single-transaction myapp > backup.sql |
--routines | mysqldump includes stored procedures and functions in the export | Excluded | mysqldump --routines myapp > backup.sql |
--triggers | mysqldump includes triggers in the export | Included | mysqldump --triggers myapp > backup.sql |
--no-data | mysqldump exports only the schema (CREATE TABLE) without row data | Data included | mysqldump --no-data myapp > schema.sql |
--add-drop-table | mysqldump adds DROP TABLE statements before each CREATE TABLE | Included | mysqldump --add-drop-table myapp > backup.sql |
--compress | mysqldump compresses data during transfer between client and server (not the output file) | Uncompressed | mysqldump --compress myapp > backup.sql |