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”.
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.