Related

Product Now Available: Managed MySQL Databases Product
Wordpress Mysql Connection to new Droplet with Ubuntu 16 Question Question
Unable to create user in SQL Question Question

Question

wp site not showing pages, posts, images, favicon etc

Posted April 4, 2017 6.9k views
MySQLWordPressUbuntu 16.04

Hello, there!

I am testing a second wp site in my droplet.
I restored database and wp-content, but not everything shows up.
Plugins are there all activated. The theme is also active. But no pages or posts or images of the original site i am trying to migrate. It seems the site is not accessing its correct database. The articles showing are of the on board theme, ‘hello world’.

Some idea?

Add a comment
Areku
By:
Areku
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
17 answers
hansen April 4, 2017

@Areku

You need to edit the /wp-config.php in the root folder to ensure it points to the database and table you’ve just restored.

Seems like you’ve setup a default WordPress installation, which creates the default database, but did you overwrite that with the restore or did you restore into a different database?

Reply Report
  • Areku April 4, 2017

    @hansen

    Thanks!

    The default database is the one i chose the name when installing wp, right? I restored to a different database and changed the wp-config.php to the restored one.

    The correct database is there. When i command SHOW DATABASES; it shows up. The user has privileges granted also. The /wp-config.php is also all set up.

    Thanks for your help!

    Reply Report
Areku April 4, 2017

@hansen

Hi!

I campared the two config files one of my alive site in a shared host and the one i am installing in my droplet. The names of the table prefix were different. So i ajusted the name i the config file of my wp site and it is now showing everything.

But, i have a new problem: i can’t login anymore in the wp-admin.

I went to the config file again and reset the password. But it did not work.

:(

Reply Report
  • hansen April 4, 2017

    Ahh, yeah, that’s a tricky one to catch if you’re not used to play with the internals of WordPress.

    I don’t know what you use to view/edit your database with, but otherwise I would recommend www.adminer.org

    Goto your database, then the table called prefix_usermeta (where prefix would be whatever is your prefix).

    Now you’ll have to look in the column meta_key and find the row with something that would be oldprefix_capabilities (where oldprefix is whatever you had before).
    The meta_value of that row would probably be a:1:{s:13:"administrator";s:1:"1";} if you only had created one user that has administrator rights.

    Now change the oldprefix_capabilities to prefix_capabilities and that should fix it.

    Again, it might be a little confusing with prefix/oldprefix, but since I don’t know what your real prefix names are, then I can’t use those as examples.

    Reply Report
    • hansen April 4, 2017

      @Areku
      If you had multiple users, then you need to change the meta_value for each user. No matter if they were administrators or not - otherwise they cannot login either.
      You can see the user ID in the user_id column. Normally the first user you created has 1 as ID, which is automatically created as administrator. But you might have changed that later if you added other users or deleted the first user created.

      Reply Report
    • Areku April 4, 2017

      @hansen

      Great!!!!!

      The prefix of my fresh wp install was ‘wp’.
      The prefix of my wanted database is 'wordpress      ’

      Going to adminer site.

      <3

      Reply Report
      • hansen April 4, 2017

        @Areku
        Okay, you can do it manually - and maybe learn a little from going around in Adminer and the database.
        Or you can run this in your WordPress database, which should automatically search for wp_capabilities and replace it with wordpress_capabilities:

        UPDATE `wordpress_usermeta` SET `meta_key`='wordpress_capabilities' WHERE `meta_key`='wp_capabilities';
        Reply Report
        • hansen April 5, 2017

          @Areku
          Come to think of it, there’s another key which might need updating too.
          It’s called PREFIX_user_level - I’m not 100% sure if it’s required, so start without it to begin with otherwise run this to fix it.

          UPDATE `wordpress_usermeta` SET `meta_key`='wordpress_user_level' WHERE `meta_key`='wp_user_level';
          Reply Report
Areku April 5, 2017

I have only one user.
Now reading carefully your answers.
:)

Reply Report
Areku April 5, 2017

Hi, i am trying to open my database, USE 'xxx';, but mysql says ‘database changed’.

This is actually my second wordpress install. My main install is this one www.radha.org.br. So, i have to adjust only this specific database.

