Please style sheet are not equal in internet explorer browser Firefox, Chrome, Safari, Apple and Opera browser please visit this website.

Thank for Visit My Site and Please sent me Shayari, Status and Quotes post request.

MySQL - Security, Access System, Data Directory, General MySQL Administration

It is a fact of life, or at least of human social organization, that not all information is intended to be made available to all individuals. Thus with MySQL, a database is generally set up in such a way that not everyone can see all of the data (let alone change or delete it). In order to protect data from prying eyes (or unauthorized tampering), MySQL provides a dual access system.
The first level determines whether the user has the right to communicate with MySQL at all. The second level determines what actions (such as SELECT, INSERT, DROP) are permitted for which databases, tables, or columns.
The MySQL administrator is responsible for keeping the contents of databases secure so that only those who have the proper authorization can access records. This involves both internal and external security.
Internal security concerns the issues that arise in relation to other users who have direct access to the MySQL server host-that is, other users who have login accounts on that host. Generally, internal security exploits involve file system access, so you'll want to protect the contents of your MySQL installation from being attacked by people who have accounts on the machine on which the server runs.
In particular, the data directory should be owned and controlled by the administrative MySQL login account used for running the server. If you don't do this, your other security-related efforts may be compromised. For example, you'll want to make sure you've properly set up the accounts listed in the grant tables that control client connections over the network.
But if the access mode for the data directory contents is too permissive, someone might be able to put in place an entirely different client access policy by replacing the files that correspond to the grant tables.
External security concerns the issues involved with clients connecting from outside. It's necessary to protect the MySQL server from being attacked through connections coming in over the network asking for access to database contents.
You should set up the MySQL grant tables so they don't allow access to the databases managed by the server unless a valid name and password are supplied. Another danger is that it may be possible for a third party to monitor the network and capture traffic between the server and a client.
If this is a concern, you may want to configure your MySQL installation to support connections that use the Secure Sockets Layer (SSL) protocol.
Securing MySQL Under Linux/Unix
If you have newly installed MySQL on a test system, then you are starting out with an immensely insecure default setting, one that is not password-protected. It is interesting to note that this default setting depends on the operating system.
Under Windows, the setting is even more insecure than under Unix and Linux. Please note that the default privileges have changed repeatedly over time.
The user root on the local system (host name localhost or computername) has unlimited rights. Furthermore, all users on the local system are permitted to access MySQL without a password; in contrast to root, these users have no rights whatsoever (they can't even execute a SELECT command). Access from an external computer is by default not allowed.
root Password for Local Access
With the following two commands you can secure root access with a password xxx. (Instead of xxx,you should, of course, provide the password of your choice.)
root# mysqladmin -u root -h computername password xxx
root# mysqladmin -u root -h localhost password xxx
Instead of computername you should give the name of the local computer (which you can determine with the command hostname). As we have mentioned, the name localhost is valid when the local connection is effected over a socket file (which is the usual state of affairs). On the other hand, computername holds when the local connection is via TCP/IP (in particular, with Java programs).
No Local Access Without Password: Please note that in accordance with the MySQL default settings, a MySQL connection can be made from the local computer with any user name other than root.
In contrast to the root connection, such connections are, to be sure, provided with no privileges, but nonetheless they represent a security risk. (For example, such users have unrestricted access to test databases.)
Protecting MySQL Under Windows
The way of proceeding under Windows is very similar to that under Unix/Linux. However, the default configuration under Windows is even more insecure, for which reason a bit more work is necessary.
Furthermore, using the commands mysql and mysqladmin is more complex (in particular, when you wish to pass parameters). The simplest way of proceeding is first to open a command window (START |PROGRAMS |ACCESSORIES |COMMAND PROMPT) and there use cd to make the bin\ directory of the MySQL installation the current directory. (Hint: You will save some typing if you move the directory via Drag&Drop from Explorer into the command window.)
root Password for Local Access: With the following command you can secure root for the local computer with a password (where instead of xxx you provide the password of your choice). This example assumes that MySQL was installed in the directory Q:\Programs\mysql.
C:\ [mysql_dir]... \bin> mysql -u root
Welcome to the MySQL monitor.
mysql> SET PASSWORD FOR root@localhost = PASSWORD('xxx');
Query OK, 0 row affected (0.00 sec)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
Instead of executing SET PASSWORD in mysql, it must also be possible simply to execute the command mysqladmin -u root -h localhost password xxx in order to change the password for root. However, it appears that this command does not always function reliably under Windows, and often, instead of changing the root password for localhost, changes the root password for access by any number of other computers.
No root Access from External Computers: The following commands prevent root access from external computers.
Q:\ [mysql dir]... \bin>
mysql -u root -p
Enter password: xxx
Welcome to the MySQL monitor.
mysql> USE mysql;
mysql> DELETE FROM user WHERE user='root' AND host='%';
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Fewer Privileges for Local Users: In the default setting, local users have almost unrestricted rights, even if they do not register as root. The following command deletes all privileges for nonroot access.
mysql> REVOKE ALL ON *.* FROM ''@localhost;
Query OK, 1 row affected (0.00 sec)
mysql> REVOKE GRANT OPTION ON *.* FROM ''@localhost;
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
No Local Access Without a Password:
Local users can still access universally visible databases (for example, those whose name begins with test_).
If it is not your wish that anyone with access to the local computer be able to register without a password, you can do without the above command and instead simply forbid local access without a password.
. There is another reason to recommend this course of action: The entry User = '' and Host = 'localhost' is often unsuspectingly given preference to other entries in the user table (e.g., those with User = 'aname' and User = 'a name' and Host = '%').
By deleting the entry, you avoid possible confusion:
mysql> DELETE FROM user WHERE user='' AND host='localhost';
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
From now on, administrative tasks are possible only as root under localhost. Therefore, you must use the commands mysql and mysqladmin with the options -u root and -p.
No Access from External Computers Without a Password: In the default setting, anyone can register with MySQL from an external computer. This access comes with no privileges, but nonetheless it should be prevented.
Here are the necessary commands:
mysql> DELETE FROM user WHERE host='%' AND user='';
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
WinMySQLadmin Users: If you specified a user name and password at the initial launch of WinMySQLadmin, then under some conditions, the program has an additional user with unrestricted privileges (like those of root). This is particularly the case for older versions of WinMySQLadmin. In the following it is assumed that the user name is namexy.
User namexy is secured by a password, but this password is stored in plain text in the file Windows\my.ini. Anyone who is permitted to work at the computer and knows a bit about MySQL can easily obtain unrestricted access to MySQL.
You have three options for closing this security loophole:
. Delete the user namexyz.
. Delete the password line in Windows\my.ini.
. Restrict the privileges of this user.
The first two of these options are not ideal, because the program WinMySQLadmin can then be used only with severe restrictions. A workable compromise consists in setting the privileges of namexy in such a way that the most important administrative tasks are possible, but alteration of data is forbidden.
> ON *.* FROM namexyz@localhost;
Query OK, 1 row affected (0.00 sec)
mysql> REVOKE GRANT OPTION ON *.* FROM namexyz@localhost;
Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES;
Query OK, 0 row s affected (0.01 sec)
Creating New DataBase User & Granting Permission

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
object_type =
with_option =

If you wish to change the access privileges for all the tables of a database, the correct form to use is ON database.*. If you wish to alter global privileges, then specify ON *.*.Itis not allowed to use wild cards in database names.
Grant ALL on *.* to ravish identified by 'mkdtutorials';
Registering New Users
All users with the computer name * are permitted to link to MySQL if they know the password xxx. The privilege USAGE means that all global privileges have been set to N. The users thereby at first have no privileges whatsoever (to the extent that so far no individual databases, tables, or columns have been made accessible to all users who can log into MySQL):
The following command gives the user admin on the local computer unrestricted privileges. All privileges (including Grant)are set:
GRANT ALL ON *.* TO admin@localhost IDENTIFIED BY 'xxx'
Enabling Access to a Database
The following command gives the user peter on the local computer the right to read and alter data in all tables of the database mylibrary. If peter@localhost is unknown to the user table of the mysql database, then this name is added without a password. (If there is already a peter@localhost, then the password is not changed.)
ON library.* TO peter@localhost
If you wish to add to peter's privileges the right to lock tables and create temporary tables (which is useful in many applications), the command looks like this:
Prohibiting Changes in a Database
The next command takes away from peter the right to make changes to mylibrary, but peter retains the right to read the database using SELECT (assuming that the command of the previous example was just executed).
ON mylibrary.* FROM peter@localhost
Enabling Access to Tables
With the following command the user kahlila on the local computer is given the right to read data from the table authors in the database mylibrary (but not to alter it):
GRANT SELECT ON mylibrary.authors TO kahlila@localhost
Enabling Access to Individual Columns
The access privileges for katherine are more restrictive than those for kahlila: She is permitted only to read the columns title and subtitle of the table books in the database mylibrary.
GRANT SELECT(title, subtitle) ON mylibrary.books TO katherine@localhost
Granting Database Access to All Local Users
All users on the local computer can read and edit data in the mp3 database:
Viewing Access Privileges with SHOW GRANTS
If you have lost track of which privileges a particular user has, the command SHOW GRANTS is just what you need:
SHOW GRANTS FOR peter@localhost;
Grants for peter@localhost:
GRANT SELECT ON mylibrary.* TO 'peter'@'localhost'
1 row in set (0.00 sec)
SHOW GRANTS FOR testuser@localhost
GRANT USAGE ON *.* TO 'testuser'@'localhost'
IDENTIFIED BY PASSWORD '663c5dd53dae4ed0'
ON 'myforum'.* TO 'ptestuser'@'localhost'
Changing a Password with mysqladmin
The program mysqladmin carries out various administrative tasks. Although this program does not offer any immediate assistance in managing access privileges, it does offer two applications that seem to fit into this chapter.
Changing a Password
You can use GRANT to change the password of a previously registered user. However, GRANT can be used only when at the same time access privileges are to be changed. If all you want to do is to change a password, then mysqladmin is a simpler alternative:
> mysqladmin -u peter -p password newPW
Enter password: oldPW
The above command changes the password for the user peter on the computer localhost. Please note that the new password is passed as a parameter, while the old password is entered on request. (This order, first the new and then the old, is rather unusual.)
What If You Forgot Root Password ?
What do you do if you have forgotten the root password for MySQL (and there is no other MySQL user with sufficient administrative privileges and a known password to restore the forgotten password)?
Fear not, for I bring you glad tidings. MySQL has thought about this possibility. The way to proceed is this: Terminate MySQL (that is, the MySQL server mysqld) and then restart it with the option --skip-grant-tables. The result is that the table with access privileges is not loaded. You can now delete the encrypted password for root, terminate MySQL, and then restart without the given option. Now you can give the root user a new password.
The following example is based on MySQL under Linux, although the same procedure is possible under other configurations (though with slight variations). In each case, we assume that you have system administrator privileges on the operating system under which MySQL is running.
The first step is to terminate MySQL:
root# /etc/rc.d/mysql stop
Under Windows you end MySQL in the Service Manager (CONTROL PANEL | ADMINISTRATIVE TOOLS |SERVICES).
In the second step you relaunch mysqld via safe_mysqld (a launch script for mysqld) with the option --skip-grant-tables. The option --user specifies the account under which mysqld should be executed.
The option --datadir tells where the MySQL databases can be found. Here the same setting as with a normal MySQL launch should be used. (The precise instruction depends on the system configuration.)
root# startproc /usr/bin/_mysqld_safe --user=mysql \
--datadir=/var/lib/mysql --skip-grant-tables
Under Windows, execute the following commands for a manual start:
C:\> cd [mysql_dir]\bin
C:\[mysql_dir]\bin> mysqld --skip-grant-tables
Now you can use mysql to reset the root password both for the host name localhost and the actual computer name. (Under Windows you must execute mysql in a second command window, since the first window is blocked by mysqld.)
root# mysql -u root
Welcome to MySQL monitor.
mysql> USE mysql;
Database changed.
mysql> UPDATE user SET password=PASSWORD('new password')
> WHERE user='root' AND host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE user SET password=PASSWORD('new password')
> WHERE user='root' AND host='computername';
Query OK, 1 row affected (0.00 sec)
Then, you must restart MySQL so that the access database mysql can again be used. (As long as MySQL is running with --skip-grant-tables, anyone can establish a connection to the server with unlimited privileges!) Under Linux you execute the following commands:
root# /etc/init.d/mysql[d] start
root# /etc/init.d/mysql[d] start
Access System
Internal Working of Access System
Two-Tiered Access Control

Access control for MySQL databases is managed in two tiers: In the first tier it is merely checked whether the user has the right to establish a connection to MySQL.

This is accomplished by the evaluation of three pieces of information:

user name, host name, and password.


Only if a connection can be established does the second level of access control come into play, which involves every single database command. For example, if a SELECT is executed, MySQL checks whether the user has access rights to the database, the table, and the column. If an INSERT is executed, then MySQL tests whether the user is permitted to alter the database, the table, and finally the column.




How, then, does MySQL manage the information as to which commands can be executed? MySQL uses tables in which are stored privileges. If a user, let us call her athena, has a SELECT privilege for the database owls, then she is permitted to read all the data in owls (but not to change it). If athena has a global SELECT privilege, then it holds for all databases saved under MySQL.


The privileges recognized by MySQL are displayed below in table. Note that the names in the corresponding columns of mysql tables always end in _priv. The Select privilege is thus stored in the column Select_priv. In part, the column names are abbreviated (e.g., Create_tmp_table for the Create-Temporary-Table privilege).


MySQL privileges

 For access to tables


may read data (SELECT command)


may insert new records (INSERT)


may change existing records (UPDATE)


may delete existing records (DELETE)

Lock Tables

may block tables (LOCK )


may execute stored procedures (since MySQL 5.0)


For databases, tables, and indexes


may create new databases and tables

Create Temporary Table

may create temporary tables


may rename and change the structure of tables (ALTER)


may create and delete indexes for tables


undocumented; perhaps in the future will allow one to create relations between tables


may delete existing tables and databases


For file access


may read and change files in the local file system


For MySQL administration

Grant Option

may give other users one's own privileges

Show Databases

may obtain a list of all databases (SHOW DATABASES)


may list MySQL processes of other users (SHOW PROCESSLIST)


may terminate processes of other users (KILL) and execute certain other administrative commands (CHANGE/PURGE MASTER, SET GLOBAL)


may execute various commands (reload, refresh, flush-xxx)

Replication Client

may obtain information about the participants in a replication system

Replication Slave

may read MySQL server data via replication


may terminate MySQL


Note also that there are quite a few new privileges in MySQL 4.0 that were not present in version 3.23 (e.g., Create Temporary Table, Execute, Lock Tables, Show Databases, Replication Client, Replication Slave, Super), and that many old favorites have been changed (Process).


In the MySQL documentation, you will encounter (for example, in the description of the GRANT command) the privileges All and Usage. All means that all privileges should be granted with the exception of Grant. Usage means that all privileges should be denied.


All and Usage are thus themselves not independent privileges, but an aid in avoiding a listing of privileges in executing the GRANT command.


The meaning of most of the privileges listed in should be clear without further explanation. For the not-so-clear privileges there will be some explanation in the coming paragraphs.


Privileges are given in this book with an intial capital letter followed by lowercase letters in order to distinguish them from their like-named SQL commands (Select privilege and SELECT command). MySQL couldn't care less how you distribute uppercase and lowercase letters.


The Grant Privilege


The Grant privilege indicates that a MySQL user can dispense access privileges. (This is most easily accomplished with the SQL command GRANT, whence the name of the privilege.) However, the ability to dispense privileges is limited to the privileges possessed by the grantor. That is, no user can give privileges to another that he or she does not already possess.


The Grant Option privilege is an often overlooked security risk. For example, a test database is created to which all members of a team have unrestricted access. To this end, all the privileges in the relevant entry in the db table are set to Y (the db table will be described a bit later).


A perhaps unforeseen consequence is that everyone who has unrestricted access to this test table can give unrestricted access privileges (either to him- or herself or to other MySQL users) for other databases as well!


The File Privilege


MySQL users with the File privilege may use SQL commands for direct access to the file system of the computer on which the MySQL server is running, for example, with the command SELECT ... INTO OUTFILE name or the command LOAD DATA or with the function LOAD_FILE.


In the case of file access, it is necessary, of course, to pay heed to the access privileges of the file system. (Under Unix/Linux the MySQL server normally runs under the mysql account. Therefore, only those files that are readable by the Unix/Linux user mysql can be read.) Nevertheless, the File privilege is often a considerable security risk.


The Privileges PROCESS and SUPER


The Process privilege gives the user the right to determine, using the command SHOW PROCESSLIST, a list of all processes (connections), including those of other users. (One may obtain a list of one's own processes without this privilege.)


The privilege Super permits the user to end both his own and others' processes with KILL. (If the Super privilege has not been granted, then only the current process can be ended.)


The Super privilege also permits the execution of some administrative commands: CHANGE MASTER for executing the client configuration of a replication system, PURGE MASTER to delete binary logging files, and SET GLOBAL for changing global MySQL variables.


Global Privileges Versus Object Privileges


In MySQL privileges can be chosen to be either global or related to a particular object. Global indicates that the privilege is valid for all MySQL objects (that is, for all databases, tables, and columns of a table).


The management of object-related privileges is somewhat more difficult, but it is also more secure. Only thus can you achieve, for example, that a particular MySQL user can alter a particular table, and not all tables managed under MySQL. An assumption in the use of object-related privileges is that the corresponding global privileges are not set. (What is globally allowed cannot be withheld at the object level.)


This hierarchical idea is maintained within the object privileges as well. First it is checked whether access to an entire database is allowed. Only if that is not allowed is it then checked whether access to the entire table named in the SQL command is allowed. Only if that is forbidden is it then checked whether perhaps access to individual columns of the table is allowed.


The mysql Database


It is not surprising that the management by MySQL of access privileges is carried out by means of a database. This database has the name mysql, and it consists of several tables, responsible for various aspects of access privileges.


The Tables of the Database mysql


The database mysql contains six tables (five in earlier versions) of which five are for managing access privileges. These five tables are often referred to as grant tables. The following list provides an overview of the tasks of these six tables:

. user controls who (user name) can access MySQL from which computer (host name). This table also contains global privileges.
. db specifies which user can access which databases.
. host extends the db table with information on the permissible host names (those that are not present in db).
. tables_priv specifies who can access which tables of a database.
. columns_priv specifies who can access which columns of a table.
. func enables the management of UDFs (user-defined functions); this is still undocumented.
Tools for Setting Access Privileges

One can edit the tables of a database (assuming, of course, that you have the appropriate access privileges) with the usual SQL commands INSERT, UPDATE, and DELETE. However, that is a tiring and error-prone occupation. It is much more convenient to use the commands GRANT and REVOKE, which are the centerpiece of this section. Further alternatives for particular tasks are the MySQL tool mysqladmin and the Perl script mysql_setpermission.




MySQL maintains, for reasons of speed optimization, copies of the mysql tables in RAM. Direct changes to the tables are effective only if they are explicitly reread by MySQL via the SQL command FLUSH PRIVILEGES or the external program mysqladmin reload. (With GRANT and REVOKE this rereading takes place automatically.)


Changing Access Privileges with GRANT and REVOKE

The syntax of the GRANT and REVOKE commands, in simplified form, is as follows:
GRANT privileges
ON [database.]table
TO user@host [IDENTIFIED BY 'password']
REVOKE privileges
ON [database.]table
FROM user@host

If you wish to change the access privileges for all the tables of a database, the correct form to use is ON database.*. If you wish to alter global privileges, then specify ON *.*.Itis not allowed to use wild cards in database names.


For user you can specify '' to indicate all users on a particular computer (for example, ''@computername). On the other hand, for host you must use '%' (for example, username@'%').


Depending on their function, these commands change the mysql tables user, db, tables_priv, and columns_priv.(The host table remains untouched.)

Connection Problems

The following list gives the typical causes of problems in establishing connections. Note that a particular error message can arise from one of several different causes.

The MySQL server does not run:
If you attempt a connection with mysql, you obtain error 2002 (Can't connect to MySQL server on 'hostname') or error 2003 (Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock)

Under Windows, you can tell whether the server is running by looking in the task manager, while under Unix you use the command ps | grep -i mysql. As result, a list of processes should appear (since the server divides itself into a number of processes for reasons of efficiency). If that does not occur, then the server must be started (under Linux with the command /etc/init.d/mysql[d] start).

The client program does not find the socket file:
  • Under Unix/Linux, communication takes place mostly over a socket file if server and client are running on the same computer. For this to function, both programs must agree on the location of this file. When problems arise, you should ensure that there is an entry socket=filename in the configuration file /etc/my.cnf in the section [client], where filename specifies the actual location of the socket file. Normally, this file has the name /var/lib/mysql/mysql.sock.
    . The network connection between client and server is broken:
    If your program is running on a different computer from that of the MySQL server, execute on the client computer the command ping serverhostname to test whether a connection to the server computer exists. If that is not the case, you must first repair the network configuration.
    . MySQL accepts no connections over the network (over TCP/IP):

    This can be achieved with the option –skip-networking or a corresponding setting in my.cnf. This setting is often chosen to give MySQL maximum security. A database connection is then available only from the local computer and only via a socket file.


    The problem is generally recognizable from error 2003 (Can't connect to MySQL server). A solution is to remove the option my.cnf from the start script.

    . MySQL accepts no connections from your computer:
    This problem generally arises when the MySQL server is running on the computer of an Internet service provider. There the server is generally so configured that only connections from local computers (or a local network) are allowed. For administration you must therefore either create a telnet-/ssh connection or use a program that is executed locally on the server and is served via the internet (e.g., phpMyAdmin).
    . Name resolution of host names does not work correctly:
    In establishing a connection over a network, error 1130 arises (Host n.n.n.n' is not allowed to connect to this MySQL server). The most likely cause of this error is either the incorrect specification of the host name in the mysql.user table or an incorrect name-server configuration. Depending on the network configuration, the host names must be given in the column user.Host with or (more seldom) without domain name.
    A solution is to add domain names to the host names in the mysql.user table (uranus ? uranus.sol) or to remove this (uranus.sol -- > uranus) and try again. (Do not forget FLUSH PRIVILEGES.)

    If that does not work, you can test with the commands hostname, host, and resolveip whether there are problems with name resolution. An emergency solution that almost always works (but is inflexible) is to give the IP number instead of the host name in the user.Host column. There are many other suggestions about dealing with host-name problems earlier in this chapter.

    . User name or password are incorrect:
    Watch out for typos! Note as well that not only user name and password must correspond, but the host name as well. (Thus a connection is usually possible only from certain specific computers.) Also, read the previous point relating to resolution of the host name; perhaps that is where the problem resides.
    Note that the user.Password column does not contain passwords in plain text, but in encrypted form. If you wish to change a password with SQL commands, you must use the function PASSWORD("xxx").
    . An incorrect entry was used in the mysql.user table:
    When user x attempts to register with computer y, the MySQL server compares the entries in the user table in a particular order: First, entries are considered whose Host character string is unique, and only then Host entries with wild cards (% and _). Within these two groups, again unique User strings are preferred to those with wild cards.
    The result of this order of precedence is that user abc on the local computer (localhost) will be unable under certain circumstances to register, although in the user table there is an entry Host='%' / User='abc'. The reason is that in the default setting of access privileges there is also an entry Host='localhost' / User=''. This entry is given precedence to the first one because there the host name is given explicitly.
    To solve the problem, either add a second entry Host='localhost' / User='abc' to the user table or delete the entry Host='localhost' / User=''. I would recommend the second variant, since that entry represents a security risk.
    . No user name was specified:
    If you do not specify a user name in your program for making a connection, then the login name of the account under which the program was launched is given automatically. In programs that are launched interactively, this is your login name.
    With programs that run over a web server (PHP or JSP scripts, Perl CGI files, etc.), the account name of the web server is used. For security reasons the web server usually runs not as root (Linux) or with administrator privileges (Windows), but in a separate account, such as wwwrun or apache. The problem is now that in the mysql access tables, the user wwwrun or apache is unknown. Therefore, access to the database is denied. Therefore, do not forget in script files to specify the user name for the connection to MySQL explicitly.
    . The connection succeeds, but access to the database is impossible: This error occurs immediately if you specify the desired database during the establishment of the connection. However, the error cannot occur until you select the desired database (USE dbname). Error message 1045 is, for example, Access denied for user ... to database ... .
    The most likely cause of the error is that the user in fact does not have access rights to the database. Perhaps in GRANT you have specified only the Usage privilege (which allows a login, but not the actual use of a database). Execute the command GRANT SELECT, INSERT ... ON dbname.* TO name@hostname to allow database access to dbname.
    If the MySQL server is running on the computer of an Internet service provider (ISP),then the server is generally so configured that only local access is possible. In other words, your PHP or Perl scripts run without problems (because they are executed on the same computer), but you cannot access your databases from home, say, with the MySQL Control Center.
    Here the issue is the correct (because secure) setting of the access privileges. You will find scarcely an ISP that allows MySQL connections from an arbitrary computer on the Intenet. You must therefore use programs for administration that run locally on the computer of the ISP (e.g., phpMyAdmin).
    . It is impossible to create a local TCP/IP connection:
    This problem usually occurs under Unix/Linux. A local connection succeeds only if with option -h no computer name or IP number is specified. The most likely cause is a problem with the resolution of the host name. As a rule, you must add the domain name in the column user.Host, and then it works. Another cause can be the local network configuration (file /etc/hosts). We have already given some tips in this chapter especially for Red Hat and SuSE Linux.
    . The local connection fails for Java programs:
    This problem is usually connected with the previous point, since Java programs, in contrast to most other MySQL clients, generally use TCP/IP (and not a socket file).. Another cause of error can be the incorrect installation of Connector/J, but then an error occurs in the attempt to use JDBC (java.lang.ClassNotFoundException: com.mysql.jdbc.Driver).
    . Port 3306 is blocked:
    Between the MySQL server and your program there is a firewall that is blocking port 3306. This problem can occur only when your program and the MySQL server are running on different computers. If you manage the firewall yourself, you must clear port 3306; otherwise, you must ask the administrator to do so.
    . The MySQL server crashes at every attempt at a TCP/IP connection:
    This problem occurred in the past with Linux distributions. Since the server was immediately restarted, the problem was not always correctly identified. (The error message is usually Lost connection to MySQL server during query.)
    A solution to this problem is frequently an update of the glib library or the installation of the MySQL package from (The MySQL packages included in the distributions are not always optimally configured. This has been particularly bad in the case of Red Hat 8.0 and SuSE 8.1. The MySQL documentation therefore recommends using only versions of MySQL compiled by the MySQL team, and after a number of negative experiences, I agree.)
    . An update of the MySQL server causes problems:
    In MySQL 4.0 the security system was greatly expanded. If you are moving your database system from version 3.23.n to 4.n, you must be sure to bring the mysql tables into conformity with the new security system. For this, there is provided the script mysql_fix_privilege_tables. This script creates new columns (vis-à-vis MySQL 3.23) in the mysql tables, but leaves the settings of these columns in the default setting N. You may need to take a close look at the new access privileges and explicitly grant certain individual new privileges, such as Lock, Create_tmp_table for normal users, and Super for administrators.
    The Special Case of Named Pipes
    Named pipes represent a mechanism by which two programs can communicate under Windows NT/2000/XP. Data exchange follows the first-in-first-out (FIFO) principle.
    MySQL supports named pipes only under Windows NT/2000/XP, and not under Windows 9x/ME or under Unix/Linux. Named pipes can be used only when the server is appropriately compiled (mysqld-nt and mysqld-max, but not mysqld-opt) and when the configuration file Windows\my.ini contains the option enable-named-pipe in the group [mysqld]. By default, this is not the case, since in the past, open named pipes to the MySQL server caused problems in shutting down Windows.
    If you wish to establish an explicit named-pipe connection, specify a period (.) as host name or use the option --pipe. Even if you specify no options, a named pipe will be used if the server permits it:
    C:\> mysql -u name -p
    C:\> mysql -h . -u name -p
    C:\> mysql -h localhost --pipe -u name -p
    If you wish to create a connection explicitly over TCP/IP, you must specify the host name:
    C:\> mysql -h computername -u name -p
    C:\> mysql -h localhost -u name -p
    Whether named pipes are permitted can be determined in the program mysql with the command status.
    Further Tips for Error-Checking

    If you believe that your mysql database is properly configured, yet database access fails nonetheless, stop the MySQL server, add temporarily skip-grant-tables to the [mysql] section in or my.ini and restart the server. Now everyone has access to all data. If access now succeeds, you at least know with certainty that the problem resides in the mysql tables. Do not forget to remove skip-grant-tables from the configuration file.

    MySQL manages temporary storage (cache) with a list of most recently used IP addresses and associated host names. This cache makes it possible for the usually time-consuming process of name resolution to proceed efficiently. However, if you change your network configuration without restarting the MySQL server, it can happen that this cache contains incorrect, that is, no longer valid, entries. To run the MySQL server without this cache, add skip-host-cache in the [mysql[ section of my.cnf or my.ini and restart the server.
    Unfortunately, there is no way to make the MySQL server save precise information as to why a login attempt failed. In the error log is mentioned only the IP address of the login attempt, but not the course of name resolution, which entries in the mysql tables were looked at, etc.
    Data Directory
    Location of Data Directory
    A default data directory location is compiled into the server. Under UNIX, typical defaults are /usr/local/mysql/var if you install MySQL from a source distribution, /usr/local/mysql/data if you install from a binary distribution, and /var/lib/mysql if you install from an RPM file. Under Windows, the default data directory is C:\[mysql_install_dir]\data.
    The data directory location can be specified explicitly when you start up the server by using a --datadir=dir_name option. This is useful if you want to place the directory somewhere other than its default location. Another way to specify the location is to list it in an option file that the server reads at startup time. Then you don't need to include it on the command line each time you start the server. Data directory relocation is covered later in the chapter.
    As a MySQL administrator, you should know where your server's data directory is located. If you run multiple servers, you should know where each one's data directory is. But if you don't know the location (perhaps you are taking over for a previous administrator who left poor notes), there are several ways to find out:
    . Ask the server for the location. The server maintains a number of variables pertaining to its operation, and it can report any of their values. The data directory location is indicated by the datadir variable, which you can obtain using a mysqladmin variables commandor a SHOW VARIABLES statement. From the command line, use mysqladmin. On UNIX, the output might look like this:
    .	% mysqladmin variables
    | Variable_name | Value                 |
    | datadir       | /usr/local/mysql/var/ |
    On Windows, the output might look like the following instead:
    C:\> mysqladmin variables
    | Variable_name | Value                 |
    | datadir       | c:\[mysql_install_dir]\data\        |
    From within mysql, check the variable's value like this:
    mysql> SHOW VARIABLES LIKE 'datadir';
    | Variable_name | Value                 |
    | datadir       | /usr/local/mysql/var/ |
    If you have multiple servers running, they will be listening on different TCP/IP port numbers, sockets, or named pipes. You can get data directory information from each of them in turn by supplying appropriate --port or --socket options to connect to the port or socket on which each server is listening. Specifying a host of explicitly tells mysqladmin to connect to the server on the local host using a TCP/IP connection:
    % mysqladmin --host= --port=port_num variables
    Under UNIX, specifying a value of localhost causes a UNIX socket connection to be used. You can also specify a --socket option if necessary to indicate the socket file pathname:
    % mysqladmin --host=localhost --socket=/path/to/socket variables 
    Under Windows NT-based systems, a named pipe connection can be specified by giving '.' as a hostname, perhaps with a --socket option to indicate the pipe name:
    C:\> mysqladmin --host=. --socket=pipe_name variables
    For any platform, to connect via TCP/IP to a remote server running on another host, specify a --host option that indicates the name of the server host:
    % mysqladmin --host=host_name variables
    Specify a --port option as well if you need to connect to a port number other than the default.
    . Under UNIX, use the ps command to see the command line of any currently executing mysqld process or processes. By looking for a --datadir option, you may be able to determine the data directory location. If you have a BSD-style ps, try the following command:
    . % ps axww | grep mysqld
    For a System V-style ps, try this instead:
    % ps -ef | grep mysqld
    The ps command can be especially useful if your system runs multiple servers because you can discover multiple data directory locations at once. The drawbacks are that ps must be run on the server host and that no useful information is produced unless the --datadir option was specified explicitly on the mysqld command line.
    . Look in an option file that the server reads when it starts up. For example, if you look in /etc/my.cnf under UNIX or C:\my.cnf under Windows, you may find a datadir line in the [mysqld] option group:
    . [mysqld]
    . datadir=/path/to/data/directory
    The pathname indicates the location of the data directory.
    . The server's help message includes an indication of the default data directory location that is compiled in. This will tell you the directory that the server actually uses when it runs, if the location is not overridden at startup time. To see this output, issue the following command:
    . % mysqld --help
    . ...
    . datadir /usr/local/mysql/var/
    . ...
    . If you installed MySQL from a source distribution, you can examine its configuration information to determine the data directory location. For example, the location is available in the top-level Makefile. But be careful: The location is the value of the localstatedir variable in the Makefile, not the value of the datadir variable, as you might expect. Also, if the distribution is located on an NFS-mounted file system that is used to build MySQL for several hosts, the configuration information will be accurate only for the host for which the distribution was most recently built. That may not show you the data directory for the server in which you're interested.
    . Failing any of the previous methods, you can use find to search for database files. The following command searches for .frm (description) files:
    . % find / -name "*.frm" -print
    The .frm files store the definitions of the tables managed by the server, so they are part of any MySQL installation. These files normally will be found in directories that all have a common parent directory; that parent should be the data directory.
    In the examples that follow throughout this chapter where I denote the location of the MySQL data directory as DATADIR, you should interpret that as the location of the data directory for the server on your own machine.
    Structure of DD
    The MySQL data directory contains all of the databases and tables managed by the server. In general, these are organized into a tree structure that is implemented in straightforward fashion by taking advantage of the hierarchical structure of the UNIX or Windows file systems:
    . Each database corresponds to a directory under the data directory.
    . Tables within a database correspond to files in the database directory.
    The exception to this hierarchical implementation of databases and tables as directories and files is that the InnoDB table handler stores all InnoDB tables from all databases within a single common tablespace. This tablespace is implemented using one or more large files that are treated as a single unified data structure within which tables and indexes are represented. The InnoDB tablespace files are stored in the data directory by default.
    The data directory also may contain other files:
    . An option file, my.cnf.
    . The server's process ID (PID) file. When it starts up, the server writes its process ID to this file so that other programs can discover the value if they need to send signals to it. (This file is not used on Windows or by the embedded server.)
    . Status and log files that are generated by the server. These files provide important information about the server's operation and are valuable for administrators, especially when something goes wrong and you're trying to determine the cause of the problem. If some particular query kills the server, for example, you may be able to identify the offending query by examining the log files.
    . It's common to store files in the data directory such as the DES key file or the server's SSL certificate and key files.
    How the MySQL Server Provides Access to Data
    When MySQL is used in the usual client/server setup, all databases under the data directory are managed by a single entity-the MySQL server mysqld..
    How the MySQL server controls access to the data directory.
    When the server starts up, it opens any log files that you request it to maintain and then presents a network interface to the data directory by listening for various types of network connections. To access data, client programs establish a connection to the server and then communicate requests as SQL queries to perform the desired operations-for example, creating a table, selecting records, or updating records.
    The server performs each operation and sends back the result to the client. The server is multi-threaded and can service multiple simultaneous client connections. However, because update operations are performed one at a time, the practical effect is to serialize requests so that two clients can never change a given record at exactly the same time.
    If you're running an application that uses the embedded server, a slightly different architecture applies, because there is only one "client"-that is, the application into which the server is linked. In this case, the server listens to an internal communication channel rather than to network interfaces. Nevertheless, it's still the embedded server part of the application that manages access to the data directory, and it's still necessary to coordinate query activity arriving over multiple connections if the application happens to open several connections to the server.
    Under normal conditions, having the server act as the sole arbiter of database access provides assurance against the kinds of corruption that can result from multiple processes accessing the database tables at the same time. Nevertheless, administrators should be aware that there are times when the server does not have exclusive control of the data directory:
    . When you run multiple servers on a single data directory. Normally you run a single server to manage all databases on a host, but it's possible to run multiple servers. If each server manages its own independent data directory, there is no problem of interaction. But it's possible to start multiple servers and point them at the same data directory.
    In general, this is not a good idea. If you try it, you'd better make sure your system provides good file locking or the servers will not cooperate properly. You also risk having your log files become a source of confusion (rather than a source of helpful information) if you have multiple servers writing to them at the same time.
    . When you run table repair utilities. Programs such as myisamchk and isamchk are used for table maintenance, troubleshooting, and repair operations, and they operate directly on the files that correspond to the tables.
    As you might guess, because these utilities can change table contents, allowing them to operate on tables at the same time the server is doing so can cause table damage. The best way to avoid problems of this sort is to bring down the server before running any repair utilities
    How MySQL Represents Databases in the File System
    Each database managed by the MySQL server has its own database directory. This exists as a subdirectory of the data directory, with the same name as the database it represents. For example, a database mydb corresponds to the database directory DATADIR/mydb. This representation allows several database-level statements to be almost trivial in their implementation.
    SHOW DATABASES is essentially nothing more than a list of the names of the directories located within the data directory. Some database systems keep a master table that lists all the databases maintained, but there is no such construct in MySQL. Given the simplicity of the data directory structure, the list of databases is implicit in the contents of the data directory, and such a table would be unnecessary overhead.
    CREATE DATABASE db_name creates an empty directory db_name in the data directory. Under UNIX, the directory is owned by and accessible only to the login account that is used for running the server. This means that the CREATE DATABASE operation is equivalent to executing the following shell commands on the server host while logged in under that account:
    % cd DATADIR
    % mkdir db_name
    % chmod u=rwx,go-rwx db_name
    The minimal approach of representing a new database by an empty directory contrasts with other database systems that create a number of control or system files even for an "empty" database.
    The DROP DATABASE statement is implemented nearly as easily. DROP DATABASE db_name removes the db_name directory in the data directory, along with any table files contained within it. This is almost the same as executing the following commands on UNIX:
    % cd DATADIR
    % rm -rf db_name
    or the following commands on Windows:
    C:\> cd DATADIR
    C:\> del /S db_name
    The differences between a DROP DATABASE statement and the shell commands are as follows:
    . For DROP DATABASE, the server removes only files with extensions known to be used for tables. If you've created other files in the database directory, the server leaves them intact, and the directory itself is not removed. (One implication of this is that the database name continues to be displayed by SHOW DATABASES.)
    . InnoDB table and index contents are maintained in the InnoDB tablespace, not as files in the database directory. If a database contains InnoDB tables, you must use DROP DATABASE so that the InnoDB handler can remove the tables from the tablespace; do not remove the database directory by using an rm or del command.
    How MySQL Represents Tables in the File System
    MySQL supports handlers for several types of database tables: ISAM, MyISAM, MERGE, BDB, InnoDB, and HEAP. Every table in MySQL is represented on disk by at least one file, which is the .frm file that contains a description of the table's structure. For most table types, there are also other files that contain the data rows and index information. These vary according to the table type, as outlined in the following discussion.
    ISAM Tables
    The original table type in MySQL is the ISAM type. MySQL represents each ISAM table by three files in the database directory of the database that contains the table. The files all have a basename that is the same as the table name and an extension that indicates the purpose of the file. For example, a table named mytbl is represented by three files:
    . mytbl.frm is the description file that stores the format (structure) of the table.
    . mytbl.ISD is the ISAM data file that stores the contents of the table's rows.
    . mytbl.ISM contains index information for any indexes the table has.
    MyISAM Tables
    MySQL 3.23 introduced the MyISAM table type as the successor to the ISAM type, which now is considered pretty much obsolete. Like the ISAM handler, the MyISAM handler represents each table by three files, using the extensions .frm, .MYD, and .MYI for the description, data, and index files, respectively.
    MERGE Tables
    A MERGE table is a logical construct. It represents a collection of identically structured MyISAM tables that are treated for query purposes as a single larger table. Within a database directory, a MERGE table is represented by its .frm file and a .MRG file that is nothing more than a list of the names of the table's constituent MyISAM tables, one name per line.
    One implication of this representation is that it's possible to change the definition of a MERGE table by flushing the table cache with FLUSH TABLES and then directly editing the .MRG file to change the list of MyISAM tables named there.
    BDB Tables
    The BDB handler represents each table by two files, the .frm description file and a .db file that contains the table's data and index information.
    InnoDB Tables
    The preceding table types all are represented using files that each are uniquely associated with a single table. InnoDB tables are handled in a somewhat different way. The only file that corresponds directly to a given InnoDB table is the .frm table description file, which is located in the directory for the database to which the table belongs.
    The data and indexes for all InnoDB tables are managed together within a single unified tablespace. Typically, the tablespace itself is represented by one or more large files in the data directory. These components of the tablespace form a logically contiguous storage area equal in size to the sum of the sizes of the individual files.
    HEAP Tables
    HEAP tables are in-memory tables. Because the server stores a HEAP table's data and indexes in memory rather than on disk, the table is not represented in the file system at all, other than by its .frm file.
    How SQL Statements Map onto Table File Operations
    Every table type uses a .frm file to store the table description, so the output from SHOW TABLES db_name is the same as a listing of the basenames of the .frm files in the database directory for db_name. Some database systems maintain a registry that lists all tables contained in a database. MySQL does not because it is unnecessary; the "registry" is implicit in the structure of the data directory.
    To create a table of any of the types supported by MySQL, you issue a CREATE TABLE statement that defines the table's structure. For all table types, the server creates a .frm file that contains the internal encoding of that structure. The server also creates any other files that are associated with tables of the given type.
    For example, it creates .MYD and .MYI data and index files for a MyISAM table or a .db data/index file for a BDB table. For InnoDB tables, the handler initializes data and index information for the table within the InnoDB tablespace. Under UNIX, the ownership and mode of any files created to represent the table are set to allow access only to the account that the server runs as.
    When you issue an ALTER TABLE statement, the server re-encodes the table's .frm file to reflect the structural change indicated by the statement and modifies the contents of the data and index files likewise. This happens for CREATE INDEX and DROP INDEX as well because the server handles them as equivalent ALTER TABLE statements. Altering an InnoDB table causes the handler to modify the table's data and indexes within the InnoDB tablespace.
    DROP TABLE is implemented by removing the files that represent the table. Dropping an InnoDB table also causes any space associated with the table in the InnoDB tablespace to be marked as free.
    For some table types, you can remove a table manually by removing the files in the database directory to which the table corresponds. For example, if mydb is the current database and mytbl is an ISAM, MyISAM, BDB, or MERGE table, DROP TABLE mytbl is roughly equivalent to the following commands on UNIX:
    % cd DATADIR
    % rm -f mydb/mytbl.*
    or to the following commands on Windows:
    C:\> cd DATADIR
    C:\> del mydb\mytbl.*
    For an InnoDB or HEAP table, parts of the table are not represented within the file system in discrete files, so DROP TABLE does not have a file system command equivalent. For example, the .frm file is the only file uniquely associated with an InnoDB table. Removing that file will leave the table data and indexes "stranded" within the InnoDB tablespace.
    Operating System Constraints on Database and Table Naming
    MySQL has general rules for naming databases and tables.
    The rules can be summarized briefly as follows:
    . Names can be constructed from the alphanumeric characters in the current character set, as well as the underscore and dollar characters ('_' and '$').
    . Names can be up to 64 characters long.
    . From MySQL 3.23.6 on, other characters can be used in a name by quoting the name in backticks (for example, `odd@name`). Quoting is often necessary if you use reserved words as column names.
    However, because names of databases and tables correspond to names of directories and files, the operating system on which a server runs may impose additional constraints that stem from file system naming conventions:
    . You are limited in database and table names to the characters that are legal in filenames. This is true for every table type, because every type is represented in the file system by at least a .frm file. For example, '$' is allowed in a name by MySQL's rules, but if your operating system doesn't allow it in filenames, you can't use it in directory or table names either.
    In practice, this is not a concern for either UNIX or Windows. The greatest difficulty you might have is referring to names directly from the shell when performing database administration. For example, the '$' dollar sign character is special to UNIX shells. If you give a database a name such as $mydb that includes that character, any reference to the name from the command line may be interpreted by the shell as a variable reference:
    . % ls $mydb
    . mydb: Undefined variable.
    If this happens, you must escape the '$' character or use quoting to suppress its special meaning:
    % ls \$mydb
    % ls '$mydb'
    If you use quotes, use single quotes. Double quotes do not suppress variable interpretation.
    . A database or table name cannot include the pathname separator character, even if quoted. For example, on UNIX and Windows, pathname components are separated by '/' and '\', respectively, and neither character can be used. The reason that both are disallowed regardless of platform is to make it easier to move databases and tables from one platform to another. (For example, if you were allowed to use '/' in the name of a table on Windows, you could not move the table to UNIX.)
    . Although MySQL allows database and table names to be up to 64 characters long, the length of names is also bound by the length allowed by your operating system. Normally this is not a problem, although under UNIX, some older System V-ish systems may enforce a 14-character limit. In that case, the effective limit on database names is 14 characters. The limit for table names is 10 characters because names of files representing tables end with a period and an extension of up to three characters.
    . Case sensitivity of the underlying file system affects how you name and refer to databases and tables. If the file system is case sensitive (as is typical for UNIX), the two names mytbl and MYTBL refer to different tables. If the file system is not case sensitive (as for Windows or for HFS+ file systems under Mac OS X), mytbl and MYTBL refer to the same table. You should keep this issue in mind if you develop a database on a server that uses case sensitive filenames and there is a possibility you might move the database to a server where filenames are not case sensitive.
    One way to deal with the issue of case sensitivity is to always name your databases and tables with a given lettercase. Another is to set the lower_case_table_names server variable to 1, which has two effects:
    . The server converts a table's name to lowercase before creating the corresponding disk files.
    . When the table is referenced later in a query, the server converts its name to lowercase before attempting to find the table on disk.
    The result of these actions is that names are not treated as case sensitive. However, there are two caveats you should keep in mind with respect to use of the lower_case_table_names variable. First, it does not apply to database names until MySQL 4.0.2; prior to that it applies only to table names. Second, you should enable this variable before you start creating databases or tables, not after.
    If you create names that include uppercase characters and then set the variable, it will not have the desired effect because then you will already have names stored on disk that are not entirely lowercase. To avoid this problem, rename any databases or tables with names that contain uppercase characters to names that are entirely lowercase before enabling the variable.
    Factors That Affect Maximum Table Size
    Table sizes in MySQL are bounded, but sizes are limited by a combination of factors, so it is not always a simple matter to determine precisely what the bounds are. Factors that affect table size are as follows:
    . MySQL has its own internal limits on table sizes. These vary by table type:
    o For ISAM tables, the .ISD and .ISM files are limited to 4GB apiece.
    o For MyISAM tables, the .MYD and .MYI files also are limited to 4GB apiece by default. However, by using the AVG_ROW_LENGTH and MAX_ROWS options when you create the table, the files can be approximately 8 million terabytes apiece.
    o The maximum size of a MERGE table is a function of the combined maximum sizes of its constituent MyISAM tables.
    o BDB table sizes are bound by the .db file size allowed by the handler. This varies according to the table page size (which is determined at server build time), but for even the smallest page size (512 bytes), the maximum file size is 2 terabytes.
    o For InnoDB, the maximum size of the InnoDB tablespace is 4 billion pages, where the default page size is 16KB. (MySQL can be recompiled from source to use an InnoDB page size ranging from 8KB to 64KB.) The maximum tablespace size also is the bound on the size of any individual InnoDB table.
    . The operating system imposes a maximum file size limit. In general, the trend has been for operating systems to relax file size constraints over time, but limits as low as 2GB are still relatively common. This size limit applies to files used to represent tables, such as the .MYD and .
    MYI files for a MyISAM table. It also applies to the component files that make up the InnoDB tablespace. However, the InnoDB tablespace size can easily exceed the maximum file size; just configure a tablespace that consists of multiple files, each of which is the maximum file size. Another way to circumvent the file size limit is to use raw partitions in the InnoDB tablespace, which can be done as of MySQL 3.23.41. Tablespace components that are on raw partitions can be as large as the partition itself.
    . For table types that represent data and indexes in separate files (such as ISAM and MyISAM), a table's size limit is reached when any of its individual files hits its own size limit. The table's indexing characteristics will affect which file this will be. For a table with no or few indexes, it is likely that the data file will reach its size limit first. For a heavily indexed table, the index file may hit the limit first.
    . The presence of an AUTO_INCREMENT column implicitly limits the number of rows a table may have. For example, if the column is TINYINT UNSIGNED, the maximum value it may hold is 255, so that also becomes the maximum number of rows the table may hold. Larger integer types allow more rows. More generally, placing any unique index on a table limits its row count to the maximum number of unique values in the index.
    To determine the actual table size you can achieve, you must consider all applicable factors. The effective maximum table size likely will be determined by the smallest of those factors. Suppose you want to create a ISAM table. MySQL will allow the data and index files to reach 4GB.
    But if your operating system imposes a size limit on files of 2GB, that will be the effective limit for the table files. On the other hand, if your system has large file support, files can be bigger than 4GB and then the determining factor on table size will be MySQL's internal 4GB limit.
    &With respect to InnoDB tables, one point to keep in mind is that all such tables must fit within the InnoDB tablespace. If you have a single InnoDB table, it can be as large as the tablespace. But if, as is more likely, you have many InnoDB tables, they all share the same space and thus each is constrained in size not only by the size of the tablespace but also by how much of the tablespace is allocated to other tables.
    Any individual InnoDB table can grow as long as the tablespace is not full. Conversely, when the tablespace fills up, no InnoDB table can grow any larger until you add another component to the tablespace to make it bigger.
    Implications of Data Directory Structure for System Performance;
    The structure of the MySQL data directory is easy to understand because it uses the hierarchical structure of the file system in such a natural way. At the same time, this structure has certain performance implications, particularly regarding operations that open the files that represent database tables.
    One consequence of the data directory structure is that for table handlers that represent each table using multiple files, an open table can require multiple file descriptors, not just one. The server caches descriptors intelligently, but a busy server can easily use up lots of them while servicing many simultaneous client connections or executing complex queries that reference several tables. This can be a problem, because file descriptors are a scarce resource on many systems, particularly those that set the default per-process descriptor limit fairly low.
    For example, a MyISAM table is represented by three files. If you want to have 10,000 MyISAM tables, your database directory will contain 30,000 files. With that many files, you may notice a slowdown due to the time taken by file-opening operations. (Linux ext2 and Solaris file systems are subject to this problem.) If this is cause for concern, you might want to think about using a type of file system that is highly efficient at dealing with large numbers of files.
    For example, ReiserFS exhibits good performance even with large numbers of small files. If that is not possible, it may be necessary to reconsider the structure of your tables in relation to the needs of your applications and reorganize your tables accordingly. Ask whether or not you really require so many tables; sometimes applications multiply tables needlessly.
    An application that creates a separate table per user results in many tables, all of which have identical structures. If you wanted to combine the tables into a single table, you might be able to do so by adding another column identifying the user to which each row applies. If this significantly reduces the number of tables, the application's performance improves.
    As always in database design, you must consider whether this particular strategy is worthwhile for a given application. Reasons not to combine tables in the manner just described are as follows:
    . Increased disk space requirements. Combining tables reduces the number of tables required (decreasing table-opening times) but adds another column (increasing disk space requirements). This is a typical time versus space tradeoff, and you'd need to decide which factor is most important. If speed is paramount, you'd probably be willing to sacrifice a little extra disk space. If space is tight, it might be more acceptable to use multiple tables and live with a slight delay.
    . Security considerations. These may constrain your ability or desire to combine tables. One reason to use a separate table per user is to allow access to each table only to that user by means of table-level privileges. If you combine tables, data for all users will be in the same table.
    MySQL has no provision for restricting access to particular rows to a given user; thus, you might not be able to combine tables without compromising access control. On the other hand, if all access to the data is controlled by your application (users never connect directly to the database), you can combine the tables and use application logic to enforce row-level access to the combined result.
    Another way to create many tables without requiring so many individual files is to use InnoDB tables. The InnoDB handler associates only a .frm file uniquely with each table and stores the data and index information for all InnoDB tables together in the InnoDB tablespace.
    This minimizes the number of disk files needed to represent the tables, and it also substantially reduces the number of file descriptors required for open tables. InnoDB needs only one descriptor per component file of the tablespace (which is constant during the life of the server process) and briefly a descriptor for any table that it opens while it reads the table's .frm file.
    MySQL Status and Log Files
    In addition to database directories, the MySQL data directory contains a number of status and log files. The default location for these files is the server's data directory, and the default name for each of them is derived using the server host name, denoted as HOSTNAME in the table.
    MySQL Status and Log Files

    File Type

    Default Name

    File Contents

    Process ID file

    The server process ID

    General query log


    Connect/disconnect events and query information

    Slow-query log


    Text of queries that take a long time to process

    Update log


    Text of queries that modify data

    Binary update log


    Binary representation of queries that modify data

    Binary update log index


    List of current binary update log files

    Error log


    Startup and shutdown events and error conditions

    The Process ID File
    The server writes its process ID (PID) into the PID file when it starts up and removes the file when it shuts down. The PID file is the means by which a server allows itself to be found by other processes. For example, if the operating system runs the mysql.server script at system shutdown time to shut down the MySQL server, that script examines the PID file to determine which process it needs to send a termination signal to.
    The MySQL Log Files
    MySQL can maintain a number of different log files. Most logging is optional; you can use server startup options to enable just the logs you need and also to specify their names if you don't like the defaults. This section describes the log files briefly.
    The general log provides general information about server operation: who is connecting from where and what queries they are issuing. The update log provides query information, too, but only for queries that modify database contents.
    The contents of the update log are written as SQL statements that can be executed by providing them as input to the mysql client. The binary update log is similar to the update log but is represented in a more efficient binary format. The accompanying binary log index file lists which binary log files the server currently is maintaining.
    Update and binary update logs are useful if you have a crash and must revert to backup files because you can repeat the updates performed after the backup was made by feeding the logs to the server. This allows you to bring your databases up to the state they were in when the crash occurred.
    Here is a sample of the kind of information that appears in the general log as the result of a short client session that creates a table in the test database, inserts a row into the table, and then drops the table:
    020727 15:00:17       1 Connect     sampadm@localhost on test
                          2 Query       CREATE TABLE mytbl (val INT)
                          2 Query       INSERT INTO mytbl VALUES(1)
                          2 Query       DROP TABLE mytbl
                          2 Quit
    The general log contains columns for date and time, server thread (connection) ID, event type, and event-specific information. For any line that is missing the date and time fields, the values are the same as for the previous line that does have them. (In other words, the server logs the date and time only when they change from the previously logged date and time.)
    The same session appears in the update log as follows. The statements include terminating semicolons, allowing them to be given as input to the mysql program should the updates need to be repeated for a recovery operation.
    use test;
    CREATE TABLE mytbl (val INT);
    INSERT INTO mytbl VALUES(1);
    DROP TABLE mytbl;
    For the update log, an extended form of logging is available by using the --log-long-format option. Extended logging provides information about who issued each query and when. This uses more disk space, of course, but may be useful if you want to know who is doing what without trying to correlate update log contents with the connection events in the general log.
    For the session just shown, extended update logging produces the following information:
    # Time: 020727 15:00:17
    # User@Host: sampadm[sampadm] @ localhost []
    use test;
    CREATE TABLE mytbl (val INT);
    # User@Host: sampadm[sampadm] @ localhost []
    INSERT INTO mytbl VALUES(1);
    # User@Host: sampadm[sampadm] @ localhost []
    DROP TABLE mytbl;
    The extra information is written using lines that begin with '#' so that they are interpreted as comments if you feed the log to mysql for execution by the server.
    The error log contains a record of diagnostic information produced by the server when exceptional conditions occur. It's useful if the server fails to start up or exits unexpectedly because it often will contain the reason why.
    Logs can grow quite large, so it's important to make sure they don't fill up your file system. You can expire the logs periodically to keep the amount of space that use within bounds.
    It's a good idea to make sure your log files are secure and not readable by arbitrary users because they may contain the text of queries that include sensitive information, such as passwords. For example, the following log entry displays the password for the root user; it's certainly not the kind of information you want just anyone to have access to:
    020727 15:47:24 4 Query UPDATE user SET
    WHERE user='root'
    The logs are written to the data directory by default, so securing your logs is a matter of securing the data directory against being accessed through login accounts other than that of the MySQL administrator.
    Repositioning DD Contents
    The preceding part of this chapter discusses the data directory structure in its default configuration, which is that all databases, status, and log files are located within it. However, you have some latitude in determining the placement of the data directory's contents. MySQL allows you to relocate the data directory itself or certain elements within it. There are several reasons why you might want to do this:
    . You can put the data directory on a file system that has a capacity greater than the file system where it's located by default.
    . If your data directory is on a busy disk, you can put it on a less active drive to balance disk activity across physical devices. You can put databases and log files on different drives or distribute databases across drives for the same reasons. Similarly, the InnoDB tablespace is conceptually a single large block of storage, but you can put its individual component files on different drives to improve performance.
    . Putting databases and logs on different disks helps minimize the damage that can be caused by a failure of a single disk.
    . You might want to run multiple servers, each with its own data directory. This is one way to work around problems with per-process file descriptor limits, especially if you cannot reconfigure the kernel for your system to allow higher limits.
    . Some systems keep server PID files in a specific directory, such as /var/run. You might want to put the MySQL PID file there, too, for consistency of system operation.
    In similar fashion, if your system uses /var/log for log files, you can also put the MySQL logs there. (However, many systems allow only root to write to these directories. That means you'd need to run the server as root, which for security reasons is not a good idea.)
    The rest of this section discusses which parts of the data directory can be moved and how you go about making such changes.
    Relocation Methods
    There are two ways to relocate the data directory or elements within it:
    . You can specify an option at server startup time, either on the command line or in an option file. For example, if you want to specify the data dir ectory location, you can start the server with a --datadir=dir_name option on the command line or you can put the following lines in an op tion file:
    . [mysqld]
    . datadir=dir_name
    Typically, the option file group name for server options is [mysqld], as shown in the example. However, depending on your circumstances, other option group names may be more appropriate. For example, the [embedded] group applies to the embedded server. Or if you're running multiple servers using mysqld_multi, the group names will be of the form [mysqldn], where n is some integer associated with a particular server instance.
    . You can move the thing to be relocated, and then make a symlink (symbolic link) in the original location that points to the new location.
    Neither of these methods works universally for everything that you can relocate. Table below summarizes what can be relocated and which relocation methods can be used. If you use an option file, it is possible to specify options in the global option file (such as /etc/my.cnf under UNIX or C:\my.cnf or my.ini in the system directory under Windows).
    It's also possible to use the option file my.cnf in the default data directory (the directory compiled into the server). This is a good option file to use for server-specific options if you run multiple servers, but because the server looks for it only in the compiled-in data directory location, the file won't be found if you relocate that directory. (One workaround for this problem is to move the data directory and then make its original location a symlink that points to the new location.)

    Relocation Method Summary

    Entity to Relocate

    Applicable Relocation Methods

    Entire data directory

    Startup option or symlink

    Individual database directories


    Individual database tables


    InnoDB tablespace files

    Startup option

    PID file

    Startup option

    Log files

    Startup option

    Assessing the Effect of Relocation
    Before attempting to relocate anything, it's a good idea to verify that the operation will have the desired effect. I tend to favor the du, df, and ls-l commands for obtaining disk space information, but all of these depend on correctly understanding the layout of your file system.
    The following example illustrates a subtle trap to watch out for when assessing a data directory relocation. Suppose your data directory is /usr/local/mysql/data and you want to move it to /var/mysql because df indicates the /var file system has more free space (as shown by the following example):
    % df /usr /var
    Filesystem 1K-blocks Used Avail Capacity Mounted on
    /dev/wd0s3e 396895 292126 73018 80% /usr
    /dev/wd0s3f 1189359 1111924 162287 15% /var
    How much space will relocating the data directory free up on the /usr file system? To find out, use du-s to see how much space that directory uses:
    % cd /usr/local/mysql/data %
    du -s
    133426 .
    That's about 130MB, which should make quite a difference on /usr. But will it really? Try df in the data directory:
    % df /usr/local/mysql/data
    Filesystem 1K-blocks Used Avail Capacity Mounted on
    /dev/wd0s3f 1189359 1111924 162287 15% /var
    That's odd. If we're requesting the free space for the file system containing the data directory (that is, /usr), why does df report the space on the /var file system? The following ls-l command provides the answer:
    % ls -l /usr/local/mysql/data
    lrwxrwxr-x  1 mysqladm  mysqlgrp  10 Dec 11 23:46 data -> /var/mysql
    This output shows that /usr/local/mysql/data is a symlink to /var/mysql. In other words, the data directory already has been relocated to the /var file system and replaced with a symlink that points there. So much for freeing up a lot of space on /usr by moving the data directory to /var!
    Moral: A few minutes spent assessing the effect of relocation is a worthwhile investment. It doesn't take long, and it can keep you from wasting a lot of time moving things around only to find that you've failed to achieve your objective.
    Relocating the Entire Data Directory
    To relocate the data directory, bring down the server and move the data directory to its new location. Then you should either remove the original data directory and replace it with a symlink that points to the new location or restart the server with a --datadir option that explicitly indicates the new location. The symlink method is preferable if the data directory contains a my.cnf file and you want the server to continue to find it.
    Relocating Individual Databases
    The server wants to find database directories in the data directory, so the only way to relocate a database is by the symlink method. Under UNIX, do so as follows:
    1.	Shut down the server if it is running.
    2.	Copy or move the database directory to its new location.
    3.	Remove the original database directory.
    4.	Create a symlink in the data directory that has the name of the original
    5.	database and that points to the new database location.
    6.	Restart the server.
    The following example shows how you might use this procedure to move a database bigdb to a different location:
    % mysqladmin -p -u root shutdown
    Enter password: ******
    % cd DATADIR
    % tar cf - bigdb | (cd /var/db; tar xf -)
    % mv bigdb bigdb.orig
    % ln -s /var/db/bigdb .
    % mysqld_safe&
    You should execute these commands while logged in as the MySQL administrator. The procedure shown here renames the original database directory to bigdb.orig as a precaution. After you verify that the server works properly with the relocated database, you can remove the original one:
    % rm -rf bigdb.orig
    Under Windows, database relocation is handled somewhat differently:
    1. Shut down the server if it is running.
    2. Move the database directory to where you want it.
    3. Create a .sym file in the MySQL data directory that points to the new database location. For example, if you move the sampdb database from C:\mysql\data\sampdb to E:\mysql-book\sampdb, you should create a file named sampdb.sym in C:\mysql\data that contains the following line:
    4. E:\mysql-book\sampdb\
    The .sym file acts as a symbolic link to let the MySQL server know where to find the relocated database directory.
    5. Make sure that symbolic link support is enabled when you start the server. You can do this with the --use-symbolic-links option on the command line or by placing the following lines in an option file:
    6. [mysqld]
    7. use-symbolic-links
    For Windows database relocation to work properly as just described, you must be running a -max server (mysqld-max or mysqld-max-nt) from MySQL 3.23.16 or later.
    If you're moving a database to another file system as an attempt to redistribute database storage, remember that InnoDB table contents are stored within the InnoDB tablespace, not in the database directory. For a database composed primarily of InnoDB tables, relocating the database directory may have little effect on storage distribution.
    Removing a Relocated Database
    You can remove a database with the DROP DATABASE statement, but servers from versions of MySQL older than 3.23 have trouble removing a database that has been relocated.
    The tables in the database are removed correctly, but an error occurs when the server attempts to remove the database directory because the directory is a symlink and not a real directory. If you encounter this problem, you must complete the DROP DATABASE operation by manually removing the database directory and the symlink that points to it.
    Relocating Individual Tables
    Relocation of an individual table is supported only under certain limited circumstances:
    .	You must be using MySQL 4.0 or later.
    .	Your operating system must have a working realpath() call.
    .	The table to be relocated must be a MyISAM table.
    If those conditions are all true, you can move the table's .MYD data and .MYI index files to their new locations and then create symlinks to them in the database directory under the original data and index filenames. (Leave the .frm file in the database directory.)
    You should not try to relocate a table if any of the preceding conditions are not satisfied. If you do so anyway and then refer to the table with an ALTER TABLE, OPTIMIZE TABLE, or REPAIR TABLE statement, your changes may be undone. Each of those statements operates by creating in the database directory a temporary table that implements your alteration or optimization, and then deleting the original table and renaming the temporary table to the original name.
    The result is that your symlinks are removed and the new table ends up right back in the database directory where your original table was before you moved it. Furthermore, the old table files that you moved out of the database directory are still in the location where you moved them-and you might not even realize they are there, continuing to take up space.
    Also, the symlinks have been destroyed, so when you realize later what has happened, you may not have any good way of tracking down the files if you've forgotten where you moved them. Because it's difficult to guarantee that no one with access to the table will ever alter or optimize it (and thus undo any attempted relocation), it's best to leave tables in the database directory.
    Relocating the InnoDB Tablespace
    You configure the InnoDB tablespace initially by listing the locations of its component files in an option file, using the innodb_data_home_dir and innodb_data_file_path options. If you have already created the tablespace, it's possible to relocate regular files that are part of it, for example, to distribute them across different file systems.
    Because you list the file locations using startup options, the way to relocate some or all of the tablespace files is like this:
    1. Shut down the server if it is running.
    2. Move the tablespace file or files that you want to relocate.
    3. Update the option file where the InnoDB configuration is defined to reflect the new locations of any files that you moved.
    4. Restart the server.
    Strictly speaking, it's possible to relocate a tablespace component by moving it and then creating a symlink to it at the original location. But there's no point in doing so; you have to list a location for component in the option file anyway, so you may as well list the real location rather than that of a symlink.
    Relocating Status and Log Files
    To relocate the PID file, bring down the server and then restart it with the appropriate option to specify the file's new location. For example, to create the PID file as /tmp/, use --pid-file=/tmp/ on the command line or include the following lines in an option file:
    If you specify the filename as an absolute pathname, the server creates the file using that pathname. Otherwise, the file is created under the data directory. For example, if you specify, the PID file will be in the data directory.
    Relocation Precautions
    You should bring down the server before performing any relocation operation and then restart it afterward. For some types of relocations, such as moving a database directory, it is possible to keep the server running, but not recommended.
    If you do that, you must make sure the server is not accessing the database you're moving. You should also be sure to issue a FLUSH TABLES statement before moving the database to make sure the server closes all open table files. Failure to observe these precautions can result in damaged tables.
    You should bring down the server before performing any relocation operation and then restart it afterward. For some types of relocations, such as moving a database directory, it is possible to keep the server running, but not recommended.
    If you do that, you must make sure the server is not accessing the database you're moving. You should also be sure to issue a FLUSH TABLES statement before moving the database to make sure the server closes all open table files. Failure to observe these precautions can result in damaged tables.
    General MySQL Administration
    Securing a New Installation
    The MySQL installation procedure sets up the server's data directory and populates it with two databases:
    . A mysql database that contains the grant tables
    . A test database that can be used for testing purposes
    When MySQL is installed for the first time, the grant tables in the mysql database are in their initial state that allows anyone to connect to the server without a password. This could be insecure, so you should set up some passwords. If you're setting up a second installation on a machine that already has MySQL installed in another location, you'll need to set up passwords for the new server.
    The examples discussed below use a MySQL server hostname of change the instructions to use your own hostname. The examples also assume that your server is running, because you'll need to connect to it.
    How the Grant Tables Are Set Up Initially
    The grant tables in the mysql database are set up during the MySQL installation procedure with two kinds of accounts:
    . Accounts that have a username of root. These are superuser accounts intended for administrative purposes. They have all privileges and can do anything, including deleting all your databases.
    Note: The fact that the MySQL and UNIX superuser accounts each have the name root is coincidental. Each has exceptional privileges, but they have nothing to do with each other.
    . Accounts that are associated with no username at all. These are "anonymous" accounts; they're useful for testing because they allow people to connect to the server without having accounts explicitly set up for them in advance. Anonymous users usually are given very few privileges to limit the scope of what they can do.
    Every account known to a MySQL server is listed in the user table of its mysql database, so that's where you'll find the initial root and wordiness anonymous accounts. None of these accounts have passwords initially because it's expected that you'll supply your own. Therefore, one of your first acts in administering a MySQL installation should be to establish passwords, at least for the privileged accounts. Otherwise, unauthorized users can gain root access to your server easily.
    After you secure the initial accounts, you can proceed to set up other accounts to allow the members of your user community to connect to the server under names that you specify and with privileges appropriate for what those users should be allowed to do.
    Entries in the user table contain a Host value indicating where a user can connect from, and User and Password values indicating the name and password the user must give when connecting from that host. The user table also has a number of columns indicating what superuser privileges the account has, if any.
    Under UNIX, the data directory is initialized during the installation procedure by running mysql_install_db, a script that sets up the grant tables in the mysql database. mysql_install_db initializes the user table as follows:




    Superuser Privileges















    These entries allow connections as follows:
    . The root entries allow you to connect to the local MySQL server, using a hostname of localhost or For example, from you can connect as root with the mysql program using either of the following commands:
    . % mysql -h localhost -u root
    . % mysql -h -u root
    As root, you have all privileges and can perform any operation.
    . The entries with blank User values are the anonymous accounts. They allow connections to the local server without any username:
    . % mysql -h localhost
    . % mysql -h
    . Anonymous users have no superuser privileges, but another grant table (the db table, not shown) specifies that anonymous users can use the test database or any database having a name that begins with test.
    Under Windows, the data directory and the mysql database are included pre-initialized with the MySQL distribution with accounts that are set up somewhat differently than those on UNIX systems. The Windows user table entries look like this:




    Superuser Privileges

















    In these entries, the Host value of % acts as a wildcard, meaning that the user named by the User value can connect from any host. Thus, the initial Windows user table entries specify accounts as follows:
    . You can connect as root from the local host or from any remote host. As root, you have all privileges and can perform any operation.
    . You can connect anonymously with no username. If you connect from the local host, you will have the same superuser privileges as root and can do anything. If you connect remotely from another host, you will have no superuser privileges. On Windows, the db table allows anonymous users access to the test database or any database having a name that begins with test.
    For Windows, an important implication of the fact that one of the root accounts has % for a Host value is that anyone, anywhere can connect as root with no password. This leaves your server completely vulnerable, so you'll certainly want to lock down that account by giving it a password. In addition, the fact that the localhost anonymous account has the same privileges as root means that it's not sufficient to assign passwords just to the root accounts. It's also a good idea to establish a password for the local anonymous account to revoke its superuser privileges or perhaps to delete it entirely. The following discussion covers all three options.
    Establishing Passwords for the Initial MySQL Accounts
    This section describes the various methods for setting passwords for the root accounts. Depending on the method you use, you may also need to tell the server to reload the grant tables so that it notices the change.
    (The server performs access control using in-memory copies of the grant tables. For some methods of changing passwords in the user table, the server may not recognize that you've changed anything, so you must tell it explicitly to reread the tables.) This section also suggests some options for dealing with the anonymous superuser account that is present initially in the user table on Windows.
    One way to establish passwords is to use the mysqladmin program:
    % mysqladmin -h localhost -u root password "rootpass" %
    mysqladmin -h -u root password "rootpass"
    This works for both UNIX and Windows. The word "password" in these commands is a literal word that indicates what you want mysqladmin to do (set a password), and rootpass represents the value to which you want to set the password. Both mysqladmin commands are necessary.
    The first sets the password for the root account associated with localhost and the second for the account associated with (On Windows, the second command sets the password for the root account associated with the Host value of %.)
    A second way to set the passwords is to issue SET PASSWORD statements. Each statement names the User and Host values of the user table entry that you want to modify, in 'user_name'@'host_name' format. For UNIX, change the passwords like this:
    % mysql -u root
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass');
    mysql> SET PASSWORD FOR 'root'@'' = PASSWORD('rootpass');
    For Windows, use a slightly different second statement because one of the root accounts has a different Host value:
    C:\> mysql -u root
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass');
    mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('rootpass');
    Another way to assign passwords is to modify the user table directly. This works for any version of MySQL, and actually may be your only recourse if you have a really old version of MySQL that predates both mysqladmin password and SET PASSWORD. To set the password for both root entries at the same time, do the following:
    % mysql -u root
    mysql> USE mysql;
    mysql> UPDATE user SET Password=PASSWORD('rootpass') WHERE User='root'; mysql> FLUSH PRIVILEGES;
    If you use mysqladmin password or SET PASSWORD to change passwords, the server notices that you've made a change to the grant tables and automatically rereads them to refresh its in-memory copy of the tables.
    . If you use UPDATE to modify the user table directly, it's necessary to tell the server to reload the tables explicitly. One way to do so, if you have MySQL 3.22.9 or later, is to issue a FLUSH PRIVILEGES statement, as shown in the preceding example. You can also use mysqladmin to reload the grant tables:
    % mysqladmin -u root reload
    % mysqladmin -u root flush-privileges
    reload should work for any version of MySQL; flush-privileges is available as of MySQL 3.22.12. From now on, whenever I say "reload the grant tables," I mean you should use one of the three methods just shown; it doesn't matter which one. (Examples in the remaining part of this chapter generally use FLUSH PRIVILEGES.)
    After you have set the root password (and reloaded the grant tables if necessary), you'll need to specify your new password whenever you connect to the server as root:
    % mysql -p -u root
    Enter password: ********
    Another effect of setting the root password is that no one else will be able to connect as root without knowing the password, which is really the point of the exercise.
    The need to specify a password when connecting as root from this point on will be true not just for mysql but also for programs like mysqladmin, mysqldump, and so on. For brevity, many of the examples in the rest of this chapter do not show the -u or -p options; I assume you'll add them as necessary whenever you connect to the server as root.
    The user table at this stage still contains anonymous user entries that have no password. If you have no need for these entries, consider deleting them entirely. To do this, connect to the server as root (using your new password, of course), delete any rows from the user and db tables that have a blank User value, and reload the grant tables:
    % mysql -p -u root
    Enter password: ********
    mysql> USE mysql;
    mysql> DELETE FROM user WHERE User = '';
    mysql> DELETE FROM db WHERE User = '';
    If you leave the anonymous user accounts in place, remember that the local anonymous user has the same privileges as root on Windows, which may be more access than you care to allow. To weaken that account to the same strength as the one for the remote anonymous user, revoke its superuser privileges by connecting to the server as root and issuing these statements:
    mysql> REVOKE ALL ON *.* FROM ''@'localhost';
    mysql> REVOKE GRANT OPTION ON *.* FROM ''@'localhost';
    Setting Up Passwords for a Second Server
    The preceding instructions assume that you want to establish passwords on a system that hasn't had MySQL installed on it before. However, if MySQL is already installed in one location and you're setting the passwords for a new server installed in a second location on the same machine, you may find that when you attempt to connect to the new server without a password, it rejects the attempt with the following error:
    % mysql -u root
    ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
    Hm! Why did the server say it received a password when you didn't specify one? What this usually indicates is that you have an option file set up that lists the password for accessing the previously installed server. mysql finds the option file and automatically uses the password listed there. To override that and explicitly specify "no password," use a -p option and press Enter when mysql prompts for the password:
    % mysql -p -u root
    Enter password: just press Enter
    You can use this strategy for mysqladmin and for other MySQL programs as well.
    Server Startup & Shutdown
    One general goal that you will have as a MySQL administrator is to make sure the server is running as much of the time as possible so that your users can access it. Occasionally, however, it's necessary to bring down the server. For example, if you're relocating a database, you don't want the server updating tables in that database at the same time.
    The tension between the desire to keep the server running and the need to shut it down occasionally is something this book can't resolve for you. But we can at least discuss how to get the server started and stopped so that you have the ability to perform either operation as you see fit. Many aspects of the procedures for this are different for UNIX and Windows, so the following discussion covers them separately.
    Running the MySQL Server on UNIX
    On UNIX, the MySQL server can be started either manually or automatically at system startup time. It's also possible to arrange for the server to run automatically at system boot time as part of the standard startup procedure. (In fact, this is probably how you'll start the server under normal operating conditions after you get everything set up the way you want.)
    But before discussing how to start the server, let's consider which login account it should be run under when it does start. On a multiuser operating system such as UNIX, you have a choice about which login account to use for running the server. For example, if you start it manually, the server runs as the UNIX user you happen to be logged in as. That is, if I log in as ravish and start the server, it runs as ravish. If instead I use the su command to switch user to root and then start the server, it runs as root.
    You should keep in mind two goals for your MySQL server startup procedures under UNIX:
    You want the server to run as some user other than root. To say the server runs "as" a given user means that the server process is associated with the user ID of that user's login account, and that it has that user's privileges for reading and writing files in the file system. This has certain security implications, particularly for processes that run as the root user, because root is allowed to do anything, however dangerous.
    One way to avoid these dangers is to have the server relinquish its special privileges. Processes that start as root have the capability to change their user ID to that of another account and thus give up root's privileges in exchange for those of a regular unprivileged user. This makes the process less dangerous. In general, you should limit the power of any process unless it really needs root access, and mysqld in particular does not.
    The server needs to access and manage the contents of the MySQL data directory, but little else. This means that if the server starts as root, you should tell it to change during its startup procedure to run as an unprivileged user.
    You want the server to run as the same user all the time. It's inconsistent for the server to run as one user sometimes and as another user other times. That leads to files and directories being created under the data directory with different ownerships and can even result in the server not being able to access certain databases or tables. Consistently running the server as the same user enables you to avoid this problem.
    Running the Server Using an Unprivileged Login Account
    To set up for running the server as an unprivileged non-root user, follow this procedure:
    Shut down the server if it's running:
    % mysqladmin -p -u root shutdown
    Choose a login account to use for running mysqld. You can use any account, but it's cleaner conceptually and administratively to create a separate account that is devoted exclusively to MySQL activity. You can also designate a group name specifically for use with MySQL. I'll call these user and group names mysqladm and mysqlgrp. If you use different names, substitute them anywhere you see mysqladm and mysqlgrp throughout this book.
    For example, if you have installed MySQL under your own account and have no special administrative privileges on your system, you'll probably run the server under your own user ID. In this case, substitute your own login name and group name for mysqladm and mysqlgrp. If you installed MySQL on Linux using an RPM file, the installation procedure may have created an account automatically, using mysql for both the user and group names. Substitute that name for mysqladm and mysqlgrp.
    Methods for Starting the Server
    After you've decided what account to use for running the server, you have several choices about how to start it up. It's possible to run the server manually from the command line or automatically during the system startup procedure. Methods for doing this include the following:
    Invoke mysqld directly
    This is probably the least-common method. I won't discuss it further except to say that mysqld--help is a useful command for finding out what startup options the server supports.
    Invoke the mysqld_safe script
    mysqld_safe invokes the server and then monitors it and restarts it if it dies. mysqld_safe is commonly used on BSD-style versions of UNIX, and it is also used by mysql.server on non-BSD systems. (mysqld_safe is called safe_mysqld prior to MySQL 4, which you'll need to take into account for any instructions given in this chapter relating to mysqld_safe.)
    mysqld_safe redirects error messages and other diagnostic output from the server into a file in the data directory to produce an error log. mysqld_safe sets the ownership of the error log so that it is owned by the user named with the --user option. This can lead to trouble if you to use different --user values at different times.
    The symptom is that mysqld_safe's attempt to write to the error log will fail with a "permission denied" error. This can be especially problematic because if you examine the error log to see what the difficulty is, it will contain no useful information related to the cause. If this problem occurs, remove the error log and invoke mysqld_safe again.
    Invoke the mysql.server script
    mysql.server starts up the server by executing mysqld_safe. This script can be invoked with an argument of start or stop to indicate whether you want the server to start up or shut down. It serves as a wrapper around mysqld_safe for use on systems that use the System V method of arranging startup and shutdown scripts into several directories. Each directory corresponds to a particular run level and contains scripts to be invoked when the machine enters or exits that run level.
    To coordinate several servers, use the mysqld_multi script
    This startup method is more complicated than the others, so I'll defer discussion to the "Running Multiple Servers" section later in this chapter.
    The mysqld_safe and mysqld_multi scripts are installed in the bin directory under the MySQL installation directory or can be found in the scripts directory of the MySQL source distribution. The mysql.server script is installed under the share/mysql directory under the MySQL installation directory or can be found in the support-files directory of the MySQL source distribution. You'll need to copy it to the proper startup directory and make it executable if you want to use it.
    If you install MySQL using an RPM file obtained from the MySQL Web site, the mysql.server script is installed under the name mysql; you'll find it in the /etc/rc.d/init.d directory. If you use a MySQL RPM obtained from RedHat, a similar startup script is installed under the name mysqld.
    The arrangements that you'll need to make to have a startup script execute at system boot time depend on the type of system you have. Read through the following examples and use or adapt the instructions that most closely match the startup procedures for your system.
    For BSD-style systems, it's common to have a few files in the /etc directory that initiate services at boot time. These files often have names that begin with rc, and it's likely that there will be a file named rc.local (or something similar) intended specifically for starting locally installed services. On such a system, you might add lines like the following to rc.local to start up the server:
    if [ -x /usr/local/bin/mysqld_safe ]; then
        /usr/local/bin/mysqld_safe &
    Modify the lines appropriately if the pathname to your MySQL bin directory is different on your system.
    For System V-style systems, you can install mysql.server. Copy it to the appropriate startup directory under /etc. This may have been done for you already if you run Linux and installed MySQL from an RPM file. Otherwise, install the script in the main startup script directory with the name you want to use, make sure the script is executable, and place links to it in the appropriate run level directory.
    Note: I'll assume here that mysql.server gets installed into the startup directory under the name mysql, but I'll generally continue to discuss it as mysql.server to make it clear what I'm referring to.
    The layout for startup file directories varies from system to system, so you'll need to check around to see how your system organizes them. For example, under Solaris, the general multiuser run level is 2. The main script directory is /etc/init.d, and the run level directory is /etc/rc2.d, so the commands would look like this:
    # cp mysql.server /etc/init.d/mysql
    # cd /etc/init.d
    # chmod +x mysql
    # cd /etc/rc2.d
    # ln -s ../init.d/mysql S99mysql
    At system startup time, the boot procedure automatically will invoke the S99mysql script with an argument of start.
    Running the MySQL Server On Windows
    MySQL distributions for Windows include several servers, each of which is built with different options. You can start the server manually from the command line under any version of Windows.
    In addition, for NT-based systems, it's possible to install any server as a service. You can set the MySQL service to run automatically when Windows starts up, and control it from the command line or by using the Windows Services Manager. If you use one of the servers built specifically for NT, you can set it up so that clients can connect using named pipes.
    Running the Server Manually
    To start a server manually, invoke it from the command line:
    C:\> mysqld
    If you want error messages to go to the console window rather than to the error log (the mysql.err file in the data directory), use the --console option:
    C:\> mysqld --console
    Use mysqld-nt on NT-based systems if you want to allow connections via named pipes. Named pipe support is enabled by default for mysqld-nt up through MySQL 3.23.49. This has now been changed; to take advantage of named pipe support for MySQL 3.23.50 and up, add the --enable-named-pipe option to the startup command.
    (It's not necessarily a good idea to do this! The reason named pipes now are disabled by default is that they were found to cause problems at server shutdown time on many machines. If you use this option, be sure to check whether or not your server shuts down properly.)
    To stop the server, use mysqladmin:
    C:\> mysqladmin -p -u root shutdown
    Running the Server as a Service
    On NT-based versions of Windows, the MySQL server can be installed as a service using the following command:
    C:\> mysqld-nt --install
    This will cause the server to run automatically whenever Windows starts. If you prefer to use a service that does not run automatically, install the server as a "manual" service:
    C:\> mysqld-nt --install-manual
    These examples use mysqld-nt, but you can actually install any server as a service. You might install mysqld instead if you don't care about named pipe support, for example.
    As a general rule, when you install a server as a service, you give no other options on the command line and list them in an option file instead. An exception to this rule occurs if you install several Windows servers as services.
    After the server has been installed as a service, you can control it using the service name, which is MySql. This can be done from the command line or from the Windows Services Manager if you prefer a graphical interface. The Services Manager can be found as a Services item in the Windows Control Panel or in the Administrative Tools item in the Control Panel, depending on your version of Windows.
    To start or stop the service from the command line, use the following commands:
    C:\> net start MySql
    C:\> net stop MySql
    If you use the Services Manager, it presents a window that displays a list of the services it knows about, along with additional information such as whether each service is running and whether it is automatic or manual. To start or stop the MySQL server, select its entry in the services list and then choose the appropriate button or menu item.
    You can also shut down the server from the command line with mysqladmin shutdown.
    To remove the server from the list of services, shut it down if it is running, and then issue the following command:
    C:\> mysqld-nt --remove
    Specifying Startup Options
    On any platform, there are two primary methods for specifying startup options when you invoke the server:
    You can list them on the command line, in which case it's possible to use either the long or short forms of any option for which both forms are available. For example, you can use either --user=mysqladm or -u mysqladm.
    You can list the options in an option file. When specifying an option this way, only its long option form can be used, and it's given without the leading dashes:
    It's generally easiest to use an option file. You can do so for any startup method, and once you put the options there, they'll take effect each time the server starts. Listing options on the command line works only when starting up the server manually or by using mysqld_safe. It does not work for mysql.server, which is intended to support only start and stop options on the command line.
    Also, with limited exceptions, you cannot specify startup options on the command line if you use --install or --install-manual to install a Windows server as a service. (The exceptions are discussed in the "Running Multiple Servers" section later in this chapter.)
    The usual files used for specifying server options under UNIX are the /etc/my.cnf file and the my.cnf file in the data directory. Under Windows, you can use the my.ini file in the Windows system directory, C:\my.cnf, and the my.cnf file in the data directory. If the file you want to use it doesn't exist, create it.
    Generally, server startup options are placed in the [mysqld] option group. For example, to indicate that you want the server to run as mysqladm and to use a base directory location of /usr/local/mysql, you can put following group in the option file:
    That is equivalent to launching the server as follows with the options on the command line:
    % mysqld --user=mysqladm --basedir=/usr/local/mysql
    The complete list of option groups used by servers and the server startup programs is shown in the following table:


    Option Groups Used By Program


    [mysqld], [server]


    [mysqld], [server], [mysqld_safe], [safe_mysqld]


    [mysqld], [server], [safe_mysqld]


    [mysqld], [mysql_server]


    [embedded], [server]

    The line for libmysqld refers to the embedded server that can be linked into programs to produce MySQL-based applications that do not require a separate standalone server. (Chapter 6, "The MySQL C API," describes how to write applications that use the embedded server.)
    The [server] group can be used for options that apply to any server, whether standalone or embedded. The [mysqld] or [embedded] groups can be used for options that apply only to standalone servers or to embedded servers. Similarly, the [mysqld_safe] or [mysql_server] groups allow you to specify options that are used only when you invoke one startup script or the other.
    Prior to MySQL 4, mysqld_safe was called safe_mysqld. Instructions in this book that refer to the [mysqld_safe] option group that is used by mysqld_safe should be read as references to the [safe_mysqld] group if you're using safe_mysqld instead.
    See Appendix E for more information on the format and syntax of option files.
    If you launch the server by using a startup script, a third way to specify options is to modify the script to pass those options directly to the server. I don't recommend this except as a last resort. It has the significant disadvantage that you'll have to remember to redo your changes each time you install a new version of MySQL, which will wipe out your modified script with the new version.
    Shutting Down the Server
    To shut down the server manually, use mysqladmin:
    % mysqladmin -p -u root shutdown
    This works for both UNIX and Windows. If you installed the server as a service under Windows, it's also possible to stop the server manually from the command line:
    C:\> net stop MySql
    Or you can use the graphical interface offered by the Services Manager to select and stop the server.
    If you have set the server to start up automatically when your system boots, you shouldn't need to do anything special to stop it automatically at system shutdown time. BSD UNIX systems normally shut down services by sending processes a TERM signal. They either respond to the signal appropriately or are killed unceremoniously. mysqld responds by terminating when it receives this signal.
    If you run the MySQL server as a service on a Windows NT-based system, the service manager automatically will tell the server to stop at system shutdown time. Under other versions of Windows, or if you do not run the server as a service, you should bring down the server manually with mysqladmin shutdown at the command line before shutting down Windows.
    Regaining Control of the Server When You Can't Connect to It
    Under certain circumstances, you may need to restart the server manually due to an inability to connect to it. Of course, this is somewhat paradoxical because typically when you manually shut down the server, you do so by connecting to it with mysqladmin shutdown, which tells it to terminate. How then can this situation arise?
    First, the MySQL root password might have gotten set to a value that you don't know. This can happen when you change the password-for example, if you accidentally type an invisible control character when you enter the new password value. Or you may simply have forgotten the password.
    Second, under UNIX, connections to localhost are made through a UNIX domain socket file (for example, /tmp/mysql.sock). If the socket file gets removed, local clients won't be able to use it to connect. This might happen if your system runs a cron job that removes temporary files in /tmp now and then.
    If the reason you can't connect is that the socket file has been removed, you can get it back simply by restarting the server. The server will re-create the socket file when it comes back up. The trick here is that because the socket file is gone, you can't use it to establish a connection for telling the server to shut down. You must establish a TCP/IP connection instead. To do this, connect to the local server by specifying a host value of rather than localhost:
    % mysqladmin -p -u root -h shutdown
 is an IP number (it refers to the local host's loopback interface), so it explicitly forces a TCP/IP connection to be used rather than a socket connection.
    If it is the case that the socket file is being removed by a cron job, the missing-socket problem will recur until you change the cron job or use a socket file located somewhere else. You can specify a different socket by naming it in a global option file. For example, if the MySQL base directory is /usr/local/mysql, you can move the socket file there by adding the following lines to /etc/my.cnf:
    It's necessary to specify the pathname both for the server and for client programs so that they all use the same socket file. If you set the pathname only for the server, client programs will still expect to find the socket at the old location. Restart the server after making the change so that it creates the socket in the new location.
    Unfortunately, this method works only for clients that read the option file; many do, but some may not. If you recompile MySQL from source, you can reconfigure the distribution to use a different pathname by default for the server and clients both. This will also automatically affect third-party programs that use the client library.
    If you can't connect because you can't remember or don't know the root password, you need to regain control of the server so that you can set the password again. To do this, perform the following procedure:
    Shut down the server. Under UNIX, if you can log in as root on the server host, you can terminate the server using the kill command. Find out the server's process ID by looking in the server's PID file (which is usually located in the data directory) or by using the ps command. Then try telling the server process to shut down normally by sending it a TERM signal:
    # kill -TERM PID
    That way, tables and logs will be flushed properly. If the server is jammed and unresponsive to a normal termination signal, you can use kill-9 to forcibly terminate it.
    # kill -9 PID
    That is a last resort because there may be unflushed modifications, and you risk leaving tables in an inconsistent state. Under Linux, ps may show several mysqld "processes." These are really threads of the same process, so you can kill any of them to kill them all.
    If you start the server using mysqld_safe, it will be monitoring the server and will immediately restart it after you kill it. To avoid this, determine the PID of the mysqld_safe process and kill it first before killing mysqld.
    If you run the server as a service under Windows, you can bring it down normally without knowing any passwords by using the Services Manager or by issuing this command:
    C:\> net stop MySql
    To forcibly terminate the server on Windows, use the Task Manager (Alt-Ctrl-Del). Like kill-9 on UNIX, this is a last resort.
    Restart the server with the --skip-grant-tables option to disable use of the grant tables for verifying connections. That allows you to connect with no password and with all privileges. However, it also leaves your server wide open so that other people can connect the same way, so issue a FLUSH PRIVILEGES statement as soon as you connect:
    % mysql
    The FLUSH statement tells the server to reread the grant tables, causing it to start using them again. You will remain connected, but the server will require any subsequent connection attempts by other clients to be validated with the tables as usual. The FLUSH statement also re-enables the GRANT statement, which is disabled when the server is not using the grant tables.
    After changing the root password, you may want to shut down the server again and bring it back up using your normal startup procedure.
    User Account Management
    The MySQL administrator should know how to set up MySQL user accounts by specifying which users can connect to the server, where they can connect from, and what they can do while connected. This information is stored in the grant tables in the mysql database and is managed primarily by means of two statements:
    . GRANT creates MySQL accounts and specifies their privileges.
    . REVOKE removes privileges from existing MySQL accounts.
    These statements were introduced in MySQL 3.22.11 to make it easier to manage user accounts. Prior to 3.22.11, it was necessary to manipulate the contents of the grant tables directly by issuing SQL statements such as INSERT and UPDATE. GRANT and REVOKE act as a front end to the grant tables.
    They are more convenient to work with conceptually because you describe the permissions you want to allow, and the server maps your requests onto the proper grant table modifications automatically. Nevertheless, although it's much easier to use GRANT and REVOKE than to modify the grant tables directly.
    You may also want to consider using the mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution. These are Perl scripts that provide an alternative to the GRANT statement for setting up user accounts. mysql_setpermission requires that you have DBI support installed.
    The GRANT and REVOKE statements affect four tables:

    Grant Table



    Users who can connect to the server and their global privileges


    Database-level privileges


    Table-level privileges


    Column-level privileges

    There is a fifth grant table named host, but it is not affected by GRANT or REVOKE and is not discussed here.
    When you issue a GRANT statement for an account, an entry is created for that account in the user table. If the statement specifies any global privileges, those are recorded in the user table, too.
    If you specify privileges that are specific to a given database, table, or table column, they are recorded in the db, tables_priv, and columns_priv tables.
    The rest of this section describes how to set up MySQL user accounts and grant privileges, how to revoke privileges and remove users from the grant tables entirely, and how to change passwords or reset lost passwords.
    Creating New Users and Granting Privileges
    The syntax for the GRANT statement looks like this:
    GRANT privileges (columns)
    ON what TO account IDENTIFIED BY 'password'
    REQUIRE encryption requirements
    WITH grant or resource management options;
    Several of these clauses are optional and need not be specified at all. In general, you'll most commonly fill in the following parts:
    . privileges
    The privileges to assign to the account. For example, the SELECT privilege allows a user to issue SELECT statements and the SHUTDOWN privilege allows the user to shut down the server.
    . columns
    The columns the privileges apply to. This is optional, and you use it only to set up column-specific privileges. If you want to list more than one column, separate their names by commas.
    . what
    The level at which the privileges apply. The most powerful level is the global level for which any given privilege applies to all databases and all tables. Global privileges can be thought of as superuser privileges. Privileges also can be made database-specific, table-specific, or (if you specify a columns clause) column-specific.
    . account
    The account that is being granted the privileges. The account value consists of a username and a hostname in 'user_name'@'host_name' format because in MySQL, you specify not only who can connect but from where. This allows you to set up separate accounts for two users who have the same name but who connect from different locations.
    MySQL lets you distinguish between them and assign privileges to each independently of the other. The user_name and host_name values are recorded in the User and Host columns of the user table entry for the account and in any other grant table records that the GRANT statement creates.
    Your username in MySQL is just a name that you use to identify yourself when you connect to the server. The name has no necessary connection to your UNIX login name or Windows name. By default, client programs will use your login name as your MySQL username if you don't specify a name explicitly, but that's just a convention.
    There is also nothing special about the name root that is used for the MySQL superuser that can do anything. It's just a convention. You could just as well change this name to nobody in the grant tables and then connect as nobody to perform operations that require superuser privileges.
    . password
    The password to assign to the account. This is optional. If you specify no IDENTIFIED BY clause for a new user, that user is assigned no password (which is insecure). If you use GRANT to modify the privileges of an existing account, the account's password is either replaced or left unchanged, depending on whether you include or omit an IDENTIFIED BY clause.
    When you do use IDENTIFIED BY, the password value should be the literal text of the password; GRANT will encode the password for you. Don't use the PASSWORD() function as you do with the SET PASSWORD statement.
    The REQUIRE and WITH clauses are optional. REQUIRE is available as of MySQL 4.0.0 and is used for setting up accounts that must connect over secure connections using SSL. WITH is used to grant the GRANT OPTION privilege that allows the account to give its own privileges to other users. As of MySQL 4.0.2, WITH is also used to specify resource management options that allow you to place limits on how many connections or queries an account can use per hour. These options help you prevent the account from hogging the server.
    Usernames, passwords, and database and table names are case sensitive in grant table entries. Hostnames and column names are not. When you want to set up an account, it's generally possible to figure out the kind of GRANT statement to issue by asking some simple questions:
    . Who can connect, and from where? What is the user's name, and where will that user connect from?
    . What type of access should the account be given? That is, what level of privileges should the user have, and what should they apply to?
    . Are secure connections required?
    . Should the user be allowed to administer privileges?
    . Should the user's resource consumption be limited?
    The following discussion asks these questions and provides some examples showing how to use the GRANT statement to set up MySQL user accounts.
    Who Can Connect, and from Where?
    The account part of the GRANT statement specifies the user's name and where that user can connect from. You can allow a user to connect from as specific or broad a set of hosts as you like. At the one extreme, you can limit access to a single host if you know users will be connecting only from that host. For example, to grant access to all the tables in the sampdb database for host-specific accounts, you can use statements like these:
    GRANT ALL ON sampdb.* TO 'userxyz'@'localhost' IDENTIFIED BY 'user'; GRANT ALL ON sampdb.* TO 'userabc'@'' IDENTIFIED BY 'mkdtutorials9999032942';
    If the username or hostname parts of the account value do not contain any special characters such as '-' or '%', you may not need to quote them (for example, ravish@localhost is legal without quotes). However, it should always be safe to use quotes, and the examples in this book do so as a rule. But note that the username and hostname are quoted separately; use 'ravish'@'localhost', not 'ravish@localhost'.
    Allowing a user to connect from a single host is the strictest form of access you can allow. At the other extreme, you may have a user who travels a lot and needs to be able to connect from hosts all over the world. If the user's name is max, you can allow him to connect from anywhere, as follows:
    GRANT ALL ON sampdb.* TO 'user'@'%' IDENTIFIED BY 'diamond';
    The '%' character functions as a wildcard with the same meaning as in a LIKE pattern match. Thus, as a hostname specifier, % means "any host." This is the easiest way to set up a user, but it's also the least secure.
    To take a middle ground, you can allow a user to connect from a limited set of hosts. For example, to allow mary to connect from any host in the domain, use a host specifier of
    GRANT ALL ON sampdb.* TO 'xyz'@'' IDENTIFIED BY '26198509';
    The other LIKE wildcard character ('_') can be used in host values to match any single character.
    The host part of the account value can be given using an IP address rather than a hostname, if you want. You can specify a literal IP address or an address that contains pattern characters. Also, as of MySQL 3.23, you can specify IP numbers with a netmask indicating which bits to use for the network number:
    GRANT ALL ON sampdb.* TO 'user'@'' IDENTIFIED BY 'water';
    GRANT ALL ON sampdb.* TO 'abc'@'192.168.0.%' IDENTIFIED BY 'snow';
    GRANT ALL ON sampdb.* TO 'xyz'@''
    IDENTIFIED BY 'coke';
    The first of these statements indicates a specific host from which the user can connect. The second specifies an IP pattern for the 192.168.0 Class C subnet. In the third statement, specifies a netmask that has the first 17 bits turned on. It matches any host with 192.168.0 in the first 17 bits of its IP address.
    Using a host value of localhost in a GRANT statement allows the user to connect to the server from the local host by specifying a host value of localhost or (the local host's loopback IP address). A localhost account also matches on Windows when the user connects by specifying a hostname of '.'
    (period) if the server supports named pipes. On UNIX, connections to localhost are made via the UNIX socket file. On Windows, connections to '.' are made via a named pipe if named pipes are available. All other connections are made via TCP/IP, including connections to, the loopback address.
    If you give no hostname part at all in an account specifier, it's the same as using a host part of %. Thus, 'xyz' and 'max'@'%' are equivalent account values in GRANT statements. This means that if you intend to specify an account of 'xyz'@'localhost' but mistakenly write 'xyz@localhost' instead, MySQL will accept it as legal.
    What happens is that MySQL interprets 'xyz@localhost' as containing only a user part and adds the default host part of % to it, resulting in an effective account name of 'xyz@localhost'@'%'. To avoid this, be sure always to quote the user and host parts of account specifiers separately.
    Log Files
    When the MySQL server begins executing, it examines its startup options to see whether or not it should perform logging and opens the appropriate log files if it should. There are several types of logs you can tell the server to generate:
    . The general query log
    This log contains a record of client connections, queries, and various other miscellaneous events. It is useful for monitoring server activity-who is connecting, from where, and what they are doing. It's the most convenient log to use when you want to determine what queries clients are sending to the server, which can be very useful for troubleshooting or debugging.
    . The slow-query log
    This log's purpose is to help you identify statements that may be in need of being rewritten for better performance. The server maintains a long_query_time variable that defines "slow" queries. If a query takes more than that many seconds of real time, it is considered slow and is recorded in the slow-query log. The slow-query log is also used to log queries for which no indexes were used.
    . The update log
    This log records queries that modify the database. The term "update" in this context refers not just to UPDATE statements, but to any statement that modifies data. For this reason, it contains a record of queries such as DELETE, INSERT, REPLACE, CREATE TABLE, DROP TABLE, GRANT, and REVOKE. Update log contents are written as SQL statements in a form that can be used as input to the mysql program.
    Originally, the purpose of this log was to create a record to be used in conjunction with backups to restore tables after a crash. (You can restore a database from your backup files and then rerun any queries that modified the database subsequent to the backup by using the update logs as input to mysql.
    That way you can bring the tables to the state they were in at the time of the crash.) But as of MySQL 3.23.14, when the binary update log was introduced, the update log should be considered deprecated.
    . The binary update log and the binary log index file
    The binary update log contents are similar to the contents of the update log, but it's stored in a more efficient format and with additional information. It is used for recovery operations and for transmitting updates to replication slave servers. The binary logs are accompanied by an index file that lists which binary log files exist on the server.
    The default location for each of these log files is the data directory, although the server won't create any of them unless you ask for them. Each log can be enabled by specifying a startup option for mysqld. Other than the binary log, these logs are written in ASCII format and can be viewed directly. To see the contents of a binary log, use the mysqlbinlog utility.
    Another log file, the error log, is a special case that is handled somewhat differently
    Of all the logs, the general query log is most useful for monitoring the server, so when you first start using MySQL, I recommend that you enable the general log in addition to whatever other logs you want. After you have gained some experience with MySQL, you may want to turn off the general log to reduce your disk-space requirements.
    To enable logging, use the options shown in the following table. If the log filename is optional (as indicated by square brackets) and you don't provide one, the server uses a default name and writes the log file in the data directory. The default name for each of the log files is derived from the name of your server host, represented by HOSTNAME in the following discussion.
    If you specify a log name that is a relative pathname, the name is interpreted with respect to the data directory. A full pathname can be specified to place the log in some other directory. The server will create any log file that does not exist, but will not create the directory in which the file is to be written. If necessary, create the directory before starting the server.
    To enable logging, use the options shown in the following table. If the log filename is optional (as indicated by square brackets) and you don't provide one, the server uses a default name and writes the log file in the data directory. The default name for each of the log files is derived from the name of your server host, represented by HOSTNAME in the following discussion.
    If you specify a log name that is a relative pathname, the name is interpreted with respect to the data directory. A full pathname can be specified to place the log in some other directory. The server will create any log file that does not exist, but will not create the directory in which the file is to be written. If necessary, create the directory before starting the server.

    Logging Option

    Log Enabled by Option


    General log file


    Binary update log file


    Binary update log index file


    Update log file


    Slow-query log file


    ISAM/MyISAM log file


    Affects slow-query and update log format

    If the BDB or InnoDB table handlers are enabled, they create their own logs (by default, in the data directory). You cannot control whether or not the logs are generated, but you can specify where they are written by using the following options:

    Logging Option



    BDB log file directory


    InnoDB log archive directory


    InnoDB log file directory

    If you specify either of the InnoDB options, you should specify both, and you must give both the same value.
    The General Query Log
    This log contains a record of when clients connect to the server, each query that is sent to it by clients, and various other events that are not represented as queries (such as server startup and shutdown). If you enable the general log by specifying the --log option without a filename, the default name is HOSTNAME.log in the data directory.
    Queries are written to this log in the order that the server receives them. This may well be different than the order in which they finish executing, particularly for a mix of short and long queries.
    The Slow-Query Log
    The slow-query log provides a record of which queries took a long time to execute, where "long" is defined by the value of the long_query_time server variable in seconds. Slow queries also cause the server to increment its Slow_queries status counter. The slow-query log can be useful for identifying queries that you might be able to improve if you rewrite them.
    However, you'll need to take general load into account when interpreting the contents of this log. Query time is measured in real time (not CPU time), so if your server is bogged down, it's more likely that a query will be assessed as being slow, even if at some other time it runs under the limit.
    If you enable the slow-query log by specifying --log-slow-queries without a filename, the default name is HOSTNAME-slow.log in the data directory. If the --log-long-format option is given in conjunction with --log-slow-queries, MySQL also logs queries that execute without benefit of any index.
    The Update Log
    The update log is used to record statements that modify data, such as INSERT, DELETE, or UPDATE. SELECT statements are not written to this log. An UPDATE statement such as the following is not written to the update log, either, because it doesn't actually change any values:
    UPDATE t SET i = i;
    MySQL must execute a statement first to determine whether it modifies data, so queries are written to the update log when they finish executing rather than when they are received.
    Prior to MySQL 3.23.14 (when the binary update log was introduced), the update log can be used for database backup and recovery. However, the update log now is deprecated in favor of the binary log, which serves the same purposes and supports replication operations as well.
    Update logging is enabled with the --log-update option. The MySQL server names update log files using the following rules:
    If you enable the update log by specifying --log-update without a filename, the server generates a numbered series of log files in the data directory using your server's hostname as the file basename: HOSTNAME.001, HOSTNAME.002, and so forth. There are plans to change numbered log names to use six digits rather than three. This will help make log names sort better. Currently, they sort out of order when you cross the threshold from .999 to .1000. Using six digits will make out-of-order sorting much less likely.
    . If you specify a log name that contains no extension, the server uses that name rather than the hostname as the basename and generates a numbered series of log files. For example, if you specify --log-update=update, it generates update logs named update.001, update.002, and so forth.
    . If you enable update logging and specify a log name that contains an extension, the server always uses exactly that name for the log and does not generate a numbered series of log files.
    For update logs that are generated in numbered sequence, the server creates the next file in the series whenever it starts up or the logs are flushed.
    If the --log-long-format option is given in conjunction with --log-update, MySQL writes additional information to the log, indicating which user issued each statement and at what time.
    The Binary Update Log and the Binary Log Index File
    Like the update log, the binary update log is used for recording queries that modify data, but its contents are written in a more efficient binary format rather than in ASCII. The binary log also contains additional information, such as query execution timestamps. The binary nature of this log means that it is not directly viewable, but you can use the mysqlbinlog utility to produce readable binary log output.
    The binary update log can be used for database backup and recovery, and you must enable it if you want to set up a server as a master server that is replicated to a slave server.
    Queries are written to the binary update log in order of execution. That is, they're logged in the order they finish, not the order in which they are received, which is an important property for making replication work properly. Queries that are part of a transaction are cached until the transaction is committed, at which time all queries in the transaction are logged.
    If the transaction is rolled back, the transaction is not written to the binary log because it results in no changes to the database. (This is similar to the way individual queries are not written to the update log unless they actually change data; for the binary update log, the same principle applies, but extends across multiple statements in transactional context.)
    If you enable binary logging, the server also creates an accompanying binary log index file that lists the names of the existing binary log files.
    The default index filename is the same as the basename of the binary logs, with an .index extension. To specify a name explicitly, use the --log-bin-index option. If the name includes no extension, .index will be added to the name automatically. For example, if you specify --log-bin-index=binlog, the index filename becomes binlog.index.
    The Error Log
    The error log is used for recording diagnostic and error information. This log is handled differently on UNIX and Windows, as described in the following discussion.
    The Error Log on UNIX
    On UNIX, the error log is not created by the server, unlike the other logs, but rather by the mysqld_safe script that is used to start up the server.
    mysqld_safe creates the error log by redirecting the server's standard output and standard error output (the output streams known as stdout and stderr in the C programming language). The default error log name is HOSTNAME.err. You can specify a different error log name by passing an --err-log option to mysqld_safe on the command line or by including an err-log line in the [mysqld_safe] group of an option file. (Prior to MySQL 4, mysqld_safe is named safe_mysqld. The safe_mysqld script supports --err-log back to version 3.23.22 . Before that, safe_mysqld always writes the log using the default name, and there is no way to change it other than by editing the script.)
    If you specify a relative pathname for the error log, the name is interpreted with respect to the directory from which mysqld_safe is invoked. This is in contrast to the other log files, which are created by mysqld and for which relative pathnames are interpreted with respect to the data directory. Because you won't necessarily always invoke mysqld_safe from the same directory (for example, if you execute it manually on different occasions), it's best to specify an absolute pathname to ensure that the error log is always created in the same location.
    The error log is created if you start the server using the mysql.server script because mysql.server invokes mysqld_safe. However, mysql.server doesn't recognize --err-log on the command line or in its [mysql_server] option group, so if you want to give a specific error log name in this case, you must do so in the [mysqld_safe] group of an option file.
    If you start mysqld directly, error messages go to your terminal and there is no error log. You can redirect the output yourself to capture a record of diagnostic output. For example, to write error information to a file named /tmp/mysql.err, invoke the server like this for csh or tcsh:
    % mysqld >& /tmp/mysql.err &
    The Error Log on Windows
    On Windows, the server writes diagnostic information to the file mysql.err in the data directory by default. No alternative filename can be given. If you start the server with the --console option, it writes diagnostic output to the console window and does not create an error log. (The --console option has no effect if you run the server as a service because there is no console to write to in that case.)
    Log File Expiration
    One danger of enabling logging is that it has the potential to generate huge amounts of information, possibly filling up your disks. This is especially true if you have a busy server that processes lots of queries. To keep the last few logs available online while preventing log files from growing without bound, you can use log file expiration techniques. Some of the methods available for keeping logs manageable include the following:
    . Log rotation. This applies to logs that have a fixed filename, such as the general query log and the slow-query log.
    . Age-based expiration. This method removes log files that are older than a certain age. It can be applied to numbered log files that are created in numbered sequence, such as the update logs and the binary update logs.
    . Replication-related expiration. If you use the binary update log files for replication, it's better not to expire them based on age. Instead, you should consider a binary log file eligible for expiration only after its contents have been replicated to all slave servers. This form of expiration therefore is based on determining which binary logs are still in use.
    Log rotation is often used in conjunction with log flushing to make sure that any buffered log information has been written to disk. Logs can be flushed by executing a mysqladmin flush-logs command or by issuing a FLUSH LOGS statement.
    The rest of this section describes how to use these expiration techniques. For any that you put into practice, you should also consider how the log files fit into your database-backup methods. (It's a good idea to back up any log files that may be needed for recovery operations, so you don't want to expire such files before you've backed them up!) The example scripts discussed here can be found in the admin directory of the sampdb distribution.
    Rotating Fixed-Name Log Files
    The MySQL server writes some types of log information to files that have fixed names. This is true for the general query log and the slow-query log. It's also true for the update log if you're not logging updates to a numbered series of files. To expire fixed-name logs, use log rotation. This allows you to maintain the last few logs online, but limit the number to as many as you choose to prevent them from overrunning your disk.
    Log file rotation works as follows. Suppose the log file is named log. At the first rotation, you rename log to log.1 and tell the server to begin writing a new log file. At the second rotation, rename log.1 to log.2, log to log.1, and tell the server to begin writing another new log file.
    In this way, each file rotates through the names log.1, log.2, and so forth. When the file reaches a certain point in the rotation, you expire it by letting the previous file overwrite it. For example, if you rotate the logs daily and you want to keep a week's work of logs, you would keep log.1 through log.7. At each rotation, you would expire log.7 by letting log.6 overwrite it to become the new log.7.
    The frequency of log rotation and the number of old logs you keep will depend on how busy your server is (active servers generate more log information) and how much disk space you're willing to devote to old logs.
    The following shell script can be used to perform rotation of fixed-name log files:
    #! /bin/sh
    # - rotate MySQL log file that has a fixed name
    # Argument 1: log file name
    if [ $# -ne 1 ]; then
        echo "Usage: $0 logname" 1>&2
        exit 1
    mv $logfile.6 $logfile.7
    mv $logfile.5 $logfile.6
    mv $logfile.4 $logfile.5
    mv $logfile.3 $logfile.4
    mv $logfile.2 $logfile.3
    mv $logfile.1 $logfile.2
    mv $logfile $logfile.1
    mysqladmin flush-logs
    The script takes the log file name as its argument. You can either specify the full pathname of the file or change directory into the log directory and specify the file's name in that directory. For example, to rotate a log named log in /usr/mysql/data, you can use the following command:
    % /usr/mysql/data/log
    On Windows, log rotation doesn't work quite the same way as on UNIX. If you attempt to rename a log file while the server has it open, a "file in use" error occurs. To rotate the logs, shut down the server first and then rename the files and restart the server. I'll leave it to you to stop and restart the server as you want, but the log file renaming can be performed using the following batch script:
    @echo off
    REM rotate_fixed_logs.bat - rotate MySQL log file that has a fixed name
    if not "%1" == "" goto ROTATE
        @echo Usage: rotate_fixed_logs logname
        goto DONE
    set logfile=%1
    erase %logfile%.7
    rename %logfile%.6 %logfile%.7
    rename %logfile%.5 %logfile%.6
    rename %logfile%.4 %logfile%.5
    rename %logfile%.3 %logfile%.4
    rename %logfile%.2 %logfile%.3
    rename %logfile%.1 %logfile%.2
    rename %logfile% %logfile%.1
    rotate_fixed_logs.bat is invoked much like the shell script, with a single argument that names the log file to be rotated. For example, like this:
    C:\> rotate_fixed_logs C:\mysql\data\log
    Expiring Replication-Related Log Files
    The server generates binary update logs in numbered sequence. One way to manage them is to expire them based on age, as described in the previous section. However, if you're using the binary logs for replication, age is not necessarily an indicator of whether a log can be removed. Instead, you should expire a binary log only after its contents have been replicated to all the slave servers.
    Unfortunately, the master server itself doesn't know how many slaves there are or which files have been propagated to them. The master won't purge binary logs that have not yet been sent to connected slaves, but there is no guarantee that a given slave is connected at any particular time.
    This means that you yourself must know which servers are acting as slaves and then connect to each one and issue a SHOW SLAVE STATUS statement to determine which of the master's binary log files the slave currently is processing. (The file's name is the value in the Master_Log_File column.) Any binary log that is no longer used by any of the slaves can be removed. Suppose you have the following scenario:
    . The local server is the master and it has two slaves, S1 and S2.
    . The binary log files that exist on the master have names from binlog.038 through binlog.042.
    . SHOW SLAVE STATUS produces the following result on S1:
    . mysql> SHOW SLAVE STATUS\G
    . ...
    . Master_Log_File: binlog.41
    . ...
    And this result on S2:
    Master_Log_File: binlog.40
    In this case, the lowest-numbered binary log still required by the slaves is binlog.40, so any log with a lower number can be removed. To do that, connect to the master server and issue the following statement:
    mysql> PURGE MASTER LOGS TO 'binlog.040';
    That causes the server to delete all binary logs with numbers lower than the named file, which for the situation just described, includes binlog.038 and binlog.039. SHOW SLAVE STATUS is available as of MySQL 3.23.22, and PURGE MASTER LOGS is available as of MySQL 3.23.28. Both statements require the SUPER privilege (PROCESS prior to MySQL 4.0.2).
    Importing & Exporting Text Files
    Sometimes, the contents of a table should be written as efficiently as possible into a text file or read from such a file. MySQL offers several ways of doing this:
    . The SQL command LOAD DATA reads in a text file and transfers the contents into a table.
    . With mysqlimport there is a command available that is equivalent to LOAD DATA.Itis especially well suited for automating the importation of a script file.
    . The SQL command SELECT ... INTO OUTFILE writes the result of a query into a text file.
    . If you wish to automate exportation with a script, then the command mysqldump is to be recommended. Its functionality is similar to that of SELECT ... INTO OUTFILE.
    . In many cases you can use the universal tool mysql for implementing text, HTML, or XML exportation.
    If none of the above commands suits your needs, then you will have to write your own script to assist you in importing or exporting. The programming language Perl was made for such tasks.
    Special Characters in the Imported or Exported File
    A common feature of LOAD DATA, SELECT ... INTO OUTFILE, mysqlimport, and mysqldump is the set of options for handling special characters in a text file. There are four options for this purpose, which as SQL commands look like this:
    FIELDS TERMINATED BY 'fieldtermstring'
    ENCLOSED BY 'enclosechar'
    ESCAPED BY 'escchar'
    LINES TERMINATED BY 'linetermstring'
    . fieldtermstring specifies the character string that separates the columns within the row (for example, a tab character).
    . enclosechar specifies a character that is permitted to appear in the text file before and after individual entries (usually a single or double quote character for character strings). If an entry begins with this character, then that character is deleted at the beginning and end. (The end of a column is recognized by fieldtermstring.)
    . escchar specifies the escape character that is to be used to indicate special characters (the default is the backslash). This is necessary if special characters appear in character strings of the text file that are also used for separating rows or columns. Moreover, MySQL expects code 0 in the form \0 (where the backslash is to be replaced by escchar).
    . linetermstring specifies the character string with which a row is terminated. With DOS/Windows text files the character string '\r\n' must be used.
    Working with Character Strings, Numbers, Dates, and BLOBs
    For all the commands introduced in this section there is a data format that must be followed exactly. In particular, for importation you must hold to the format expected by MySQL. For exportation you have somewhat more leeway, in that you can use SQL functions for formatting data in the SELECT command (such as DATE_FORMAT for formatting dates and times).
    Moreover, there are four options that you can use to determine how rows and columns should be separated and how character strings and special characters should be indicated.
    . Numbers: For very large and very small numbers in the FLOAT and SINGLE formats one has the use of scientific notation in the form -2.3e-037.
    . Character Strings: Strings are not changed in importation and exportation (ANSI format, one byte per character). Special characters contained in the character string are marked by default with the backslash in order to distinguish these from the characters used for separation (e.g., tab, carriage return, linefeed).
    . BLOBs: Binary objects are treated byte for byte like character strings. Neither in importing nor exporting is there the possibility of using hexadecimal character strings (0x123412341234 ... ).
    . Date and Time: Dates are treated as character strings of the form 2003-12-31, and times as character strings of the form 23:59:59. Timestamp values are considered integers of the form 20031231235959.
    . NULL: The treatment of NULL is problematic. The following text assumes that the backslash is used as the escape character for special characters and the double quote character for indicating a character string. If you use other characters (options FIELDS ESCAPED BY '?' ENCLOSED BY '?'), then you will have to reconfigure the following paragraphs.
    In exporting with escape characters, NULL is represented by \N. In exporting without escape characters NULL is simply represented by the four characters NULL. However, NULL or \N is placed between double quote characters (though not if they are in a text or BLOB field) and can therefore be distinguished from character strings.
    In importing with escape characters, MySQL accepts NULL, \N, and "\N" as NULL. However, "NULL" is interpreted as a character string (consisting of the four characters NULL).
    Importing with LOAD DATA INFILE
    The syntax of LOAD DATA is as follows:
    LOAD DATA [loadoptions] INFILE 'filename.txt' [duplicateoptions]
    INTO TABLE tablename [importoptions] [IGNORE ignorenr LINES]
    The result is that the file filename.txt is imported into the table tablename. There are various options
    Example 1
    The table SAMPLE consists of THREE columns:
    AUTO_INCREMENT column (S_NUMBER) and dob.
    The column name refers to its data type. The Windows text file data.txt is to be imported into this table. (Here --> represents a tab character):
    "Ravish" 1985-09-26
    "Aman" 1984-07-07
    "Vishwajit" 1986-07-13
    "Sanjeev" 1984-07-15
    "Ravish Kumar Tiwari" 1984-09-29
    The import command looks like this:
    USE ravish
    LOAD DATA INFILE 'c:\data.txt'
    INTO TABLE sample
          ENCLOSED BY '\"'
      Query OK, 3 rows affected (0.00 sec)
      Records: 3 Deleted: 0 Skipped: 0 Warnings: 2
    A SELECT command demonstrates that the importation was only partially successful. In both the first and third lines, the date has been incorrectly interpreted. Moreover, in the third line the decimal number with the German comma for a decimal point has caused problems; namely, the part to the right of the decimal point has gone missing. In sum, take care to obey the MySQL formatting rules to the letter when preparing a file for importation:
    CSV Import
    Sometimes, one wishes to import data into MySQL from a spreadsheet program like Excel. Such programs generally offer the possibility to store tables in CSV (comma-separated values) format. In principle, the importation of such files proceeds effortlessly. For files that were created with Excel under Windows, suitable import options look like this:
    In practice, importation usually runs into trouble with the formatting of dates (usually in the form 12/31/2003, about which MySQL is clueless). In particular, with Microsoft software, the automatic country-specific formatting of numbers can cause problems when, for example, Excel suddenly represents a decimal number with a comma instead of a period for the decimal point. In such cases one can get help from a special import script for MySQL (or else you can program your own export filters for Excel).
    Error Search
    As the previous examples have shown, importing text is often problematic. Unfortunately, LOAD DATA returns in MySQL 4.0 merely a brief status output, which gives, among other things, the number of warnings:
    Query OK, 15 rows affected (0.00 sec)
    Records: 10 Deleted: 5 Skipped: 0 Warnings: 2
    This result means that ten records were read in. Five existing records were replaced by new records (thus 15 rows affected). There were problems with two records (rows) detected by LOAD DATA (2 warnings). The nature of the problems, and which rows of the file were affected, remains a mystery.
    Exporting with SELECT ... INTO OUTFILE
    With the command SELECT ... INTO OUTFILE we are dealing with a garden-variety SELECT, where before the FROM part, INTO OUTFILE is used to specify a file name and several possible options:
    SELECT [selectoptions] columnlist
    INTO OUTFILE 'filename.txt' exportoptions
    FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
    The data types of the columns are taken from the column names. The following lines show the result of an OUTFILE exportation without special options. The tab character is again indicated by a hooked arrow. The resulting file has two lines, which for reasons of space are here distributed over five lines:
    select * into outfile 'c:\data1.txt' from sample;
    Exporting with mysqldump
    As an alternative to SELECT ... INTO OUTFILE there is the auxiliary program mysqldump. This program is actually primarily a backup .
    The program mysqldump always stores entire tables (and not the result of a particular SELECT query). A further difference is that mysqldump normally does not return a text file with the raw data, but entire INSERT commands. The resulting file can then later be read in with mysql. To use mysqldump for text exportation you must specify the option --tab:
    mysqldump --tab=verz [options] databasename tablename
    With --tab a directory is specified. In this directory mysqldump stores two files for each table: tablename.txt and tablename.sql. The *.txt file contains the same data as after SELECT ... INTO OUTFILE. The *.sql file contains a CREATE TABLE command, which allows the table to be re-created.
    As with mysqlimport, the representation of special characters can be controlled with four options: --fields-terminated-by, --fields-enclosed-by, --fields-escaped-by, and --lines-terminated-by. These options are analogous to the SQL options described at the beginning of this section. They should be set in quotation marks (for example, "--fields-enclosed-by=+"):
    C:\> mysqldump -u root -p --tab=c:\tmp "--fields-enclosed-by=\"" ravish sample
    Enter password: sa
    XML Exporting with mysqldump
    If you execute mysqldump with the option --xml, you obtain as result an XML file (where, however, characters outside the 7-bit ASCII character set are not represented by Unicode, but by the latin1 character set):
    C:\> mysqldump -u root -p --xml ravish sample > C:\_temp\samle.xml Enter password: sa
    Given below is mysql dump file

    <?xml version="1.0"?>
    <mysqldump xmlns:xsi="">
    <database name="ravish">
            <table_structure name="sample">
                    <field Field="s_number" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
                    <field Field="name" Type="char(30)" Null="YES" Key="" Extra="" />
                    <field Field="dob" Type="date" Null="YES" Key="" Extra="" />
                    <key Table="sample" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="s_number" Collation="A" Cardinality="5" Null="" Index_type="BTREE" Comment="" />
                    <options Name="sample" Engine="InnoDB" Version="10" Row_format="Compact" Rows="5" Avg_row_length="3276" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Auto_increment="6" Create_time="2007-05-12 11:37:32" Collation="latin1_swedish_ci" Create_options="" Comment="InnoDB free: 4096 kB" />
            <table_data name="sample">
                    <field name="s_number">1</field>
                    <field name="name">Ravish</field>
                    <field name="dob">1985-09-26</field>
                    <field name="s_number">2</field>
                    <field name="name">Aman</field>
                    <field name="dob">1984-07-07</field>
                    <field name="s_number">3</field>
                    <field name="name">Vishwajit</field>
                    <field name="dob">1986-07-13</field>
                    <field name="s_number">4</field>
                    <field name="name">Sanjeev</field>
                    <field name="dob">1984-07-15</field>
                    <field name="s_number">5</field>
                    <field name="name">Ravish Kumar Tiwari</field>
                    <field name="dob">1984-09-29</field>

    Multiple Servers
    Most people run a single MySQL server on a given machine, but there are circumstances under which it can be useful to run multiple servers:
    . You may want to test a new version of the server while leaving your production server running. In this case, you'll be running different server binaries.
    . Operating systems typically impose per-process limitations on the number of open file descriptors. If your system makes it difficult to raise the limit, running multiple instances of the server binary is one way to work around that limitation.
    . Internet service providers often provide individual customers with their own MySQL installation, which necessarily requires multiple servers. This may involve running multiple instances of the same binary if all customers run the same version of MySQL, or different binaries if some customers run different versions than others.
    Those are some of the more common reasons to run multiple servers, but there are others. For example, if you write MySQL documentation, it's often necessary to test various server versions empirically to see how their behavior differs.
    I fall into this category, for which reason I have lots of servers installed (more than 30 at the moment). However, I run just a couple of them all the time. The others I run only on occasion for testing purposes, so I need to be able to start and stop them easily on demand.
    General Multiple Server Issues
    Running several servers is more complicated than running just one because you need to keep them from interfering with each other. Some of the issues that arise occur when you install MySQL. If you use different versions, they must each be placed into a different location. For precompiled binary distributions, you can accomplish this by unpacking them into different directories. ;
    For source distributions that you compile yourself, you can use the --prefix option for configure to specify a different installation location for each distribution. Other issues occur at runtime when you start up the servers. Each server process must have unique values for several parameters. For example, every server must listen to a different TCP/IP port for incoming connections or else they will collide with each other.
    This is true whether you run different server binaries or multiple instances of the same binary. A similar problem occurs if you enable logging. Each server should write to its own set of log files because having different servers write to the same files is sure to cause problems.
    You can specify a server's options at runtime when you start it, typically in an option file. Alternatively, if you run several server binaries that you compile from source yourself, you can specify during the build process a different set of parameter values for each server to use. These become its built-in defaults, and you need not specify them explicitly at runtime.
    When you run multiple servers, be sure to keep good notes on the parameters you're using so that you don't lose track of what's happening. One way to do this is to use option files to specify the parameters. (This can be useful even for servers that have unique parameter values compiled in because the option files serve as a form of explicit documentation.)
    The following discussion enumerates several types of options that have the potential for causing conflicts if they're not set on a per-server basis. Note that some options will influence others, and thus you may not need to set each one explicitly for every server. For example, every server must use a unique process ID file when it runs. But the data directory is the default location for the PID file, so if each server has a different data directory, that will implicitly result in different default PID files.
    If you're running different server versions, it's typical for each distribution to be installed under a different base directory. It's also best if each server uses a separate data directory. To specify these values explicitly, use the following options:




    Pathname to root directory of MySQL installation


    Pathname to data directory

    In many cases, the data directory will be a subdirectory of the base directory, but not always. For example, an ISP may provide a common MySQL installation for its customers (that is, the same set of client and server binaries) but run different instances of the server, each of which manages a given customer's data directory. In this case, the base directory may be the same for all servers, but individual data directories may be located elsewhere, perhaps under customer home directories.
    . The following options must have different values for each server, to keep servers from stepping on each other:




    Port number for TCP/IP 410


    Pathname to UNIX domain socket file


    Pathname to file in which server writes its process ID

    . If you enable logging, any log names that you use must be different for each server. Otherwise, you'll have multiple servers contending to log to the same files. That is at best confusing, and at worst it prevents things like replication from working correctly.
    Log files named by the options in the following table are created under the server's data directory if you specify relative filenames. If each server uses a different data directory, you need not specify absolute pathnames to get each one to log to a distinct set of files

    Logging Option

    Log Enabled by Option


    General log file


    Binary update log file


    Binary update log index file


    Update log file


    Slow-query log file


    ISAM/MyISAM log file

    . If the BDB or InnoDB table handlers are enabled, the directories in which they write their logs must be unique per server. By default, the server writes these logs in the data directory. To change the location, use the following options:

    Logging Option



    BDB log file directory


    InnoDB log archive directory


    InnoDB log file directory

    . If you specify either of the InnoDB options, you should specify both, and you must give both the same value.
    . Under UNIX, if you use mysql_safe to start your servers, it creates an error log (by default in the data directory). You can specify the error log name explicitly with --err-log=file_name. Note that this option must be given to mysqld_safe rather than to mysqld, and that relative pathnames are interpreted with respect to the directory from which mysqld_safe is invoked, not with respect to the data directory as for the other log files. If you use this option, specify an absolute pathname to make sure you always create the error log in the same location.
    . Under UNIX, it may also be necessary to specify a --user option on a per-server basis to indicate the login account to use for running each server. This is very likely if you're providing individual MySQL server instances for different users, each of whom "owns" a separate data directory.
    . Under Windows, different servers that are installed as services must each use a unique service name.
    Configuring and Compiling Different Servers
    If you're going to build different versions of the server, you should install them in different locations. The easiest way to keep different distributions separate is to indicate a different installation base directory for each one by using the --prefix option when you run configure. If you incorporate the version number into the base directory name, it's easy to tell which directory corresponds to which version of MySQL. This section illustrates one way to accomplish that, by describing the particular configuration conventions that I use to keep my own MySQL installations separate.
    The layout I use places all MySQL installations under a common directory: /var/mysql. To install a given distribution, I put it in a subdirectory of /var/mysql named using the distribution's version number. For example, I use /var/mysql/40005 as the installation base directory for MySQL 4.0.5, which can be accomplished by running configure with a --prefix=/var/mysql/40005 option. I also use other options for additional server-specific values, such as the TCP/IP port number and socket pathname. The configuration I use makes the TCP/IP port number equal to the version number, puts the socket file directly in the base directory, and names the data directory as data there.
    To set up these configuration options, I use a shell script named config-ver that looks like the following (note that the data directory option for configure is --localstatedir, not --datadir):
    # InnoDB is included by default as of MySQL 4:
    # - prior to 4.x, include InnoDB with --with-innodb
    # - from 4.x on, exclude InnoDB with --without-innodb
    OTHER="--enable-local-infile --with-embedded-server"
    rm -f config.cache
    ./configure \
        --prefix=$PREFIX \
        --localstatedir=$PREFIX/data \
        --with-unix-socket-path=$PREFIX/mysql.sock \
        --with-tcp-port=$VERSION \
    I make sure the first line is set to the proper version number and modify the other values as necessary, according to whether or not I want the InnoDB and BDB table handlers, LOCAL support for LOAD DATA, and so forth. That done, the following commands configure, build, and install the distribution:
    % sh config-ver
    % make
    % make install
    Next, I change location into the installation base directory and initialize its data directory and grant tables:
    % cd /var/mysql/40005
    % ./bin/mysql_install_db
    Strategies for Specifying Startup Options
    After you have your servers installed, how do you get them started up with the proper set of runtime options that each one needs? You have several choices:
    . If you run different servers that you build yourself, you can compile in a different set of defaults for each one, and no options need to be given at runtime. This has the disadvantage that it's not necessarily obvious what parameters any given server is using.
    . To specify options at runtime, you can list them on the command line or in option files. If you need to specify lots of options, writing them on the command line is likely to be impractical. Putting them in option files is more convenient, although then the trick is to get each server to read the proper set of options. Strategies for accomplishing this include the following:
    o Use a --defaults-file option to specify the file that the server should read to find all of its options, and specify a different file for each server. This way, you can put all the options needed by a given server into one file to fully specify its setup in a single place. (Note that when you use this option, none of the usual option files, such as /etc/my.cnf, will be read.)
    o Put any options that are common to all servers in a global option file such as /etc/my.cnf and use a --defaults-extra-file option on the command line to specify a file that contains additional options that are specific to a given server. For example, use the [mysqld] group in /etc/my.cnf for options that should apply to all servers. These need not be replicated in individual per-server option files.
    Be sure that any options placed into a common option group are understood by all servers that you run. For example, you can't use local-infile to enable the use of LOAD DATA LOCAL if any of your servers are older than version 3.23.49 because that is when that option was introduced. Its presence in a common option group will cause startup failure for older servers.
    o Servers look for an option file named my.cnf in the compiled-in data directory location. If each server has a different data directory pathname compiled in, you can use these my.cnf files to list options specific to the corresponding servers. In other words, use /etc/my.cnf for any common settings that you want all servers to use, and use DATADIR/my.cnf for server-specific settings where DATADIR varies per server. (Note that this strategy does not work if you need to specify the data directory location at runtime. Nor will it work if you're running multiple instances of a given server binary.)
    o Use the mysqld_multi script to manage startup for multiple servers. This script allows you to list the options for all servers in a single file, but associate each server with its own particular option group in the file.
    . Under Windows, you can run multiple services, using special option file group-naming conventions specific to this style of server setup.



    Post a Comment

    Circle Me On Google Plus


    Follow Us