Sponsored Links:

5 Useful Mysql Command Options-Pager-Prompt-Rehash-Tee-System

And that path you may use to load data:
mysql>source /path/to/tobe_loaded.sql

Original Article can be found here: http://kedar.nitty-witty.com/blog/5-useful-mysql-command-options-pager-prompt-rehash-tee-system/

Setting prompt from my.cnf: In my.cnf you may specify the default prompt string under [mysql] section.

2. \! OR system : Execute system command

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

*Note the escaped slashes(\).

By default this feature is enabled and you can disable it by option –disable-auto-rehash.

mysql> prompt \u@\h [\d]>
PROMPT set to ‘\u@\h [\d]> ‘

This will allow you to scroll query result horizontally using the left-arrow and right-arrow keys.

To stop logging you may use notee command.

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

5. Pager : Uses the specified command for paging query output.

3. \T OR tee : Log commands and outputs to a (log) file

username@hostname [Current-Default-Database]>

For eg:

For Date time related settings:5 Useful Mysql Command Options-Pager-Prompt-Rehash-Tee-System

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.
Enter:
mysql> \#
or
mysql> rehash
This enables database, table, and column name completion.
To complete a name, enter the initial part of name and press Tab. If the name is unambiguous, mysql will complete it for you.

If you have large number of columns and have readability problems you may use:
mysql>pager less -S

1. \# OR rehash: Enable automatic rehashing.

You can also search through the result set with /search-term in result set.
Pager will work only in Linux/Unix systems.

It comes very handy when you’re working simultaneously with multiple MySQL prompts with different databases or servers.

[mysql]
prompt=\\u@\\h [\\d]>

mysql>pager less -S -X

Sponsored Links:

Loading…

For eg. below will fill information_schema if there is no ambiguity in inform* names:
mysql> use inform

I wish MySQL’d have provided short-date format in prompts.

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

Will output every query output to /path/to/file.log

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

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

mysql> system locate tobe_loaded.sql
/path/to/tobe_loaded.sql

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

We do have many other prompt options available.

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

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