一 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 | 杀掉匹配进程 |
| 输出符合条件 | |
| --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 | TRIGGER2.目标用户所需权限CREATE | ALTER | SELECT | INSERT | UPDATE | LOCK TABLES | SUPER | DROP | CREATE VIEW | CREATE ROUTINE | EXECUTE | EVENT | TRIGGER | GRANT OPTION3.可跨版本进行dbcopy 4.更新mysql-connector-python用于支持8.0数据库ERROR: Authentication plugin 'caching_sha2_password' is not supported5.由于通过网络传输考虑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.
