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


    - Issue one off commands

    mysqladmin [options] command [command-arg] [command [command-arg]] ...


    - Checks, repairs, optimizes, or analyzes tables

    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


    - Dump entire tables or databases for backup / regeneration

    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


    - Shortcut to LOAD DATA INFILE (not for importing output from mydump generally, us redirection with mysql client for that)

    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


    - Used to display database, table and column info

    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 “_”


    - Load emulation client

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