MySQL-Utilities使用

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.