MySQL Database

 

Contents

  1. Startup
  2. Connect to the server
  3. Enter queries
  4. Use databases
  5. Create a database
  6. Create a table
  7. Load data into a table
  8. Retrieve information from a table
  9. Information about databases and tables
  10. Common queries
  11. Maximum value for a column
  12. Maximum of column per group
  13. Use user variables
  14. Use foreign keys
  15. Search on two keys
  16. Calculate visits per day
  17. Use autoincrement
  18. Use mysql in batch mode
  19. Use with Apache
  20. Database administration
  21. Configuring mySQL
  22. mysqld
  23. `my.cnf'

  24. General security issues
  25. mysqld security
  26. Connect to the MySQL server
  27. Causes of access denied errors
  28. Database Backups
  29. BACKUP table
  30. RESTORE table
  31. CHECK table
  32. REPAIR table
  33. myisamchk
  34. Table Info

  35. Language Reference
  36. OPTIMIZE table
  37. ANALYZE table
  38. FLUSH
  39. RESET
  40. PURGE [MASTER] logs
  41. MySQL Localisation and International Usage
  42. Character Set Used for Data and Sorting
  43. Character Definition Arrays
  44. String Collating Support
  45. Multi-byte Character Support
  46. Problems With Character Sets

  47. Server-side utilities

  48. Client-side utilities

  49. MySQL Log Files
  50. Error Log
  51. General Query Log
  52. Update Log
  53. Binary Log
  54. Slow Query Log
  55. Log File Maintenance

  56. Optimisation
  57. Optimise selects
  58. EXPLAIN (Get Information About a select)
  59. Estimating Query Performance
  60. Speed of select Queries
  61. How MySQL Optimises where Clauses
  62. How MySQL Optimises distinct
  63. How MySQL Optimises LEFT JOIN and right JOIN
  64. How MySQL Optimises order by
  65. How MySQL Optimises LIMIT
  66. Speed of insert queries
  67. Speed of update queries
  68. Speed of delete queries
  69. Locking issues
  70. How MySQL locks tables
  71. Table locking issues
  72. Optimising database structure
  73. Design choices
  74. Get your data as small as possible
  75. How MySQL uses indexes
  76. Column indexes
  77. Multiple-column indexes
  78. How MySQL opens and closes tables
  79. Drawbacks to creating large numbers of tables in the same database
  80. Optimising the mySQL server
  81. System/Compile time and startup parameter tuning
  82. Tuning server parameters
  83. How compiling and linking affects the speed of MySQL
  84. How MySQL uses memory
  85. How MySQL uses DNS
  86. set
  87. Disk issues
  88. Use symbolic links
  89. MySQL language reference
  90. Language structure
  91. Literals: How to write strings and numbers
  92. Legal Names
  93. Case Sensitivity in Names
  94. User Variables
  95. System Variables
  96. Comment
  97. Is MySQL Picky About Reserved Words?
  98. Column Types
  99. Numeric Types
  100. Date and Time Types
  101. String Types
  102. Choosing the Right Type for a Column
  103. Use Column Types from Other Database Engines
  104. Column Type Storage Requirements
  105. Functions for Use in select and where Clauses
  106. Non-Type-Specific Operators and Functions
  107. String Functions
  108. Numeric Functions
  109. Date and Time Functions
  110. Cast Functions
  111. Other Functions
  112. Functions for Use with group by Clauses
  113. Data Manipulation: select, insert, update, delete
  114. select
  115. handler
  116. insert
  117. insert DELAYED
  118. update
  119. delete
  120. TRUNCATE
  121. REPLACE
  122. load data infile
  123. DO
  124. Data Definition: create, drop, ALTER
  125. create database
  126. drop database
  127. create table
  128. ALTER table
  129. RENAME table
  130. drop table
  131. create INDEX
  132. drop INDEX
  133. Basic MySQL User Utility Commands
  134. use
  135. describe (Get Information About Columns)
  136. MySQL Transactional and Locking Commands
  137. BEGIN/COMMIT/ROLLBACK
  138. LOCK tables/UNLOCK tables
  139. set TRANSACTION

  140. MySQL Full-text search
  141. Query cache
  142. Query Cache Configuration
  143. Query Cache Options in select
  144. Query Cache Status and Maintenance
  145. MySQL Table Types
  146. MyISAM Tables
  147. Space Needed for Keys
  148. MyISAM Table Formats
  149. MyISAM Table Problems
  150. MERGE Tables
  151. MERGE Table Problems
  152. ISAM Tables
  153. heap Tables

  154. A Problems and Common Errors
  155. A.1 How to Determine What Is Causing Problems
  156. A.2 Common Errors When Using MySQL
  157. A.2.1 Access denied Error
  158. A.2.2 MySQL server has gone away Error
  159. A.2.3 Can't connect to [local] MySQL server Error
  160. A.2.4 Host '...' is blocked Error
  161. A.2.5 Too many connections Error
  162. A.2.6 Some non-transactional changed tables couldn't be rolled back Error
  163. A.2.7 Out of memory Error
  164. A.2.8 Packet too large Error
  165. A.2.9 Communication Errors / Aborted Connection
  166. A.2.10 The table is full Error
  167. A.2.11 Can't create/write to file Error
  168. A.2.12 Commands out of sync Error in Client
  169. A.2.13 Ignoring user Error
  170. A.2.14 Table 'xxx' doesn't exist Error
  171. A.2.15 Can't initialize character set xxx error
  172. A.2.16 File Not Found
  173. A.3 Installation Related Issues
  174. A.3.1 Problems When Linking with the MySQL Client Library
  175. A.3.2 How to Run MySQL As a Normal User
  176. A.3.3 Problems with File Permissions
  177. A.4 Administration Related Issues
  178. A.4.1 What To Do If MySQL Keeps Crashing
  179. A.4.2 How to Reset a Forgotten Root Password
  180. A.4.3 How MySQL Handles a Full Disk
  181. A.4.4 Where MySQL Stores Temporary Files
  182. A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'
  183. A.4.6 Time Zone Problems
  184. A.5 Query Related Issues
  185. A.5.1 Case-Sensitivity in Searches
  186. A.5.2 Problems Using DATE Columns
  187. A.5.3 Problems with null Values
  188. A.5.4 Problems with alias
  189. A.5.5 Deleting Rows from Related Tables
  190. A.5.6 Solving Problems with No Matching Rows
  191. A.5.7 Problems with Floating-Point Comparison
  192. A.6 Table Definition Related Issues
  193. A.6.1 Problems with ALTER table.
  194. A.6.2 How To Change the Order of Columns in a Table
  195. A.6.3 TEMPORARY table problems
  196. B Contributed Programs
  197. Utilities
  198. Debugging a MySQL server
  199. E.1.1 Compiling MYSQL for Debugging
  200. E.1.2 Creating Trace Files
  201. Use a Stack Trace
  202. E.1.5 Use Log Files to Find Cause of Errors in mysqld
  203. Making a Test Case If You Experience Table Corruption
  204. Debugging a MySQL client
  205. The DBUG Package
  206. Locking methods
  207. Environment Variables
  208. MySQL Regular Expressions

 

Startup

On Ubuntu linux, here is an example of how to start the mysqld daemon... 

/usr/sbin/mysqld --basedir=/usr                          \
                 --datadir=/var/lib/mysql                \
                 --skip-grant-tables                     \
                 --user=root                             \
                 --pid-file=/var/run/mysqld/mysqld.pid   \
                 --skip-external-locking                 \
                 --port=3306                             \
                 --socket=/var/run/mysqld/mysqld.sock &

Here is another method... 

/etc/init.d/mysql start

To shut down the server... 

mysqladmin -u root shutdown -S /var/lib/mysql/mysql.sock

...or run... 

/etc/init.d/mysql stop

To cycle the server... 

/etc/init.d/mysql restart

 

Connect to the Server

Typical connection command... 

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

Some MySQL installations allow users to connect as the anonymous (unnamed) user to the mysqld server running on the local host.... 

 mysql

To exit... 

mysql> quite
Bye

...or you can disconnect by pressing Control-D.

 

Entering Queries

Simple query to get MySQL version... 

mysql> select version(), current_date;
+----------------------------+--------------+
| version()                  | current_date |
+----------------------------+--------------+
| 5.0.38-Ubuntu_0ubuntu1-log | 2007-06-08   |
+----------------------------+--------------+

More sophisticated query that does calculation... 

mysql> select sin(pi()/2), (1446+10)*5;
+------------------+-------------+
| SIN(PI()/4)      | (1446+10)*5 |
+------------------+-------------+
| 0.70710678118655 |        7280 |
+------------------+-------------+
To enter multiple statements on a single line, end each statement with a semicolon... 

mysql> select version(); select now();
+----------------------------+
| version()                  |
+----------------------------+
| 5.0.38-Ubuntu_0ubuntu1-log |
+----------------------------+

+---------------------+
| now()               |
+---------------------+
| 2007-06-08 14:45:14 |
+---------------------+

MySQL determines the end of statements by looking for the terminating semicolon, not by looking for the end of the input line. So you can enter a statement across multiple lines... 

mysql> select
    -> user()
    -> ,
    -> current_date;
+--------------------+--------------+
| user()             | current_date |
+--------------------+--------------+
| katebush@localhost | 1999-03-18   |
+--------------------+--------------+

To cancel a command, enter \c. For example... 

mysql> select
    -> user()
    -> \c
mysql>

 

Use Databases

Use the show statement to find out what databases currently exist on the server: 

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CoreTasks          |
| CoreTypes          |
| ND                 |
| OptionalTasks      |
| OptionalTypes      |
...

To use database CoreTypes... 

mysql> use CoreTypes
Database changed

To show tables in a database... 

mysql> show tables
    -> ;
+------------------------+
| Tables_in_CoreTypes    |
+------------------------+
| mtc_antlib             |
| mtc_assertions         |
| mtc_custom-programming |
| mtc_description        |
| mtc_dirset             |
| mtc_filelist           |
| mtc_fileset            |
| mtc_filterchain        |
| mtc_filterset          |
| mtc_mapper             |
| mtc_namespace          |
| mtc_patternset         |
| mtc_permissions        |
| mtc_propertyset        |
| mtc_selectors-program  |
| mtc_selectors          |
| mtc_xmlcatalog         |
| mtc_zipfileset         |
+------------------------+
19 rows in set (0.00 sec)

To grant access to a database 

mysql> grant all on CoreTypes.* to your_mysql_name;

 

Create a Database

To create and use a database: 

mysql> create database CoreTypes;
mysql> use CoreTypes

Under Unix, database names are case-sensitive (unlike SQL keywords), so always refer to the database as CoreTypes, not as Coretypes, coretypes, or some other variant. This is also true for table names.

To select the database from the command-line... 

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

Note that "CoreTypes" is not the password. To supply a password on the command-line after the -p option, do so with no intervening space,... 

> mysql -h host -u user -pmypassword CoreTypes

 

Create a Table

To create a table... 

mysql> connect CoreTypes;
create table `mtc_antlib` 
(
    `page`      varchar( 32 ) not null,
    `id`        INT( 11 ) unsigned not null autoincrement,
    `name`      varchar( 255 ) not null,
    `mail`      varchar( 255 ) not null,
    `date`      datetime not null,
    `text`      text not null,
    `ip`        varchar( 255 ) not null,
    `url`       varchar( 255 ) not null,
    PRIMARY KEY ( `page`, `id` )
);

varchar allows column values that vary in length. You can pick any length from 1 to 255.

To verify that the table was created... 

mysql> describe mtc_antlib;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| page    | varchar(32)      | NO   | PRI |         |                |
| id      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255)     | NO   |     |         |                |
| mail    | varchar(255)     | NO   |     |         |                |
| date    | datetime         | NO   |     |         |                |
| text    | text             | NO   |     |         |                |
| ip      | varchar(255)     | NO   |     |         |                |
| url     | varchar(255)     | NO   |     |         |                |
+---------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

 

Load Data into a Table

Suppose you have the following data...

Name Owner Species Sex Birth Death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Max Michael dog m 1990-08-27
Penny Wendy dog m 1998-08-31 2005-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird
1997-12-09
Slim Michael snake m 1996-04-29

You could create a text file `pet.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the create table statement.

