Tips for Using mysql Under Unix/Linux

Scroll Region: Under Unix/Linux, mysql is usually executed in a shell window (see Figure 4-2). Every such program (xterm, console, gnome-terminal, etc.) offers the possibility of setting the number of lines that are kept in temporary storage. If you provide a large enough value, then you can use a scroll bar to examine previously input commands and copy them with the mouse to the clipboard.

Keyboard Shortcuts: Under most operating systems, mysql relies on the readline library. Therefore, the usual suspects in the lineup of Unix/Linux keyboard shortcuts are available for editing input (for example, Ctrl+K for deleting a line from the cursor location to the end, Ctrl+Y for restoring the most recently deleted text). Most of the keyboard shortcuts correspond to those under the editor Emacs.

Furthermore, names of tables and columns can be automatically completed with Tab, after the initial letters have been input.

Private Configuration File: Frequently used settings or options (such as user name and password) can be stored under Unix/Linux in a user-specific configuration file with the name ~/.my.cnf. Options related to all client tools are placed in the group [client], while those relating specifically to mysql go into the group [mysql]. The following lines provide an example.

# Options for all MySQL tools
[client]
user=username
password=xxx
# Options for mysql
[mysql]
database=mydatabase

Since the file contains a password in plain text, it should be protected from prying eyes:

user$ chmod 600 ~/.my.cnf

Problems with Character Sets

With many current Linux distributions, the character set utf8 is the default; otherwise, it is generally latin1 (ISO-8559-1) or latin9 (ISO-8559-15). If communication between mysql and the MySQL server does not take place in a single character set, then international special characters will get screwed up. The problem can be simply solved in one of two ways:

• The simplest approach is to start mysql as usual and then execute the SQL command SET NAMES ‘name’, where name is the character set valid in the console. MySQL supports considerably fewer character sets than Unix/Linux, but in most cases, latin1 or utf8 will work. (MySQL does not know about the character set latin9. Use instead latin1. The only difference between the two character sets is the euro symbol.)
• The other variant consists in starting mysql with the option —default-character-set=name, where instead of name, again the desired character set is given.

Which character set is being used for communication between mysql and the MySQL server can be determined in mysql quite easily with the command status. The relevant lines are Server and Conn. character set.

Posted in: mysql