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 [email protected]:/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.
for this purpose we will be migrating sakila database
then start taking the backup by using the following syntax
mysqldump [options] --databases db1 db2 ..db_name > backup_name.sq
we can mention multiple database but for our purpose we only have one database
mysqldump -uroot -p --databases sakila > backup_sakila.sq
6 transfer the database and restore it
now we will use scp to transfer the database to destination MySQL server
scp backup_sakila.sq [email protected]:/home/appuser/STGmigr/
next we will restore the database using the below command
mysql -uroot -p < backup_sakila.sq |