For missing values, use null values (\N). For example...

Whistler Gwen bird \N 1997-12-09 \N

To load the text file `pet.txt' into the pet table,... 

mysql> load data LOCAL infile "pet.txt" into table pet;

You can specify the column value separator and end of line marker explicitly in the load data statement if you wish, but the defaults are tab and linefeed.

To add new records one at a time... 

mysql> insert into pet
    -> values ('Puffball','Wendy','hamster','f','1999-03-30',null);

Note that string and date values are specified as quoted strings. Also, you can insert null directly to represent a missing value. You do not use \N.

 

Retrieve Information from a Table

To select all information from a table... 

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | null       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | null       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | null       |
| Max      | Michael  | dog     | m    | 1990-08-27 | null       |
| Penny    | Wendy  | dog     | m    | 1998-08-31 | 2005-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | null       |
| Whistler | Gwen   | bird    | null | 1997-12-09 | null       |
| Slim     | Michael  | snake   | m    | 1996-04-29 | null       |
| Puffball | Wendy  | hamster | f    | 1999-03-30 | null       |
+----------+--------+---------+------+------------+------------+

To update a record... 

mysql> update pet set birth = "2006-08-31" where name = "Penny";

To select only particular rows from the table... 

mysql> select * from pet where name = "Penny";
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Penny  | Wendy | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

To find animals born after 1998... 

mysql> select * from pet where birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | null  |
| Puffball | Wendy | hamster | f    | 1999-03-30 | null  |
+----------+-------+---------+------+------------+-------+

You can combine conditions, for example, to locate female dogs: 

mysql> select * from pet where species = "dog" and sex = "f";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | null  |
+-------+--------+---------+------+------------+-------+

The preceding query uses the and logical operator. There is also an or operator: 

mysql> select * 
       from pet 
       where species = "snake" 
          or species = "bird";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | null  |
| Whistler | Gwen  | bird    | null | 1997-12-09 | null  |
| Slim     | Michael | snake   | m    | 1996-04-29 | null  |
+----------+-------+---------+------+------------+-------+

"and" and "or" may be intermixed.... 

select * 
from pet 
where (species = "cat" and sex = "m")
   or (species = "dog" and sex = "f");
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | null  |
| Buffy | Harold | dog     | f    | 1989-05-13 | null  |
+-------+--------+---------+------+------------+-------+

 

Selecting Particular Columns

To know when your animals were born... 

mysql> select name, birth from pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Max      | 1990-08-27 |
| Penny    | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

To find out who owns pets... 

mysql> select owner from pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Michael  |
| Wendy  |
| Gwen   |
| Gwen   |
| Michael  |
| Wendy  |
+--------+

To minimise the output, retrieve each unique output record just once... 

mysql> select distinct owner from pet;
+--------+
| owner  |
+--------+
| Michael  |
| Wendy  |
| Gwen   |
| Harold |
+--------+

To get birth dates for dogs and cats only... 

mysql> select name, 
              species, 
              birth 
from pet
where species = "dog" 
   or species = "cat";
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Max    | dog     | 1990-08-27 |
| Penny  | dog     | 1989-08-31 |
+--------+---------+------------+

 

Sorting Rows

To sort by date: 

mysql> select name, birth from pet order by birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Penny    | 1989-08-31 |
| Max      | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

To force a case-sensitive sort, use the binary cast: 

mysql> select name, birth from pet order by binary(name)

To sort in reverse order... 

mysql> select name, birth from pet order by birth desc;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Max      | 1990-08-27 |
| Penny    | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

To sort on multiple columns... 

mysql> select name, species, birth from pet order by species, birth desc;

+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Max      | dog     | 1990-08-27 |
| Penny    | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

 

Date Calculations

Show, for each pet, birth date, current date, and age in years. 

select name, 
       birth, 
       current_date,
       (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) as age
from pet;

+----------+------------+--------------+------+
| name     | birth      | current_date | age  |
+----------+------------+--------------+------+
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Max      | 1990-08-27 | 2001-08-29   |   11 |
| Penny    | 1989-08-31 | 2001-08-29   |   11 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
+----------+------------+--------------+------+

Here, year() pulls out the year part of a date and right() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date.

The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if current_date occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful.

The query works, but the result could be scanned more easily if the rows were presented in some order. This can be done by adding an ORDER BY name clause to sort the output by name: 

select name, 
       birth, 
       current_date,
       (year(current_date)-year(birth))
       - (right(current_date,5)<right(birth,5)) as age
from pet 
order by name;

+----------+------------+--------------+------+
| name     | birth      | current_date | age  |
+----------+------------+--------------+------+
| Penny    | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Max      | 1990-08-27 | 2001-08-29   |   11 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
+----------+------------+--------------+------+

To sort the output by age rather than name... 

select name, 
       birth, 
       current_date,
       (year(current_date)-year(birth)) - (right(current_date,5)<right(birth,5)) as age
from pet order by age;
+----------+------------+--------------+------+
| name     | birth      | current_date | age  |
+----------+------------+--------------+------+
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Max      | 1990-08-27 | 2001-08-29   |   11 |
| Penny    | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
+----------+------------+--------------+------+

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is null. Then, for those with non-null values, compute the difference between the death and birth values: 

mysql> select name, birth, death,
    -> (year(death)-year(birth)) - (right(death,5)<right(birth,5))
    -> AS age
    -> from pet where death IS not null order by age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Penny  | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

The query uses death IS not null rather than death <> null because null is a special value.

What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several date-part extraction functions, such as year(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth): 

mysql> select name, birth, MONTH(birth) from pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Max      | 1990-08-27 |            8 |
| Penny    | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

Finding animals with birthdays in the upcoming month is easy, too. Suppose the current month is April. Then the month value is 4 and you look for animals born in May (month 5) like this: 

mysql> select name, birth from pet where MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

There is a small complication if the current month is December, of course. You don't just add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1).

You can even write the query so that it works no matter what the current month is. That way you don't have to use a particular month number in the query. DATE_ADD() allows you to add a time interval to a given date. If you add a month to the value of NOW(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays: 

mysql> select name, birth from pet
    -> where MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

A different way to accomplish the same task is to add 1 to get the next month after the current one (after using the modulo function (MOD) to wrap around the month value to 0 if it is currently 12): 

mysql> select name, birth from pet
    -> where MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

Note that MONTH returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

 

Working with null Values

The null value can be surprising until you get used to it. Conceptually, null means missing value or unknown value and it is treated somewhat differently than other values. To test for null, you cannot use the arithmetic comparison operators such as =, <, or <>. To demonstrate this for yourself, try the following query: 

mysql> select 1 = null, 1 <> null, 1 < null, 1 > null;
+----------+-----------+----------+----------+
| 1 = null | 1 <> null | 1 < null | 1 > null |
+----------+-----------+----------+----------+
|     null |      null |     null |     null |
+----------+-----------+----------+----------+

Clearly you get no meaningful results from these comparisons. Use the IS null and IS not null operators instead: 

mysql> select 1 IS null, 1 IS not null;
+-----------+---------------+
| 1 IS null | 1 IS not null |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Note that in MySQL, 0 or null means false and anything else means true. The default truth value from a boolean operation is 1.

This special treatment of null is why, in the previous section, it was necessary to determine which animals are no longer alive using death IS not null instead of death <> null.

Two null values are regarded as equal in a group by.

When doing an order by, null values are presented first if you do order by ... ASC and last if you do order by ... desc.

 

Pattern Matching

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi, grep, and sed.

SQL pattern matching allows you to use `_' to match any single character and `%' to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Note that you do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

