{"id":638,"date":"2010-02-27T17:34:40","date_gmt":"2010-02-27T17:34:40","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=638"},"modified":"2014-06-24T18:55:51","modified_gmt":"2014-06-24T18:55:51","slug":"5-useful-mysql-command-options-pager-prompt-rehash-tee-system","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/5-useful-mysql-command-options-pager-prompt-rehash-tee-system","title":{"rendered":"5 useful MySQL Command Options-pager-prompt-rehash-tee-system"},"content":{"rendered":"

There are set of commands that MySQL itself interprets. You may use “help” or “\\h” at the mysql> prompt to list them.<\/p>\n

Below are the 5 most useful MySQL Command Options.<\/div>\n

1. \\# OR rehash: Enable automatic rehashing.<\/h2>\n

Do you have long table names, you find it difficult to remember tablenames or you’re just as lazy as I am, rehashing is good for you.
\nEnter:
\nmysql> \\#<\/em>
\nor
\nmysql> rehash<\/em>
\nThis enables database, table, and column name completion.
\nTo complete a name, enter the initial part of name and press Tab. If the name is unambiguous, mysql will complete it for you.<\/p>\n

For eg. below will fill information_schema if there is no ambiguity in inform* names:
\n mysql> use inform<\/em><\/p>\n

By default this feature is enabled and you can disable it by option –disable-auto-rehash.<\/p>\n

2. \\! OR system : Execute system command<\/h2>\n

System or \\! will allow you to execute system commands without exiting from mysql prompt.
\nA very simple example of the use of this command will be of searching a path to sql!!<\/p>\n

mysql> system locate tobe_loaded.sql
\n\/path\/to\/tobe_loaded.sql<\/em><\/p>\n

And that path you may use to load data:
\n mysql>source \/path\/to\/tobe_loaded.sql<\/em><\/p>\n

3. \\T OR tee : Log commands and outputs to a (log) file<\/h2>\n

\\T filename will log (append) your sql commands and it’s output to ‘filename’ file. This command is usefule while debugging through.<\/p>\n

To stop logging you may use notee command.<\/p>\n

[ad#ad-2-300×250]<\/p>\n

4. \\R OR prompt : Set mysql prompt<\/h2>\n

Command prompt [PROMPT-STRING] will set the mysql prompt as per specified string. Using prompt without parameter will set it to default “mysql>”.<\/p>\n

It comes very handy when you’re working simultaneously with multiple MySQL prompts with different databases or servers.<\/p>\n

For eg:<\/p>\n

mysql> prompt \\u@\\h [\\d]>
\nPROMPT set to ‘\\u@\\h [\\d]> <\/em>‘<\/p>\n

username@hostname [Current-Default-Database]><\/p>\n

Setting prompt from my.cnf: In my.cnf you may specify the default prompt string under [mysql] section.<\/p>\n

[mysql]
\nprompt=\\\\u@\\\\h [\\\\d]><\/em><\/p>\n

*Note the escaped slashes(\\).<\/p>\n

We do have many other prompt options available.<\/p>\n

    \n
  • \\S – semicolon<\/li>\n
  • \\’ – single quote<\/li>\n
  • \\” – double quote<\/li>\n
  • \\v – server version<\/li>\n
  • \\p – port<\/li>\n
  • \\\\ – backslash<\/li>\n
  • \\n – newline<\/li>\n
  • \\t – tab<\/li>\n
  • \\ – space (Not a space after \\ )<\/li>\n
  • \\d – default database<\/li>\n
  • \\h – default host<\/li>\n
  • \\_ – space<\/li>\n
  • \\c – a mysql statement counter. keeps increasing as you type commands.<\/li>\n
  • \\u – username<\/li>\n
  • \\U – username@hostname accountname<\/li>\n<\/ul>\n

    For Date time related settings:<\/p>\n

      \n
    • \\D – full current date (as shown in the above example)<\/li>\n
    • \\w – 3 letter day of the week (e.g. Mon)<\/li>\n
    • \\y – the two digit year<\/li>\n
    • \\Y – the four digit year<\/li>\n
    • \\o – month in number<\/li>\n
    • \\O – 3 letter month (e.g. Jan)<\/li>\n
    • \\R – current time in 24 HR format<\/li>\n
    • \\r – current time in 12 hour format<\/li>\n
    • \\m – the minutes<\/li>\n
    • \\s – the seconds<\/li>\n
    • \\P – AM or PM<\/li>\n<\/ul>\n

      I wish MySQL’d have provided short-date format in prompts.<\/p>\n

      5. Pager : Uses the specified command for paging query output.<\/h2>\n

      Pager command will handle the query output paging as per specified command.
      \n mysql>pager cat > \/path\/to\/file.log <\/em><\/p>\n

      Will output every query output to \/path\/to\/file.log<\/p>\n

      If you’re expecting a long query result you may use more \/ less linux command as a pager which will help reading.
      \nmysql>pager less
      \nThis will allow to scroll up \/ down with your MySQL query result just like the less command.<\/p>\n

      If you have large number of columns and have readability problems you may use:
      \n mysql>pager less -S<\/em><\/p>\n

      OR<\/em><\/p>\n

      mysql>pager less -S -X<\/em><\/p>\n

      This will allow you to scroll query result horizontally using the left-arrow and right-arrow keys.<\/p>\n


      \n<\/em><\/p>\n

      You can also search through the result set with \/search-term in result set.
      \nPager will work only in Linux\/Unix systems.<\/p>\n","protected":false},"excerpt":{"rendered":"There are set of commands that MySQL itself interprets. You may use “help” or “\\h” at the mysql> prompt to list them. Below are the 5 most useful MySQL Command…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[26,427,240,239],"class_list":{"0":"post-638","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-command-options","9":"tag-mysql","10":"tag-mysql-command-options","11":"tag-mysql-commandline"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/638","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=638"}],"version-history":[{"count":1,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/638\/revisions"}],"predecessor-version":[{"id":1595,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/638\/revisions\/1595"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=638"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=638"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=638"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}