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+ / MySQL 5.6
 * @package ExportMySQLUsers
 * @author Janich Rasmussen <[email protected]>
 * @license http://www.dbad-license.org/ DBAD license

 * Script updated in 2016 to support PHP7 with MySQL v5.6 with authentication hash strings.
*  @Updated author Ryan Morris <Hackmods>
 */
 
// Set up database root credentials
$host = 'localhost';
$user = 'root';
$pass = '-------';
$port = 3306;

// ---- Do not edit below this ----
$start = microtime(true);
// 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`, `authentication_string` 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  PASSWORD \''. $pass .'`' . ' IDENTIFIED WITH auth_plugin AS "mysql_native_password"';
		// Fetch any permissions found in database
		$result2 = $link->query('SHOW GRANTS FOR `'. $user .'`@`'. $host .'`');
		if ($result2)
		{
			while ($row2 = $result2->fetch_row())
			{
				$export[] = $row2[0];
			}
		}
	}
}
else
{
	echo 'No results found';
}
$link->close();
echo implode(";\n", $export);
echo nl2br("\n");
echo 'Script executed in ' . (microtime(true) - $start) . 'seconds';
?>