Let’s say your day is going pretty good. You are sitting down to write a blog post and have a great idea too. As you bring up your blog to check on an older post you see something strange. Nothing. As in no posts on your site. Fighting back panic, you try to log into your admin panel. Maybe you are successful and maybe you aren’t. In either case, you find that you can’t bring these posts back.
Just this situation happened to SelfishMom last week. All of her posts were gone and, while she could log in to her administrator account and see the posts there, nothing she could do could bring them back. She feared that a hacker had gained control of her site. I’m going to show just how I helped her bring her posts back – as well as what I could have done had things gone differently.
Just a warning: This is going to involve some intense mySQL queries. They will be very powerful, but can also be very confusing. If you find yourself in this situation and don’t want to wrangle with mySQL, I can always help. That help might come with an hourly rate, however. You can contact me using my contact form on this site or message me on Twitter.
First, let’s launch phpMyAdmin. Different web hosts have it set up different, so you might need to check with your host to see how to launch this. Most web hosts let you launch cPanel by going to yoursite.com/cpanel. You log in (with credentials given to you by your host), find an entry for phpMyAdmin and launch this.
Once you are in phpMyAdmin, you can access your database directly. Your database should be on the left hand side. Click on it. (You might need to click on a + sign first to show the database.) A series of mySQL tables will be shown. Within here is all of your WordPress data.
Find one of your WordPress tables. It should be named something like "wp_users" or "wp_posts". The "wp_" prefix might be different depending on your setup. For the purposes of this post, I’m going to list all of the tables using a wp_ prefix. If your tables used a different prefix, just replace yours for wp_ in the following queries.
Let’s deviate from SelfishMom’s situation for a moment and suppose that she wasn’t able to log in at all. How could she have reset her administrative password without having access to the administrative panel? This is actually pretty easy via phpAdmin. For brevity’s sake, and since they did such a good job on it, here’s WPExplorer’s tutorial on it.
Ok, now that we have a login, let’s address another concern: Hackers. Did a hacker somehow gain control of SelfishMom’s site and make himself the administrator? Click on the SQL tab in phpMyAdmin. A blank box will appear. In here, type:
SELECT u.*, m . *
FROM wp_usermeta m, wp_users u
WHERE u.id = m.user_id
AND m.meta_key like ‘%user_level%’ and m.meta_value = 10
After you click Go, this will show you a listing of users who are set as administrators. Ideally, you should see only ones that you have set up. If you see any users there that you don’t recognize, those might be hacker accounts. You can lower their access by noting the ID number in the listing. (For the purposes of the query below, I’ll use the number 42.) Typing in:
Update wp_usermeta Set meta_value = 0 Where meta_key like ‘%user_level%’ and ID = 42
and clicking Go will lower their access level to 0 (Basic access). We could have deleted their account, but at this point I’d prefer to lower the access in case we need to use the account.
What if your account isn’t listed though? This would mean you’ve definitely lost administrative access. Let’s get that back for you. Run the following query (replacing "admin" with your administrative username):
Select ID from wp_users where user_login = ‘admin’
Make a note of your ID number. (You’ll need it again later.) Now run the following query. In place of your ID number, I’ll use the number 2.
Update wp_usermeta Set meta_value = 10 Where meta_key like ‘%user_level%’ and ID = 2
There’s one more administrative access level to check. Enter and run the following query:
SELECT u.*, m . *
FROM wp_usermeta m, wp_users u
WHERE u.id = m.user_id
AND m.meta_key LIKE ‘%capabilities%’ and m.meta_value like ‘%admin%’
Again, this should show only your administrator account. If a mystery account shows up, revert it to basic access by noting the ID number and running the following. (Again, I’m going to use 42 in my example. Replace it with the actual ID number.)
Update wp_usermeta Set meta_value = ‘a:0:{}’ Where meta_key like ‘%capabilities%’ and ID = 42
If your account wasn’t listed, run the following query (substituting your administrative ID – obtained earlier – for the number 2):
Update wp_usermeta Set meta_value = ‘a:1:{s:13:"administrator";b:1;}’ Where meta_key like ‘%capabilities%’ and ID = 2
Now that we’ve sorted out administrative access, log into your WordPress Admin panel. Keep phpMyAdmin open though, we’ll need that later. Once you are in, look for your posts. If they are there, then try to make them live. If you can, then congratulations. Your troubles should be over. You might want to secure your WordPress site more, though.
If you can’t make your posts live, then there are two other possible problems. The first possibility is that your database has grown so large that it is bumping against the limit your host set for it. To see how large your database is, run the following query:
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;
This should give you a listing of one or more databases with sizes. If your database sizes are more than what your host provides, then unfortunately there is little to do. You will need to contact your host to discuss your options.
If your database is under the limit, then most likely the database tables have been corrupted. Don’t worry, though. There is an easy fix. At the top of the page, above the Browse tab, the server should be listed. Next to that should be your database.. Click on the database’ name to see a listing of database tables. Next, click on the checkbox next to all of the tables relating to WordPress (all of the ones with the "wp_" or other prefix). Finally, at the bottom of the page, click on the "With selected" drop down and select "Repair table." The repair process should begin and, when it is done, you should see all your posts live again – just like SelfishMom did.
There is one possibility we didn’t cover yet, though. What happens if, after you log in, you find that all your posts are gone? While it is possible that they remain in the database somewhere and are recoverable, sadly this is too complex to cover here. The best bet here is to have a good backup process in place and to restore your database from a known good backup. You might lose a little bit of data in the process, but it’s better than losing everything.
I hope this has been an informative post on how you can recover your WordPress posts even under seemingly dire circumstances. Hopefully, you’ll never need to use them. Of course, should you find yourself in this situation and need some help from someone well-versed in the ins and outs of WordPress and mySQL, feel free to contact me.
