MySQL Utility 认知（1)

mysql untility 是 内嵌于mysql workbench 的一些脚本，展现给我们的是一个简单 的命令，我们 通过这些 命令可以很方便的管理和维护mysql服务器。

目前在 mySQL Workbench 5.2.47 版本中的 mysql untility 里面有如下命令：

1： mysqlauditamin  
作用：主要用来监察审计日志，但mysql 服务器必须安装 或启用 Audit plugin ，不然这个命令使用不了
语法：mysqlauditadmin --server=username:passwd@hostname:port --show-options

2:mysqlauditgrep
作用：看字面意思应该是查找审计日志的你想要的内容，和linux 的grep 用途相似
语法：mysqlauditgrep [options]  audit_log_file ,具体可以通过 mysqlauditgrep --help 查看

3:mysqldncompare
作用：可以用来比对2个数据库之间的差别，生产报告
语法：mysqldbcompare --server1=user:passwd@host1:port --server2=user:passwd@host2:port  db1:db2

例如：
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbcompare.exe --server1=root:sbcenter@172.72.15.41 --server2=root:sbcenter@172.72.15.40 gjj:gjj
-a  --changes-for=server2 --show-reverse -f tab
# server1 on 172.72.15.41: ... connected.
# server2 on 172.72.15.40: ... connected.
# Checking databases gjj on server1 and gjj on server2
#
# WARNING: Objects in server1.gjj but not in server2.gjj:
#        TABLE: test1
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     gjj_table                               pass    pass    SKIP
#
No primary key found.

# TABLE     gjj_table1                              pass    pass    SKIP
#
No primary key found.

# TABLE     gjj_table2                              pass    pass    SKIP
#
No primary key found.

# TABLE     test                                    pass    FAIL    SKIP
#
# Row counts are not the same among gjj.test and gjj.test.
#
No primary key found.


# Database consistency check failed.
#
# ...done

C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>

这里有test 表中的行数不一致，还有server2中没定义test1 表！！

4:mysqlcopy
作用：在不同的mysql服务器之间复制数据库，在同服务器内复制数据库
语法：mysqldbcopy.exe --source=user:pass@host:port:socket  --destination=user:pass@host:port:socket orig_db:new_db

例如：
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbcopy.exe --source=root:sbcenter@172.72.15.41 --destinatio
n=root:sbcenter@172.72.15.40 test_new:test_new_des
# Source on 172.72.15.41: ... connected.
# Destination on 172.72.15.40: ... connected.
# WARNING: A partial copy from a server that has GTIDs enabled will by default include the GTIDs of all transactions, ev
en those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip
-gtid option. To export all databases, use the --all option and do not specify a list of databases.
ERROR: The copy operation contains GTID statements that require the global gtid_executed system variable on the target t
o be empty (no value). The gtid_executed value must be reset by issuing a RESET MASTER command on the target prior to at
tempting the copy operation. Once the global gtid_executed value is cleared, you may retry the copy.

C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbcopy.exe --source=root:sbcenter@172.72.15.41 --destinatio
n=root:sbcenter@172.72.15.40 test_new:test_new_des
# Source on 172.72.15.41: ... connected.
# Destination on 172.72.15.40: ... connected.
# WARNING: A partial copy from a server that has GTIDs enabled will by default include the GTIDs of all transactions, ev
en those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip
-gtid option. To export all databases, use the --all option and do not specify a list of databases.
# GTID operation: SET @MYSQLUTILS_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
# GTID operation: SET @@SESSION.SQL_LOG_BIN = 0;
# GTID operation: SET @@GLOBAL.GTID_PURGED = '42077619-65AC-11E2-9484-000C290429AE:1-8,
E2F3ECB6-613B-11E2-B792-000C29E30F3D:1-135512';
# Copying database test_new renamed as test_new_des
# Copying TABLE test_new.test1
# Copying data for TABLE test_new.test1
# GTID operation: SET @@SESSION.SQL_LOG_BIN = @MYSQLUTILS_TEMP_LOG_BIN;
#...done.

C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>
注意：当一开始执行mysqldbcopy的时候，需要在目标库上reset master 清空gtid，方可用此命令

5：mysqldbexport
作用：从数据库中导出元数据和数据，导出的格式可以是：sql，, CSV, TAB, Grid, Vertical
语法：mysqldbexport.exe --server=user:pass@host:port:socket db1, db2, db3
例如：
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbexport.exe --server=root:sbcenter@172.72.15.41 -e both test_new > c:\export.ext

其中 -e both  选项是导出数据和元数据

6：mysqldbimport
作用：和mysqldbexport 相反，把元数据和数据导入到数据库中
语法：mysqldbimport.exe --server=user:pass@host:port:socket db1.csv db2.sql db3.grid
例如：
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldbimport.exe --server=root:sbcenter@172.72.15.40 -i both  c:\export.txt

7：mysqldiff
作用：比较对象的定义，并输出报告，感觉是mysqldbcompare 的细分版
语法：mysqldiff.exe --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

8：mysqldiskusage
作用：查看数据磁盘的使用情况
语法： mysqldiskusage.exe --server=user:pass@host:port:socket db1 --all
例如：
C:\Program Files\MySQL\MySQL Workbench 5.2 CE\utilities>mysqldiskusage.exe --server=root:sbcenter@172.72.15.41 gjj -all
# Source on 172.72.15.41: ... connected.

NOTICE: Your user account does not have read access to the datadir. Data sizes will be calculated and actual file sizes
may be omitted. Some features may be unavailable.

# Database totals:
+----------+-------------+
| db_name  |      total  |
+----------+-------------+
| gjj      | 11,616,256  |
+----------+-------------+

Total database disk usage = 11,616,256 bytes or 11.00 MB

# Log information.
# The general_log is turned off on the server.
# The slow_query_log is turned off on the server.
# log_error information is not accessible. Check your permissions.
# Binlog information not accessible. Check your permissions.
# Binlog information not accessible. Check your permissions.
# InnoDB data file information is not accessible. Check your permissions.
#...done.

暂时到这里了！