Linux under MySQL how to automatically back up shell script _linux shell

Programs on the Linux server update the MySQL database every day, so remember to write a shell script, combined with crontab, to back up the database regularly. Actually very simple, the main is to use the mysqldump command with MySQL.

 #!/bin/bash # Shell script to backup MYSQL database # to backup nysql databases file To/backup dir and later pick up By your # script. 
Can skip few databases from backup too. # for more info (Installation info): # http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/ mysql-backup-script.html # last updated:aug-2005 #---------------------------------------------------------------- ----# This are a free shell script under GNU GPL version 2.0 or above # Copyright (C), Nixcraft project # Fee dback/comment/suggestions:http://cyberciti.biz/fb/#----------------------------------------------------------- --------------# This script are part of Nixcraft shell script Collection (NSSC) # Visit http://bash.cyberciti.biz/for m 
Ore information. #-------------------------------------------------------------------------myuser= ' Set-mysql-user-name ' # USERNAME mypass= "Set-password" # PASSWORD myhost= "localhost" # Hostname # Linux Bin PATHS, change this if it can is autodetected via which command mysql= "$ (which MYSQL)" mysqldump= "$ (which mysqldump)" Chown= "$ (which CHOWN)" Chmod= "$ (which CHMOD)" gzip= "$ (which GZIP)" # Backup Dest directory, change this if you have SOM Eother location dest= "/backup" # Main directory where backup'll be stored mbd= ' $DEST/mysql ' # get hostname host= ' $ ( hostname) "# Get data in dd-mm-yyyy format now= ' $ (date +"%d-%m-%y ")" # File to store current backup File file= "" # St Ore List of Databases dbs= "" # Do not BACKUP these databases iggy= "TEST" [!-D $MBD] && mkdir-p $MBD | | 
: # only Root can access it! $CHOWN 0.0-r $DEST $CHMOD 0600 $DEST # Get all database List A dbs= "$ ($MYSQL-u $MyUSER-H $MyHOST-p$mypass-bse 
  ' Show databases ') ' for DB in $DBS do skipdb=-1 if [' $IGGY '!= ']; Then for I with $IGGY do ["$db" = "$i"] && skipdb=1 | | : Done fi if ["$skipdb" = "1"]; Then file= "$MBD/$db. $HOST. $NOW. Gz"
  # do all Inone job in pipe, # Connect to MySQL using mysqldump for select MySQL database # and pipe it out to GZ File in Backup dir:) $MYSQLDUMP-u $MyUSER-H $MyHOST-p$mypass $db | $GZIP-9 > $FILE fi done

After saving, add the above script to the crontab schedule. such as: 4:30 A.M. Daily Backup: 4 * * * */data/backup-db.sh

If you use mysql5.1, you may be prompted for mysqldump errors:

Mysqldump:couldn ' t execute ' show create TABLE ' general_log ': Table ' mysql.general_log ' doesn ' t exist mysqldump:cou


LDN ' t execute ' show create TABLE ' slow_log ': Table ' mysql.slow_log ' doesn ' t exist

The reason is that there are no show_log tables and General_log tables in the MySQL library, which need to be created manually:

CREATE TABLE IF not EXISTS general_log ( 
 event_time timestamp not NULL DEFAULT current_timestamp on UPDATE current_tim Estamp, 
 user_host mediumtext not NULL, 
 thread_id int (one) not NULL, 
 server_id Int (a) unsigned NOT NULL, 
 Command_type varchar () NOT NULL, 
 argument mediumtext not null 
) engine=csv DEFAULT Charset=utf8 comment= ' General log '; 
CREATE TABLE IF not EXISTS slow_log ( 
 start_time timestamp not NULL DEFAULT current_timestamp on UPDATE current_timest AMP, 
 user_host mediumtext not NULL, 
 query_time time isn't null, 
 lock_time time is not NULL, 
 rows_sent int (one) not NULL, 
 rows_examined int (one) not NULL, 
 db varchar is not NULL, 
 last_insert_id int (one) not null,< c17/>insert_id Int (one) not NULL, 
 server_id Int (a) unsigned not null, 
 sql_text mediumtext not null 
) Engin E=csv DEFAULT charset=utf8 comment= ' slow log ';

Method Two:

Attention:

dumpfile=db$ (date +%y%m%d) If set to this, make sure to put the script in the backup directory. Dumpfile= "$BackupPath" db$ (date +%y%m%d) if set to do so, there is a hint in the log that tar:removing leading '/' from member names is because the backup directory uses an absolute path.
However, this does not affect the data, can be based on their own habits. -------------------------------------------------------------------start #!/bin/bash #This is a shellscript for Auto DB Backup #Powered by aspbiz #2004 -09 #Setting #设置数据库名, database logins, passwords, backup paths, log paths, data file locations, and backup methods #默认情况下备份方式是tar, or mysqldump,
Mysqldotcopy #默认情况下, log in to the MySQL database with root (empty), 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 Backu  P 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 $N
Ewfile $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--------------------------------------- ------------------------------------------------------End

The above content is this article to introduce you to the Linux MySQL how to automatically back up the shell script, I hope you like.