Export MySQL users and permissions

exportMysqlUsers.php

<?php
/**
 * Export MySQL users and permissions
 *
 * This script exports raw CREATE USER and GRANT queries of a given database 
 * to help migrate MySQL users and permissions to a new server.
 * Users will keep their passwords across the migration.
 *
 * Warning: The import queries expects exactly the same database structure!
 *
 * This was made in 10 min a rainy afternoon.
 * Usage is on your own responsibility!
 * Feel free to improve it.
 *
 * @requires PHP 5.3+
 * @package ExportMySQLUsers
 * @author Janich Rasmussen <janich@gmail.com>
 * @license http://www.dbad-license.org/ DBAD license
 */


// Set up database root credentials
$host = 'localhost';
$user = 'root';
$pass = '--Insert Your Root Password Here And Delete It When You Are Done--';
$port = 3306;


// ---- 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
$link = new mysqli($host, $user, $pass, 'mysql', $port, 'mysql');

// Test connection
if ($link->connect_error)
{
	printf('Connect failed (%s): %s', $link->connect_errno, $link->connect_error);
	die();
}

// Do this right!
$link->query('SET NAMES \'utf8\'');

// Get users from database
$result	= $link->query('SELECT `User`, `Host`, `Password` FROM `user`');
if ($result)
{
	while ($row = $result->fetch_row())
	{
		$user	= $row[0];
		$host	= $row[1];
		$pass	= $row[2];

		$export[] = 'CREATE USER `'. $user .'`@`'. $host .'` IDENTIFIED BY `'. $pass .'`';

		// Fetch any permissions found in database
		$result2 = $link->query('SHOW GRANTS FOR `'. $user .'`@`'. $host .'`');
		if ($result2)
		{
			while ($row2 = $result2->fetch_row())
			{
				$export[] = $row2[0];
			}
		}
	}
}

$link->close();

echo implode(";\n", $export);

Aww man, thanks for sharing this, you just saved me an hour of manual work/coding!

One small bug;

$export[] = 'CREATE USER `'. $user .'`@`'. $host .'` IDENTIFIED BY `'. $pass .'`';

Should be;

$export[] = 'CREATE USER `'. $user .'`@`'. $host .'` IDENTIFIED BY \''. $pass .'\'';

Otherwise you get;

mysql> CREATE USER `TEST`@`%` IDENTIFIED BY `*TEST`;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`*TEST`' at line 1

Thanks man was very useful...drop me a message if you are interested to have a python version of that...I will put it in my github account

Thanks a lot.

To use PDO instead of mysqli:
https://gist.github.com/zaiddabaeen/e88a2d10528e31cd6692

Thanks for this! I added password hash / auth_plugin support for this, as the password field is gone in version 5.6 of MySQL. https://gist.github.com/hackmods/88714fb81e998102b4052e7957e39ef2