A practical method of Linux automatic backup compressing MySQL database

MySQL regular backup is an important task, but manual operation is too cumbersome, but also difficult to avoid omissions, use the following method to let the system regularly back up data.

1. Create a backup folder

#cd/www

#makedir Backup

2. Write Run script

#vi Autobackup

Write the following content:

Filename= ' Date +%y%m%d '

Mysql_bin_dir/mysqldump–opt Dataname-u User-ppassword | gzip >/www/mysqlbackup/name${filename}.sql.gz

Save exit

Description

(1) The bin path of the Mysql_bin_dir:mysql;

(2) Dataname: database name;

(3) User: Database username;

(4) Password: User password;

(5) Name: Custom backup file prefix identification.

As in the example above, the MySQL database will be automatically backed up and stored in gzip compression, in the form of a file named name20080101.gz.

3. Add Execute permissions for scripts

#chmod +x Autobackup

4, let crontab to complete the task of regular execution

In this step, the method of Redhat will be different, the latter is specifically given.

Edit crontab:

#vi/etc/crontab

In the last line, add:

5 * * * root/www/autobackup

Run the script at 5 o ' Day, or you can modify 5 for another specified time.

Redhat Method:

Redhat's crontab takes 4 directories per time (/etc/cron.hourly: hourly;/etc/cron.daily: daily;/etc/cron.weekly: Weekly;/etc/cron.monthly: Monthly) The way scripts come out of the running.

Just copy the script you just edited to the appropriate directory in the Redhat.

5. Restart Crontab

#/etc/rc.d/init.d/crond restart

Complete.

Mysqldump Command Little Kee-opt

The default mysqldump exported SQL file contains not only the exported data, but also the structure information of all the data tables in the exported database. –opt: This mysqldump command parameter is optional, if you bring this option to represent the Quick,add-drop-table,add-locks,extended-insert,lock-tables parameter that activates the mysqldump command, 　　That is, you do not need to append these parameters when you use mysqldump to export MySQL database information through the –opt parameter.　　–quick: The delegate ignores buffered output, and the mysqldump command exports the data directly to the specified SQL file. 　　–add-drop-table: As the name implies, add the drop-table IF exists statement before each create Tabel command to prevent data table names.　　–add-locks: Represents locking and unlocking a specific data table before and after the insert data, you can open the mysqldump exported SQL file, and the lock tables and unlock tables statements appear before insert. –extended-insert (-E): This parameter indicates that multiple rows can be inserted.

A practical method of Linux automatic backup compressing MySQL database