Manually reset WordPress master password

It may have happened to many of us who maintain multiple wordpress installations with very secure passwords. One day, we just forget our passwords and then realize that the email setup for the user is invalid, and we cant reset the password through email.

If it happens, there is a technique to reset the password through mysql.

Login to the Phpmyadmin control panel.

Identify the username of the account we’re about to reset from the wp_users table.

Then execute the following sql code in Phpmyadmin:

UPDATE `wp_users`
SET `user_pass` = MD5('newpassword')
WHERE `user_login` ='username_to_reset_for'
LIMIT 1;

You may need to modify the quoted strings for your database.

Voila, now you can login with the new password.

Another GUI based method for PhpMyadmin and other ways to reset password are described in the WordPress Codex.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Assign posts to another author in WordPress

First find the USER ID from wp_users table.

Execute the following SQL command from the Database:

UPDATE wp_posts SET post_author = '1' WHERE post_author = '10';[/code] 

switches the posts authored by user No 10, to ownership by user No 1


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Installing Linux Apache, Mysql, PHP on Ubuntu

First off, installing Apache:

[[email protected]]$ sudo apt-get install apache2

Setting up apache2-mpm-worker (2.2.22-1ubuntu1) ...
 * Starting web server apache2                                                               apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1 for ServerName
                                                                                      [ OK ]
Setting up apache2 (2.2.22-1ubuntu1) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place

Note the seemingly error line: apache2: Could not reliably determine the server’s fully qualified domain name, using 127.0.0.1 for ServerName

Though it’s not an error, it recurs every time Apache is restarted. It can be fixed by editing httpd.conf

kdesudo kate /etc/apache2/httpd.conf

This file is usually empty. The error message can be fixed by adding the following to that file:

ServerName localhost

Visit http://localhost/ in any browser and you can test Apache. If installed properly, you get a default page:

It works!
This is the default web page for this server.
The web server software is running but no content has been added, yet.

Installing PHP:

sudo apt-get install php5 libapache2-mod-php5

After installing php, Apache may need to be restarted to use the new config.

sudo /etc/init.d/apache2 restart

To test PHP, create a test page:

kdesudo kate /var/www/testphp.php

OR

sudo emacs /var/www/testphp.php

Add the following contents:

<?php phpinfo(); ?>

Now, open this page: http://localhost/testphp.php

The last step is to install MySql

sudo apt-get install mysql-server

You will be asked to set up a root password. I left it blank. I was asked for the mysql root password no fewer than three times!

Next, install Phpmyadmin:

sudo apt-get install libapache2-mod-auth-mysql php5-mysql phpmyadmin

Make sure that you choose to reconfigure Apache2 during MySql installation:

      Configuring phpmyadmin ├───────────────────────────┐      
      │ Please choose the web server that should be automatically configured to run   │      
      │ phpMyAdmin.                                                                   │      
      │                                                                               │      
      │ Web server to reconfigure automatically:                                      │      
      │                                                                               │      
      │    [x] apache2                                                                │      
      │    [ ] lighttpd                        

You get another prompt asking for a database to be configured:

 ┌───────────────────────────────┤ Configuring phpmyadmin ├───────────────────────────────┐  
 │                                                                                        │  
 │ The phpmyadmin package must have a database installed and configured before it can be  │  
 │ used.  This can be optionally handled with dbconfig-common.                            │  
 │                                                                                        │  
 │ If you are an advanced database administrator and know that you want to perform this   │  
 │ configuration manually, or if your database has already been installed and             │  
 │ configured, you should refuse this option.  Details on what needs to be done should    │  
 │ most likely be provided in /usr/share/doc/phpmyadmin.                                  │  
 │                                                                                        │  
 │ Otherwise, you should probably choose this option.                                     │  
 │                                                                                        │  
 │ Configure database for phpmyadmin with dbconfig-common?                                │  
 │                                                                                        │  
 │                         
<Yes>                            <No>          

Choose Yes.
Hit Enter twice when asked for the user and MySql app password.

Now, restart Apache:

sudo /etc/init.d/apache2 restart

You can login to Phpmyadmin by visiting http://localhost/phpmyadmin

When trying to login as root and without a password, you will be greeted by a message: “Login without a password is forbidden by configuration (see AllowNoPassword)”

To fix this,:

kdesudo kate /etc/phpmyadmin/config.inc.php

And edit the corresponding line in the file so that it reads like this:

$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

by uncommenting the line from the following block:

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

Now you will be allowed to login as root without a password.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Search and replace MySQL databases (Search within a field and replace a part of the string)

This is Part 2 of the article on MySQL string search and replace. This deals with searching for a sub string within a larger string in a field, update that string, and then put the large string back in the field. This is illustrated by a typical problem-How to replace HTML code tags by other tags within multiple posts in a WordPress install.

Changing HTML code tags throughout all posts in a WordPress installation.

I wanted to change the tag “code” to that of a syntax highlighter plugin I recently installed, to make my blog look pretty

