mysql 升级脚本_MySQL升级相关小脚本

前言：在某些特殊的情况下，我们希望能拿到全量的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

需要注意：

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