How To Restore MySQL Users And Passwords During Migration
In any MySQL replication or migration, generally, we’ll skip the mysql database from the dump file. Since it has the current server’s settings, users, etc. So we should not create a mess on the target server with this source mysql database dump. But one downside of this approach is we’ll not get the users on the target MySQL server. Maybe you tried some approaches that are mentioned here. But whenever I don’t the credentials list during the migration and don’t have time to explore/install any tools likept-show-grants, then I’ll use this trick.
DISCLAIMER You should try this approach when you don’t any user credentials on the source DB. Take a backup of the MySQL database on the target DB side. Do this with your DBA’s help. And it is tested in MySQL 5.6 and 5.7
Step #1: Get the grant: #
-- Get grants
mysql -h SOURCE_DB_IP_ADDRES -u root -p'PASSWORD' --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h IP_ADDRES -u root -p'PASSWORD' --skip-column-names -A | sed 's/$/;/g' > user_grants.sql
-- clean up the password field
-- If the source is RDS
sed -i 's/IDENTIFIED BY PASSWORD <secret>//g' user_grants.sql
-- If the source is VM/CloudSQL/or whatever
sed -i "s/IDENTIFIED[^']*'[^']*//" user_grants.sql
Step #2: Generte create user statement: #
-- MySQL 5.6
mysql -h source_DB -u root -p'password' --skip-column-names -A mysql -e "SELECT Concat('create user \'', user, '\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'', password,'\';') FROM mysql.user where user not in ('mysql.session','mysql.sys','debian-sys-maint','root');" > create_user.sql
-- MySQL 5.7
mysql -h source_DB -u root -p'password' --skip-column-names -A mysql -e "SELECT Concat('create user \'', user, '\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'', authentication_string,'\';') FROM mysql.user where user not in ('mysql.session','mysql.sys','debian-sys-maint','root');" > create_user.sql
Step #3: Restore the users: #
mysql -u target_db_ip -u root -p myqsl < create_user.sql
mysql -u target_db_ip -u root -p myqsl < user_grants.sql
Bouns: #
Credits: Pankaj #
If you want to rename a user like change the HOST part, then you can use the following commands.
mysql -h source_DB -u root -p'password' --skip-column-names -A mysql -e "SELECT Concat('rename user \'',user,'\'@\'',host,'\' to \'',user,'\'@\'','10.20.4.240\'',';') from mysql.user where user not in ('mysql.session','mysql.sys','debian-sys-maint','root','mysql.infoschema');" > rename_user.sql
mysql -u target_db_ip -u root -p myqsl < rename_user.sql
Again a friendly reminder, keep your DBA with you and do take a backup of the MySQL database. If required you can still use pt-show-grants and mysqlpump as well.