1. Common mysql Command Options
1. --auto-rehash (tab completion for table names and fields)
# mysql -u root --auto-rehash
# vim my.cnf
[mysql]
auto-rehash
2. -B (disable history file, non-interactive mode)
# mysql -uroot -D test -e "show tables;" -B
3. -E (vertical output for query results)
# mysql -uroot -D test -e "show tables;" -E
4. -D (specify the database to use)
# mysql -u root -D test
5. --default-character-set (set default character set)
# mysql -u root -D test --default-character-set=utf8
6. --delimiter (set command terminator)
# mysql -u root -D test --delimiter=\| # default is semicolon, changed to pipe
7. -e (execute SQL without interaction)
# mysql -uroot -D bak_test -e "show tables;"
8. -f (ignore errors and continue)
# mysql -uroot bak_test -e "show databaseds;show tables;" -f
9. -N (do not show column names)
# mysql -uroot bak_test -e "select * from user" -N
10. -p (use password for login)
# mysql -u root -o test -p -S /tmp/mysql.sock
11. -h (specify host IP)
# mysql -u root -h 192.168.1.102
12. -H (generate HTML output)
# mysql -uroot bak_test -e "show tables " -H
13. -X (generate XML output)
# mysql -uroot bak_test -e "show tables " -X
14. --prompt (set prompt string)
# mysql -u root --prompt=\^\_\^
15. -S (specify socket file, used for multiple instances)
# mysql -u root -D test -S /tmp/mysql.sock
16. -v (output executed SQL statements)
# mysql -u root -D test -e "show tables;" -v
17. -P (specify port number)
# mysql -u root -o test -P 13306 -S /tmp/mysql.sock
18. Interactive Mode Commands:
help (\h) get help
connect (\r) reconnect
delimiter (\d) set statement end delimiter
ego (\G) send command to server, display in vertical format
exit (\q) exit client
go (\g) send command to server
quit (\q) exit Mysql
source (\.) execute SQL script
status (\s) get server status
system (\!) run shell commands
tee (\T) output to file
use (\u) select database
charset (\C) switch character set
warnings (\W) display warning messages
nowarning (\w) suppress warning messages
19. Interactive Mode Shortcuts:
Ctrl+w: delete word before cursor
Ctrl+u: delete all before cursor
Ctrl+y: paste deleted content
Ctrl+a: move to start of line
Ctrl+e: move to end of line
2. Common mysqladmin Connection Options
1. Option Parameters
-u, --user=name # specify username
-h, --host=name # specify hostname
-p, --password # specify password
-P, --port # specify port
-c number # repeat count, must be used with -i
-i number # interval between repeats
2. Common Commands
create
drop
debug # enable debug logging
status # output server status
--sleep: # refresh interval
--count: # number of times to display
extended-status # show extended status, equivalent to SHOW GLOBAL STATUS;
flush-hosts # clear host cache (DNS, connection error counts)
flush-logs # flush logs, generate new log files
flush-privileges # reload privileges
flush-status # reset status variables
flush-tables # close open table handles
flush-threads # clear thread cache
kill # kill a thread
password # change user password
ping # check if server is online
processlist # show running threads
reload # same as flush-privileges
refresh # same as flush-logs and flush-hosts
shutdown # shut down MySQL server
start-slave # start slave thread
stop-slave # stop slave thread
variables # output server variables
version # show server version
3. Examples
1) View server status every 2 seconds, 5 times
# mysqladmin -uroot -p -i 2 -c 5 status
2) View server status
# mysqladmin -uroot -p status
3) Change root password:
# mysqladmin -u root -p old_password password 'newpassword'
4) Check if MySQL server is available:
# mysqladmin -uroot -p ping
5) View server version
# mysqladmin -uroot -p version
6) View current status values
# mysqladmin -uroot -p extended-status
7) View server system variables
# mysqladmin -uroot -p variables
8) Show all running processes
# mysqladmin -uroot -p processlist
# mysqladmin -uroot -p -i 1 processlist # refresh every second
9) Create a database
# mysqladmin -uroot -p create new_database
10) Drop a database
# mysqladmin -uroot -p drop new_database
11) Reload privilege information
# mysqladmin -uroot -p reload
12) Flush all table caches and log files
# mysqladmin -uroot -p refresh
13) Shut down safely
# mysqladmin -uroot -p shutdown
14) Execute flush commands
# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
15) Kill a process:
# mysqladmin -uroot -p processlist
# mysqladmin -uroot -p kill 10086
16) Start and stop slave
# mysqladmin -u root -p stop-slave
# mysqladmin -u root -p start-slave
17) Execute multiple commands at once
# mysqladmin -u root -p process status version
3. Common mysqlshow Parameters
1. Display all databases
# mysqlshow -uroot -p
2. Display all tables in a database
# mysqlshow -uroot -p daba-test
3. Count columns in tables
# mysqlshow -uroot -p daba-test -v
4. Count rows and columns in tables
# mysqlshow -uroot -p daba-test -v -v
4. Common mysqlbinlog Parameters
1. -d (only show specific database)
# mysqlbinlog mysql.000007 -d db1
2. -o (skip first N entries)
# mysqlbinlog mysql.000007 -o 3 # skip first three operations
3. -r (output to specified file)
# mysqlbinlog mysql.000007 -o 3 -r /tmp/test.sql
4. -s (show only SQL statements)
# mysqlbinlog mysql.000007 -s
5. --set-charset=char-name (character set)
# mysqlbinlog mysql.000007 --set-charset=utf8
6. --start-datetime=date --stop-datetime=date (specific date range)
# mysqlbinlog mysql.000007 --start-datetime='2015/07/17 11:06:14' --stop-datetime='2015/07/17 11:12:20'
7. --start-position=# --stop-position=# (specific position range)
# mysqlbinlog mysql.000007 --start-position=20 --stop-position=80
8. --server-id=# (specific server_id binlog)
# mysqlbinlog mysql.000007 --server-id= 2
5. Common mysqdlump Parameters
1. -A ,--all-databases (export all databases)
# mysqldump -h127.0.0.1 -uroot -p -A > all_db.sql
2. -Y,--all-tablespaces (export all tablespaces)
# mysqldump -h127.0.0.1 -uroot -p -A -Y > all_db.sql
3. --add-drop-database (add DROP DATABASE before creating)
# mysqldump -h127.0.0.1 -uroot -p -A --add-drop-database > all_db.sql
4. --add-drop-table (add DROP TABLE before creating, default enabled)
# mysqldump -h127.0.0.1 -uroot -p -A (default adds DROP)
# mysqldump -h127.0.0.1 -uroot -p -A –skip-add-drop-table (remove DROP)
5. --add-locks (lock tables before export, unlock after, default enabled)
# mysqldump -h127.0.0.1 -uroot -p -A (default adds LOCK)
# mysqldump -h127.0.0.1 -uroot -p -A –skip-add-locks (remove LOCK)
6. --apply-slave-statements (add STOP SLAVE before CHANGE MASTER, START SLAVE at end)
# mysqldump -h127.0.0.1 -uroot -p -A --apply-slave-statements
7. --character-sets-dir (directory of character sets)
# mysqldump -h127.0.0.1 -uroot -p -A --character-sets-dir=/data/mysql/share/charsets
8. -C,--compress (enable compression between client and server)
# mysqldump -h127.0.0.1 -uroot -p -A --compress
9. -B,--databases (export multiple databases)
# mysqldump -h127.0.0.1 -uroot -p -B test mysql
10. --default-character-set (set default character set, default utf8)
# mysqldump -h127.0.0.1 -uroot -p -A --default-character-set=latin1
11. --dump-slave (used on slave, add binlog position to SQL file)
Set to 1, add CHANGE MASTER command to file
Set to 2, add CHANGE MASTER comment to file
Automatically locks tables, unlocks after, usually used with --single-transaction, default 0
# mysqldump -h127.0.0.1 -uroot -p -A --dump-slave=1
# mysqldump -h127.0.0.1 -uroot -p -A --dump-slave=2
12. -flush-logs (flush logs before export, if exporting multiple databases, flush multiple times, lock tables, flush once)
# mysqldump -h127.0.0.1 -uroot -p -A --flush-logs
13. -flush-privileges (refresh privileges before export, ensure correct recovery)
# mysqldump -h127.0.0.1 -uroot -p -A --flush-privileges
14. --ignore-table (exclude specific table, multiple tables can be repeated)
# mysqldump -h127.0.0.1 -uroot -p -A --ignore-table=mysql.user
15. --include-master-host-port (add MASTER_HOST and MASTER_PORT to CHANGE MASTER)
# mysqldump -h127.0.0.1 -uroot -p -A --include-master-host-port
16. -x,--lock-all-tables (lock all tables, ensure data consistency, global read lock)
# mysqldump -h127.0.0.1 -uroot -p -A -x
17. -l,--lock-tables (lock tables before export, lock each database separately, cannot guarantee logical consistency)
# mysqldump -h127.0.0.1 -uroot -p -A -l
18. --master-data (used on master, add binlog position to SQL file)
Set to 1, add CHANGE MASTER command to file
Set to 2, add CHANGE MASTER comment to file
Automatically locks tables, unlocks after, usually used with --single-transaction, default 0
# mysqldump -h127.0.0.1 -uroot -p -A --master-data=1;
# mysqldump -h127.0.0.1 -uroot -p -A --master-data=2;
19. -n,--no-create-db (export only data, no CREATE DATABASE)
# mysqldump -h127.0.0.1 -uroot -p -A --no-create-db
20. -t,--no-create-info (export only data, no CREATE TABLE)
# mysqldump -h127.0.0.1 -uroot -p -A --no-create-info
21. -d,--no-data (export only structure, no data)
# mysqldump -h127.0.0.1 -uroot -p -A -d
22. -P,--port (specify port number)
# mysqldump -h127.0.0.1 -uroot -p -A -P 3306
23. --protocol (connection protocol, including: tcp, socket, pipe, memory)
# mysqldump -h127.0.0.1 -uroot -p -A --protocol=tcp
24. -r,--result-file (output directly to file, common on Windows, does not support redirection>)
# mysqldump -h127.0.0.1 -uroot -p -A --result-file=/tmp/mysqldump_result_file.txt
25. -R,--routines (export stored procedures and functions)
# mysqldump -h127.0.0.1 -uroot -p -A --routines
26. --single-transaction (hot backup for InnoDB, snapshot backup, no table locking)
# mysqldump -h127.0.0.1 -uroot -p -A --single-transaction
27. --dump-date (export date, default enabled)
# mysqldump -h127.0.0.1 -uroot -p -A
# mysqldump -h127.0.0.1 -uroot -p -A --skip-dump-date # no date
28. -S,--socket (specify socket file)
# mysqldump -h127.0.0.1 -uroot -p -A --socket=/tmp/mysqld.sock
29. --tables (export specific tables)
# mysqldump -h127.0.0.1 -uroot -p -B test --tables test
30. --tz-utc (set time zone to UTC to ensure TIMESTAMP accuracy across time zones)
# mysqldump -h127.0.0.1 -uroot -p -A --tz-utc
31. -v,--verbose (verbose mode)
# mysqldump -h127.0.0.1 -uroot -p -e 'show databases' -v
32. -V,--version (show version)
# mysqldump -V
33. -E,--events (export events)
# mysqldump -h127.0.0.1 -uroot -p -A --events