mysqldump
The mysqldump client is used to export existing table data, table structures, or both from the MySQL server. If requested, the exported data can include all necessary SQL statements required to re-create the dumped information. Furthermore, you can specify whether to dump one, some, or all databases found on the server, or even just specific tables in a given database.
You can invoke mysqldump using any of the following three syntax variations:
# mysqldump [options] database [tables]
# mysqldump [options] –databases [options] database1 [database2...]
# mysqldump [options] –all-databases [options]
Let’s consider a few examples. The first example dumps just the table structures of all databases found on a local server to a file named output.sql:
# mysqldump -u root -p –all-databases –no-data > mysql-data-structures.sql
Note that the output is being directed to a file; otherwise, the output would be sent to standard
output, the screen. Also, keep in mind that the .sql extension is not required. This extension is used here merely for reasons of convenience; you can use any extension you wish.
The next example dumps just the data of a single database, corporate:
# mysqldump -u root -p –no-create-info corporate > mysql-data-structures.sql
The final example dumps both the structure and the data of two tables located in the corporate database, including DROP TABLE statements before each CREATE statement. This is
particularly useful when you need to repeatedly re-create an existing database, because attempting to create already existing tables results in an error; thus the need for the DROP TABLE statements.
# mysqldump -u root -p –add-drop-table corporate product staff > output.sql
mysqlshow
The mysqlshow utility offers a convenient means for determining which databases, tables, and columns exist on a given database server. Its usage syntax follows:
mysqlshow [options] [database [table [column]]]
For example, suppose you want to view a list of all available databases:
# mysqlshow -u root -p
To view all tables in a particular database, such as mysql:
# mysqlshow -u root -p mysql
To view all columns in a particular table, such as the mysql database’s db table:
# mysqlshow -u root -p mysql db
Note that what is displayed depends entirely upon the furnished credentials. In the preceding examples, the root user is used, which implies that all information will be at the user’s disposal. However, other users will likely not have as wide-ranging access. Therefore, if you’re interested in surveying all available data structures, use the root user.
mysqlhotcopy
You can think of the mysqlhotcopy utility as an optimized mysqldump, using Perl, the MySQL DBI module, and various optimization techniques to back up one or several databases, writing the data to a file (or files) of the same name as the database that is being backed up. Although optimized, this utility comes at somewhat of a disadvantage insofar as it can be run only on the
same machine on which the target MySQL server is running. If you require remote backup capabilities, take a look at mysqldump or MySQL’s replication features. Three syntax variations are available:
# mysqlhotcopy [options] database1 [/path/to/target/directory]
# mysqlhotcopy [options] database1…databaseN /path/to/target/directory
# mysqlhotcopy [options] database./regular-expression/
As is the norm, numerous options are available for this utility, a few of which are demonstrated in the usage examples. In the first example, the corporate and mysql databases are copied to a backup directory:
# mysqlhotcopy -u root -p corporate mysql /usr/local/mysql/backups
The following variation of the first example adds a default file extension to all copied database files:
# mysqlhotcopy -u root -p –suffix=.sql corporate mysql /usr/local/mysql/backups
For the last example, a backup of all tables in the corporate database that begin with the word sales is created:
# mysqlhotcopy -u root -p corporate./^sales/ /usr/local/mysql/backups
Like all other MySQL utilities, you must supply proper credentials to use mysqlhotcopy’s functionality. In particular, the invoking user needs to have SELECT privileges for those tables being copied. In addition, you need write access to the target directory. Finally, the Perl DBI::mysql module must be installed.
mysqlimport
The mysqlimport utility offers a convenient means for importing data from a delimited text file into a database. It is invoked using the following syntax:
# mysqlimport [options] database textfile1 [textfile2...]
This utility is particularly useful when migrating to MySQL from another database product or legacy system, because the vast majority of storage solutions (MySQL included) are capable of both creating and parsing delimited data. An example of a delimited data file follows:
Hemingway, Ernest\tThe Sun Also Rises\t1926\n
Steinbeck, John\tOf Mice and Men\t1937\n
Golding, William\tLord of the Flies\t1954
In this example, each item (field) of data is delimited by a tab (\t), and each row by a newline (\n). Keep in mind that the delimiting characters are a matter of choice, because most modern storage solutions offer a means for specifying both the column and the row delimiters
when creating and reading delimited files. Suppose these rows were placed in a file called books.sql, and that you wanted to read this data from and write it to a database aptly called books:
# mysqlimport -u root -p –fields-terminated-by=\t \
>–lines-terminated-by=\n books books.sql
The executing user requires INSERT permissions for writing the data to the given table, in addition to FILE privileges to make use of mysqlimport.
myisamchk
Although it is widely acknowledged that MySQL is quite stable, certain conditions out of its control can result in corrupt tables. Such corruption can wreak all sorts of havoc, including preventing further insertions or updates, and even resulting in the temporary (and in extreme
cases, permanent) loss of data. If you experience any table errors or oddities, you can use the myisamchk utility to check MyISAM table indices for corruption, and repair them if necessary.
It’s invoked using the following syntax:
# myisamchk [options] /path/to/table_name.MYI
In the absence of any options, myisamchk just checks the designated table for corruption. For example, suppose you want to check the table named staff that resides in the corporate database:
# myisamchk /usr/local/mysql/data/corporate/staff.MYI
Varying degrees of checks are also available, each of which requires additional time but more thoroughly reviews the table for errors. Although the default is simply check (–check), there also exists a medium check (–medium-check) and an extended check (–extend-check). Only use the extended check for the most severe cases, because medium check will catch the overwhelming majority of errors, and consume considerably less time. You can also review extended information for each of these checks by supplying the –information (-i) option, which offers various table-specific statistics.
If problems are identified with the table, you’ll be notified accordingly. If an error is found, you can ask myisamchk to attempt to repair it by supplying the –recover (-r) option:
# myisamchk -r /usr/local/mysql/data/corporate/staff.MYI
Note that what is presented here is just a smattering of the options available to this utility. Definitely consult the manual before using myisamchk to check or repair tables. Also, you should only run myisamchk when the MySQL daemon is not running. If you don’t have the luxury of taking your database server offline, take a look at the next utility, mysqlcheck.
mysqlcheck
As of version 3.23.38, the mysqlcheck utility offers users the means for checking and, if necessary, repairing corrupted tables while the MySQL server daemon is running. It can be invoked in any of the three following ways:
# mysqlcheck [options] database [tables]
# mysqlcheck [options] –databases database1 [database2...]
# mysqlcheck [options] –all-databases
In addition to the typical user credentials and concerned databases and tables, you can specify whether you want to analyze (-a), repair (-r), or optimize (-o) by passing in the appropriate parameter. So, for example, suppose the staff table, located in the table corporate, became corrupted due to sudden hard-drive failure. You could repair it by executing:
# mysqlcheck -r corporate staff
Like myisamchk, mysqlcheck is capable of finding and repairing the overwhelming majority of errors. In addition, it offers a wide-ranging array of features. Therefore, before you use it to resolve any mission-critical problems, take some time to consult the MySQL manual to ensure that you’re using the most effective set of options.
