After restoring, incorrect database credentials are used

Question

I am trying to get a local drupal environment up and running using an sql dump from a production environment. I am attempting to follow instructions from drupal's documentation.

So far, from our production server, I went ahead and ran drush sql-dump > prod_drupal_backup.sql. Then, I used scp to pull the prod_drupal_backup.sql file from the production server.

On my local machine, using MAMP, I created a database (prod_drupal, same as the database on the production server) and gave a user, admin, access to the database. I went ahead and pulled down the codebase and created a fresh Drupal instance. During this set up, I gave Drupal the admin credentials to connect to the database and had a fresh site up and running.

With a backup, I was ready to restore the database using the prod_drupal_backup.sql SQL dump file. So I went and ran /Applications/MAMP/Library/bin/mysql -uroot prod_drupal < ~/Desktop/db_backup.sql. After this completed, I checked the database via MAMP's localhost:8888/phpmyadmin. The prod_drupal database had the correct data in it. So then I went to the website (localhost:8888) and saw what I wanted, the login screen with all of our styling and everything. When I logged in using one of the accounts registered for the site, it thrown the The website encountered an unexpected error. Please try again later. error.

When I look at my MAMP sql error logs in mysql_error.log, I see errors like access denied for user 'mikesmith@localhost'. This makes zero sense because Drupal is supposed to be connecting to the database with admin, not mikesmith. The way Drupal is trying to connect to the database doesn't match what's in the settings.php file.

What am I doing wrong?

I am using MAMP Pro 5.5.1 and PHP 7.2.21, if this makes any difference.

Answer 1

Some possible solutions / debugging steps:

Check that your local settings.php file is correct by using drush status command. Do this when in the same directory as the settings.php file (or with a correctly set up site alias). You should see information about the database and drupal version. If you don't then something is wrong with your settings.

Next, Drupal uses the DB to cache things in the DB. This cached information in an SQL dump can cause a local site to crash. Some ways to possible ways to correct this are:

• On the local site, try using the drush cc (assumed D7 per tag) on the command line.

(OR)

• Prior to making the dump from production, clear cache (either via admin screen or drush cc).

(OR)

• Remove the contents of any Cache_* table on the local site (keep the tables)

Another possibility is that the password encryption salt string is different between the sites. This is set in the settings.php file and should be the same on both sites.

Finally, you can always reset the local user password via drush. Two useful drush commands are:

• drush uli --name=xxx (Gets a password reset URL.. may need to put the correct host name)

• drush upwd [username] [new-password]