Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Can you change from MD5 based passwords, as they aren't really very secure... Read this post by Mr Atwood... codinghorror.com/blog/2007/09/… –  Dave Rix Jul 3 '12 at 8:06
    
Not sure how Wordpress handles passwords though... –  Dave Rix Jul 3 '12 at 8:06

2 Answers 2

Try running this and you might be able to cut/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");
share|improve this answer
    
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

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"

all_dbs="$(mysql -u $user -p$password -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
share|improve this answer
    
Ah ok so I have to have root access to do this? or can my cPanel login details suffice ? –  Billy Jul 2 '12 at 7:02
    
@Billy : I have written a unix shell script if you have mysql access you can execute this script easily. I have never used cPanel so can't say anything. –  Abdul Manaf Jul 2 '12 at 7:07
    
Ah ok understood. I'm a bit of a coding noob and it didn't look like this was PHP so I didn't use it yet. –  Billy Jul 2 '12 at 7:08
    
will it help you? –  Abdul Manaf Jul 2 '12 at 7:09
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.