In linxu, two mysql scripts are automatically backed up every day.

Mysql automatic backup is based on the crontab Task Plan in linux. After writing the. sh file, we need to add it to crontab in linux and set the backup database execution time. I will introduce it below.

Scheduled execution script:

Run

The Code is as follows:	Copy code
Crontab-e 00 00 ***/bin/bash yourpath/mysqlbak. sh

Open the automatic execution File

 

The Code is as follows:	Copy code
Vi/etc/crontab

Add the following content to etc to automatically execute the task.

The Code is as follows:	Copy code
00 00 *** root/mysqlbak. sh

The above two 00 *** scripts are automatically executed every morning.

Hour, day, month, and week commands

M: minute (0-59 ). Each minute is represented by * or */1.
H: hour (0-23 ). (0 indicates 0)
D: Day (1-31 ).
M: Month (1-12 ).
D: days in a week (0 ~ 6, 0 is Sunday ).

Run */5 * every five minutes ****
0 *** per hour ****
Execute 0 0 every day ***
Execute 0 0 every week ** 0
0 0 1 per month **
Execute 0 0 1 1 * every year *

Restart cron

The Code is as follows:	Copy code
/Etc/rc. d/init. d/crond restart Or Service crond restart

Instance 1

Mysqlback. sh

 

The Code is as follows:

Copy code

#! /Bin/bash # Function Description: This function is used to back up a database. # Prepared on: 2010/12/06 PATH =/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin Export PATH # Database username Dbuser = 'root' # Database Password Dbpasswd = '000000' # Database name. You can define multiple databases separated by spaces, for example, test test1 test2. Dbname = 'test1 test2' # Backup time Backtime = 'date + % Y % m % d % H % M % s' # Log backup path Logpath = '/second/backup' # Data backup path Datapath = '/second/backup' # Log record header Echo '"backup time is $ {backtime}, backup database table $ {dbname} starts" >$ {logpath}/mysqllog. log # Formally back up the database For table in $ dbname; do Source = 'mysqldump-u $ {dbuser}-p $ {dbpasswd }$ {table }>$ {logpath}/$ {backtime }. SQL '2 >>$ {logpath}/mysqllog. log; # Successful backup: If ["$? "= 0]; then Cd $ datapath # Compressing the database to save hard disk space Tar jcf zookeeper table1_zookeeper backtime=.tar.bz2 $ {backtime}. SQL>/dev/null # Delete the original file, leaving only the compressed file Rm-f $ {datapath}/$ {backtime}. SQL Echo "database table $ {dbname} is backed up successfully !! ">>$ {Logpath}/mysqllog. log Else # If the backup fails, perform the following operations: Echo "database table $ {dbname} backup failed !! ">>$ {Logpath}/mysqllog. log Fi Done

Instance 2

Run the script once every morning. The script is automatically backed up once every day, and only the backups of the last five days are retained on the server.

The Code is as follows:

Copy code

#! /Bin/bash # This is a ShellScript For Auto DB Backup # Powered by aspbiz #2004-09 # Setting # Set the database name, database login name, password, backup path, Log Path, and data file location, # And backup methods # By default, the backup method is tar, mysqldump, or mysqldotcopy. # By default, log on to the mysql database with root (empty) and back up to/root/dbxxxxx. tgz DBName = mysql DBUser = root DBPasswd = BackupPath =/root/ LogFile =/root/db. log DBPath =/var/lib/mysql/ # BackupMethod = mysqldump # BackupMethod = mysqlhotcopy # BackupMethod = tar # Setting End NewFile = "$ BackupPath" db $ (date + % y % m % d). tgz DumpFile = "$ BackupPath" db $ (date + % y % m % d) OldFile = "$ BackupPath" db $ (date + % y % m % d -- date = '5 days ago '). tgz Echo "-----------------------------------------"> $ LogFile Echo $ (date + "% y-% m-% d % H: % M: % S") >>$ LogFile Echo "--------------------------"> $ LogFile # Delete Old File If [-f $ OldFile] Then Rm-f $ OldFile >>$ LogFile 2> & 1 Echo "[$ OldFile] Delete Old File Success! ">>> $ LogFile Else Echo "[$ OldFile] No Old Backup File! ">>> $ LogFile Fi If [-f $ NewFile] Then Echo "[$ NewFile] The Backup File is exists, Can't Backup! ">>> $ LogFile Else Case $ BackupMethod in Mysqldump) If [-z $ DBPasswd] Then Mysqldump-u $ DBUser -- opt $ DBName> $ DumpFile Else Mysqldump-u $ DBUser-p $ DBPasswd -- opt $ DBName> $ DumpFile Fi Tar czvf $ NewFile $ DumpFile >>$ LogFile 2> & 1 Echo "[$ NewFile] Backup Success! ">>> $ LogFile Rm-rf $ DumpFile ;; Mysqlhotcopy) Rm-rf $ DumpFile Mkdir $ DumpFile If [-z $ DBPasswd] Then Mysqlhotcopy-u $ DBUser $ DBName $ DumpFile >>$ LogFile 2> & 1 Else Mysqlhotcopy-u $ DBUser-p $ DBPasswd $ DBName $ DumpFile >>$ LogFile 2> & 1 Fi Tar czvf $ NewFile $ DumpFile >>$ LogFile 2> & 1 Echo "[$ NewFile] Backup Success! ">>> $ LogFile Rm-rf $ DumpFile ;; *) /Etc/init. d/mysqld stop>/dev/null 2> & 1 Tar czvf $ NewFile $ DBPath $ DBName >>$ LogFile 2> & 1 /Etc/init. d/mysqld start>/dev/null 2> & 1 Echo "[$ NewFile] Backup Success! ">>> $ LogFile ;; Esac Fi Echo "-----------------------------------------"> $ LogFile