MySQL备份恢复基础

一，mysqldump命令

mysqldump这个命令一般可以满足备份的大部分需要，因为这个工具支持全库备份，单库备份，单表备份，甚至逻辑备份，只备份表结构等等功能。

主要命令格式：

shell>mysqldump[options]>dump.sql

主要的一些参数：

-u ，--user 用户名

-p , --password 用户密码

-S ，--socket 套接字

-h , --host 指定连接的服务器

-P，--port 指定服务器的对应端口

--default-character-set 设置备份集字符集

-A ，--all-databases 备份所有数据库

-B ，--databases 导出某个或者某些数据库，数据库名以空格分隔

--tables 导出指定的表，格式为“数据库名 表名”，这个参数会覆盖-B参数

-l ,--lock-tables 锁定表，默认开启

--single-transaction ,对于支持事物的数据库引擎来说，保证了一致性，通常和-l一起工作，写作 --single-transaction -l， 其作用是取代其他引擎的 -x 参数。

-d, --nodata, 只导出对象的结构，不导出数据。

-t , --no-create-info , 只导出对象数据而不包含对象的结构

-F，--flush-logs ，一般在全库备份的时候需要先刷新日志，以方便恢复。

--flush-privileges , 备份前刷新一下权限。

-R , --routines , 导出存储过程，函数等MYSQL程序。

-E， --events ,输出event

--master-data=1/2 --1,记录备份后将继续回复的binlog 编号和位置。 2，编号和位置前有注释

一般以上参数适应了大多数的应用场景

对于myisam,比较常用的全库备份语句:

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

对于innodb，比较常用的全库备份语句：

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

二，备份脚本：

1.全库备份：

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"===Jobsstartedat`date+%F''%T''%w`===\n">>${LOG_FILE}echo-e"***Executedcommand:${MYSQL_DUMP}|gzip>${DATA_FILE}">>${LOG_FILE}${MYSQL_DUMP}|gzip>$DATA_FILEecho-e"***Executedfinishedatat`date+%F''%T''%w`===">>${LOG_FILE}echo-e"***Backupfilesize:`du-sh${DATA_FILE}`===\n">>${LOG_FILE}echo-e"---Findexpiredbackupanddeletethosefiles---">>${LOG_FILE}fortfilein$(/usr/bin/find${DATA_PATH}/-mtime+6)doif[-d$tfile];thenrmdir$tfileelif[-f$tfile];thenrm-f$tfilefiecho-e"---Deletefile:$tfile---">>${LOG_FILE}doneecho-e"\n===Jobsendedat`date+%F''%T''%w`===\n">>${LOG_FILE}

2. 分库备份：

vimysql_full_backup_by_dbs.sh#!/bin/shsource/data/mysqldata/scripts/mysql_env.iniDATA_PATH=/data/mysqldata/backup/mysql_full_by_dbsif[!-d${DATA_PATH}/${MYSQL_PORT}];thenmkdir-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"===Jobsstartedat`date+%F''%T''%w`===\n">>${LOG_FILE}fordbsin`${MYSQL_CMD}-e"showdatabases"|sed'1d'|egrep-v"information_schema|mysql|performance_schema"`doecho-e"***Database:${dbs}Backupstartat`date+%F''%T''%w`***">>${LOG_FILE}${MYSQL_DUMP}-B${dbs}|gzip>${DATA_PATH}/${MYSQL_PORT}/${dbs}_`date+%F''%T''%w`.sql.gzecho-e"***Executedfinishedatat`date+%F''%T''%w`===">>${LOG_FILE}echo-e"***Backupfilesize:`du-sh${DATA_FILE}`===\n">>${LOG_FILE}doneecho-e"---Findexpiredbackupanddeletethosefiles---">>${LOG_FILE}fortfilein$(/usr/bin/find${DATA_PATH}/${MYSQL_PORT}-mtime+6)doif[-d$tfile];thenrmdir$tfileelif[-f$tfile];thenrm-f$tfilefiecho-e"---Deletefile:$tfile---">>${LOG_FILE}doneecho-e"\n===Jobsendedat`date+%F''%T''%w`===\n">>${LOG_FILE}

3. 分表备份

vimysql_full_backup_by_tbs.sh#!/bin/shsource/data/mysqldata/scripts/mysql_env.iniDATA_PATH=/data/mysqldata/backup/mysql_full_by_dbsif[!-d${DATA_PATH}/${MYSQL_PORT}];thenmkdir-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"===Jobsstartedat`date+%F''%T''%w`===\n">>${LOG_FILE}fordbsin`${MYSQL_CMD}-e"showdatabases"|sed'1d'|egrep-v"information_schema|mysql|performance_schema"`doecho-e"***Database:${dbs}Backupstartat`date+%F''%T''%w`***">>${LOG_FILE}##${MYSQL_DUMP}-B${dbs}|gzip>${DATA_PATH}/${MYSQL_PORT}/${dbs}_`date+%F''%T''%w`.sql.gzif[!-d${DATA_PATH}/${MYSQL_PORT}/${dbs}];thenmkdir-p${DATA_PATH}/${MYSQL_PORT}/${dbs}fifortblsin`${MYSQL_CMD}-D${dbs}-e"showtables"|sed'1d'`doecho-e"***Tables:${dbs}.${tbls}startdumpat`date+%F''%T''%w`***">>${LOG_FILE}echo-e"ExecuteCommand:${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.gzecho-e"###End${dbs}.${tbls}Dump!Stop_time:`date+%F''%T''%w`###">>${LOG_FILE}doneecho-e"***Executedfinishedat`date+%F''%T''%w`===">>${LOG_FILE}echo-e"***Backupfilesize:`du-sh${DATA_FILE}`===\n">>${LOG_FILE}doneecho-e"---Findexpiredbackupanddeletethosefiles---">>${LOG_FILE}fortfilein$(/usr/bin/find${DATA_PATH}/${MYSQL_PORT}/${dbs}-mtime+6)doif[-d$tfile];thenrmdir$tfileelif[-f$tfile];thenrm-f$tfilefiecho-e"---Deletefile:$tfile---">>${LOG_FILE}doneecho-e"\n===Jobsendedat`date+%F''%T''%w`===\n">>${LOG_FILE}eco-e"---Findexpiredlogsanddeletethosefiles---">>${LOG_FILE}fortfilein$(/usr/bin/find${DATA_PATH}/${MYSQL_PORT}/${dbs}-mtime+6)doif[-f$tfile];thenrm-f$tfilefiecho-e"---Deletefile:$tfile---">>${LOG_FILE}doneecho-e"\n===Jobsendedat`date+%F''%T''%w`===\n">>${LOG_FILE}