Export MySQL users

Ein Script zum Exportieren aller MySQL Benutzer und ihren Berechtigungen.

#!/bin/bash

# for MySql >= 5.6
mysql -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), authentication_string from user where not user like 'mysql.%' and user <> 'root' and user not like 'debian%' order by lower(user)" |

# for MySQL <= 5.5
# mysql -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), password from user where not user like 'mysql.%' and user <> 'root' and user not like 'debian%' order by lower(user)" |

while read user pass ; do
    echo "### $user"
    echo "DROP USER IF EXISTS $user;"
    echo "CREATE USER $user IDENTIFIED BY PASSWORD '$pass';"
    mysql -N -B -e "SHOW GRANTS FOR $user" | sed 's/$/;/'
    echo ""
done