Tuesday, October 12, 2010

Mysql backup on linux using ssh and cron

Sometimes, we may need to backup and synchronize mysql on linux as a background process. Linux has a great program for this called cron. It allows tasks to be automatically run in the background at regular intervals.

Create a script file(.sh) using following code snippet to backup and restore database in to another database:
ssh user@remote-host "mysqldump -u my-remote-db-username --password=my-remote-db-password my-remote-db-name" | mysql -udb-username --password=db-password --host=host-name -C db-name


Open a terminal and run:
sudo crontab -l

To edit the list of cronjobs you can run following code which will open default editor to let us the crontab:
sudo crontab -e

You can configure your script in to crontab by following code:
* * * * * /bin/execute/this/script.sh


The above five stars represent different date parts in the following order:
  1. minute (from 0 to 59)
  2. hour (from 0 to 23)
  3. day of month (from 1 to 31)
  4. month (from 1 to 12)
  5. day of week (from 0 to 6) (0=Sunday)

No comments:

Post a Comment