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

Source: Internet
Author: User
Tags chmod gz file mkdir

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 &GT;/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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.