Utilities and Programs for MySQL

only for RuBoard - do not distribute or recompile

Utilities and Programs for MySQL

In addition to the MySQL database server (mysqld), the MySQL suite includes a number of programs for administering the databases and the database server. These programs were installed when you installed via the RPM. isamchk and myisamchk are utilities that verify and repair database tables and files. mysqlaccess is used to grant, modify, and revoke user permissions. mysqladmin is a utility for higher-level administration, such as creating databases and shutting down the myslqd server. mysqldump comes in handy for exporting data or backing up tables in a pinch ; it outputs data in SQL insert statements in such a way that the output file can be fed back into a MySQL server (or other database) and the rows will be loaded. mysqlimport is the bulk loader utility, and mysqlshow displays information about database objects such as tables.

isamchk : Examining and Repairing Damaged Tables

If, for some unknown reason, a database table becomes corrupted, this utility can attempt a repair. The underlying data files that hold the data are called Indexed Sequential Access Method (ISAM) files.

Usage:

 % isamchk [OPTIONS] tablename.ISM 

It is important to note that if you are using 3.23 or greater, you are using the MyISAM file format as the default. Unless you specified your tables in the old format, you need to be using the myisamchk utility, not isamchk. The ISAM table structure is being replaced by MyISAM, and at some point in the future, ISAM will not be supported.

Keep in mind that the options listed in Tables 1.6 and 1.7 do not constitute a complete list! Give the command

 % isamchkh  more 

for a full list of options applicable to your system.

The [OPTIONS] portion of the usage line represents the options listed in Tables 1.6 and 1.7

A Quick Note Regarding the Options Listed for the Utilities

I have only included commands and options that I think are useful, unique, and/or relatively common. I have excluded those that I have never had any use for and cannot see any actual use for, either to a DBA or developer. Please do not think that just because there is no option listed for a particular task that such an option does not exist. As a starting point, find the help option for the utility and see if it lists anything that interests you or lends itself to your situation.

Table  1.6. Options to Use When Examining Tables

Option Short	Option Long	Description
-a	--analyze	Analyzes the distribution of keys in the target table.
-d	--description	Reports information on the target table.
-e	--extended-check	Thoroughly checks the target table. ( isamchk should report all problems even without this option.)
-I	--information	Reports table statistics.
-v	--verbose	Reports much more for all operations (a personal favorite).

Table  1.7. Options to Use When Repairing Tables

Option Short	Option Long	Description
-o	--safe-recover	A slower, older recovery method, but it can handle more cases than “r can.
-r	--recover	General fix; won t work to fix unique keys that are, in fact, not unique.
-q	--quick	Used with “r to get a faster repair; the data file isn t touched.

Data Files and Their Structure

If you are like me, you cannot help but wonder how the data exists down at the lowest level. Well, the short answer is not in text format. Feel free to create a small database and table and look at the files; you will find information in this section on how MySQL stores your data. If you want more than that, you will probably have to go to the source code.

ISAM Files:The Older Structure

This section describes the older ISAM structure, which has been replaced by MyISAM in version 3.23 and newer . Under the new format, you still have the option to use this type of storage, or perhaps you inherited an older implementation of MySQL.

Each table in the database is actually a set of three files: the *.ISD files contain the data, the *.ISM file contains information about the structure of the data, such as keys and indexes, and the *.frm files contain the structure of the table.

The isamchk utility only checks the *.ism file. The functions it can perform are similar to the Repair and Compact Database options available under Access.

If you are using 3.23 or newer, by default you will use the MyISAM storage format, which means you will then in turn use the myisamchk utility.

MyISAM File Structures

These are the newer file structures that allow greater flexibility than the old (and are the default in version 3.23 and newer). If I give the same commands as in the previous whirlwind tour section (Listing 1.1), and then examine the tables, I find that (on my system, anyway) three files are located in the /var/lib/mysql/test_db/ directory:

tbl_books_1.frm	72 bytes
tbl_books_1.MYI	1024 bytes
tbl_books_1.MYD	8604 bytes

The *.frm is the same type as the old structure, but the *.MYI files correspond to the old **.ISM files, and the *.MYD files correspond to the older *.ISD files. In this case, none of the files is very large, with tbl_books_1.frm weighing in at 8604 bytes. You can view the files, but because they are not stored in a text-friendly format, they won t have much meaning.

Listing 1.2 contains lines from Listing 1.1 that are of particular relevance here.

