导航

MySQL 之迁移用户及权限

Posted on 2018-10-19 09:45  许爱琪  阅读(2389)  评论(0)  编辑  收藏  举报

参考来源：

https://www.cnblogs.com/huangmr0811/p/5570994.html

https://blog.csdn.net/u011665746/article/details/79067656

 

5.6导出执行脚本：

入参：
export_db_ip=$1         #服务器IP
export_db_port=$2       #MySQL端口
export_user=$3          #导出用户
export_user_passwd=$4   #导出用户密码
 1 ###传入导出服务器及用户信息
 2 export_db_ip=$1
 3 export_db_port=$2
 4 export_user=$3
 5 export_user_passwd=$4
 6 
 7 ###文件保存目录
 8 path=`pwd`
 9 
10 ###导出用户
11 mysql -B -N -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql.session' and user != 'mysql.sys' AND user != 'root' AND user != ''" mysql > $path/mysql_all_users.txt
12 
13 ###导出用户权限
14 while read line; 
15 do 
16     mysql -B -N -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -e "SHOW GRANTS FOR $line"; 
17 done < $path/mysql_all_users.txt > $path/mysql_all_users_sql.sql
18 
19 
20 ###每行行尾增加；
21 sed -i 's/$/;/' $path/mysql_all_users_sql.sql
22 
23 ###立即生效
24 echo "flush privileges;" >> $path/mysql_all_users_sql.sql
View Code

 

 5.7导出执行脚本：

入参：
export_db_ip=$1         #服务器IP
export_db_port=$2       #MySQL端口
export_user=$3          #导出用户
export_user_passwd=$4   #导出用户密码
#!/bin/bash
#Function export user privileges
#5.7存在问题: show grants for 不会给出密码信息，必须用 show create user
# https://dev.mysql.com/doc/refman/5.7/en/show-grants.html  
# show create user 为5.7版本开始存在，5.6执行报错。


###传入导出服务器及用户信息
export_db_ip=$1
export_db_port=$2
export_user=$3
export_user_passwd=$4

###文件保存目录
path=`pwd`

source /etc/profile
 
pwd=password 
expgrants()  
{  
  mysql -B -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -N $@ -e "SELECT CONCAT(  'SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -f  $@ | \
  sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' 
 
  mysql -B -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -h $export_db_ip -P $export_db_port -u${export_user} -p${export_user_passwd} -f  $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'   
}  
expgrants > $path/mysql_all_users_sql_5.7.sql


###立即生效
echo "flush privileges;" >> $path/mysql_all_users_sql_5.7.sql
View Code

 

导出执行语句：

[root@db02 tmp]# mysql -u root -p < mysql_all_users_sql.sql