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

Basic MySQL backup and recovery

One, mysqldump command

Mysqldump this command can generally meet most of the backup needs, because this tool supports full-library backup, single-database backup, single-table backup, or even logical backup, only backup table structure and so on.

Main command format:

Shell > mysqldump [options] > dump.sql

Some main parameters:

-u,-- user user name

-p,-- password user password

-S,-- socket socket

-h,-- host specifies the connected server

-the port of the specified server is specified by Pmam Mushport.

-- default-character-set sets backup set character set

-A,-- all-databases backs up all databases

-B,-- databases exports one or more databases with database names separated by spaces

-- tables exports the specified table in the format "database name table name". This parameter overrides the-B parameter

-l,-- lock-tables lock table, which is enabled by default

-- single-transaction, which ensures consistency for database engines that support things, usually works with-l and writes-- single-transaction-l, which replaces the-x parameters of other engines.

-d,-- nodata, only the structure of the object is exported, not the data.

-t,-- no-create-info, which only exports object data and does not contain the structure of the object

Generally speaking, when backing up the entire database, you need to refresh the log first to facilitate recovery.

-- flush-privileges, refresh permissions before backup.

-R,-- routines, export stored procedures, functions and other MYSQL programs.

-E,-- events, output event

-- master-data=1/2-- 1, record the binlog number and location that will continue to reply after the backup. 2, there are comments before the number and location

Generally, the above parameters are suitable for most application scenarios.

For myisam, the more commonly used full-library backup statement:

Mysqldump-uroot-p-A-B-F-R-flush-privileges-- master-data=2-x-- events | gzip > / data/backup/all.sql.gz

For innodb, the more commonly used full-library backup statement:

Mysqldump-uroot-p-A-B-F-R-flush-privileges-- master-data=2-- single-transaction-- events | gzip > / data/backup/all.sql.gz

Second, backup script:

1. Full library backup:

Vi / data/mysqldata/scripts/mysql_full_backup.sh #! / bin/shsource / data/mysqldata/scripts/mysql_env.iniDATA_PATH=/data/mysqldata/backup/mysql_fullDATA_FILE=$ {DATA_PATH} / dbfullbak_ `date +% F`.sql.gzLOG _ FILE=$ {DATA_PATH} / dbfullbak_ `date +% F`.logMYSQL _ PATH=/opt/mysql/binMYSQL_DUMP= "${MYSQL_PATH} / mysqldump-u$ {MYSQL_USER}-p$ {MYSQL_PASS}-S / data/mysqldata / ${MYSQL_PORT} / mysql.sock-A-R-x-default-character-set=utf8 "echo > ${LOG_FILE} echo-e" = Jobs started at `date +% qualified% Tunable% w` = =\ n "> ${LOG_FILE} echo-e" * Executed command:$ {MYSQL_DUMP} | gzip > ${DATA_FILE} "> ${LOG_FILE} ${MYSQL_DUMP} | gzip > $DATA_FILEecho-e" * * Executed finished at at `date +% Fairchild% Find expired backup and delete those files% w` = = "> ${LOG_FILE} echo-e" * Backup file size: `du-sh ${DATA_FILE} `=\ n "> ${LOG_FILE} echo-e"-- Find expired backup and delete those files-- "> ${LOG_FILE} for tfile in $(/ usr/bin/find ${DATA_PATH} /-mtime + 6) do if [- d $tfile] Then rmdir $tfile elif [- f $tfile]; then rm-f $tfile fi echo-e "- Delete file: $tfile -" > ${LOG_FILE} doneecho-e "\ nFlowers = Jobs ended at `date +% favored% qualified% w` =\ n" > > ${LOG_FILE}

two。 Sub-library backup:

Vi mysqlflowers fullbacks backupports by databases dbs.shallows cards binpool source / data/mysqldata/scripts/mysql_env.iniDATA_PATH=/data/mysqldata/backup/mysql_full_by_dbsif [!-d ${DATA_PATH} / ${MYSQL_PORT}] Then mkdir-p$ {DATA_PATH} / ${MYSQL_PORT} fiLOG_FILE=$ {DATA_PATH} / ${MYSQL_PORT} / dbfullbak_ `date +% F`.logMYSQL _ PATH=/opt/mysql/binMYSQL_CMD= "${MYSQL_PATH} / mysql-u$ {MYSQL_USER}-p$ {MYSQL_PASS}-S / data/mysqldata/$ {MYSQL_PORT} / mysql.sock" MYSQL_DUMP= "${MYSQL_PATH} / mysqldump-u$ {MYSQL_USER}-p$ { MYSQL_PASS}-S / data/mysqldata/$ {MYSQL_PORT} / mysql.sock-- single-transaction-l "echo > ${LOG_FILE} echo-e" = Jobs started at `date +% qualified% Tunable% w` = =\ n "> ${LOG_FILE} for dbs in `${MYSQL_CMD}-e" show databases "| sed '1d' | egrep-v" information_schema | mysql | performance_schema "`do echo-e" * * Database: ${dbs} Backup start At `date +% qualified% qualified% w` * "> ${LOG_FILE} ${MYSQL_DUMP}-B ${dbs} | gzip > ${DATA_PATH} / ${MYSQL_PORT} / ${dbs} _ `date +% qualified% date +% qualified% w`sql.gzecho-e" * * Executed finished at at `date +% qualified% checked% w` = "> > ${LOG_FILE} echo-e" * * Backup file size : `du-sh ${DATA_FILE} `=\ n "> ${LOG_FILE} doneecho-e"-- Find expired backup and delete those files-- "> > ${LOG_FILE} for tfile in $(/ usr/bin/find ${DATA_PATH} / ${MYSQL_PORT}-mtime + 6) do if [- d $tfile] Then rmdir $tfile elif [- f $tfile]; then rm-f $tfile fi echo-e "- Delete file: $tfile -" > ${LOG_FILE} doneecho-e "\ nFlowers = Jobs ended at `date +% favored% qualified% w` =\ n" > > ${LOG_FILE}

