一 MySQL安全登陸加密工具mysql_config_editor實踐  

1.mysql_config_editor 是出現在mysql5.6.6之後版本的新特性。

2.可以給指定的連線和密碼生成一個加密檔案.mylogin.cnf,預設位於當前使用者root目錄下。

3.MySQL客戶端工具可透過讀取該加密檔案連線MySQL，避免重複輸入登入資訊，避免敏感資訊暴露。

引數	描述
--all	輸出所有配置
--debug(=debug_options)	輸出debug資訊
--host=host_name	主機
--login-path=name	登陸名
--password	密碼
--port=port_num	TCP埠
--socket=path	unix套接字
--user=user_name	使用者名稱
--verbose	除錯模式
--version	版本
--warn	警告並請求確認是否覆蓋登入路徑

注：可用以下命令獲得更細節的描述資訊

mysql_config_editor set –help
mysql_config_editor remove –help
mysql_config_editor print –help
mysql_config_editor reset --help

1.生成加密檔案

shell> mysql_config_editor set --login-path=mysql_3308 --host=192.168.111.187 --user=root --port=3308 --password

注意：使用者是要在mysql當中建立好的遠端使用者，若是本地使用者可能會報錯

2.檢視加密檔案

shell> mysql_config_editor print --all
[mysql_3308]
user = root
password = *****
host = 192.168.111.187
port = 3308

3.使用加密登入

shell> mysql --login-path=mysql_3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

shell> mysqladmin --login-path=mysql_3308 processlist
+----+------+--------------------+------+---------+------+------------+------------------+
| Id | User | Host               | db   | Command | Time | State      | Info             |
+----+------+--------------------+------+---------+------+------------+------------------+
| 3  | root | localhost          | hzmc | Sleep   | 1077 |            |                  |
| 8  | root | pxc-cluster2:36550 |      | Query   | 4    | User sleep | select sleep(30) |
| 10 | root | pxc-cluster1:33122 |      | Query   | 0    | starting   | show processlist |
+----+------+--------------------+------+---------+------+------------+------------------+

檢視所有連線mysql使用者的情況

4.刪除某個加密登陸

shell> mysql_config_editor remove --login-path=mysql_3308

注：mysql_3308是加密檔案登入名

或者直接刪除root目錄下的.mylogin.cnf檔案，若是有多個加密登陸，則會刪除全部加密登陸

5.重置所有加密

shell> mysql_config_editor reset

即刪除所有加密

或者直接刪除root目錄下的.mylogin.cnf檔案

mysqladmin --login-pathm=mysql_3308 processlist
+----+------+--------------------+------+---------+------+------------+------------------+
| Id | User | Host               | db   | Command | Time | State      | Info             |
+----+------+--------------------+------+---------+------+------------+------------------+
| 3  | root | localhost          | hzmc | Sleep   | 1077 |            |                  |
| 8  | root | pxc-cluster2:36550 |      | Query   | 4    | User sleep | select sleep(30) |
| 10 | root | pxc-cluster1:33122 |      | Query   | 0    | starting   | show processlist |
+----+------+--------------------+------+---------+------+------------+------------------+

檢視所有連線mysql使用者的情況

4.刪除某個加密登陸

shell> mysql_config_editor remove --login-path=mysql_3308

注：mysql_3308是加密檔案登入名

或者直接刪除root目錄下的.mylogin.cnf檔案，若是有多個加密登陸，則會刪除全部加密登陸

5.重置所有加密

shell> mysql_config_editor reset

即刪除所有加密

或者直接刪除root目錄下的.mylogin.cnf檔案

二 MySQL管理工具：MySQL Utilities 

MySQL Utilities 是官方提供的MySQL管理工具，功能面面俱到，主要有五個層面的工具：資料庫層面(複製、比較、差異、匯出、匯入)、稽核日誌層面、伺服器層面(例項克隆、例項資訊)、系統層面(磁碟使用情況、冗餘索引、搜尋後設資料、程式)、高可用性層面(主從複製、故障轉移、主從同步)。

工具是基於python編寫的，不需要安裝其他任何工具和庫。

1、MySQL Utilities軟體安裝

1 、MySQL Utilities軟體及依賴包下載

https://dev.mysql.com/downloads/connector/python/

2.MySQL Utilities軟體安裝

MySQL Utilities軟體需要Python2.6版本，即需要提前安裝MySQL Connector/Python通用版本（高於1.0.8）。

軟體安裝存在三種安裝方式，分別為RPM包、二進位制包、原始碼安裝。

