I came across a problem today while working with a client’s WordPress page and the solution taught me a little bit about phpMyAdmin, WordPress, and databases – so I thought I’d share.
My client uses register.com for hosting, which allows for phpMyAdmin access but doesn’t give the root user high enough privileges to make changes to databases. You can only export tables. My client also has no way to access the front-end of the wordpress site, since they lost the password and access to the set-up email address.
The problem was that the client wanted to move all blog posts from the old site to a new site I had set up.
I couldn’t log into their wordpress site to export the posts. I didn’t have privileges to change the password or email to their database. Register.com was also being cranky with ssh – I couldn’t figure out how to access the server. All I could do was download a .sql file.
Once I had done that, I had to install phpMyAdmin on the new server that I had made. I hosted with Digital Ocean and was able to follow their tutorial here. DO sometimes doesn’t set a mysql password, meaning you can access with just
$ mysql -u root -p
I had to set a mysql password before I could configure phpMyAdmin, then reset privileges, and restart mysql. Once phpMyAdmin is installed, you can open it with http://yoursite.com/phpMyAdmin and login with your mysql username and password.
From there, all I had to do was import the .sql file (gzipped) to the wp_posts table. I ran into some trouble with duplicate ID numbers – these either needed to be changed or deleted, which did mess with a few small things on the site. It would’ve been best to import this data before there was any conflicting data in the wp_posts table.
The import was successful, and my posts populated in the WordPress dashboard!
I learned that all wordpress posts and pages are NOT stored in the wordpress site content folder, but actually all in the database. This will change how I backup my site – I thought all I need to back up was the content there, but it looks like you need to backup the database as well.