What Are MySQL Grants?
MySQL grants are privileges issued to users in MySQL. They allow users different permissions to different databases, specific tables or even fields. In certain situations, such as migrating to a new server, you will need to duplicate those grants from one user to another or copy all of the grants to another server or running instance. You can read more about grants on the MySQL project site.
Copy All MySQL Grants And Users
If you are migrating the permissions from one server to another, you need to extract all of the grants from the source server. To do this run the following query:
mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done|grep -v ^-->> mysql-grants.sql
Running the above command will create a text file of all of the grants in SQL format, which can then be imported somewhere else. Once that has completed copy it to the new server and import it with the following command:
mysql < mysql-grants.sql
The new server will now have the same grants as the source server. Then you can easily perform import the same databases and everything should function as it did on the original server.
Copy A Single MySQL User And Grant
If you unsure of what the user and host need to be copied, you can view all of the users to determine what needs to be copied. This can be performed by doing querying the ‘mysql.user’ table with the following command:
select Host,User from mysql.user\G;
This will return something similar in output:
MariaDB [(none)]> select Host,User from mysql.user\G; *************************** 1. row *************************** Host: 127.0.0.1 User: root *************************** 2. row *************************** Host: ::1 User: root *************************** 3. row *************************** Host: localhost User: root *************************** 4. row *************************** Host: localhost User: test 4 rows in set (0.00 sec)
To obtain a single user’s grants and password hash you would do the following:
MariaDB [(none)]> show grants for test@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*BF26929E9DA9100BA9800813F631D5BC4B01AC1E' | +----------------------------------------------------------------------------------------------------------------------+ You can now that that grant information on another MySQL host or apply it to another user.
