149 lines
4.1 KiB
Bash
Executable File
149 lines
4.1 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
|
|
## - *** some defines *** - ##
|
|
|
|
## - Backup direction
|
|
## -
|
|
backup_base_dir=$1
|
|
backup_base_dir=${backup_base_dir:="/data/backup"}
|
|
|
|
backup_dir=${backup_base_dir}/mysql
|
|
|
|
mkdir -p $backup_dir
|
|
|
|
|
|
_local=$2
|
|
_local=${_local:=false}
|
|
|
|
mkdir -p $backup_dir
|
|
|
|
## - Database connection data
|
|
## -
|
|
db_user=backup
|
|
db_pass=Rgfwcsgth6Zjk4W
|
|
db_host=localhost
|
|
db_port=3306
|
|
|
|
## - Some needed binaries
|
|
## -
|
|
mysql_bin=`which mysql`
|
|
mysqldump_bin=`which mysqldump`
|
|
mkdir_bin=`which mkdir`
|
|
date_bin=`which date`
|
|
gzip_bin=`which gzip`
|
|
find_bin=`which find`
|
|
|
|
if [ -z "$mysql" ]; then
|
|
mysql="/usr/local/mysql/bin/mysql"
|
|
fi
|
|
|
|
if [ -z "$mysqldump" ]; then
|
|
mysqldump="/usr/local/mysql/bin/mysqldump"
|
|
fi
|
|
|
|
|
|
|
|
YEAR=`date +"%Y"`
|
|
MONTH=`date +"%m"`
|
|
DAY=`date +"%d"`
|
|
HOUR=`date +"%H"`
|
|
MINUTE=`date +"%M"`
|
|
DATE=${YEAR}${MONTH}${DAY}-${HOUR}${MINUTE}
|
|
|
|
## - Hold backups at least $days days
|
|
## -
|
|
days=3
|
|
|
|
|
|
## - ** End: some defines *** - ##
|
|
|
|
|
|
|
|
DATABASES=`$mysql_bin -h$db_host -P$db_port -u$db_user -p$db_pass -N -s -e 'show databases'`
|
|
|
|
## - backup all databases
|
|
## -
|
|
for i in $DATABASES ; do
|
|
|
|
$mkdir_bin -p ${backup_dir}/${i}
|
|
|
|
mysqldump_flags="--opt --routines -l"
|
|
if [ "$i" = "information_schema" -o "$i" = "performance_schema" ];then
|
|
mysqldump_flags="--single-transaction $mysqldump_flags"
|
|
fi
|
|
|
|
echo -e "\tsave database $i ..."
|
|
$mysqldump_bin $mysqldump_flags -h$db_host -P$db_port -u$db_user -p$db_pass $i | \
|
|
$gzip_bin | cat > ${backup_dir}/${i}/${i}-${DATE}.sql.gz
|
|
|
|
## - dumptables "user" and "db" from database mysql"
|
|
## -
|
|
if [ "$i" = "mysql" ]; then
|
|
_flags="--add-locks --disable-keys --extended-insert --lock-tables --quick --no-create-info"
|
|
|
|
_table=user
|
|
## - dump table user (database mysql)
|
|
## - each row as aseperate sql statement
|
|
## -
|
|
## - | awk '{gsub(/\),\(/,");\n(")}; 1'
|
|
## - | sed 's/^(/INSERT INTO `user` VALUES (/'
|
|
## -
|
|
## - delete row containig host = "localhost" with empty user and password
|
|
## - sed "/^INSERT INTO \`user\` VALUES ('[^']*','root',.\+/d"
|
|
## -
|
|
## - delete rows containig user "root"
|
|
## - sed "/^INSERT INTO \`user\` VALUES ('[^']*','root',.\+/d"
|
|
## -
|
|
## - delete rows containig user "debian-sys-maint"
|
|
## - sed "/^INSERT INTO \`user\` VALUES ('[^']*','debian-sys-maint',.\+/d"
|
|
## -
|
|
$mysqldump_bin $_flags -h$db_host -P$db_port -u$db_user -p$db_pass $i $_table \
|
|
| awk '{gsub(/\),\(/,");\n(")}; 1' \
|
|
| sed "s/^(/INSERT INTO \`$_table\` VALUES (/" \
|
|
| sed "/^INSERT INTO \`$_table\` VALUES ('localhost','','','.*/d" \
|
|
| sed "/^INSERT INTO \`$_table\` VALUES ('[^']*','root','.*/d" \
|
|
| sed "/^INSERT INTO \`$_table\` VALUES ('[^']*','debian-sys-maint','.*/d" \
|
|
> ${backup_dir}/${i}/${i}-${_table}-${DATE}.sql
|
|
|
|
_table=db
|
|
## - dump table db (database mysql)
|
|
## - each row as aseperate sql statement
|
|
## -
|
|
## - | awk '{gsub(/\),\(/,");\n(")}; 1'
|
|
## - | sed 's/^(/INSERT INTO `user` VALUES (/'
|
|
## -
|
|
## - delete rows for database names "test*"
|
|
## - sed "/^INSERT INTO \`$_table\` VALUES ('[^']*','test[^']*','.*/d"
|
|
## -
|
|
$mysqldump_bin $_flags -h$db_host -P$db_port -u$db_user -p$db_pass $i $_table \
|
|
| awk '{gsub(/\),\(/,");\n(")}; 1' \
|
|
| sed "s/^(/INSERT INTO \`$_table\` VALUES (/" \
|
|
| sed "/^INSERT INTO \`$_table\` VALUES ('[^']*','test[^']*','.*/d" \
|
|
> ${backup_dir}/${i}/${i}-${_table}-${DATE}.sql
|
|
|
|
## - BSD sed is not GNU sed , so we used "awk" for substitution
|
|
## - a newline. On GNU sed (like on linux systems), also the
|
|
## - following sed statement works
|
|
## -
|
|
#| sed 's/),(/);\n(/g' \
|
|
fi
|
|
|
|
done
|
|
|
|
## - Create archive from the whole backup
|
|
## -
|
|
if ! $_local ; then
|
|
_curdir=`pwd`
|
|
cd $backup_dir
|
|
cd .. > /dev/null 2>&1
|
|
tar -czf mysql_dump-${DATE}.tar.gz `find mysql -name "*${DATE}*"`
|
|
cd $_curdir
|
|
fi
|
|
|
|
|
|
## - remove all files older than $days days..
|
|
## -
|
|
$find_bin $backup_dir -type f -mtime +${days} -exec rm {} \;
|
|
|
|
exit 0
|