Magento 2 database config file location

When you are working on Magento, you may have to make changes in Magento database configuration file, now and then.

Editing Magento DB Config files are important when:

• Installing a fresh Magento site
• Changing DB or Magento site
• Migrating the store to a new server

This article will guide you to change the Magento 1 and Magento 2 db config files.

Magento Database Configuration File

In Magento, edit the db:

1. Follow the path: /app/etc/
2. Locate the local.xml file
3. Open the local.xml file with your editor

You will see the code like this:

This file is editable. And you can edit all the values needed such as:

• Host: Host address
• Username/Password: Account information that has access to Database
• Database Name: the Name of Magento website database.

After making the changes, save the file.

For Magento 2 db Config File

In Magento 2, edit the db:

1. Follow the path: //app/etc/
2. Locate the env.php file
3. Open the env.php file with your editor

You will find a code like this:

From here, change the values.

• Host: Change the Host Address
• Username/Password: Changing the account info of the Magento 2
• Database Name: Name of the Magento DB

After making the change, save the updates.

Maintenance of the database file is important for Magento store.

I hope this small tutorial helped you in configuring the Magento database files. if any doubt, leave it in the comments.

Also Read

• How to Change Magento 2 Base URL?
• Magento 2 Commands: A Useful list for Magento Developers
• Magento 2 Requirements that You Must Take care of

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="" 
     --admin-user="admin"
     --admin-password="magentip123" 
     --backend-frontname="admin"
     --language="en_US"
     --currency="USD"
     --timezone="America/Chicago"
     --use-rewrites=1

Also, in .htaccess file in your Magento 2 folder, try replacing FollowSymlinks directives with SymlinkIfOwnwner  => This should help resolve this issue

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.

Where is Magento database config file?

Where is database in Magento 2?

How can I change database in Magento 2?

What is System Configuration in Magento 2?