1. Home
  2. Knowledge Base
  3. Pre-migration Preparation
  4. How to enable remote access to MySQL Database server

How to enable remote access to MySQL Database server

By default, the remote access to a MySQL database server is restricted for security purposes. However, when migrating to a help desk that works on a MySQL database server you need to enable remote access for the time of migration.

Follow these steps to enable remote access to your database server:

Step 1: Login Using SSH (if server is outside your data center)

ssh user@server1.yourdomain.com
### login as the root using su or sudo ##
su
# or use sudo ##
sudo -i

OR directly log in as root user if allowed:

ssh root@server1.yourdomain.com

Step 2: Edit the my.cnf file

After connecting, edit the MySQL server configuration file my.cnf using vi a text editor:

  • If you are using Debian/Ubuntu Linux file is located at /etc/mysql/my.cnf location.
  • If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location.
  • If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf location.

To edit the /etc/my.cnf, run:

# vi /etc/my.cnf

Step 3: Locate the line that reads the following

[mysqld]


Make sure line skip-networking is commented (or remove line) and add the following line:

bind-address=YOUR-SERVER-IP


For instance, if your MySQL server IP is 75.44.44.1 then entire block should look like this:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 75.44.44.1
# skip-networking
....
..
....

Where,

  • bind-address: IP address to bind to.
  • skip-networking: Do not listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

Step 4: Save and Close the file

To restart the MySQL server, type the following command:

  • Debian / Ubuntu Linux, type: # /etc/init.d/mysql restart OR # systemctl restart mysql
  • RHEL / CentOS / Fedora / Scientific Linux, type: # /etc/init.d/mysqld restart OR # systemctl restart mysqld
  • FreeBSD: # /usr/local/etc/rc.d/mysql-server restart OR # service mysql-server restart

Step 5: Grant access to a remote IP address

Connect to MySQL server:

$ mysql -u root -p mysql

Granting access to a new database

Let's say you want to grant access to a database called helpdeskmigration to our IP address 188.40.249.30 then you need to type the following commands at mysql> prompt:

mysql> CREATE DATABASE helpdeskmigration;
mysql> GRANT ALL ON helpdeskmigration.* TO bar@'188.40.249.30' IDENTIFIED BY 'PASSWORD';

Granting access to an existing database

If you created a new user for the purpose of migration, type the following command at mysql> prompt:

mysql> update db set Host='188.40.249.30' where Db='databasename';
mysql> update user set Host='188.40.249.30' where user='username';

Where,

  • databasename is the name of the database you want to grant access to;
  • username is the name of the user which has the appropriate permissions.

Step 6: Logout of MySQL

Type the mysql> exit command to logout of MySQL.

Step 7: Open port 3306

Open TCP port 3306 using iptables or BSD pf firewall.

Sample IP tables to open Linux iptables firewall:

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

Finally, save all rules (RHEL / CentOS specific command):

# service iptables save

Sample FREEBSD / OPENBSD / NETBSD PF FIREWALL rules (/ETC/PF.CONF)

Use the following rule to open port # 3306 on BSD based systems:

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306 flags S/SA synproxy state

Final step: Test the connection

Go to Migration Wizard and try connecting your database. If successfully connected, the Wizard will forward you to the next step. Continue setting up your migration.

If you bump into an issue, reach out to our support team with a screenshot of the message you've receive from the Wizard.

Did this answer your question?
Book a Call
cancel
%s
— %s