MySQL scheduled backup: using crontab in Linux to regularly back up instances _ MySQL

MySQL scheduled backup: using crontab in Linux 1. Mysql commands for backing up a database

######################################## ##########################
# Backing up a database
######################################## ##########################

# Root user, create a backup directory
Mkdir-p/usr/local/cncounter/mysql_dump
Cd/usr/local/cncounter/mysql_dump

# Exporting databases and hot standby
Mysqldump-u root-pmypasssecret cncounter> cncounter_dump. SQL .20140414_133
2. Mysql command to restore a database

######################################## ##########################
# Restoring a database
######################################## ##########################
# Change password
# Mysqladmin-u root password "mypasssecret"
# Mysqladmin-u root password oldpass "mypasssecret"

# Logon
Mysql-u root-pmypasssecret

-- Hot backup only backs up tables and data in the database.
Use cncounter;
Source/usr/local/cncounter/mysql_dump/cncounter_dump. SQL .20140414_133;
Exit;

3. use crontab to regularly back up Mysql

3.1 Backup Script

######################################## ##########################
# Crontab scheduled backup
######################################## ##########################
#
# Root user, create execution script
Mkdir-p/root/mysql_dump/data
Cd/root/mysql_dump
Touch mysql_back.sh
Chmod 755 mysql_back.sh

# Edit backup script
Vim mysql_back.sh

############### The content of the backup script is as follows:
#! /Bin/sh
# File:/root/mysql_dump/mysql_back.sh
# Database info
DB_NAME = "cncounter"
DB_USER = "root"
DB_PASS = "mypasssecret"
# Others vars
# Whereis mysqldump
# IS 'but not'
BIN_DIR = "/usr/bin"
BCK_DIR = "/root/mysql_dump/data"
DATE = 'date + % Y % m % d _ % H % M % s'
# TODO
Mkdir-p $ BCK_DIR
$ BIN_DIR/mysqldump -- opt-u $ DB_USER-p $ DB_PASS $ DB_NAME/
> $ BCK_DIR/$ DB_NAME.dump _ $ DATE. SQL

Of course, the executed script does not need to be so flexible: the backslash (/) at the end of the line indicates the shell command line break. if it is written inside a single row, it is no longer needed.

/Usr/bin/mysqldump -- opt-uroot-pmypasssecret cncounter/
>/Root/mysql_dump/data/cncounter. dump _ 'date + % Y % m % d _ % H % M % s'. SQL

The SQL file dumped by dump may be large. you can also enable gzip compression. Generally, the compression ratio can be 10 times: that is, the content output to the file is processed by the gzip program through the pipeline operator.

/Usr/bin/mysqldump -- opt-uroot-pmypasssecret cncounter | gzip/
>/Root/mysql_dump/data/cncounter. dump _ 'date when policyymmd1_h1_m1_s'. SQL .gz

3.2 add to crontab

# Add to crontab
Crontab-e

# Add a row. the root user does not need to specify the execution username, ESC, and wq.
1 1 ***/root/mysql_dump/mysql_back.sh

# The crontab service does not need to be restarted.
# Service crond restart

3.3 crontab

# Cat/etc/crontab
SHELL =/bin/bash
PATH =/sbin:/bin:/usr/sbin:/usr/bin
MAILTO = root
HOME =/

# For details see man 4 crontabs

# Example of job definition:
#. ---------------- Minute (0-59)
# |. ----------- Hour (0-23)
# |. ---------- Day of month (1-31)
# |. ------- Month (1-12) OR jan, feb, mar, apr...
# |. ---- Day of week (0-6) (Sunday = 0 or 7) OR sun, mon, tue, wed, thu, fri, sat
# |
# ***** User-name command to be executed

It can be found that the crontab execution cycle consists of five parts, the first is the number of minutes, the second is the hour, and the third is the day of the month... If it is *, scheduling is performed every day.
User-name. if scheduling is performed by another user, you can specify this parameter. otherwise, root cannot be specified. otherwise, scheduling logs are generated but not executed.