使用mysqldump进行备份

常用语法
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --database [OPTIONS] DB1 [DB2..]
mysqldump [OPTIONS] --all-database [OPTIONS]

mysqldump常用参数
-u,--user=name
-p,--password[=name]
SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,PROCESS

--single-transaction //对innodb有效数据库一致性
-l,--lock-tables //依次锁定某一个db所有表数据一致性
-x,--lock-all-table //所有数据备份时候只读
--master-data=[1/2]

存在触发器存储过程调度事件要加参数
-R,--routines
--triggers
-E,--events

--hex-blob //对数据中blog16进制保存
-tab=path //存储表结构加数据
-w,--where='过滤条件'
Where只支持单表

mysqldump备份实例

create user 'backup'@'localhost' identified by '123456';
grant select,reload,lock tables,replication client,show view,event,process on . to 'backup'@'localhost'; //给用户权限
mysqldump -ubackup -p123456 --master-data=2 --single-transaction --routines --triggers --events szyqbbs > szyqbbsdb.sql
grep "CREATE TABLE" szyqbbsdb.sql //查看表
备份单个数据表
mysqldump -ubackup -p123456 --master-data=2 --single-transaction --routines --triggers --events szyqbbs bbs_user_info > szyqbbsdb_userinfo.sql
备份所有数据库
mysqldump -ubackup -p123456 --master-data=2 --single-transaction --routines --triggers --events --all-databases > alldata.sql
指定目录 tab 会拆分成.sql .txt
mysqldump -ubackup -p123456 --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/szyqbbsdb" szyqbbs > szyqbbsdb.sql
chown mysql:mysql szyqbbsdb //改变目录用户为mysql用户这样mysql用户才能写入数据
mysqldump -ubackup -p123456 --master-data=2 --single-transaction --routines --triggers --events --where "cid>100 and cid<200" szyqbbs bbs_card> szyqbbsdb_card_100_200.sql

备份脚本

#!/bin/bash
###############Basic parameters##########################
DAY=`date +%Y%m%d`
Environment=$(/sbin/ifconfig | grep "inet addr" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' | awk -F':' '{print $2;}')
USER="backup"
PASSWD="123456"
HostPort="3306"
MYSQLBASE="/home/mysql/"
DATADIR="/home/www/data/db_backup/${DAY}"
MYSQL=`/usr/bin/which mysql`
MYSQLDUMP=`/usr/bin/which mysqldump`
mkdir -p ${DATADIR}

Dump(){
 ${MYSQLDUMP} --master-data=2 --single-transaction  --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database}  > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql
}

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema','abgent_web_20')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
   database=${db}
   Dump
done

1.参数意义
DAY 当前备份日期
Environment 主机IP
MYSQLBASE 备份目录
DATADIR 当前备份子目录
for db in //排除一些数据库如information_schema

如何恢复mysqldump备份的数据

mysql -u -p dbname < backup.sql
mysql> source /tmp/backup.sql
mysql -uroot -pweizai -e"create database bak_szyqbbsdb"

用备份数据还原删除的数据

INSERT TO mc_orderdb.order_master (///字段////)
SELECT a.* FROM bak_orderdb.order_master a
LEFT JOIN mc_orderdb.order_master b on a.order_id=b.order_id
WHERE b.order_id IS NULL;
//静态数据可以动态不可以

恢复误删除数据

基于时间点的步骤
mysqlbin 结合数据库日志 position

基于时间点的恢复
1.具有指定时间点前的mysqldump的全备
2.具有全备到指定时间点的mysql二进制文件

二进制文件备份

使用xtrabackup全备

innobackupex --user=root --password=pwd
--parallel=2 /home/db_backup