mysql export users and passwords to another server

mysqldump migrate users to new server


MySQL 5.6 and older

while read line; do mysql -B -N -e "SHOW GRANTS FOR $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_all_users_grants.sql

if MySQL 5.7 and above

while read line; do mysql -B -N -e "SHOW CREATE USER $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_create_users.sql

while read line; do mysql -B -N -e "SHOW GRANTS FOR $line"; done < <(mysql -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql' AND user != 'root' AND user != ''" mysql) | sed 's/$/;/' > mysql_all_users_grants.sql

Excludes some users like root

Leave a Reply

Your email address will not be published. Required fields are marked *