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

First log in to mysql:

Mysql-u root-p Note: it is not the password of the system root. The default root password is empty after mysql is installed.

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

> grant all on zabbix.* to backup@'localhost' identified by "123456";-create a user and password to back up the zabbix database, authorizing all permissions to the zabbix database

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

Host=localhost

User=backup

Password='123456'

When the preparation is done, write the script:

-mysql_backup.sh--

#! / bin/bash

# auto backup mysql db-description information

# from net video 2017-description

# define backup path-- the first step is to prepare the parameter definition, which is conducive to the simplicity of the following code.

BAK_DIR=/data/backup/ `date +% Y% m% d`

MYSQLDB=zabbix

MYSQLCMD=/usr/bin/mysqldump

The first of the above parameters is to specify the backup path, the second is the database name of the backup, and the third is the backup execution program

# Juge the exec user author

If [$UID-ne 0]; then

Echo "Must to be use root for exec shell."

Exit

Fi

The above statements are mainly used to determine whether to execute with root users.

# 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

The above statement determines whether the backup directory exists, and if it does not exist, it will be created and prompted to create it successfully.

# Mysql backup command

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

# this statement is to perform the backup,-- the mysql user and password in the defaults-extra-file application file, the-d parameter, and 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

The above statement is just a message indicating whether the backup is successful or not. If [$?-eq 0] determines whether the above code has been executed incorrectly.

Check the script with the command before executing the script: sh-n auto_mysql_backup.sh

In order to test the effect, let's first check whether there is this file in the backup directory. We can see that there is no such directory as 20170405.

Sh auto_mysql_backup.sh executes script

You can see the prompt that 20170405 of the directory was created successfully and backed up into, let's go to the directory to have a look?

You can see above that the backup of the zabbix database was successful.

Note: in the above script, we can modify MYSQLDB to a manually entered parameter, which makes our script smarter.

Crontab-e

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

Write this script to crontab and execute it every day, and then write an incremental backup in a later article, which is more intelligent.

Authorize MYDB database to backup user grant all on MYDB.* to backup@'localhost' identified by "123456" in database

Minor modifications to the above documents are 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 be use root for exec shell."

Exit

Fi

# the added code is as follows

If [- z "$1"]; then

Echo-e "\ 033 [32mUsage:\ nPlease Enter DataBase that you will backup\ NMUI -\ n\ nUsage: {$0 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-this allows you to back up the specified database. As long as the authorization is granted to the backup user, the authorized user here can also be processed as an input parameter of $2.

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.