MySQL backup script

MySQL is widely used in small and midsize businesses, and the following script can be used for full backups in a production environment.

Backup scenario: Full backup every 1:30 A.M., with 30 days of backup data (binary logs can be opened for incremental backup and recovery)

The steps are as follows:

1 Creating a backup user on the database server

Mysql> Grant Select,lock tables on bbs.* to ' backupmysql ' @ ' 192.168.154.160 ' identified by ' backupmysql ';

Mysql>flush privileges;

2 test the database backup from the backup host (the MySQL server requires an open 3306 port)

Mysqldump-ubackupmysql-pbackupmysql-h 192.168.154.162--databases BBS > Bbs.sql

3 Writing the backup script (on the backup host)

cd/data/script/

Vim mysqlbackup.sh

#!/bin/bash

#Name: Backupmysql

#Date: 2017-03-04

#Author: ZWJ

#Mail: [Email protected]

#Function: Mysqlbackup

#Version: 1.1

Myuser= "Backupmysql"

Mypass= "Backupmysql"

Mysqlhost= "192.168.154.162"

myconn= "-u$myuser-p$mypass-h $MYSQLHOST"

mydb= "BBS"

Mydst= "/data/backup/"

Mycmd= "/usr/bin/mysqldump"

mytime=$ (Date +%f)

Name= "$MYDB-$MYTIME"

CD $MYDST

$MYCMD $MYCONN--databases $MYDB > $NAME. sql

/bin/tar zcf $NAME. sql.tar.gz $NAME. SQL--remove &>/dev/null

Find $MYDST-mtime +30-name "*.tar.gz"-exec rm-rf {} \;

4 Set up Scheduled tasks

Crontab-e

*/30 1 * * */data/script/mysqlbackup.sh

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1905534

MySQL backup script