1. Mysql commands for backing up a database
Copy codeThe Code is as follows:
######################################## ##########################
# 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
Copy codeThe Code is as follows:
######################################## ##########################
# 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
Copy codeThe Code is 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
############### 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.
Copy codeThe Code is as follows:
/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.
Copy codeThe Code is as follows:
/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
Copy codeThe Code is as follows:
# 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
Copy codeThe Code is as follows:
# 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.