First, I had to find out which table prefix was used by my site among the various multisite installs that I had.

So, go to the Database in Phpmyadmin, search for “blog.droidzone.in” by logging into phpmyadmin, then clicking on the Database, and then Search:

Phpmyadmin>MyDatabase>Search

Enter the string,
And search for “All words”

I found 7 matches inside table wp_5_options

Entering that Table, I found that the field siteurl, in table wp_5_options contained this URL. So it was evident that the tables with prefix “wp_5” contained the posts that I needed to modify.

I could then do a search within the whole database for the code tag, and then narrow down the results by those tables which had prefix wp_5.

Eureka! I found 107 matches inside table wp_5_posts for the tag.

Next I browse the results to see the field that contains this result.

I found that post_content is the field which contains posts.

So, my strategy is clear. I simply need to replace all strings which contain the code tag with the new tag.

I cant do a Mysql query on this, because I dont know how 😉

So, I export the rows containing these strings to an sql file, search and replace the tag in a text editor, and import the new sql file! 🙂

Caution: Dont choose the “Check all” followed by export! Only the first page of results will be exported in that case! You need to export everything, right? 🙂

So, choose the Export button under Query results operations, and export to sql file, then search and replace all code tags, by the target code tag.

The important thing to do before importing the results back is that the records already exist in the table so import will fail if simply imported. Instead, we need to replace the SQL statement “INSERT INTO” with the statement “REPLACE INTO ” (There is an alternate easier method-See below).

So, the following line:

INSERT INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

becomes:

REPLACE INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

Alternately, we can choose to use the REPLACE INTO statement instead of the INSERT INTO statement into the exported sql file directly from Phpmyadmin.

Summary,

  1. Phpmyadmin>Search>Word(s) or value(s) to search for (wildcard: “%”): code
  2. Inside table(s): Select All: Go
  3. Browse (result)
  4. Scroll down, Query results operations > Export
  5. Exporting rows from “wp_5_posts ” table
  6. Custom – display all possible options
  7. Dump all rows
  8. Data dump options
  9. Function to use when dumping data: REPLACE
  10. Open the exported sql file in Notepad
  11. Search and Replace All:
  12. Search: searchstring
  13. Replace:  targetstring
  14. Import sql file back

Voila, it’s done.

Of course SQL gurus may have easier methods to do the same. However this works for me fine. 😀

When everything is done, we simply import the file into the Table.

Update: Alternate method using SQL:

Select the database in phpmyadmin.
Execute the following query:

UPDATE wp_5_posts
SET `post_content` = REPLACE(
    `post_content`, '<pre class="lang:default decode:true ">',
       '<code>')

To see the search text, before a replacement, do:

SELECT * FROM `wp_5_posts` WHERE `post_content` LIKE '%<pre class="lang:default decode:true ">%'

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Search and replace MySQL databases (Search within a field and replace a part of the string)

This is Part 2 of the article on MySQL string search and replace. This deals with searching for a sub string within a larger string in a field, update that string, and then put the large string back in the field. This is illustrated by a typical problem-How to replace HTML code tags by other tags within multiple posts in a WordPress install.

Changing HTML code tags throughout all posts in a WordPress installation.

I wanted to change the tag “code” to that of a syntax highlighter plugin I recently installed, to make my blog look pretty

First, I had to find out which table prefix was used by my site among the various multisite installs that I had.

So, go to the Database in Phpmyadmin, search for “blog.droidzone.in”

I found 7 matches inside table wp_5_options

Entering that Table, I found that the field siteurl, in table wp_5_options contained this URL. So it was evident that the tables with prefix “wp_5” contained the posts that I needed to modify.

I could then do a search within the whole database for the code tag, and then narrow down the results by those tables which had prefix wp_5.

Eureka! I found 107 matches inside table wp_5_posts for the tag.

Next I browse the results to see the field that contains this result.

I found that post_content is the field which contains posts.

So, my strategy is clear. I simply need to replace all strings which contain the code tag with the new tag.

I cant do a Mysql query on this, because I dont know how 😉

So, I export the rows containing these strings to an sql file, search and replace the tag in a text editor, and import the new sql file! 🙂

Caution: Dont choose the “Check all” followed by export! Only the first page of results will be exported in that case! You need to export everything, right? 🙂

So, choose the Export button under Query results operations, and export to sql file, then search and replace all code tags, by the target code tag.

The important thing to do before importing the results back is that the records already exist in the table so import will fail if simply imported. Instead, we need to replace the SQL statement “INSERT INTO” with the statement “REPLACE INTO ” (There is an alternate easier method-See below).

So, the following line:

INSERT INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

becomes:

