Mysql Server

Aug 04

Mysql Optimizing, Analyzing, Checking, and Repairing Tables

A regular part of a database administrator’s job is to do preventative maintenance, as well as to repair things when they go wrong. In spite of the best efforts, data errors can occur, such as in the case of a power failure that interrupts a write. Usually you can correct these fairly painlessly.

There are four main tasks involved in checking and repairing:

Optimizing tables
Analyzing tables (analyzes and stores the key distribution for MyISAM and BDB tables)
Checking tables (checks the tables for errors, and, for MyISAM tables, updates the key statistics)
Repairing tables (repairs corrupted MyISAM tables)

Optimizing Tables

Tables that contain BLOB and VARCHAR fields will, over time, become less optimized. Because these field types are variable in length, when records are updated, inserted, or deleted, they will not always take the same amount of space, the records will start to become fragmented, and empty spaces will remain. Just like with a fragmented disk, this situation will slow performance, so to keep MySQL in tiptop shape, you should regularly defragment it. The way to do this is to optimize the table, which can be done in a number of ways. There’s the OPTIMIZE TABLE statement, the mysqlcheck utility (if the server is running), or the myisamchk utility (if the server is not running or there is no interaction with the table).

Optimizing currently works only with MyISAM and partially with BDB tables. With MyISAM tables, optimizing does the following:

Defragments tables where rows are split or have been deleted
Sorts the indexes if they have not been already
Updates the index statistics if they have not been already

With BDB tables, optimizing analyzes the key distribution (the same as ANALYZE TABLE; see the "Analyzing Tables with ANALYZE TABLE" section later in this chapter).

Optimizing Tables with the OPTIMIZE Statement

The OPTIMIZE statement is a SQL statement used when connected to a MySQL database. The syntax is as follows:

OPTIMIZE TABLE tablename

You can also optimize many tables at once, separating each with a comma:

mysql> OPTIMIZE TABLE customer,sales;
+------------------+----------+----------+-----------------------------+
| Table            | Op       | Msg_type | Msg_text                    |
+------------------+----------+----------+-----------------------------+
| firstdb.customer | optimize | status   | Table is already up to date |
| firstdb.sales    | optimize | status   | OK                          |
+------------------+----------+----------+-----------------------------+
2 rows in set (0.02 sec)

The customer table in this instance has already been optimized.

Optimizing Tables with mysqlcheck

mysqlcheck is a command-line utility that can perform numerous checking and repairing tasks besides optimization. A full description of all the mysqlcheck features follows later in the chapter in the section titled "Using mysqlcheck." The server must be running for you to use mysqlcheck. To optimize the customer table from the firstdb database, use the -o mysqlcheck option, as follows:

% mysqlcheck -o firstdb customer  -uroot -pg00r002b
firstdb.customer           Table is already up to date

mysqlcheck allows you to optimize more than one table at a time by listing all the tables after the database name:

% mysqlcheck -o firstdb customer sales  -uroot -pg00r002b
firstdb.customer           Table is already up to date
firstdb.sales              Table is already up to date

You could also optimize the entire database by leaving out any table references, with the following:

% mysqlcheck -o firstdb -uroot -pg00r002b

Optimizing Tables with myisamchk

Finally, you can use the myisamchk command-line utility when the server is down or not interacting with the server. (Flush the tables before running this statement if the server is up with mysqladmin flush-tables. You still need to make sure the server is not interacting with the table, though, or else corruption may result.) This is the oldest way of checking tables. You have to run myisamchk from the exact location of the table or specify the path leading to the table. A full description of all the myisamchk features follows later in this chapter in the section titled "Using myisamchk."

The equivalent of an optimize statement is as follows:

myisamchk --quick --check-only-changed --sort-index --analyze tablename

or as follows:

myisamchk -q -C -S -a tablename

For example:

% myisamchk --quick --check-only-changed --sort-index --analyze customer
- check key delete-chain
- check record delete-chain
- Sorting index for MyISAM-table 'customer'

The -r option repairs the table, but also eliminates wasted space:

% myisamchk -r sales   
- recovering (with sort) MyISAM-table 'sales'
Data records: 8
- Fixing index 1
- Fixing index 2

If you do not specify the path to the table index file, and you’re not in the right directory, you’ll get the following error:

% myisamchk -r customer
myisamchk: error: File 'customer' doesn't exist

Specifying the full path to the .MYI file corrects this:

% myisamchk -r /usr/local/mysql/data/firstdb/customer
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/firstdb/customer'
Data records: 0

Warning

Tables are locked during the optimization, so don’t run this during peak hours! Also, make sure you have a reasonable amount of free space on the system when running OPTIMIZE TABLE. If you try to run it when your system has almost or already run out of disk space, MySQL may not be able to complete the optimization, leaving your table unusable.

Optimizing is an important part of any administrative routine for databases that contain MyISAM tables and should be performed regularly.

Analyzing Tables

Analyzing tables improves performance by updating the index information for a table so that MySQL can make a better decision on how to join tables. The distribution of the various index elements is stored for later usage. (Analyzing currently only works with MyISAM and BDB tables.)

There are three ways to analyze a table:

When connected to MySQL, with the ANALYZE TABLE statement
With the mysqlcheck command-line utility
With the myisamcheck command-line utility

Regular analysis of tables can help performance and should be a regular part of any maintenance routine.

Analyzing Tables with ANALYZE TABLE

ANALYZE TABLE is a statement used when connected to a database on the server. The syntax is as follows:

ANALYZE TABLE tablename

For example:

mysql> ANALYZE TABLE sales;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| firstdb.sales | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)

The Msg_type (message type) is one of status, error, info, or warning. Here’s what would happen if the index file was missing altogether and you tried to analyze the table:

mysql> ANALYZE TABLE zz;
+------------+---------+----------+----------------------------------+
| Table      | Op      | Msg_type | Msg_text                         |
+------------+---------+----------+----------------------------------+
| firstdb.zz | analyze | error    | Table 'firstdb.zz' doesn't exist |
+------------+---------+----------+----------------------------------+
1 row in set (0.00 sec)

The table will only be analyzed again if it has changed since the last time it was analyzed:

mysql> ANALYZE TABLE sales;
+---------------+---------+----------+-----------------------------+
| Table         | Op      | Msg_type | Msg_text                    |
+---------------+---------+----------+-----------------------------+
| firstdb.sales | analyze | status   | Table is already up to date |
+---------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

Analyzing Tables with mysqlcheck

The mysqlcheck command-line utility is discussed fully later in this chapter in the section titled "Using mysqlcheck." The server needs to be running for you to use mysqlcheck, and it works only with MyISAM tables. To use it to analyze tables, you use the -a option:

% mysqlcheck -a firstdb sales -uroot -pg00r002b
firstdb.sales                                      OK

You can also analyze more than one table from a database by listing the tables after the database name:

% mysqlcheck -a firstdb sales customer -uroot -pg00r002b
firstdb.sales                 Table is already up to date
firstdb.customer              Table is already up to date

If you tried to analyze a table that does not support analysis (such as an InnoDB table), no harm would be done and the operation would just fail. For example:

% mysqlcheck -a firstdb innotest -uroot -pg00r002b
firstdb.innotest
error    : The handler for the table doesn't support check/repair

You could also analyze all tables in the database with by leaving out any table names:

% mysqlcheck -a firstdb innotest -uroot -pg00r002b

Analyzing Tables with myisamchk

The myisamchk command-line utility is discussed fully later in this chapter in the section titled "Using myisamchk." The server should either not be running, or you must be sure that there is no interaction with the tables with which you’re working. If the –skip-external-locking option is not on, you can safely use myisamchk to analyze tables, even if the server is running. The tables will be locked, affecting access, but there will be no erroneous reports. If –skip-external-locking is used, you’ll need to flush the tables before starting the analysis (with mysqladmin flush-tables) and ensure that there is no access. You may get invalid results if mysqld or anything else accesses the table while myisamchk is running. To analyze tables, use the -a option:

% myisamchk -a /usr/local/mysql/data/firstdb/sales   
Checking MyISAM file: /usr/local/mysql/data/firstdb/sales
Data records:       9   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2

Checking Tables

Errors can occur when the indexes are not synchronized with the data. System crashes or power failures can all cause situations where the tables have become corrupted. Corruption of the data is fairly rare; in most cases, the corruption is of the index files. These can be hard to spot, though you may notice information being returned slowly or data not being found that should be there. Checking tables should be the first thing you do when you suspect an error. Some of the symptoms of corrupted tables include errors such as the following:

