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.
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="email@example.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:
In case you forgot dbname, dbusername or password, here’s the trick to retrieve:
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
Now you will see the proper dbname of your Magento 2 website, just update it in env.php file
Follow this MySQL command to list all users
SELECT user FROM mysql.user;
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
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.