16

I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is there a way to achieve this or do I have to set this newly up on the new server?

  • Are you moving the data to another server running same version of MySQL ? – RolandoMySQLDBA May 4 '15 at 16:04
16

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases

mysqldump [options] --all-databases > all_databases_dump.sql

mysqldump -u root -p mysql user > user_table_dump.sql

These mysql database tables contain grant information

user: User accounts, global privileges, and other non-privilege columns.

db: Database-level privileges.

tables_priv: Table-level privileges.

columns_priv: Column-level privileges.

procs_priv: Stored procedure and function privileges.

After restoring cross check with

select Host, user, password from user ;

SHOW GRANTS FOR 'user'@'localhost';
| improve this answer | |
  • 8
    Caution. If you will be loading this into a newer version of MySQL, the dump of mysql.user may fail due to schema changes. – Rick James May 4 '15 at 17:31
  • 1
    @RickJames: what should we do if we want to migrate to a newer version and restore the users? – brunoqc Feb 28 '17 at 17:42
  • 1
    mysql_upgrade is a script to take care of schema changes. But it expects you to be making only one major change at a time, and in-place, not reloading. Research it. (Sorry, I don't have experience in the area of upgrades.) – Rick James Feb 28 '17 at 19:02
  • 1
    After restore you may/will also need flush privileges; on the new mysql. Like mysql -u root -p -e'flush privileges;' This may/will also set your root mysql password on your new server to be the root password from your old server so be sure you know what that is. – meesern May 31 '19 at 10:42
0

This PHP script was inspired by the need to do the same thing as the original question where the servers in question were running different version of MariaDB. Since it's PHP it should work on any platform that supports PHP (version 7.3 or higher).

<?php
ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting(E_ALL);

//
// You will want to modify the 4 variables below for your environment
//

$dbuser       = 'root';                   // DB user with authority to SHOW GRANTS from mysql.user
$dbpassword   = 'blahblah';               // password for the DB user
$useroutfile  = '/temp/Users.sql';        // where to write the user file that may be imported on new server
$grantoutfile = '/temp/Grants.sql';       // where to write the grant file that may be imported on new server
$ignore_users = ['root','replication_user'];  // array of users that should NOT be exported

//
// There really should not be any reason to modify anything below this comment 
// but please do browse through it and understand what is being done
//

$dsn = 'mysql:host=localhost;charset=utf8mb4';
$opt = [PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION ,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC       ,
        PDO::ATTR_EMULATE_PREPARES   => true                   ,
       ];
try {

    $ourdb = new PDO ($dsn,$dbuser,$dbpassword,$opt);

} catch (PDOException $e) {

    error_log($e);  // log the error so it may be looked at later if necessary
    echo 'Could not connect to the SQL server';
    exit;
}  // end of the try/catch block

$notuser = implode(',',array_map('add_quotes',$ignore_users));

//
// We got connected to the database so now let's make sure we can open the
// output files for writing - note that using mode w will overwrite any
// existing files so we'll always start off cleanly
//

$userout = fopen($useroutfile,'w');

if ($userout === false) {  // could not open the output file for writing for some reason

    error_log('Could not open the output file for writing (' . $useroutfile . ')');
    exit;

}  // end of if we could not open the output file for writing

$grantout = fopen($grantoutfile,'w');

if ($grantout === false) {  // could not open the output file for writing for some reason

    error_log('Could not open the output file for writing (' . $grantout . ')');
    exit;

}  // end of if we could not open the output file for writing

$Query = $ourdb->query("
    SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query 
           FROM mysql.user 
           WHERE user NOT IN(" . implode(',',array_map('add_quotes',$ignore_users)) . ")
");
$users = $Query->fetchAll(PDO::FETCH_COLUMN);

foreach ($users as $GrantQ) {  // go through each of the users found

    $UserQ  = $ourdb->query("$GrantQ");  // retrieve the grants for a user
    $grants = $UserQ->fetchAll(PDO::FETCH_COLUMN);

    foreach ($grants as $grant) {  // go through each of the grants found for this user

        if (stripos($grant,'IDENTIFIED BY PASSWORD') === false) {

            fwrite($grantout,$grant . ';' . PHP_EOL);  // write the command to actually do the grant

        } else {

            fwrite($userout,$grant . ';' . PHP_EOL);  // write the command to actually do the grant
}
        }  // end of foreach through the grants found

}  // end of foreach through the queries to show the grants for each user

fwrite($userout ,'FLUSH PRIVILEGES;' . PHP_EOL);  // make sure SQL knows about the new users and privileges
fwrite($grantout,'FLUSH PRIVILEGES;' . PHP_EOL);  // make sure SQL knows about the new users and privileges
fclose($userout);   // close our output file
fclose($grantout);  // close our output file
echo 'The grants for ' . count($users) . ' users were written to ' . $useroutfile . PHP_EOL;

function add_quotes($str) {return sprintf("'%s'", $str);}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.