Command line mysql tools
Note that all of these clients take standard mysql client options, e.g. host / password etc
mysqladminm
- Issue one off commands
- http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html
mysqladmin [options] command [command-arg] [command [command-arg]] ...
mysqlcheck
- Checks, repairs, optimizes, or analyzes tables
- http://dev.mysql.com/doc/refman/5.6/en/mysqlcheck.html
mysqlcheck [options] db_name [tbl_name ...]
- Check database / tables, whole database dumped if no table names
mysqlcheck [options] --databases db_name ...
- Check all tables in databases in list
mysqlcheck [options] --all-databases
- Check all tables in all databases
- Use -a, -r, -o options to analyze, repair and optimise respectively
- Use --auto-repair to check and repair any tables with errors
- Use -v for verbose output
mysqldump
- Dump entire tables or databases for backup / regeneration
- http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
mysqldump [options] db_name [tbl_name ...]
- Select database / tables, whole database dumped if no table names
mysqldump [options] --databases db_name [...]
- Dump databases in list
mysqldump [options] --all-databases
- Dump all databases
mysqldump -t -h<host> -u<user> -p <datatbase> <table> --single-transaction --extended-insert=FALSE --where="<column>=<value>"
- Dump from table with clause
- INFORMATION_SCHEMA only included in dump for versions 5.1.38 when stated explicitly
- general_log or slow_query_log ignored pre 5.1.38, structure only post 5.1.38
mysqldumpslow <filename>
- Summarises information contained in a slow queries log file
mysqlimport
- Shortcut to LOAD DATA INFILE (not for importing output from mydump generally, us redirection with mysql client for that)
- http://dev.mysql.com/doc/refman/5.6/en/mysqlimport.html
mysqlimport [options] db_name textfile1 [textfile2 ...]
- Import to db_name from text files
- For ease of use, textfile name should match table in which data is being imported
mysqlshow
- Used to display database, table and column info
- http://dev.mysql.com/doc/refman/5.6/en/mysqlshow.html
mysqlshow [options] [db_name [tbl_name [col_name]]]
- Without database, table or column -> databases
- Without table or column -> tables in database
- Without column -> columns in table in database
- Can use SQL wildcards “*”, “?”, “%”, or “_”
mysqlslap
- Load emulation client
- http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html
mysqlslap [options]
- Use v option to stack detail (-vvv for most information)
- --concurrency=<value> option specifies number of concurrent clients to emulate
- --iterations=<value> option specifies number of passes
- --number-of-queries=<value> option specifies product of number of clients and queries each
- Can specify custom query
e.g. --create-schema=world --query="SELECT Name, District FROM City WHERE City = 'ING';"