To find names beginning with `b': 

mysql> select * from pet where name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | null       |
| Penny  | Wendy  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

To find names ending with `fy': 

mysql> select * from pet where name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | null  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | null  |
+--------+--------+---------+------+------------+-------+

To find names containing a `w': 

mysql> select * from pet where name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | null       |
| Penny    | Wendy | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | null | 1997-12-09 | null       |
+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use the `_' pattern character: 

mysql> select * from pet where name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | null  |
| Buffy | Harold | dog     | f    | 1989-05-13 | null  |
+-------+--------+---------+------+------------+-------+

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some characteristics of extended regular expressions are:

To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP.

To find names beginning with `b', use `^' to match the beginning of the name: 

mysql> select * from pet where name REGEXP "^b";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | null       |
| Penny  | Wendy  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+


mysql> select * from pet where name REGEXP "^[bB]";

To find names ending with `fy', use `$' to match the end of the name: 

mysql> select * from pet where name REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | null  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | null  |
+--------+--------+---------+------+------------+-------+

To find names containing a lowercase or uppercase `w', use this query: 

mysql> select * from pet where name REGEXP "w";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | null       |
| Penny    | Wendy | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | null | 1997-12-09 | null       |
+----------+-------+---------+------+------------+------------+

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value like it would be if you used a SQL pattern.

To find names containing exactly five characters, use `^' and `$' to match the beginning and end of the name, and five instances of `.' in between: 

mysql> select * from pet where name REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | null  |
| Buffy | Harold | dog     | f    | 1989-05-13 | null  |
+-------+--------+---------+------+------------+-------+

You could also write the previous query using the `{n}' ``repeat-n-times'' operator: 

