MySQL backup and crontab regular backup in Linux _ MySQL

MySQL backup and crontab regular backup in Linux 1. back up a database

######################################## ########################### Back up a database ########## ######################################## ################# root user, create the backup directory mkdir-p/usr/local/cncounter/mysql_dumpcd/usr/local/cncounter/mysql_dump # export the database, hot backup mysqldump-u root-pmypasssecret cncounter> counters

2. restore a database

######################################## ########################### Restore a database ########## ######################################## ################ change the password # mysqladmin-u root password "mypasssecret" # mysqladmin-u root password oldpass "mypasssecret" # log on mysql-u root-pmypasssecret -- hot backup is only used to back up tables in the database, and data use cncounter; source/usr/local/cncounter/mysql_dump/cncounter_dump. SQL .20140414_logs; exit;

3. crontab scheduled backup

3.1 edit backup script

######################################## ########################### Crontab scheduled backup ########## ######################################## ################# root user, create the execution script mkdir-p/root/mysql_dump/datacd/root/mysql_dumptouch mysql_back.shchmod 755 mysql_back.sh # Edit the backup script vim mysql_back.sh ############### # The following is the backup script content #! /Bin/sh # File: /root/mysql_dump/mysql_back.sh # Database infoDB_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' # TODOmkdir-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

3.2 add to crontab

# Add to crontabcrontab-e # Add a row. the root user does not need to specify the execution Username. ESC, wq1 1 ***/root/mysql_dump/mysql_back.sh # you do not need to restart the crontab service # service crond restart

3.3 crontab

# cat /etc/crontab SHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/binMAILTO=rootHOME=/# 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.