备份类型: 根据备份时是服务器是否在线: 热备份(HOT): 读写操作不受影响 温备份(WARM): 进可以执行读操作,写操作不能执行 冷备份(COLD): 读写操作均不能进行 根据备份时对数据文件的操作: 物理备份: 直接复制数据文件。速度快,但是跨平台移植可能受到底层文件系统格式的影响。 逻辑备份: 将数据导出至文本文件中。速度慢。但是保存文件中会丢失浮点数精度。可以使用文本处理工具进行二次处理。移植性强。备份出来的数据可能比原数据占用空间还大。 根据备份时的数据大小: 完全备份(full):备份全部数据 增量备份(incremental):仅备份上次完全备份或增量备份后变化的数据。 差异备份(differnetial):仅备份上次完全备份以来变化的数据。占用空间比增量大。 增量备份和差异备份的区别: 虽然都是对变化的数据备份,但是增量在还原时要还原此前一系列的增量+完全备份的内容。差异备份还原时只要选择最近一个备份+完全备份的内容即可。 需要备份的东西: 数据、服务器配置文件、二进制日志、事务日志 二进制日志注意点: 备份时除了使用FLUSH LOGS以外,建议将sync_binlog的值设置为1,这样可以在安全性上保证事务不会没有写入日志。 还原的时候先暂时关闭二进制日志记录功能,还原过程没必要记录二进制日志中。备份策略: 考虑以下2点: 1.数据在某个周期的变化量 2.数据还原是所需时间备份方案: 完全+增量 完全+差异热备份: MyISAM:不支持热备份,可以使用温备份 InnoDB:支持热备。可以使用xtrabackup(物理备份)和mysqldump等工具。备份工具: MYSQL自带: mysqldump 逻辑备份,对MyISAM可以实现温备份。InnoDB可以实现热备。 mysqlhotcopy 物理备份,冷备份工具。基于perl脚本,使用FLUSH TABLES和LOCK TABLES以及scp完成备份。 SELECT INTO OUTFILE SELECT语句,只能对单张表操作。 文件工具: cp 冷备份,直接复制文件 lvm 使用逻辑卷快照功能,实现几乎热备。需要手工执行FLUSH TABLES 和LOCK TABLES,然后创建快照,创建完成后执行UNLOCK TABLES,复制快照文件。 注意:MyISAM可以直接锁表,但是对InnoDB而言,因为可能会涉及到事务的操作,例如从日志同步到磁盘中,或者事务未同步到磁盘中。此时如果锁表还得等待事务同步完成。 商业工具: ibbackup InnoDB 开源工具: xtrabackup#mysqldump [OPTION] DBNAME[TBNAME1,...] 默认要提前手工创建数据库,因为备份中不包含CREATE DATABASE命令。 [OPTION] -u USERNAME -p PASSWORD -h HOST --master-data = 0|1|2 是否记录当前的二进制文件和事件位置 0 不记录二进制日志文件及其事件位置 1 以CHANGE MASTER TO的方式记录位置,可用于恢复后直接启动从服务器。 2 以CHANGE MASTER TO的方式记录位置,但是记录默认被注释。 --lock-all-tables 锁定所有表 --lock-tables --flush-logs 执行日志滚动 --single-transaction 启动热备事务,适用于InnoDB,借助于MVCC生成一个隔离级别为REPEATABLE-READ的事务。这样无论后台如何变化,看到的数据都不会发生变化。 会自动处理表锁,不能和--lock-all-tables或者--lock-tables同时使用。 --all-databases 备份所有库,包含CREATE DATABASE命令,无需手动创建库 --database DBNAME,... 备份指定库,包含CREATE DATABASE命令,无需手动创建库 --events 备份事件调度器 --routines 备份存储过程和函数 --triggers 备份触发器 备份思路: 锁表 --> 磁盘同步数据-->刷新二进制日志 --> 备份二进制日志文件(最好全部保留) --> 完全备份 --> 增量备份 还原思路: 如有必要重新初始化数据库 --> 临时关闭二进制日志记录 --> 还原完全备份 --> 还原增量备份 --> 即时点还原(将编号最大的二进制日至文件通过管道送给mysql服务器 )--> 开启二进制日志记录 备份策略: 完全+增量: 还原策略: 完全+增量+即时点 实例: 1.备份jiaowu数据库,假设为MyISAM引擎,使用温备份: mysql> LOCK TABLES; mysql> FLUSH TABLES; 或 mysql>FLUSH TABLES WITH READ LOCK; mysql>FULSH LOGS; mysql>SHOW BINARY LOGS; #mysqldump -uroot -p jiaowu> /root/backup/jiaowu.sql mysql>UNLOCK TABLES; 或 #mysqldum -uroot -p --master-data=2 --lock-all-tables --flush-logs jiaowu > /root/backup/jiaowu.sql 2.备份jiaowu数据库,假设为InnoDB引擎,使用热备份: #mysqldump -uroot -p --master-data=2 --flush-logs --single-transaction jiaowu > /root/backup/jiaowu.sql 3.完全备份: #mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/backup/alldb-`date +%F-%H-%M-%S `.sql 4.增量备份: 可以备份二进制日志或者是对某表进行备份,根据具体情况而定。 假设刷新日志后,生成最新的日志为mysql-bin.000008。 mysql>FLUSH LOGS; #cp mysql-bin-* /root/binlog/ #mysqlbinlog mysql-bin-000002 >/root/backup/mysql-inc/binlog-2.sql ... #mysqlbinlog mysql-bin-000007 >/root/backup/mysql-inc/binlog-3.sql 5.即时点备份: #mysqlbinlog mysql-bin-0000008> /root/backup/mysql-inc/binlog-`date +%F+%H+%M+%S `.sql 6.假设数据所在磁盘损坏,进行还原: 初始化数据库并启动服务 #cd /usr/local/mysql #scripts/mysql_install_db --user=root --datadir=/mdata/data #service mysqld start 导入完全备份和所有增量: #mysql -uroot -p < /root/backup/alldb-xxxx.sql #mysql -uroot -p < /root/backup/mysql-inc/binlog-xxxxx.sql ... 导入即时点数据: #mysqlbinlog mysql-bin-LATEST > /root/lastbinlog.sql #mysql -uroot -p < /root/lastbinlog.sql 或 #mysqlbinlog mysql-bin-LATEST |mysql -uroot -p 6.使用5的方法虽然可以恢复,但是会被记录到二进制日志中,一般还原的操作是无需记录到二进制日志的。临时关闭当前会话的二进制日志。 mysql>SET sql_log_bin=0; mysql>SOURCE /root/backup/alldb-xxxx.sql; mysql>SOURCE /root/backup/mysql-inc/binlog-xxxxx.sql; ... mysql>SOURCE /root/lastbinlog.sql; mysql>SET sql_log_bin=1;SELECT INTO OUTFILE: 只能对单张表进行操作。恢复速度快,一般用作单个表的备份。 存放的是纯粹的数据,各个字段和值之间用制表符(tab)分隔,无其他额外信息。 备份: mysql>SELECT * INTO OUTFILE 'FILE' FROM TBNAME [WHERE ...]; 还原: mysql>LOAD DATA INFILE 'FILE' INTO TABLE TBNAME; 注意:备份文件必须放在mysql用户有访问权限的目录。还原的表得提前创建表格式。 实例: 1.备份tutors表: mysql>use jiaowu; mysql>SELECT * INTO OUTFILE ‘/root/backup/selbackup/tutors.txt’ FROM tutors; 2.还原tutors表的内容到tut表中: mysql>use jiaowu; mysql>CREATE TABLE tut LIEK tutors; 或 mysql>CREATE TABLE tut (TID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Tname CHAR(20) NOT NULL ,Gender enum('F','M'),Age TINYINT NOT NULL); mysql>LOAD DATA INFILE '/root/backup/selbackup/tutors.txt’' INTO TABLE tut;LVM快照: 前提: 1.数据要在逻辑卷上 2.逻辑卷所在卷组必须有足够的空间使用快照卷 3.事务日志和数据必须在同一个卷上。 备份思路: 锁表 --> 滚动日志 --> 记录二进制日志名称和位置信息 -->创建快照 --> 释放锁 -->挂载并备份快照卷中的数据 --> 删除快照卷 -->备份二进制日志文件 恢复: 实例: 1.备份jiaowu库: mysql>FLUSH TABLES WITH READ LOCK; mysql>FLUSH LOGS; mysql>SHOW MASTER STATUS; 记录二进制日志名称和位置,为即时点还原提供依据。 或 在另一个终端上执行 #mysql -uroot -p -e `SHOW MASTER STATUS\G` > /root/backup/lvmbkup/master-`date +%F`.info #lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata mysql>UNLOCK TABLES; #mont /dev/myvg/mydata-snap /mnt -o ro #cd /mnt #cd jiaowu #mkdir -p /root/backup/lvmbackup/fullbackup-`data+%F` #cp -a ./* /root/backup/lvmbackup/fullbackup-xxxxx/ #cp -a ../ibdataX /root/backup/lvmbackup/fullbackup-xxxxx/ 注意:默认InnoDB的表空间文件为共享,复制时需要一并复制。建议将innodb_file _per_table=1,使用单独表空间文件。这样直接复制文件夹即可。 #umount /mnt #lvremove --force /dev/myvg/mdata-snap #cd /root/backup/lvmbackup/fullbackup-xxxxx/ #rm -f mysql-bin.* #cd /mydata/data #cat /root/backup/lvmbkup/master-xxxxx.info #mysqlbinlog --start-datetime='2015-07-25 07:01:04' mysql-bin.00000X mysql-bin.0000X+1 ... > /root/backup/lvmbackup/incremental-`date +%F-%H-%M-%S`.sql 2.还原: #service mysqld stop #cp -a /root/backup/lvmbackup/fullbackup-xxxx/* /mydata/data #service msyqld start #msyql -uroot -p mysql>SET sql_log_bin=0; mysql>SOURCE /root/backup/lvmbackup/incremental-xxxxx.sql; mysql>SET sql_log_bin=1;xtrabackup: 一般直接在服务器上使用此工具。 备份思路: 完全备份--> 增量备份--> 即时点备份 还原思路: 完全备份准备 --> 增量备份准备 --> 关闭服务器 --> 还原完全备份 --> 修改恢复数据的属主属组 --> 即时点还原 --> 重做一次完全备份(重要) InnoDB: full incremental HOT MyISAM: WRAM full XtraDB 增强功能的InnoDB,若想使用则在编译mysql时替换掉InnoDB引擎。 安装: #yum localinstall percona-xtrabackup-xxxx.rpm 二进制文件: /usr/bin/innobackupex perl脚本,调用具体的工具完成操作。 备份目录中文件: xtrabackup_checkpoints 记录备份类型(完全|增量...)、备份状态(prepared状态等)、日志序列号(LSN)等。 LSN:InnoDB每个储存数据的数据块都有对应的日志序列号,如果块中的数据发生了改变,则对应的LSN会+1 内容如下: backup_type= from_lsn= 起始的LSN号,完全备份从0开始 to_lsn= 结束的LSN号 last_lsn= 最后一次的LSN号,如果是增量备份则会从此号开始。 xtrabackup_binlog_info 二进制日志文件和位置 xtrabackup_binlog_pos_innodb 二进制文件和位置以及用于InnoDB或XtraDB的二进制文件和位置 xtrabackup_binary 备份时使用的具体工具 xtrabackup_logfile backup~my.cnf 备份命令用到的配置选项信息 #innobackupex [OPTION] DIR [OPTION] --user=DBUSERNAME --password=DBUSERPASSWD --host=DBHOST --port=DBSERVERPORT --no-timestamp 禁止自动生成以时间命名的文件夹 --apply-log 对备份好的数据进行准备 --use-memory NUM 指定准备时占用的内存大小,默认不指定为100M --copy-back 恢复完全数据 --incremental 增量备份 --incremental-basedir=FULLBACKUPDIR 指定完全备份文件路径 --incremental-dir=INCRBACKUPDIR 指定增量备份的文件路径 --redo-only 指定只进行redo操作。 --export 执行导出操作 --incremental-force-scan 增量备份时强制扫描全部的数据页。在mariadb上增量备份不指定此参数会报错。 实例: 1.创建专门用于备份的mysql账号: mysql>CREATE USER 'bkuser'@'localhost' IDENTIFIED BY '123456' mysql>REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkuser'; mysql>GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkuser'; mysql>FLUSH PRIVILEGES; 2.完全备份: #innobackupex --user=bkuser --password=123456 --host=localhost /root/backup/xtrabackup/full/ 或 #innobackupex --user=root --password=123456 --host=localhost /root/backup/xtrabackup/full/ 4.第一次增量备份: 对MyISAM只能是完全备份。 #innobackupex --incremental /backup/xtrabackup/incr/ --incremental-basedir=/root/backup/xtrabackup/full/xxxxx/ 5.第二次增量备份: 第一次增量备份完成后,再进行增量备份的时候。--incremental-basedir要指定增量备份的目录而不是完全备份的目录 #innobackupex --incremental /backup/xtrabackup/incr/ --incremental-basedir=/root/backup/xtrabackup/incr/xxxxx1/ 以后增量备份只要指定上次增量即可 6.准备: 备份好的数据不能直接还原,其中可能有部分事务没有提交或正在进行。已经执行的的事务同步到数据中,正在执行的事务进行回滚。 完全备份准备: #innobackupex --apply-log --redo-only /root/backup/xtrabackup/xxxxx/ 第一次增量备份准备: #innobackupex --apply-log --redo-only /root/backup/xtrabackup/full/xxxxx/ --incremental-dir=/root/backup/xtrabackup/incr/xxxxx1/ 注意:有些在完全备份中未能提交的事务在后续增量备份中已经提交过。如果不指定--redo-only选项则有可能这些时间会被undo,造成恢复后无法提交。 第二次增量备份准备: #innobackupex --apply-log --redo-only /root/backup/xtrabackup/full/xxxxx/ --incremental-dir=/root/backup/xtrabackup/incr/xxxxx2/ 注意:所有备份的准备完成后后,所有的数据都将合并到完全备份中,恢复时只恢复完全备份即可。 7.恢复: 备份二进制日志以便即时点恢复: mysql>FLUSH LOGS #service mysqld stop #cd /mdata/data #cp mysql-bin.0000* /root/backup/binlogfile 还原完全备份: #innobackupex --copy-back /root/backup/xtrabackup/full/xxxxx/ 修改恢复完成数据的属主属组: #cd /mdata/data #chown -R mysql:mysql ./* 启动服务 #service mysqld start 即时点还原 #cd /root/backup/binlogfile #mysqlbinlog mysql-bin-000001 > bin0001.sql ... mysql>SET sql_bin_log=0 mysql>SOURCE /root/backup/binlogfile/bin0001.sql ... 9.导入导出单张表: 注意:要进行此操作,必须保证导出操作所在数据库的innodb__file_per_table在创建要导出的表之前是启用的。同时要保证导入操作所在的数据库innodb_file_per_table和innodb_expand_import也是要启用的。 导出: 导出操作是在完全备份的准备阶段进行。会为每个innodb表的表空间创建以.exp结尾的文件。 #innobackupex --apply-log --export /root/backup/xtrabackup/full/xxxxx/ 复制.exp和.ibd文件 导入: 创建innodb表: mysql>CREATE TABLES TBNAME (...) ENGINE=InnoDB; 删除表空间: mysql>ALTER TABLE DBNAME.TBNAME DISCARD TABLESPACE; 导入.exp文件和.ibd文件: 复制.exp和.ibd文件到数据目录下: mysql>ALTER TABLE DBNAME.TBNAME IMPORT TABLESPACE;
mysql fulsh_mysql之备份和恢复(msyqldump、LVM、xtrabackup)
最新推荐文章于 2024-05-20 19:22:08 发布