4

IP my Website: 192.168.19.33 and IP Database server is: 192.168.19.41.

How to create user account in phpmyadmin for the connection between the two servers?

And how to edit this setting:

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=website
DB_USERNAME=root
DB_PASSWORD=123456

to this setting:

DB_CONNECTION=mysql
DB_HOST=192.168.19.41
DB_PORT=3306
DB_DATABASE=website
DB_USERNAME=username
DB_PASSWORD=password
  • Why do you need to connect to two servers? Your database only resides on one server. – John Conde Dec 29 '16 at 14:39
  • @JohnConde Hi, i have 5 servers and need one database server, but i do not know how to connect to phpmyadmin in another server... – mySun Dec 29 '16 at 14:42
  • 1
    If you have one database server you just need to connect to that one server since all phpmyadmin is a user interface for mysql. It doesn't need to know or care about your web servers. – John Conde Dec 29 '16 at 14:43
  • @JohnConde I'm sorry my English is not good, how to setting DB_HOST instead localhost ? – mySun Dec 29 '16 at 14:47
  • 1
    You'll need to allow remote connection through your control panel or ssh – Abu Nooh Dec 29 '16 at 15:15
3

Create the remote connection

To create a remote connection:

  1. On your database server, as a user with root privileges, open your MySQL configuration file.

    To locate it, enter the following command:

    mysql --help

    The 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.cnf

  2. Search the configuration file for bind-address.

    If it exists, change the value as follows.

    If it doesn’t exist, add it anywhere except the [mysqld] section.

    bind-address = 192.168.19.33

  3. Save your changes to the configuration file and exit the text editor.

  4. Restart the MySQL service: service mysql restart


Grant access to a database user

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:

  1. Log in to the database server.
  2. Connect to the MySQL database as the root user.
  3. Enter the following command:

    GRANT ALL ON website.* TO username@192.168.19.33 IDENTIFIED BY 'password';


Verify database access

On your web node host, enter the following command to verify the connection works:

mysql -u username -h 192.168.19.41 -p

If the MySQL monitor displays as follows, the database is ready for use by the website:

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.

Source: http://devdocs.magento.com/guides/v2.0/install-gde/prereq/mysql_remote.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.