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:
- Follow the path: /app/etc/
- Locate the local.xml file
- 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:
- Follow the path: //app/etc/
- Locate the env.php file
- 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="//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.