MySQL Utilities介绍
MySQL Utilities 提供一组命令行工具用于维护和管理 MySQL 服务器，包括：
管理工具 (克隆、复制、比较、差异、导出、导入)
复制工具 (安装、配置)
一般工具 (磁盘使用情况、冗余索引、搜索元数据)
安装MySQL-Utilities
wget https://downloads.mysql.com/archives/get/p/30/file/mysql-utilities-1.6.5.tar.gz
tar -xf mysql-utilities-1.6.5.tar.gz
cd mysql-utilities-1.6.5/
python ./setup.py build
python ./setup.py install
MySQL-Utilities工具列表
Binary Log Operations(二进制日志操作)
mysqlbinlogmove 二进制日志移动
mysqlbinlogpurge 二进制日志清理
mysqlbinlogrotate 二进制日志老化工具
Database Operations(数据库操作)
mysqldbexport 数据导出
mysqldbimport 数据导入
mysqldbcopy 库级别数据库复制
mysqldiff 数据库对象级别比较工具
mysqldbcompare 数据库库级别比较工具
General Operations(通用用的操作)
mysqldiskusage 磁盘空间查看
mysqlfrm 恢复故障表.frm文件
mysqluserclone 用户克隆工具
mysqluc Utilities帮助工具
mysqlindexcheck 索引检测工具
mysqlmetagrep 元数据过滤器
mysqlprocgrep 进程搜索及清理工具
High Availability Operations(高可用)
mysqlreplicate 主从复制工具
mysqlrpladmin 主从复制管理工具
mysqlrplcheck 主从复制检测工具
mysqlrplms 主从多元复制工具
mysqlrplshow 主从复制拓扑图工具
mysqlrplsync 主从复制同步工具
mysqlfailover 主从failover工具
mysqlslavetrx 从库事务跳过工具
Server Operations(服务器操作)
mysqlserverinfo 服务器信息查看工具
mysqlserverclone 服务器克隆工具
Specialized Operations(特殊操作)
mysqlauditadmin 审计管理工具
mysqlauditgrep 审计日志过滤工具
部分工具使用案例
[root@k8master opt]# mysql -uroot -proot
root@localhost:(none) 11:08:32> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+---------------+------+-----------+--------------------------------------+
| 93 | 192.168.1.139 | 3306 | 91 | 8e1d6d71-e109-11ee-8f74-525400b0aa61 |
| 92 | 192.168.1.138 | 3306 | 91 | 735ed25b-e1d1-11ee-a047-52540020123e |
+-----------+---------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
[root@node2 ~]# mysqlrplshow --master=root:root123@192.168.1.137:3306 --discover-slaves-login=root:root123 -v
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.1.137: ... connected.
# Finding slaves for master: 192.168.1.137:3306
# Replication Topology Graph
192.168.1.137:3306 (MASTER)
|
+--- 192.168.1.138:3306 [IO: Yes, SQL: Yes] - (SLAVE)
|
+--- 192.168.1.139:3306 [IO: Yes, SQL: Yes] - (SLAVE)
[root@node2 mysql-utilities-1.6.5]# mysqlrplcheck --master=root:root123@192.168.1.137:3306 --slave=root:root123@192.168.1.138:3306 -v
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.1.137: ... connected.
# slave on 192.168.1.138: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking for binary logging on master [pass]
Are there binlog exceptions? [pass]
Replication user exists? [pass]
Checking server_id values [pass]
master id = 91
slave id = 92
Checking server_uuid values [pass]
master uuid = 6f85418e-e1d1-11ee-bf1b-525400f7e1fa
slave uuid = 735ed25b-e1d1-11ee-a047-52540020123e
Is slave connected to master? [pass]
Check master information file [pass]
#
# Master information file:
#
Master_Log_File : mysql-bin.000002
Read_Master_Log_Pos : 154
Master_Host : 192.168.1.137
Master_User : slave
Master_Password : slave@123
Master_Port : 3306
Connect_Retry : 60
Master_SSL_Allowed : 0
Master_SSL_CA_File :
Master_SSL_CA_Path :
Master_SSL_Cert :
Master_SSL_Cipher :
Master_SSL_Key :
Master_SSL_Verify_Server_Cert : 0
Heartbeat : 30
Bind :
Ignored_server_ids : 0
Uuid : 6f85418e-e1d1-11ee-bf1b-525400f7e1fa
Retry_count : 86400
SSL_CRL :
SSL_CRL_Path :
Enabled_auto_position : 1
Channel_Name :
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Master lower_case_table_names: 1
Slave lower_case_table_names: 1
Checking slave delay (seconds behind master) [pass]
# ...done.
[root@node2 mysql-utilities-1.6.5]# mysqlindexcheck --server=root:root123@192.168.1.137:3306 --show-drops --show-indexes --stats --report-indexes -vvv sakila
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.1.137: ... connected.
# Checking indexes...
# Getting indexes for sakila.language
# Showing indexes from sakila.language:
#
+-----------+-----------+----------+--------+---------+----------------+--------------------+
| database | table | name | type | unique | accepts nulls | columns |
+-----------+-----------+----------+--------+---------+----------------+--------------------+
| sakila | language | PRIMARY | BTREE | True | False | language_id |
| sakila | language | idx_01 | BTREE | False | False | name, last_update |
| sakila | language | idx_02 | BTREE | False | False | name |
+-----------+-----------+----------+--------+---------+----------------+--------------------+
#
# The following index is a duplicate or redundant for table sakila.language:
#
CREATE INDEX `idx_02` ON `sakila`.`language` (`name`) USING BTREE
# may be redundant or duplicate of:
CREATE INDEX `idx_01` ON `sakila`.`language` (`name`, `last_update`) USING BTREE
#
# DROP statement:
#
ALTER TABLE `sakila`.`language` DROP INDEX `idx_02`;
#
# WARNING: Not enough data to calculate best/worst indexes.
#
# ...done.
[root@node2 mysql-utilities-1.6.5]# mysqldiff --server1=root:root123@192.168.1.137:3306 --server2=root:root123@192.168.1.138:3306 sakila:sakila
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 192.168.1.137: ... connected.
# server2 on 192.168.1.138: ... connected.
# Comparing `sakila` to `sakila` [PASS]
# Comparing `sakila`.`language` to `sakila`.`language` [PASS]
# Success. All objects are the same.
[root@node2 mysql-utilities-1.6.5]# mysqldiskusage --server=root:root123@192.168.1.137:3306 sakila --all
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.1.137: ... connected.
WARNING: You are using a remote server and the datadir cannot be accessed. Some features may be unavailable.
# Database totals:
+----------+--------+
| db_name | total |
+----------+--------+
| sakila | 0 |
+----------+--------+
Total database disk usage = 0 bytes
# Log information.
# general_log information not accessible from a remote host.
# slow_query_log information not accessible from a remote host.
# log_error information not accessible from a remote host.
# Binary log information:
Current binary log file = mysql-bin.000002
+-------------------+-------+
| log_file | size |
+-------------------+-------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 2788 |
+-------------------+-------+
Total size of binary logs = 2,965 bytes or 2.90 KB
# Relay log information not accessible from a remote host.
# InnoDB data information not accessible from a remote host.
#...done.
[root@k8master ~]# mysqldiskusage --server=root:root@localhost:3306 -a
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name | total |
+---------------------+-------------+
| test | 480,242 |
| mysql | 12,123,824 |
| performance_schema | 830,937 |
| sakila | 139,841 |
| sys | 480,242 |
+---------------------+-------------+
Total database disk usage = 13,574,844 bytes or 12.95 MB
# Log information.
# The general_log is turned off on the server.
+------------+----------+
| log_name | size |
+------------+----------+
| slow.log | 764 |
| error.log | 404,013 |
+------------+----------+
Total size of logs = 404,777 bytes or 395.29 KB
# Binary log information:
Current binary log file = mysql-bin.000004
+-------------------+-------+
| log_file | size |
+-------------------+-------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 2835 |
| mysql-bin.000003 | 217 |
| mysql-bin.000004 | 194 |
| mysql-bin.index | 76 |
+-------------------+-------+
Total size of binary logs = 3,499 bytes or 3.42 KB
# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+--------------+
| innodb_file | size |
+--------------+--------------+
| ib_logfile0 | 536,870,912 |
| ib_logfile1 | 536,870,912 |
| ibdata1 | 77,594,624 |
+--------------+--------------+
Total size of InnoDB files = 1,151,336,448 bytes or 1.07 GB
#...done.
浙公网安备 33010602011771号