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

Magento database configuration

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 change 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 to edit the below file.

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

Go to your Magento installation directory and edit 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 choose System > Tools > Cache Management.> Flush Magento Cache.

How to  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 created after you complete the database configuration step.

database-configuration-magento-2-768x396

How to fix some database issues in Magento 2

1. Issues while establishing database connection after successfully installed 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 .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

Error while establishing database connection

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:

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 database root password

Firstly, you have to stop MySQL service

service mysql stop

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

mysqld_safe --skip-grant-tables &

After that, you need to login to MySQL and follow this syntax to set a 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, you  run these commands

mysql> use mysql;

Next step, you change the user password

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

Replace user and 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 and in case you face any difficulties, CONTACT US and our developers will definitely help you! Magesolution is one of the leading Magento 2 extensions & theme providers and magento web development services in the world. We provide high-quality products and services, specifically eCommerce, Web, and Apps Development that will meet every simple to complex requirement.

 

 

206 Total Views 3 Views Today