Reply Report
  • hansen April 5, 2017

    @Areku Remember the @ otherwise we don’t get notified.

    Just use Adminer, so you have a graphical interface, which is easier to work with.
    How many different database do you have?

    Yes, you need to adjust the settings only for the specific database, where you’re having problems with that restored WordPress.

    I cannot see which database you’re using on your site - that’s only visible in the wp-config.php file of the root of that site.

    Reply Report
Areku April 5, 2017

@hansen

Firstly, i am very grateful to your help.

I just asked in Adminer’s forum if they have a tutorial to install it in my droplet. I found something, but for apache users.

I have two databases for real and one ‘default’, justly this one i created when installing the second wp site, that i shall delete.

The site radha.org.br is alive.

The site arun.com.br is the one i am migrating to my VPS. It is alive in a shared hosting. I know the name of its database. But when i make the command USE xxx; Mysql says 'database changed’. So, i can not begin anything.

:(

Reply Report
Areku April 5, 2017

@hansen

Let’s remake the journey. if you still are available for that. :) Thanks a lot.

I installed a second wp site in My VPS, creating a database ‘default’. This install was made to bring my other domain in. Then i brought my desired domain’s database and wp-content in. This restored database brought in did not match with the configuration of the existing default wp-config.php file in the line 'table prefix’.

The table prefix in the freshed installed default wp-config.php file was set to 'wp_', which i changed to 'wordpress_', to match the configuration of the wp-config.php file from where this desired database came, in which the table prefix was 'wordpress_'.

Then the site showed as it should. :)
But then it was not possible to login in wp-admin. :(

Examining the Adminer i see that both tables you mentioned have 'wordpress_’ as table prefix in the actual configuration. What i think would be right. But, I tried to change it to its old value just see if it would result in anything positive. One first, testing, and the other. I tested it and still i couldn’t login in wp-admin.

I changed back the tables to their original values in Adminer and came here to report to you.

All the best,

  • Maybe the solution would be to redo the whole process, re-exporting the desired database with the table prefix to match the default one. I would have to learn how to do that in phpmyadmin.
Reply Report
  • hansen April 5, 2017

    @Areku
    Hehehe, of course we can redo everything :-)

    Which domain is the one you’re trying to move - arun.com.br ?
    Have you migrated the domain yet ? (EDIT: Meaning have you changed the DNS)

    What happens when you try to login in wp-admin ?

    Reply Report
Areku April 5, 2017

Yez. I am bringing arun.com.br to my droplet. But first i need to check if all is working in order to change DNS.

So i have two admins in my droplet:
In radha.org.br all is working and i login in radha-wp-admin successfully. in arun.com.br (testing mode via changing my local hosts file) i am not able anymore to go into arun-wp-admin after database adventure.

Thanks!!

Reply Report
Areku April 5, 2017

@hansen

sorry!

Just says my password or user is not right.

Reply Report
  • hansen April 5, 2017

    @Areku

    Okay, let’s get everything cleaned up.

    1. Create a database called arun.
    2. Import your data dump into that database. (what’s the prefix?)
    3. Now goto the web-root of the site (I’m guessing /var/www/arun.com.br) and edit wp-config.php
    4. In wp-config.php, make sure to update the username and password, but also set the following define('DB_NAME', 'arun'); and $table_prefix = 'wordpress_'; (has to match the prefix from the data import).

    So no changing anything in the database, just do a straight import and let’s see where that leaves us.

    If it’s still complaining about username/password, then run the following query:

    USE `arun`; UPDATE `wordpress_users` SET `user_pass`=MD5('change-this-password') WHERE `ID`=1

    And then try to login with whatever the username is and password change-this-password

    Reply Report
Areku April 5, 2017

@hansen

Thank you so much!

1) Ok. the database ‘arun’ is already there. Actually it is the original name of the first database created to arun.com.br’s wp.
2) I don’t know the command to import the *sql.gz INTO database arun.
2.1) The prefix used in my shared hosting wp is 'wordpress’ The prefix used in this new wp install in my droplet is 'wp’.
3) and 4) OK.

I shall delete this one database that did not work, right? And leave the wp-content, i guess…

Fine, @hansen

I’ll have a nice sleep now after whole day of commands. I close my eyes and keep commanding lines into my dreams. Funny.

All the best

Reply Report
  • hansen April 5, 2017

    @Areku

    1. Remove the database arun and create it again, so we’re sure it’s clean.

    2. Use Adminer to import the data to arun.

    We will continue to use wordpress_ as the prefix, so we don’t need to modify anything.

    Reply Report
