Dev Notes

Software Development Resources by David Egan.

Export MySQL Users


MariaDB, MySQL
David Egan

Build a SQL command that can be used to reconstruct MySQL users - useful for migrating servers, or building a verification check server for backups.

Run this command on the source server:

# Replace PASS with your root password for MySQL
MYSQL_CONN="-uroot -pPASS"

mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user WHERE user NOT IN ('root','pma','phpmyadmin','debian-sys-maint')" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > ~/MySQLUserGrants.sql

See: http://serverfault.com/a/399875

Output:

GRANT USAGE ON *.* TO 'xyz_user'@'localhost' IDENTIFIED BY PASSWORD '*rehoorufiuhrfourur8';
GRANT ALL PRIVILEGES ON `xyz`.* TO 'xyz_user'@'localhost';
GRANT SELECT, RELOAD, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY PASSWORD 'yguirfuiyrfurfygufr';
GRANT USAGE ON *.* TO 'abc_user'@'localhost' IDENTIFIED BY PASSWORD '*yguguyrfeguirefgiuryfegu';
GRANT ALL PRIVILEGES ON `abc`.* TO 'abc_user'@'localhost';
...

This is a set of valid SQL commands that can be run on the destination server to create the necessary users. You can then import files, import databases, adjust /etc/hosts if necessary and the migrated sites should work.

Find All Users for a Database

# Log in as mysql root user
mysql -u root -p
SELECT USER FROM mysql.db WHERE db='DB_NAME';

Resources

comments powered by Disqus