mysql> select * from pet where name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | null  |
| Buffy | Harold | dog     | f    | 1989-05-13 | null  |
+-------+--------+---------+------+------------+-------+

 

Counting Rows

Databases are often used to answer the question, ``How often does a certain type of data occur in a table?'' For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of censuses on your animals.

Counting the total number of animals you have is the same question as ``How many rows are in the pet table?'' because there is one record per pet. The COUNT() function counts the number of non-null results, so the query to count your animals looks like this: 

mysql> select COUNT(*) from pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to find out how many pets each owner has: 

mysql> select owner, COUNT(*) from pet group by owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Michael  |        2 |
| Wendy  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

Note the use of group by to group together all records for each owner. Without it, all you get is an error message: 

mysql> select owner, COUNT(owner) from pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no group by clause

COUNT() and group by are useful for characterising your data in various ways. The following examples show different ways to perform animal census operations.

Number of animals per species: 

mysql> select species, COUNT(*) from pet group by species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

Number of animals per sex: 

mysql> select sex, COUNT(*) from pet group by sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| null |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

(In this output, null indicates sex unknown.)

Number of animals per combination of species and sex: 

mysql> select species, sex, COUNT(*) from pet group by species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | null |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

You need not retrieve an entire table when you use COUNT(). For example, the previous query, when performed just on dogs and cats, looks like this: 

mysql> select species, sex, COUNT(*) from pet
    -> where species = "dog" or species = "cat"
    -> group by species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

Or, if you wanted the number of animals per sex only for known-sex animals: 

mysql> select species, sex, COUNT(*) from pet
    -> where sex IS not null
    -> group by species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

 

Use More Than one Table

The pet table keeps track of which pets you have. If you want to record other information about them, such as events in their lives like visits to the vet or when litters are born, you need another table. What should this table look like? It needs:

Given these considerations, the create table statement for the event table might look like this: 

mysql> create table event (name varchar(20), date DATE,
    -> type varchar(15), remark varchar(255));

As with the pet table, it's easiest to load the initial records by creating a tab-delimited text file containing the information:

name date type remark
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Penny 1991-10-12 kennel
Max 1991-10-12 kennel
Max 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday

Load the records like this: 

mysql> load data LOCAL infile "event.txt" into table event;

Based on what you've learned from the queries you've run on the pet table, you should be able to perform retrievals on the records in the event table; the principles are the same. But when is the event table by itself insufficient to answer questions you might ask?

Suppose you want to find out the ages of each pet when they had their litters. The event table indicates when this occurred, but to calculate the age of the mother, you need her birth date. Because that is stored in the pet table, you need both tables for the query: 

mysql> select pet.name,
    -> (TO_DAYS(date) - TO_DAYS(birth))/365 AS age,
    -> remark
    -> from pet, event
    -> where pet.name = event.name and type = "litter";
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |
| Buffy  | 4.12 | 5 puppies, 2 female, 3 male |
| Buffy  | 5.10 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

There are several things to note about this query:

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. For example, to find breeding pairs among your pets, you can join the pet table with itself to pair up males and females of like species: 

select p1.name, 
       p1.sex, 
       p2.name, 
       p2.sex, 
       p1.species
from pet AS p1, pet AS p2
where p1.species = p2.species and p1.sex = "f" and p2.sex = "m";
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Max    | m    | dog     |
| Buffy  | f    | Penny  | m    | dog     |
+--------+------+--------+------+---------+

In this query, we specify aliases for the table name in order to refer to the columns and keep straight which instance of the table each column reference is associated with.

 

Getting Information About Databases and Tables

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

You have already seen show databases, which lists the databases managed by the server. To find out which database is currently selected, use the database() function: 

mysql> select database();
+------------+
| database() |
+------------+
| CoreTypes  |
+------------+

If you haven't selected any database yet, the result is blank.

