Magento database configuration: The simple guide to change it in Magento 2

When working with Magento 2, sometimes you need to change the Magento database configuration when updating database information or move Magento 2 website to a new hosting. In this post today, Magesolution will show you a simple guide to changing the database configuration in Magento 2. Also, we will also list out some of the most popular issues related to the Magento 2 database and how to solve them.

Magento database configuration file

Magento has transformed into an enhanced eCommerce platform with the release of Magento 2. With the improvement of performance and security-related features, Magento 2 comes with some changes in the file structure too. When working with Magento 2, sometimes you need to change Magento database configuration information such as

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

How to change Magento 2 database configuration

If you need to change your Magento 2 database server hostname, database username, password, or database name, you have to edit the below file.

/Magento2 Install Dir/app/etc/env.php

Go to your Magento installation directory and edit the app/etc/env.php configuration file in your favorite editor and search for the following settings and perform crucial changes.

  'db' =>
  array (
    'table_prefix' => '',
    'connection' =>
    array (
      'default' =>
      array (
        'host' => 'localhost',
        'dbname' => 'database_name',
        'username' => 'database_username',
        'password' => 'database_password',
        'active' => '1',
      ),
    ),
  ),

Now update the orange highlighted values as following

• localhost: MySQL server hostname.
• database_username: MySQL user to connect database server.
• database_password: MySQL user password.
• database_name: MySQL database name of Magento.

After you’re done configuring, make sure to save the file and clear your cache by moving into your Magento backend and choosing System > Tools > Cache Management.> Flush Magento Cache.

How to  download Magento 2 app/etc/env.php file

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

How to fix some database issues in Magento 2

1. Issues while establishing database connection after successfully installing Magento 2

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

Error while establishing database connection

This may occur because you run the wrong command to install Magento 2.

You can retry installing by using this syntax

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

Besides, in the .htaccess file in your Magento 2 folder, you try replacing FollowSymlinks directives with SymlinkIfOwnwner . This should help solve this issue

2. Issues while establishing database connection when migrating your site to a new hosting

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

This issue occurs because the database information was not set correctly in the env.php file. And to fix this error, you will need to recheck database information and replace the incorrect information 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 it:

dbname

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

mysql -uroot -p

and error the following syntax to show all databases

SHOW DATABASES;

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

dbuser

Follow this MySQL command to list all users

SELECT user FROM mysql.user;

dbpassword

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

Firstly, you have to stop the MySQL service:

service mysql stop

Next step, you run this command to enable connection to the MySQL server without a password

mysqld_safe --skip-grant-tables &

After that, you need to login into MySQL and follow this syntax to set a new root password

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

Finally, restart the MySQL server

service mysql restart

to change the password for a non-root user, you run these commands.

mysql> use mysql;

Next step, you change the user password.

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

Replace the user  mynewpass with your MySQL user and new password.

Conclusion

That’s all about the Magento database configuration and how to change it in Magento 2.  We hope you find it useful. In case you face any difficulties, we are here to help you. As one of the leading eCommerce development and solutions providers in Vietnam, we provide the Magento Website Development service, which assists you increase the speed and user experience of your Magento store. CONTACT US and our developers will definitely help you!