Unexpected end of file.
Record file is crashed.
tablename.frm is locked against change.
Can’t find file tablename.MYI (Errcode: ###).
Got error ### from table handler. The perror utility gives more information about the error number. Just run perror (which is stored in the same directory as the other binaries such as mysqladmin) and the error number. For example:
```
% perror 126
126 = Index file is crashed / Wrong file format
```

Some of the other more common errors include:

126 = Index file is crashed / Wrong file format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on write or update
144 = Table is crashed and last repair failed
145 = Table was marked as crashed and should be repaired

Once connected to the MySQL server, you can issue a CHECK TABLE command, make use of the mysqlcheck utility (when the server is running), or use the myisamchk utility when the server has been stopped. Checking updates the index statistics and checks for errors.

If any errors are found, the table will need to be repaired (see the "Repairing Tables" section later in this chapter). Serious errors mark the table as corrupt, in which case it can no longer be used until it is repaired.

Tip

Always check tables after a power failure or a system crash. You can usually fix any corruption that has occurred before users notice any problems.

Checking Tables with CHECK TABLES

The syntax for CHECK TABLE is as follows:

CHECK TABLE tablename [option]

For example:

mysql> CHECK TABLE customer;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| firstdb.customer | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (0.01 sec)

CHECK can check only MyISAM and InnoDB tables.

There are five options depending on the level of checking you want to do, as shown in Table 4

Table 4: CHECK TABLE Options

Option	Description
QUICK	This is the quickest check and does not scan the rows to check for wrong links.
FAST	Only checks tables that haven’t been closed properly.
CHANGED	Only checks tables that haven’t been closed properly or have been changed since last check.
MEDIUM	The default option. It scans rows to check that deleted links are correct. It also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED	This is the slowest method, but it checks the table for complete consistency by doing a full key lookup for every index associated with each row.

The QUICK option is useful for checking tables where you don’t suspect any errors.

If an error or warning is returned, you should try and repair the table.

You can check more than one table at a time by listing the tables one after another,

for example:

mysql> CHECK TABLE sales,customer;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| firstdb.sales    | check | status   | OK       |
| firstdb.customer | check | status   | OK       |
+------------------+-------+----------+----------+
2 rows in set (0.01 sec)

Checking Tables with mysqlcheck

The mysqlcheck command-line utility can be used when the server is running and works only with MyISAM tables. It is described fully later in this chapter in the section titled "Using mysqlcheck." Table 5 lists the options.

The syntax is as follows:

mysqlcheck [options] databasename tablename[s]

For example:

% mysqlcheck -c firstdb customer -uroot -pg00r002b
firstdb.customer                               OK

Table 5: The mysqlcheck Options That Apply to Table Checking

Option	Description
–auto-repair	Used in conjunction with one of the check options, it will automatically beginto repair corrupted tables after the checks have completed.
-c, –check	Checks tables.
-C, –check-only-changed	Checks tables that have changed since the last check or were not closed properly.
-F, –fast	Checks tables that haven’t been closed properly.
-e, –extended	This is the slowest form for checking, but it will make sure the table is completely consistent. You can also use this option to repair, though itisusually not necessary.
-m, –medium-check	This is much faster than the extended check, and it finds the vast majority oferrors.
-q, –quick	The fastest check, this does not check table rows when checking. When repairing, it only repairs the index tree.

You can check more than one table by listing a number of tables after the database name:

% mysqlcheck -c firstdb sales customer -uroot -pg00r002b
firstdb.sales                                      OK
firstdb.customer                                   OK

You can check all tables in the database by just specifying the name of the database.

% mysqlcheck -c firstdb -uroot -pg00r002b

Checking Tables with myisamchk

When the server is shut down or there is no interaction with the tables you’re checking, you can use the myisamchk command-line option (described fully in the "Using myisamchk" section later in this chapter). If the –skip-external-locking option is not on, you can safely use myisamchk to check tables, even if the server is running. The tables will be locked, affecting access, but there will be no erroneous reports. If –skip-external-locking is used, you’ll need to flush the tables before starting the check (with mysqladmin flush-tables) and ensure that there is no access. You may get wrong results (with tables being marked as corrupted even when they are not) if mysqld or anything else accesses the table while myisamchk is running.

The syntax is as follows:

mysiamchk [options] tablename

The equivalent to the CHECK TABLE statement is the medium option:

myisamchk -m table_name

The default for myisamchk is the ordinary check option (-c). There is also the fast check (-F), which only checks tables that haven’t been closed properly. This is not the same as the lowercase -f option, which is the force option, meaning the check continues even if errors occur. There is also the medium check (-m), slightly slower and more complete. The most extreme option is the -e option (that performs an extended check), which is the most thorough and slowest option. It’s also usually a sign of desperation; use this only when all other options have failed. Increasing the key_buffer_size variable can speed up the extended check (if you have enough memory). See Table 6 for the checking options.

Table 6: myisamchk Checking Options

Option	Description
-c, –check	Ordinary check and the default option.
-e, –extend-check	Slowest and most thorough form of check. If you are using –extended-check and have much memory, you should increase the value of key_buffer_size a lot!
-F, –fast	Fast check, which only checks tables that haven’t been closed properly.
-C, –check-only-changed	Checks only the tables that have been changed since the last check.
-f, –force	This runs the repair option if any errors are found in the table.
-i, –information	Displays statistics about the table that is checked.
-m, –medium-check	Medium check, faster than an extended check, and good enough for most cases.
-U, –update-state	Keeps information about when the table was checked and whether the table has crashed, which is useful for the -C option. Should not be used when the table is being used and the –skip-external-locking option is active.
-T, –read-only	Does not mark the table as checked (useful for running myisamchk when the server is active and the –skip-external-locking option is in use).

The following is a sample myisamchk output when errors are found:

% myisamchk largetable.MYI
Checking MyISAM file: Hits.MYI
Data records: 2960032   Deleted blocks:       0
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
- check file-size
myisamchk: warning: Size of datafile is: 469968400 Should be: 469909252
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
myisamchk: error: Found 2959989 keys of 2960032
- check record links
myisamchk: error: Record-count is not ok; is 2960394 Should be: 2960032
myisamchk: warning: Found    2960394 parts   Should be: 2960032 parts

Repairing Tables

If you have checked the tables and errors have been found, you’ll need to repair them. There are various repair options available, depending on which method you use, but you may not have success. If the disk has failed, or if none of them work, the only option is to restore from your backup. Repairing a table can take up significant resources, both disk and memory:

Generally, repairing a table takes up twice as much disk space as the original data file (on the same disk). A quick repair (see the options in the following sections) is an exception because the data file is not modified.
Some space for the new index file (on the same disk as the original). The old index is deleted at the start, so this is usually not significant, but it will be if the disk is close to full.
With the standard and –sort-recover options, a sort buffer is created. This takes up the following amount of space (largest_key + row_pointer_length) * number_of_rows * 2. You can move some or all of this to memory (and increase the speed of the process) by increasing the size of the mysqld variable sort_buffer_size if you have the available memory. Otherwise, it is created as specified by the TMPDIR environment variable or the -t myisamchk option.
Memory usage is determined by the mysqld variables or the options set in the myisamchk command line (see the section titled "Using myisamchk").

If the error is caused by the table running out of space and the table type is InnoDB, you will have to enlarge the InnoDB tablespace. MyISAM tables have a huge theoretical size limit (eight million terabytes), but by default pointers are only allocated for 4GB. If the table reaches this limit, you can extend it by using the MAX_ROWS and AVG_ROW_LENGTH ALTER TABLE parameters. To prepare the table called limited for great things (currently it only has three records), you use the following:

mysql> ALTER TABLE limited MAX_ROWS=999999999999 AVG_ROW_LENGTH=100;
Query OK, 3 rows affected (0.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

This allocates pointers for a much greater number of records. The AVG_ROW_LENGTH is used when BLOB and TEXT fields are present, and it gives MySQL an idea of the average size of a record, which it can then use for optimization purposes.

Repairing Non-MyISAM Table Types

The three methods of repairing discussed in the following sections work only with MyISAM tables. Some of the options have been reported to work occasionally with BDB tables, but they were not designed for this. Currently, the only way to repair corrupted BDB and InnoDB tables is to restore from backup.

Repairing Tables with REPAIR TABLE

You can run the REPAIR TABLE statement when connected to the MySQL server. It currently only works with MyISAM tables. See Table 7 for the options.

The syntax is as follows:

Table 10: Available REPAIR TABLE Options

Option	Description
QUICK	Fastest repair because the data file is not modified. It uses much less disk space as well because the data file is not modified.
EXTENDED	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
USE_FRM	This is the option to use if the .MYI file is missing or has a corrupted header. It will rebuild the indexes from the definitions found in the .frm table definition file.

The following is an example of using REPAIR in the case of a missing .MYI file. Let’s delete the .MYI file of an existing table, t4.

% ls -l t4.*
-rw-rw----  1 mysql  mysql    10 Jun 14 02:00 t4.MYD
-rw-rw----  1 mysql  mysql  4096 Jun 14 02:00 t4.MYI
-rw-rw----  1 mysql  mysql  8550 Jun  8 10:46 t4.frm
% rm t4.MYI
% mysql -uguru2b -pg00r002b firstdb

A normal REPAIR does not work:

mysql> REPAIR TABLE t4;
+------------+--------+----------+--------------------------------------+
| Table      | Op     | Msg_type | Msg_text                             |
+------------+--------+----------+--------------------------------------+
| firstdb.t4 | repair | error    | Can't find file: 't4.MYD' (errno: 2) |
+------------+--------+----------+--------------------------------------+
1 row in set (0.47 sec)

The current error message (4.0.3) indicates that the .MYD file cannot be found, when it’s actually the .MYI file that’s missing. The error message is likely to have been clarified by the time you read this. To repair the table in this instance, you need to use the USE_FRM option, which, as the name suggests, uses the .frm definition file to re-create the .MYI index file:

mysql> REPAIR TABLE t4 USE_FRM;
+------------+--------+----------+------------------------------------+
| Table      | Op     | Msg_type | Msg_text                           |
+------------+--------+----------+------------------------------------+
| firstdb.t4 | repair | warning  | Number of rows changed from 0 to 2 |
| firstdb.t4 | repair | status   | OK                                 |
+------------+--------+----------+------------------------------------+
2 rows in set (0.46 sec)

Repairing Tables with mysqlcheck

The mysqlcheck command-line utility is used while the server is still running and works only with MyISAM tables. It is described fully later in this chapter in the "Using mysqlcheck" section. To repair tables, you use the -r option:

% mysqlcheck -r firstdb customer -uroot -pg00r002b
firstdb.customer                                   OK

You can repair multiple tables at the same time by listing the table names after the

database name:

% mysqlcheck -r firstdb customer sales -uroot -pg00r002b
firstdb.customer                                   OK
firstdb.sales                                      OK

If for some reason all tables in a database are corrupt, you can repair them all by just supplying the database name:

% mysqlcheck -r firstdb -uroot -pg00r002b

Repairing Tables with myisamchk

You can use the myisamchk command-line utility (described fully later in this chapter in the "Using myisamchk" section) repair tables (see Table 10.8).

Table 8: Repairing Tables with myisamchk

Option	Description
-D #, –data-file-length=#	Specifies the maximum length of the data file when re-creating it.
-e, –extend-check	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
-f, –force	Overwrites old temporary files (that have an extension of .TMD) instead of aborting if it encounters a preexisting one.
-k #, keys-used=#	Specifies which keys to use, which can make the process faster. Each binary bit stands for one key starting at 0 for the first key.
-r, –recover	Repairs most corruption and should be the first option attempted. You can increase thesort_buffer_size to make the recovery go more quickly if you have the memory. This option will not recover from the rare form of corruption where a unique key is not unique.
-o, –safe-recover	A more thorough, yet slower repair option than -r that should be used only if -r fails. This reads through all rows and rebuilds the indexes based on the rows. It also uses less disk space than -r because a sort buffer is not created. You can increase the size of key_ buffer_size to improve repair speed.
-n, –sort-recover	Forces MySQL to use sorting to resolve the indexes, even if the resulting temporary files are large.
–character-sets-dir=…	The directory containing the character sets.
–set-character-set=name	Specifies a new character set for the index.
-t, –tmpdir=path	Specifies a new path for storing temporary files if you don’t want to use whatever the TMPDIR environment variable specifies.
-q, –quick	Fastest repair because the data file is not modified. Specifying the q twice (-q -q) will modify the data file if there are duplicate keys. Uses much less disk space as well because the data file is not modified.
-u, –unpack	Unpacks a file that has been packed with the myisampack utility.

The server should either not be running, or you must be sure there is no interaction with the tables with which you’re working, such as when you start MySQL with the –skip-external-locking option. If the –skip-external-locking option is not on, you can only safely use myisamchk to repair tables if you are sure there will be no simultaneous access. Whether –skip-external-locking is used or not, you’ll need to flush the tables before starting the repair (with mysqladmin flush-tables) and ensure that there is no access. You may get wrong results (with tables being marked as corrupted even when they are not) if mysqld or anything else accesses the table while myisamchk is running.

The syntax is as follows:

myisamchk [options] [tablenames]

You must run myisamchk from the directory containing the .MYI files or supply the path. The following examples show a repair in action, with MySQL deciding whether to use sorting or a keycache:

% myisamchk -r customer
- recovering (with keycache) MyISAM-table 'customer.MYI'
Data records: 0
% myisamchk -r sales   
- recovering (with sort) MyISAM-table 'sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

If you have lots of memory, besides increasing the size of sort_buffer_size and key_buffer_size as described previously, you can also set some other variables to make myisamchk perform more snappily. See the full myisamchk description later in this chapter in the "Using myisamchk" section.

Using mysqlcheck

The mysqlcheck utility is a boon to more recent users of MySQL because, beforehand, much of the repairing and checking functionality could only be used when the server was shut down. Luckily this limitation is a thing of the past with the mysqlcheck utility.

mysqlcheck uses the CHECK, REPAIR, ANALYZE, and OPTIMIZE statements to perform these tasks from the command line, which is useful for automated maintenance of your databases (see Table 9).

The syntax is as follows:

mysqlcheck [options] databasename [tablenames]

or as follows:

mysqlcheck [options] --databases databasename1 [databasename2 databasename 3 ...]

or as follows:

Table 9: Mysqlcheck Options

Option	Description
-A, –all-databases	Checks all available databases.
-1, –all-in-1	Combines queries for tables into one query per database (instead of one per table). Tables are in a comma-separated list.
-a, –analyze	Analyzes the listed tables.
–auto-repair	Automatically repairs tables if they are corrupted (after all tables in the query have been checked).
-#, –debug=…	Outputs a debug log.
–character-sets-dir=…	This specifies the directory where the character sets are.
-c, –check	Checks tables.
-C, –check-only-changed	Checks tables that have changed since the last check or were not closed properly.
–compress	Uses compression in the client/server protocol.
-?, –help	Displays the help message and exits.
-B, –databases	Lists a number of databases to check (all tables in the databases are checked).
–default-character-set=…	Sets the default character set.
-F, –fast	Checks tables that haven’t been closed properly.
-f, –force	Forces the process to continue even if it encounters an error.
-e, –extended	This is the slowest form for checking but will make sure the table is completely consistent. You can also use this option to repair, though it is usually not necessary.
-h, –host=…	Hostname to which to connect.
-m, –medium-check	Much faster than the extended check and finds the vast majority of errors.
-o, –optimize	Optimizes the tables.
-p, –password[=...]	The password with which to connect.
-P, –port=…	The port to use for connecting.
-q, –quick	The fastest check, this does not check table rows when checking. When repairing, it only repairs the index tree.
-r, –repair	Repairs most errors, except unique keys that somehow contain duplicates.
-s, –silent	Displays no output except for error messages.
-S, –socket=…	Specifies the socket file to use when connecting.
–tables	List of tables to check. With the -B option, this will take precedence.
-u, –user=#	Specifies the user to connect as.
-v, –verbose	Prints lots of output about the process.
-V, –version	Displays the version information and exits.

The mysqlcheck utility also has a feature that allows it to be run in different ways without specifying the options. By simply creating a copy of mysqlcheck with one of the following names, it will take that default behavior:

mysqlrepair: The default option is -r.
mysqlanalyze: The default option is -a.
mysqloptimize: The default option is -o.

The default option when it is named mysqlcheck is -c. All of these renamed files can have the full mysqlcheck functionality—it’s just that their default behavior is changed.

Using myisamchk

The myisamchk utility is the older utility, available since the early days of MySQL. It is also used to analyze, check, and repair tables, but care needs to be taken if you want to use it when the server is running. Table 10 describes the general myisamchk options, Table 11 describes the check options, Table 12 describes the repair options, and Table 13 describes other options.

The server should either not be running, or you must be sure there is no interaction with the tables with which you’re working, such as when you start MySQL with the –skip-external-locking option. If the –skip-external-locking option is not on, you can only safely use myisamchk to repair tables if you are sure there will be no simultaneous access. Whether or not –skip-external-locking is used, you’ll need to flush the tables before starting the repair (with mysqladmin flush-tables) and ensure that there is no access.

I suggest you rather use one of the other options if the server is running.

The syntax is as follows:

myisamchk [options] tablename[s]

You must run myisamchk from the directory where the .MYI index files are located unless you specify the path to them; otherwise you’ll get the following error:

% myisamchk -r sales.MYI
myisamchk: error: File 'sales.MYI' doesn't exist

Specifying the path solves the problem:

% myisamchk -r /usr/local/mysql/data/firstdb/sales.MYI
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/firstdb/sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

The table name can be specified with or without the .MYI extension.

% myisamchk -r sales
- recovering (with sort) MyISAM-table 'sales'
Data records: 9
- Fixing index 1
- Fixing index 2
% myisamchk -r sales.MYI
- recovering (with sort) MyISAM-table 'sales.MYI'
Data records: 9
- Fixing index 1
- Fixing index 2

Warning

A common mistake is to try run myisamchk on an .MYD data file. Always use the .MYI index file!

You can use wildcard character to search all tables in a database directory (*.MYI) or even all tables in all databases:

Table 10: General myisamchk Options

Option	Description
-#, –debug=debug_options	Outputs a debug log. A common debug_option string is d:t:o,filename.
-?, –help	Displays a help message and exits.
-O var=option, –set-variable var=option	Sets the value of a variable. The possible variables and their default values for myisamchk can be examined with myisamchk –help.
-s, –silent	Only outputs error messages. A second s can be used to completely silence myisamchk.
-v, –verbose	Displays more information than usual. As with silent, multiple v’s can be used to output more information (-vv or -vvv).
-V, –version	Displays the myisamchk version details and exits.
-w, –wait	If the table is locked, -w will wait for the table to be unlocked rather than exiting with an error. If mysqld was running with the –skip-external-locking option, the table can only be locked by another myisamchk command.

By running myisamchk –help, besides the general options, you can see what variables you can change with the -O option and what the current settings are:

% myisamchk --help
..
Possible variables for option --set-variable (-O) are:
key_buffer_size       current value: 520192
myisam_block_size     current value: 1024
read_buffer_size      current value: 262136
write_buffer_size     current value: 262136
sort_buffer_size      current value: 2097144
sort_key_blocks       current value: 16
decode_bits           current value: 9
ft_min_word_len       current value: 4
ft_max_word_len       current value: 254
ft_max_word_len_for_sort  current value: 20

The space allocated by the key_buffer_size is used when doing an extended check or when indexes are inserted one row at a time (using the safe-recover option). sort_buffer_size is used in the default repair, when indexes are sorted in the repair.

To achieve a faster repair, set the sort_buffer_size to about one-quarter of the total available memory. Only one of the two variables is used at a time, so you don’t need to worry about running out of memory by making both values large.

Note

Inside the my.cnf (or my.ini) file, there are separate sections for mysqld and myisamchk. You can quite easily set the sort_buffer_size to a high value for repairing, and keep it lower if your system has other requirements for day-to-day running.

Table 11: myisamchk Check Options

Option	Description
-c, –check	Ordinary check and the default option.
-e, –extend-check	Slowest and most thorough form of check. If you are using –extended-check and don’t have much memory, you should increase the value of key_buffer_size a lot!
-F, –fast	Fast check that only checks tables that haven’t been closed properly.
-C, –check-only-changed	Checks only the tables that have been changed since the last check.
-f, –force	This runs the repair option if any errors are found in the table.
-i, –information	Displays statistics about the table that is checked.
-m, –medium-check	Medium check, faster than an extended check and good enough for most cases.
-U, –update-state	Keeps information about when the table was checked and whether the table has crashed, which is useful for the -C option. Should not be used when the table is being used and the –skip-external-locking option is active.
-T, –read-only	Does not mark the table as checked (useful for running myisamchk when the server is active and the –skip-external-locking option is in use).

Table 12: myisamchk Repair Options

Option	Description
-D #, –data-file-length=#	Specifies the maximum length of the data file when re-creating it.
-e, –extend-check	Attempts to recover every possible row from the data file. This option should not be used unless as a last resort because it may produce garbage rows.
-f, –force	Overwrites old temporary files (that have an extension of .TMD) instead of aborting if it encounters a preexisting one.
-k #, keys-used=#	Specifies the keys to use, which can make the process faster. Each binary bit stands for one key starting at 0 for the first key (for example, 1 is the first index, 10 is the second index).
-r, –recover	Repairs most corruption and should be the first option attempted. You can increase the sort_buffer_size to make the recover go more quickly if you have the memory. This option will not recover from the rare form of corruption where a unique key is not unique.
-o, –safe-recover	A more thorough, slower repair option than -r, which should be used only if -r fails. This reads through all rows and rebuilds the indexes based on the rows. It also uses less disk space than -r because a sort buffer is not created. You can increase the size of key_ buffer_size to improve repair speed.
-n, –sort-recover	Forces MySQL to uses sorting to resolve the indexes, even if the resulting temporary files are large.
–character-sets-dir=…	The directory containing the character sets.
–set-character-set=name	Specifies a new character set for the index.
-t, –tmpdir=path	Specifies a new path for storing temporary files if you don’t want to use the contents of the TMPDIR environment variable.
-q, –quick	Fastest repair as the data file is not modified. Running this option a second time will modify the data file if there are duplicate keys. Uses much less disk space as well because the data file is not modified.
-u, –unpack	Unpacks a file that has been packed with the myisampack utility.

Table 13: Other myisamchk Options

Option	Description
-a, –analyze	Analyzing tables improves performance by updating the index information for atable so that MySQL can make a better decision on how to join tables. The distribution of the various index elements is stored for later usage. This option isthe same as ANALYZE TABLE.
-d, –description	Displays a description of the table.
-A, –set-auto-increment[=value]	Sets the AUTO_INCREMENT counter to the specified value (or increments it by one if no value is supplied).
-S, –sort-index	Sorts the index tree blocks in descending order, which improves the performance of seeks and table scanning by key.
-R, –sort-records=#	Sorts records according to the index specified (index numbers begin from 1; you can use SHOW INDEX to see an ordered list). This can speed up queries that are ordered on this index, as well as ranged selects. It will probably be very slow if you sort a large table for the first time.

Running myisamchk with the -d option produces the following kind of output:

% myisamchk -d customer
MyISAM file:         customer
Record format:       Packed
Character set:       latin1 (8)
Data records:        3  Deleted blocks:                 0
Recordlength:        75
table description:
Key Start Len Index   Type
1      2         4      unique   long

Aug 02

Using Benchmarks Test Mysql

By jason Mysql Server No Comments »

MySQL distributions come with a benchmark suite called run-all-tests. You can use it to test various DBMSs to see how well they perform. To use it, you need to have Perl, the Perl DBI module, and the DBD module for the DBMS you want to test. Table 1 explains the options for run-all-tests.


Table 1: Options for run-all-tests





Option



Description





--comments 



Adds a comment to the benchmark output.





--cmp=server[,server...] 



Runs the test with limits from the specified servers. By running all servers with the same --cmp, the test results will be comparable between the different SQL servers.





--create-options=# 



Specifies an extra argument to all create statements. For example, to create all tables as BDB tables, you would use --create-options=TYPE=BDB.





--database 



Specifies the database in which the test tables are created. The default is the test database.





--debug 



Displays debugging information. You normally only use this when debugging a test.





--dir 



Indicates where the test results should be stored. The default is the defaultoutput.





--fast 



Allows the use of nonstandard ANSI SQL commands to make the test gofaster.





--fast-insert 



Uses fast inserts where possible, which include multiple value lists, such as INSERT INTO tablename VALUES (values),(values) or simply INSERT INTO tablename VALUES (values) rather than INSERT INTO tablename(fields) VALUES (values).





--field-count 



Specifies how many fields there are to be in the test table. Usually only used when debugging a test.





--force 



Continues the test even when encountering an error. Deletes tables before creating new ones. Usually only used when debugging a test.





--groups 



Indicates how many different groups there are to be in the test. Usually only used when debugging a test.





--lock-tables 



Allows the use of table locking to get more speed.





--log 



Saves the results to the --dir directory.





--loop-count (Default) 



Indicates how many times each test loop is to be executed. Usually only used when debugging a test.





--help 



Displays a list of options.





--host='host name' 



Specifies the host where the database server is located. The default islocalhost.





--machine="machine or os_name" 



The machine/operating system name that is added to the benchmark output filename. The default is the operating system name + version.





--odbc 



Uses the DBI ODBC driver to connect to the database.





--password='password' 



Specifies the password for the user the test connects as.





--socket='socket' 



Specifies the socket to connect with (if sockets are supported).





--regions 



Specifies how AND levels should be tested. Usually only used when debugging a test.





--old-headers 



Gets the old benchmark headers from the old RUN- file.





--server='server name' 



Specifies which DBMS on which to run the test. These can include Access, Adabas, AdabasD, Empress, Oracle, Informix, DB2, mSQL, MS-SQL, MySQL, Pg, Solid, and Sybase. The default is MySQL.





--silent 



Does not output information about the server when the test starts.





--skip-delete 



Specifies that the test tables created are not deleted. Usually only used when debugging a test.





--skip-test=test1[,test2,...] 



Excludes the specified tests when running the benchmark.





--small-test 



Speeds up the tests by using smaller limits.





--small-tables 



Uses fewer rows to run the tests. This would be used if the database cannot handle large tables for some reason (they could have small partitions, forexample).





--suffix 



Adds a suffix to the database name in the benchmark output filename. Used when you want to run multiple tests without overwriting the results. When using the --fast option, the suffix is automatically _fast.





--random 



Generates random initial values for the sequence of test executions, which could be used to imitate real conditions.





--threads=# 



Specifies the number of threads to use for multiuser benchmarks. The default is 5.





--tcpip 



Use TCP/IP to connect to the server. This allows the test to do many new connections in a row as the TCP/IP stack can be filled.





--time-limit 



Specifies a time limit in seconds for a test loop before the test ends, and the result estimated. The default is 600 seconds.





--use-old-results 



Uses the old results from the --dir directory instead of actually running the tests.





--user='user_name' 



Specifies the user to connect as.





--verbose 



Displays more info. Usually only used when debugging a test.





--optimization='some comments' 



Adds comments about optimizations done before the test.





--hw='some comments' 



Adds comments about hardware used for this test.





% cd sql-bench
% perl run-all-tests --small-test --password='g00r002b'
Benchmark DBD suite: 2.14
Date of test:        2002-07-21 21:35:42
Running tests on:    Linux 2.2.5-15 i686
Arguments:           --small-test
Comments:
Limits from:
Server version:      MySQL 4.0.1 alpha max log
Optimization:        None
Hardware:            

ATIS: Total time: 19 wallclock secs ( 5.23 usr  0.96 sys +  0.00 cusr
  0.00 csys =  0.00 CPU)
alter-table: Total time:  2 wallclock secs ( 0.12 usr  0.03 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
big-tables: Total time:  1 wallclock secs ( 0.43 usr  0.10 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
connect: Total time:  8 wallclock secs ( 2.90 usr  0.66 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
create: Total time:  0 wallclock secs ( 0.15 usr  0.01 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
insert: Total time: 31 wallclock secs ( 8.47 usr  1.43 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
select: Total time: 55 wallclock secs (17.76 usr  1.71 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)
transactions: Test skipped because the database doesn't support
 transactions
wisconsin: Total time: 42 wallclock secs ( 9.55 usr  1.84 sys +  0.00
 cusr  0.00 csys =  0.00 CPU)

All 9 test executed successfully

Totals per operation:
Operation                      seconds     usr     sys     cpu   tests
alter_table_add                   1.00    0.07    0.00    0.00      92
alter_table_drop                  0.00    0.03    0.00    0.00      46
connect                           0.00    0.22    0.02    0.00     100
connect+select_1_row              1.00    0.27    0.04    0.00     100
connect+select_simple             1.00    0.27    0.04    0.00     100
count                             1.00    0.13    0.00    0.00     100
count_distinct                    1.00    0.13    0.02    0.00     100
count_distinct_2                  1.00    0.16    0.02    0.00     100
count_distinct_big                1.00    0.12    0.04    0.00      30
count_distinct_group              1.00    0.17    0.00    0.00     100
count_distinct_group_on_key       1.00    0.13    0.01    0.00     100
count_distinct_group_on_key_parts 1.00    0.16    0.01    0.00     100
count_distinct_key_prefix         1.00    0.12    0.01    0.00     100
count_group_on_key_parts          1.00    0.09    0.00    0.00     100
count_on_key                     20.00    6.11    0.60    0.00    5100
create+drop                       0.00    0.01    0.00    0.00      10
create_MANY_tables                0.00    0.01    0.00    0.00      10
create_index                      1.00    0.00    0.00    0.00       8
create_key+drop                   0.00    0.12    0.01    0.00     100
create_table                      1.00    0.03    0.00    0.00      31
delete_all_many_keys              1.00    0.08    0.00    0.00       1
delete_big                        0.00    0.01    0.00    0.00       1
delete_big_many_keys              1.00    0.07    0.00    0.00     128
delete_key                        0.00    0.06    0.01    0.00     100
delete_range                      1.00    0.01    0.00    0.00      12
drop_index                        0.00    0.00    0.00    0.00       8
drop_table                        0.00    0.01    0.00    0.00      28
drop_table_when_MANY_tables       0.00    0.00    0.00    0.00      10
insert                           57.00   13.90    2.51    0.00   44768
insert_duplicates                 1.00    0.29    0.04    0.00    1000
insert_key                        0.00    0.04    0.01    0.00     100
insert_many_fields                0.00    0.12    0.00    0.00     200
insert_select_1_key               0.00    0.00    0.00    0.00       1
insert_select_2_keys              0.00    0.00    0.00    0.00       1
min_max                           1.00    0.06    0.01    0.00      60
min_max_on_key                   17.00    8.12    0.64    0.00    7300
multiple_value_insert             0.00    0.03    0.00    0.00    1000
order_by_big                      1.00    0.30    0.10    0.00      10
order_by_big_key                  1.00    0.29    0.14    0.00      10
order_by_big_key2                 1.00    0.28    0.12    0.00      10
order_by_big_key_desc             0.00    0.35    0.08    0.00      10
order_by_big_key_diff             0.00    0.35    0.05    0.00      10
order_by_big_key_prefix           1.00    0.31    0.09    0.00      10
order_by_key2_diff                0.00    0.01    0.00    0.00      10
order_by_key_prefix               0.00    0.02    0.00    0.00      10
order_by_range                    0.00    0.03    0.00    0.00      10
outer_join                        1.00    0.01    0.00    0.00      10
outer_join_found                  1.00    0.01    0.01    0.00      10
outer_join_not_found              1.00    0.03    0.01    0.00      10
outer_join_on_key                 0.00    0.01    0.00    0.00      10
select_1_row                      1.00    0.27    0.06    0.00    1000
select_1_row_cache                1.00    0.18    0.07    0.00    1000
select_2_rows                     1.00    0.43    0.05    0.00    1000
select_big                        0.00    0.31    0.10    0.00      17
select_big_str                    1.00    0.55    0.22    0.00     100
select_cache                      4.00    0.95    0.18    0.00    1000
select_cache2                     4.00    1.28    0.11    0.00    1000
select_column+column              1.00    0.35    0.06    0.00    1000
select_diff_key                   0.00    0.02    0.00    0.00      10
select_distinct                   1.00    0.30    0.06    0.00      80
select_group                      4.00    0.61    0.09    0.00     391
select_group_when_MANY_tables     0.00    0.00    0.00    0.00      10
select_join                       1.00    0.06    0.03    0.00      10
select_key                        0.00    0.02    0.01    0.00      20
select_key2                       0.00    0.02    0.00    0.00      20
select_key2_return_key            1.00    0.12    0.00    0.00      20
select_key2_return_prim           0.00    0.00    0.00    0.00      20
select_key_prefix                 0.00    0.05    0.00    0.00      20
select_key_prefix_join            2.00    0.62    0.16    0.00      10
select_key_return_key             0.00    0.02    0.00    0.00      20
select_many_fields                1.00    0.31    0.10    0.00     200
select_range                      2.00    0.23    0.05    0.00      41
select_range_key2                 1.00    0.43    0.02    0.00     505
select_range_prefix               1.00    0.42    0.05    0.00     505
select_simple                     0.00    0.21    0.04    0.00    1000
select_simple_cache               0.00    0.14    0.05    0.00    1000
select_simple_join                0.00    0.13    0.05    0.00      50
update_big                        1.00    0.01    0.00    0.00      10
update_of_key                     1.00    0.20    0.03    0.00     500
update_of_key_big                 0.00    0.02    0.01    0.00      13
update_of_primary_key_many_keys   0.00    0.12    0.02    0.00     256
update_with_key                   4.00    1.03    0.12    0.00    3000
update_with_key_prefix            1.00    0.58    0.01    0.00    1000
wisc_benchmark                    2.00    0.70    0.16    0.00      34
TOTALS                          156.00   43.84    6.55    0.00   76237

The benchmark suite is useful for comparing various platforms. MySQL comes with a set of results, but these are dated and not particularly useful. I suggest repeating the test yourself to make them meaningful in your situation.

It’s also important to benchmark your own applications (under the highest possible load) before you roll them out. An application that can help you impose load on your server is super-smack, downloadable from the MySQL site.

Another useful script distributed with MySQL is crash-me, which verifies the functionality on a specific installation and tests the reliability of the server under stress (see Table 2). It gets its name from the results when an installation fails the test. It’s also portable and can test multiple database platforms for comparison purposes. As a result of its behavior, it should never be run in a live environment. It can crash not only the database server, but it also takes significant amounts of memory, meaning it can impact on other programs running on the server. Be aware, though, that MySQL has developed it, so it naturally highlights MySQL strengths and downplays MySQL weaknesses for comparison purposes. For example, triggers and procedures, which MySQL does not currently implement, may seem from viewing the crash-me output to be as important as nonstandard MySQL features, such as using || for OR instead of string concatenation.

Table 2: Thecrash-meOptions

Option	Description
–help, –Information	Displays a help list of options.
–batch-mode	Runs the test without asking for input and exits if it encounters errors.
–comment=’some comment’	Adds the specified comment to the crash-me limit file.
–check-server	Does a new connection to the server every time it checks if the server isstillrunning. This can be useful if a previous query causes wrong data tostart being returned.
–database=’database’	Creates the test tables in this database. The default is test.
–dir=’directory_name’	Saves the output to this directory
–debug	Displays lots of output to assist in debugging if there is a problem.
–fix-limit-file	Reformats the crash-me limit file. This does not rerun the crash-me.
–force	Begins the test immediately, without warning and without waiting for input. Use this option to automate the test.
–log-all-queries	Displays all executed queries. Mostly used for debugging crash-me.
–log-queries-to-file=’filename’	Logs full queries to the specified file.
–host=’hostname’	Runs the test on the specified host. The default is localhost.
–password=’password’	Specifies the password for the current user.
–restart	Saves states during each test, making it possible, in the case of a crash, to continue from where it left off by restarting with the same options.
–server=’server name’	Specifies the server on which to run the test. These include Access, Adabas, AdabasD, Empress, Oracle, Informix, DB2, Mimer, mSQL, MS-SQL, MySQL, Pg, Solid, or Sybase. The default is MySQL. Other servers will not have their names reported.
–user=’user_name’	Specifies the username to connect as.
–start-cmd=’command to restart server’	Will use the specified command to restart the database server in the case of it dying. (The availability of this option says everything!)
–sleep=’time in seconds’	Specifies the time in seconds to wait before restarting the server. The default is 10 seconds.

A sample display of crash-me follows:

% perl crash-me --password='g00r002b'
Running crash-me 1.57 on 'MySQL 4.0.1 alpha max log'

I hope you didn't have anything important running on this server....
Reading old values from cache: /usr/local/mysql-max-4.0.1-alpha-pc-
linux-gnu-i686/sql-bench/limits/mysql.cfg

NOTE: You should be familiar with 'crash-me --help' before continuing!

This test should not crash MySQL if it was distributed together with
 the running MySQL version.
If this is the case you can probably continue without having to worry
 about destroying something.

Some of the tests you are about to execute may require a lot of
memory.  Your tests WILL adversely affect system performance. It's
not uncommon that either this crash-me test program, or the actual
database back-end, will DIE with an out-of-memory error. So might
any other program on your system if it requests more memory at the
wrong time.

Note also that while crash-me tries to find limits for the database server
it will make a lot of queries that can't be categorized as 'normal'.
It's not unlikely that crash-me finds some limit bug in your server so
if you run this test you have to be prepared that your server may die
during it!

We, the creators of this utility, are not responsible in any way if
your database server unexpectedly crashes while this program tries to
find the limitations of your server. By accepting the following
question with 'yes', you agree to the above!

You have been warned!

Start test (yes/no) ?
Tables without primary key: yes
SELECT without FROM: yes
Select constants: yes
Select table_name.*: yes
Allows ' and " as string markers: yes
Double '' as ' in strings: yes
Multiple line strings: yes
" as identifier quote (ANSI SQL): error
` as identifier quote: yes
[] as identifier quote: no
Column alias: yes
Table alias: yes
Functions: yes
Group functions: yes
Group functions with distinct: yes
Group by: yes
Group by position: yes
Group by alias: yes
Group on unused column: yes
Order by: yes
Order by position: yes
Order by function: yes
Order by on unused column: yes
Order by DESC is remembered: no
Compute: no
INSERT with Value lists: yes
INSERT with set syntax: yes
allows end ';': yes
LIMIT number of rows: with LIMIT
SELECT with LIMIT #,#: yes
Alter table add column: yes
Alter table add many columns: yes
Alter table change column: yes
Alter table modify column: yes
Alter table alter column default: yes
Alter table drop column: yes
Alter table rename table: yes
rename table: yes
truncate: yes
Alter table add constraint: yes
Alter table drop constraint: no
Alter table add unique: yes
Alter table drop unique: with drop key
Alter table add primary key: with constraint
Alter table add foreign key: yes
Alter table drop foreign key: with drop foreign key
Alter table drop primary key: drop primary key
Case insensitive compare: yes
Ignore end space in compare: yes
Group on column with null values: yes
Having: yes
Having with group function: yes
Order by alias: yes
Having on alias: yes
binary numbers (0b1001): no
hex numbers (0x41): yes
binary strings (b'0110'): no
hex strings (x'1ace'): no
Value of logical operation (1=1): 1
Simultaneous connections (installation default): 101
query size: 1048574

Supported sql types
Type character(1 arg): yes
Type char(1 arg): yes
Type char varying(1 arg): yes
Type character varying(1 arg): yes
Type boolean: no
Type varchar(1 arg): yes
Type integer: yes
Type int: yes
Type smallint: yes
Type numeric(2 arg): yes
Type decimal(2 arg): yes
Type dec(2 arg): yes
Type bit: yes
Type bit(1 arg): yes
Type bit varying(1 arg): no
Type float: yes
Type float(1 arg): yes
Type real: yes
Type double precision: yes
Type date: yes
Type time: yes
Type timestamp: yes
Type interval year: no
Type interval year to month: no
Type interval month: no
Type interval day: no
Type interval day to hour: no
Type interval day to minute: no
Type interval day to second: no
Type interval hour: no
Type interval hour to minute: no
Type interval hour to second: no
Type interval minute: no
Type interval minute to second: no
Type interval second: no
Type national character varying(1 arg): yes
Type national character(1 arg): yes
Type nchar(1 arg): yes
Type national char varying(1 arg): yes
Type nchar varying(1 arg): yes
Type national character varying(1 arg): yes
Type timestamp with time zone: no

Supported odbc types
Type binary(1 arg): yes
Type varbinary(1 arg): yes
Type tinyint: yes
Type bigint: yes
Type datetime: yes

Supported extra types
Type blob: yes
Type byte: no
Type long varbinary: yes
Type image: no
Type text: yes
Type text(1 arg): no
Type mediumtext: yes
Type long varchar(1 arg): no
Type varchar2(1 arg): no
Type mediumint: yes
Type middleint: yes
Type int unsigned: yes
Type int1: yes
Type int2: yes
Type int3: yes
Type int4: yes
Type int8: yes
Type uint: no
Type money: no
Type smallmoney: no
Type float4: yes
Type float8: yes
Type smallfloat: no
Type float(2 arg): yes
Type double: yes
Type enum(1 arg): yes
Type set(1 arg): yes
Type int(1 arg) zerofill: yes
Type serial: no
Type char(1 arg) binary: yes
Type int not null auto_increment: yes
Type abstime: no
Type year: yes
Type datetime: yes
Type smalldatetime: no
Type timespan: no
Type reltime: no
Type int not null identity: no
Type box: no
Type bool: yes
Type circle: no
Type polygon: no
Type point: no
Type line: no
Type lseg: no
Type path: no
Type interval: no
Type serial: no
Type inet: no
Type cidr: no
Type macaddr: no
Type varchar2(1 arg): no
Type nvarchar2(1 arg): no
Type number(2 arg): no
Type number(1 arg): no
Type number: no
Type long: no
Type raw(1 arg): no
Type long raw: no
Type rowid: no
Type mlslabel: no
Type clob: no
Type nclob: no
Type bfile: no
Remembers end space in char(): no
Remembers end space in varchar(): no
Supports 0000-00-00 dates: yes
Supports 0001-01-01 dates: yes
Supports 9999-12-31 dates: yes
Supports 'infinity dates: error
Type for row id: auto_increment
Automatic row id: _rowid

Supported sql functions

Supported odbc functions

Supported extra functions

Supported where functions

Supported sql group functions
Group function AVG: yes
Group function COUNT (*): yes
Group function COUNT column name: yes
Group function COUNT(DISTINCT expr): yes
Group function MAX on numbers: yes
Group function MAX on strings: yes
Group function MIN on numbers: yes
Group function MIN on strings: yes
Group function SUM: yes
Group function ANY: no
Group function EVERY: no
Group function SOME: no

Supported extra group functions
Group function BIT_AND: yes
Group function BIT_OR: yes
Group function COUNT(DISTINCT expr,expr,...): yes
Group function STD: yes
Group function STDDEV: yes
Group function VARIANCE: no

mixing of integer and float in expression: yes
No need to cast from integer to float: yes
Is 1+NULL = NULL: yes
Is concat('a',NULL) = NULL: yes
LIKE on numbers: yes
column LIKE column: yes
update of column= -column: yes
String functions on date columns: yes
char are space filled: no
DELETE FROM table1,table2...: no
Update with sub select: no
Calculate 1--1: yes
ANSI SQL simple joins: yes
max text or blob size: 1048543 (cache)
constant string size in where: 1048539 (cache)
constant string size in SELECT: 1048565 (cache)
return string size from function: 1047552 (cache)
simple expressions: 1837 (cache)
big expressions: 10 (cache)
stacked expressions: 1837 (cache)
tables in join: 63 (cache)
primary key in create table: yes
unique in create table: yes
unique null in create: yes
default value for column: yes
default value function for column: no
temporary tables: yes
create table from select: yes
index in create table: yes
null in index: yes
null in unique index: yes
null combination in unique index: yes
null in unique index: yes
index on column part (extension): yes
different namespace for index: yes
case independent table names: no
drop table if exists: yes
create table if not exists: yes
inner join: yes
left outer join: yes
natural left outer join: yes
left outer join using: yes
left outer join odbc style: yes
right outer join: yes
full outer join: no
cross join (same as from a,b): yes
natural join: yes
union: no
union all: no
intersect: no
intersect all: no
except: no
except all: no
except: no
except all: no
minus: no
natural join (incompatible lists): yes
union (incompatible lists): no
union all (incompatible lists): no
intersect (incompatible lists): no
intersect all (incompatible lists): no
except (incompatible lists): no
except all (incompatible lists): no
except (incompatible lists): no
except all (incompatible lists): no
minus (incompatible lists): no
subqueries: no
insert INTO ... SELECT ...: yes
atomic updates: no
views: no
foreign key syntax: yes
foreign keys: no
Create SCHEMA: no
Column constraints: no
Table constraints: no
Named constraints: no
NULL constraint (SyBase style): yes
Triggers (ANSI SQL): no
PSM procedures (ANSI SQL): no
PSM modules (ANSI SQL): no
PSM functions (ANSI SQL): no
Domains (ANSI SQL): no
many tables to drop table: yes
drop table with cascade/restrict: yes
-- as comment (ANSI): yes
// as comment: no
# as comment: yes
/* */ as comment: yes
insert empty string: yes
Having with alias: yes
table name length: 64 (cache)
column name length: 64 (cache)
select alias name length: +512 (cache)
table alias name length: +512 (cache)
index name length: 64 (cache)
max char() size: 255 (cache)
max varchar() size: 255 (cache)
max text or blob size: 1048543 (cache)
Columns in table: 3398 (cache)
unique indexes: 32 (cache)
index parts: 16 (cache)
max index part length: 255 (cache)
index varchar part length: 255 (cache)
indexes: 32
index length: 500 (cache)
max table row length (without blobs): 65534 (cache)
table row length with nulls (without blobs): 65502 (cache)
number of columns in order by: +64 (cache)
number of columns in group by: +64 (cache)
crash-me safe: yes
reconnected 0 times

Tagged with: benchmark • mysql • test

Aug 02

Changing Mysql Variable Values with the Server Running

By jason Mysql Server 1 Comment »

Until MySQL version 4.0.3, you always had to restart the server to change the variable values. Now, you can use the much more convenient SET statement to make a change without shutting down the server.

You can use the SET statement in two ways. The default is for the change you make to affect the SESSION only, meaning that when you connect next time (and for all other connections) the variable will still be at the setting specified in the configuration file. If you specify the GLOBAL keyword, all new connections will use the new value. When the server restarts, however, it will always use the values set in the configuration file, so you always need to make the changes there as well. To set a variable with the GLOBAL option, you need to have the SUPER permission.

The syntax is as follows:

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION]–
   sql_variable=expression...]

For example, the following:

mysql> SET SESSION max_sort_length=2048;

is the same as this:

mysql> SET max_sort_length=2048;

There is an alternate syntax too, used for compatibility with other database management systems (DBMSs), using the @@ syntax, as follows:

SET @@{global | local}.sql_variable=expression, [@@{global |–
   local}.sql_variable=expression]

To repeat the previous example in this syntax, you use the following:

mysql> SET @@local.max_sort_length=2048;

SESSION and LOCAL are synonyms.

If, after experimenting with the new variable, you decide to return to the old value, there’s no need to trust your memory or to look it up in the configuration file. You can use the DEFAULT keyword to restore a GLOBAL value to the value in configuration file, or a SESSION value to the GLOBAL value. For example:

mysql> SET SESSION max_sort_length=DEFAULT;

and

mysql> SET GLOBAL max_sort_length=DEFAULT;

Tagged with: change variable • mysql • running

Aug 01

Improving Hardware to Speed Up the Mysql Server

By jason Mysql Server 1 Comment »

Hardware is one of the easiest improvements (if you have the cash) you can make to a poorly performing database server. As a general rule of thumb, pack in as much memory as you can first, then use the fastest disks you can get, and finally get the fastest CPU (Central Processing Unit). The best is to benchmark your system, using the various tools available to your operating system to see whether it’s the CPU, memory, disk speed, or a combination that is the bottleneck. This will give you the best idea of the vagaries of your usage and assist you in upgrading. Running the benchmark suite will also help show the performance of different kinds of tasks.

Memory

Memory is the most important element because it allows you to tweak the mysqld variables. Large amounts of memory mean you can create large key and table caches. Memory that is as large as possible allows MySQL to use quicker memory rather than disk as much as possible, and the quicker the memory is, the faster MySQL can access the data stored there. Large amounts of memory on its own is not as useful as if you actively tweaked the mysqld variables to make use of the extra memory, so you can’t be too lazy and just stick in the memory and wait for fireworks.

Disks

Ultimately, MySQL has to fetch the data from disk, and this is where fast disk access plays a role. The disk seek time is important because it determines how fast the physical disk can move to get to the data it needs, so you should choose the disk with the best disk seek time. Also, SCSI (small computer system interface) disks are usually faster than IDE (Intelligent [or Integrated] Drive Electronics) disks, so you’ll probably want these.

An important improvement you can make is to stripe data across multiple disks (where the OS breaks data in parts, spreading it evenly over multiple disks), as well as symlinking (where you create a link from the data directory to another disk). InnoDB tables have a mechanism to split data across multiple disks quite easily, but MyISAM tables do not (being made up of single files), so striping or other forms of RAID can be quite useful. "Multiple Drives," looks at these topics in more detail.

CPU

The faster the processor, the quicker any calculations can be done and the quicker the results sent back to the client. Besides processor speed, the bus speed and the size of the cache are important. An analysis of available processors is beyond the scope of this book and will probably be outdated before this book is even published, but be sure to investigate your processor possibilities carefully to see how it performs in various

Tagged with: hardware • improving • mysql • speed up

Jul 30

Optimizing the mysqld Variables

By jason Mysql Server 1 Comment »

To look at what the existing values for the mysqld variables are, you can use mysqladmin from the command line:

% mysqladmin -uroot -pg00r002b variables;

or when connected to MySQL:

mysql> SHOW VARIABLES
+----------------------+---------------------------------+
| Variable_name        | Value                           +
+----------------------+---------------------------------+
| back_log             | 50                              |
| basedir              | /usr/local/mysql-max-4.0.1-     |
|                      |  alpha-pc-linux-gnu-i686        |
| bdb_cache_size       | 8388600                         |
| bdb_log_buffer_size  | 32768                           |
| bdb_home             | /usr/local/mysql/data/          |
| bdb_max_lock         | 10000                           |
| bdb_logdir           |                                 |
| bdb_shared_data      | OFF                             |
| bdb_tmpdir           | /tmp/                           |
| bdb_version          | Sleepycat Software: Berkeley DB |
|                      |  3.2.9a: (December 23, 2001)    |
| binlog_cache_size    | 32768                           |
| character_set        | latin1                          |
| character_sets       | latin1 big5 czech euc_kr gb2312 |
|                      |  gbk latin1_de sjis tis620 ujis |
|                      |  dec8 dos german1 hp8 koi8_ru   |
|                      |  latin2 swe7 usa7 cp1251 danish |
|                      |  hebrew win1251 estonia         |
|                      |  hungarian koi8_ukr win1251ukr  |
|                      |  greek win1250 croat cp1257     |
|                      |  latin5                         |
| concurrent_insert    | ON                              |
| connect_timeout      | 5                               |
| datadir              | /usr/local/mysql/data/          |
| delay_key_write      | ON                              |
| delayed_insert_limit | 100                             |
| delayed_insert_      |                                 |
|  timeout             | 300                             |
| delayed_queue_size   | 1000                            |
| flush                | OFF                             |
| flush_time           | 0                               |
| ft_min_word_len      | 4                               |
| ft_max_word_len      | 254                             |
| ft_max_word_len_for_ |                                 |
|  sort                | 20                              |
| ft_boolean_syntax    | + -><()~*:""&|                  |
| have_bdb             | YES                             |
| have_innodb          | YES                             |
| have_isam            | YES                             |
| have_raid            | NO                              |
| have_symlink         | YES                             |
| have_openssl         | NO                              |
| init_file            |                                 |
| innodb_additional_   |                                 |
|  mem_pool_size       | 1048576                         |
| innodb_buffer_pool   |                                 |_
|  size                | 8388608                         |
| innodb_data_file_path| ibdata1:64M                     |
| innodb_data_home_dir |                                 |
| innodb_file_io_      |                                 |
|  threads             | 9                               |
| innodb_force_recovery| 0                               |
| innodb_thread_       |                                 |
|  concurrency         | 8                               |
| innodb_flush_log_at_ |                                 |
|  trx_commit          | OFF                             |
| innodb_fast_shutdown | OFF                             |
| innodb_flush_method  |                                 |
| innodb_lock_wait_    |                                 |
|  timeout             | 1073741824                      |
| innodb_log_arch_dir  |                                 |
| innodb_log_archive   | OFF                             |
| innodb_log_buffer_   |                                 |
|  size                | 1048576                         |
| innodb_log_file_size | 5242880                         |
| innodb_log_files_in_ |                                 |
|  group               | 2                               |
| innodb_log_group_    |                                 |
|  home_dir            | ./                              |
| innodb_mirrored_log_ |                                 |
|  groups              | 1                               |
| interactive_timeout  | 28800                           |
| join_buffer_size     | 131072                          |
| key_buffer_size      | 16773120                        |
| language             | /usr/local/mysql-max-4.0.1-alpha|
|                      |  -pc-linux-gnu-i686/share/mysql/|
|                      |  english/                       |
| large_files_support  | ON                              |
| locked_in_memory     | OFF                             |
| log                  | ON                              |
| log_update           | OFF                             |
| log_bin              | ON                              |
| log_slave_updates    | OFF                             |
| log_long_queries     | ON                              |
| long_query_time      | 20                              |
| low_priority_updates | OFF                             |
| lower_case_table_    |                                 |
|  names               | 0                               |
| max_allowed_packet   | 1047552                         |
| max_binlog_cache_size| 4294963200                      |
| max_binlog_size      | 1073741824                      |
| max_connections      | 100                             |
| max_connect_errors   | 10                              |
| max_delayed_threads  | 20                              |
| max_heap_table_size  | 16777216                        |
| max_join_size        | 4294967295                      |
| max_sort_length      | 1024                            |
| max_user_connections | 0                               |
| max_tmp_tables       | 32                              |
| max_write_lock_count | 4294967295                      |
| myisam_bulk_insert_  |                                 |
|  tree_size           | 8388608                         |
| myisam_max_extra_    |                                 |
|  sort_file_size      | 256                             |
| myisam_max_sort_     |                                 |
|  file_size           | 2047                            |
| myisam_recover_      |                                 |
|  options             | OFF                             |
| myisam_sort_buffer_  |                                 |
|  size                | 8388608                         |
| net_buffer_length    | 7168                            |
| net_read_timeout     | 30                              |
| net_retry_count      | 10                              |
| net_write_timeout    | 60                              |
| open_files_limit     | 0                               |
| pid_file             | /usr/local/mysql/data/host.pid  |
| port                 | 3306                            |
| protocol_version     | 10                              |
| record_buffer        | 131072                          |
| record_rnd_buffer    | 131072                          |
| rpl_recovery_rank    | 0                               |
| query_buffer_size    | 0                               |
| query_cache_limit    | 1048576                         |
| query_cache_size     | 0                               |
| query_cache_startup_ |                                 |
|  type                | 1                               |
| safe_show_database   | OFF                             |
| server_id            | 1                               |
| slave_net_timeout    | 3600                            |
| skip_external_locking| ON                              |
| skip_networking      | OFF                             |
| skip_show_database   | OFF                             |
| slow_launch_time     | 2                               |
| socket               | /tmp/mysql.sock                 |
| sort_buffer          | 524280                          |
| sql_mode             | 0                               |
| table_cache          | 64                              |
| table_type           | MYISAM                          |
| thread_cache_size    | 0                               |
| thread_stack         | 65536                           |
| transaction_isolation| READ-COMMITTED                  |
| timezone             | SAST                            |
| tmp_table_size       | 33554432                        |
| tmpdir               | /tmp/                           |
| version              | 4.0.1-alpha-max-log             |
| wait_timeout         | 28800                           |

Also important when tuning is the information supplied by the server itself. You can view this from the command line with the following:

% mysqladmin extended-status

or when connected to the server:

mysql> SHOW STATUS
+--------------------------+----------+
| Aborted_clients          | 142      |
| Aborted_connects         | 5        |
| Bytes_received           | 9005619  |
| Bytes_sent               | 15444786 |
| Connections              | 794      |
| Created_tmp_disk_tables  | 1        |
| Created_tmp_tables       | 716      |
| Created_tmp_files        | 0        |
| Delayed_insert_threads   | 0        |
| Delayed_writes           | 0        |
| Delayed_errors           | 0        |
| Flush_commands           | 1        |
| Handler_delete           | 27       |
| Handler_read_first       | 1534     |
| Handler_read_key         | 608840   |
| Handler_read_next        | 652228   |
| Handler_read_prev        | 164      |
| Handler_read_rnd         | 14143    |
| Handler_read_rnd_next    | 1133372  |
| Handler_update           | 90       |
| Handler_write            | 131624   |
| Key_blocks_used          | 6682     |
| Key_read_requests        | 2745899  |
| Key_reads                | 6026     |
| Key_write_requests       | 63925    |
| Key_writes               | 63790    |
| Max_used_connections     | 20       |
| Not_flushed_key_blocks   | 0        |
| Not_flushed_delayed_rows | 0        |
| Open_tables              | 64       |
| Open_files               | 128      |
| Open_streams             | 0        |
| Opened_tables            | 517      |
| Questions                | 118245   |
| Select_full_join         | 0        |
| Select_full_range_join   | 0        |
| Select_range             | 2300     |
| Select_range_check       | 0        |
| Select_scan              | 642      |
| Slave_running            | OFF      |
| Slave_open_temp_tables   | 0        |
| Slow_launch_threads      | 0        |
| Slow_queries             | 8        |
| Sort_merge_passes        | 0        |
| Sort_range               | 3582     |
| Sort_rows                | 16287    |
| Sort_scan                | 806      |
| Table_locks_immediate    | 82957    |
| Table_locks_waited       | 2        |
| Threads_cached           | 0        |
| Threads_created          | 793      |
| Threads_connected        | 1        |
| Threads_running          | 1        |
| Uptime                   | 1662790  |
+--------------------------+----------+

The list of variables and status information grows longer with each new release. Your version will probably have more than this, and you should read the latest documentation to see exactly what these extras do. A full explanation of those currently in use is given later in this chapter, in Table 2.

Most MySQL distributions come with four sample configuration files:

my-huge.cnf For systems with more than 1GB memory that are mostly dedicated to MySQL.

my-large.cnf For systems with at least 512MB memory that are mostly dedicated to MySQL.

my-medium.cnf For systems with at least 32MB memory dedicated entirely to MySQL or with at least 128MB on a machine that serves multiple purposes (such as a dual web/database server).

my-small.cnf For systems with less than 64MB memory where MySQL cannot take up too much of the resources.

These files can usually be found in /usr/share/doc/packages/MySQL/ (an RPM installation), /usr/local/mysql-max-4.x.x-platform-operating-system-extra/support-files/ (Unix binary installation), or C:\mysql\ (Windows).

Warning

On Windows, the .cnf extension can conflict with FrontPage and NetMeeting.

As a starting point, I suggest replacing your my.cnf file (or my.ini) with one of these configurations, choosing the configuration closest to the your needs.

Choosing the right configuration for your system will get you a large step of the way toward optimality, but to achieve optimal usage requires fine-tuning the configuration for your system and usage specifics. You’ll see some of the variables in the following sections.

Optimizing table_cache

The table_cache variable is one of the most useful variables to adjust. Every time MySQL accesses a table, if there is space in the cache, that table is placed there. It’s faster to access the table in memory than the table on disk. You can see whether you need to increase the value of your table_cache by examining the value of open_tables at peak times (one of the extended status values you saw with SHOW STATUS or mysqladmin variables). If you find that open_tables is at the same value as your table_cache, and the value of opened_tables (another extended status value) is increasing, you should increase the table_cache if you have enough memory.

Note

The number of open tables can be higher than the number of tables in your databases. MySQL is multithreaded, and there may be many queries running at a time, each of which may open a table.

Look at the following three scenarios, all during peak hours.

Scenario 1 This scenario is taken from a live server that’s not particularly busy:
table_cache - 512
open_tables - 103
opened_tables - 1723
uptime - 4021421 (measured in seconds)
It looks like the table_cache is set too high in this case. The server has been up for a long time (if the server had just come up you wouldn’t know if the table_cache would be reached soon or if the opened_tables would soon begin to increase). The number of opened tables is reasonably low, and the number of open tables is well below what it could be, considering that this is a peak time.

Scenario 2 This scenario is taken from a development server:
table_cache - 64
open_tables - 64
opened_tables - 431
uptime - 1662790 (measured in seconds)
Here, although the open_tables is at its maximum, the number of open_tables is reasonably low, considering that the server has been up a while. There is probably not much benefit to be gained from upping the table_cache.

Scenario 3 This scenario is taken from an underperforming live server:
table_cache - 64
open_tables - 64
opened_tables - 22423
uptime - 19538 
The table_cache in this instance is set too low. The open_tables is at its maximum, and the number of opened_tables is high, even though the uptime is less than six hours. If your system has spare memory available, you should increase the table_cache.

Warning

Don’t blindly set the table_cache to a high value. If you do not need a high value, keep the value of the table_cache to something reasonable. If it is set too high, you may run out of file descriptors, and consequently see unreliable performance or connections being refused.

Optimizing key_buffer_size

The key_buffer_size affects the size of the index buffers, which in turn affects the speed of index handling, in particular index reads. The higher the value, the more of the indexes MySQL can hold in memory, which is much faster to access than from disk. A suggested rule of thumb is to set it from between a quarter to half of the available memory on your server (if your server is dedicated to MySQL). You can get a good idea how to adjust the key_buffer_size by comparing the key_read_requests and key_reads status values. The ratio of key_reads to key_read_requests should be as low as possible, with 1:100 being about the highest acceptable limit (1:1000 is better, 1:10 is terrible). The key_reads value indicates how many times the key needs to be read from disk, which is what you want to avoid by setting the key buffer to as high a value as possible.

The following scenarios examine two possibilities.

Scenario 1 A healthy situation:

key_buffer_size - 402649088 (384M)
key_read_requests - 597579931
key_reads - 56188

Scenario 2 Alarm bells ringing:

key_buffer_size - 16777216 (16M)
key_read_requests - 597579931
key_reads - 53832731

Scenario 1 reflects a healthy situation. The ratio is over 1:10000, but alarm bells should be ringing in scenario 2, where the ratio is about a worrying 1:11. As a solution, you should increase the key_buffer_size to as much as the memory allows. A hardware upgrade is necessary if you don’t have enough memory to cater for this.

The ratio of key_writes to key_write_requests can also be a useful one to look at. It’s usually close to 1 if you do mainly inserts and updates of one record at a time, but if you often insert or update large volumes of data at a time, you would want this lower. Using INSERT DELAYED statements will also reduce this ratio.

Dealing with Too Many Connections

A common, and sometimes easily fixable, error that can occur when systems get too busy is the Too many connections error. When the number of threads_connected goes beyond the number of max_connections often, it’s time to make a change. If the queries are being handled smoothly, the solution can be as simple as increasing the value of max_connections.

Most applications should make use of persistent connections rather than ordinary connections (for example, in PHP, using the pconnect() function rather than the connect() function). Persistent connections remain open even after the query has finished running, which, on busy servers, means that the next query does not have to take any resources to connect again. Maintaining a large number of persistent, but unused, connections is less resource intensive than rapidly connecting, disconnecting, and reconnecting in quick succession.

Note

Persistent connections cannot be used in CGI mode, and are affected by the KeepAlive settings in the Apache web server.

This scenario examines a web server under heavy load that uses persistent connections:

max_connections - 250
max_used_connections - 210
threads_connected - 202
threads_running - 1

It may look like MySQL is wasting resources in this scenario, but in this case it’s simply that the 202 threads_connected are persistent, based upon the number of instances of the web server, and are hardly taking up any resources. Only one thread is actually running, so the database is probably not taking much strain. If the threads_connected gets ever closer to the max_connections without any problems, you may even want to increase the max_connections to avoid exceeding the connections limit. You can see how close the connections have ever gotten to maximum by looking at the max_used_connections value. If this is close, or equal to the max_connections, it’s certainly time to make allowances for an increase.

Personally, I’ve always found persistent connections to be better, though there are some reports that, because the MySQL connection overhead is much lighter than other databases (such as Oracle, where you have to use persistent connections in most cases), it makes little difference or even penalizes performance. The best suggestion is to test performance on your own systems.

Warning

When testing, make sure to test properly under load. There are some documents on the Web with all kinds of erroneous comparisons between persistent and nonpersistent connections.

In a system such as the previous scenario, a climbing threads_running value is often an indicator that the database server is not handling the load. Examining the process list can help identify the queries causing the blockage. What follows is a portion of the output from a database server just before it crashed. The number of threads_connected continued increasing until the server could handle it no longer and fell over. The processlist output helped to identify the problematic queries:

% mysqladmin processlist;
Id     User   Host      Db      Command Time State  Info
6464   mysql  websrv2…  news    Sleep   590
6482   mysql  websrv2…  news    Sleep   158
6486   mysql  websrv2…  news    Sleep   842
7549   mysql  websrv2…  news    Sleep   185
8126   mysql  websrv2...news    Sleep   349
9938   mysql  websrv2...news    Sleep   320
1696   mysql  websrv2...news    Sleep   100
4143   mysql  websrv2...news    Sleep   98
5071   mysql  websrv2...news    Sleep   843
5135   mysql  websrv2...news    Sleep   155
92707  mysql  zubat...  news    Sleep   530
93014  mysql  zubat...  news    Query   13  Locked select s_id from arts
                                                    where a_id =
                                                    'E232625'
93060  mysql  zubat...  news    Sleep   190
93096  mysql  zubat...  news    Query   171 Copying
                                            to tmp
                                            table   select distinct
                                                     arts.a_id,
                                                     arts.headline1,
                                                     nartsect.se_id,
                                                     arts.mdate,
                                                     arts.set_
93153  mysql  zubat...  news    Sleep   207
93161  mysql  zubat...  news    Query   30  Locked  SELECT DISTINCT
                                                     arts.a_id FROM
                                                     arts,keywordmap
                                                     WHERE arts.s_id in
                                                     (1) AND arts.
93165  mysql  zubat...  news    Query   36  Locked  select arts.name,
                                                     arts.headline1,
                                                     arts.se_id,
                                                     n_blurb.blurb,
                                                     slook.name as
                                                     sectna
93204  mysql  zubat...  news   Query    31  Locked  select arts.name,
                                                     arts.headline1,
                                                     arts.se_id,
                                                     n_blurb.blurb,
                                                     slook.name as
                                                     sectna
93205  mysql  zubat...  news   Query    156 Copying
                                            to tmp
                                            table    select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set_
93210  mysql  zubat...  news   Query    50  Locked   select arts.a_id,
                                                      arts.headline1,
                                                      nartfpg.se_id,
                                                      nartfpg.se_id,
                                                      arts.mdate,
                                                      nartfpg.p
93217  mysql  zubat...  news   Query    38  Locked   select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook.name as
                                                      sectna
93222  mysql  zubat...  news   Query    8   Locked   select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook2.name as
                                                      sectn
93226  mysql  zubat...  news   Query    39  Locked   select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook.name as
                                                      sectna
93237  mysql   zubat...  news  Query    33  Locked   select arts.a_id,
                                                      arts.headline1,
                                                      nartfpg.se_id,
                                                      nartfpg.se_id,
                                                      arts.mdate,
                                                      nartfpg.p
93244  mysql   zubat...  news  Query    99  Copying
                                             to tmp
                                             table   select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set_
93247  mysql   zubat...  news  Query    64  Locked   select s_id from
                                                      arts where
                                                      a_id='32C436'
93252  mysql   zubat...  news  Query    120  Copying
                                              to tmp
                                              table  select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set_
93254  mysql   zubat...  news  Sleep    47
93256  mysql   zubat...  news  Sleep    171
93257  mysql   zubat...  news  Query    176  Copying
                                              to tmp
                                              table  select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set_
93261  mysql   zubat...  news  Sleep    349
93262  mysql   zubat...  news  Sleep    1
93263  mysql   zubat...  news  Query    153  Copying
                                              to tmp
                                              table  select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set_
93267  mysql   zubat...  news  Query    27   Locked  select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook.name as
                                                      sectna
93276  mysql   zubat...  news  Query    29   Locked  select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook.name as
                                                      sectna
93278  mysql   zubat...  news  Query    183  Copying
                                              to tmp
                                              table  select distinct
                                                      arts.a_id,
                                                      arts.headline1,
                                                      nartsect.se_id,
                                                      arts.mdate,
                                                      arts.set
93280  mysql   zubat...  news  Sleep    36
93285  mysql   zubat...  news  Sleep    10
93284  mysql   zubat...  news  Query    49   Locked  select arts.name,
                                                      arts.headline1,
                                                      arts.se_id,
                                                      n_blurb.blurb,
                                                      slook.name as
                                                      sectna

Of the two web servers indicated in the list, websrv2 is behaving normally (all its threads are completed, and the connections are sleeping), but zubat has problem queries piling up.

There are many queries, and this is only a small portion of the whole list, but the query you should examine in this case is the one beginning like this:

select distinct arts.a_id, arts.headline1, nartsect.se_id, arts.mdate, arts.set ...

Notice how the status for all of these queries is Copying to tmp table, and the others were Locked. In this case, the problem was that a developer had made a change to the query so that it no longer used an index. Routinely examining the processlist output can help identify queries that are slow even before they cause something as drastic as the server to fail.

The slow_queries value is another good one you should examine. If it creeping up all the time, it probably indicates a problem. A well-tuned system should have as few slow queries as possible. Some complex joins may be unavoidably slow, but it’s more likely that slow queries are just badly optimized.

Optimizing the delayed_queue_size and back_log Variables

INSERT DELAYED frees the client but does not process the query immediately if there is anything else in the queue. Instead, MySQL waits for a gap so the inserts can be processed. The delayed_queue_size plays a role here. If the variable is set to its default value, 1,000, this means that after 1,000 delayed statements are in the queue, the client will no longer be freed and will have to wait. Having so many queued queries is not usually healthy, but if your system is one where a large number of inserts are made at a similar time, and you find clients having to wait even though you’re using INSERT DELAYED, you should increase the delayed_queue_size.

Another variable that helps manage short bursts of activity is the back_log variable. If a system receives a large number of connection requests in a short space of time, MySQL will count those it has not yet processed as part of the backlog. As soon as the back_log limit is reached, any more requests that would be queued are instead refused. If your system is one that gets large numbers of connection requests in short bursts, and you find that some are getting refused for this reason, you should increase the back_log value. If your system is just busy, and the requests are a constant stream, increasing the back_log on its own will not do much. It gives your server breathing space to handles short bursts, but it does not help with an overloaded system.

Optimizing the sort_buffer Variable

The sort_buffer variable has already been discussed as far as it pertains to speeding up the operations of myisamchk but it can also be a useful variable to fine-tune for everyday operations. If you do lots of sorting normally (frequently using ORDER BY on large tables, for example), the sort_buffer is a useful one to change. Each thread that performs a sort allocates a buffer of sort_buffer size. The my-huge.cnf configuration file (for systems with at least 1GB memory) defaults the sort_buffer to 256M for myisamchk, and 2M for mysqld. Although you want the mysqld figure to be able to handle large sorts, if you have many simultaneous connections performing ORDER BY clauses, because each is assigned a sort_buffer, you can run into memory problems.

Configuring InnoDB Tables

To get InnoDB tables running smoothly, it is even more critical to configure the variables correctly than with MyISAM tables. The most important is the innodb_data_file_path, which specifies the space available to the tables (data and indexes). It specifies one or more data files, as well as allocating a size to them. You should make the last data file auto extend (only the last data file can do this). So, instead of simply running out of space when all the space is taken, the auto extended data file will grow (in chunks of 8MB) to accommodate the extra data. For example:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

Here the two data files are placed on different disks (called disk1 and disk2). The data will first be placed in ibdata1, until the 900MB limit is reached, and then will be placed into ibdata2. Once the 50MB limit is reached, ibdata2 will automatically extend in 8MB chunks.

If a disk becomes physically full, you’ll need to add another data file on another disk, which requires some manual work for configuration. To do this, look at the physical size of the final data file and round it down to the nearest megabyte. Set this data file size specifically, and add the new data file definition. For example, if the disk2 specified previously fills up with ibdata2 at 109MB, you’ll use something like the following definition:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/
ibdata3:500M:autoextend

You’ll need to restart the server for the changes to take effect.

Introducing the mysqld Options

Table 1 describes the mysqld options.

Table 1: The mysqld Options

Option	Description
–ansi	MySQL not only has a number of extensions but also a number of differences to standard ANSI (standard SQL, as defined by the American National Standards Institute) behavior. If this is set, MySQL will run in ANSI mode (the changes this causes are discussed later in this chapter in the section "Running MySQL in ANSI Mode").
-b, –basedir=path	The path to the base directory or the MySQL installation directory. Other paths are usually taken relative to this.
–big-tables	Allows large result sets by saving all temporary sets on file when memory is not sufficient.
–bind-address=IP	The Internet Protocol (IP) address or hostname to which to bind MySQL.
–character-sets-dir=path	The directory where the character sets are located.
–chroot=path	For security purposes, you can start MySQL in a chroot environment with this option. This causes MySQL to run in a subset of the directories, hiding the full directory structure. This does, however, limit the usage of LOAD DATA INFILE and SELECT … INTO OUTFILE.
–core-file	This option causes a core file to be written if mysqld dies unexpectedly. Some systems may require you to specify a –core-file-size. Some systems may also not write a core file if the –user option is used.
-h, –datadir=path	Path to the data directory.
–debug[...]=	If MySQL is configured with –with-debug, this option can be used to generate a trace file of what mysqld does.
–default-character-set=charset	Sets the default character set (the default is latin1).
–default-table-type=type	Sets the default table type (tables of this type are created if no table type is specified in the CREATE statement). By default, this will be MyISAM.
–delay-key-write-for-all-tables	With this option, MySQL does not flush the key buffers between writes for any MyISAM table.
–des-key-file=filename	The default keys are read from this file. This is used by the DES_ENCRYPT() and DES_DECRYPT() functions.
–enable-external-locking	This option enables system locking. It should not be used on systems where the locked daemon does not work fully. (This applied to Linux, although this may no longer be the case with newer versions.)
–enable-named-pipe	On Windows NT/2000/XP, this option enables support for named pipes.
-T, –exit-info	A bit mask of different flags used in debugging. Not suggested you use this unless you know what you’re doing!
–flush	This option ensures MySQL flushes all changes to disk after each SQL statement. Usually the operating system handles this. You should not need to use this unless you’re having problems.
-?, –help	Displays a help list and exits.
–init-file=file	Tells MySQL to execute SQL statements contained in this file when it startsup.
-L, –language=…	This option sets the language to be used for client error messages. Can be the language or the full path to the language file.
-l, –log[=file]	Connections and queries will be logged to the specified file.
–log-isam[=file]	This option logs all changes to MyISAM or ISAM files to the specified file (only used when debugging these table types).
–log-slow-queries[=file]	Logs all queries that take longer than the value of the variable long_query_time (in seconds) to execute to the slow query log.
–log-update[=file]	Logs all updates to the specified update log. Instead use –log-bin.
–log-bin[=file]	Logs all updates to the specified binary update log.
–log-long-format	Logs more information. If the slow query log is being used (–log-slow-queries), any queries that do not use an index are logged there as well.
–low-priority-updates	If this option is used, all inserts, updates, and deletes will have a lower priority than selects. Where you don’t want this to apply to all queries, you can use SET OPTION SQL_LOW_PRIORITY_UPDATES=1 to apply it to a specific thread or LOW_PRIORITY … to apply it to a specific INSERT, UPDATE, or DELETE query.
–memlock	Locks mysqld into memory. This option is only available if your system supports the mlockall() function (as Solaris does). You’d normally only use this if the operating system is having problems and mysqld is swapping to disk. You can see if –memlock has been used by looking at the value of the locked_in_memory variable.
–myisam-recover [=option[,option...]]]	The available options are DEFAULT, BACKUP, FORCE, QUICK, or "". If this is set to anything but "", when MySQL starts it will check tables to see if they are marked as crashed or not closed properly. If so, it will run a check on the table and attempt to repair corrupted tables. If the BACKUP option is used, MySQL will create a backup copy of the .MYD data file if any changes are made during the course of the repair (giving it the extension .BAK). The FORCE option forces the repair even if data is to be lost, and the QUICK option does not check the rows if there are no delete blocks in the data. Allerrors will be noted in the error log, so you can see what happened. Setting the BACKUP and FORCE options together allow MySQL to recover automatically from many problems (with the backup in case things go wrong). DEFAULT is the same as not giving any options.
–pid-file=path	Specifies the path to the pid (process id) file.
-P, –port=…	The port number that MySQL uses to listen for TCP/IP connections.
-o, –old-protocol	Specifies that MySQL use the ancient 3.20 protocol for compatibility with some equally ancient clients.
–one-thread	Specifies that MySQL only use one thread. You only want to use this for debugging!
-O, –set-variable var=option	Sets a variable to allow you to optimize it. The full list of variables follows this table, in Table 2.
–safe-mode	Skips some optimizing stages. This option implies the –skip-delay-key-write option.
–safe-show-database	Not used in any but the earliest versions of MySQL 4. If set, users who do not have any privileges having anything to do with a database will not see that database listed when they perform a SHOW DATABASES statement (the SHOWDATABASES privilege removes the need for this).
–safe-user-create	This option adds to security by not allowing users to create new users (with the GRANT statement) unless they have INSERT privilege on the mysql.user table or one of the columns in that table.
–skip-concurrent-insert	Nullifies concurrent inserts (where selects and inserts can be performed at the same time on optimized tables). You should not need to do this unless debugging.
–skip-delay-key-write	Causes MySQL to ignore the delay_key_write option for all tables.
–skip-grant-tables	This option starts MySQL without the privilege tables (giving everyone fullaccess). Never use this unless you have to (such as when you, as root, have forgotten the password). Once you’ve finished, run mysqladmin flush-privileges or mysqladmin reload to start using the privilege tables again.
–skip-host-cache	Hostnames are usually cached, but you can force MySQL to query the DNS server for every connect instead. This will slow down connection speeds.
–skip-external-locking	Disables system locking. This has important consequences for myisamchk.
–skip-name-resolve	MySQL does not resolve hostnames, so all Host column values in the privilege tables must be a specific IP (or localhost). Hostnames are not resolved. This option can improve connection speeds if you have many hosts or slow DNS.
–skip-networking	This option causes MySQL to allow only local connections. It will not listen for TCP/IP (Transmission Control Protocol/Internet Protocol) connections at all. This is a good security measure if possible.
–skip-new	MySQL uses ISAM as a default table type and does not use some oftheoptions that were new in version 3.23. It also implies –skip-delay-key-write. This option should not be needed anymore,unless its behaviorchanges.
–skip-symlink	This option ensures that one cannot delete or rename files to which a symlinked file in the data directory points. You should use it if you are not using symlinks as a security measure to ensure no one can drop or rename a file outside of the mysqld data directory.
–skip-safemalloc	This options speeds up performance as it avoids checking for overruns for each memory allocation and memory freeing (these checks are done when MySQL is configured with –with-debug=full).
–skip-show-database	If set, the SHOW DATABASES statement does not return results unless the client has the PROCESS privilege. (The SHOW DATABASES privilege, introduced in early versions of MySQL 4, removes the need for this.)
–skip-stack-trace	Does not use stack traces (which is useful if running mysqld under a debugger). Some systems require this option to get a core file.
–skip-thread-priority	Disables the use of thread priorities for a faster response time.
–socket=path	The path to the socket file for use for local connections (instead of the default, usually /tmp/mysql.sock).
–sql-mode=option[,option[, option...]]	The various differences between ANSI standard and MySQL can be set using these options. They are REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY, or "" (to reset). Using them all is the same as the –ansi option. See the discussion later in this chapter, "Running MySQL in ANSI Mode," for what each difference is.
–temp-pool	This option should only be needed when an operating system leaks memory when creating large numbers of new files with different names (as happened with some versions of Linux). Instead, MySQL will use a small set of names for temporary files.
–transaction-isolation= { READ-UNCOMMITTED \| READ-COMMITTED \| REPEATABLE-READ \| SERIALIZABLE }	Sets the default transaction isolation level.
-t, –tmpdir=path	The directory used to store temporary files and tables. It’s useful to make itdifferent to your usual temporary space if that is too small to hold temporary tables.
-u, –user= [user_name \| userid]	Supplies a username to run MySQL as. When starting mysqld as root, this option must be used.
-V, –version	Displays version information and exits.
-W, –warnings	Warnings will be displayed in the error file.

Exploring the mysqld Variables

When running mysqladmin variables from the command line, or SHOW VARIABLES from the mysql prompt, a long list of variables will be displayed. Most often, they relate to an option that you can set in the configuration file. Table 2 explains the variables displayed. Depending on your system setup and version, you may not have all these options—or, more likely, as MySQL keeps adding to them, you may have more.

Table 2: The mysqld Variables

Exploring All the Status Variables

Variable	Description
ansi_mode	MySQL not only has a number of extensions but also a number of differences to standard ANSI behavior. If this is set, MySQL will run in ANSI mode (the changes this causes are discussed later in this chapter, in the section "Running MySQL in ANSI Mode").
back_log	The number of queued connection requests MySQL can have waiting before it starts refusing connections. This is the same as the size of the listen queue for incoming TCP/IP connections, which is also limited by the operating system. The lower of back_log and the operating system limit will apply. See the operating system documentation (for example, man listen on Unix) for more information.
basedir	The path to the base directory or the MySQL installation directory.
bdb_cache_size	The size of the buffer allocated cache data and indexes for BDB tables. If yoursystem does not use BDB tables, use the –skip-bdb option to avoid wasting memory.
bdb_lock_detect	The Berkeley lock detect, which can be one of DEFAULT, OLDEST, RANDOM, or YOUNGEST.
bdb_log_buffer_size	The size of the buffer for BDB logs. If your system does not use BDB tables, use the –skip-bdb to avoid wasting memory.
bdb_home	The base directory for BDB tables, which should be the same as –datadir.
bdb_max_lock	The maximum number of locks that can be applied to a BDB table. Increase this if your transactions are likely to be long or your queries require many rows to be examined. Errors such as bdb: Lock table is out of available locks or Got error 12 from … indicate a need to increase the value. The default value is 10000.
bdb-no-recover	If set, MySQL does not start BDB in recover mode. Usually you should only set this if there is corruption in the BDB logs that prevents a successful startup.
bdb-no-sync	If set, MySQL will not synchronously flush the logs.
bdb_logdir	The directory containing the BDB logs.
bdb_shared_data	If set, MySQL starts BDB in multiprocess mode, meaning that DB_PRIVATE isnot used.
bdb_tmpdir	The directory location for BDB temporary files.
binlog_cache_size	The cache size for transactions to be written to the binary log. If the transactions are large and take more than the default 32KB cache, you should increase this.
character_set	The character set to use when no other is specified (usually latin1).
character_sets	The full list of supported character sets. If you are compiling MySQL from source and know you are never going to use them, you can compile MySQL not to support the extra character sets.
concurrent_inserts	If active (by default it is), you can insert into MyISAM tables at the same time as querying, giving a performance gain (as long as the table contains no gaps from previously deleted records. You can ensure this by regularly optimizing the tables). The –safe or –skip-new options nullify this.
connect_timeout	The time in seconds MySQL waits for packets before it times out with a Bad handshake. The default is 5 seconds. This helps to prevent denial of service attacks where many bad connection attempts are made in order to prevent legitimate users from connecting.
datadir	The directory where the data is stored.
delay_key_write	If active (the default), MySQL will not flush the key buffer for a table on every index update for tables with the DELAY_KEY_WRITE option. Rather, it will only be flushed when the table is closed. This increases the speed of key writes, but it also increases the chance of corruption, so you should regularly check the tables. You can specify that the DELAY_KEY_WRITE option is default for all tables by using the –delay-key-write-for-all-tables option. The –safe or –skip-new options nullifies this option.
delayed_insert_limit	After inserting delayed_insert_limit rows, MySQL checks to see if there are any SELECT statements pending, and processes these, before continuing with the remaining INSERT DELAYED statements. The default is 100 rows.
delayed_insert_timeout	This determines how long an INSERT DELAYED thread should wait for INSERT statements before terminating.
delayed_queue_size	The number of rows allocated for the INSERT DELAYED queue. If this limit is reached, clients performing INSERT DELAYED will wait until there is space.
flush	If set, MySQL flushes all changes to disk after each SQL statement. Usually the operating system handles this. Defaults to OFF as you should not need to use this unless you’re having problems.
flush_time	The time in seconds between automatic flushes (where the tables are closed and synchronized to disk). This is usually set to 0 unless you’re running a system with very little resources or Windows 95/98/Me.
ft_min_word_len	The minimum length of words to be included in a FULLTEXT index. After changing this value, any FULLTEXT indexes will need to be rebuilt. The default is 4.
ft_max_word_len	The maximum length of words to be included in a FULLTEXT index. After changing this value, any FULLTEXT indexes will need to be rebuilt. The default is 254.
ft_max_word_len_sort	Words of this length or less are inserted into the FULLTEXT index with the fast index re-creation when the index is rebuilt. Words longer than this length are inserted into the index the slow way. You are unlikely to want to change the default (20) unless the words in your index are of unusual average lengths. If the value is set too high, the process will be slower as the temporary files will be bigger, and fewer keys will be in one sort block. If the value is too low, too many words will be inserted the slow way.
ft_boolean_syntax	The list of operators supported by the MATCH … AGAINST(… IN BOOLEAN MODE) statement (+ -><()~*:""&\|).
have_innodb	Set to YES if MySQL supports InnoDB tables or DISABLED if the –skip-innodb option is used.
have_bdb	Set to YES if MySQL supports BDB tables or DISABLED if the –skip-bdb option is used.
have_raid	Set to YES if MySQL supports the RAID (Redundant Array of Inexpensive Disks) option.
have_openssl	Set to YES if MySQL supports SSL (Secure Sockets Layer) encryption between client and server.
init_file	The name of a file containing SQL statements to be executed when the server starts (by default none is specified).
innodb_data_home_dir	The home directory for InnoDB data files and used as the common part of the path. If this is not mentioned, it will be the same as datadir. By setting this to an empty string, you can use absolute file paths in innodb_data_file_path.
innodb_data_file_path	The paths to individual data files and their sizes. The innodb_data_home_dir part of the path is added to this to give the full path. File sizes are specified in megabytes (M) or gigabytes (G). Can be larger than 4GB on operating systems that support big files, and the sum of the sizes should be at least 10MB.
innodb_mirrored_log_groups	Specifies the number of identical copies of log groups to keep for the database. Currently this should be 1.
innodb_log_group_home_dir	Specifies the directory path to InnoDB log files.
innodb_log_files_in_group	Specifies the number of log files in the log group. 3 is the suggested value (logsare written in rotation).
innodb_log_file_size	Specifies the size in megabytes of each log file in a log group. Suggested values are from 1MB to 1/innodb_log_files_in_group of the innodb_buffer_pool_size. A high value saves disk input/output (I/O) because less flush activity is needed, but slows recovery after a crash. The total size of the log files should not be more than 4GB on 32-bit computers.
innodb_log_buffer_size	Specifies the size of the buffer used to write logs. A suggested value is 8MB. The larger the buffer, the less disk I/O because then transactions do not need to be written to disk until they are committed.
innodb_flush_log_at_trx_commit	If set, logs are flushed to disk as soon as the transaction is committed (and are therefore permanent and able to survive a crash). This should not normally beset to anything but ON if your transactions are important. You can set this to OFF if performance is more critical and you want to reduce disk I/O at the cost of safety.
innodb_log_arch_dir	Specifies the directory where the logs are to be archived. Currently this should be the same as innodb_log_group_home_dir because log archiving is not currently used.
innodb_log_archive	If set, InnoDB log files will be archived. Currently, MySQL recovers using its own log files, so this should be set to OFF.
innodb_buffer_pool_size	Specifies the size in bytes of the memory buffer used to cache table indexes and data. The larger this is, the better the performance because less disk I/O is then required. Up to 80 percent of memory is suggested on a dedicated database server because any larger may cause operating system paging.
innodb_additional_mem_pool_size	Specifies the size in bytes of a memory pool used to store information about the internal data structures. 2MB is a possibility, but if you have many tables, make sure there is enough memory allocated; otherwise MySQL will use operating system memory (you can see the warnings in the error log if this occurs and increase the value).
innodb_file_io_threads	The number of file I/O threads in InnoDB. The suggested value is 4, but it is suspected Windows may benefit from a higher setting.
innodb_lock_wait_timeout	The time in seconds an InnoDB transaction waits for a lock before rolling back. InnoDB detects deadlocks automatically in its own lock tables, but if they come from outside (such as a LOCK TABLES statement), deadlock may arise, in which case this value is used.
innodb_flush_method	Flushing method. The default is fdatasync, and the alternative is O_DSYNC. Usually fdatasync is faster, though on some versions of Linux and Unix it has proven to be slower.
interactive_timeout	The time in seconds that the server waits for any activity on an interactive connection (one using the CLIENT_INTERACTIVE option when connecting) before closing it. The wait-timeout option applies to ordinary connections. The default is 28800.
join_buffer_size	The size in bytes of the buffer used for full joins (joins where no index is used).The buffer is allocated to each full join. Increasing this will make full joins faster, although the best way to speed up a join is by adding appropriate indexes.
key_buffer_size	The size in bytes of the buffer used for index blocks. This is discussed fully inthe section "Optimizing Key Buffer Size."
language	The location of the language file used for error messages.
large_file_support	ON if MySQL was compiled with support for large files. The default is ON.
locked_in_memory	ON if MySQL was locked in memory (in other words, if mysqld was started with the –memlock option). The default is OFF. You normally only want this ON if the operating system is having problems and mysqld is swapping to disk.
log	ON if logging of all queries is enabled.
log_update	ON if update logging is enabled (you should rather use the binary log for this).
log_bin	ON if binary update logging is enabled.
log_slave_updates	ON if updates from a slave are logged.
long_query_time	The time in seconds that defines a slow query. Queries that take longer than this will cause the slow queries counter to be incremented and will be logged in the slow query log file if slow query logging is enabled.
lower_case_table_names	Set to 1 if table names are stored in lowercase and are case insensitive. The default is 0.
max_allowed_packet	The maximum allowable size in bytes of one packet of data. The message buffer is initialized to the size specified by net_buffer_length, but it can grow up to this size. If you use large BLOB or TEXT columns, set this to the size of the largest column.
max_binlog_cache_size	The largest amount of memory in bytes a multistatement transaction can usewithout throwing the error: "Multistatement transaction required more than max_binlog_cache_size bytes of storage.
max_binlog_size	As soon as the current binary log exceeds this size, the logs will be rotated and a new one created.
max_connections	The maximum number of connections allowed. See the earlier discussion, "Dealing with Too Many Connections."
max_connect_errors	The maximum number of times a host can attempt a connection that becomes interrupted before the host is blocked from any further connections. This limit is imposed to reduce the possibility of denial of service attacks. Hosts can be unblocked by running FLUSH HOSTS.
max_delayed_threads	The maximum number of threads that can handle INSERT DELAYED statements. Once this is reached, further INSERT statements will be ordinary inserts and not make use of the DELAYED attribute.
max_heap_table_size	Maximum size in bytes that HEAP tables can become.
max_join_size	Joins that MySQL determines will return more rows than this limit will return an error. This prevents users from accidentally (or maliciously) running huge queries that could take return many millions of rows and take up too many resources.
max_sort_length	When sorting BLOB or TEXT fields, only up to this number of bytes will be used. For example, if this is set to 1024, only the first 1024 characters will beused in sorting.
max_user_connections	Determines the maximum number of connections a single user can haveactive. The default, 0, indicates there is no limit (except for max_connections).
max_tmp_tables	The maximum number of temporary tables a client can keep open at the same time. (At the time of this writing, this is not used; check the latest documentation.)
max_write_lock_count	If this many consecutive write locks occur, MySQL will allow a number of read locks to run.
myisam_bulk_insert_tree_size	When MySQL inserts in bulk (for example, LOAD DATA INFILE…), it uses a tree-like cache to speed up the process. This is the maximum size in bytes ofthe cache for each thread. The default is 8MB, and setting it to 0 disables this feature. The cache is only used when adding to a table that is not empty.
myisam_recover_options	The available options are DEFAULT, BACKUP, FORCE, QUICK, or OFF. If this is set to anything but OFF, when MySQL starts it will check tables to see if they are marked as crashed or not closed properly. If so, it will run a check on the table and attempt to repair corrupted tables. If the BACKUP option is used, MySQL will create a backup copy of the .MYD data file if any changes are made during the course of the repair (giving it the extension .BAK). The FORCE option forces the repair even if data is to be lost, and the QUICK option does not check the rows if there are no delete blocks in the data. All errors will be noted in the error log, so you can see what happened. Setting the BACKUP and FORCE options together allow MySQL to recover automatically from many problems (with the backup in case things go wrong).
myisam_sort_buffer_size	The size in bytes of the buffer allocated when sorting or repairing an index.
myisam_max_extra_sort_file_size	When MySQL creates an index, it subtracts the key cache size from the size of the temporary table it would use with fast index creation. If the difference is larger than this amount (specified in megabytes), MySQL uses the key cache method.
myisam_max_sort_file_size	The maximum size (in megabytes) of the temporary file MySQL creates when creating or repairing indexes. If this size would be exceeded, MySQL uses the slower key cache method to create or repair the index.
net_buffer_length	The size in bytes that the communication buffer is set to between queries. Tosave memory is systems with low memory, set this to the expected length of SQL statements sent by clients. It is automatically enlarged to the size of max_allowed_packet if the statement exceeds this length.
net_read_timeout	Time in seconds that MySQL waits for data from a connection before aborting the read. If no data is expected, the net_write_timeout is used, and slave_net_timeout is used for the master/slave connection.
net_retry_count	The number of times to retry a read on a communication port before aborting.
net_write_timeout	The number of seconds to wait for a block to be written to a connection beforeaborting.
open_files_limit	MySQL uses this value to reserve file descriptors. Increase this if you get the error Too many open files. Usually this is set to 0, in which case MySQL uses the larger of max_connections5 or max_connections + table_cache2.
pid_file	The path to the pid file.
port	The port number that MySQL uses to listen for TCP/IP connections.
protocol_version	The protocol version that MySQL uses.
record_buffer	MySQL allocates a buffer of this size in bytes for each thread that performs a sequential scan (where records are read in order, one after another). If you do many sequential scans, you may want to increase this value.
record_rnd_buffer	A buffer of this size in bytes is allocated when reading rows in nonsequential order (for example, after a sort), and rows read through this avoid disk seeks. If not set, it will be the same as the record_buffer.
query_buffer_size	The initial size in bytes allocated to the query buffer. It should be sufficient for most queries; otherwise it should be increased.
query_cache_limit	The limit in bytes for the query cache. Results larger than this will not be cached. The default is 1MB.
query_cache_size	The size in bytes allocated to the query cache (where results are stored from old queries). 0 indicates the cache is disabled.
query_cache_startup_type	This can be one of 0, 1, or 2. 0 (off) means MySQL does not cache or retrieve results. 1 (on) means MySQL caches all results unless they come with SQL_NO_CACHE. 2 (demand) means that only queries with SQL_CACHE are cached.
safe_show_database	Not used in any but the earliest versions of MySQL 4. If set, users who do not have any privileges having anything to do with a database will not see that database listed when they perform a SHOW DATABASES statement (the SHOW DATABASES privilege removes the need for this).
server_id	The ID of the server. Important for replication purposes to identify servers.
skip_external_locking	Disables system locking if ON. Has important consequences for myisamchk.
skip_networking	Is ON if MySQL allows only local connections.
skip_show_database	If set, the SHOW DATABASES statement does not return results unless the client has the PROCESS privilege. (The SHOW DATABASES privilege, introduced in early versions of MySQL 4, removes the need for this.)
slave_net_timeout	Time in seconds MySQL waits for more data from a master/slave connection before the read aborts.
slow_launch_time	Time in seconds before the launch of a thread increments the slow_launch_threads counter.
socket	The path to the Unix socket used by the server.
sort_buffer	The size in bytes allocated to the buffer used by sorts. See the discussion earlier in this chapter titled "Optimizing the sort_buffer variable."
table_cache	The number of open tables for all threads. See the discussion earlier in this chapter titled "Optimizing table_cache."
table_type	The default table type (usually MyISAM).
thread_cache_size	The number of threads kept in a cache for reuse. New threads are taken from the cache if any are available, while a client’s threads are placed in the cache when disconnecting if space is available. If you have lots of new connections, you can increase this value to improve performance. Systems with good thread implementation normally don’t benefit much from this. You can see its efficiency by comparing the Connections and Threads_created status variables.
thread_concurrency	On Solaris systems, MySQL uses this value to determine whether to call thethr_setconcurrency() function, which assists the threads system inknowing the number of threads that should be running at the same time.
thread_stack	The size in bytes of the stack for each thread. The behavior of the crash-me benchmark depends on this value.
timezone	The server time zone.
tmp_table_size	The maximum size for a temporary table in memory. If it becomes larger, itwill automatically become a MyISAM table on disk. If you perform lots of queries that result in large temporary tables (such as with complex GROUP BY clauses) and have enough memory, increase this value.
tmpdir	The directory used to store temporary files and tables.
version	The server version number.
wait_timeout	The time in seconds MySQL waits for activity on a connection before closing it. The interactive_timeout applies to interactive connections.

Status variables are reset every time the server restarts. They allow you to monitor the behavior of your server and to identify potential bottlenecks, problems, and improvements you can make. Table 3 contains a comprehensive list.

Table 3: The MySQL Status Values

Value	Description
Aborted_clients	Indicates the number of connections that were aborted because for some reason the client did not close the connection properly. This could happen ifthe client does not call the mysql_close() function before exiting, the wait_timeout or interactive_timeout limits have been exceeded, orthe client closed in the middle of a transfer.
Aborted_connects	Indicates the number of failed attempts to connect to MySQL server that failed. This could occur because the client tried to connect with the wrong password, does not have permission to connect, takes longer than connect_timeout seconds to obtain a connect packet, or the packet doesn’t contain the correct information.
Bytes_received	The number of bytes that has been received from all clients.
Bytes_sent	The number of bytes that has been sent to all clients.
Com_[statement]	One of these variables exists for each kind of statement. The value indicates the number of times this statement has been executed.
Connections	The number of attempts to connect to the MySQL server.
Created_tmp_disk_tables	The number of implicit temporary tables on disk that were created while executing statements.
Created_tmp_tables	The number of implicit temporary tables in memory that were created whileexecuting statements.
Created_tmp_files	The number of temporary files created by mysqld.
Delayed_insert_threads	The number of delayed insert handler threads currently in use.
Delayed_writes	The number of records written by an INSERT DELAYED statement.
Delayed_errors	The number of records written by an INSERT DELAYED where an error occurred. The most usual error is a duplicate key.
Flush_commands	The number of FLUSH statements executed.
Handler_commit	The number of internal COMMIT commands.
Handler_delete	The number of times a row was deleted from a table.
Handler_read_first	The number of times the first entry of an index was read, usually indicating a full index scan (for example, assuming indexed_col is indexed, the statement SELECT indexed_col from tablename results in a full index scan).
Handler_read_key	The number of requests where an index is used when reading a row. You’ll want this to increase quickly, as it indicates your queries are using indexes.
Handler_read_next	The number of requests to read the next row in order of an index. This would get increased if you are doing a full-index scan or querying an index based ona range constant.
Handler_read_prev	The number of requests to read the previous row in index order. This would be used by an SELECT fieldlist ORDER BY fields DESC type of statement.
Handler_read_rnd	The number of requests to read a row based on a fixed position. Queries that require the results to be sorted would increment this counter.
Handler_read_rnd_next	The number of requests to read the next row in the data file. You would usually not want this to be high because it means that queries are not making use of indexes and have to read from the data file.
Handler_rollback	The number of internal ROLLBACK commands.
Handler_update	The number of requests to update a record in a table.
Handler_write	The number of requests to insert a record into a table.
Key_blocks_used	The number of blocks used in the key cache.
Key_read_requests	The number of requests causing a key block to be read from the key cache. The Key_reads:Key_read_requests ratio should be no higher than 1:100 (i.e., 1:10 is bad).
Key_reads	The number of physical reads causing a key block to be read from disk. The Key_reads:Key_read_requests ratio should be no higher than 1:100 (again, 1:10 is bad).
Key_write_requests	The number of requests causing a key block to be written to the cache.
Key_writes	The number of times there has been a physical write of a key block to disk.
Max_used_connections	The maximum number of connections in use at any one time. See the connections discussion earlier in this chapter ("Dealing with Too ManyConnections").
Not_flushed_key_blocks	The number of key blocks in the key cache that have changed but have not yetbeen flushed to disk.
Not_flushed_delayed_rows	The number of records currently in INSERT DELAY queues waiting to be written.
Open_tables	The number of tables that are currently open. See the table cache discussion earlier in this chapter ("Optimizing table_cache").
Open_files	The number of files that are currently open.
Open_streams	The number of streams that are open. These are mostly used for logging.
Opened_tables	The number of tables that have been opened. See the table cache discussion earlier in this chapter ("Optimizing table_cache").
Qcache_queries_in_cache	The number of queries in the cache.
Qcache_inserts	The number of queries added to the cache.
Qcache_hits	The number of times the query cache has been accessed.
Qcache_not_cached	The number of queries that were not cached (due to being too large, or because of the QUERY_CACHE_TYPE).
Qcache_free_memory	The amount memory still available for the query cache.
Qcache_total_blocks	The total number of blocks in the query cache.
Qcache_free_blocks	The number of free memory blocks in the query cache.
Questions	Total number of queries initiated.
Rpl_status	The status of failsafe replication. (This is only used by later versions of MySQL4.)
Select_full_join	The number of joins that have been performed without using indexes. You donot want this to be high.
Select_full_range_join	The number of joins performed using a range search on the reference table.
Select_range	The number of joins performed using ranges on the first table (a large number here is normally fine).
Select_range_check	The number of joins performed without indexes that checked for indexes after each row.
Select_scan	The number of joins performed that did a full scan of the first table.
Slave_open_temp_tables	The number of currently open temporary tables held by a slave.
Slave_running	ON or OFF. Is ON if the server is a slave connected to a master.
Slow_launch_threads	The number of threads that took more than slow_launch_time to create.
Slow_queries	The number of queries that took more than long_query_time.
Sort_merge_passes	The number of merge passes performed during a sort. If this becomes too large, you should increase the sort_buffer.
Sort_range	The number of sorts performed with ranges.
Sort_rows	The number of sorted rows.
Sort_scan	The number of sorts performed by scanning the table.
ssl_[variables]	Contents of various variables used by SSL. This is not implemented in early versions of MySQL 4.
Table_locks_immediate	The number of times a table lock was immediately acquired.
Table_locks_waited	The number of times a table lock was not immediately acquired. A high value is usually a symptom of performance problems. You’ll need to optimize by improving your queries and indexes, using another table type, splitting your tables, or using replication.
Threads_cached	The number of threads currently in the thread cache.
Threads_connected	The number of currently open connections.
Threads_created	The number of threads created to handle connections.
Threads_running	The number of active (not sleeping) threads.
Uptime	The time in seconds the server has been running.

Tagged with: mysql optimizing

Jul 28

Mysql Security,part 4

By jason Mysql Server 1 Comment »

1.Summary

The MySQL user management mechanism is powerful, flexible, and often misused. The mysql database contains the various permission tables that allow access to be controlled based on user, host, and the action being performed. You can update the tables directly with SQL statements (in which case flushing the tables activates the changes) or through the more convenient GRANT and REVOKE statements.

The first task in a new installation should be to issue a root password. Until then, anyone can connect as root and have full access to everything. You can use mysqladmin, the SET statement, or GRANT to do this.

MySQL allows SSL connections for added security. This is not installed by default because it has performance implications.

You also learned some general principles for securing your data:

Never issue a user the root password. They should always be connecting with another username.
Never give anyone access to the user table, even for reading. Just viewing the encrypted password is enough to potentially allow a user full access.
Always issue the minimum permissions you can. Issuing minimum permissions means that the user table contains N for all columns.
For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.
Ensure you cannot connect as the root user without a password from any server.
Passwords should never be stored in plain text and should not be dictionary words.
Check the user privileges every now and again and make sure that no one has granted anyone else inappropriate privileges.

2.Application Security

Most security holes are caused by poor applications. There are a number of common pitfalls to avoid:

Never trust user data. Always verify any data entered by a user.
Inserting quotations in a website form is a common cause of breakages. For example, an application takes a username and password and runs a query such as SELECT * FROM passwords WHERE username=’$username’ AND password=’$password’. Poorly designed applications will allow $password to contain something like aaa’;DELETE FROM passwords;. MySQL parsing the query thinks the single quote after the three a‘s is the end of the query and then happily performs the next query. Most languages have simple functions to avoid this, escaping any quotations in the string, such as mysql_real_escape_ string() in C or addslashes() in PHP.
Check the size of the data. A complex calculation may work well with a single digit number, but a 250-digit number passed by a user may cause the application to crash.
Remove any special characters from strings passed to MySQL.
Use quotes around numbers as well as strings.

3.System Security

By default, MySQL runs as its own user on Unix. The user and group are mysql. Never be tempted to allow anyone access to the system as the mysql user—it should only be for the database itself. MySQL also creates a separate directory where it places data files. This directory is accessible only to the mysql user. The default settings have been chosen for a reason; keep to these principles:

Separate the data directory.
Secure the data directory (no one else should be able to read, much less write, in the MySQL data directory).
Run MySQL as its own user.

4.Security Issues with LOAD DATA LOCAL

Restoring from a client machine may be convenient, but it does come with a security risk. Someone could use LOAD DATA LOCAL to read any files to which the user they are connecting as has access. They could do this by creating a table and reading from the table after they have loaded the data. If they were connecting as the same user as the web server, and have the right access to run queries, this becomes dangerous. By default, MySQL allows LOAD DATA LOCAL. To prevent this and disallow all LOAD DATA LOCAL, start the MySQL server with the –local-infile=0 option. You could also compile MySQL without the –enable-local-infile option.

Tagged with: mysql • security

Jul 28

Mysql Security,part 3

By jason Mysql Server 2 Comments »

SSL Connections

The connection between the client and the server is by default not encrypted. In most network architectures, this would not be a risk because the connection between the database client and server is not public. But there are instances where data needs to be moved over public lines, and an unencrypted connection potentially allows someone to view the data as it is moved.

MySQL can be configured to support SSL connections, although this does impact on performance. To do this, perform the following steps:

Install the openssl library, which can be found at www.openssl.org/.
Configure MySQL with the –with-vio –with-openssl option.

If you need to check whether an existing installation of MySQL supports SSL (or whether your installation has worked), check to see whether the variable have_openssl is YES:

mysql> SHOW VARIABLES LIKE '%ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+
1 row in set (0.00 sec)

Once SSL is supported, you can make use of it with various grant options (see Table 14.5).

Table 1: SSL Grant Options

Option	Description
REQUIRE SSL	The client must connect with SSL encryption.
REQUIRE X509	The client has to have a valid certificate to connect.
REQUIRE ISSUER cert_issuer	The client has to have a valid certificate issued by cert_issuer to connect.
REQUIRE SUBJECT cert_subject	The client has to have a valid certificate with the subject cert_subject.
REQUIRE CIPHER cipher	The client has to make use of the specified cipher.

REQUIRE SSL is the least restrictive of the SSL options. SSL encryption of any kind is acceptable. This would be useful where you don’t want to send plain text, but simple encryption of the connection is sufficient:

mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost IDENTIFIED
 BY "g00r002b" REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

REQUIRE X509 is the same, but it is marginally more restrictive because the certificate must be a valid one:

mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost IDENTIFIED
 BY "g00r002b" REQUIRE X509;
Query OK, 0 rows affected (0.01 sec)

REQUIRE ISSUER and REQUIRE SUBJECT are more secure because the certificate has to come from a specific issuer or contain a specific subject:

mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost IDENTIFIED
 BY "g00r002b" REQUIRE ISSUER "C=ZA, ST=Western Cape, L=Cape Town,
 O=Mars Inc CN=Lilian Nomvete/Email=lilian@marsorbust.co.za";
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost
IDENTIFIED
 BY "g00r002b" REQUIRE SUBJECT "C=ZA, ST=Western Cape, L=Cape Town,
 O=Mars Inc CN=Benedict Mhlala/Email=benedict@marsorbust.co.za";
Query OK, 0 rows affected (0.01 sec)

REQUIRE CIPHER allows you to ensure that weak SSL algorithms are not used, as you can specify a specific cipher:

mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost IDENTIFIED
 BY "g00r002b" REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA"; 
Query OK, 0 rows affected (0.01 sec)

You can specify any or all of the previous options at the same time (the AND is optional):

mysql> GRANT ALL PRIVILEGES ON securedb.* TO root@localhost IDENTIFIED
 BY "g00r002b" REQUIRE ISSUER "C=ZA, ST=Western Cape, L=Cape Town,
 O=Mars Inc CN=Lilian Nomvete/Email=lilian@marsorbust.co.za" AND
 SUBJECT "C=ZA, ST=Western Cape, L=Cape Town, O=Mars Inc CN=Benedict
 Mhlala/Email=benedict@marsorbust.co.za" AND CIPHER "EDH-RSA-DES-CBC3-SHA";
Query OK, 0 rows affected (0.01 sec)

Tagged with: mysql • security • ssl connection

Jul 27

Mysql Security,part 2

By jason Mysql Server No Comments »

Managing Users and Permissions

MySQL has a well-designed, flexible, and easy-to-manage permissions system. Permissions are what allow or disallow certain users or host machines from connecting to the database server and from performing certain operations on the databases, tables, or even certain columns in the tables.

For example, take some possible scenarios:

A news website includes a database server, a web server, and an intranet where staff update the news. Connections from the web server should only have permission to perform SELECT queries on the database, and connections from the intranet would allow UPDATE and INSERT queries for the staff.
A financial transactions system has one database containing a log of records and one database containing customer balances. UPDATEs are permitted on the customer balance database, but not on the log database.
A booking system has ordinary users who can only insert records to a particular table and an administrator who can update this table.

The mysql Database

When MySQL is installed, the mysql database is one of those automatically created. A thorough knowledge of the tables in this database is vital to be able to effectively administer security on the system. Six tables in the mysql database affect system access:

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

Table .1: The MySQL Tables

Table	Description
user	Lists users and the associated hosts and passwords that may access the server, as well as theglobal permissions they have. It’s best to disallow any global permissions and instead specifically allow them access in one of the other tables.
db	Lists databases that users may access. Permissions granted here apply to all tables in the database.
host	Together with the db table allows a more controlled form of access based on the particular host.
tables_priv	Lists access to specific tables. Permissions granted here apply to all columns in the table.
columns_priv	Lists access to specific columns.
func	Not yet used.

What Fields the Tables Contain

Let’s take a look at the tables in the mysql database. Your distribution may contain some slight differences.

mysql> SHOW COLUMNS FROM user;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| Host                  | varchar(60)
                           binary      |      | PRI |         |       |
| User                  | varchar(16)
                           binary      |      | PRI |         |       |
| Password              | varchar(16)
                           binary      |      |     |         |       |
| Select_priv           | enum('N','Y')|      |     | N       |       |
| Insert_priv           | enum('N','Y')|      |     | N       |       |
| Update_priv           | enum('N','Y')|      |     | N       |       |
| Delete_priv           | enum('N','Y')|      |     | N       |       |
| Create_priv           | enum('N','Y')|      |     | N       |       |
| Drop_priv             | enum('N','Y')|      |     | N       |       |
| Reload_priv           | enum('N','Y')|      |     | N       |       |
| Shutdown_priv         | enum('N','Y')|      |     | N       |       |
| Process_priv          | enum('N','Y')|      |     | N       |       |
| File_priv             | enum('N','Y')|      |     | N       |       |
| Grant_priv            | enum('N','Y')|      |     | N       |       |
| References_priv       | enum('N','Y')|      |     | N       |       |
| Index_priv            | enum('N','Y')|      |     | N       |       |
| Alter_priv            | enum('N','Y')|      |     | N       |       |
| Show_db_priv          | enum('N','Y')|      |     | N       |       |
| Super_priv            | enum('N','Y')|      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')|      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')|      |     | N       |       |
| Execute_priv          | enum('N','Y')|      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')|      |     | N       |       |
| Repl_client_priv      | enum('N','Y')|      |     | N       |       |
| ssl_type              | enum('','ANY'
                         ,'X509'
                         ,'SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob         |      |     |         |       |
| x509_issuer           | blob         |      |     |         |       |
| x509_subject          | blob         |      |     |         |       |
| max_questions         | int(11)
                           unsigned    |      |     | 0       |       |
| max_updates           | int(11)
                           unsigned    |      |     | 0       |       |
| max_connections       | int(11)
                           unsigned    |      |     | 0       |       |
+-----------------------+--------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM db;  
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM host;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM tables_priv;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| Host        | char(60) binary      |      | PRI |         |       |
| Db          | char(64) binary      |      | PRI |         |       |
| User        | char(16) binary      |      | PRI |         |       |
| Table_name  | char(60) binary      |      | PRI |         |       |
| Grantor     | char(77)             |      | MUL |         |       |
| Timestamp   | timestamp(14)        | YES  |     | NULL    |       |
| Table_priv  |set('Select','Insert',
               'Update', 'Delete',
               'Create', 'Drop',
               'Grant', 'References',
               'Index', 'Alter')     |      |     |         |       |
| Column_priv |set('Select','Insert',
               'Update','References')|      |     |         |       |
+-------------+----------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM columns_priv; ;
+-------------+-----------------------+------+-----+---------+-------+
| Field       | Type                  | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| Host        | char(60) binary       |      | PRI |         |       |
| Db          | char(64) binary       |      | PRI |         |       |
| User        | char(16) binary       |      | PRI |         |       |
| Table_name  | char(64) binary       |      | PRI |         |       |
| Column_name | char(64) binary       |      | PRI |         |       |
| Timestamp   | timestamp(14)         | YES  |     | NULL    |       |
| Column_priv | set('Select','Insert',
               'Update','References') |      |     |         |       |
+-------------+-----------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM func;
+-------+------------------------------+------+-----+----------+-------+
| Field | Type                         | Null | Key | Default  | Extra |
+-------+------------------------------+------+-----+----------+-------+
| name  | char(64) binary              |      | PRI |          |       |
| ret   | tinyint(1)                   |      |     | 0        |       |
| dl    | char(128)                    |      |     |          |       |
| type  | enum('function','aggregate') |      |     | function |       |
+-------+------------------------------+------+-----+----------+-------+

Table 2 describes the various privileges.

Table 2: What the Columns Mean

Column	Description
Host	The host machine from which the user connects.
User	The username supplied for the connection (the -u option).
Password	The password the user connects as (the -p option).
Db	The database on which the user is trying to perform the operation.
Select_priv	Permission to return data from a table (a SELECT statement). SELECT results thatcan be calculated without needing to access a table still return a result even if the user does not have SELECT privileges.
Insert_priv	Permission to add new records to the table (an INSERT statement).
Update_priv	Permission to modify data in a table (an UPDATE statement).
Delete_priv	Permission to remove records from a table (a DELETE statement).
Create_priv	Permission to create databases and tables.
Drop_priv	Permission to drop databases or tables.
Reload_priv	Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin).
Shutdown_priv	Permission to shut down the server.
Process_priv	Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements).
File_priv	Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable.
Grant_priv	Permission to grant privileges available to the user to other users.
References_priv	Not currently used by MySQL.
Index_priv	Permission to create, modify, or drop indexes.
Alter_priv	Permission to change the structure of a table (an ALTER statement).
Show_db_priv	Permission to see all databases.
Super_priv	Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands.
Create_tmp_table_priv	Permission to create a temporary table (CREATE TEMPORARY TABLE).
Lock_tables_priv	Permission to lock a table for which the user has SELECT permission.
Execute_priv	Permission to run stored procedures (scheduled for MySQL 5).
Repl_client_priv	Permission to ask about replication slaves and masters.
Repl_slave_priv	Permission to replicate (see Chapter 12, "Database Replication").
ssl_type	Permission to connect is only granted if Secure Sockets Layer (SSL) is used.
ssl_cipher	Permission to connect is only granted if a specific cipher is present.
x509_issuer	Permission to connect is only granted if the certificate is issued by a specific issuer.
x509_subject	Permission to connect is only granted if the certificate contains a specific subject.
max_questions	Maximum number of queries the user can perform per hour.
max_updates	Maximum number of updates the user can perform per hour.
max_connections	Maximum number of times the user can connect per hour.

How MySQL Examines Permissions to Allow Access

When a user tries to connect, MySQL examines the user table first to confirm that the particular user, host, and password combination is listed. If not, the user is denied access. When a user is trying to connect directly to a database, the db table will also be examined if the user gets through the other checks. If the user does not have permission to connect to that database, access is denied.

When a connected user tries to perform an administrative operation (for example, mysqladmin shutdown), MySQL examines the column related to the operation from the user table. If permission for the required operation is granted, the operation goes ahead. If not, the operation fails.

When a connected user tries to perform a database-related operation (SELECT, UPDATE, and so on), MySQL examines the related field from the user table. If permission for the required operation (for the SELECT, UPDATE, and so on) is granted, the operation is permitted. If not, MySQL goes to the next step.

The db table is examined next. MySQL looks for the database on which the user is performing the operation. If this does not exist, permission is denied, and the operation fails. If the database does exist, and the host and user match, the field relating to the operation is examined. If permission is granted for the required operation, the operation succeeds. If permission is not granted, MySQL proceeds to the next step. If the database and user combination does exist, and the host field is blank, MySQL examines the host table to see whether the host can perform the required operation. If the host and database are found in the host table, the related field on both the host and db tables determines whether the operation succeeds. If permission is granted on both tables, the operation succeeds. If not, MySQL proceeds to the next step.

MySQL examines the tables_priv table taking into account the table(s) on which the operation is being performed. If the host, user, db, and table combination do not exist, the operation fails. If they do exist, the related field is examined. If permission is not granted, MySQL proceeds to the next step. If permission is granted, the operation succeeds.

Finally, MySQL examines the columns_priv tables, taking into account the table columns being used in the operation. If permission related to the required operation is granted here, the operation succeeds. If not, if fails.

The order of precedence for the MySQL permission tables is shown in Figure 1.

Figure 1: Precedence for MySQL permission tables

How to Populate the Permission Tables

The permission tables are populated with some default values:

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+---
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password
         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
4 rows in set (0.05 sec)
mysql> SELECT * FROM db;  
+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv |
Index_priv | Alter_priv |

+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+

| %    | test    |      | Y           | Y           | Y           | Y            | Y           | Y         | N          | Y               | Y         |
Y          |
| %    | test\_% |      | Y           | Y           | Y           | Y
| Y           | Y         | N          | Y               | Y          |
Y          |
+------+---------+------+-------------+-------------+-------------+----
---------+-------------+-----------+------------+-----------------+----
--------+------------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM host;
Empty set (0.00 sec)
mysql> SELECT * FROM tables_priv;
Empty set (0.00 sec)
mysql> SELECT * FROM columns_priv;
Empty set (0.00 sec)

Notice that the default settings are not secure. Anyone can connect from the local host as the root user and have total authority. An anonymous user (where no username is supplied) can connect from the local host to the default test database and to any database where the name begins with test.

Note

In Unix, MySQL uses the Unix login username if no username is supplied. This means that someone logged in as root can simply enter MySQL without specifying a username, and they will have full permissions.

One of the first tasks to perform in a new installation is to set new permissions and at least a new password for the root user.

Manipulating the Permission Tables Directly

There are two ways to set permissions—by using the MySQL GRANT and REVOKE statements or by directly changing the values in the tables. The easiest and most convenient way is with the GRANT and REVOKE statements, but it’s important to understand how the tables affect permissions. For now, you’re going to look at changing permissions by changing the values in the tables with the basic INSERT, UPDATE, and DELETE SQL statements. You’ll see the other method later in the section titled "Using GRANT and REVOKE to Manipulate the Permission Tables." To add a password for the root user, you would write the following:

mysql> UPDATE user SET password=PASSWORD('g00r002b') WHERE user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Notice the use of the PASSWORD() function. You must use this function when updating the tables directly. It encrypts the password so that it cannot be read simply by viewing the contents of the tables. For example:

mysql> SELECT host,user,password FROM user;
+---------------------+------+------------------+
| host                | user | password         |
+---------------------+------+------------------+
| localhost           | root | 43b591f759a842a9 |
| test.testhost.co.za | root | 43b591f759a842a9 |
| localhost           |      |                  |
| test.testhost.co.za |      |                  |
+---------------------+------+------------------+
4 rows in set (0.00 sec)

Warning

Be careful when directly changing permissions. Neglecting the WHERE clause would mean that all passwords change, and suddenly no existing users would be able to connect.

Changes to the permissions do not take effect immediately when made directly to the MySQL tables. MySQL needs to reread the grant tables. You can force it to do this by issuing FLUSH PRIVILEGES, mysqladmin flush-privileges, or mysqladmin reload.

mysql> INSERT INTO user (Host,User,Password) VALUES ('localhost',
 'administrator', PASSWORD('admin_pwd'));
Query OK, 1 row affected (0.09 sec)

Before the permissions are flushed, this data does not take effect. You can connect as the administrator user without any password:

% mysql -uadministrator;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.1-alpha-max-log

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

Before the database is reloaded, connecting as administrator is accepted, because, not finding the specific name, connecting is the same as for an anonymous user where no password is needed. You can see this by looking at the third and fourth records from the user table. After the flush, the administrator user can no longer connect without a password.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
 % mysql -uadministrator;
ERROR 1045: Access denied for user: 'administrator@localhost'
 (Using password: NO)

It’s never good to use the root user for anything but administration. Day-to-day connections should be through users with permissions developed especially for the tasks that user performs. For this sales system, you’re going to add two users—an administrator and a regular user. The administrator will have full permissions to do anything, and the regular user will have certain limitations. To add the administrator, you could simply add a record to the user table, giving a full set of permissions to the administrator. But that would mean the administrator of the sales rep system would have full access to any other database that gets developed on the system. It’s almost always better to limit permissions at a user level and then activate permissions on a lower level. You’re going to add a record to the user and to the database table to do this. I use an INSERT statement without specifying fields (for ease of typing) with the db table example, in case you’re following these examples. Be sure that the fields match the fields in the tables from your distribution, in case they have changed:

mysql> INSERT INTO user (host,user,password)
VALUES('localhost','administrator',PASSWORD('l3tm31n'));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO db 
VALUES('localhost','firstdb','administrator','y','y','y','y','n','n',
'n','n','n','n');
Query OK, 1 row affected (0.01 sec)

The tables now contain the following:

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y        | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |

| localhost                 |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N        | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |

+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
5 rows in set (0.05 sec)

mysql> SELECT * FROM db;;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |

+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y           | Y
| Y           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

3 rows in set (0.01 sec)

The administrator can connect to the database with the password but only perform data manipulation on the firstdb database.

Remember to flush the tables before these permissions take effect:

% mysqladmin reload -u root -p
Enter password:
% mysql mysql;
ERROR 1045: Access denied for user: 'root@localhost' (Using
 password: NO)

If you were not logged in as root, you’d get an error indicating that the anonymous user does not have permission:

% mysql mysql;
ERROR 1044: Access denied for user: '@localhost' to database
 'mysql'

Using GRANT and REVOKE to Manipulate the Permission Tables

Rather than updating the tables directly and having to flush the database, an easier way is to use the GRANT and REVOKE statements to manage permissions. The basic GRANT syntax is as follows:

GRANT privilege ON table_or_database_name TO user_name@hostname
 IDENTIFIED BY 'password'

To add a regular user for this sales system, you could do the following:

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |

| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N       | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | regular_user    | 1bfcf83b2eb5e59   | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
6 rows in set (0.05 sec)
mysql> SELECT * FROM db;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y          | Y
| Y           | N           | N         | N          | N
| N          | N          |
| localhost | sales   | regular_user  | Y           | N           | N
| N           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
4 rows in set (0.01 sec)

The password is automatically encrypted when issued with GRANT, so there is no need to use the PASSWORD() function to encrypt it. In fact, if you do, you’ll re-encrypt the password! You can change the password by reissuing the same permissions with a new password.

You can also revoke permissions in the same way as you grant them:

mysql> REVOKE SELECT ON sales.* FROM regular_user@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | regular_user    | 1bfcf83b2eb5e59   | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
6 rows in set (0.05 sec)
mysql> SELECT * FROM db;
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+
| Host      | Db      | User          | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

| %         | test    |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| %         | test\_% |               | Y           | Y           | Y
| Y           | Y           | Y         | N          | Y
| Y          | Y          |
| localhost | firstdb | administrator | Y           | Y           | Y
| Y           | N           | N         | N          | N
| N          | N          |
+-----------+---------+---------------+-------------+-------------+----
---------+-------------+-------------+-----------+------------+--------
---------+------------+------------+

3 rows in set (0.00 sec)

Notice that all trace of the user has been removed from the db table but that the user still exists in the user table. There is no way to remove this from the table without directly deleting it. A user with no permissions (called USAGE permission) can still connect to the server and access some information, such as, for early versions of MySQL 4, viewing the existing databases! For example:

mysql> exit
Bye
% mysql -uregular_user -pl3tm37n_2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 4.0.1-alpha
-max-log

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

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| firstdb  |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

To remove all traces of the user, delete them from the user table directly (while connected as root):

mysql> exit
Bye
% mysql mysql -uroot -pg00r002b
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 4.0.1-alpha
-max-log

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

mysql> DELETE FROM user WHERE user='regular_user';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user;
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| localhost                 | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za       | root             |                  | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y
| Y                | Y            | Y               | Y
|          |            |             |              |             0 |
0 |               0 |
| localhost                 |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| test.testhost.co.za      |                  |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
| localhost                | administrator    | 26981a09472b4835 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
5 rows in set (0.05 sec)

Table 3 describes all the privileges available.

Table 3: Privileges

Privilege	Description
ALL	Grants all the basic permissions.
ALL PRIVILEGES	Same as ALL.
ALTER	Permission to change the structure of a table (an ALTER statement), excluding indexes.
CREATE	Permission to create databases or tables, excluding indexes.
CREATE TEMPORARY TABLES	Permission to create a temporary table (CREATE TEMPORARY TABLE statement).
DELETE	Permission to remove records from a table (a DELETE statement).
DROP	Permission to drop databases or tables, excluding indexes.
EXECUTE	Permission to run stored procedures (scheduled for MySQL 5).
FILE	Permission to read and write files on the server (for LOAD DATA INFILE or SELECT INTO OUTFILE statements). Any files that the MySQL user can read are readable.
GRANT	Permission to grant permissions owned by the user to another user.
INDEX	Permission to create, modify, or drop indexes.
INSERT	Permission to add new records to the table (an INSERT statement).
LOCK TABLES	Permission to lock a table for which the user has SELECT permission.
PROCESS	Permission to view the current MySQL processes or kill MySQL processes (for SHOW PROCESSLIST or KILL SQL statements).
REFERENCES	Not currently used by MySQL.
RELOAD	Permission to reload the database (a FLUSH statement or a reload, refresh, or flush issued from mysqladmin).
REPLICATION CLIENT	Permission to ask about the replication slaves and masters.
REPLICATION SLAVE	Permission to replicate from the server (slaves need this to replicate).
SHOW DATABASES	Permission to see all databases.
SELECT	Permission to return data from a table (a SELECT statement).
SHUTDOWN	Permission to shut down the server.
SUPER	Permission to connect, even if the maximum number of connections is reached, and perform the CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL commands.
UPDATE	Permission to modify data in a table (an UPDATE statement).
USAGE	Permission to connect to the server and perform statements available to all (for early versions of MySQL 4 this included SHOW DATABASES).

The earlier example granted permissions for all tables in the sales database. You can easily manipulate this by changing the database and table names you grant on.

Table 14.4: Database and Table Names

Name	Description
.	All tables in all databases
*	All tables in the current database
databasename.*	All tables in the database databasename
databasename.tablename	The table tablename in the database databasename

For example:

mysql> GRANT SELECT ON *.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n_2';  
Query OK, 0 rows affected (0.00 sec)

Because permission is granted on all databases, there is no need for an entry in the database table; just the user table, with the field select_priv set to Y:

mysql> SELECT * FROM user;
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
| Host                | User          | Password         | Select_priv
| Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv |
References_priv | Index_priv | Alter_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject |
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
| localhost           | root          | 43b591f759a842a9 | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | NONE     |            |             |
|
| test.testhost.co.za | root          | 43b591f759a842a9 | Y
| Y           | Y           | Y           | Y           | Y         | Y
| Y             | Y            | Y         | Y          | Y
| Y          | Y          | NONE     |            |             |
|
| localhost           |               |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| test.testhost.co.za |               |                  | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| localhost           | administrator | 74126e0c6742d7e9 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
| localhost           | regular_user  | 1bfcf83b2eb5e591 | Y
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | NONE     |            |             |
|
+---------------------+---------------+------------------+-------------
+-------------+-------------+-------------+-------------+-----------+--
-----------+---------------+--------------+-----------+------------+---
--------------+------------+------------+----------+------------+------
-------+--------------+
6 rows in set (0.00 sec)

Using SET to Set User Passwords

Another way to change passwords is with the SET PASSWORD statement. Any nonanonymous user can change their own password in this way. (This is another reason to assign users carefully. It’s not unheard of for a user to change their password, denying others access, because they do not realize they share this username!)

You can set a password for the user you’re connected as follows:

mysql> SET PASSWORD=PASSWORD('g00r002b2');
Query OK, 0 rows affected (0.00 sec)

A user with access to the user table in the mysql database can set passwords for other users too, by specifying the user:

mysql> SET PASSWORD FOR root=PASSWORD('g00r002b');
Query OK, 0 rows affected (0.00 sec)

Remember to use the PASSWORD() function to encrypt the password. If you don’t, the password is stored in the user table as plain text, but because on connection the given password is automatically encrypted before being compared to the password in the user table, you will not be able to connect (if you try this, you’ll need to refer to the section titled "What to Do If You Can’t Connect…" afterward to continue):

mysql> SET PASSWORD FOR root='g00r002b';
Query OK, 0 rows affected (0.00 sec)

Now, after exiting you won’t be able to reconnect as root:

% mysql -uroot -pg00r002b2
ERROR 1045: Access denied for user: 'root@localhost' (Using
 password: YES)

Using mysqladmin to Set User Passwords

When using mysqladmin, as with the GRANT statement, you should not use the PASSWORD() function:

% mysqladmin -uroot -pg00r002b password g00r002b

Wildcard Permissions

There’s no need to enter 1,001 hosts if that’s how many hosts to which you need to grant access. MySQL accepts wildcards in the host table. For example, the following allows a user to connect from a host ending with marsorbust.co.za:

mysql> GRANT SELECT ON sales.* TO regular_user@"%.
marsorbust.co.za"
 IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM user WHERE host LIKE '%mars%';
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| Host                      | User             | Password         |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
| %.marsorbust.co.za          | regular_user     | 1bfcf83b2eb5e591 | N
| N           | N           | N           | N           | N         | N
| N             | N            | N         | N          | N
| N          | N          | N            | N          | N
| N                | N            | N               | N
|          |            |             |              |             0 |
0 |               0 |
+---------------------------+------------------+------------------+----
---------+-------------+-------------+-------------+-------------+-----
------+-------------+---------------+--------------+-----------+-------
-----+-----------------+------------+------------+--------------+------
------+-----------------------+------------------+--------------+------
-----------+------------------+----------+------------+-------------+--
------------+---------------+-------------+-----------------+
1 row in set (0.05 sec)

The quotes in the GRANT statement allow wildcards, or any special characters, to be used. You can of course also insert wildcards into the MySQL tables directly.

What to Do If You Can’t Connect or Have No Permissions

It’s not impossible. Maybe you revoke yourself into oblivion, use DELETE where you shouldn’t have, or even damage the files holding the permission tables, and now you can’t connect at all, even as root. Fear not; there is a solution!

First, stop MySQL completely. As the root user on Unix, if you run MySQL out of /init.d, you may be able to run the following:

% /etc/rc.d/init.d/mysql stop
Killing mysqld with pid 5091
Wait for mysqld to exit\c
.\c
.\c
.\c
.\c
020612 01:14:41  mysqld ended

 done

If not, still as root, you’ll need to kill the specific MySQL-related processes:

% ps -ax |grep mysql
 5195 pts/0    S      0:00 sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/lo
 5230 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5232 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5233 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5234 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5235 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5236 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5237 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5238 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5239 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5240 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
 5241 pts/0    S      0:00 /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bi
% kill 5195 5230 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241  
mysqld ended

In the eventuality that this doesn’t work, you may have to use kill -9 (followed by the process ID) to really kill the process.

On Windows, you can simply use the task manager to close MySQL.

Then, restart MySQL without the grant tables (this ignores any permission restrictions):

% mysqld_safe --skip-grant-tables

And now you should be able to add a root password, either through directly manipulating the tables directly, with GRANT, or with mysqladmin:

 % mysqladmin -u root password 'g00r002b'

Don’t forget to stop the server, and restart it without –skip-grant-tables, to activate your root password.

What to Do If the User Table Becomes Corrupted

Sometimes it can happen that the user table has become corrupted, so you still cannot change the password with mysqladmin. This happened to me once after a crash and could conceivably happen if someone tampers with the files directly. If you want to follow this example, you can simulate the loss of the user table by renaming it, and then flushing the tables (the original will probably still be cached otherwise):

% mv user.MYD user_bak.olddata
% mysql -uroot -pg00r002b;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.1-alpha-
max-log

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

mysql>FLUSH TABLES;
mysql> SELECT * FROM user;
ERROR 1016: Can't open file: 'user.MYD'. (errno: 145)

If this is the case, you should still start MySQL without the grant tables and then try dropping the table:

mysql> DROP TABLE user; 
Query OK, 0 rows affected (0.01 sec)

CREATE TABLE user (
  Host varchar(60) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  Password varchar(16) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Reload_priv enum('N','Y') NOT NULL default 'N',
  Shutdown_priv enum('N','Y') NOT NULL default 'N',
  Process_priv enum('N','Y') NOT NULL default 'N',
  File_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  Show_db_priv enum('N','Y') NOT NULL default 'N',
  Super_priv enum('N','Y') NOT NULL default 'N',
  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',
  Lock_tables_priv enum('N','Y') NOT NULL default 'N',
  Execute_priv enum('N','Y') NOT NULL default 'N',
  Repl_slave_priv enum('N','Y') NOT NULL default 'N',
  Repl_client_priv enum('N','Y') NOT NULL default 'N',
  ssl_type enum('','ANY','X509','SPECIFIED') NOT NULL default '',
  ssl_cipher blob NOT NULL,
  x509_issuer blob NOT NULL,
  x509_subject blob NOT NULL,
  max_questions int(11) unsigned NOT NULL default '0',
  max_updates int(11) unsigned NOT NULL default '0',
  max_connections int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
Query OK, 0 rows affected (0.00 sec)

This probably still won’t give you permission, however:

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n';
ERROR 1047: Unknown command
mysql> exit
Bye
[root@test mysql]# mysqladmin -uroot password 'g00r002b'
mysqladmin: unable to change password; error: 'You must have privileges
 to update tables in the mysql database to be able to change passwords
 for others'

You’ll need to insert some values into the table once more. Here you add the default values. Be sure they match the columns for the user table you created, if it is different:

[root@test mysql]# mysql mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.1-alpha
-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>INSERT INTO user VALUES ('localhost', 'root', '', 'Y',
 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.01 sec)
mysql>INSERT INTO user VALUES ('%', 'root', '', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N'
, 'N',
 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO user VALUES ('localhost', '', '', 'N', 'N'
, 'N',
 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
 'N', 'N',
 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);
Query OK, 1 row affected (0.00 sec)

You’ll need to reload (or flush the privilege tables) in order to activate the permissions, and then once again you can start issuing commands:

mysql> exit
Bye
[root@test mysql]# mysqladmin reload
[root@test mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.1-alpha-max-log

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

mysql> GRANT SELECT ON sales.* TO regular_user@localhost IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.00 sec)

Other Grant Options

By default, MySQL does not allow a user to pass their permissions onto someone else. And, being a control freak, I don’t suggest you allow your users to do this. You’ve probably got a good reason for not granting them the permission in the first place, and you wouldn’t want another user to override this. But, if you must—perhaps in a situation where there are multiple trusted users—there is a way. The WITH GRANT OPTION will allow a user to grant any permissions that they have to another user.

The following demonstrates this in action, using two databases: sales and customer. The administrator creates a regular_user2, with permission to perform SELECT queries on the sales database, and then grants the GRANT option to the first regular_user, who has permission to SELECT on the customer database, who will then in turn grant the same rights to regular_user2:

mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
% mysql -u regular_user2 -pl3tm37n
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.1-alpha-
max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
ERROR 1044: Access denied for user: 'regular_user2@localhost' to database
 'customer'

Regular_user2 cannot grant anything to another user:

mysql> exit
Bye
[root@test /root]# mysql -u regular_user -pl3tm37n
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.1-alpha-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT SELECT ON customer.* TO regular_user@localhost IDENTIFIED
 BY 'l3tm37n' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

However, regular_user can grant permissions to regular_user2.

There are some other useful options that help avoid the possibility of one user hogging connections. These limit queries, updates, or connections to a certain number per hour. The three options are as follows:

MAX_QUERIES_PER_HOUR n
MAX_UPDATES_PER_HOUR n
MAX_CONNECTIONS_PER_HOUR n

Without these three options, the only limit on user activity is the max_user_connections variable. But this is global, and you cannot limit one kind of user from an activity.

Where Could User Limitation Be Useful?

Limiting database-intensive queries, such as searches that involve many joins of large tables, can be useful. The application could connect as a different user with this limit. The application is still a better place to do this limiting, but an hourly limit may be useful in some situations—for example, situations where there is potential for denial of service attacks, or situations where one user could make multiple connections and cause the performance of the database server to degrade.

Which Type of User Limitation to Use?

Connections usually have the lowest impact on the database, but there is still the possibility that

max_user_connections could be taken up by one user. Setting MAX_CONNECTIONS_PER_HOUR is the most cautious option.

Sometimes you may not be worried about the number of connections, but rather about a user performing multiple queries at the same time or performing extraneous queries. You could set

MAX_QUERIES_PER_HOUR to avoid users performing unnecessary queries and wasting resources or performing too many of a possible heavy query in a short space of time.

UPDATEs have a higher performance impact than SELECTs and a very marked impact where table locking is used (such as the default MyISAM table type). You may use MAX_ UPDATES_PER_HOUR to limit updates for performance reasons or where users need not make many updates.

Tip

It pays to be paranoid. Users can abuse the database, intentionally or not. If you cannot see a use for them to have certain permissions, do not grant them. It’s always easier to add permissions than to take away permissions once they’ve been granted. I’ve encountered large systems where the security consisted only of one user and one password. When this was compromised, it proved impossible to gracefully add limitations.

As an example of user limitations, you can limit regular_user2 to two connections per hour:

mysql> GRANT SELECT ON sales.* TO regular_user2@localhost IDENTIFIED BY 'l3tm37n' 
  WITH MAX_CONNECTIONS_PER_HOUR 2;
Query OK, 0 rows affected (0.00 sec)

If regular_user2 exceeds the number of connections, they’ll get an error such as follows:

ERROR 1226: User 'regular_user2' has exceeded the 'max_connections'
 resource (current value: 2)

Similarly, if MAX_QUERIES_PER_HOUR was assigned and exceeded, the error message would be as follows:

ERROR 1226: User 'regular_user2' has exceeded the 'max_questions'
 resource (current value: 4)

Tip

Be reasonable with your limitations. If a user should only perform one query an hour, realize that they could enter an incorrect query and have to perform a second one.

A Strategy for Managing Users Securely

The more complex your needs, the more complex your strategy will have to be. Simple websites can often suffice with two users: an administration user who can update data and a user for the website application who can only perform SELECTs from certain tables, for example. The general principle is to grant the user only the privileges they need and no more. If they need more at a later stage, it is easy to grant them additional ones. But taking them away is another matter!

MySQL users are mostly of three kinds: There are individual people (for example, Anique or Channette), applications (for example, a salary system or the news website), and roles (for example, updating the news or updating the salaries). These may overlap to various degrees—for example, Anique may update both salaries and news, using both applications and performing both roles. The DBA needs to decide whether to issue Anique and Channette their own passwords, issue passwords to the news and salary systems, or create a user based upon whether the news or salaries are being updated.

If you opt for users as individuals and issue Anique her own password, she only has to remember one login to the database. But then she needs to be given access to update both the salary and news databases. If she, or the application she is using, makes a mistake, there is the possibility of her damaging data on which she should not even be working. For example, if the salary and news databases both have a days_data table—with the news database version growing continually until it is archived and the salary data being manually removed after it has been processed—there is the possibility of her removing the news table when she meant to remove the salary table.

If you opt for users as applications, you solve some of these problems. However, it seems a user now has to remember two passwords. Also, you cannot track which user has made which changes to the database. You have a solution, however, because where security is necessary, it’s likely that the individual will have to log into the application (potentially allowing you to track the changes an individual makes to the database), and the application then logs into the database. The user could have the same username and password to both applications, but they could never destroy news data when connected as the salary application (as you’d not have given the salary user permission to update the news database).

Applications, though, often have many roles, with many levels of user. Perhaps anyone may view their own salary details, but only an administrator can update them. Giving the application permission to update data potentially allows an ordinary user to update the data. Consider also the development process: A trusted senior developer builds the salary administration component of the application, and a team of junior developers builds the salary-viewing component. Issuing the same password to the application allows the junior developers to update the data when they don’t need to and probably shouldn’t be allowed to update it. In this case, you could issue usernames based upon a combination of role and application (salary administration, salary viewing, news administrator, news viewing).

Some principles to keep in mind include the following:

Never issue a user the root password. They should always be connecting with another username and password.
Always issue the minimum permissions you can. (But be reasonable! You’ll always get some sadists who take great glee in granting permission on a query-by-query basis. For example, allowing the user to read the surname column, then forcing them to come begging for more permission when they need to read the first name shortly afterward.) The global permissions assigned in the user table should always be N, though, and then access to specific databases granted in the db table.
For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.

Avoiding Granting Dangerous Privileges

Although you should always issue the minimum privileges required, there are some privileges that are particularly dangerous, where the security risk may outweigh the convenience factor. Remember that you should never grant access on a global level.

The following privileges in particular could be security risks:

Any privileges on the mysql database A malicious user can still gain access even after being able to only view the encrypted passwords.

ALTER A malicious user could make changes to the privilege tables, such as renaming them, which renders them ineffective.

DROP If a user can DROP the mysql database, the permission limitations will no longer be in place.

FILE Users with the FILE privilege can potentially access any file that is readable by all. They can also create a file that has the MySQL user privileges.

GRANT This allows users to give their privileges to others who may not be as trustworthy as the original user.

PROCESS Queries that are running can be viewed in plain text, which includes any that change or set passwords.

SHUTDOWN It’s unlikely a DBA will be fooled into granting this privilege easily, and it should go without saying that users with the SHUTDOWN privilege can shut down the server and deny access to everyone.

Tagged with: mysql • security

Jul 26

Mysql Security,part 1

By jason Mysql Server 1 Comment »

mysql security when connecting.

When connecting, it is insecure to connect in the following manner:

% mysql -uusername -ppassword

I use this throughout this chapter for convenience, to make the password visible for purposes of the examples, but a security-conscious user should not connect in this way for the following reasons:

Anyone looking over your shoulder can see the password in plain text.
The password could be visible in the history (for example, in Unix, someone getting access to someone else’s terminal could scroll through the most recent commands and be able to see the password).
Programs that view the system status (such as the Unix ps) could see the password in plain text.

Instead, connect by entering the password when prompted for it:

% mysql -uroot -p
Enter password:

If you need to store the password in a file, make sure it is properly secured. For example, if the password is stored in the my.cnf file in the user’s home directory on a server, this file should not be readable by anyone else. The root user of the system can of course read this file. Be aware that the root user of the system is not necessarily the same as the MySQL root user. Similarly, applications often make use of a configuration file to store the database password. Make sure this is secure too.

Warning

Never store a configuration file that contains a database password for a web application, or any password for that matter, in the web tree.

Finally, don’t use the MYSQL_PWD environment variable to store your password if you desire any form of security, for similar reasons of not specifying the password on the command line. Environment variables are not secure.

Tagged with: mysql security

Jul 24

Optimizing mysql with tuning-primer.sh

By jason Mysql Server No Comments »

MySQL Server must run a few days or weeks, or it wont be be safe to follow these recommendations.

Downlaod it:

wget http://www.day32.com/MySQL/tuning-primer.sh

grant privilege:

chmod +x tuning-primer.sh

Execute script:

sh tuning-primer.sh

Here is an example of tuning-primer.sh output:

— MYSQL PERFORMANCE TUNING PRIMER —
– By: Matthew Montgomery –

MySQL Version 4.1.22-standard-log i686

Uptime = 10 days 22 hrs 39 min 49 sec
Avg. qps = 0
Total Questions = 571959
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL’s Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5 sec.
You have 0 out of 571973 that take longer than 5 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/4.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 14
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 1024
Current threads_connected = 1
Historic max_used_connections = 15
The number of used connections is 1% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 245 M
Configured Max Per-thread Buffers : 7.17 G
Configured Max Global Buffers : 138 M
Configured Max Memory Limit : 7.31 G
Physical Memory : 1.97 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 259 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 188
Key buffer free ratio = 85 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 27 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 42.39 %
Current query_cache_min_res_unit = 4 K
MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 5120 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 256 tables
You have a total of 642 tables
You have 168 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 18233 temp tables, 46% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 22 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 39461
Your table locking seems to be fine

Tagged with: optimizing mysql • tuning-primer.sh

Previous Entries Next Entries

Option	Description
--comments	Adds a comment to the benchmark output.
--cmp=server[,server...]	Runs the test with limits from the specified servers. By running all servers with the same --cmp, the test results will be comparable between the different SQL servers.
--create-options=#	Specifies an extra argument to all create statements. For example, to create all tables as BDB tables, you would use --create-options=TYPE=BDB.
--database	Specifies the database in which the test tables are created. The default is the test database.
--debug	Displays debugging information. You normally only use this when debugging a test.
--dir	Indicates where the test results should be stored. The default is the defaultoutput.
--fast	Allows the use of nonstandard ANSI SQL commands to make the test gofaster.
--fast-insert	Uses fast inserts where possible, which include multiple value lists, such as INSERT INTO tablename VALUES (values),(values) or simply INSERT INTO tablename VALUES (values) rather than INSERT INTO tablename(fields) VALUES (values).
--field-count	Specifies how many fields there are to be in the test table. Usually only used when debugging a test.
--force	Continues the test even when encountering an error. Deletes tables before creating new ones. Usually only used when debugging a test.
--groups	Indicates how many different groups there are to be in the test. Usually only used when debugging a test.
--lock-tables	Allows the use of table locking to get more speed.
--log	Saves the results to the --dir directory.
--loop-count (Default)	Indicates how many times each test loop is to be executed. Usually only used when debugging a test.
--help	Displays a list of options.
--host='host name'	Specifies the host where the database server is located. The default islocalhost.
--machine="machine or os_name"	The machine/operating system name that is added to the benchmark output filename. The default is the operating system name + version.
--odbc	Uses the DBI ODBC driver to connect to the database.
--password='password'	Specifies the password for the user the test connects as.
--socket='socket'	Specifies the socket to connect with (if sockets are supported).
--regions	Specifies how AND levels should be tested. Usually only used when debugging a test.
--old-headers	Gets the old benchmark headers from the old RUN- file.
--server='server name'	Specifies which DBMS on which to run the test. These can include Access, Adabas, AdabasD, Empress, Oracle, Informix, DB2, mSQL, MS-SQL, MySQL, Pg, Solid, and Sybase. The default is MySQL.
--silent	Does not output information about the server when the test starts.
--skip-delete	Specifies that the test tables created are not deleted. Usually only used when debugging a test.
--skip-test=test1[,test2,...]	Excludes the specified tests when running the benchmark.
--small-test	Speeds up the tests by using smaller limits.
--small-tables	Uses fewer rows to run the tests. This would be used if the database cannot handle large tables for some reason (they could have small partitions, forexample).
--suffix	Adds a suffix to the database name in the benchmark output filename. Used when you want to run multiple tests without overwriting the results. When using the --fast option, the suffix is automatically _fast.
--random	Generates random initial values for the sequence of test executions, which could be used to imitate real conditions.
--threads=#	Specifies the number of threads to use for multiuser benchmarks. The default is 5.
--tcpip	Use TCP/IP to connect to the server. This allows the test to do many new connections in a row as the TCP/IP stack can be filled.
--time-limit	Specifies a time limit in seconds for a test loop before the test ends, and the result estimated. The default is 600 seconds.
--use-old-results	Uses the old results from the --dir directory instead of actually running the tests.
--user='user_name'	Specifies the user to connect as.
--verbose	Displays more info. Usually only used when debugging a test.
--optimization='some comments'	Adds comments about optimizations done before the test.
--hw='some comments'	Adds comments about hardware used for this test.

Optimizing Tables

Optimizing Tables with the OPTIMIZE Statement

Optimizing Tables with mysqlcheck

Optimizing Tables with myisamchk

Analyzing Tables

Analyzing Tables with ANALYZE TABLE

Analyzing Tables with mysqlcheck

Analyzing Tables with myisamchk

Checking Tables with CHECK TABLES

Checking Tables with mysqlcheck

Checking Tables with myisamchk

Repairing Tables

Repairing Non-MyISAM Table Types

Repairing Tables with REPAIR TABLE

Repairing Tables with mysqlcheck

Repairing Tables with myisamchk

Using mysqlcheck

Using myisamchk

Memory

Disks

CPU

Optimizing table_cache

Optimizing key_buffer_size

Dealing with Too Many Connections

Optimizing the delayed_queue_size and back_log Variables

Optimizing the sort_buffer Variable

Configuring InnoDB Tables

Introducing the mysqld Options

Exploring the mysqld Variables

Exploring All the Status Variables

The mysql Database

What Fields the Tables Contain

How to Populate the Permission Tables

Manipulating the Permission Tables Directly

Using GRANT and REVOKE to Manipulate the Permission Tables

Using SET to Set User Passwords

Using mysqladmin to Set User Passwords

Wildcard Permissions

What to Do If You Can’t Connect or Have No Permissions

What to Do If the User Table Becomes Corrupted

Other Grant Options

Where Could User Limitation Be Useful?

Which Type of User Limitation to Use?

A Strategy for Managing Users Securely

mysql security when connecting.

Categories

Tag Cloud

Archives

Links

Meta