Skip to content

Instantly share code, notes, and snippets.

@abarringer
Created June 8, 2011 20:55
Show Gist options
  • Save abarringer/1015377 to your computer and use it in GitHub Desktop.
Save abarringer/1015377 to your computer and use it in GitHub Desktop.
Move MySQL Users with Permissions to New Server
#!/bin/bash
mygrants()
{
mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user" | \
mysql $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}
mygrants --host=localhost -uroot -p
select
concat('CREATE USER ''', user, '''@''', host, ''' IDENTIFIED BY PASSWORD ''', password,''';')
from mysql.user
where user not in ('root', 'debian-sys-maint', '');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment