Mysql Generic Backup Script
Cat Mysql_backup.sql
#!/bin/sh. /etc/profilecd /home/mysql/backupexe=/usr/local/mysql/bin/mysqldumpmysql_bin=/usr/local/ mysql/bin/mysqluser=rootpassword=xxxxxsuffix= ' date + '%y%m%d%h%m ' bakdir=/home/mysql/backupremotedir=/home /mysql/backupremotehost= ' xxx.xxx.xx.xx ' Copy=5bakdata () {$exe -u$user -p$password -- single-transaction -r --default-character-set=utf8 --triggers --events -- Hex-blob --databases xxx1 > $bakdir/xxx1_$suffix.sql$exe -u$user -p$password --single-transaction -r --default-character-set=utf8 -- triggers --events --hex-blob --databases xxx2 > $bakdir/xxx2_$suffix.sql$exe -u$user -p$password --single-transaction -r --default-character-set=utf8 --triggers --events --hex-blob -- flush-logs --master-data=2 &Nbsp; -a > $bakdir/all_$suffix.sql}remotebak () {scp $bakdir/xxx2_$suffix.sql $ RemoteHost: $REMOTEDIRSCP $bakdir/xxx2_$suffix.sql $remotehost: $remotedirscp $bakdir/all_$ suffix.sql $remotehost: $remotedirscp $bakdir/privileges_backup_$suffix.sql $remotehost: $ Remotedir}clearbak () {ssh $remotehost "find $remotedir/*.sql -mtime + $copy -exec rm {} \; " find $bakdir/*.sql -mtime + $copy -exec rm {} \;} Optimize_tab () {mysql -u$user -p$password xxx1 -e "show tables " | sed ' 1d ' | awk ' {print ' optimize table "$"; \nanalyze table "$"; "}" | mysql -u$user -p$password xxx1;mysql -u$user -p$password xxx2 -e "Show tables " | sed 1d ' | awk ' {print " optimize table "$" ; \nanalyze table " $ ";"} ' | MYSQL -U$USER -P$PASSWORD XXX2;} Do_backup_grant_priv () { split=\ ' special= ' $mysql _bin -u$ user -p$password --default-character-set=utf8 -bse "Select user, host from mysql.user; " | grep "$split" |wc -l if [ $special - Eq 0 ];then split=\ ' else special= ' $mysql _bin -u$user -p$password -- default-character-set=utf8 -bse "select user, host from mysql.user;" | grep -E "'" |wc -l ' if [ $special -eq 0 ];then split=\ ' \ ' else echo "Error:the username have special char " exit fi fi for SQLUSER in ' $mysql _bin -u$user -p$password --default-character-set=utf8 -Bse "Select concat (' $split ', user, ' $split ', ' @ ', ', host, ') from mysql.user; " ' do $mysql _bin -u$user -p$password --default-character-set=utf8 -bse " show grants for $SQLUSER; " | sed ' s/$/;/g ' >> $bakdir/privileges_backup_$suffix.sql done }echo "----------------------------------------" date + ' ===data backup begin:%y%m%d %t===== ' bakdatadate + ' ===data backup end:%y%m%d %t===== ' date + ' ===pri backup begin:%y%m%d %t===== ' do_backup_grant_privdate + ' ===PRI BACKUP end:%y%m%d %t===== ' date + ' ===scp begin:%y%m%d %t===== ' remotebakdate + ' ===SCP end:%y%m%d %t===== ' date + ' ===cls begin:%y%m%d %t===== ' clearbakdate + ' ===CLS end:%y%m%d %t===== ' date + ' ===opt begin:%y%m%d %t===== ' flag= ' date + "%w" ' if [ $flag -eq 6 ] ; thenoptimize_tabfidate + ' ===opt end:%y%m%d %t== = = = ' echo -e ' \ n ' echo -e ' \ n '
This article from "Small Fish Blog" blog, declined reprint!
Mysql Generic Backup Script