Areku April 5, 2017

@hansen

Hello!

Did all as instructed. It looks like this in Adminer:

information_schema          ?               ?
arun                                    utf8_unicode_ci ?   ?
bddarunwp                           utf8_general_ci ?   ?

Any additional step now?

Reply Report
Areku April 5, 2017

@hansen

Now i configured wp via backend, but the content does not appear in frontend.

Reply Report
  • hansen April 5, 2017

    @Areku

    But did you import the data into the database arun ?

    There should be no configuration besides a couple of modifications to wp-config.php
    The site should simply work after you’ve adjusted the database connection.

    Reply Report
Areku April 5, 2017

@hansen

Well, i logged in in adminer into database arun and imported the database desired. But certainly i did not do it correctly, cause i can see two databases, and not only one.

Reply Report
  • hansen April 5, 2017

    @Areku
    Remove the arun database. Then import the data, which will create a database (since that’s what you’ve chosen when you exported the data).
    Now rename the newly created database to arun, and then make the small adjustment to wp-config.php so it reads from that database (and with appropriate logins)

    Reply Report
Areku April 5, 2017

@hansen

I am seeing a way to move one into another. Must be it. :).

Reply Report
Areku April 5, 2017

@hansen

Thank you!

I had some trouble figuring out everything. I deleted the two databases. I then created a database with the same name of my desired wp site. Granted priviledeges to user. In the config file i changed the database for this last created one.

And all is working.

But there is a mistery. It may be due to the fact i am in a testing mode via my local hosts file. When i try to login in wp-admin with my oficial user in the new wp install my user is denied. However, when i login with my user from my wp site alive in the shared hosting, it works.

I presume that when i change DNS, what i’ll be doing today, this error will be away.

What do you think?

Reply Report
  • hansen April 6, 2017

    @Areku

    Great. You’ve come a long way. Good work!

    Please define with my oficial user - what do you mean, which user, from where?

    Since you migrated the entire database - the login should be exactly the same as the current shared site. Just like the rest of the site, there shouldn’t be any differences.

    I would recommend going live with DNS and then let’s troubleshoot directly if there are any smaller issues.

    Reply Report
Areku April 6, 2017

@hansen

I meant the user configured in my fresh installed wp-config.php.
I’ll go live and see.

Thanks!!

Reply Report
Areku April 6, 2017

@hansen

Site alive.
But.
The conflict with permissions continue.
I log in my wp-admin with user and password of my older install in the shared hosting. It does not accept the user and pass defined in mysql and wp-config file in the new install.
I have only one database for the new wp site. Where to begin.

Reply Report
  • hansen April 6, 2017

    @Areku

    Great. Then everything is working exactly like expected - and that is correct.

    You have many different logins/passwords - and that’s correct.

    You have a password for the root user for the droplet. You have a different login for MySQL. And finally you have a different login for WordPress.

    Since you have migrated a database a WordPress, the login for that WordPress will not change.
    The login for the MySQL is likely different, since you’ve setup a new installation of MySQL compared to the one you were running on the shared host.

    Everything is correct. Congrats on successful migration :-)

    Reply Report
Areku April 6, 2017
[deleted]
Reply Report
Areku April 6, 2017

@hansen

Thank you a looooot!
All the best!!

Reply Report
  • hansen April 6, 2017

    @Areku

    The login to the server (Ubuntu in your case), is different from the login to MySQL, which is different from the login to WordPress.

    You could set all three logins to the same - which I would highly recommend NOT doing.

    The login in the wp-config.php is the login for MySQL - not WordPress. This is probably the root cause of your problems you had in the beginning, which I didn’t catch, so I led your on a larger ride than it should had been - sorry :-)

    Did you solve the update of plugins problem? You need to chown the newly migrated wp-content to match the user of PHP, which is default www-data
    This should do it chown -R www-data:www-data /var/www/arun.com.br

    Reply Report
    • Areku April 6, 2017

      @hansen

      Fine! You were more than helpful. Thanks!

      Yes i solved exactly doing that. I went back to @jellingwood tutorial wp in lemp… and there it was… i am making the updates and then i am going to close again, as instructed in the tutorial.

      All the best!!

      Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help