Recipe 1.3. 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 Section 1.7 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 Section 1.1.

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 (Section 1.4) 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 typeOption syntax formsDefault 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.





MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net