To find out what tables the current database contains (for example, when you're not sure about the name of a table), use this command: 

mysql> show tables;
+---------------------+
| Tables in CoreTypes |
+---------------------+
| event               |
| pet                 |
+---------------------+

If you want to find out about the structure of a table, the describe command is useful; it displays information about each of a table's columns: 

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | null    |       |
| owner   | varchar(20) | YES  |     | null    |       |
| species | varchar(20) | YES  |     | null    |       |
| sex     | char(1)     | YES  |     | null    |       |
| birth   | date        | YES  |     | null    |       |
| death   | date        | YES  |     | null    |       |
+---------+-------------+------+-----+---------+-------+

Field indicates the column name, Type is the data type for the column, null indicates whether the column can contain null values, Key indicates whether the column is indexed, and Default specifies the column's default value.

If you have indexes on a table, show INDEX from tbl_name produces information about them.

 

Examples of Common Queries

Here are examples of how to solve some common problems with MySQL.

Some of the examples use the table shop to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (article, dealer) is a primary key for the records.

Start the command-line tool mysql and select a database: 

mysql your-database-name

(In most MySQL installations, you can use the database-name 'test').

You can create the example table as: 

create table shop (
 article INT(4) unsigned ZEROFILL DEFAULT '0000' not null,
 dealer  CHAR(20)                 DEFAULT ''     not null,
 price   DOUBLE(16,2)             DEFAULT '0.00' not null,
 PRIMARY KEY(article, dealer));

insert into shop values
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

Okay, so the example data is: 

mysql> select * from shop;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

 

The Maximum Value for a Column

``What's the highest item number?'' 

select MAX(article) AS article from shop

+---------+
| article |
+---------+
|       4 |
+---------+

 

The Row Holding the Maximum of a Certain Column

``Find number, dealer, and price of the most expensive article.''

In SQL-99 (and MySQL Version 4.1) this is easily done with a subquery: 

select article, dealer, price
from   shop
where  price=(select MAX(price) from shop)

Another solution is to sort all rows descending by price and only get the first row using the MySQL-specific LIMIT clause: 

select article, dealer, price
from   shop
order by price desc
LIMIT 1

NOTE: If there are several most expensive articles (for example, each 19.95) the LIMIT solution shows only one of them!

 

Maximum of Column per Group

``What's the highest price per article?'' 

select article, MAX(price) AS price
from   shop
group by article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

 

The Rows Holding the Group-wise Maximum of a Certain Field

``For each article, find the dealer(s) with the most expensive price.''

In SQL-99 (and MySQL Version 4.1 or greater), I'd do it with a subquery like this: 

select article, dealer, price
from   shop s1
where  price=(select MAX(s2.price)
              from shop s2
              where s1.article = s2.article);

 

Use user variables

You can use MySQL user variables to remember results without having to store them in temporary variables in the client.

For example, to find the articles with the highest and lowest price you can do: 

mysql> select @min_price:=MIN(price),@max_price:=MAX(price) from shop;
mysql> select * from shop where price=@min_price or price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

 

Use Foreign Keys

InnoDB tables supports checking of foreign key constraints.

You don't actually need foreign keys to join 2 tables. The only thing MySQL currently doesn't do (in table types other than InnoDB), is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from a table with a foreign key definition. If you use your keys like normal, it'll work just fine: 

create table person (
    id SMALLINT unsigned not null autoincrement,
    name CHAR(60) not null,
    PRIMARY KEY (id)
);

create table shirt (
    id SMALLINT unsigned not null autoincrement,
    style ENUM('t-shirt', 'polo', 'dress') not null,
    color ENUM('red', 'blue', 'orange', 'white', 'black') not null,
    owner SMALLINT unsigned not null REFERENCES person(id),
    PRIMARY KEY (id)
);

insert into person values (null, 'Antonio Paz');

insert into shirt values
(null, 'polo', 'blue', LAST_insert_ID()),
(null, 'dress', 'white', LAST_insert_ID()),
(null, 't-shirt', 'blue', LAST_insert_ID());

insert into person values (null, 'Lilliana Angelovska');

insert into shirt values
(null, 'dress', 'orange', LAST_insert_ID()),
(null, 'polo', 'red', LAST_insert_ID()),
(null, 'dress', 'blue', LAST_insert_ID()),
(null, 't-shirt', 'white', LAST_insert_ID());

select * from person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

select * from shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

select s.* from person p, shirt s
 where p.name LIKE 'Lilliana%'
   and s.owner = p.id
   and s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

 

Searching on Two Keys

MySQL doesn't yet optimise when you search on two different keys combined with or (searching on one key with different or parts is optimised quite well): 

select field1_index, field2_index from test_table where field1_index = '1'
OR  field2_index = '1'

The reason is that we haven't yet had time to come up with an efficient way to handle this in the general case. (The and handling is, in comparison, now completely general and works very well.)

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimisation is also very good if you are using very complicated queries where the SQL server does the optimisations in the wrong order. 

create TEMPORARY table tmp
select field1_index, field2_index from test_table where field1_index = '1';
insert into tmp
select field1_index, field2_index from test_table where field2_index = '1';
select * from tmp;
drop table tmp;

The above way to solve this query is in effect a UNION of two queries.

 

Calculating Visits Per Day

The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page. 

create table t1 (year year(4), month INT(2) unsigned ZEROFILL,
             day INT(2) unsigned ZEROFILL);
insert into t1 values(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);
select year,month,BIT_COUNT(BIT_OR(1<<day)) AS days from t1
       group by year,month;

Which returns:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

The above calculates how many different days was used for a given year/month combination, with automatic removal of duplicate entries.

 

Use autoincrement

The autoincrement attribute can be used to generate a unique identity for new rows: 

create table animals (
             id MEDIUMINT not null autoincrement,
             name CHAR(30) not null,
             PRIMARY KEY (id)
             );
insert into animals (name) values ("dog"),("cat"),("penguin"),
                                  ("lax"),("whale");
select * from animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
+----+---------+

You can retrieve the used autoincrement key with the LAST_insert_ID() SQL function or the mysql_insert_id() API function. Note: for a multi-row insert, LAST_insert_ID()/mysql_insert_id() will actually return the autoincrement key from the first inserted row. This allows multi-row inserts to be reproduced on other servers.

For MyISAM and BDB tables you can specify autoincrement on secondary column in a multi-column key. In this case the generated value for the autoincrement column is calculated as MAX(auto_increment_column)+1) where prefix=given-prefix. This is useful when you want to put data into ordered groups. 

create table animals (
             grp ENUM('fish','mammal','bird') not null,
             id MEDIUMINT not null autoincrement,
             name CHAR(30) not null,
             PRIMARY KEY (grp,id)
             );
insert into animals (grp,name) values("mammal","dog"),("mammal","cat"),
                  ("bird","penguin"),("fish","lax"),("mammal","whale");
select * from animals order by grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
+--------+----+---------+

Note that in this case, the autoincrement value will be reused if you delete the row with the biggest autoincrement value in any group.

 

Use mysql in Batch Mode

In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file: 

 mysql < batch-file

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

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

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

If you want the script to continue even if you have errors, you should use the --force command-line option.

Why use a script? Here are a few reasons:

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

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

But like this when run in batch mode: 

species
bird
cat
dog
hamster
snake

If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv.

You can also use scripts in the mysql command-line prompt by using the source command: 

mysql> source filename;

 

Use MySQL with Apache

There are programs that let you authenticate your users from a MySQL database and also let you log your log files into a MySQL table.

You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file: 

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

In MySQL you can do a variation of: 

load data infile '/local/access_log' into table table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

 

Database Administration

 

mysqld

In most cases you should manage mysqld options through option files.

mysqld and mysqld.server reads options from the mysqld and server groups. mysqld_safe read options from the mysqld, server, mysqld_safe and mysqld_safe groups. An embedded MySQL server usually reads options from the server, embedded and xxxxx_SERVER, where xxxxx is the name of the application.

mysqld accepts a lot of command-line options. Here follows some of the most common ones. For a full list execute mysqld --help.

--ansi Use SQL-99 syntax instead of MySQL syntax. See section Running MySQL in ANSI Mode.
-b, --basedir=path Path to installation directory. All paths are usually resolved relative to this.
--big-tables Allow big result sets by saving all temporary sets on file. It solves most 'table full' errors, but also slows down the queries where in-memory tables would suffice. MySQL is able to solve it automatically by using memory for small temporary tables and switching to disk tables where necessary.
--bind-address=IP IP address to bind to.
--console Write the error log messages to stderr/stdout even if --log-error is specified.
--character-sets-dir=path Directory where character sets are. See section The Character Set Used for Data and Sorting.
--chroot=path Put mysqld daemon in chroot environment at startup.
--core-file Write a core file if mysqld dies. For some systems also specify --core-file-size to mysqld_safe.

Note that on some systems, like Solaris, you will not get a core file if you are also using the --user option.