Listing 1.2 Code from the Whirlwind Tour Section

 11 mysql> create database test_db;  24 mysql> use test_db;  26 mysql> show tables;  29 mysql> create table tbl_books_1  30 (name varchar(30),  31   title char (30),  32   year int  33);  53 mysql> insert into tbl_books_1 values ('Jack Kerouac', 'On The Road', 1957);  56 mysql> insert into tbl_books_1 values ('Ayn Rand', 'Atlas Shrugged', 1934); 

myisamchk

This utility is the updated version of isamchk for the MyISAM storage format. Its usage is essentially the same:

 myisamchk [OPTIONS] tables.MYI 

The options listed for the isamchk utility also apply to myisamchk , so they aren t repeated here, but Table 1.8 lists additional flags that are of interest. These flags apply to myisamchk only.

Table  1.8. Additional Flags for myisamchk (Not Applicable to isamchk)

Option Short	Option Long	Description
-c	--check	Checks the target table for errors.
-m	--medium-check	Runs faster than an extended check, but finds only 99.99% of all errors (whatever that means).

mysqlaccess

Perhaps one of the most useful arguments to this utility is the “howto command-line argument. When you send the following command, you get examples of how this utility can be used:

 %mysqlaccesshowto 

Note that the %mysqlaccess “howto option is different from the “? option. This utility has few flags, and they are pretty straightforward, so I won t go over them here. Give the -? option a try for a full list that is applicable to your machine.

Usage is pretty simple for mysqlaccess :

 mysqlaccess [host [user [db]]] OPTIONS 

The user and the db must be provided here if no options are specified. If no host is provided, it assumes the local server (˜localhost).

For example, suppose you have installed a local, single-person-only db server and are logged in as root. If you send the command

 % mysqlaccess root mysql 

you get the following output:

 mysqlaccess Version 2.05, 17 Feb 2000  By RUG-AIV, by Yves Carlier ([e-mail])  Changes by Steve Harvey ([e-mail])  This software comes with ABSOLUTELY NO WARRANTY.  +++USING FULL WHERE CLAUSE+++  +++USING FULL WHERE CLAUSE+++  +++USING FULL WHERE CLAUSE+++  Access-rights  for USER 'root', from HOST 'localhost', to DB 'mysql'       +-----------------+---+     +-----------------+---+        Select_priv      Y       Shutdown_priv    Y         Insert_priv      Y       Process_priv     Y         Update_priv      Y       File_priv        Y         Delete_priv      Y       Grant_priv       Y         Create_priv      Y       References_priv  Y         Drop_priv        Y       Index_priv       Y         Reload_priv      Y       Alter_priv       Y        +-----------------+---+     +-----------------+---+  BEWARE: Everybody can access your DB as user 'root' from host 'localhost'        : WITHOUT supplying a password.        : Be very careful about it!!  The following rules are used:  db    : 'No matching rule'  host  : 'Not processed: host-field is not empty in db-table.'  user  :  'localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'  BUGs can be reported by email to [e-mail] 

As you can see, this gives a warning that anyone logged in at the local machine can access mysql.

mysqladmin

The general usage of this command is as follows :

 % mysqladmin [OPTIONS] command 

The options are pretty standard: -v for verbose, -t=XX for timeout to connect to the server, and so on. To see the latest list on your machine, send this command:

 % mysqladmin -?  more 

The command part is of more use on a regular basis. Not all the commands are listed in Table 1.9, but the most common and more interesting ones are. Again, to get a full list, send the preceding command.

Table  1.9. Parameters to the mysqladmin Utility

Command	Description
create db_name	Creates a database of name db_name .
drop db_name	Drops the database named.
flush-logs	Clears out all logs.
flush-privileges or reload	Reloads grant tables; either command does the same thing.
ping	Reports if mysqld is alive .
shutdown	Takes the server down.
status	Reports status from the server.

Command Description

mysqldump

If you have a database like the one described in the MyISAM file structure (that is, one table with two rows) and you send this command

 % mysqldump test_db 

you will get the following output:

 # MySQL dump 8.7  #  # Host: localhost    Database: test_db  #-------------------------------------------------------- # Server version     3.23.21-beta-log  #  # Table structure for table 'tbl_books_1'  #  CREATE TABLE tbl_books_1 (name varchar(30),    title varchar(30),    year int(11));  #  # Dumping data for table 'tbl_books_1'  #  INSERT INTO tbl_books_1 VALUES ('Ayn Rand','Atlas Shrugged',1934);  INSERT INTO tbl_books_1 VALUES ('Jack Kerouac','On The Road',1957); 

