Version <= 5.6
#!/bin/bash
#Function export user privileges
source /etc/profile
pwd=******
expgrants()
{
mysql -B -u'root' -p${pwd} -N -S /var/lib/mysql/mysql.sock $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" |
mysql -u'root' -p${pwd} -S /var/lib/mysql/mysql.sock $@ |
sed 's/(GRANT .*)/1;/;s/^(Grants for .*)/-- 1 /;/--/{x;p;x;}'
}
expgrants > ./grants.sql
Version >= 5.7
#/bin/bash
#Function export user privileges
source /etc/profile
pwd=password
expgrants()
{
mysql -B -u'root' -p${pwd} -N -P3306 $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user where user not in ('mysql.session','mysql.sys')" |
mysql -u'root' -p${pwd} -P3306 -f $@ |
sed 's#$#;#g;s/^(CREATE USER for .*)/-- 1 /;/--/{x;p;x;}'
mysql -B -u'root' -p${pwd} -N -P3306 $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user where user not in ('mysql.session','mysql.sys')" |
mysql -u'root' -p${pwd} -P3306 -f $@ |
sed 's/(GRANT .*)/1;/;s/^(Grants for .*)/-- 1 /;/--/{x;p;x;}'
}
expgrants > ./grants.sql