-h, --datadir=path Path to the database root.
--debug[...]= If MySQL is configured with --with-debug, you can use this option to get a trace file of what mysqld is doing.
--default-character-set=charset Set the default character set. See section The Character Set Used for Data and Sorting.
--default-table-type=type Set the default table type for tables.
--delay-key-write[= OFF | ON | ALL] How MyISAM DELAYED KEYS should be used. See section Tuning Server Parameters.
--des-key-file=filename Read the default keys used by DES_ENCRYPT() and DES_DECRYPT() from this file.
--enable-external-locking (was --enable-locking) Enable system locking. Note that if you use this option on a system on which lockd does not fully work (as on Linux), you will easily get mysqld to deadlock.
--enable-named-pipe Enable support for named pipes (only on NT/Win2000/XP).
-T, --exit-info This is a bit mask of different flags one can use for debugging the mysqld server; one should not use this option if one doesn't know exactly what it does!
--flush Flush all changes to disk after each SQL command. Normally MySQL only does a write of all changes to disk after each SQL command and lets the operating system handle the syncing to disk. See section A.4.1 What To Do If MySQL Keeps Crashing.
-?, --help Display short help and exit.
--init-file=file Read SQL commands from this file at startup.
-L, --language=... Client error messages in given language. May be given as a full path. See section Non-English Error Messages.
-l, --log[=file] Log connections and queries to file. See section The General Query Log.
--log-bin=[file] Log all queries that changes data to the file. Used for backup and replication.
--log-bin-index[=file] Index file for binary log file names. See section The Binary Log.
--log-error[=file] Log errors and startup messages to this file. The Error Log.
--log-isam[=file] Log all ISAM/MyISAM changes to file (only used when debugging ISAM/MyISAM).
--log-slow-queries[=file] Log all queries that have taken more than long_query_time seconds to execute to file.
--log-long-format Log some extra information to the update log. If you are using --log-slow-queries then queries that are not using indexes are logged to the slow query log.
--low-priority-updates Table-modifying operations (insert/delete/update) will have lower priority than selects. It can also be done via...

{insert | REPLACE | update | delete} low_priority ...

...to lower the priority of only one query, or by...

SET low_priority_updateS=1

...to change the priority in one thread.

--memlock Lock the mysqld process in memory. This works only if your system supports the mlockall() system call (like Solaris). This may help if you have a problem where the operating system is causing mysqld to swap on disk.
--myisam-recover [=option[,option...]]] Option is any combination of...

  • DEFAULT
  • BACKUP
  • FORCE
  • QUICK

You can also set this explicitly to "" if you want to disable this option. If this option is used, mysqld will on open check if the table is marked as crashed or if the table wasn't closed properly. The last option only works if you are running with...

--skip-external-locking

If this is the case mysqld will run check on the table. If the table was corrupted, mysqld will attempt to repair it.

The following options affects how the repair works.

Option Description
DEFAULT The same as not giving any option to --myisam-recover.
BACKUP If the data table was changed during recover, save a backup of the `table_name.MYD' datafile as `table_name-datetime.BAK'.
FORCE Run recover even if we will lose more than one row from the .MYD file.
QUICK Don't check the rows in the table if there aren't any delete blocks.
Before a table is automatically repaired, MySQL will add a note about this in the error log. If you want to be able to recover from most things without user intervention, you should use the options BACKUP,FORCE. This will force a repair of a table even if some rows would be deleted, but it will keep the old datafile as a backup so that you can later examine what happened.
--new From version 4.0.12, the --new option can be used to make the server behave as 4.1 in certain aspects, easing a 4.0 to 4.1 upgrade:

  • TIMESTAMP is returned as string with the format 'YYYY-MM-DD HH:MM:DD'. See section Column Types.
--pid-file=path Path to pid file used by mysqld_safe.
-P, --port=... Port number to listen for TCP/IP connections.
--one-thread Only use one thread (for debugging under Linux). See section E.1 Debugging a MySQL server.
--safe-mode Skip some optimise stages.
--safe-user-create If this is enabled, a user can't create new users with the grant command, if the user doesn't have insert privilege to the mysql.user table or any column in this table.
--skip-bdb Disable usage of BDB tables. This will save memory and may speed up some things.
--skip-concurrent-insert Turn off the ability to select and insert at the same time on MyISAM tables. (This is only to be used if you think you have found a bug in this feature.)
--skip-grant-tables This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)
--skip-host-cache Never use host name cache for faster name-ip resolution, but query DNS server on every connect instead. See section How MySQL uses DNS.
--skip-innodb Disable usage of Innodb tables. This will save memory and disk space and speed up some things.
--skip-external-locking (was --skip-locking) Don't use system locking. To use isamchk or myisamchk shut down the server. See section How Stable Is MySQL?.
--skip-name-resolve Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. See section How MySQL uses DNS.
--skip-networking Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. See section How MySQL uses DNS.
--skip-new Don't use new, possible wrong routines.
--skip-symlink Deprecated option in 4.0.13; Use --skip-symbolic-links instead.
--symbolic-links || --skip-symbolic-links Enable or disable symbolic link support.

On Unix this means that you can link a MyISAM table or datafile to another directory with with the create table .... INDEX/DATA DIRECTORY="path-to-dir" command. When you delete or rename a table, the file that the symbolic link points to will also be deleted/renamed.

--skip-safemalloc If MySQL is configured with --with-debug=full, all programs will check the memory for overruns for every memory allocation and memory freeing. As this checking is very slow, you can avoid this, when you don't need memory checking, by using this option.
--skip-show-database Don't allow show databases command, unless the user has the show databases privilege. From version 4.0.2 you should no longer need this option, since access can now be granted specifically with the show databases privilege.
--skip-stack-trace Don't write stack traces. This option is useful when you are running mysqld under a debugger. On some systems you also have to use this option to get a core file. See section E.1 Debugging a MySQL server.
--skip-thread-priority Disable using thread priorities for faster response time.
--socket=path Socket file to use for local connections instead of default /tmp/mysql.sock.
--sql-mode=option[,option[,option...]] Option can be any combination of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, SERIALIZE, ONLY_FULL_GROUP_BY. It can also be empty ("") if you want to reset this.

By specifying all of the above options is same as using --ansi. With this option one can turn on only needed SQL modes. See section Running MySQL in ANSI Mode.

--temp-pool Using this option will cause most temporary files created to use a small set of names, rather than a unique name for each new file. This is to work around a problem in the Linux kernel dealing with creating a bunch of new files with different names. With the old behaviour, Linux seems to 'leak' memory, as it's being allocated to the directory entry cache instead of the disk cache.
--transaction-isolation= { READ-UNCOMMITTED | READ-COMMITTED | REPEAtable-READ | SERIALIZABLE } Sets the default transaction isolation level.
-t, --tmpdir=path Path for temporary files. It may be useful if your default /tmp directory resides on a partition too small to hold temporary tables. Starting from MySQL 4.1, this option accepts several paths separated by colon :. They will be used in round-robin fashion.
-u, --user= [user_name | userid] Run mysqld daemon as user user_name or userid (numeric). This option is mandatory when starting mysqld as root.

To avoid a possible security hole where a user adds an --user=root option to somemy.cnf file, mysqld will only use the first --user option specified and give a warning if there are multiple options. Note that `/etc/my.cnf' and `datadir/my.cnf' may override a command line option - therefore it is recommended to put this option in `/etc/my.cnf'.

