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)
### login as the root using su or sudo ##
# or use sudo ##
OR directly log in as root user if allowed:
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
Make sure line skip-networking is commented (or remove line) and add the following line:
For instance, if your MySQL server IP is 18.104.22.168 then entire block should look like this:
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 = 22.214.171.124
- 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 126.96.36.199 then you need to type the following commands at mysql> prompt:
mysql> CREATE DATABASE helpdeskmigration;
mysql> GRANT ALL ON helpdeskmigration.* TO bar@'188.8.131.52' 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='184.108.40.206' where Db='databasename';
mysql> update user set Host='220.127.116.11' where user='username';
- 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.