MySQL to MySQL Migration Guide

The steps involve installing Percona Toolkit to be able to use the Percona tool pt-show-grant. The tool will print out the users with their privileges and encrypted passwords. The following are the steps needed to successfully migrate a MySQL database and users to another MySQL server:

1. installing perocna toolkit

to install persona tool kit download rpm package
we can use wget to download
https://www.percona.com/software/database-tools/percona-toolkit

select the version and the correct OS version , then copy link from download button , we will use it along side wget
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.7/binary/redhat/8/x86_64/percona-toolkit-3.5.7-1.el8.x86_64.rpm?_gl=1*2fna65*_gcl_au*MTA0Mzk1MDYyOS4xNzE1NjkwMzk5

use yum localinstall to install percona toolkit

sudo yum localinstall 'percona-toolkit-3.5.7-1.el8.x86_64.rpm?_gl=1*2fna65*_gcl_au*MTA0Mzk1MDYyOS4xNzE1NjkwMzk5'

2. change print of hached password to hexadecimal

When using pt-show-grant, MySQL will print the password in a format that cannot be executed on the other side. To resolve this, you need to set the global variable PERSIST.print_identified_with_as_hex to 1. First, log in to MySQL and then execute the following command:

SET @@PERSIST.print_identified_with_as_hex = 1;

3.print login and move them to another server

now we can start print out the login , for this steps we will print the login to file and then use scp to move the login to other side server

now we will print out the user to file called user.sql

pt-show-grants -uroot -p[password] > user.sql

now we will move the file to other side server

scp user.sql root@10.217.10.20:/home/appuser/STGmigr/

4.apply the logins to the destination MySQL server

note : make sure to apply the user logins after migrating the databases, since some logins may have permissions on specific databases or tables.

Since we have moved the login file to the destination MySQL server, we will start applying the logins. It is best practice to apply the logins manually to avoid overriding system users and the root user on the destination MySQL server.

first use cat to read content of the file
cat user.sql

the file separate each user with the sentence -- Grants for
we should avoid system user and root user
we will start with the below normal user and apply them

The syntax for users will contain CREATE USER, followed by ALTER USER on the next line to define the password. We should reconstruct the query to include CREATE USER along with the password definition.

below example

orginial query

CREATE USER IF NOT EXISTS `user_3a4b5c6d`@`localhost`;
ALTER USER `user_3a4b5c6d`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035247F65742B482D117D3E4E25343D3C503B607A063D5877615662393562544355376D65426B7A6A624D7753657867544C334A3161366170432E7479567A715838 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

query after

CREATE USER IF NOT EXISTS `user_3a4b5c6d`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035247F65742B482D117D3E4E25343D3C503B607A063D5877615662393562544355376D65426B7A6A624D7753657867544C334A3161366170432E7479567A715838 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

we simple taking merged the create user and alter user to gather to constructed single query

now we will execute the query MySQL

now we will execute the user grants

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `user_3a4b5c6d`@`localhost`;

now you can continue doing the same to other account

5. taking database dump backup

For this step, we will take a database dump using the mysqldump tool. While it is possible to take a dump of the entire MySQL server, restoring it on the destination server will override users and other system databases. The best practice is to back up only the user database.

First, list the databases and determine the databases we would like to migrate.