As you can see, this could be very useful in backing up, porting, or reloading the database.

Usage can be either of the following:

 % mysqldump [OPTIONS] database [tables] 

or

 % mysqldump [OPTIONS] 

In the second case, you must supply either the “databases or the --all-databases option.

Table 1.10 lists the important or unexpected flags that go with the mysqldump command.

Table  1.10. mysqldump Flags

Option Short	Option Long	Description
-A	--all-databases	Dumps all databases; same as “databases with all databases listed.
-B	--databases	Dumps several listed databases, as listed after the option. USE db_name will be included in the output.
-c	--complete-insert	Uses complete insert . In other words, inserts column names before the VALUES keyword in the insert statement.
-e	--extended-insert	Allows use of the new INSERT syntax. This means multiple rows per insert statement (in fact, all rows). Rows are surrounded by parentheses and separated by commas.
	--add-drop-table	Adds a droptable statement before each CREATE TABLE statement.
	--allow-keywords	Allows creation of column names that are keywords.
-f	--force	Continues the operation if an sql-error is generated.
-t	--no-create- info	Doesn t write table creation info.
-d	--no-data	Provides no row information. Only dumps the table structure, not the INSERT statements needed to repopulate the table.
-T	--tab=/ path /	Creates a tab-separated text file for each table to the given path (in other words, creates .sql and .txt files). This works only when mysqldump is run from the same machine that is running the mysqld daemon. When using this flag, you also get the following: --fields- terminated -by= --fields- enclosed -by= --lines-terminated-by=
-v	--verbose	Gives more detailed information on the action taken.
-w	--where=cond	Dumps only records meeting condition cond , such as the following: --where=user='jimf'

mysqlimport

This is the bulk import utility for MySQL; it is analogous to SQL Server s bcp utility. It is a command-line interface to the LOAD DATA statement.

Usage is as follows:

 % mysqlimport [OPTIONS] db__name file_name 

Some of the more interesting flags are listed in Table 1.11

Table  1.11. mysqlimport Common Flags

Option Short	Option Long	Description
-d	--delete	Empties the target table before loading it.
-f	--force	Continues loading even if an error occurs.
-i	--ignore	If an input row violates the unique key constraint for a row that is already in the table, keep the row that was there first and discard the input row. Compare to --replace .
-l	--lock-tables	Locks a table while it is being loaded.
-r	--replace	If an input row violates the unique key constraint for a row that is already in the table, this discards the row that was there and inserts the new row. Compare to --ignore.

mysqlshow

mysqlshow gives information about databases, tables, or columns . It is a command-line interface to the SHOW statement.

Usage:

 % mysqlshow [OPTIONS] [database_name [table_name [column]]] 

The specific options of importance are listed in Table 1.12

Table  1.12. Common mysqlshow Flags

Option Short	Option Long	Description
-i	--status	(3.23 or newer) Displays table information ”the same information that s shown by SHOW TABLE STATUS.
-k	--keys	Shows table key information as well as column information; only applicable when a table name is specified.

myisampack

myisampack compacts a MyISAM table into a much smaller space. myisampack is like gzip, but it works on tables. It operates on the .MYD file and compresses it to be much smaller (typically 30 “70% smaller than an OPTIMIZED table). After running myisampack , you can view the table normally, but you can t update it anymore. In other words, this is perfect for archiving old tables that you no longer want to update. You can unpack a table with 'optimize table' . This is not analogous to other database tools that clean up and compress data files, which can become bloated from numerous read/write operations if the dbms does not automatically reclaim unused space. In that case, you ll want to use OPTIMIZE TABLE.

Usage:

 %myisampack [options] filename.MYI 

and

 %myisamchk -rq filename.MYI 

Note that myisamchk must be run after myisampack . This is to update keys, which are not automatically updated. If you get an error 2 on open message when you run this utility, check to make sure you have given the correct filename and path (and remember that Linux is case-sensitive by default). Table 1.13 lists some of the options for myisampack .

Table  1.13. Common myisampack Options

Option Short	Option Long	Description
-b	--backup	Backs up the table first to tbl_name.old.
-f	--force	Packs the table even if it gets bigger or a tempfile exists.
-j	--join='tbl'	Joins all given tables into one, called tbl .All tables named must have the same structure.
-t	--test	Only tests packing the table.
-v	--verbose	Details output mode.
-w	--wait	Tells the command to wait and try again if the table is in use.
-#	--debug=file	Outputs debug info to a file.
-?	--help	Displays option info and exits.

only for RuBoard - do not distribute or recompile