Lets create 2 folders.

mkdir /usr/hostonnet/
mkdir /home/hon_mysql_backup

Create following file

vi /usr/hostonnet/mysql_backup_daily.sh

With following content.

#!/bin/bash
#title           : mysql_backup_daily.sh
#description     : Make daily backup of MySQL data directory.
#web             : http://blog.hostonnet.com/mysql-data-directory-backup-with-rsync

#/usr/bin/mysql -e "FLUSH TABLES WITH READ LOCK;"
rsync -vrplogDtH --delete /var/lib/mysql/ /home/hon_mysql_backup/daily/   2>&1 > /var/log/mysql_backup_daily.log
#/usr/bin/mysql -e "UNLOCK TABLES;"

Lets generate shell scripts for weekly and monthly mysql backup from daily backup file we created above.

sed -e "s/daily/weekly/g" /usr/hostonnet/mysql_backup_daily.sh > /usr/hostonnet/mysql_backup_weekly.sh
sed -e "s/daily/monthly/g" /usr/hostonnet/mysql_backup_daily.sh > /usr/hostonnet/mysql_backup_monthly.sh

Make them executable

chmod 755 /usr/hostonnet/mysql_backup_daily.sh
chmod 755 /usr/hostonnet/mysql_backup_weekly.sh
chmod 755 /usr/hostonnet/mysql_backup_monthly.sh

Set cronjob, so backup commands run daily, weekly and monthly.

@daily /usr/hostonnet/mysql_backup_daily.sh
@weekly /usr/hostonnet/mysql_backup_weekly.sh
@monthly /usr/hostonnet/mysql_backup_monthly.sh

Make sure crond is running on your server, if not refer

http://blog.hostonnet.com/centos-crontab-command-not-found

On Ubuntu, verify cronjob running with

root@cloud1:~# service cron status
cron start/running, process 907
root@cloud1:~# 

On CentOS/RHEL, it is

[root@server12 ~]# service crond status
crond (pid  15121) is running...
[root@server12 ~]#

Locking Tables

If you are copying MySQL data folder, there is a chance the backup can be corrupt (repair possible in most cases) as data is written to the data directory while you copying, so on large database, you may not able to copy before data file changes. One solution is to lock tables before you backup

mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;"

Unlock database after backup finished.

mysql -u root -p"password" -e "UNLOCK TABLES;"