IngmarBoddington
2/14/2013 - 12:45 PM

Command line mysql tools

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';"