1.4. Direct Connection to MySQL Server
1. Database connection
This section describes how to set up a direct connection to your MySQL server, using Store Manager for Magento.
Store Manager for Magento requires a direct MySQL database connection. Most hosting companies allow direct connections to MySQL databases. Typically, this is accomplished by accessing your assigned control panel and adding your home/office computer IP address, or domain name, to the Access List of IP addresses that are allowed remote access to your MySQL database, usually through port 3306. This option can be found under the MySQL settings section. Feel free to ask your hosting provider's support department for assistance, if you experience difficulty enabling direct MySQL access. They will be glad to help, as it is a relatively trivial task.
To configure a direct connection to your remote database, follow these steps:
1. Open Settings -> Preferences -> Database Connection Tab (F12)
2. Enable the Direct connection to remote database checkbox
3. Specify the following Remote Database Connection Settings:
"Host (Server address)". Usually it is your website name, such as "mywebsite.com" (do not include the http://www. prefix); you may use your IP address as well (also without the http://www. prefix). Please check your store's server parameters (located in the local.xml file, in your store's /app/etc directory). Open the local.xml file and you will find your database server name (host).
<host><![CDATA[mywebsite.com]]></host>
"Port". 3306 is used in most cases (if necessary, please ask your hosting provider regarding port verification).
"MySQL Version". Most hosting companies typically support versions 4.1 or 4.0 and, or, some version of 5.x. You can select "Auto", if you're not sure which choice to select.
"User name (login)", "Password" and "Database (name)". Specify your username, password and database name. You will find them in the local.xml file, as well:
<username><![CDATA[name]]></username> //specifies your User Name (login)
<password><![CDATA[pass]]></password> //specifies your password
<dbname><![CDATA[remote_database]]></dbname> //specifies your database name
You may also type manually the type database name, or use the browse button [...] to see the list of available databases and select the one you need from the drop-down list.
Tip: Use the [Test Connection] button to verify whether all settings are correct and a connection to your remote database is possible.
4. Click [OK] to save these entries, and close Preferences to connect to your database, or click cancel to discard any changes.
You can also use the Database Connection wizard to set up your connection, or select Database Backup/Restore to select additional options.
If you're unable to connect to your remote database, here are few key items to check.
If you receive a message "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" is your IP), you will need to add your IP address to your remote Access List for allowable direct MySQL connections. Please see the article how to add your IP to the Access List.
From the latest updated version of Store Manager, you are able to configure some advanced settings details such as SSL version and Server Name Indication (SNI) checkbox.
If you have the private SSL certificate to make your connection more secure, you can choose the correct version of the Secure Sockets Layer from the drop-down list and check the SNI fields if you are using such extension as well.
2. Advanced Database Settings
Advanced Database Settings allows you to configure additional settings for your current database.
"Use MySQL protocol compression". Use the Compress property to use compression while transferring data. Setting this property to True is quite effective, transferring large volumes of data, even through relatively slow connections. Pay attention that each row is compressed separately. Be careful when setting this option as in some cases it may decrease fetch speed, rather than increasing it. This property is ignored under CLR. The default value is False.
"Write detailed SQL log". Select this checkbox if you need to see all the SQL.log files that were created by Store Manager during processing.
"Table prefix". Enter your database prefix for the correct functioning of Store Manager with your currently selected database. Click the [Autodetect] button and the database prefix will be determined automatically.
"Rebuild 'catalog_category_product_index' for entire product category tree". The drop down list allows you to select whether you want to specify re-indexing of your data. You can select one of the following settings for it, "Auto", "Enable", or "Disable".
"Delete special price values when it is equal 0". Store Manager will, if this option is enabled, prevent a net value of "0" being accidentally entered into this field, reverting it to a "blank", or non-specified value. This will keep you from inadvertently making "unwanted gifts" to your Clients. The result is that the product's "Regular Price" and product availability, will remain in effect.
Please check the following guide on how to connect with the store directly by using the manual or automated methods: