MySQL Cookbook, 2nd Edition

Starting and Stopping mysql

Problem

You want to start and stop the mysql program.

Solution

Invoke mysql from your command prompt to start it, specifying any connection parameters that may be necessary. To leave mysql, use a QUIT statement.

Discussion

To start the mysql program, try just typing its name at your command-line prompt. If mysql starts up correctly, you’ll see a short message, followed by a mysql> prompt that indicates the program is ready to accept statements. To illustrate, here’s what the welcome message looks like (to save space, I won’t show it in any further examples):

%mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18427 to server version: 5.0.27-log

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

mysql>

If you invoke mysql and you get an error message that it cannot be found or is an invalid command, that means your command interpreter doesn’t know where mysql is installed. See What to Do if mysql Cannot Be Found for instructions on setting the PATH environment variable that your command interpreter uses to find commands.

If mysql tries to start but exits immediately with an “access denied” message, you need to specify connection parameters. The most commonly needed parameters are the host to connect to (the host that runs the MySQL server), your MySQL username, and a password. For example:

%mysql -h localhost -p -u cbuser
Enter password: cbpass

If you don’t have a MySQL username and password, you must obtain permission to use the MySQL server, as described earlier in Setting Up a MySQL User Account.

The way you specify connection parameters for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, to generate a dump file named cookbook.sql that contains a backup of the tables in the cookbook database, execute mysqldump like this:

%mysqldump -h localhost -p -u cbuser cookbook > cookbook.sql
Enter password: cbpass

Some operations require an administrative MySQL account. The mysqladmin program can perform operations that are available only to the MySQL root account, so you need to invoke it as follows:

%mysqladmin -p -u root shutdown
Enter password:        ← enter MySQL root account password here

In general, I’ll show commands for MySQL programs in examples with no connection parameter options. I assume that you’ll supply any parameters that you need, either on the command line or in an option file (Specifying Connection Parameters Using Option Files) so that you don’t have to type them each time you invoke mysql, mysqldump, and so forth.

The syntax and default values for the connection parameter options are shown in the following table. These options have both a single-dash short form and a double-dash long form.

Parameter type	Option syntax forms	Default value
Hostname	-h hostname	localhost
	--host = hostname
Username	-u username	Your login name
	--user = username
Password	-p	None
	--password

If the value that you use for an option is the same as its default value, you can omit the option. However, as the table indicates, there is no default password. To supply one, use a -p or --password option, and then enter your password when mysql prompts you for it:

%mysql -p
Enter password:        ← enter your password here

If you like, you can specify the password directly on the command line by using -p password (note that there is no space after the -p) or --password = password. I don’t recommend doing this on a multiple-user machine, because the password may be visible to other users who are running tools, such as ps, that report process information.

To terminate a mysql session, issue a QUIT command:

mysql>QUIT

You can also terminate the session by issuing an EXIT command or (under Unix) by typing Ctrl-D.

The Meaning of localhost in MySQL

One of the parameters you specify when connecting to a MySQL server is the host on which the server is running. Most programs treat the hostname localhost and the IP address 127.0.0.1 as synonyms for “the local host.” Under Unix, MySQL programs behave differently: by convention, they treat the hostname localhost specially and attempt to connect to the local server using a Unix domain socket file. To force a TCP/IP connection to the local server, use the IP address 127.0.0.1 rather than the hostname localhost. Alternatively, specify a --protocol=tcp option to force use of TCP/IP for connecting.

The default port number is 3306 for TCP/IP connections. The pathname for the Unix domain socket varies, although it’s often /tmp/mysql.sock. To specify the socket file pathname explicitly, use a -S file_name or --socket= file_name option.