This topic discusses how to set up a connection from your web server to a MySQL server on another host.
This is an advanced topic that should be used only by an experienced network administrator or database administrator. You must have
root access to the file system and you must be able to log in to MySQL as
Before you begin, you must:
Use the following guidelines to configure remote database connections if your web server or database server are clustered:
If you have issues connecting to either host, first ping the other host to make sure it’s reachable. You also might need to allow connections from one host to another by modifying firewall and SELinux rules (if you use SELinux).
To create a remote connection:
rootprivileges, open your MySQL configuration file.To locate it, enter the following command:
mysql --helpThe location displays similar to the following:
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnfOn Ubuntu 16, the path is typically
bind-address.If it exists, change the value as follows.If it doesn’t exist, add it anywhere except the
bind-address = <ip address of your Magento web node>See MySQL documentation, especially if you have a clustered web server.
service mysqld restart
service mysql restart
If MySQL fails to start, look in syslog for the source of the issue. Resolve the issue using MySQL documentation or another authoritative source.
To enable your web node to connect to the database server, you must grant a web node database user access to the database on the remote server.
This example grants the
root database user full access to the database on the remote host.
To grant access to a database user:
GRANT ALL ON <local database name>.* TO <remote web node username>@<remote web node server ip address> IDENTIFIED BY '<database user password>';For example,
GRANT ALL ON remote_database.* TO email@example.com IDENTIFIED BY 'dbuserpassword';
If your web server is clustered, enter the same command on every web server. You must use the same username for every web server.
On your web node host, enter the following command to verify the connection works:
mysql -u <local database username> -h <database server ip address> -p
If the MySQL monitor displays as follows, the database is ready for connection
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 213 Server version: 5.6.26 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
If your web server is clustered, enter the command on each web server host.