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 username@’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.