The mysql Client

The mysql program is a command-line client used for sending commands to a MySQL server. It can be used to enter SQL commands to query a database or alter table definitions; it also has its own set of commands to control its operation.

The mysql Program

In this lesson and throughout this book, mysqlprinted in lower caserefers specifically to the MySQL command-line client program.

Starting the Command-Line Client

To start the command-line client, simply invoke the mysql program from the command line. If your local MySQL server allows anonymous connections, you can invoke mysql without any additional switches. The result looks similar to the following:

 $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2084 to server version: 4.1.12-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 

The mysql> prompt indicates that you will now be typing commands into the mysql client instead of the system shell.

When MySQL is first installed, anonymous connections are allowed unless you disable them. It is a good idea to remove anonymous user access; this is discussed in Appendix A, "Installing MySQL."

If your server does not allow anonymous connections, you will see an error like the following when you attempt to start the mysql program:

 $ mysql ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: NO) 

If you will be using mysql to connect to a remote database and there is no local MySQL server, or if the local MySQL server is not running, the error will look like this:

 $ mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) 

Connecting to MySQL

Even if your database does allow anonymous connections, you will need to connect using a username and password to do anything useful. To connect to a MySQL database, you must know the connection parameters to use.

Because MySQL can accept connections over a network, you must specify the database server hostname. If you are connecting to a MySQL server running on the local machine, the hostname is localhost. Otherwise, you can use the IP address or hostname of the remote server.

You also need to supply a username and password to authenticate with the database server. As you will learn in Lesson 18, "Managing User Access," the username and password to access a database can be locked down so that they work only when you connect from a specific location.

Finally, you must supply the database name to connect to. Each username may have access to one or more databases on the server; you must authenticate to gain access to your own databases.

The mysql program accepts a number of switches to specify how to connect to a database. Use the - -user switch to supply a username, and use the - -password switch to supply a password, as shown:

 $ mysql --user=yourname --password=yourpass 

Note that using this command as shown means that your password is visible onscreen. To avoid this, use the - -password switch without an argument to be presented with a password entry prompt.

 $ mysql --user=yourname --password Enter password: 

Enter your password when prompted to log on to the MySQL server. Note that your password is not displayed onscreen as you type.

Windows Menu

On Windows systems, there is a menu item in the MySQL program group named MySQL Command Line Client. Selecting this item invokes mysql with a connection to the local server using the username root; you are prompted to enter a password to continue.

If there is a problem authenticating with the MySQL server using either of these methods, you will see an error like this:

 ERROR 1045 (28000): Access denied for user 'yourname'@'localhost' (using password: YES) 

To connect to a database on a remote server, use the - -host switch.

 $ mysql - -host=host.yourdomain.com - -user=yourname - -password 

The value given in - -host can be the IP address of the server or a fully qualified domain name.

Remote Connections

Remember that your firewall must be configured to allow you to access the MySQL port if you need to connect to a remote database.

Each of the connection parameter switches to mysql has a shorter version that you can use, if you prefer. The - -user switch can be replaced by -u, and - -password by - p. When using the shorter switches, note that the equals sign is not neededthe value is given immediately after the switch.

For instance, the following two commands are identical:

 $ mysql --user=yourname - -password $ mysql -uyourname -p 

The - - host switch can be abbreviated to - h in the same way, as shown in the following command:

 $ mysql - hhost.yourdomain.com - uyourname - p 

MySQL Port

The default port for connections to MySQL over a network is 3306. If your server uses a different port number, specify it using - -port= or -P when connecting using mysql.

The - -database switch can be abbreviated to -D. In fact, the - -database or -D switch can be omitted, and you can just specify the database at the end of the mysql command, as shown:

 $ mysql-hhost.yourdomain.com -uyourname -ppassword dbname 

Password Argument

When using the short connection switches, make sure that there is no space between - p and the password. Otherwise, you will be prompted to enter a password, and the password you gave on the command line will be treated as the database name.