Allow remote access to MySQL server from VPS

Today I have job for backup mysql to other server that specialize on mysql backup online. The job is easy but I think it worth to share for someone out there who want to do same thing ūüôā

I’ll use my vps on DigitalOcean and MySQL backup service from mysqlbackuponline.

DigitalOcean

On DO side the steps are :

1. Create database

2. Grant “select” access to the database you want to backup (don’t forget to flush privileges). ex :

grant select on dbname.* to [email protected]’126.220.63.%’ identified by ‘password’;

3. Open firewall access to mysql port, like this :

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

Check if the rule is exist :

# iptables -L -n

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT¬†¬†¬†¬† tcp¬† —¬† 0.0.0.0/0¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 0.0.0.0/0¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† tcp dpt:3306

4. Edit /etc/mysql/my.cnf or /etc/my.cnf (position depend on your server operating system)

Make sure “bind-address” section is right. For me comment the line is enough.

5. Restart MySQL server

Mysqlbackuponline

On Mysqlbackuponline side the steps are :

1. Login to mysqlbackuponline

2. Click on + Add New MySQL server

3. Enter all requested information

4. Test Connection

Make sure connection work well “Test connection succeeded”.

backup_db

Test run the job :

11:27:43 AM
Started by a user
11:27:43 AM
Environment version 1.0.0.199
11:27:45 AM
Starting MySql backup…
11:27:45 AM
Using mode (MySQLTcpIpServer)
11:27:45 AM
Creating a backup of the database: backupdb
11:27:47 AM
Server version : 5.0.95
11:30:55 AM
Archived in 0.02 sec
11:30:55 AM
Archive size 66794 bytes
11:30:55 AM
The backup was saved successfully
11:30:55 AM
Backup state was saved (0.02 sec)
11:30:55 AM
Sending email…
11:30:56 AM
Email have been sent to [email protected]
11:30:56 AM
Job finished (Success)

Great service from mysqlbackuponline. Time to schedule it for daily backup.