Shell Learning automatic backup MySQL Database

First login to MySQL:

mysql-u root-p Note: The default root password is empty after MySQL is installed, not the system root password

> set password for ' root ' @ ' localhost ' = password (' * * * * * '); --Modify the root user's local password

> Grant all on zabbix.* to [email protected] ' localhost ' identified by "123456"; ---Create a user and password that backs up the Zabbix database, authorizing all permissions to the Zabbix database

To exit the database, we edit the /etc/my.cnf and add the user name and password under the [client] module as follows:

Host=localhost

User=backup

Password= ' 123456 '

Prepare for the job and then write the script:

------------------------mysql_backup.sh--------------------------------------

#!/bin/bash

#auto backup MySQL DB-- description information

#from NET video 2017--Description information

#define BACKUP path-The first step is to prepare for the parameter definition, which facilitates the brevity of the following code

bak_dir=/data/backup/' Date +%y%m%d '

Mysqldb=zabbix

Mysqlcmd=/usr/bin/mysqldump

#以上的参数第一个是指定备份路径, the second is the database name of the backup, and the third is the executing program of the backup

#Juge the exec user author

If [$UID-ne 0];then

echo "must to is use the root for exec shell."

Exit

Fi

#以上的语句主要是用于判断是否用root用户执行

#Juge If backup exists

if [!-D $BAK _dir];then

Mkdir-p $BAK _dir

Echo-e "\033[32mthe $BAK _dir creat successfully!\033[0m"

Else

echo "This $BAK _dir is exists ..."

Fi

#以上的语句是判断备份的目录是否存在 If it does not exist then create and prompt to create success

#Mysql Backup command

$MYSQLCMD--defaults-extra-file=/etc/my.cnf-d $MYSQLDB > $BAK _dir/$MYSQLDB. sql

#这条语句就是执行备份的,--defaults-extra-file the MySQL user and password in the application file, the-d parameter, only the table structure is exported

#Juge Success or Failed

If [$?-eq 0];then

Echo-e "\033[32mthe Mysql Backup $MYSQLDB successfully!\033[0m"

Else

Echo-e "\033[32mthe Mysql Backup $MYSQLDB failed,please check.\033[0m"

Fi

#以上的语句只是一个提示备份成功与否的信息if [$?-eq 0] Determine if the above code is executed incorrectly

Use the command to detect the script before executing the script:sh-n auto_mysql_backup.sh

To test the effect, let's look at the backup directory to see if there is a file, you can see that there is no 20170405 such directory

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8F/8C/wKioL1jkxQfDrh1yAAAhPDyYKdg902.png-wh_500x0-wm_ 3-wmp_4-s_161461255.png "title=" Qq20170405182032.png "alt=" Wkiol1jkxqfdrh1yaaahpdyykdg902.png-wh_50 "/>

SH auto_mysql_backup.sh Execute Script

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8F/8F/wKiom1jkxeCxtfpoAAAdDl-JZ2o305.png-wh_500x0-wm_ 3-wmp_4-s_1976176644.png "title=" Qq20170405182348.png "alt=" Wkiom1jkxecxtfpoaaaddl-jz2o305.png-wh_50 "/>

You can see the hint that the directory that created 20170405 was successful and backed up, let's go to the directory to see it?

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/8F/8F/wKiom1jkxlGCqhGpAAAeQBH7Dy8519.png-wh_500x0-wm_ 3-wmp_4-s_680609931.png "title=" qq picture 20170405182550.png "alt=" Wkiom1jkxlgcqhgpaaaeqbh7dy8519.png-wh_50 "/>

The above can see that the backup Zabbix database was successful.

Note: In the above script we can modify the mysqldb for a manually entered parameters, which makes our scripts smarter

Crontab-e

0 0 * * */bin/bash/data/sh/auto_mysql_backup.sh >>/tmp/mysql_back.log

Write the script to crontab every day to execute it, and then write an incremental backup in a later article to make it smarter.

Authorize the MYDB database in the database to the backup user grant all on mydb.* to [e- Mail protected] ' localhost ' identified by "123456";

The above documents are slightly modified as follows:

-------------------------auto_mysql_backup02.sh---------------------------------------

#!/bin/bash

#auto Backup Database

#from Net video 2017

bak_dir=/data/backup/' Date +%y%m%d '

Mysqldb=$1

Mysqlcmd=/usr/bin/mysqldump

If [$UID-ne 0];then

echo "must to is use the root for exec shell."

Exit

Fi

#增加的代码如下

If [-Z "$"];then

Echo-e "\033[32musage:\nplease Enter DataBase that you'll backup \ n-------------------------------\n\nusage: {$ mysql } \033[0m "

Exit

Fi

if [!-D $BAK _dir];then

Mkdir-p $BAK _dir

Echo-e "\033[32mthe $BAK _dir creat successfully!\033[0m"

Else

echo "This $BAK _dir is exists ..."

Fi

#Mysql BACKUP COMMAND

$MYSQLCMD--defaults-extra-file=/etc/my.cnf-d $MYSQLDB > $BAK _dir/$MYSQLDB. sql

If [$?-eq 0];then

Echo-e "\033[32mthe Mysql Backup $MYSQLDB successfully!\033[0m"

Else

Echo-e "\033[32mthe Mysql Backup $MYSQLDB failed,please check.\033[0m"

Fi

---------------------------------------------------------------------------------------------------
sh auto_mysql_backup02.sh MYDB --so that you can back up the specified database, as long as authorized to the backup user, the authorized users here we can also be made into the input parameter $ $ to deal with.

This article is from the "Sed for linux commands" blog, so be sure to keep this source http://beforce.blog.51cto.com/3335637/1913160

Shell Learning automatic backup MySQL Database