The mysql Command-Line Tool

In MySQL Database Architecture we covered the fact that MySQL is a client-server database management system (DBMS). In this chapter we will look at the number of client tools that are provided with MySQL to enable the user to interact with databases via the MySQL database server.

The mysql tool is probably the most useful utility and is the tool that you will likely use the most as you learn and continue to use MySQL. mysql is a command-line client tool that is installed as standard with the MySQL package. From the mysql command-prompt it is possible to issue a wide range of commands to the database server such as creating and deleting databases and tables, searching for data, adding new rows and much more. Throughout this book the capabilities of the mysql tool will be covered in great detail.

Assuming MySQL has been installed (see Installing MySQL on Linux or Installing MySQL on Windows for installation details) the mysql tool may be loaded at the operating system command-prompt as follows:

mysql

The above command will display the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 234343 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If you see an error message, it may be that your database system is configured to require login credentials, or that the server is running on a different system. For example, if your database server requires username and password to gain access the -u and -p command-line options may be used respectively:

mysql -u john -p

The above command will prompt for the password for user john before allowing access to the database management system.

If the mysql client is running a different system to the MySQL server, the -h flag may be used to specify the name of the remote host together with -P to specify the port:

mysql -h myDBServer -p 5678

A list of command-line options can be obtained running mysql --help at the command-prompt.

Once mysql is running commands are entered at the mysql> prompt. Typing help at this prompt will display a list of the commands supported by the tool.

Commands are terminated by a semi-colon (;). If a command is not terminated by a semi-colon, pressing enter simply continues the current command on the following line.

To exit from mysql type quit at the mysql> command-prompt.