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:.
mysql [options] [databasename] [ < commands.sql]
Some Useful Options
|-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:
mysql -u root -p -e "CREATE DATABASE sakilakubili"
The monitor executes a command to create a database after prompting the user for a password.
mysql -B -u sakilauser -psakila sakila --tee=" sqllog/logfile1.log" -e "SELECT * FROM category"
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
|-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:
mysql -B -N -u sakilauser -psakila sakila --tee=" sqllog/tabs-notitle.log"
-e "SELECT * FROM category" perror 2
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.
mysql --vertical -u sakilauser -psakila sakila --tee="sqllog/vertical.log"
-e "SELECT * FROM category"
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:
mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
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.
mysql -u root -p -e "SELECT db FROM mysql.db;SELECT VERSION();SELECT NOW()"
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:
|\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||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:
SELECT * FROM category;
At OS level, change to ClassFiles/MySQLMonitor and execute:
root> mysql -uroot -p*** sakila
mysql> source Demos/Select-Category.sql
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:
SELECT * FROM category;
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:
.... OR ....
mysql Ver 14.12 Distrib 5.0.27, for Win32 (ia32)
Connection id: 1
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.