MySQL Client tool

The MySQL database not only provides a server-side application for the database, but also provides a large number of client tools
procedures, such as mysql,mysqladmin,mysqldump and so on, are familiar to everyone. Although some people have
Have a better understanding of the features, but the ones that really make the most of these tools may not be too many, or
may not be fully aware of some of these characteristics. So here I also simply do an introduction.

1. mysql

Believe that in all MySQL client tools, the reader knows the most is MySQL, the most used should also not
He belongs to him. MySQL functions like Oracle's sqlplus, providing users with a command-line interface to manipulate management
MySQL server. Its basic use of grammar here is not introduced, everyone just run a "MySQL--help" on
The following basic usage Help information is available:
[Email protected]:~$ MySQL--help
MySQL Ver 14.14 distrib 5.1.26-rc, for Pc-linux-gnu (i686) using Editline
Wrapper
Copyright (C) 2000-2008 MySQL AB
This software comes with absolutely NO WARRANTY. This is the free software,
And you is welcome to modify and redistribute it under the GPL license
Usage:mysql [OPTIONS] [Database]
-?,--Help Display this help and exit.
... ...
-E,--execute=name execute command and quit. (Disables--force and history
File
-E,--vertical Print the output of a query (rows) vertically.
... ...
-H,--html produce HTML output.
-X,--xml produce XML output
... ...
--prompt=name Set the MySQL prompt to this value.
... ...
--tee=name Append everything into outfile. See Interactive Help (\h)
Also. Does not work in batch mode. Disable with
--disable-tee. This option was disabled by default.
... ...
-U,--safe-updates only allow to UPDATE and DELETE that uses keys.
--select_limit=# Automatic limit for Select when using--safe-updates
--max_join_size=# Automatic limit for rows in a join when using
--safe-updates
... ...
--show-warnings show warnings after every statement.
... ...
The above content is only part of the output, omitted to remove the most common parameters of the people (because you should
have been very familiar with), left some people think may not be too often used, but in some cases can give us
Some of the parameter options that bring unexpected surprises.
First look at the "-E,--execute=name" parameter, this parameter is to tell MySQL, I just execute "-e" after
Instead of logging on to MySQL Server via a MySQL connection. This parameter in our write some
Basic MySQL Check and monitor script is very useful, I personally often use in the script to him.

If you use the "-E,--vertical" parameter when connecting, all query results after logging in will be
Display, the effect is the same as when we end with "\g" after a query, the usage scenario for this parameter may not be particularly
Many.

The two parameters of "-H,--html" and "-X,--xml" are interesting, and after enabling these two parameters, the Select
All the results will be output in Html and XML format, and in some cases, like XML
or Html file format to export some of the report files, it is very convenient.

The "--prompt=name" parameter is a very important parameter option for those who do operations, and its main function
is to customize the display content of your own MySQL prompt. By default, after we log in to the database via MySQL,
The MySQL prompt is just a very simple content "mysql>", with no additional information. It's very fortunate that
MySQL provides us with the option of customizing the message via the "--prompt=name" parameter, which can be configured by configuring the explicit
Log in to the host address, login username, current time, current database Schema,mysql Server some of the letter
And so on. I personally strongly recommend the login host name, login user name and the schema of the three items to add the prompt content,
Because when you manage MySQL more and more and more frequently, it's very easy to operate
Do not care too much about the environment in which you are present and cause the wrong command to be executed in the wrong environment and cause serious consequences.
Case If we add these items to the prompt, at least it is more convenient to remind ourselves of the current environment,
To minimize the probability of making mistakes.

My personal prompt definition: "\\[email protected]\\h: \\d \\r:\\m:\\s>", shows the effect:
"[Email protected]: Test 04:25:45>"

The "--tee=name" parameter is also a very useful parameter option for operations personnel to tell MySQL that all the Lost
Input and output are recorded in the file. In some of our larger maintenance changes, in order to facilitate the investigation, it is best to the whole
All input and output contents of the operation process are preserved. With the "--tee=name" parameter, you never have to pass
Copy screen to save the operation process.

"-U,--safe-updates", "--select_limit=#" and "--max_join_size=#" are all three parameters
is a parameter for performance-related considerations. After you use the "-U,--safe-updates" parameter, all non-use
The index of the update and delete operations of the request, "--select_limit=#" is used only if there is a "-U,--safe-updates" parameter, the function is to limit the number of query records, "--max_join_size=#" also need with "-U,- -safe-updates "To limit the maximum number of records participating in a join.

The function of the "--show-warnings" parameter is to automatically execute the "show" after each query is executed.
Warnings ", showing the contents of the last warning.

The above only describes some of the few parameter options that are not too often used but very characteristic, in fact MySQL
The program supports very many parameter options, has its own parameters, and is also submitted to MySQL Server. MySQL's
Parameter options can be written in the MySQL Server startup parameters (my.cnf) parameter group, or
Some of the connection option parameters are read from the [client] parameter group so that many parameters do not have to be executed each time
MySQL is entered manually while the MySQL program itself automatically load these parameters from the My.cnf file.
If a reader friend wishes to have a deeper understanding of MySQL's other parameter options or MySQL's other more state-owned diagrams,
can be accessed through the MySQL official reference manual or by executing "MySQL--help" after getting help information
Self-experiment to do further deep understanding. Of course, if you are a basic can understand C language friend, then you finish
It is possible to discover more interesting content through the source code of the MySQL program.

2, Mysqladmin

usage:mysqladmin [OPTIONS] Command command ...
Mysqadmin, as the name implies, provides functions that are related to MySQL management. such as MySQL Server
Status checks, flush of various statistics, creation/deletion of databases, shutdown of MySQL Server, etc. Mysqladmin
Can do, although most of them can be logged on to MySQL Server through MySQL connection to complete, but
Most of the work done by mysqladmin is easier and more convenient. Here I will introduce what I have often used
Several common functions:
The ping command can easily detect if MySQL Server is still serving properly
[Email protected]:~# mysqladmin-u sky-ppwd-h localhost ping
Mysqld is alive
The status command can get several basic status values for the current MySQL Server:
[Email protected]:~# mysqladmin-u sky-ppwd-h localhost status
uptime:20960 threads:1 questions:75 Slow queries:0 opens:15 Flush
Tables:1 Open tables:9 Queries per second avg:0.3
Processlist Gets the connection thread information for the current database:
[Email protected]:~# mysqladmin-u sky-ppwd-h localhost processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 48 | Sky | localhost | | Query | 0 | | Show Processlist |
+----+------+-----------+----+---------+------+-------+------------------+
The above three functions are often used in some of my own simple monitoring scripts, although the information obtained is
is relatively limited, but for the completion of some relatively basic monitoring, is sufficient. In addition, you can also use the
Mysqladmin to start slave and stop slave,kill a thread connected to MySQL Server and so on.

3, Mysqldump

usage:mysqldump [OPTIONS] database [tables]
OR mysqldump [Options]--databases [options] DB1 [DB2 DB3 ...]
OR mysqldump [Options]--all-databases [options]
Mysqldump This tool I think most of the readers are probably more familiar with the function of the MySQL Server
Dump the data from the database into a text file in the form of an SQL statement. Although Mysqldump is done as a MySQL
A logical Backup tool is known to everyone, but I personally feel that he is more appropriate for the SQL build export tool because
The files generated by mysqldump are all SQL statements, including database and table creation statements. Of course, by
After you add the "-T" option parameter to the MYSQLDUMP program, you can generate a text file that is specified in non-SQL form. This
function is actually called the "select * into OUTFILE from ..." statement in MySQL. can also be
The statement created by the "-D,--no-data" only generates the structure. When declaring a SQL statement, the character set sets this
One is also more critical, it is recommended that each time you execute the mysqldump program, you can explicitly specify the character set content by "--default-character-set=name" as much as possible to prevent the creation of unusable content in the wrong character set.
The SQL files generated by mysqldump can be executed through the MySQL tool.

4, Mysqlimport

Usage:mysqlimport [OPTIONS] Database textfile ...
The Mysqlimport program is a text data that will be stored in a specific format, such as through the "select * into
OUTFILE from ... "The resulting data file is imported into a tool program in the specified MySQL Server, such as
Imports a standard CSV file into the specified table in a specified database. Mysqlimport tools are actually just
A wrapper implementation of the "Load Data infile" command.

5, Mysqlbinlog

Usage:mysqlbinlog [OPTIONS] Log-files
The main function of the Mysqlbinlog program is to analyze the binary logs generated by MySQL Server (that is, the large
familiar with Binlog). When we want to do some sort of recovery by the binlog of the previous backup
, Mysqlbinlog can help us find out what needs to be done to restore operations. Through Mysqlbinlog, we
Resolves a specified time period in Binlog or specifies the start and end of a log to a SQL statement, and
Export to the specified file, and during parsing, you can also filter the output by specifying the database name.

6, Mysqlcheck

Usage:mysqlcheck [OPTIONS] database [tables]
OR Mysqlcheck [OPTIONS]--databases DB1 [DB2 DB3 ...]
OR Mysqlcheck [OPTIONS]--all-databases
Mysqlcheck Tool program can check (check), repair (repair), analyze (analyze) and optimize
(optimize) tables in MySQL Server, but not all of the storage engines support all four of these features,
Repair functionality is not supported like Innodb. In fact, these four functions of the Mysqlcheck program can be connected via MySQL
After you log on to MySQL Server, perform the exact same task by executing the corresponding command.

7, Myisamchk

Usage:myisamchk [OPTIONS] tables[. MYI]
The function is somewhat similar to "mysqlcheck-c/-r", for checking and repairing tables of the MyISAM storage engine, but only for
The index file of the MyISAM storage engine is valid and can be done without logging on to the MySQL Server.

8, Myisampack

usage:myisampack [OPTIONS] filename ...
Compress the MyISAM table to reduce storage footprint, typically used in archive backup scenarios,
The compressed MyISAM table becomes read-only and cannot be modified. When we want to archive back up some calendars
To provide more efficient query services, you can use the Myisampack to work
The MyISAM table is compressed, because even though the replacement of the archive storage engine can turn the table into
A read-only compressed table, but the archive table is not supported by the index, and the compressed MyISAM table can still make
With its index.

9, Mysqlhotcopy

Usage:mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
Mysqlhotcopy and other client-side utilities The difference is that he's not written by C (or C + +) programs,
It is a Perl script that can only be used in a unix/linux environment. His main function is in MySQL.
MyISAM Storage engine's tables for online backup operations, which are actually backed up by a table in the database
Row lock, and then copy its structure, data and index files to complete the backup operation, of course, you can also specify "--noindices" to tell mysqlhotcopy do not need to back up the index file.

10. Other tools

In addition to the tools described above, MySQL also comes with a number of other tool programs, such as
Line Innodb file do checksum innochecksum, convert MSQL C API function Msql2mysql,
Dumpmyisam Full-text index myisam_ftdump, analysis processing slowlog mysqldumpslow, query MySQL
Mysql_config of the relevant development package location and include file location, Report bug Mysqlbug to MySQL AB,
Test suites Mysqltest and mysql_client_test, bulk modifying table storage engine type
Mysql_convert_table_format, you can extract the query statement for a given matching rule from the update log.
Mysql_find_rows, changing the mysql_fix_extensions of the MyIsam Storage engine table suffix, repairing the system table
Mysql_fix_privilege_tables, see the Mysqlshow,mysql Upgrade tool for database-related object structures
Mysql_upgrade, to kill the mysql_zap of the client connection thread by a given matching pattern, to view the error number information
Perror, the text replacement tool replace, and so on a series of tools are available for us to use. If you want the MySQL
Based on the source code to do some of their own modifications, such as modifying the MyISAM storage engine, you can use the Myisamlog
To perform a trace analysis of the MyISAM log.

MySQL Client tool