mysql 备份和恢复 mysqldump xtrabackup

最新推荐文章于 2024-05-20 19:22:08 发布

mysql_lover

最新推荐文章于 2024-05-20 19:22:08 发布

阅读量410

点赞数 1

分类专栏： mysql 文章标签： mysql 备份 mysqldump x

本文链接：https://blog.csdn.net/mysql_lover/article/details/54344299

版权

mysql 专栏收录该内容

30 篇文章 4 订阅

订阅专栏

一、mysqldump备份

1)全量备份
mysqldump -uroot -proot --all-databases --lock-all-tables --routines --triggers --master-data=2 --flush-logs > /data/mysql/mysql3306/myback/2017-1-11.17-25.full.sql
--all-databases 备份所有数据库
--lock-all-tables 所有表加读锁
--routinge 存储过程与函数
--triggers 触发器
其他参数：
` --add-locks 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。
--add-drop-table 在每个create语句之前增加一个drop table。
--allow-keywords 允许创建是关键词的列名字。这由表名前缀于每个列名做到。
-c, --complete-insert 使用完整的insert语句(用列名字)。
-C, --compress 如果客户和服务器均支持压缩，压缩两者间所有的信息。
--delayed 用INSERT DELAYED命令插入行。
-e, --extended-insert 使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）
-#, --debug[=option_string] 跟踪程序的使用(为了调试)。
-F, --flush-logs 在开始导出前，洗掉在MySQL服务器中的日志文件。
-f, --force 即使我们在一个表导出期间得到一个SQL错误，继续。
-h, --host=..
-l, --lock-tables. 为开始导出锁定所有表。
-t, --no-create-info 不写入表创建信息(CREATE TABLE语句）
-d, --no-data 不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！
-P port_num, --port=port_num
-q, --quick 不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。
-u user_name, --user=user_name 与服务器连接时，MySQL使用的用户名。缺省值是你的Unix登录名。
-O var=option, --set-variable var=option 设置一个变量的值。可能的变量被列在下面。
-v, --verbose冗长模式。打印出程序所做的更多的信息。
-V, --version打印版本信息并且退出。
-w, --where='where-condition' 只导出被选择了的记录；注意引号是强制的。
--opt 同--quick --add-drop-table --add-locks --extended-insert --lock-tables

2）mysqldump全量数据恢复
登录mysql，执行 source + datapath.sql
3）场景模拟，假设我在表中新增数据后，不小心执行drop table操作，进行数据恢复
（1）创建一张表 mysql> create table testBack(id int auto_increment primary key,name varchar(10));
（2）插入几条数据 insert into testBack(name) values('aaa'),('bbb'),('ccc'),('ddd'),('eee'),('fff');
（3）对该数据库做一个备份 mysqldump -uroot -proot --lock-all-tables --routines --triggers --master-data=2 --flush-logs --database backup_test > /data/mysql/mysql3306/myback/2017-1-12.10-31.backup_test.sql
（4）插入几条数据 insert into testBack(name) values('ggg'),('hhh'),('iii');
（5）删除表 drop table testBack;
（6）恢复之前的备份数据并设置不记录日志set global sql_log_bin=0; source /data/mysql/mysql3306/myback/2017-1-12.10-44.backup_test.sql
（7）查看二进制日志 mysqlbinlog binlog3306.000007
# at 420
#170112 13:09:57 server id 1 end_log_pos 555 CRC32 0x18c54f02 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484197797/*!*/;
DROP TABLE `testBack` /* generated by server */
/*!*/;
# at 555
可知删除操作是在420处做的
（8）将二进制日志转储为sql，并导入
mysqlbinlog --stop-position=420 binlog3306.000007>/data/mysql/mysql3306/myback/change.sql
source /data/mysql/mysql3306/myback/change.sql

二、使用xtrabackup进行完全备份、增量备份、热备份
1）下载、安装
下载：wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.2/binary/Linux/x86_64/percona-xtrabackup-2.1.2-611.tar.gz
安装：
首先安装相关的依赖
yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package -y
然后解压：tar xvf percona-xtrabackup-2.1.2-611.tar.gz
cd percona-xtrabackup-2.1.2/bin
然后将innobackupex、xtrabackup等文件copy到mysql程序目录下/bin、目录
然后将mysql安装目录下的文件做软链接到/usr/bin/目录
ln -s /data/mysql/mysql3306/bin/* /usr/bin/
最后，测试是否安装成功 innobackupex --help
为备份建立一个只有备份权限的用户
mysql> create user 'backup'@'localhost' identified by 'backup';
mysql> revoke all privileges,grant option from 'backup'@'localhost';
mysql> grant reload,lock tables,replication client on *.* to 'backup'@'localhost';
mysql> flush privileges;

2）完整备份
innobackupex --host=127.0.0.1 --user=backup --password=backup --defaults-file=/data/mysql/mysql3306/etc/my.cnf /data/mysql/mysql3306/myback
xtrabackup_checkpoints 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)
都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
xtrabackup_binlog_info mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
xtrabackup_binlog_pos_innodb 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
xtrabackup_binary 备份中用到的xtrabackup的可执行文件；
backup-my.cnf 备份命令用到的配置选项信息.

3)全量数据恢复
service mysqld stop
rm -rf /data/mysql/mysql3306/data/*
innobackupex --apply-log /data/mysql/mysql3306/myback/2017-01-12_15-09-25
--apply-log 的意义在于把备份时没commit的事务撤销，已经commit的但还在事务日志中的应用到数据库
innobackupex --copy-back /data/mysql/mysql3306/myback/2017-01-12_15-09-25/
--copy-back数据库恢复，后面跟上备份目录的位置
chown -R mysql:mysql /data/mysql/mysql3306/data
service mysqld start ##如果能启动代表恢复正常

4）增量备份
首先在表中插入一些数据
mysql> insert into testBack(name) values ('add1'), ('add2'), ('add3');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
然后执行增量备份语句
innobackupex --user=backup --password=backup --incremental --incremental-basedir=/data/mysql/mysql3306/myback/2017-01-12_15-09-25/ /data/mysql/mysql3306/myback
--incremental 指定是增量备份
--incremental-basedir 指定基于哪个完整备份做增量备份，最后是增量备份保存的目录
增量备份只能对InnoDB引擎做增量备份，对MyISAM的表是完全复制

5）增量备份的恢复
（1）停库：service mysqld stop
（2）删除数据文件：rm -rf /data/mysql/mysql3306/data/*
（3）撤销备份时没有提交的事务、把备份时commit的但还在事务日志中的应用到时数据
innobackupex --apply-log --redo-only /data/mysql/mysql3306/myback/2017-01-12_15-09-25
--redo-only 指的是把备份时commit的但还在事务日志中的应用到时数据，但是还没提交的不撤消，
因为这个事务可能在增量备份中提交，假如的撤消了增量备份中就不提交，因为事务已经不完整
（4）将增量备份全并到完整备份中去
innobackupex --apply-log /data/mysql/mysql3306/myback/2017-01-12_15-09-25/ \
--incremental-dir=/data/mysql/mysql3306/myback/2017-01-12_15-27-08/
/data/mysql/mysql3306/myback/2017-01-12_15-09-25/ 这个是完整备份的目录
--incremental-dir 后跟的是增量备份的目录
这个会使增量备份中的的数据合并到完整备份中，如果还有增量备份，继续合并，恢复时恢复完整备份即可
（5）恢复数据，并起动MySQL
innobackupex --copy-back --defaults-file=/data/mysql/mysql3306/etc/my.cnf /data/mysql/mysql3306/myback/2017-01-12_15-09-25/
chown -R mysql:mysql /data/mysql/mysql3306/data
service mysqld start

检查数据

推荐一篇博客地址是:http://www.cnblogs.com/zengkefu/p/5669512.html