建議採用RPM包安裝

1）RPM包安裝方式

rpm –ivh mysql-connector-python-2.1.7-1.el6.x86_64.rpm
rpm –ivh mysql-utilities-1.6.5-1.el6.noarch.rpm

2）二進位制包安裝

tar –xvf mysql-utilities-1.6.5.tar
cd mysql-utilities-1.6.5
python ./setup.py build
python ./setup.py install

1、mysqldiff工具 

可以比對兩個庫中缺少的表，相同的表缺少的欄位。

用來比較物件的定義是否相同，並顯示不同的地方。

即只能比較兩個資料庫都有的表之間的差異，也就是說它不會生成create語句。

常用引數	含義
--server1=SERVER1	伺服器一連線
--server2=SERVER2	伺服器二連線
--character-set=CHARSET	預設讀取 character_set_client引數
--force	檢測到不同不退出程式
-d DIFFTYPE, --difftype=DIFFTYPE	輸出格式 SQL直接生成修改語句(unified/context/differ/sql)
--changes-for=CHANGES_FOR	以server 為模板
--show-reverse	同時會包含server2和server1的修改。
--skip-table-options	跳過關於表名、AUTO_INCREMENT、ENGINE、CHARSET等差異

注：源端及目標端使用者均必須擁有SELECT許可權，同時還需要對mysql資料庫有SELECT許可權。

模板

mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

1.庫與庫之間比較

shell> mysqldiff --server1=root:Abcd321#@192.168.111.187:3308 --server2=root:Abcd321#@192.168.111.187:3308 hzmc:hzmc1 --force

注：建議新增--force引數，當檢測到不同物件繼續檢查 

2.物件與物件之間比較

shell> mysqldiff --server1=root:Abcd321#@192.168.111.187:3308 --server2=root:Abcd321#@192.168.111.187:3308 hzmc.cons1:hzmc1.cons1 --force

3.庫與庫之間比較並以server2為參照生成修改語句

shell> mysqldiff --server1=root:Abcd321#@192.168.111.187:3308 --server2=root:Abcd321#@192.168.111.187:3308 hzmc:hzmc1 -d sql --changes-for=server2

注:-d [unified|context|differ|sql] 選擇不同輸出格式

4.庫與庫之間比較跳過關於表名、AUTO_INCREMENT、ENGINE、CHARSET等差異

shell> mysqldiff --server1=root:Abcd321#@192.168.111.187:3308 --server2=root:Abcd321#@192.168.111.187:3308 hzmc:hzmc1 --skip-table-options --force -d sql

2.mysqlprocgrep工具

一種處理程式的工具，封裝了查詢INFORMATION_SCHEMA.PROCESSLIST的命令

常用引數	含義
--server=SERVER	連線到伺服器
-Q, --print-sql, --sql	包括建立儲存過程語句
--sql-body	只顯示儲存過程SQL_BOBY
--kill-connection	殺掉匹配會話
--kill-query	殺掉匹配程式
--print	輸出符合條件
--match-id=PATTERN	匹配ID
--match-user=PATTERN	匹配使用者
--match-host=PATTERN	匹配主機名
--match-db=PATTERN	匹配資料庫
--match-command=PATTERN	匹配狀態
--match-info=PATTERN	匹配資訊
--match-state=PATTERN	匹配宣告
--age=AGE	匹配SLEEP多於給定的時間(s,m,h,d,w)
-f FORMAT, --format=FORMAT	[grid[default],csv,tab,vertical]

標準命令

mysqlprocgrep --server=user:pass@host:port:socket [options]

注：使用者需擁有PROCESS 和 SUPER 許可權

示例

1.找出sleep超過90秒的使用者連線

shell> mysqlprocgrep  --server=root:Abcd321#@192.168.111.187:3308 -f vertical  --match-command='Sleep'  --age=90s  --print

2.找到user為root的使用者連線

shell> mysqlprocgrep  --server=root:Abcd321#@192.168.111.187:3308 -f vertical  --match-user=root  --print

3. kill掉root使用者狀態為sleep的程式且sleep超過90秒的使用者連線

shell> mysqlprocgrep  --server=root:Abcd321#@192.168.111.187:3308 -f vertical  --match-command='Sleep'  --age=90s --kill-connection

4.找到命令為select sleep命令的程式

shell> mysqlprocgrep  --server=root:Abcd321#@192.168.111.187:3308 --match-info='select sleep%'

5.殺死使用者root在一分鐘內建立的所有程式

