How to expert MySQL user account, with password and right permission to another MySQL server

Hi,
As we are going to migrate from percona xtradB cluster 5.7x to 8.0.19, may I know how toexpert MySQL user account, with password and right permission to another MySQL server?
I planned to use;
mysqldump
–host=<DB host we want to back from > --all-databases --events
–routines --triggers --replace --master-data=2 >
<dump_file.sql>
to backup everything from existing mysql to the new one, but what I know is this command do not have back username and password as well as permission to the new percona xtraDB cluster 8.0.19 ?

by restore I use this one:
mysql --host=<DB host we want to
restore to > -u root -p < <dump_file.sql>

any idea on how to copy username and password to another MySQL server?

The best tool for this comes from the Percona Toolkit, pt-show-grants
Install the toolkit and use this tool to securely copy users and their hashed passwords to another mysql server.

1 Like

how about mysqlpump ? it seems it is better ?

1 Like

Not for user accounts without modification. pt-show-grants would be easier to get a copy of all the user accounts and simply copy/paste from A->B

1 Like

I said that as some of  your member said mysqlpump is the best and my test is ok with mysqlpump too !
“pt-show-grants would be easier to get a copy of all the user accounts and simply copy/paste from A->B”
e.g. this  ?

pt-show-grants --separate --revoke | diff othergrants.sql -
1 Like

or as from this link https://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server/399875#399875

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user&lt;&gt;''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' &gt; MySQLUserGrants.sql

then I just execute MySQLUserGrants.sql, which will create all user with password and respective grants?

I also see from the same link, both of them works perfectly?

METHOD #1

You can use pt-show-grants from Percona Toolkit

MYSQL_CONN="-uroot -ppassword"
pt-show-grants ${MYSQL_CONN} &gt; MySQLUserGrants.sql

METHOD #2

You can emulate pt-show-grants with the following

``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql ```

That’s essentially what pt-show-grants does. Our Percona Toolkit tools are simple wrappers around complex SQL to make things easier. You can do it the complicated way, or the easy way (Percona toolkit)

“or the easy way (Percona toolkit)”
ahaha.

so my command above is correct? 
so this one: “MYSQL_CONN=”-uroot -ppassword"" , is set in linux ? 

You don’t have to do that MYSQL_CONN variable. Just put the parameters directly within your call to pt-show-grants

pt-show-grants -uroot -pmypassword &gt;mysqlusegrants.sql


“pt-show-grants -uroot -pmypassword >mysqlusegrants.sql”
ok, just one command in linux ? so this means pt-show-grants can only backup username and password and nothing else.

so when restore I have to do :
mysql -u root -p < mysqlusegrants.sql  ?

> pt-show-grants can only backup username and password and nothing else
No. It backs up usernames, passwords, and all GRANTs associated with each user account.

>so when restore I have to do :
> mysql -u root -p < mysqlusegrants.sql
Correct

tks.