How to setup automatic MySQL backup on Ubuntu/Debian and CentOS

目录
[隐藏]

Overview

This tutorial is to guide you on setting up a simple, automatic MySQL database backups using mysqldump and a cron job.

Prerequisites

  1. Installed and running MySQL server
  2. SSH access
  3. root or sudo user

Step 1: Backing up a MySQL database

You can use the ‘mysqldump’ that comes in any standard MySQL installation to do a database dump from the shell. To backup the ‘exampledb’ database to a file do:

mysqldump -uroot -p exampledb > exampledb.sql

Note that there is no space between the ‘-u’ switch and the user (root). With the ‘-p’ switch and paramater after it the ‘mysqldump’ will prompt you to enter a password.

This command will output plain SQL queries in the exampledb.sql, but as they are text they can be compressed really good to save space. So to make a compressed backup, let’s do:

mysqldump -uroot -p exampledb | gzip > exampledb.sql.gz

You can use these commands to make manual backups of your databases. If you want to backup all databases on a server, use the ‘–all-databases’ switch instead of database name, like this:

mysqldump -uroot -p –all-databases| gzip > mysql_server.sql.gz

Step 2: Setting an automated scheduled backup of a MySQL database

Now that you know how to make manual database backups, let’s automate them with a cron job. You must set up a cron job that calls the ‘mysqldump’ tool on a schedule.

First let’s make a directory for storing the backups:

mkdir /var/backups/mysql

As backups are more usefull when they provide history, let’s give the backup file with more meaningful name by adding the date and time when the backup was taken:

mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”%Y_%m_%d_%I_%M”).sql.gz

To automate the backup process ‘mysqldump’ must have the user password inline, so it’s highly recommended to set up an additional user specifically for backups.

Open a crontab editor:

crontab -e

and add the following line at the bottom:

0 1 * * *    mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”\%Y_\%m_\%d_\%I_\%M”).sql.gz

The “0 1 * * *” stands for a schedule on every 01:00 or everyday at one after midnight. The cron line format is “m h dom mon dow command”, where:

  1. m – is for minutes
  2. h – is for hour
  3. dom – is the day of the month
  4. mon – is the month
  5. dow – day of the week

The ‘*’ symbol in any of these means “on every” minute, hour, day and so.

Saving the crontab will set your backup on schedule and you are good to go.

留下评论

电子邮件地址不会被公开。 必填项已用*标注