Shulou(Shulou.com)06/01 Report--

Introduction-Qiu Yuetao

The company's new project is online, and a full network backup is established today. Now this is the mysql part of the record.

Backup method: complete + binlog

Backup location: local + remote location

Backup time: 0: 00 every day, cut binlog log

1. Environment introduction:

[root@jm1j-node1 scripts] # cat / etc/redhat-release CentOS release 6.8 (Final) [root@jm1j-node1 scripts] # mysql-Vmysql Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86 / 64) using EditLine wrapper

2. Standard script storage directory

Mkdir-p / server/scripts

3. Write the script as follows

#! / bin/bash#---# $Name: mysqlbackup.sh# $Version: v1.The $Author: qiuyuetao# $organization: https://blog.51cto.com/qiuyt# $Create Date: 2017-12-1$ Description: Mysql backup Script#---#source / etc/init.d/functionsDAY= `date +% Y-%m-%d\ -% Hmurf% M`Back _ DIR= "/ data/backup/mysql/dump" DBUSER= "root" # data user DBPASSWD= "* * Database password" BIN_PATH= "/ application/mysql/bin" # global variable You can use which to get LOG= "/ var/log/mysqlback.log" # backup log Cklog = "/ var/log/bkmd5-" $DAY ".log" # md5 encrypted log Post email # # Close all tables and refresh log # "$BIN_PATH" / mysql-u "$DBUSER"-p "$DBPASSWD"-e "flush logs" # "$BIN_PATH" / mysql-u "$DBUSER"-p "$DBPASSWD"-e "flush tables with read lock" echo "mysqldump start `date +% H\:% M\:% S` >" $LOG "# Each backup database to the target location # for i in `" $BIN_PATH "/ Mysql-u "$DBUSER"-p "$DBPASSWD"-e "show databases" | grep-vE "Database | information_schema | performance_schema" `do if [!-d "$BACK_DIR" / "$I"] Then mkdir-p "$BACK_DIR" / "$I" fi "$BIN_PATH" / mysqldump-u "$DBUSER"-p "$DBPASSWD"-- default-character-set=utf8-- events-- master-data=2-- lock-all-tables-f-- log-error= "$LOG"-B "$I" | gzip > "$BACK_DIR" / "$I" / "$DAY" .sql.gzmd5sum "$BACK_DIR" / "$I" / "$(date +%) F-- date='0 days ago') "- * .sql.gz > > $CKLOG doneecho" mysqldump stop `date +% F\% H\:% M\:% S` ">" $LOG "echo" > > "$LOG" # # Delete old 10day backup files retains data for the last 10 days # "$BIN_PATH" / mysql-u "$DBUSER"-p "$DBPASSWD"-e "show databases" | grep-vE "Database | information_schema | performance_schema" `dorm-rf "$BACK_DIR" / "$RM" / $(date +% F-- date='10 days ago') * .sql.gz doneecho production database backup integrity MD5 initial value | mail-s backup check-a "$CKLOG" qiuyt@*.com # # your own mailbox

4, add execution permission

[root@jm1j-node1 scripts] # chmod + x mysqlbackup.sh `

5, test manually on the command line

View backup results

# if no mail is sent, please check whether the service is installed, started, port 25 enabled, etc., and then send the mail test manually

6. Configure mailbox

1) check to see if mailx is installed

[root@jm1j-node1 scripts] # rpm-qa | grep mailxmailx-12.4-8.el6_6.x86_64

2) modify mail configuration

[root@yilonghc-m01 ~] # tail / etc/mail.rc # # t add the following content: set bsdcompatset from=outlokk@163.comset smtp=smtp.163.comset smtp-auth-user=*@163.com # your own email set smtp-auth-password=* # # authorization code set smtp-auth=login

6. Scheduled backup (you can join the crontab scheduled task normally, which is executed at 0: 00 every day)

Not found above, the following command to install

Yum install-y vixie-cron

Add a scheduled task

Crontab-e#mysqlbackup by qyt at 2017-12-1300 00 * / bin/sh / server/scripts/mysqlbackup.sh > / dev/null 2 > & 1

# # remote backup has multiple methods. In order to reduce server pressure, I use rsync client pull method

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.