Bobcares

How to setup MySQL remote access in Linode?

by | Jan 15, 2019

These days many web applications have files and databases lying on separate servers.

Due to security restrictions, database server allows connection only from web servers. And, if your website developer needs to check the database, MySQL remote access comes handy.

But, enabling remote MySQL access in Linode may not always work correctly due to configuration errors.

At Bobcares, we help Linode server owners to set up remote MySQL access as part of our Managed Cloud Services.

Today, we’ll see how our Support Engineers setup remote MySQL access in Linode and fix common errors.

 

Risks in enabling remote MySQL access

Running MySQL on a separate Linode server allows to allocate more resource exclusively for databases. Therefore, at some point of time, there will be a need to set up remote access on the server.

But, this has to be done considering the security of the database server. Allowing public access on MySQL server can make your Linode server a target for attack.  This can affect all the databases running on the server. That’s why, our Support Engineers always restrict remote MySQL access to selected trusted IP addresses. Again, the connection will be limited to specific databases too.

 

How we setup remote MySQL access in Linode?

Now, let’s have a look on how we enable remote MySQL access in Linode server.

The process starts with the setup of database server. Some servers have MySQL while some may have MariaDB. For example, in CentOS7 servers,  MySQL is no longer in the repositories and MariaDB has become the default database system.

Here, our Dedicated Engineers fetch the required packages and install MySQL on the server. Now we need to enable remote MySQL access.

1. MySQL IP address binding

As the first step, we need to ensure that MySQL is properly listening on port 3306 of the server IP address. This can be checked by looking the service ports listening on the server.

For example, our Support Engineers use the netstat command to confirm MySQL binding.

In a CentOS7 server, the results will look like :

[root@li4xx-241 ~]# netstat -lpan | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      14763/mysqld

 

2. Firewall modification

By default, most servers deny connections on MySQL port. Usually, this restriction happens in the server firewall.  So, to allow remote access, we need to allow port 3306 traffic from trusted IP address.

For example, to allow port 3306 traffic on a CentOS7 server, our Support Engineers modify Firewalld using the below commands:

firewall-cmd --permanent --zone=trusted --add-source=192.0.x.x
firewall-cmd --permanent --zone=trusted --add-port=3306/tcp
firewall-cmd --reload

This allows MySQL connection from IP 192.0.x.x.

 

3. MySQL user and privileges

Last, but not the least, for remote MySQL access to work, we need to set MySQL users and privileges. We need to add the MySQL user to the database too.

To give remote access to user ‘mydbuser’ to database ‘mydb’ from IP address 61.xx.xx.20, our Security Engineers use the following command in MySQL prompt of the Linode server:

mysql> grant all on mydb.* to 'mydbuser'@'61.xx.xx.20' identified by 'ww4ICED0XsUw.';
Query OK, 0 rows affected (0.00 sec)

After this, the remote MySQL connection to Linode server works correctly.

 

Common errors with Linode remote MySQL access and fixes

Although, enabling remote MySQL involves only basic steps, we often see customers reporting trouble connecting to database servers. Let’s have a quick look at them.

1. Wrong firewall

Recently, a Linode customer reported problems when connecting remotely. The error said:

Can't connect to MySQL server on 'xx.xx.xx.xx'(10061)

Here, our Support Engineers analyzed the server logs and found that the problem was with the server firewall. The customer’s IP address was not specifically allowed in the firewall. Modifying the firewall fixed the problem and connection worked fine.

 

2. Wrong MySQL access host

Similarly, problems can happen when proper MySQL access host is not set up.

Here, the remote IP address was already white-listed in the server firewall. But, remote MySQL connection reported the error:

HHost 'xyz.domain' is not allowed to connect to this MySQL serverConnection closed by foreign host.

To fix, our Dedicated Engineers had to configure MySQL access host for the database user from the MySQL prompt of the Linode server.

 

Conclusion

In short, enabling remote MySQL access in Linode servers involves modifying MySQL configuration, enabling IP address in firewall, etc. Today, we’ve seen how our Support Engineers set up remote MySQL access and fix common MySQL access problems in Linode server.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF