MySQL自带工具使用介绍

Published on 2016 - 06 - 05

MySQL数据库不仅提供了数据库的服务器端应用程序,还提供了大量的客户端工具程序,如mysql、mysqladmin、mysqldump等,都是大家熟悉的。虽然有些人对这些工具的功能都已经比较了解了,但是真正能将这些工具程序物尽其用的人可能并不是太多。或者知道得不全,也可能并不完全了解其中的某种特性,所以在这里我也简单地做一些介绍。

mysql

相信在所有MySQL客户端工具中,读者了解最多的就是mysql了,用得最多的也应该非它莫属。mysql的功能和Oracle的sqlplus一样,它为用户提供一个命令行接口来操作管理MySQL服务器。其基本的使用语法这里就不介绍了,大家只要运行一下“mysql--help”就会得到相应的基本使用帮助信息,如示例代码1所示:

上面的内容只是输出的一部分,省去了大家最常用的一些参数(因为大家应该已经很熟悉了),留下了部分个人认为可能不太经常用到,但是在有些情况下却能给我们带来意外惊喜的参数选项。

-e, --execute=name

首先看看“-e, --execute=name”参数,这个参数是告诉mysql,我只要执行“-e”后面的某个命令,而不是要通过mysql连接登录到MySQL Server上面。此参数在写一些基本的MySQL检查和监控的脚本中非常有用,我个人就经常在脚本中使用它。

-E, --vertical

如果在连接时使用了“-E, --vertical”参数,登入之后的所有查询结果都将以纵列显示,效果和我们在一条query之后以“\G”结尾一样,这个参数的使用场合不是特别多。

-H, --html & -X, --xml

“-H, --html”与“-X, --xml”这两个参数很有意思,在启用这两个参数之后,select出来的所有结果都会按照“Html”与“Xml”格式输出,在有些场合之下,比如希望用Xml或Html文件格式导出报表文件时,用它们是非常方便的。

--prompt=name

“--prompt=name”参数对于做运行维护的人来说非常重要,其主要功能是定制自己的mysql提示符的显示内容。在默认情况下,通过mysql登入数据库之后,mysql的提示符很简单——“mysql>”,没有其他任何附加信息。非常幸运的是mysql通过“--prompt=name”参数给我们提供了自定义提示信息的办法,可以通过配置显示登入的主机地址、登录用户名、当前时间、当前数据库schema、MySQL Server的一些信息等。我强烈建议将登录主机名、登录用户名和所在的schema这三项加入提示内容,因为当大家手边管理的MySQL越来越多,操作越来越频繁的时候,非常容易混淆自己当前所处的环境,造成在错误的环境执行了错误的命令,这会导致严重后果。如果我们在提示内容中加入了这几项,至少可以方便地提醒自己当前所处环境,以尽量减少犯错误的概率。

我个人的提示符定义为:"\u@\h : \d \r:\m:\s> ",显示效果:

--tee=name

“--tee=name”参数也是对运维人员非常有用的参数选项,用来告诉mysql将所有输入和输出内容都记录到文件里。当做一些较大维护变更的时候,为了方便被查,最好是将整个操作过程的所有输入和输出内容都保存下来。有了“--tee=name”参数,就再也不用通过copy屏幕来保存操作过程了。

-U, --safe-updates

“-U, --safe-updates”、“--select_limit=#”和“--max_join_size=#”三个参数都是与性能相关的参数。使用“-U, --safe-updates”参数之后,将禁止所有不能使用索引UPDATE和DELETE操作的请求,“--select_limit=#”的使用前提是有“-U, --safe-updates”参数,功能是限制查询记录的条数,“--max_join_size=#”也须要与“-U, --safe-updates”一起使用,限制参与join的最大记录数。

--show-warnings

“--show-warnings”参数作用是在执行完每一条query之后自动执行一次“show warnings”,显示出最后一次warning的内容。

上面介绍了部分不是太常使用但是很有特点的几个参数选项,实际上mysql程序支持非常多的参数选项,有其自身的参数,也有提交给MySQL Server的。mysql的所有参数选项都可以写在MySQL Server启动参数文件(my.cnf)的[mysql]参数group中,还有部分连接选项参数会从[client]参数group中读取,这样,很多参数就不必每次执行mysql的时候手工输入,而由mysql程序自动从my.cnf文件中读取。

mysqladmin

mysqadmin,顾名思义,提供的功能都是与MySQL管理相关的各种功能。如MySQL Server状态检查,各种统计信息的flush,创建/删除数据库,关闭MySQL Server等。mysqladmin所能做的事情,虽然大部分都可以通过mysql连接登录MySQL Server之后来完成,但是通过mysqladmin来完成操作会更简单更方便。这里介绍经常使用的几项功能,如示例代码2所示:

