11

I have one hosting account using cPanel and phpmyadmin.

I have 50 databases under this account, all WordPress.

I need this query modified so that it runs through all databases to update the password.

UPDATE 'wp_users' SET 'user_pass' = MD5('somepassword') WHERE 'user_login' ='admin' LIMIT 1;

Hoping for a solution that can target all databases instead of having to go through them one by one.

Thank you

11

I can't say anything about cPanel and phpmyadmin but In general i can do it by writing a simple script.

I have written a shell script for you

#!/bin/bash

# mysql credential 
user="root"
pass="root"

# list of all databases
all_dbs="$(mysql -u $user -p$pass -Bse 'show databases')"        

for db in $all_dbs
     do
        if test $db != "information_schema" 
            then if test $db != "mysql" 
            then mysql -u$user -p$pass $db -sN -e "UPDATE wp_users SET user_pass = MD5('somepassword') WHERE user_login ='admin' LIMIT 1;"
        fi
    fi  
     done
| improve this answer | |
  • My host is weird about giving shell access and they charge for doing custom commands like this. I've already begin going through each of the databases and updating them manually. Thank you for helping me :) – Billy Jul 2 '12 at 7:21
8

Try running this and then cut n paste the output back into phpMyAdmin

SELECT CONCAT('UPDATE `',     
    schema_name, '`.\'wp_users\' SET \'user_pass\' = MD5(\'somepassword\') WHERE \'user_login\' =\'admin\' LIMIT 1;')
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','test');

or, if you are able to install code on this server, install Common Schema by Shlomi Noach and execute the following QueryScript

call common_schema.foreach( 'schema', "UPDATE `${schema}`.`wp_users` SET 'user_pass' = MD5('somepassword') WHERE 'user_login' ='admin' LIMIT 1");
| improve this answer | |
  • 2
    I agree with the first part of this, build an SQL query containing the schema_name, and just run that. You could even build it in Excel if you needed to - presuming you have all 50 db names that you could put into a column... – Dave Rix Jul 3 '12 at 8:03

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.