Blogs

Set up a remote MySQL database connection | Adobe Commerce

Follow Saigon Techsoluionts

Sometimes, you might want to host the database on a separate server instead of running the database server and the Web server on the same computer.

Adobe has provided a way to connect to a MySQL server on a different machine. Starting with Adobe Commerce and Magento Open Source 2.4.3, you can also configure your application to use an Amazon Web Services (AWS) Aurora database with no code changes.

Aurora is a high-performance, fully compatible MySQL server hosted on AWS.

Connecting to an

AWS Aurora Database Using Aurora as your database is as easy as specifying the database in

the normal configuration settings of Adobe Commerce and Magento Open Source, using the default database connector.

When running bin/magento setup:install

, use the Aurora information in the db-: bin/magento setup:install

… -db-host=’database-aurora.us-east-1.rds.amazonaws.com’ -db-name=’magento2′ -db-user=’username’ -db-password=’password’ …

The db-host value is the Aurora URL with the https:// and ending :p ortnumber removed.

Configuring

a remote database connection

Prerequisites

Before you begin, you must:

Install the MySQL server on the database server. Create a DB instance on

  • the database server

    • . Install the

  • MySQL client on the
  • Adobe Commerce or Magento open source web node. See the MySQL documentation for more details.

High availability

Use the following guidelines

to configure remote database connections if the Web server or database server is clustered:

  • You must configure a connection for each node on the Web server

    • . Typically, you configure

  • a database connection to the database load balancer; however, clustering of databases can be complex and is up to you to configure it. Adobe does not make specific recommendations for database clustering.

    For more information, see the MySQL documentation.

Troubleshooting

connection issues

If you’re having trouble connecting to either host, first ping the other host to make sure it’s reachable. You might need to allow connections from one host to another by modifying the firewall and SELinux rules (if you use SELinux).

Create

the remote connection To

create a remote connection

:

  1. On the database server, as a user with root privileges, open

    the MySQL configuration file.

    To locate

    it, type the following command: mysql -help The

    location looks similar to

    the following: The default options are read from the following files in the order listed:

    2. /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf Locate the binding address in the configuration file. If it

  2. exists, change the value as follows. If it

    does not exist, Add it to the [mysqld] section.

    bind-address = <web node IP address>

    See the MySQL documentation, especially if you have a clustered web server.

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

  4. Restart MySQL

    service:

    • CentOS: mysqld restart

      • service

    • Ubuntu: mysql restart service

Grant access to a database user To allow the 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 on

    2. to the database server.

  2. Connect to

    3. the MySQL database as root.

  3. Type the following command:

    GRANT ALL ON <local database name>.* TO <remote web node user name>@<remote web node server IP address> IDENTIFIED BY ‘<database user password>’;

    For example

    GRANT ALL ON magento_remote.* TO dbuser@192.0.2.50 IDENTIFIED BY ‘dbuserpassword’;

Verify database access

On the web node host, type the following command to verify that the connection is working

: mysql -u <local database user name> -h <database server IP address> -p

If the MySQL monitor is displayed as follows, the database is ready for Adobe Commerce or Magento Open Source: 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 the web server

is clustered, type the command on each web server host.

Install Adobe Commerce or Magento Open Source

When installing Adobe Commerce or Magento Open Source, you must specify the following:

The base URL (also called the store address) specifies the host name or IP address of the web node The database host is the IP address of the remote database

server

  • (or load balancer if the database server is clustered) User name of the

    • database is the local web node

  • Database user to which you gave access
  • The

    • database password is the password of the user of the local web node Database name

  • is the name of the database on the remote server

Contact US