Automatically backing up mysql databases

[Hikaru12]

I have four databases that I'm using for several corporate web applications. One of them is a form application in which we're using to try to convert to paperless forms. The integrity of these forms is important so backup is paramount.

 

Is there any way to backup MYSQL databases to backup automatically on the web host and also on a local network drive that gets backed up daily? I've thought about using a combination of Cron with the mysqldump functionality and a program that automatically FTP's to the site and pulls the file down daily to the backup server. 

 

My question is does the mysqldump functionality include the "If Exists" table creation feature to ensure tables are being correctly recreated. Do I need to have a database with the same name as the backed up one to restore it or does it automatically create a new one? 

 

Also does cron automatically find scripts found in the /etc/cron.daily or cron.weekly folders to run? Do I have to specify the location of the script in the cron commands? Thanks.

[Opcode]

mysqldump does in fact automatically add checks to ensure tables exist beforehand (or at least drops them before recreating them).

 

I would just add a cron job to the operating system.

15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /var/www/database/database_`data ' %m-%d-%Y'`.sql.gz15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /home/user/database_`data ' %m-%d-%Y'`.sql.gz

You will need to create the database initially before you can execute the dump upon it as mysqldump only dumps the tables and data.

[SSL]

For security you should create a separate system user AND database user. The database user should have minimal permissions, while the system user should not have login shell access.

 

Use ssh, rsync, or scp to get your files to or from a remote server. The chron job can run a shell script that handles the necessary actions.

[Hikaru12]

Thanks for all the help. You guys have been very helpful.