The Data Guy

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.

· mysql, security

Advertisement


Loading Comments