Update MySQL user passwords from H-Sphere DataBase

This article is only applicable to H-Sphere control panel where old mysql passwords are stored as clear text.

With the latest update, php 53 comes with mysqlnd module which doesn’t allow old_passwords to be used.

In /etc/my.cnf comment old_passwords if any,

Backup your MySQL database and restart mysql server and execute the following code:

Create a php file and copy/paste below code:

<?php

ini_set("display_errors",1);

//Postgres login on CP server
$dbuser = "wwwuser";
$dbpass = "dbpass";
$dbhost = "cp_pgsql_ip";
$dbname = "hsphere";

//MySQL login on MySQL server
$myroot = "root";
$mypass = "mysql_root_pass";
$myhost = "mysql_server_ip";
$mylid = 25;

$mycon = mysql_connect($myhost, $myroot, $mypass);
if (!$mycon) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mysql', $mycon);

	$dbconn = pg_connect("host=$dbhost dbname=$dbname user=$dbuser password=$dbpass")
    or die('Could not connect: ' . pg_last_error());

	$users = pg_query("select mu.login,mu.password from mysql_users as mu ,mysqlres as mr where mu.parent_id=mr.id and mr.mysql_host_id=$mylid order by mu.login asc; ;")
	or die('Query failed: ' . pg_last_error());
	while ($line = pg_fetch_array($users, null, PGSQL_ASSOC))
	{
			echo "Setting ".$line["login"].": ";
			$login = $line["login"];
			$pass = $line["password"];

			mysql_query("set password for '".$login."'@'localhost' = password('".$pass."');");
			mysql_query("set password for '".$login."'@'%' = password('".$pass."');");
			echo "<br/>";
	}

?>

Good luck!.