shell> mysqlprocgrep  --server=root:Abcd321#@192.168.111.187:3308 --match-user='root'  --age=1m --kill-query

6.用正規表示式找出user名稱中存在'yz'的程式

shell> mysqlprocgrep  --server=root:mysql@192.168.186.11:3306 -G --match-user='yz'

7.用正規表示式找出user名稱中存在'yz'同時ID包含2的程式

shell> mysqlprocgrep  --server=root:mysql@192.168.186.11:3306 -G --match-user='yz' --match-id=2

等於 WHERE USER REGEXP 'yz' AND ID REGEXP '2'

8.kill空閒程式的儲存過程

shell> mysqlprocgrep --kill-connection --match-state=sleep --print-sql

3.mysqlindexcheck工具

MySQL允許使用者建立重複或冗餘的索引。重複索引是沒有優勢的，在某些情況下，冗餘的索引可能是有益的。但是重複和冗餘索引會減慢更新和插入操作的。

可透過該工具檢查重複或冗餘索引，對索引進行最佳化。

常用引數	含義
--server=SERVER	連線到伺服器
-d, --show-drops	顯示用於刪除索引的DROP語句
-i, --show-indexes	顯示每個表的索引
-s, --skip	跳過不存在的表
-f, --format=FORMAT	輸出格式，grid(預設)，tab，csv，vertical
--stats	顯示索引效能統計資料
--best=BEST	將索引統計資訊限制為最好的N個索引
--worst=WORST	將索引統計資訊限制為最差的N個索引
-r, --report-indexes	輸出既沒有唯一的索引也沒有主鍵的表

注:

1. –best及—worst 需與—stats引數配合使用

2. 連線使用者需要對mysql資料庫和需要檢測的庫(表)的SELECT許可權

解析示例

3.1 顯示每個表的索引

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --show-indexes hzmc

3.2 顯示既沒有唯一的索引也沒有主鍵的表

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --report-indexes  hzmc

3.3 顯示每個表的索引同時輸出用於刪除索引的DROP語句及既沒有唯一的索引也沒有主鍵的表

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --show-drops --show-indexes --report-indexes hzmc

3.4 根據索引統計資訊檢視效率最好的N個索引

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --stats  --best=N hzmc

3.5 根據索引統計資訊檢視效率最差的N個索引

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --stats  --worst=N hzmc

3.6 顯示索引但跳過不存在表

shell> mysqlindexcheck --server=root:Abcd321#@192.168.111.187:3308 --show-indexes --skip hzmc.cons1 hzmc.con1

注: 根據指定順序先後執行,若不加--skip引數，執行到不存在表直接報錯退出

4.mysqlfrm工具

mysqlfrm 是一個恢復性質的工具，用來讀取.frm檔案並從該檔案中找到表定義資料生成CREATE語句。在大多數情況下，生成的CREATE語句用於在另一個伺服器上建立表或進行診斷等。然而，有些功能是不儲存在.frm檔案中的，因此這些功能將被忽略的。如：外來鍵約束、自增長序列。

mysqlfrm 有兩種操作模式。預設的模式是再生個例項，使用--basedir選項或指定--server選項來連線到已經安裝的例項。這種過程不會改變原始的.frm檔案。該模式也需要指定--port選項來給再生的例項使用，該埠不能與現有的例項衝突。在讀取.frm檔案後，再生的例項將被關閉，所有的臨時檔案將被刪除的。

另一個模式是診斷模式，需要指定 --diagnostic 選項。byte-by-byte讀取.frm檔案 儘可能多的恢復資訊。該模式有更多的侷限性，不能校驗字符集。

當使用預設模式無法讀取檔案或者該伺服器上沒有安裝MySQL例項就使用診斷模式。

需要指定.frm檔案的路徑，也可以指定一個目錄，該目錄下的所有.frm檔案將被讀取。

常用引數	含義
--version	mysqlfrm版本
--license	檢視license
--help	檢視幫助資訊
--basedir=basedir	資料庫basedir
--diagnostic	逐位元組讀取frm檔案以形成CREATE語句，建議使用--server 或 --basedir
--new-storage-engine=NEW_ENGINE	改變儲存引擎
--frmdir=frmdir	新frm位置，僅指定--new-storage-engine引數使用
--port=port	派生埠
-s, --show-stats	檢視統計資訊
--user=user	啟動派生伺服器的使用者帳戶
--start-timeout=START_TIMEOUT	等待派生伺服器啟動的秒數，預設10s
-v, --verbose	資料庫資訊
-q, --quiet	關閉除CREATE語句和警告或之外的所有訊息錯誤

