MySQL升级相关小脚本

最新推荐文章于 2024-02-08 14:20:45 发布

weixin_34232617

最新推荐文章于 2024-02-08 14:20:45 发布

阅读量85

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/3023401/blog/1791876

版权

2019独角兽企业重金招聘Python工程师标准>>>

前言：在某些特殊的情况下，我们希望能拿到全量的mysql授权表信息，手动操作比较麻烦，这时候就需要一个脚本

一、MySQL权限表mysql.user批量导出脚本

1、备份用户权限的脚本

mysql version<5.7:

[mysql@gz-mysql-offline02 ~]$ cat /home/mysql/scripts/mysql_exp_grant.sh
#/bin/bash
#Function export user privileges

pwd=123456
port=3306
expgrants()
{
  /usr/bin/mysql -B -u'root' -p${pwd} -h'127.0.0.1' -P${port} -N $@ -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | \
  /usr/bin/mysql -u'root' -p${pwd} -h'127.0.0.1' -P${port} $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}

expgrants > ./grants.sql

mysql version>=5.7:

[mysql@gz-mysql-offline00 scripts]$ cat mysql_exp_grant.sh
#/bin/bash
#Function export user privileges

pwd=123456
port=3306
expgrants()
{
  mysql -B -u'root' -p${pwd} -h'127.0.0.1' -P${port} -N $@ -e "SELECT CONCAT(  'SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -h'127.0.0.1' -P${port} -f  $@ | \
  sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'

  mysql -B -u'root' -p${pwd} -h'127.0.0.1' -P${port} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -h'127.0.0.1' -P${port} -f  $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}

expgrants > ./grants.sql

2、生成的sql语句（摘自参考）

[root@HKBO ~]# head grants.sql  
-- Grants for root@127.0.0.1   
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*EB3EA446C759C9DA93F84FCB56430DBEF051A9DD' WITH GRANT OPTION;  
GRANT ALL PRIVILEGES ON `CNBO0815`.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;  
  
-- Grants for root@172.16.10.%   
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.10.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';  
  
-- Grants for CNBO@192.168.1.%   
GRANT USAGE ON *.* TO 'CNBO'@'192.168.1.%' IDENTIFIED BY PASSWORD '*ABD91BAD4A3448428563952E281015B237310EA8';

3、恢复用户权限

将生成的脚本在目标服务器上执行即可。 mysql -uname -ppwd <grants.sql
需要注意：
a、目标服务上为非空服务器，已经存在一些账户及权限应考虑会覆盖的问题。
b、如果仅仅需要迁移非root用户，可以在原脚本中添加过滤条件，即 where user<>'root' 。

原文：https://blog.csdn.net/leshami/article/details/40106401

二、MySQL netstat抓取流量小脚本

#/bin/bash
#auth: tom
#time: 2018-04-16
#function: count mysql connections
while true
do
  netstat -an|grep -w 10.0.0.8:3306|awk -F '[ ]+' '{print $5}'|awk -F '[:]' '{print $1}'|sort|uniq >>./netstat.log
  sleep 1
done

三、MySQL抓取processlist小脚本

#/bin/bash
#auth: tom
#time: 2018-04-28
#function: count mysql processlist
while true
do
  echo "+--------+------------------+-------------------+--------------------+---------+--------+--------------------------------------------------------+------------------+" >>./process_list.log
  mysqladmin -utest -p'test123' -h10.0.0.8 -P3306 processlist|grep confluencedb >>./process_list.log
  sleep 1
done

转载于:https://my.oschina.net/u/3023401/blog/1791876