3. Sub-table backup

Vi mysqlcards fullbacks backupsetting bylines tbs.shacks. Bins. Bins. BINGULAR source / data/mysqldata/scripts/mysql_env.iniDATA_PATH=/data/mysqldata/backup/mysql_full_by_dbsif [!-d ${DATA_PATH} / ${MYSQL_PORT}] Then mkdir-p$ {DATA_PATH} / ${MYSQL_PORT} fiLOG_FILE=$ {DATA_PATH} / ${MYSQL_PORT} / dbfullbak_ `date +% F`.logMYSQL _ PATH=/opt/mysql/binMYSQL_CMD= "${MYSQL_PATH} / mysql-u$ {MYSQL_USER}-p$ {MYSQL_PASS}-S / data/mysqldata/$ {MYSQL_PORT} / mysql.sock" MYSQL_DUMP= "${MYSQL_PATH} / mysqldump-u$ {MYSQL_USER}-p$ { MYSQL_PASS}-S / data/mysqldata/$ {MYSQL_PORT} / mysql.sock-- single-transaction-l "echo > ${LOG_FILE} echo-e" = Jobs started at `date +% qualified% Tunable% w` = =\ n "> ${LOG_FILE} for dbs in `${MYSQL_CMD}-e" show databases "| sed '1d' | egrep-v" information_schema | mysql | performance_schema "`do echo-e" * * Database: ${dbs} Backup start At `date +% qualified% accounts% w` * "> ${LOG_FILE} # # ${MYSQL_DUMP}-B ${dbs} | gzip > ${DATA_PATH} / ${MYSQL_PORT} / ${dbs} _ `date +% qualified% accounts% w`.sql.gz if [!-d ${DATA_PATH} / ${MYSQL_PORT} / ${dbs}] Then mkdir-p ${DATA_PATH} / ${MYSQL_PORT} / ${dbs} fi for tbls in `$ {MYSQL_CMD}-D ${dbs}-e "show tables" | sed '1d' `do echo-e "* * Tables: ${dbs}. ${tbls} start dump at `date +% qualified% thanks% w` * *" > ${LOG_FILE } echo-e "Execute Command: ${MYSQL_DUMP}-- tables ${dbs} ${tbls} | gzip > ${DATA_PATH} / ${MYSQL_PORT} / ${dbs} / ${dbs}. ${tbls} .sql.gz" > ${LOG_FILE} ${MYSQL_DUMP}-tables ${dbs} ${tbls} | gzip > ${DATA_PATH} / ${MYSQL_PORT} / ${dbs} / ${dbs}. ${tbls} .sql.gz echo-e "# End ${dbs}. ${tbls} Dump! Stop_time: `date +% qualified% qualified% w` # "> ${LOG_FILE} doneecho-e" * Executed finished at `date +% qualified% echo% w` = = "> > ${LOG_FILE} echo-e" * Backup file size: `date-sh ${DATA_FILE} `=\ n "> ${LOG_FILE} doneecho-e"-- Find expired backup and delete those files-- "> ${LOG_FILE } for tfile in $(/ usr/bin/find ${DATA_PATH} / ${MYSQL_PORT} / ${dbs}-mtime + 6) do if [- d $tfile] Then rmdir $tfile elif [- f $tfile] Then rm-f $tfile fi echo-e "--Delete file: $tfile--" > ${LOG_FILE} doneecho-e "\ nFlowers = Jobs ended at `date +% qualified% Troubles% w` =\ n" > > ${LOG_FILE} eco-e "--Find expired logs and delete those files--" > > ${LOG_FILE} for tfile in $(/ usr/bin/find ${DATA_PATH} / ${MYSQL_) PORT} / ${dbs}-mtime + 6) do if [- f $tfile] Then rm-f $tfile fi echo-e "- Delete file: $tfile -" > ${LOG_FILE} doneecho-e "\ nFlowers = Jobs ended at `date +% favored% qualified% w` =\ n" > > ${LOG_FILE}

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.