注：

1、某些引擎表在預設模式下不可讀取的。如PARTITION, PERFORMANCE_SCHEMA，必需在診斷模式下可讀。

2、關掉所有資訊除了CREATE 語句和警告或錯誤資訊，使用--quiet選項。

3、使用--show-stats 選項統計每個.frm檔案資訊。

4、使用--user 選項指定再生的例項以哪個許可權執行。

5、如果再生的例項超過10秒啟動，需調大--start-timeout 選項引數。

4.1 Server模式  server選項

shell> mysqlfrm --server=root:mysql@localhost --user=root --port=3307 /var/lib/mysql/data/yzw/cons1.frm

4.2 Server模式  basedir選項

shell> mysqlfrm --basedir=/usr/local/mysql --user=root --port=3307 /var/lib/mysql/data/test3/cons1.frm

4.3 診斷模式

shell> mysqlfrm --diagnostic /var/lib/mysql/data/yzw/cons1.frm

4.4 改變儲存引擎

shell> mysqlfrm --server=root:mysql@localhost --user=root --port=3307 --new-storage-engine=MyISAM --frmdir=/tmp /var/lib/mysql/data/test3/cons1.frm

注：需指定--new-storage-engine及-—frmdir引數

--new-storage-engine:新儲存引擎

-—frmdir：新生成FRM檔案儲存位置，預設名字為表名.frm

4.5、兩個模式比較

實際情況

mysql> show create table cons1\G
1. row
       Table: cons1
