1.4 Direct Connection to MySQL Database

fmDirectConnection - fmPreferences

This section describes how to connect Store Manager for PrestaShop to your store database using the Direct Connection to MySQL Database connection type.

Direct Connection Overview

Advantages:

  • You will see everything you’ve changed in your online store instantly.

  • There is the possibility to work with the store in groups up to 4 people without any conflicts with data.

Disadvantages:

  • The direct connection to the database has to be permitted at a server. Some hosting companies forbid those types of connection, by default. In this case, you should consider the Bridge PHP connection.

  • The speed of direct connection depends on the speed of your internet and server power.

  • Import of a great amount of data to your store, for instance, 100 000 of products, will take more time with direct connection than with the Bridge connection.

Note Most hosting companies allow direct connections to MySQL databases. However, your hosting may block external IPs from accessing your store database. To avoid being blocked, you have to add your home/office computer IP address or domain name to the Access List – a list of IP addresses allowed to access MySQL port 3306 from outside.

Read a guide on How to add my IP address to Access List and allow direct MySQL connections.

Feel free to ask your hosting provider support to assist you in enabling direct MySQL access.

Steps to Set Up Direct Connection to MySQL Database

1. Open Preferences -> Database Connection (F12)

2. Select "Direct connection to your store database"

3. Under Remote Database Connection Settings, specify your store database settings.

For PrestaShop Up to 1.6.x

      1. Open your store using any FTP Client (like FileZilla, or any other).

      2. Open your store root directory via FTP and go to the config directory.

      3. Open the settings.inc.php file

In the file, find the following details and copy them into corresponding fields in Store Manager:

define('_DB_SERVER_', 'localhost')

define('_DB_NAME_', 'your_database_name_goes_here');

define('_DB_USER_', 'your_user_goes_here');

define('_DB_PASSWD_', 'your_database_password_goes_here');

define('_DB_PREFIX_', 'ps_');

For PrestaShop 1.7.x and Higher

      1. Open your store using any FTP Client (like FileZilla, or any other).

      2. Open your store root directory via FTP and go to app directory -> config directory

      3. Open the Parameters.php file.

      4. In the file, find the following details and copy them into corresponding fields in Store Manager:

'database_host' => 'localhost',

'database_name' => 'your_database_name_goes_here',

'database_user' => 'your_user_goes_here',

'database_password' => 'your_database_password_goes_here',

'database_prefix' => 'ps_',

Host address is usually your website name, like "mywebsite.com" (do not include http://www. prefix). Also, you may use an IP address.

If the row define'_DB_SERVER_'/database_host shows"localhost" value, use your store URL (without http://www) as a Host address.

Port is usually 3306 (ask your hosting provider to check if the one is used).

Select database - click the button to see the list of available databases and select the one you need from the drop-down.


After you have configured all settings, press [Test Connection] to verify the settings are correct and connection to your remote database is possible.

If the connection is successful, click [OK] to save this entry and close Preferences to connect to your database.

Troubleshoot Connection Errors

  • If you're unable to connect to a remote database, here are the key items to check.

  • If you got the "SQL ERROR: Access denied for user 'admin'@'91.203.79.82' (using Password: YES)" notification (where 'admin' is your username and '91.203.79.82' your IP), you have to add your IP address to the Access List and allow direct MySQL connections. Please check the article on how to add your IP to the Access List.

Advanced Database Settings

Advanced Database Settings allow you to set up additional settings for your current database.

"Write SQL log". You can select this checkbox if you need to see all SQL log files which were created by Store Manager during its work.

"Table prefix". You need to enter your database prefix for correct work of your Store Manager with the current database. You can click [Autodetect] button and database prefix will be determined automatically.

To set up your connection step by step, read an article on how to use Database Connection Wizard.