Summary of the mysql backup method by using crontab in ubuntu

To regularly back up mysql in ubuntu, you must first write a shell script to back up the mysql database, and then use crontab to regularly execute the shell script to back up the mysql database.

1. The shell script for backing up mysql is as follows:

The code is as follows:	Copy code
#! /Bin/bash Date_str = $ (date + % Y % m % d-% T) Cd/home/steven/backup Mysqldump-h localhost-u root -- password = xxxx-R-E-e -- Max_allowed_packet = 1048576 -- net_buffer_length = 16384 databaseName | Gzip>/home/steven/backup/juziku_1_date_str. SQL .gz

   
   
  
Store the above script at/home/steven/mysql_backup.sh (of course, you can also store it elsewhere)

Run the following command to grant the execution permission:

The code is as follows:	Copy code
Sudo chmod + x/home/steven/mysql_backup.sh

After completing this step, we will execute this script to see if we can back up the mysql database.
Enter

The code is as follows:	Copy code
./Mysql_backup.sh

You can see the backed up database files.

2. After completing this step, you can back up the mysql database. Next, we will execute this script regularly through crontab.
Use the crontab-e command, which is relatively simple to use.
In the command line input, enter crontab-e directly.
An editing window is opened, and the last line will display a message in the content format:

The code is as follows:	Copy code
# M h dom mon dow command

Specific meaning: minute hour date month week command, in a month (mon) one day (dom) or the day of the week (dow) (h, 24 hours) A Minute (m) executes a command, * indicates any time. For example, 0 3 ***/home/steven/mysql_backup.sh means to execute the mysql_backup.sh script three o'clock every morning.

We just need to add a line in it, the content is as follows:

The code is as follows:	Copy code
# Back up the mysql database at three o'clock every day 0 3 ***/home/steven/mysql_backup.sh

In this way, the mysql database will be automatically backed up at every morning.

Another instance crontab

The system crontab is used to regularly execute backup files and save the backup results by date to achieve the purpose of backup.

1. Create the path to save the backup file/mysqldata

The code is as follows:	Copy code
# Mkdir/mysqldata

2. Create the/usr/sbin/bakmysql file.

The code is as follows:	Copy code
# Vi/usr/sbin/bakmysql input Rq = 'date + % Y % m % d' Tar zcvf/mysqldata/mysql1_rq.tar.gz/var/lib/mysql

Or write it

 

The code is as follows:	Copy code
Rq = 'date + % Y % m % d' Mysqldump -- all-databases-u root-p980405>/mysqldata/mysql1_rq.tar.gz /Var/lib/mysql

Is the directory of your database files. Some users are/usr/local/mysql/data.

/Mysqldata/indicates the directory where the backup file is saved. This can be done according to your requirements.

3. Modify file attributes to make them executable.

The code is as follows:	Copy code
# Chmod + x/usr/sbin/bakmysql

4. Modify/etc/crontab

# Add vi/etc/crontab below

 

The code is as follows:	Copy code
01 3 *** root/usr/sbin/bakmysql indicates that the backup is performed at three o'clock every day.

5. Restart crond.

The code is as follows:	Copy code
#/Etc/rc. d/init. d/crond restart

Complete.

In this way, you can see such files in/mysqldata every day.

Mysql20040619.tar.gz you can download it directly.

Stop the database service process or lock the database before running the tar command,

Otherwise, the database may be damaged when the data is recovered. If you are lucky, you can fix the problem.