MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home MySQL Monitor

MySQL Monitor

In this lesson of the MySQL tutorial, you will learn...
  1. To use MySQL command-line Monitor.
  2. To understand various options.


Using MySQL Monitor (SQL Command Interpreter)

Print PDF
User Rating: / 3
PoorBest 

The command interpreter - mysql allows for interactive execution of SQL commands and for many routine and administrative tasks. At the launch of mysql, numerous options can be specified to manage formatting, outputs and other program controls.

  • The mysql command
  • Authentication parameters for user name and password
  • One can specify a name for an existing database, where the database becomes the default database to run commands on - same as saying USE databasename.

If mysql immediately terminates, possibly with an error message such as Access denied for user sakilaadmin, then either access to MySQL is completely denied to user sakilaadmin, or it is protected by a password. In either case you must invoke mysql with the options -u name and -p:.

Syntax
mysql [options] [databasename] [ < commands.sql]

Some Useful Options

Command Alternate Form Description
-e cmd --execute=cmd Executes the given command(s) in quotes separated by semicolons.
--tee=filename Redirect all input and output to the specified file; only allowed in interactive mode.
--no-tee Do not use logging (default).

Here are a few examples of the commands using options above. These commands are stored in batch scripts and are intended to be executed on the OS command line. One can easily execute *.bat files on Unix/Linux as well:

Code Sample:

mysql -u root -p -e "CREATE DATABASE sakilakubili"

 

Code Explanation

The monitor executes a command to create a database after prompting the user for a password.

Code Sample:

mysql -B -u sakilauser -psakila sakila --tee=" sqllog/logfile1.log" -e "SELECT * FROM category"
Code Explanation

The monitor will not prompt for a password as it is specified on the command line itself. The monitor executes a SELECT command and dumps the session output into the given log file.

Note: The directory to contain the file must already be created with right permissions.

Formatting and Output Options

Command Alternate Form Description
-B --batch Separates columns in tables by tab characters (instead of by spaces and lines); Display only results of queries, no status information.
-E --vertical Show query results with columns vertically, one column on each line.
-N --skip-column-names Leaves off column titles in the output of tables.
-s --silent Displays less status information than in normal mode; does not use costly table formatting.

Here are a few examples of the options above:

Code Sample:

mysql -B -N -u sakilauser -psakila sakila --tee=" sqllog/tabs-notitle.log" 
-e "SELECT * FROM category" perror 2
Code Explanation

The output will now be separated using tabs (-B option) and show no column titles (-N option).

As before, the monitor will not prompt for a password as it is specified on the command line itself. The monitor executes a SELECT command and dumps the session output into the given log file.

Code Sample:

mysql --vertical -u sakilauser -psakila sakila --tee="sqllog/vertical.log"
 -e "SELECT * FROM category"
Code Explanation

The columns will be output one on a line (--vertical option).

Warning: Be careful using this option with very large tables.

As seen above, the --execute or -e is a very useful option with mysql as it can be used to pass SQL statements to the server. The statements must be enclosed by single or double quotation marks. If you wish to use quoted values within a statement, you should use double quotes for the statement, and single quotes for any quoted values within the statement. When this option is used, mysql executes the statements and exits.

Tip: MySQL can be asked to generate XML output and even HTML tables.

For example, you can use the following command to obtain a list of user accounts:

Code Sample:

mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
Code Explanation

Show list of users from mysql database.

Note: The long form (--execute) is followed by an equals sign (=).

No default database is passed as a separate argument, as database is part of the query.

Code Sample:

mysql -u root -p -e "SELECT db FROM mysql.db;SELECT VERSION();SELECT NOW()"
Code Explanation

Show list of databases from mysql database, among other things.

Multiple SQL statements may be passed on the command line, separated by semicolons

Commands for Interactive Mode

Unlike previous options which were used with the mysql at OS level, these commands are executed while using the monitor:

Command Alternate Form Description
\e edit Invoke external editor as set via environment variable EDITOR and there enables a change in the command; this works only under Unix/Linux; after the return to mysql, the command given in the editor is not displayed in mysql, which makes this option somewhat confusing to use.
\g go Executes the command (equivalent to ; and Return).
\h help Displays a helpful list of available commands.
\p print Displays the current command as it is in the buffer.
\c cancel Cancels input of current command and start all over again, especially for multi-line inputs.
\q exit or quit Ends mysql. Same action on Unix/Linux by typing Ctrl+D.
\s status Displays status information about the MySQL server.
\T [fn] tee [filename] Enter tee mode where all input and output is logged into the given file. If file name is not supplied, then the file name used in the previous tee command is used.

Note:if the file already exists, then the input and output are appended to the end of the file.

\t notee Ends tee mode; logging can be resumed at any time with tee or \T.
\u database USE database Makes the given database the default database.

Executing a Script from the Monitor

Use source command at the mysql command prompt to run SQL statements and mysql commands from a text file. The statements can include any statements that can be run inside of a MySQL Monitor. Executes the SQL commands contained in the file; the commands must be separated by semicolons. Create a script called "ClassFiles/MySQLMonitor/Demos/Select-Category.sql" with the following SELECT statement:

Syntax
SELECT * FROM category;

At OS level, change to ClassFiles/MySQLMonitor and execute:

Syntax
root> mysql -uroot -p*** sakila
mysql> source Demos/Select-Category.sql
mysql> exit;

We specify the source command followed the filename (and a file-path-prefix if file is not in the current directory). When you run the source command, the SQL statements and MySQL contained commands in the file are executed like a program. You can also use the following convention to use the source command:

 mysql> \. Demos/Select-Category.sql

The backslash and period (\.) replace the word source.

Execute the following SQL script logging in as sakilaadmin INTO sakila database:

Code Sample:

\T sqllog/categories.log
SELECT * FROM category;
\p
\t
\h
USE mysql;
SHOW TABLES;
\g
\q
Code Explanation

This SQL script will start a log, select output and then logout.

Notice the similarity of name with a previous batch file.

Warning: You will observe several failures, as is typical with quickly written scripts, but most of the errors are safe and self-explanatory. The errors will be discussed in the class.

Using the mysql Command to run a Script

You can also use the mysql command at your operating system's command prompt to execute SQL statements and mysql commands in a text file, without actually launching the utility, . In addition to the regular mysql command, specify :

  • The (<) symbol to input STDIN to mysql command
  • The path and filename of the file that contains the statements. We will reuse our Select- Category.sql from before.

The commands are input from a file with < file. All SQL commands can be used in this file. The commands must be followed by a semicolon. Comments are introduced with the character #.

The following command is run at the operating system's command prompt:

mysql -B -N -u sakilauser -psakila sakila < Select-Category.sql

Note: Use double quotes to enclose the path and filename in if either name contains spaces.

See Database Status

The command STATUS displays of various status data information about the database:

mysql> STATUS;
.... OR ....
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.27, for Win32 (ia32)

Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.27-community-nt
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 5 hours 28 min 52 sec

Threads: 1 Questions: 3 Slow queries: 0 Opens: 12 Flush tables: 1
Open tables: 0 Queries per second avg: 0.000
--------------

MySQL Monitor Conclusion

In this lesson of the MySQL tutorial, you learned about using mysql program - the MySQL client tool, also called the monitor.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools