How to Export & Import Existing User (with its Privileges!)

Question

I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.

I now need to duplicate one of the databases (into production) and the users associated with it.

Duplicating the database was easy:

Export:

mysqldump --no-data --tables -u root -p secondb >> secondb_schema.sql

Import:

mysql -u root -p -h localhost secondb < secondb_schema.sql

I didn't find, however, a straightforward way to export and import users, from the command line (either inside or outside mysql).

How do I export and import a user, from the command line?

---

Update: So far, I have found manual (and thus error prone) steps for accomplishing this:

-- lists all users
select user,host from mysql.user;

Then find its grants:

-- find privilege granted to a particular user
show grants for 'root'@'localhost'; 

Then manually create user with the grants listed in the result of the 'show grants' command above.

I prefer a safer, more automated way. Is there one?

Answer 1

One of the easiest ways I've found to export users is using Percona's tool pt-show-grants. The Percona tool kit is free, easy to install, and easy to use, with lots of documentation.

# Debian/Ubuntu
sudo apt-get install percona-toolkit

It's an easy way to show all users, or specific users. It lists all of their grants and outputs in SQL format. I'll give an example of how I would show all grants for test_user:

shell> pt-show-grants --only test_user

Example output of that command:

GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*06406C868B12689643D7E55E8EB2FE82B4A6F5F4';
GRANT ALTER, INSERT, LOCK TABLES, SELECT, UPDATE ON `test`.* TO 'test_user'@'%';

I usually rederict the output into a file so I can edit what I need, or load it into mysql.

Alternatively, if you don't want to use the Percona tool and want to do a dump of all users, you could use mysqldump in this fashion:

shell> mysqldump mysql --tables user db > users.sql

Note: --flush-privileges won't work with this, as the entire db isn't being dumped. this means you need to run it manually.

shell> mysql -e "FLUSH PRIVILEGES"

Answer 2

For MariaDB - version's 10.4 and above the mysql.user table has been retired and is now using the mysql.global_priv table. You can export the db and users along with the table creation data by executing the following command:

mysqldump mysql --tables global_priv db > users-db.sql

If you just want the "CREATE USER" commands instead you can execute this command:

mysqldump --system=users > create-users.sql

Answer 3

SELECT CONCAT('\create user ', user,'\'@\'', host, '\' identified by ', "'", authentication_string, "'"'\;') FROM user WHERE user != 'mysql.session' AND user !='mysql.sys'  AND user != 'root' AND user != '';

Answer 4

In mysql 5.7 and later you can use this.

mysqlpump -uroot -p${yourpasswd} --exclude-databases=% --users

This will generate a sql format output that you can redirect to mysql_users.sql.

Note that it is mysqlpump not mysqldump.

Answer 5

Yet another bash one-liner for linux that you can use instead of the Percona tool:

mysql -u<user> -p<password> -h<host> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), coalesce(password, authentication_string) from user where not user like 'mysql.%'" | while read usr pw ; do echo "GRANT USAGE ON *.* TO $usr IDENTIFIED BY PASSWORD '$pw';" ; mysql -u<user> -p<password> -h<host> -N -e "SHOW GRANTS FOR $usr" | grep -v 'GRANT USAGE' | sed 's/\(\S\)$/\1;/' ; done

Answer 6

I had the same problem. The solution is that after the import of the backup you need to do a "flush privileges;". Then the privileges of the users will be active as in the original database.

So execute:

mysql -u root -p -h localhost secondb < secondb_schema.sql

mysql -u root

then in mysql: flush privileges;

Answer 7

    pass=your_password_here; \ 
    MYSQL_PWD=$pass mysql -B -N -uroot -e "SELECT CONCAT('\'', user,'\' ','\'', host,'\' ','\'', authentication_string,'\' ','\'', plugin,'\'') FROM mysql.user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != 'mysql.sys' AND user != 'mysql.session' AND user != ''" > mysql_all_users.txt; \
    while read line; do linearray=(${line}); \
    MYSQL_PWD=$pass mysql -B -N -uroot -e "SELECT CONCAT('CREATE USER \'',${linearray[0]},'\'@\'',${linearray[1]},'\' IDENTIFIED WITH \'',${linearray[3]},'\' AS \'',${linearray[2]},'\'')"; \
        done < mysql_all_users.txt > mysql_all_users_sql.sql; \
    while read line; do linearray=(${line}); \
    MYSQL_PWD=$pass mysql -B -N -uroot -e "SHOW GRANTS FOR ${linearray[0]}@${linearray[1]}"; \
        done < mysql_all_users.txt >> mysql_all_users_sql.sql; \
    sed -e 's/$/;/' -i mysql_all_users_sql.sql; \
    echo 'FLUSH PRIVILEGES;' >> mysql_all_users_sql.sql; \
    unset pass

First mysql command : export all users to file and exclude some.
Second mysql command : loop users from file to write a sql command 'create user' to an exported file (with authentication credentials).
Third mysql command : loop users from file to append their privileges to the exported file.
sed command to append a ";" to end of lines and flush privileges to finish.
To import : MYSQL_PWD=$pass mysql -u root < mysql_all_users_sql.sql

Answer 8

A PHP script to loop over your users to get the grant commands would be as such:

// Set up database root credentials
$host = 'localhost';
$user = 'root';
$pass = 'YOUR PASSWORD';
// ---- Do not edit below this ----
// Misc settings
header('Content-type: text/plain; Charset=UTF-8');
// Final import queries goes here
$export = array();
// Connect to database
try {
    $link = new PDO("mysql:host=$host;dbname=mysql", $user, $pass);
} catch (PDOException $e) {
    printf('Connect failed: %s', $e->getMessage());
    die();
}

// Get users from database
$statement = $link->prepare("select `user`, `host`, `password` FROM `user`");
$statement->execute();
while ($row = $statement->fetch())
{
    $user   = $row[0];
    $host   = $row[1];
    $pass   = $row[2];
    $export[] = "CREATE USER '{$user}'@'{$host}' IDENTIFIED BY '{$pass}'";
    // Fetch any permissions found in database
    $statement2 = $link->prepare("SHOW GRANTS FOR '{$user}'@'{$host}'");
    $statement2->execute();
    while ($row2 = $statement2->fetch())
    {
        $export[] = $row2[0];
    }
}

$link = null;
echo implode(";\n", $export);

Gist: https://gist.github.com/zaiddabaeen/e88a2d10528e31cd6692

Answer 9

Here's what I'm using these days as part of my daily backup scripts (requires root shell and MySQL access, linux shell, and uses the mysql built-in schema:

First, I create a file /var/backup/mysqlroot.cnf containing the root password so I can automate my scripts and not hardcode any passwords in them:

[client]
password=(put your password here)

Then I create an export script which dumps create user commands and grants like this:

touch /var/backup/backup_sql.sh
chmod 700 /var/backup/backup_sql.sh
vi /var/backup/backup_sql.sh

And then write the following contents:

#!/bin/bash

mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe " \
  SELECT \
    CONCAT( 'CREATE USER \'', User, '\'@\'', Host, '\' IDENTIFIED BY \'', authentication_string, '\'\;' ) AS User \
  FROM mysql.user \
  WHERE \
    User NOT LIKE 'mysql.%' AND CONCAT( User, Host ) <> 'rootlocalhost' AND User <> 'debian-sys-maint' \
"

mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe " \
  SELECT \
    CONCAT( '\'', User, '\'@\'', Host, '\'' ) as User FROM mysql.user \
  WHERE \
    User NOT LIKE 'mysql.%' \
    AND CONCAT( User, Host ) <> 'rootlocalhost' \
    AND User <> 'debian-sys-maint' \
" | sort | while read u ; 
 do echo "-- $u"; mysql --defaults-extra-file=/var/backup/mysqlroot.cnf -sNe "show grants for $u" | sed 's/$/;/'
done

Then I just have to run it like this: /var/backup/backup_sql.sh > /tmp/exportusers.sql

Answer 10

mysql -u<user> -p<password> -h<host> -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do  mysql -u<user> -p<password> -h<host> -e"$user" > user_grant.txt; sed '1d' user_grant.txt >> user_privileges.txt; echo "flush privileges" >> user_privileges.txt; done < user.txt
awk '{print $0";"}'  user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

Above script will run in linux environment and output will be user_privileges_final.sql that you can import in new mysql server where you want to copy user privileges.

UPDATE: There was a missing - for the user of the 2nd mysql statement.

Answer 11

In complement of @Sergey-Podushkin 's answer, this shell script code is workin for me:

mysql -u<user> -p<password> -N mysql -e "select concat(\"'\", user, \"'@'\", host, \"'\"), authentication_string from user where not user like 'root'" | while read usr pw ; do mysql -u<user> -p<password> -N -e "SHOW GRANTS FOR $usr" | sed 's/\(\S\)$/\1;/'; done 

Answer 12

PhpMyAdminYou can use phpMyAdmin.

Login and Go to your database or a table where the user has access.

Select privileges

All users with access are there.

Select Export. And a little window with all the GRANTS are there ready to copy and paste.

Answer 13

I tackled this with a small C# program. There is code here to generate a script or apply the grants directly from source to destination. If porting from a Windows -> *nix environment you may have to consider case sensitivity issues.

using System;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.IO;
using System.Collections.Generic;

namespace GenerateUsersScript
{
    class Program
    {
        static void Main(string[] args)
        {
            List<string> grantsQueries = new List<string>();

            // Get A Show Grants query for each user
            using (MySqlConnection sourceConn = OpenConnection("sourceDatabase"))
            {
                using (MySqlDataReader usersReader = GetUsersReader(sourceConn))
                {
                    while (usersReader.Read())
                    {
                        grantsQueries.Add(String.Format("SHOW GRANTS FOR '{0}'@'{1}'", usersReader[0], usersReader[1]));
                    }
                }

                Console.WriteLine("Exporting Grants For {0} Users", grantsQueries.Count);

                using (StreamWriter writer = File.CreateText(@".\UserPermissions.Sql"))
                {
                    // Then Execute each in turn 
                    foreach (string grantsSql in grantsQueries)
                    {
                        WritePermissionsScript(sourceConn, grantsSql, writer);
                    }

                    //using (MySqlConnection destConn = OpenConnection("targetDatabase"))
                    //{
                    //    MySqlCommand command = destConn.CreateCommand();

                    //    foreach (string grantsSql in grantsQueries)
                    //    {
                    //        WritePermissionsDirect(sourceConn, grantsSql, command);
                    //    }
                    //}
                }
            }

            Console.WriteLine("Done - Press A Key to Continue");

            Console.ReadKey();
        }

        private static void WritePermissionsDirect(MySqlConnection sourceConn, string grantsSql, MySqlCommand writeCommand)
        {
            MySqlCommand cmd = new MySqlCommand(grantsSql, sourceConn);

            using (MySqlDataReader grantsReader = cmd.ExecuteReader())
            {
                while (grantsReader.Read())
                {
                    try
                    {
                        writeCommand.CommandText = grantsReader[0].ToString(); 

                        writeCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(grantsReader[0].ToString());

                        Console.WriteLine(ex.Message);
                    }
                }
            } 
        }

        private static void WritePermissionsScript(MySqlConnection conn, string grantsSql, StreamWriter writer)
        {
            MySqlCommand command = new MySqlCommand(grantsSql, conn);

            using (MySqlDataReader grantsReader = command.ExecuteReader())
            {
                while (grantsReader.Read())
                {
                    writer.WriteLine(grantsReader[0] + ";");
                }
            }

            writer.WriteLine();
        }

        private static MySqlDataReader GetUsersReader(MySqlConnection conn)
        {
            string queryString = String.Format("SELECT User, Host FROM USER");
            MySqlCommand command = new MySqlCommand(queryString, conn);
            MySqlDataReader reader = command.ExecuteReader();
            return reader;
        }

        private static MySqlConnection OpenConnection(string connName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connName].ConnectionString;
            MySqlConnection connection = new MySqlConnection(connectionString);
            connection.Open();
            return connection; 

        }
    }
}

with an app.config containing ...

    <connectionStrings>
        <add name="sourceDatabase" connectionString="server=localhost;user id=hugh;password=xxxxxxxx;persistsecurityinfo=True;database=MySql" />
        <add name="targetDatabase" connectionString="server=queeg;user id=hugh;password=xxxxxxxx;persistsecurityinfo=True;database=MySql" />
    </connectionStrings>