-V, --version Output version information and exit.
-W, --log-warnings (Was --warnings) Print out warnings like Aborted connection... to the `.err' file. Enabling it is recommended if you use replication for example (you will get more messages about what is happening, like network failures and reconnections). See section A.2.9 Communication Errors / Aborted Connection.

One can change most values for a running server with the SET command.

 

`my.cnf' Option Files

MySQL can read default startup options for the server and for clients from option files. MySQL reads default options from the following files on Unix:

Filename Purpose
/etc/my.cnf Global options
DATADIR/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=#
~/.my.cnf User-specific options

DATADIR is the MySQL data directory. Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up. --datadir has no effect on where the server looks for option files, because it looks for them before it processes any command-line arguments.

MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command-line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command-line or in option files take precedence over environment variable values.

Any long option that may be given on the command-line when running a MySQL program can be given in an option file as well (without the leading double dash). Run the program with --help to get a list of available options.

An option file can contain lines of the following forms:

#comment Comment lines start with `#' or `;'. Empty lines are ignored.
[group] Name of the program or group for which you want to set options. After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.
option Equivalent to --option on the command-line.
option=value Equivalent to --option=value on the command-line.

The client group allows you to specify options that apply to all MySQL clients (not mysqld). This is the perfect group to use to specify the password you use to connect to the server. (But make sure the option file is readable and writable only by yourself.)

Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences `\b', `\t', `\n', `\r', `\\', and `\s' in your value string (`\s' == blank).

Here is a typical global option file: 

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M

[mysqldump]
quick

Here is typical user option file: 

[client]
# The following password will be sent to all standard MySQL clients
password=my_password

[mysql]
no-auto-rehash
set-variable = connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you have a source distribution, you will find sample configuration files named `my-xxxx.cnf' in the `support-files' directory. If you have a binary distribution, look in the `DIR/support-files' directory, where DIR is the pathname to the MySQL installation directory (typically `/usr/local/mysql'). Currently there are sample configuration files for small, medium, large, and very large systems. You can copy `my-xxxx.cnf' to your home directory (rename the copy to `.my.cnf') to experiment with this.

All MySQL clients that support option files support the following options:

Option Description
--no-defaults Don't read any option files.
--print-defaults Print the program name and all options that it will get.
--defaults-file=full-path-to-default-file Only use the given configuration file.
--defaults-extra-file=full-path-to-default-file Read this configuration file after the global configuration file but before the user configuration file.

Note that the above options must be first on the command-line to work! --print-defaults may however be used directly after the --defaults-xxx-file commands.

Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply-specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

In shell scripts you can use the `my_print_defaults' command to parse the config files: 


 my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

The above output contains all options for the groups 'client' and 'mysql'.

 

Installing Many Servers on the Same Machine

In some cases you may want to have many different mysqld daemons (servers) running on the same machine. You may for example want to run a new version of MySQL for testing together with an old version that is in production. Another case is when you want to give different users access to different mysqld servers that they manage themselves.

One way to get a new server running is by starting it with a different socket and port... 

 MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
 MYSQL_TCP_PORT=3307
 export MYSQL_UNIX_PORT MYSQL_TCP_PORT
 scripts/mysql_install_db
 bin/mysqld_safe &

The environment variables appendix includes a list of other environment variables you can use to affect mysqld.

The above is the quick and dirty way that one commonly uses for testing. The nice thing with this is that all connections you do in the above shell will automatically be directed to the new running server!

If you need to do this more permanently, you should create an option file for each server. In your startup script that is executed at boot time you should specify for both servers:

mysqld_safe --defaults-file=path-to-option-file

At least the following options should be different per server:

The following options should be different, if they are used:

If you want more performance, you can also specify the following differently:

tmpdir can be set to a list of paths separated by colon :. They will be used in round-robin fashion. This feature can be used to spread load between several physical disks.

If you are installing binary MySQL versions (.tar files) and start them with ./bin/mysqld_safe then in most cases the only option you need to add/change is the socket and port argument to mysqld_safe.

 

Security Issues

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. Use grant and revoke commands to control access.

  1. Secure the USER table, which contains the mysql system password.

  2. You should not be able to run...

    mysql -u root

  3. Run...

    show grants

    ...and verify who has access. Remove unneccesary privileges the revoke command.

  4. Put MySQL behind a firewall or in a demilitarised zone (DMZ).

  5. Scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should be inaccessible from untrusted hosts.

  6. A simple to check if the MySQL port is open... 

     telnet server_host 3306

    If you get a connection and some garbage characters, the port is open. If telnet just hangs or the connection is refused, everything is OK; the port is blocked.

  7. To check whether MySQL data streams you can use snort or tcpdump

 

mysqld security

The following mysqld options affect security:

--local-infile[=(0|1)] If one uses --local-infile=0 then one can't use load data LOCAL infile.
--safe-user-create If this is enabled, an user can't create new users with the grant command, if the user doesn't have the insert privilege for the mysql.user table. To give a user access to create new users with those privileges that the user has right to grant... 

mysql> grant insert(user) ON mysql.user to 'user'@'hostname';
This will ensure that the user can't change any privilege columns directly, but has to use the grant command to give privileges to other users.
--skip-grant-tables This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)
--skip-name-resolve Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.
--skip-networking Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets.
--skip-show-database Don't allow show databases command, unless the user has the show databases privilege. From version 4.0.2 you should no longer need this option, since access can now be granted specifically with the show databases privilege.

 

Connecting to the MySQL Server

The mysql client can be started like this... 

 mysql [-h host_name] [-u user_name] [-pyour_pass]

Note that there is no space between -p or --password= and the password following it.

MySQL uses default values for connection parameters that are missing from the command-line:

Thus, for a Unix user joe, the following commands are equivalent: 

 mysql -h localhost -u joe
 mysql -h localhost
 mysql -u joe
 mysql

Other MySQL clients behave similarly.

On Unix systems, you can specify different default values to be used when you make a connection, so that you need not enter them on the command-line each time you invoke a client program. This can be done in a couple of ways:

 

Causes of Access denied Errors

If you encounter Access denied errors when you try to connect to the MySQL server, the following list indicates some courses of action you can take to correct the problem:

 

Database Backups

To get a consistent backup, do a LOCK tables on the relevant tables followed by FLUSH tables for the tables. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. The FLUSHtable is needed to ensure that the all active index pages is written to disk before you start the backup.

BACKUP table will not allow you to overwrite existing files.

If you want to make a SQL level backup of a table, you can use select into outfile or BACKUP table.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script.

  1. Do a full backup of the databases: 

     mysqldump --tab=/path/to/some/dir --opt --all

    ...or... 

     mysqlhotcopy database /path/to/some/dir
    You can also simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script mysqlhotcopy does use this method.

  2. Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See section The Binary Log. The binary log file(s) provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

If you have to restore something, try to recover your tables using REPAIR table or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure (this will only work if you have started MySQL with --log-bin.

  1. Restore the original mysqldump backup.

  2. Execute the following command to re-run the updates in the binary log: 

     mysqlbinlog hostname-bin.[0-9]* | mysql
    If you are using the update log (which will be removed in MySQL 5.0) you can use: 

     ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls is used to get all the update log files in the right order.

You can also do selective backups with select * into outfile 'file_name' from tbl_name and restore with load data infile 'file_name' REPLACE ... To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the table. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record on a unique key value.

If you get performance problems in making backups on your system, you can solve this by setting up replication and do the backups on the slave instead of on the master.

If you are using a Veritas filesystem, you can do:

  1. From a client (or Perl), execute: FLUSHtables WITH READ LOCK.

  2. From another shell, execute: mount vxfs snapshot.

  3. From the first client, execute: UNLOCK tables.

  4. Copy files from snapshot.

  5. Unmount snapshot.

 

BACKUP table Syntax

BACKUP table tbl_name[,tbl_name...] to '/path/to/backup/directory'

Copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. Currently works only for MyISAM tables. For MyISAM tables, copies `.frm' (definition) and `.MYD' (data) files. The index file can be rebuilt from those two.

Before using this command, please see section Database Backups.

During the backup, a read lock will be held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot, first issue LOCK tables obtaining a read lock for each table in the group.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always ``backup''
Msg_type One of status, error, info or warning.
Msg_text The message.

 

RESTORE table Syntax

RESTORE table tbl_name[,tbl_name...] from '/path/to/backup/directory'

Restores the table(s) from the backup that was made with BACKUP table. Existing tables will not be overwritten; if you try to restore over an existing table, you will get an error. Restoring will take longer than backing up due to the need to rebuild the index. The more keys you have, the longer it will take. Just as BACKUP table, RESTORE table currently works only for MyISAM tables.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always ``restore''
Msg_type One of status, error, info or warning.
Msg_text The message.

 

CHECK table Syntax

CHECK table tbl_name[,tbl_name...] [option [option...]]

option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK table works only on MyISAM and InnoDB tables. On MyISAM tables it's the same thing as running myisamchk -m table_name on the table.

If you don't specify any option MEDIUM is used.

Checks the table(s) for errors. For MyISAM tables the key statistics are updated. The command returns a table with the following columns:

Column Value
Table Table name.
Op Always ``check''.
Msg_type One of status, error, info, or warning.
Msg_text The message.

Note that you can get many rows of information for each checked table. The last row will be of Msg_type status and should normally be OK. If you don't get OK, or Table is already up to date you should normally run a repair of the table.

Table is already up to date means that the table the given TYPE told MySQL that there wasn't any need to check the table.

The different check types stand for the following:

Type Meaning
QUICK Don't scan the rows to check for wrong links.
FAST Only check tables which haven't been closed properly.
CHANGED Only check tables which have been changed since last check or haven't been closed properly.
MEDIUM Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time!

For dynamically sized MyISAM tables a started check will always do a MEDIUM check. For statically sized rows we skip the row scan for QUICK and FAST as the rows are very seldom corrupted.

You can combine check options as in: 

CHECK table test_table FAST QUICK;

Which would simply do a quick check on the table to see whether it was closed properly.

Note: that in some case CHECK table will change the table! This happens if the table is marked as 'corrupted' or 'not closed properly' but CHECK table didn't find any problems in the table. In this case CHECK table will mark the table as okay.

If a table is corrupted, then it's most likely that the problem is in the indexes and not in the data part. All of the above check types checks the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK didn't find an error in the datafile. (In most cases MySQL should find, under normal usage, any error in the data file. If this happens then the table will be marked as 'corrupted', in which case the table can't be used until it's repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example to be executed from cron) if you want to check your table from time to time. In most cases you FAST is to be prefered over CHANGED. (The only case when it isn't is when you suspect a bug you have found a bug in the MyISAM code.)

EXTENDED is only to be used after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key (this is very unlikely if a normal check has succeeded!).

Some things reported by check table, can't be corrected automatically:

 

REPAIR table Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] table tbl_name[,tbl_name...] [QUICK] [EXTENDED] [USE_FRM]

REPAIR table works only on MyISAM tables and is the same as running myisamchk -r table_name on the table.

Normally you should never have to run this command, but if disaster strikes you are very likely to get back all your data from a MyISAM table with REPAIR table. If your tables get corrupted a lot you should try to find the reason for this! See section A.4.1 What To Do If MySQL Keeps Crashing. See section MyISAM Table Problems.

REPAIR table repairs a possible corrupted table. The command returns a table with the following columns:

Column Value
Table Table name
Op Always ``repair''
Msg_type One of status, error, info or warning.
Msg_text The message.

Note that you can get many rows of information for each repaired table. The last one row will be of Msg_type status and should normally be OK. If you don't get OK, you should try repairing the table with myisamchk -o, as REPAIR table does not yet implement all the options of myisamchk. In the near future, we will make it more flexible.

If QUICK is given then MySQL will try to do a REPAIR of only the index tree.

If you use EXTENDED then MySQL will create the index row by row instead of creating one index at a time with sorting; this may be better than sorting on fixed-length keys if you have long CHAR keys that compress very well. This type of repair is like that done by myisamchk --safe-recover.

There is a USE_FRM mode for REPAIR. Use it if the `.MYI' file is missing or if its header is corrupted. In this mode MySQL will recreate the table, using information from the `.frm' file. This kind of repair cannot be done with myisamchk.

Warning: If mysqld dies during a REPAIR table, it's essential that you do at once another REPAIR on the table before executing any other commands on it. (It's of course always good to start with a backup). In the worst case you can have a new clean index file without information about the data file and when the next command you do may overwrite the data file. This is not a likely, but possible scenario.

Strictly before MySQL 4.1.1, REPAIR commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

 

Using myisamchk for Table Maintenance and Crash Recovery

You can check MyISAM tables with the CHECK table command. See section CHECK table Syntax. You can repair tables with the REPAIR table command. See section REPAIR table Syntax.

To check/repair MyISAM tables (`.MYI' and `.MYD') you should use the myisamchk utility. To check/repair ISAM tables (`.ISM' and `.ISD') you should use the isamchk utility.

In the following text we will talk about myisamchk, but everything also applies to the old isamchk.

You can use the myisamchk utility to get information about your database tables, check and repair them, or optimise them. The following sections describe how to invoke myisamchk (including a description of its options), how to set up a table maintenance schedule, and how to use myisamchk to perform its various functions.

You can, in most cases, also use the command OPTIMIZE tables to optimise and repair tables, but this is not as fast or reliable (in case of real fatal errors) as myisamchk. On the other hand, OPTIMIZE table is easier to use and you don't have to worry about flushing tables. See section OPTIMIZE table Syntax.

Even that the repair in myisamchk is quite secure, it's always a good idea to make a backup before doing a repair (or anything that could make a lot of changes to a table)

 

myisamchk Invocation Syntax

myisamchk is invoked like this: 

 myisamchk [options] tbl_name
<