Not the answer you need?
Register and ask your own question!

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?

Best Answers

Answers

  • DBA100DBA100 Current User Role Patron
    how about mysqlpump ? it seems it is better ?
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html
    By default, mysqlpump does not dump user account definitions, even if you dump the mysql system database that contains the grant tables. 

    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

  • DBA100DBA100 Current User Role Patron
    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 -
  • DBA100DBA100 Current User Role Patron
    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
    MySQLUserGrants.sql, which will create all user with password and respective grants?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
    
    then I just execute 
  • DBA100DBA100 Current User Role Patron
    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} > 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
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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)
  • DBA100DBA100 Current User Role Patron
    "or the easy way (Percona toolkit)"
    ahaha.

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

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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 >mysqlusegrants.sql


  • DBA100DBA100 Current User Role Patron
    "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  ?
  • DBA100DBA100 Current User Role Patron
    tks.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.