Mysql scheduled backup using Linux crontab timed backup instance _mysql

1. mysql to back up a database command

Copy Code code as follows:

##################################################################
# Back up a database
##################################################################

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

# Export database, hot standby
Mysqldump-u Root-pmypasssecret cncounter > cncounter_dump.sql.20140414_1333

2. mysql Restore the command of a database

Copy Code code as follows:

##################################################################
# Restore a Database
##################################################################
# Modify Password
# mysqladmin-u root password "Mypasssecret"
# mysqladmin-u root password Oldpass "Mypasssecret"

# Login
Mysql-u Root-pmypasssecret

--Hot standby is just a backup of the tables inside the database, and the data
Use Cncounter;
source/usr/local/cncounter/mysql_dump/cncounter_dump.sql.20140414_1333;
Exit

3. Use crontab scheduled backup MySQL

3.1 Backup Scripts

Copy Code code as follows:

##################################################################
# 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

############### #下面是备份脚本的内容
#!/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 script executed can be less flexible: the backslash (\) at the end of the line means that the shell instruction wraps, and if it is written inside a single line, it is no longer needed.

Copy Code code as follows:

/usr/bin/mysqldump--opt-uroot-pmypasssecret cncounter \
>/root/mysql_dump/data/cncounter.dump_ ' Date +%y%m%d_%h%m%s '. sql

Dump out of the SQL file can be very large, we can also turn on gzip compression, generally can achieve 10 times times the compression ratio: that is, the output to the contents of the file through the pipeline Operation Foujian gzip program processing.

Copy Code code as follows:

/usr/bin/mysqldump--opt-uroot-pmypasssecret Cncounter | GZIP \
>/root/mysql_dump/data/cncounter.dump_ ' Date +%y%m%d_%h%m%s '. sql.gz

3.2 Add to Crontab

Copy Code code as follows:

# Add to Crontab
Crontab-e

# Add a row, the root user does not need to specify the user name to execute, ESC,WQ
1 1 * * */root/mysql_dump/mysql_back.sh

# no need to restart Crontab service
# Service Crond Restart

3.3 Crontab Simple Description

Copy Code code as follows:

# Cat/etc/crontab
Shell=/bin/bash
Path=/sbin:/bin:/usr/sbin:/usr/bin
Mailto=root
home=/

# for details, 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 5 parts, the first of which is the number of minutes, the second is the number of hours, and the third is the day ordinal of one months ... If it is *, it means daily scheduling.
User-name section, if you need to schedule with other users, you can specify, otherwise cannot be specified, such as root users can not specify root, otherwise there is a dispatch log, but not actually executed.