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!.
  • Email, SSL
  • 6 Users Found This Useful
Was this answer helpful?

Related Articles

How to change storage engine to InnoDB in MySQL?

You’ve designed a database with MyISAM and suddenly realized that you need ACID (atomicity,...

MySQL Server Won’t Start : PID File Errors

This is kind of a common problem and may have several reasons. Sometimes when we simply want to...

PhpMyAdmin : Cannot start session without errors

Cannot start session without errors, please check errors given in your PHP and/or webserver log...

Resetting MySQL Root Password on CentOS

It is not uncommon for you to need the MySQL root password of a server, especially when...