分享一个MySQL的备份脚本(大千之中给多你一个选择)

 CREATE TABLE `backupinfo` (
`idback` int(10) unsigned NOT NULL AUTO_INCREMENT,
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`end_time` datetime DEFAULT NULL,
`size` bigint(20) unsigned DEFAULT NULL,
`type` enum('TEXT','RAW') DEFAULT NULL,
PRIMARY KEY (`idback`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

1 row in set (0.00 sec)

创建一个这样表结构的表用来记录备份的时间的起止时间,以及备份的type,备份文件的大小等等相关的备份信息.

#!/bin/bash

bkDate=`date +%d%m%Y` #记录备份时间

bkFolder="/usr/local/mysql/$bkDate/" #选择你备份的存放点,注意你要有在这个目录下写的权限.

mkdir $bkFolder #C创建备份目录

cp /etc/my.cnf $bkFolder #备份你的配置文件

#Flushing the logs and capturing the backup start time.
#Capturing backup time is just for information.
#Since this is a textual backup i am storing the backup type as text.

#ToDO: Need to copy the relavent binary logs.

mysql -p -u root -e "FLUSH LOGS;INSERT INTO mysql.backupinfo ( type ) VALUES ( 'TEXT' );" # -u你的备份用户名,-p你的密码

#Taking backup of all databases other that mysql and information_schema.

for db in `mysql --skip-column-names -u backup -pBackUp@123 -s -e "SELECT schema_name FROM information_schema.SCHEMATA WHERE schema_name NOT IN ( 'mysql','information_schema')"`

do
mkdir $bkFolder$db; #Creating a new directory for each database.
mysqldump -d --triggers -u backup -R -E -pBackUp@123 -q --databases $db | gzip > $bkFolder$db/$db.sql.gz #I am using gzip to compress the backup.

done

#Finally I am storing the end time and size of the backup.

mysql -u backup -pBackUp@123 -e "UPDATE mysql.backupinfo SET end_time = NOW(), size = TRIM(REPLACE(REPLACE('`du -s`',' ',''),'.','')) WHERE type = 'TEXT' AND DATE(start_time) = DATE(NOW())" 

备份的脚本有很多,只是给你再众多之中给你一份更多的选择而已!