mysql utility_MySQL Utility 认知（1)

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.