REPLACE INTO `wp_5_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES

 

Alternately, we can choose to use the REPLACE INTO statement instead of the INSERT INTO statement into the exported sql file directly from Phpmyadmin.

Summary,

  1. Phpmyadmin>Search>Word(s) or value(s) to search for (wildcard: “%”): code
  2. Inside table(s): Select All: Go
  3. Browse (result)
  4. Scroll down, Query results operations > Export
  5. Exporting rows from “wp_5_posts ” table
  6. Custom – display all possible options
  7. Dump all rows
  8. Data dump options
  9. Function to use when dumping data: REPLACE
  10. Open the exported sql file in Notepad
  11. Search and Replace All:
  12. Search: searchstring
  13. Replace:  targetstring
  14. Import sql file back

 

Voila, it’s done.

Of course SQL gurus may have easier methods to do the same. However this works for me fine. 😀

 

When everything is done, we simply import the file into the Table.

 

 

 


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Bypassing the File upload limit on PhpMyadmin

First create a directory:

mkdir /usr/share/phpmyadmin/upload[/code]

Next, modify the rules to config this as the upload dir:

kdesudo kate /etc/phpmyadmin/config.inc.php[/code]

$cfg['UploadDir'] = './upload';[/code]


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Switching UIDs for a user

As zitronenroellchen told us, there is a much simpler way to do this.
First identify the UID of the user you want to change. Then get the target UID. For me, I wanted to replace 155 by 1, the default value for Superuser.

Search the database and identify the tables where UID field is present.

For any table in the list
Change over the UID of the destination if it exists.

UPDATE `mybb_threads` SET uid=999 WHERE uid = 1;
UPDATE `mybb_threads` SET uid=1 WHERE uid = 155;
UPDATE `mybb_posts` SET uid=999 WHERE uid = 1;
UPDATE `mybb_posts` SET uid=1 WHERE uid = 155;[/code]

And finally, replace the user in mybb_users:

UPDATE `mybb_users` SET uid=1 WHERE uid = 155;[/code]

Of course, an easier way is to add the user to the list of SuperAdmins

Just discovered that, to retake SuperAdmin permissions for a user, there is a much easier solution, to edit config.php

$config['super_admins'] = 'UID1,UID2';

Plugin uploader issues once again!

DROP TABLE `mybb_pluginuploader`;
alter table `mybb_users` drop column `pluginuploader_key`;[/code]


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

Search and replace MySQL databases (Replace field)

This article is Part 1 of MySQL Search and Replace. Part 2 deals with replacing a string within a bigger string which is the value of the field, and replace it with just the smaller string replaced in the bigger string.

How to search and replace the value of a single field within a table in MySQL database:

 

Syntax:

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");
 To change a field named uid in Table tablename, do this:
UPDATE `tablename` SET `uid`= replace(`uid`,"155","1");
In MyBB, these are the tables which need to be searched and replaced to fix a wrong uid issue:
posts
threads
I needed to do this because because of merging my admin account with another account, I lost Super admin permissions in MyBB. This was the fix, changing the newuid to uid 1 of Superadmin
To reset the MyBB Plugin uploader password:
UPDATE `mybb_pluginuploader` SET `files` = '0e2eced896c9c8ba9bb0bdc678f19c53', `version` = '6iEMPlPpFv' WHERE `name` = '_password';

Then the password will be “test” and you can change it with the ‘Change password’ link in the Plugin Uploader.

Adding default icons to post without set icons:

UPDATE `mybb_posts` SET `icon`= replace(`icon`,"0","6");

Changing the default post edit reason:

UPDATE `mybb_posts` SET `markedit_reason`= replace(`markedit_reason`,"Improve grammar","Added more information");

Switching a comment author:
The table wp_4_comments has a field (row) called comment_author, which had entries ‘droidzone’ which needed to be replaced by ‘joelgm’

UPDATE `wp_4_comments` SET `comment_author`= replace(`comment_author`,"droidzone","joelgm");

Another case where Google blacklisted my comment URL (which made no sense), as part of complete blacklist of my domain. It causes a loop in which the domain continued to be blacklisted because my author url was part of the blacklist. I had no way but to globally search and replace the author url and the author id. 😉

UPDATE `wp_4_comments` SET `comment_author_url`= replace(`comment_author_url`,"http://www.droidzone.in/blog/author/droidzone/","http://www.droidzone.in/blog/author/droidzone/joelgm");

Globally change the “Member only” to “Anyone can download”, in WordPress Download Monitor plugin. I tried finding out what files are Member only. The plugin has a view which can display that. Next, I searched for the filename in the database and found that these resided in a table ‘wp_4_download_monitor_files’, in a field ‘members’. If the value is 1, it is member only. If 0, then anyone can download.

UPDATE `wp_4_download_monitor_files` SET `members`= replace(`members`,"1","0");

 


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

PhpMyadmin MySQL Search and replace

For some weird reason, MyBB’s default edit reason for posts is “Improve grammar”, which makes no sense for an Android forum! So I had to change the string for a lot of users. Manually editing the database is unnecessary!

 

You can exec the following operation in the SQL tab of MySQL:

 

UPDATE `mybb_posts` SET markedit_reason = replace(markedit_reason, “Improve grammar”, “Unspecified reason”);

 

So, format is:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");[/code]

[/code]

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.