Create Table: CREATE TABLE `cons1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(40) DEFAULT 'aaa',
  `id2` bigint(20) DEFAULT NULL,
  `id3` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id2` (`id2`),
  KEY `cons1_c1` (`c1`) USING HASH,
  KEY `cons1_id3` (`id3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Server模式

shell> mysqlfrm --server=root:mysql@localhost --user=root --port=3307 /var/lib/mysql/data/yzw/cons1.frm
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Spawning server with --user=root.
# Starting the spawned server on port 3307 ... done.
# Reading .frm files
#
# Reading the cons1.frm file.
#
# CREATE statement for /var/lib/mysql/data/yzw/cons1.frm:
#
CREATE TABLE `yzw`.`cons1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(40) DEFAULT 'aaa',
  `id2` bigint(20) DEFAULT NULL,
  `id3` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id2` (`id2`),
  KEY `cons1_c1` (`c1`) USING HASH,
  KEY `cons1_id3` (`id3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#...done.

診斷模式 

shell> mysqlfrm --diagnostic /var/lib/mysql/data/yzw/cons1.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /var/lib/mysql/data/yzw/cons1.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
CREATE TABLE `yzw`.`cons1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `c1` varchar(120) DEFAULT NULL, 
  `id2` bigint(20) DEFAULT NULL, 
  `id3` bigint(20) NOT NULL, 
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `id2` (`id2`),
KEY `cons1_c1` (`c1`) USING HASH,
KEY `cons1_id3` (`id3`) USING BTREE
) ENGINE=InnoDB;
#...done.

結論：

1、診斷模式存在侷限性，不能校驗字符集; 

2、請優先使用Server模式，在Server模式無法讀取檔案或者該伺服器上沒有安裝MySQL例項才使用診斷模式。

5.mysqldbcopy工具

mysqldbcopy用於同(非同)例項，同(非同)伺服器之間資料庫複製。

常用引數	含義
--source=source	主庫端連線
--destination=DESTI	目標端連線
--character-set=CHARSET	客戶端字符集，預設為目標端character_set_client
-d, --drop-first	複製前刪除庫及物件
-x EXCLUDE, --exclude=EXCLUDE	包含指定物件
-a, --all	全部資料庫匯出
--skip=SKIP_OBJECTS	跳過某一型別物件 tables, views, triggers, procedures, functions,events, grants, data, create_db
--new-storage-engine=NEW_ENGINE	改變所有表引擎為目標端，如果目標端支援改引擎
--default-storage-engine=DEF_ENGINE	預設儲存引擎，一般搭配--new-storage-engine引數使用
--locking=locking	no-locks不使用任何表鎖  lock-all使用表鎖，無事務和一致性讀 snapshot預設，單一事務的一致性讀
-G, --basic-regexp,--regexp	預設情況下是LIKE匹配。使用正則，需要指定--regexp選項
--rpl-user=RPL_USER	複製使用者賬號密碼 rpl:passwd
--rpl=RPL_MODE, --replication=RPL_MODE	指定角色，master、slave
--skip-gtid	跳過GTID
--multiprocess=MULTIPROCESS	併發 預設為1(無併發)，0(程式數等於檢測到的cpu數)
--not-null-blobs	複製前對blob型別，NULL到NOT NULL轉換，複製完成後恢復為NULL，可能需重建索引

注：

1.源端使用者所需許可權

SELECT | SHOW  |  VIEW  |  EVENT  | TRIGGER

2.目標使用者所需許可權

CREATE  | ALTER  |  SELECT  | INSERT  |  UPDATE  |  LOCK TABLES   |  SUPER  |  DROP  | CREATE VIEW  |  CREATE ROUTINE    |  EXECUTE    | EVENT    | TRIGGER  |  GRANT OPTION

3.可跨版本進行dbcopy

4.更新mysql-connector-python用於支援8.0資料庫

ERROR: Authentication plugin 'caching_sha2_password' is not supported

5.由於透過網路傳輸

考慮max_allowed_packet引數

模板語句

mysqldbcopy --source=user:pass@host:port:socket --destination=user:pass@host:port:socket orig_db:new_db

例項

1.複製單庫

shell> mysqldbcopy --source=test:test@192.168.111.187:3306 --destination=root:Abcd321#@192.168.111.187:3308 sbtest

注：該方式不能在同一例項中使用

新增-vvv引數可以在過程中顯示具體的複製過程

2.複製單庫並改名

mysqldbcopy --source=test:test@192.168.111.187:3306 --destination=root:Abcd321#@192.168.111.187:3308 sbtest:test
mysqldbcopy --source=test:test@192.168.111.187:3306 --destination=test:test@192.168.111.187:3306 sbtest:test

可以在同一個例項下操作

3.排除庫中其中一個表

mysqldbcopy --source=test:test@192.168.111.187:3306 --destination=root:Abcd321#@192.168.111.187:3308 --exclude=sbtest.sbtest1 sbtest

4.修改儲存引擎

mysqldbcopy --source=root:mysql@192.168.239.65:3305 --destination=install_3306 --regexp --exclude=^u yjkj:text --new-storage-engine=myisam --drop-first

5.搭建主從(非GTID)

mysqldbcopy --source=root:123@192.168.1.24:3305 --destination=install_3306 yjkj:tt -vvv --drop-first --locking=lock-all --rpl=master --rpl-user=root

6. mysqluserclone工具

克隆一個MySQL使用者帳戶作為模板建立一個或多個新使用者

常用引數	含義
--source=source	主庫端連線
--destination=destination	目標端連線
-d, --dump	匯出使用者的GRANT語句
--force	假如匯入使用者存在刪掉原有使用者
--include-global-privileges	包括全域性許可權
-l,--list	羅列出源端所有使用者
-f FORMAT, --format=FORMAT	顯示格式為grid (default),tab, csv, or vertical 與-l 引數配合使用

1.羅列源端使用者

mysqluserclone --source=root:Abcd321#@192.168.111.187:3308 -l

2.羅列出源端所有使用者以及grant語句

mysqluserclone --source=root:Abcd321#@192.168.111.187:3308 -l -d
WARNING: Using a password on the command line interface can be insecure.
# Source on 192.168.111.187: ... connected.
# All Users:
+----------------+------------+
| user           | host       |
+----------------+------------+
| root           | %          |
| mysql.session  | localhost  |
| mysql.sys      | localhost  |
| root           | localhost  |
+----------------+------------+
# Dumping grants for user 'root'@'%'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
# Dumping grants for user 'mysql.session'@'localhost'
GRANT SUPER ON *.* TO 'mysql.session'@'localhost'
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost'
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost'
# Dumping grants for user 'mysql.sys'@'localhost'
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost'
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost'
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost'
# Dumping grants for user 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

3.建立以root@'%'為模板的其他使用者(sam 及 tom)

mysqluserclone --source=root:mysql@192.168.56.125:3307 --destination=root:mysql@192.168.56.125:3307 root@%  sam:secret1@192.168.0.1 tom:tom12@localhost

4.建立以root@'%'為模板的其他使用者(目標端使用者已存在)

mysqluserclone --source=root:mysql@192.168.56.125:3307 --destination=root:mysql@192.168.56.125:/tmp/mysql3500.sock mcbackup@192.168.56.121 --force yzw:yzw1@localhost

注：當目標端使用者已經存在，但是未新增—force引數，會報如下錯誤

ERROR: User yzw:yzw1@localhost already exists. Use --force to drop and recreate user.