Details about the connection between MySQL and the client _ MySQL

This article demonstrates how to use the MySQL client program to connect to the database server. Mysql is an interactive program that allows you to connect to a MySQL server, run the query, and view the results. Mysql can be used in the batch mode: you put the query in a file in advance and then tell the mysql execution file content. Both methods of using mysql are involved here.

To view a selection project table provided by mysql, call it using the -- help option:

Shell> mysql -- help

This chapter assumes that mysql has been installed on your machine and has a MySQL server that you can connect. If this is not true, contact your MySQL administrator. (If you are an administrator, you will need to consult other chapters in this manual .)

Establish and stop connections with the server

1. how to use a client to establish a connection

To connect to the server, activate the mysql program from the shell program (that is, from a UNIX prompt, or from the DOS console in Windows. The command is as follows:

Shell> mysql

Another example is to directly connect to a database:

Shell> mysql db_name

"$" Represents the shell prompt in this book. This is one of the standard UNIX prompts; the other is "#". In Windows, the prompt is similar to "c:/> ".

2. the most common options for a client: Host, user, and password

To connect to the server, when you call mysql, you usually need to provide a MySQL User name and probably a password. If the server runs on a machine that you are not logged on to, you also need to specify the host name. Contact your administrator to find out what connection parameters you should use to connect (that is, the host, username and password used ). Once you know the correct parameters, you should be able to connect like this:

Shell> mysql-h host-u user-p
Enter password :********

* ******* Indicates your password. when mysql displays Enter password: prompt, Enter it.

At the beginning of learning MySQL, you may be worried about its security system, because it makes it difficult for you to do what you want. (You must obtain the permission to create and access the database. you must give your own name and password to connect to the database at any time .) However, after you enter and use your own records through the database, your views will change immediately. At this time, you will appreciate that MySQL has prevented others from spying (or worse, damage !) Your information.

The following describes the meaning of the options:

-H host_name (optional format: -- host = host_name)

The server host that you want to connect. This option can be omitted if the server runs on the same machine as mysql.

-U user_name (optional format: -- user = user_name)

Your MySQL User name. If you use UNIX and your MySQL User name is the same as your registration name, you can skip this option. mysql uses your registration name as your MySQL name.

In Windows, the default user name is ODBC. This may not be very useful. You can specify a name on the command line or set a default name in the environment variable by setting the USER variable. Use the following set command to specify a user name of paul:

-P (optional format: -- password)

This option tells mysql to prompt you to enter your MySQL password. Note: You can enter your password on the command line in the form of-pyour_password (optional form: -- password = your_password. However, for security reasons, it is best not to do so. Select-p to tell mysql that you are prompted to enter the password at startup. For example:

Enter password. (The password is not displayed on the screen to avoid being visible to others .) Note that the MySQL password is not necessarily the same as the UNIX or Windows password.

If the-p option is omitted, mysql considers that you do not need a password and do not prompt.

Note: The-h and-u options are related to the words behind them, regardless of whether there is a space between the options and the followed words. -P is not like this. if a password is provided on the command line, no space is required between the-p and the password.

For example, assume that my MySQL User name and password are tom and secret respectively, and you want to connect to the server running on the same machine that I registered. The following mysql command can complete this task:

Shell> mysql-u tom-p

After I type a command, mysql displays "Enter password", prompting you to type a password. Then I typed the password (*** indicates that I typed secret ).

If everything goes well, mysql displays a message string and a "mysql>" prompt, indicating that it is waiting for my release query. The complete startup sequence is as follows:

To connect to a server running on another machine, use-h to specify the host name. If the host is mysql.domain.net, the corresponding command is as follows:

Shell> mysql-h mysql.domain.net-u tom-p

In most examples of mysql command lines described later, we plan to save the-h,-u, and-p options for simplicity. It is assumed that you will provide any required options.

There are many ways to set accounts, so you do not have to type in connection parameters every time you run mysql. This problem has been discussed earlier. you only need to provide parameters in the option File. for details, see 3.2.2. You may want to skip to this section to find ways to connect to the server more easily.

3. end the session

After establishing a connection to the server, you can enter the following command at any time to end the session:

Quit exit

You can also type Control-D to exit, at least on UNIX.

Simplify connection with option files

When activating mysql, you may need to specify connection parameters such as the host name, user name, or password. Running a program requires a lot of input work, which will soon be annoying. You can use option files to store connection parameters to reduce input.

For example, if you use the mysqladmin client to manage databases, you will soon get bored with using such a long command line:

Shell> mysql-u root-p varialbles
Enter password :*********

You may choose to use the global option file to store your parameters:

[Mysqladmin]

# You can also use [client] to store parameters for all clients.

User = root
Password = yourpassword

In this way, mysqladmin variables will not display any storage rejection errors, and you can use the root user identity to maintain the database.

Slow down, you will immediately find that this is a big security vulnerability, because any user who can read the option file can get your password! The solution is to provide only the password option and not the password:

[Mysqladmin]
User = root
Password

In this way, when you execute the command line, you will be prompted for the data password:

Shell> mysql varialbles
Enter password :*********

However, you must provide the password option; otherwise, you must provide the-p option in the command line.

Use mysql input line editor

Mysql has a built-in GNU Readline library that allows you to edit input rows. You can process the currently entered rows, or call up the previously entered rows and execute them again (as is or after further modification ). This is very convenient when you Enter a row and find an error. you can return the row and correct it before pressing Enter. If a wrong query is input, you can call the query and edit it to solve the problem, and then submit it again. (If you type the entire query on a row, this is the easiest way .)

Table 1 lists some useful editing sequences. in addition to this table, there are also many input editing commands. The Internet search engine should be able to find the online version of the Readline manual. This manual is also included in the Readline distribution package, available at the Gnu Web site of http://www.gnu. org.

Table 1 mysql input and edit commands

Key-order column description
Up arrow, Ctrl-p
Down arrow, Ctrl-N
Left arrow, Ctrl-B
Right arrow, Ctrl-F
Escape Ctrl-B
Escape Ctrl-F
Ctrl-
Ctrl-E
Ctrl-D
Delete
Escape D
Escape Backspace
Ctrl-K
Ctrl-_ tune the previous line
Call the next line
Move the cursor left (backward)
Move the cursor right (forward)
Move a word backward
Move one word forward
Move the cursor to the line header
Move the cursor to the end of the row
Delete characters under the cursor
Delete the character on the left of the cursor
Delete words
Delete the word on the left of the cursor
Delete all characters from cursor to end of line
Undo the last modification. you can repeat it.

The following example describes a simple use of input editing. Assume that the following query is entered using mysql:

If you have noticed that "president" is misspelled as "persident" before pressing Enter, you can press the left arrow or Ctrl-B to move the cursor multiple times to the left of "s. Then, press Delete to Delete "er" twice, type "re" to correct the error, and press Enter to publish the query. If you do not notice the wrong spelling, press Enter, and no problem will occur. After an error message is displayed in mysql, press the up arrow or Ctrl-P to bring up the row and edit it.

The input line editing does not work in mysql Windows, but you can get a free cygwin_32 client distribution package from the MySQL Web site. The mysqlc program in the package is the same as that in mysql, but it supports input-line editing commands.

Batch mode connection

In the previous chapter, you interactively use mysql to enter the query and view the results. You can also run mysql in batch mode. To do this, place the command you want to run in a file and tell mysql to read its input from the file:

Shell> mysql <batch-file

If you need to specify the connection parameters on the command line, the command may look like this:

Shell> mysql-h host-u user-p <batch-file
Enter password :********

When you use mysql like this, you are creating a script file and then executing the script.

Why use a script? There are many reasons:

If you run the query repeatedly (for example, every day or every week), make it a script so that you do not retype it every time you execute it.

You can generate a new query from a similar existing query by copying and editing the script file.

When you are developing a query, the batch mode is also very useful, especially for multi-line commands or multi-line statement sequences. If you make a mistake, you don't have to repeat everything. just edit your script to correct the error and tell mysql to execute it again.

If you have a query that generates a lot of output, you can run the output through a page splitter instead of staring at it and turning it to the top of your screen:

$ Mysql <batch-file | more

You can capture the output to a file for further processing:

Shell> mysql <batch-file> mysql. out

You can distribute scripts to other people, so they can also run commands.

In some cases, interactive use is not allowed, for example, when you run a query from a cron task. In this case, you must use the batch mode.

When you run mysql in batch mode, the default output format is different (more concise) than when you use it interactively ). For example, when you run select distinct species FROM pet interactively, the output looks like this:

+ --------- +

| Species |

+ --------- +

| Bird |

| Cat |

| Dog |

| Hamster |

| Snake |

+ --------- +

But when running in batch mode, like this:

Species

Bird

Cat

Dog

Hamster

Snake

If you want to obtain the interactive output format in batch mode, use mysql-t. To echo and output the executed command, use mysql-vvv.

Summary

This chapter lists the connection situations between the client and the server. you must note the following:

1. how to provide parameters and the significance of parameters

2. ask the client to prompt for a password

3. interaction mode and batch processing mode

4. mysql client row editing