http://imysql.cn/mysql_backup_and_recover
一 备份与恢复案例
1.备份数据库(全部是innodb,备份所有DB 使用 -A)
mysqldump -uroot -p --master-data=2 --single-transaction --hex-blob -E -R --triggers -B testdb >/download/testdb.sql
案例
# cat dbbackup_dump.sh
#!/bin/bash
dbdirs=/home/backup_dump
dbname=NodeServiceDB
# dblog=$dbname-$(date +%Y%m%d%H%M).log
dbbak=$dbname-$(date +%Y%m%d%H%M).sql.gz
#mysqldump -uroot -p --master-data=2 --single-transaction --hex-blob -E -R --triggers -B $dbname |gzip> $dbdirs/$dbbak
/home/mysql/bin/mysqldump -uroot -p*** --hex-blob -E -R --triggers -q -B $dbname |gzip> $dbdirs/$dbbak
## job status ##
if [ "$?" == 0 ];then
mysql -u root -p*** -hlocalhost << EOF
use MysqlCenter;
INSERT INTO MysqlCenter.job_status(jobname,status)
SELECT "30 6 * * * /home/backup_dump/dbbackup_dump.sh" AS jobname,"SUCCESS" AS status ;
EOF
else
mysql -u root -p*** -hlocalhost << EOF
use MysqlCenter;
INSERT INTO MysqlCenter.job_status(jobname,status)
SELECT "30 6 * * * /home/backup_dump/dbbackup_dump.sh" AS jobname,"FAIL" AS status ;
EOF
fi
find /home/backup_dump/ -name '*.sql.gz' -mtime +10 -exec rm {} \;
# gunzip testdb.sql.gz
加上for循环备份数据库
# cat dbbackup_dump.sh
#!/bin/bash
dbdirs=/home/backup_dump
dbname="NodeServiceDB"
for db in $dbname ;do
dbbak=$db-$(date +%Y%m%d%H%M).sql.gz
#mysqldump -uroot -p --master-data=2 --single-transaction --hex-blob -E -R --triggers -B $dbname |gzip> $dbdirs/$dbbak
/home/mysql/bin/mysqldump -uroot -p*** --hex-blob -E -R --triggers -q -B $db |gzip -c > $dbdirs/$dbbak
done
## job status ##
if [ "$?" == 0 ];then
mysql -u root -p*** -hlocalhost << EOF
use MysqlCenter;
INSERT INTO MysqlCenter.job_status(jobname,status)
SELECT "30 6 * * * /home/backup_dump/dbbackup_dump.sh 2>/home/backup_dump/mysqldump.log" AS jobname,"SUCCESS" AS status ;
EOF
else
mysql -u root -p*** -hlocalhost << EOF
use MysqlCenter;
INSERT INTO MysqlCenter.job_status(jobname,status)
SELECT "30 6 * * * /home/backup_dump/dbbackup_dump.sh 2>/home/backup_dump/mysqldump.log" AS jobname,"FAIL" AS status ;
EOF
fi
find /home/backup_dump/ -name '*.sql.gz' -mtime +10 -exec rm {} \;
# gunzip testdb.sql.gz
加上for循环单独备份数据库中的每个表
压缩备份
mysqldump -uroot -p --master-data=2 --single-transaction --hex-blob -E -R --triggers -B school |gzip>/tmp/school.sql.gz
解压:
gunzip /tmp/school.sql.gz
参数含义:
--master-data=2 代表备份时刻记录master的Binlog位置和Position
--single-transaction 获取一致性快照
-R 备份存储过程和函数
--triggres 备份触发器
-E 备份事件
--hex-blob 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。
影响到的字段类型有 BINARY、VARBINARY、BLOB。
2.还原压缩备份
# mysql -uroot -p testdb < /download/testdb.sql
或
导入数据，一边解压一边执行sql语句
# gunzip -fc </download/testdb.sql.gz |mysql -uroot -p123456 testdb
参数含义:
-c, --stdout write on standard output, keep original files unchanged
-f, --force force overwrite of output file and compress links
mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --master-data=2 testdb | pv -q -L 10M |gzip > /tmp/testdb.gzip
pv:限流
二 恢复完备+binlog
# mysql -uroot testdb </backup/testdb_20150418.bak
恢复日志
mysqlbinlog --start-position="120" --stop-position="610" /var/lib/mysql/centos3-bin.000010 |mysql -uroot -p testdb
恢复日志(基于时间点）
# mysqlbinlog --stop-date="2014-05-29 13:45" /app/db_log/master23-bin.000022 |mysql -uroot -p testdb
# mysqlbinlog --start-date="2014-05-29 13:50" /app/db_log/master23-bin.000022 |mysql -uroot -p testdb
或者，先导出日志数据，然后基于位置找恢复点
# mysqlbinlog --start-date="2014-05-29 13:45" --stop-date="2014-05-29 13:50" /app/db_log/master23-bin.000022 > /tmp/mysql_restore.sql
恢复日志(基于位置 at)
# mysqlbinlog --stop-position="351" /app/db_log/master23-bin.000022 |mysql -uroot -p hangdb
# mysqlbinlog --start-position="495" /app/db_log/master23-bin.000022 |mysql -uroot -p hangdb
二 其他导出方式
--导出表结构,视图结构,函数,存储过程,触发器,事件,不导出数据
mysqldump -u root -p -d -R --triggers -E cloudalarm > /download/cloudalarm_structure_sql.sql
--单独备份某个表结构及数据
mysqldump -c cloudalarm t_alarm_sectorinfo2 -u root -p > /download/t_alarm_sectorinfo2.sql
metastore
/apps/svr/mysql57/bin/mysqldump -uroot -p --skip-add-drop-table -d -R --triggers -E metastore --set-gtid-purged=OFF -S /tmp/mysql3306.sock > /apps/dbchange/20180727/metastore_structure.sql
导出所有过程,函数,事件,触发器(不要表和数据)
/apps/svr/mysql57/bin/mysqldump -f -Rtdn --triggers -E -uroot -p -A -S /tmp/mysql3306.sock > procedure.sql
