• Home
  • About Us
  • Contact
  • Privacy Policy

LinuxAdmin.io

  • Home
  • Tutorials
    • System Administration
    • Linux Tutorials
    • Web Hosting
    • WordPress
    • Virtualization
    • Troubleshooting
    • OpenStack

How To Copy MySQL Grants

How to Copy MySQL Grants

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 [email protected];
+----------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------------+
| 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.
Apr 17, 2017LinuxAdmin.io
0 0 vote
Article Rating
Increasing MySQL Max ConnectionsDisable Wordpress Crontab and Run it Manually
You Might Also Like
 
Linux Access Control Lists
 
Configure ProFTPd for SFTP on CentOS
Subscribe
Notify of
guest
guest
0 Comments
Inline Feedbacks
View all comments
6 years ago MySQL 8,469
Recent Posts
  • Laravel Installation Guide For CentOS
  • Openstack Services Explanation And Overview
  • OSSEC Intrusion Detection Installation On Centos 7
  • Configure ProFTPd for SFTP on CentOS
  • How To Check And Repair MyISAM Tables In MySQL
Most Commented
Hot Clone Linux Server with Rsync
Hot Clone A CentOS Server With Rsync
14 Comments
ngx_cache_purge module
Install The ngx_cache_purge Module In Nginx
8 Comments
piwik-nginx
Piwik Analytics on Nginx
8 Comments
Tags
linuxcentosLinux Performance Tuningsysadminkvmnetworkingmemcachedanalyticssystemd
About

We love Linux and are dedicated to creating Linux administration tutorials for System Administrators since 2016.

Most Viewed
Default Gateway
How To Configure A Default Gateway on CentOS
63,002 views
Zend Opcache
Setup and Optimize Zend OpCache
46,714 views
Install ffmpeg
FFMpeg Install On CentOS 7
20,912 views
Archives
Email subscription

Sign up for our newsletter to receive the latest news and event postings.

2018 © LinuxAdmin.io
wpDiscuz