MySQL Sub-Library backup

1. Requirements overview

• Back up MySQL database data 00:00 every day;
• Each library generates a file, using gzip compression, file name: Backup_ Library Name _yyyymmdd. sql.gz, note that YYYYMMDD need to be the day before;
• Save the backup file under/backup/mysql/, create a new daily backup data directory YYYYMMDD, note the day before
• The log is saved under/backup/mysql/and is named:yyyymmdd. Log

2. Environment Preparation

Operating system: CentOS release 6.5
MySQL data: MySQL 5.1.73

3. Script description

• Determine if the backup directory exists and create it if it does not exist;
• Use the MySQL command to get a list of all databases;
• Traverse all data lists and use the mysqldump command to back up

4. Script writing

1#!/bin/Bash2 3#log file name4log=/Backup/mysql/$ (Date+%y%m%d-d -1day).Log5 6# CreateBackupDirectory7backup_dir=/Backup/mysql/$ (Date+%y%m%d-d -1day)8 9 if[!-D "${backup_dir}"]; ThenTen         Echo"Create backup Directory ${backup_dir}" >> $LOG One         mkdir${Backup_dir} A fi -  -  the#BackupDB data -  forI in ' mysql-uroot-p123456-e ' show databases;` -  Do -  +         if["$i"! = "Database"]; Then -                 Echo"Start Backup $i" >> $LOG +Mysqldump-uroot-p123456-b "$i"--flush-privileges--single-transaction--master-data=2--flush-logs--triggers-- Routines--events--hex-blob | gzip > ${backup_dir}/backup_ "$i" _$ (Date+%y%m%d-d -1day). sql.GZ A                 Echo"End Backup $i" >> $LOG at fi -  -  Done -  - Echo"End Backup" >> $LOG

5. Scheduled Tasks

1 [[email protected] mysql]# crontab-e200 * * */bin/sh /root/ Scripts/db_backup. sh >/dev/null2>&1

The output needs to be redirected to an empty

6. Key Commands

The day before YYYYMMDD

Date +%y%m%d-d -1day

Gzip

Cat Install gzip install. log.gz

MySQL command queries all libraries

" show databases; "

mysqldump command to back up the database

Mysqldump-uroot-p123456-b test--flush-privileges--single-transaction--master-data=2gzip >/backup/mysql/20180515/backup_test_20180515.sql.gz

MySQL Sub-Library backup