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

Percona Monitoring and Management (PMM) Database Monitoring and Management
, ,
1

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?

2

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
3

how about mysqlpump ? it seems it is better ?

1 Like
4

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
5

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
6

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?
7

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 ```
8

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)

9

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

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

10

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


11

“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  ?

12

> 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

13

tks.