上面这三项功能是我在一些简单监控脚本中经常使用的,虽然得到的信息还比较有限,但是对于完成一些基本的监控来说,已经足够了。此外,还可以通过mysqladmin来调用START SLAVE和STOP SLAVE命令来维护Replication,以及KILL命令来终止某个连接到MySQL Server的线程,等等。

mysqldump

mysqldump工具大部分读者都比较熟悉,其功能就是将MySQL Server中的数据以Query语句的形式导出(dump)成文本文件。虽然mysqldump是作为MySQL的一种逻辑备份工具为大家所认识的,但我个人觉得称它为Query生成导出工具更合适一点,因为通过mysqldump所生成的文件,全部是Query语句,包括数据库和表的创建语句。当然,通过给mysqldump程序加“-T”选项参数,可以生成其他指定格式的文本文件。这项功能实际上是调用MySQL中的“select * into OUTFILE from ...”语句实现的。也可以通过“-d,--no-data”仅仅生成结构创建的语句。当生成Query语句的时候,字符集设置这一项比较关键,建议每次执行mysqldump程序的时候都通过尽量做到“--default-character-set=name”显式指定字符集内容,防止以错误的字符集生成不可用的内容。mysqldump所生成的SQL文件可以通过mysql工具执行。

mysqlimport

mysqlimport程序将以特定格式存放的文本数据(如通过“SELECT * INTO OUTFILE FROM ...”所生成的数据文件)导入指定的MySQL Server中,比如将一个标准的csv文件导入指定数据库的指定表中。mysqlimport工具实际上只是“LOAD DATA INFILE”命令的一个包装实现。

mysqlbinlog

mysqlbinlog程序的主要功能就是分析MySQL Server所产生的二进制日志(也就是大家所熟知的binlog)。当我们希望通过之前备份的binlog做一些指定时间之类的恢复时,mysqlbinlog可以帮助找出恢复操作须要做哪些事情。通过mysqlbinlog,可以把binlog中指定时间段或指定日志起始和结束位置的内容解析成Query语句,并导出到指定的文件中,在解析过程中,还可以通过指定数据库名称来过滤输出内容。

mysqlcheck

mysqlcheck工具程序可以检查(check)、修复(repair)、分析(analyze)和优化(optimize)MySQL Server中的表,但并不是所有的存储引擎都支持这4项功能,像InnoDB就不支持修复功能。实际上,mysqlcheck程序的这4项功能都可以通过mysql连接登录到MySQL Server之后来实现。

myisamchk

Myisamchk的功能有点类似“mysqlcheck -c/-r”,可以检查和修复MyISAM存储引擎的表,但只对MyISAM存储引擎的索引文件有效,不用登录连接MySQL Server即可完成操作。

myisampack

对MyISAM表进行压缩处理,以缩减占用的存储空间,一般主要用在归档备份的场景下,而且压缩后的MyISAM表会变成只读,不能进行任何修改操作。当我们归档备份某些历史数据表,希望该表能够提供高效的查询服务时,就可以通过myisampack工具对MyISAM表进行压缩。虽然更换成archive存储引擎也能够将表变成只读的压缩表,但是archive表是不支持索引的,而压缩后的MyISAM表仍然可以使用其索引。

mysqlhotcopy

mysqlhotcopy和其他的客户端工具程序不太一样的地方是,它不是C(或者C++)程序编写的,而是一个perl脚本程序,仅能在Unix/Linux环境下使用。它的主要功能就是对MySQL中的MyISAM存储引擎的表进行在线备份操作,其备份操作实际上就是通过对数据库中的表进行加锁,然后复制其结构、数据和索引文件来完成的。也可以通过指定“--noindices”告诉mysqlhotcopy不须要备份索引文件。

其他工具

除了上面介绍的这些工具程序之外,MySQL还自带了其他大量的工具程序:

  • 针对离线InnoDB文件做checksum的innochecksum,
  • 转换mSQL C API函数的msql2mysql,
  • dumpMyISAM全文索引的myisam_ftdump,
  • 分析处理slowlog的mysqldumpslow,
  • 查询MySQL相关开发包位置和include文件位置的mysql_config,
  • 向MySQL AB报告bug的mysqlbug,
  • 测试套件mysqltest和mysql_client_test,
  • 批量修改表存储引擎类型的mysql_convert_table_format,
  • 从更新日志中提取给定匹配规则的query语句的mysql_find_rows,
  • 更改MyIsam存储引擎表后缀名的mysql_fix_extensions,
  • 修复系统表的mysql_fix_privilege_tables,
  • 查看数据库相关对象结构的mysqlshow,
  • MySQL升级工具mysql_upgrade,
  • 通过给定匹配模式来中止客户端连接线程的mysql_zap,
  • 查看错误信息的perror,
  • 文本替换工具replace等一系列工具程序可供我们使用。

如果您希望在MySQL源代码的基础上做一些自己的修改,如修改MyISAM存储引擎的时候,可以利用myisamlog来进行跟踪分析MyISAM的log。

参考文档