目录系列教程一、备份策略赘述1、备份的类型2、备份需要考虑的因素3、备份的目标4、备份工具二、备份方案1、cp + tar == 物理冷备2、lvm快照 + binlog == 几乎
mysql系列之开篇 Mysql关系型数据库基础概念
MySQL系列之一 MariaDB-server安装
MySQL系列之二 多实例配置
MySQL系列之三 基础篇
MySQL系列之四 SQL语法
MySQL系列之五 视图、存储函数、存储过程、触发器
MySQL系列之六 用户与授权
MySQL系列之七 MySQL存储引擎
MySQL系列之八 MySQL服务器变量
MySQL系列之九 mysql查询缓存及索引
MySQL系列之十 MySQL事务隔离实现并发控制
MySQL系列之十一 日志记录
MySQL系列之十二 备份与恢复
MySQL系列之十三 MySQL的复制
MySQL系列之十四 MySQL的高可用实现
MySQL系列之十五 MySQL常用配置和性能压力测试
类型1:
类型2:
类型3:
mysqldump工具
:逻辑备份工具,适用所有存储引擎温备;支持完全或部分备份;对InnoDB存储引擎支持热备;Schema(数据库的定义)和数据存储在一起。
用法:
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases
选项:
-A:备份所有库
-B db_name1,[db_name2,...]:备份指定库
-E:备份相关的所有event scheduler
-R:备份所有存储过程和存储函数
--triggers:备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--master-data={1|2}:
1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定默认为1
2:记录为注释的CHANGE MASTER TO语句,注意:此选项会自动关闭--lock-tables功能,自动打开--lock-all-tables功能(除非开启--single-transaction)
-F:备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A时,会导致刷新多次数据库,在同一时刻执行转储和日志刷新,则应同时使用--flush-logs和-x,--master-data或-single-transaction,此时只刷新一次;建议:和-x,--master-data或 --single-transaction一起使用
--compact 去掉注释,适合调试,生产不使用
-d:只备份表结构
-t:只备份数据,不备份create table
-n:不备份create database,可被-A或-B覆盖
--flush-privileges:备份前刷新授权表,备份mysql库或相关时需要使用
-f:忽略SQL错误,继续执行
--hex-blob:使用十六进制符号转储二进制列(例如,“abc”变为0x616263),受影响的数据类型包括BINARY, VARBINARY,BLOB,BIT
-q:不缓存查询,直接输出,加快备份速度
MyISAM备份选项:支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
-x,--lock-all-tables:加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
InnoDB备份选项:支持热备,可用温备但不建议用
--single-transaction:此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。
在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),需要保证没有其他连接使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
此选项和 --lock-tables(此选项隐含提交挂起的事务)选项是相互排斥备份大型表时,建议将--single-transaction选项和--quick结合一起使用
InnoDB建议备份策略:
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
MyISAM建议备份策略:
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
由Percona公司提供的mysql数据库备份工具,开源的能够对innodb和xtradb数据库进行热备的工具;
xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表;
innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和 MySQL Server 发送命令进行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在xtrabackup 之上做了一层封装实现的;
虽然目前一般不用 MyISAM 表,只是 MySQL 库下的系统表是 MyISAM 的,因此备份基本都通过 innobackupex 命令进行;
xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:innobackupex 功能全部集成到 xtrabackup 里面,只有一个 binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,并且Innobackupex在下一版本中移除,建议通过xtrabackup替换innobackupex。
使用innobakupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHive表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,innobackupex还会在备份目录中创建如下文件:
用法:
innobackupex --user=DBUSER --passWord=DBUSERPASS /path/to/BACKUP-DIR/
选项:
--user:该选项表示备份账号
--password:该选项表示备份的密码
--host:该选项表示备份数据库的地址
--databases:该选项接受的参数为数据名,如果要指定多个数据库,彼此间需要以空格隔开;如:"xtra_test dba_test",同时,在指定某数据库时,也可以只指定其中的某张表。如:"mydatabase.mytable"。该选项对innodb引擎表无效,还是会备份所有innodb表
--defaults-file:该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
--incremental:该选项表示创建一个增量备份,需要指定--incremental-basedir
--incremental-basedir:该选项指定为前一次全备份或增量备份的目录,与--incremental同时使用
--incremental-dir:该选项表示还原时增量备份的目录
--include=name:指定表名,格式:databasename.tablename
--apply-log:一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory:该选项表示和--apply-log选项一起使用,prepare 备份的时候,xtrabackup做crash recovery分配的内存大小,单位字节。也可(1MB,1M,1G,1GB),推荐1G
--export:表示开启可导出单独的表之后再导入其他Mysql中
--redo-only:此选项在prepare base full backup,往其中merge增量备份时候使用
--copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back:这个选项与--copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地。这个选项移除backup文件,用时候必须小心。使用场景:没有足够的磁盘空间同事保留数据文件和Backup副本
注意:
1)datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则--copy-backup选项不会覆盖;
2)在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中;
3)由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,chown -R mysql:mysql /data/mysqldb
将数据目录打包压缩备份,需要停服务,不推荐
1)备份:
~]# mkdir /backup
~]# systemctl stop mariadb #停止服务
~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #打包压缩
backup]# systemctl start mariadb
2)还原:
~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf #将损坏的库删除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz #解压打包的数据库文件
backup]# cp -av var/lib/mysql/ /var/lib/ #还原
backup]# systemctl start mariadb #启动服务,恢复成功
1)备份:需要将数据库目录存放到lvm逻辑卷上
~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf #将损坏的库删除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz #解压打包的数据库文件
backup]# cp -av var/lib/mysql/ /var/lib/ #还原
backup]# systemctl start mariadb #启动服务,恢复成功
准备lvm环境:
~]# pvcreate /dev/sda5
~]# vGCreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog} #创建数据目录和二进制日志存放目录
~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
UUID=4e3D726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0
配置数据库,模拟生成大量数据:
~]# yum install mariadb-server -y
~]# vim /etc/my.cnf
[mysqld]
datadir = /data/mysqldb #指定数据库存放路径
log_bin = /data/binlog/mariadb-bin #开启二进制日志记录,并且存放到指定路径
innodb_file_per_table = ON #开启每个表单独的表空间
~]# systemctl start mariadb
~]# mysql #连接数据库,这里省略了用户名和密码,以下都是如此
MariaDB [(none)]> CREATE DATABASE school; #创建一个测试的库
MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20); #创建一张数据表
MariaDB [school]> DELIMITER // #修改语句结束符为“//”
MariaDB [school]> CREATE PROCEDURE pro_testtb() #写一个存储过程,目的是生成十万条记录测试用
-> BEGIN
-> declare i int;
-> set i = 1;
-> while i < 100000
-> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
-> SET i = i + 1;
-> END while;
-> END//
MariaDB [school]> DELIMITER ; #记得将语句结束符再改回来
MariaDB [school]> CALL pro_testtb; #调用存储过程来
MariaDB [school]> SELECT COUNT(*) FROM testtb; #查看一下表中有十万条记录
+----------+
| COUNT(*) |
+----------+
| 99999 |
+----------+
开始备份:
MariaDB [school]> FLUSH TABLES WITH READ LOCK; #备份前切记锁表,防止用户继续写入
MariaDB [school]> FLUSH LOGS; #滚动一下二进制日志
MariaDB [school]> SHOW MASTER LOGS; #查看二进制日志的位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 30334 |
| mariadb-bin.000002 | 1038814 |
| mariadb-bin.000003 | 29178309 |
| mariadb-bin.000004 | 528 |
| mariadb-bin.000005 | 245 | #将此出记录下来,我们后边需要用到
+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_data #需要再开一个终端创建快照,不要退出mysql终端
MariaDB [school]> UNLOCK TABLES; #创建快照后第一时间解锁,小心用户投诉
~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/ #将快照挂载到/mnt
~]# cp -av /mnt/ /backup #拷贝数据到备份目录
~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap #拷贝完成后即时删除快照,影响服务器性能,到此完全备份完成~
再加点数据:
MariaDB [school]> CALL pro_testtb; #让我们模拟再来插入十万条数据
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
| 199998 | #现在是二十万条记录数据了
+----------+
2)还原:
模拟数据库损坏:
~]# rm -rf /data/mysqldb"这行
导入备份:
~]# rm -rf /data/mysqldb" 删除,撤销误删除操作
MariaDB [(none)]> SET sql_log_bin=0; #先临时关闭二进制日记记录功能
MariaDB [(none)]> source /backup/binlog.sql #导入增量备份之后的最新数据
查看确认一下数据有没有恢复完整,把my.cnf中的skip_networking删除,重启服务
到此已经恢复到了最新的状态~
1)备份单表
~]# innobackupex --include="testdb.testlog" /backup #备份表数据
~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql #备份表空间
~]# mysql -e 'DROP TABLE testdb.testlog' #模拟故障,删除testlog表
2)还原单表
~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/ #整理表数据
~]# vim /backup/desc_testdb_testlog.sql #编辑创建表空间的语句,删除以下字段
Table Create Table
testlog
~]# mysql testdb < /backup/desc_testdb_testlog.sql #导入表空间
~]# mysql testdb -e 'DESC testlog' #查看是否导入成功
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | 20 | |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE' #清除表空间
~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/ #将表数据复制到库目录
~]# chown -R mysql:mysql /var/lib/mysql/testdb/ #修改所属者和所属组
~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE' #导入表空间
本篇文章就到这里了,希望可以给你带来一些帮助,也希望您能够多多关注编程网的更多内容!
--结束END--
本文标题: MySQL系列之十二 备份与恢复
本文链接: https://www.lsjlt.com/news/129713.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0