I've had good success with MySQL replication and nightly tarballs. For smaller db, the mysql database, and schema I use a combination of scripts designed to use mysqlhotcopy and mysqldump.
InnoDB hot backup is a great commercial product but I'm not sure how it handles mixed tables in the same database. pQd's recommendation for XtraBackup may be good to compare against this.
Others like LVM snapshots and I'd say that's definitely something to consider. Ultimately, a combination of solutions would probably be best.
It's also notable this is an old topic. Between the High Performance MySQL book, the MySQL manual, and previous ServerFault questions-- this has been exhausted on a general basis. See:
Automation can be done by creating CRON jobs on your server. The following link has complete example code to backup the database automatically
http://atoztechnical.blogspot.com/2010/08/how-to-automate-database-backups.html
or
First write a script that uses mysqldump command to backup your database and store in specific directory.Now we are going to automate the process by executing this script at a particular time everyday.
Execute the following command as root user
[root@vasu /]# crontab -e
This allows you to edit the crontab file for a user. It has six fields they are
1.Minute
2.Hour
3.Day of Month
4.Month
5.Day of Week
6.Command to be executed
Add the following line to the crontab file and close it.
[root@vasu /]# crontab -e
39 19 * * * backup.sh
~
~
~
~
~
ESC:x
crontab: installing new crontab
[root@vasu /]#
Now your backup.sh script will be executed 39 minutes, 19th hour everyday. If u want to see the list of cronjobs for a user, then execute
[root@vasu /]# crontab -l
39 19 * * * backup.sh
backup.sh :
mysqldump -h hostname -u userbane -ppassword dbname > path/to/directory/backupname.sql
Best Answer
As the others have noted - you will need to install some software on the MySQL machine to do these backups. Navicat is just a client - it can not run a scheduled backup on the server.
There is much software out there that can do this for you - some of them backup directly from the MySQL DB.
The free solution is to 'roll your own' database backup. This largely depends on your operating system.
Linux - create a shell script to do mysqldump on the db you wish to backup, then send this backup to your home PC via scp, ftp, or e-mail (if you do not have the ssh and ftp infrastructure at home).
Windows - create a scheduled task to backup the mysql db to a shared directory, or set up a command line script to backup the DB and email, ftp or scp it to your local pc.
If you have PHP installed on the server you can use the following directions for either OS:
http://www.sematopia.com/?p=61
There are many ways to skin this cat!