dumping MySQL usernames

mysql --host=192.168.8.6 --port=3307 -B -N -udev -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'')
FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > mysql_all_users.txt

We can remove the --host=192.168.8.6 --port=3307 if not required and also change the username/password as required.

We can then do

while read line; do mysql --host=192.168.8.6 --port=3307 -B -N -udev -pdev -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

this will modify the first text file to contain a selection similar to the below;

GRANT ALL PRIVILEGES ON *.* TO 'dev'@'%' IDENTIFIED BY PASSWORD '*27AEDA0D3A56422C3F1D20DAFF0C8109058134F3'

A somewhat simplified and preferred version, including the ; at the end of each statement;

mysql -u root  --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host, ''';') FROM mysql.user WHERE user<>

''" | mysql -u root --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Restoration is as simple as;

mysql -uroot -p -A < MySQLUserGrants.sql