Magento 2 database configuration file – All you need to know

When working with Magento 2, sometimes you need to change database information. For example

• Changing database when moving Magento 2 website to a new hosting.
• Changing database when updating database information like Database name, Database username, Database password
• Install new Magento 2 website

This tutorial will show you step by step how changes database configuration in Magento 2. I will also list out some most commons issues related to Magento 2 database and how to fix them.

Magento 2 database configuration file

Unlike Magento 1, Magento 2 store database information in env.php , replacing local.xml in Magento 1.x.

Path to Magento 2 database configuration file

The path to Magento 2 database configuration file is /app/etc/env.php

In Magento 1, path to database config file is: /app/etc/local.xml

Change Magento 2 database configuration

By using any file editor programs, you can edit and change database information, here’s how to do it.

Changing database Information using file manager programs like Winscp, Filezilla, Bitvise.

Step 1: Connect to your server using SFTP or FTP protocol and navigate to \app\etc folder

Step 2: Edit env.php file and find the following lines

'table_prefix' => '',
'connection' =>
    array (
        'default' =>
            array (
                'host' => '127.0.0.1',
                'dbname' => 'database',
                'username' => 'root',
                'password' => 'password',
                'model' => 'mysql4',
                'engine' => 'innodb',
                'initStatements' => 'SET NAMES utf8;',
                'active' => '1',
            ),
    ),

Step 3: Replace the following information with your database information

• ‘dbname’ => ‘database’, : Your database name
• ‘username’ => ‘root’, : Your database username
• ‘password’ => ‘password’, : You database password

Finally, save the file to update changes.

Download Magento 2 app/etc/env.php file

You will not find Env.php in app/etc folder if you didn’t go through the Magento 2 installation process. This file will be automatically generated after you finish the database configuration step.

In case you need Env.php, you can download it from the github link below.

> Download env.php

Fix common Magento 2 database issues

There are some common errors related to Magento 2 database, I will list solutions below:

Error while establishing database connection when migrating Magento 2 website to a new hosting

You may see this error after installing Magento 2 or after moving Magento 2 website to a new hosting provider

Error while establishing database connection

This error happens because the database information was not set correctly in env.php file. To resolve this error, you will need to recheck database information and replace wrong inforrmation in env.php.

The lines to be replaced are:

'dbname' => 'database', 
'username' => 'root', 
'password' => 'password'

In case you forgot dbname, dbusername or password, here’s the trick to retrieve:

dbname

Connect to your server using SSH and login to MySQL with root access

mysql -uroot -p

and issue the following command to display all databases

SHOW DATABASES;

Now you will see the correct dbname of your website, just update it in env.php file

dbuser

Use this MySQL command to list all users

SELECT user FROM mysql.user;

dbpassword

It’s a bit more complicated to retrieve database root password

First, stop MySQL service

service mysql stop

Next use this command to enable connect to MySQL server without password

mysqld_safe --skip-grant-tables &

Now login to MySQL and use this command to set new root password

UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';

Finally, restart MySQL server

service mysql restart

to change password for non-root user, use these commands

mysql> use mysql;

Next, change the user password

ALTER USER 'user'@'hostname' IDENTIFIED BY 'mynewpass';

Replace user and mynewpass with your MySQL user and new password

Error while establishing database connection after successfully installed Magento 2

After a successful installation of Magento 2, you go to admin URL and still see the error

Error while establishing database connection

This may happen because you use wrong command to install Magento 2.

You can retry installing using this:

php bin/magento setup:install 
     --base-url="http://example.com/shop/"
     --db-host="127.0.0.1" 
     --db-name="mydbname"
     --db-user="mydbuser" 
     --db-password="mydbpassword"
     --admin-firstname="Magen" 
     --admin-lastname="Tip"
     --admin-email="magentip@gmail.com" 
     --admin-user="admin"
     --admin-password="magentip123" 
     --backend-frontname="admin"
     --language="en_US"
     --currency="USD"
     --timezone="America/Chicago"
     --use-rewrites=1

SQLSTATE[HY000] [1045] Access denied for user ‘magento’@’localhost

This error message may occur during Magento 2 installation process, you may see it at Add a Database step

To fix this error, you need to recreate a new database and input correct database information here.

Conclusion

Magento 2 is a very complex platform. There may be many problems related to database arising when working with Magento 2. If you still have any troubles when working with Magento 2 database, please drop your question below. I will try my best to bring out solution for your issue.