这是一步步从头学习MySQL的笔记历程&＃xff0c;本人在学习之前只接触过少量SQL Server。
第一天历程&＃xff1a;压榨自己的学习能力极限&＃xff0c;每天学习新内容之MySQL服务器&＃xff01;&＃xff08;一&＃xff09;
第二天历程&＃xff1a;压榨自己的学习能力极限&＃xff0c;每天学习新内容之MySQL服务器&＃xff01;&＃xff08;二&＃xff09;
第三天历程&＃xff1a;压榨自己的学习能力极限&＃xff0c;每天学习新内容之MySQL服务器&＃xff01;&＃xff08;三&＃xff09;
接下来每天都将更新~相信你通过笔记&＃xff0c;一定能从零学会MySQL~
废话不多说&＃xff0c;开始今天的学习压榨~&＃xff01;
MySQL的备份和恢复
我们知道如果把表数据放在Raid1上&＃xff0c;一块硬盘坏了我们可以继续使用&＃xff0c;那么是否就意味着我们可以不用备份了呢&＃xff1f;当然不是&＃xff0c;假设我们不小心执行了”drop database mydb”。是否可以备份&＃xff0c;只要执行一个删除语句&＃xff0c;看是否能将其还原回来&＃xff0c;这才是备份。而Raid只是在硬件级别上作出的冗余备份。
对于我们Mysql来说&＃xff0c;最重要的就是数据本身&＃xff0c;所以我们必须能确保数据本身不会出现任何问题。而当出现问题的时候&＃xff0c;我们如何能将损失降到最低&＃xff0c;尽可能的将数据找回来&＃xff0c;此时&＃xff0c;我们就需要用到备份。
但是注意&＃xff0c;备份永远解决不了数据的恢复&＃xff0c;还原才能。很多人仅仅只是备份了&＃xff0c;备份之后的数据&＃xff0c;将备份的东西随手一扔&＃xff0c;或者备份完成之后不检查自己的备份到底是否能够使用。所以备份解决不了任何问题&＃xff0c;还原才能。而作为一个好的DBA&＃xff0c;完全掌握备份和恢复&＃xff0c;才是工作的重中之重。
数据库的日志文件&＃xff1a;
日志一般有两类&＃xff1a;
1.事物日志&＃xff1a;实现为数据库服务器提供以下几种功能&＃xff1a;
→将随机IO事件转换为顺序IO&＃xff08;可以大大提高数据库性能&＃xff09;&＃xff0c;所有的写操作不是被立即放在磁盘上去的&＃xff0c;而是先放到了在磁盘上的一段连续空间&＃xff1a;事物空间。之后再由RDBMS控制文件内容将其同步进硬盘内。这样让硬盘在寻道的时候速度就非常快&＃xff0c;所以理论上顺序IO比随机IO快N倍。
→事件重放&＃xff08;撤销&＃xff09;的基础。能够保证数据库的事物在掉电之后依然完整&＃xff0c;再下次开启之后依然能将数据同步到数据库中。
2.事件日志&＃xff1a;过去的一段时间内某个关键时刻发生了什么事情
其实就是历史事件&＃xff0c;只记录某点某时某刻做了什么事情。而事物日志则是记录了做这些事情怎么做的&＃xff0c;里面操作了什么数据&＃xff0c;操作了多少等等精确的内容。事务日志一般只记录写操作。
一&＃xff1a;MySQL的日志分类&＃xff1a;
错误日志 the error log
二进制日志 the binary log
普通的查询日志 the general guery log :通用查询&＃xff0c;谁查询了什么的等
慢查日志 the slow query log &＃xff1a;执行过程超出了某些特定时间长度的日志&＃xff08;比如记录超过1秒查询的那次任务&＃xff09;
中继日志 the relay log
默认情况下&＃xff0c;我们的MySQL没有启动任何日志。而启动MySQL的那个脚本&＃xff0c;帮我们开启了错误日志&＃xff0c;错误日志的文件在data下&＃xff0c;叫做*.err
二&＃xff1a;错误日志&＃xff1a;
不仅仅记录了错误信息&＃xff0c;还主要记录了MySQL进程在启动和停止的时候产生的信息&＃xff0c;如果我们使用了主从复制的功能的话&＃xff0c;在服务器上启动复制的进程的时候&＃xff0c;进程信息也会记录在错误日志中。
如何去启用error log呢&＃xff1f;
只需要在服务器的主配置文件里&＃xff0c;定义&＃xff1a;
log-error &＃61; /var/log/mysqld.err
(如果改到其他位置&＃xff0c;请确保Mysql用户在那个位置上有写权限)
如果没有定义这样日志的话&＃xff0c;启动时的错误信息&＃xff0c;都会输出到屏幕上来。所以我们的Mysql
错误日志的命名格式&＃xff1a;服务器名.err
SHOW VARIABLES LIKE ‘%log%’&＃xff0c;可以看到关于log的信息&＃xff0c;
log_error&＃xff1a;这个值就是我们错误日志文件的路径&＃xff0c;我们也可以手动设置修改它的值。
log_output:日志输出的保存格式&＃xff0c;默认都是FILE&＃xff0c;保存为一个文件&＃xff0c;其实也可以保存到表中
三&＃xff1a;二进制日志&＃xff1a;
有点类似于事物日志&＃xff0c;它也能提供事物重放功能&＃xff0c;但是它不能取代事物日志&＃xff0c;它存在的主要目的&＃xff0c;可以提供&＃xff1a;
1.增量备份机制
2.基于时间点的恢复&＃xff08;可以手动控制&＃xff0c;恢复到什么位置&＃xff0c;什么时间恢复等&＃xff09;
3.复制架构的基础&＃xff08;只要把二进制日志复制到另一台机子上&＃xff0c;执行二进制日志中的内容&＃xff0c;则&＃xff09;
二进制日志的开启&＃xff0c;会给服务器增加很大的压力&＃xff0c;但是考虑到二进制日志的重要性&＃xff0c;所以还是要开启的。
二进制日志的定义&＃xff1a;
默认在数据文件目录data下叫做&＃xff1a;xxx-bin.XXXXX
而xxx-bin.index则是一个索引文件&＃xff0c;记录到底现在有多少二进制文件&＃xff0c;以及正在写哪个。
如果要自己定义的话&＃xff0c;二进制文件路径&＃xff1a;
log-bin&＃61;/data/logs/binary/changelog
二进制文件索引文件修改&＃xff1a;
log-bin-index&＃61;/data/logs/relay/binarylog.index
四&＃xff1a;慢查日志/查询日志&＃xff1a;
实现定义那些执行时间超过了特定时长的查询事件。默认是10秒。默认是没有开启的。
long_query_time &＃61;定义默认时间
slow_query_log&＃61;是否开启慢查日志 ON OFF
slow_query_log_file&＃61;定义慢查日志的路径及文件名
general_log&＃61;是否开启查询日志ON/OFF
general_log_file&＃61;查询日志的保存路径以及文件名
log_output&＃61;将日志保存在哪
五&＃xff1a;如何做日志滚动&＃xff1a;
为了让日志文件保持特定大小以免超出了我们服务器响应的能力。我们要做日志滚动
flush logs命令&＃xff1a;手动其滚动
一般只对binary logs有用&＃xff0c;它会将当前的二进制日志关闭&＃xff0c;然后新生成一个新的二进制日志。对于其他的日志文件&＃xff0c;我们完全可以手动去滚动&＃xff0c;不需要这个命令。
六&＃xff1a;二进制日志的删除方式&＃xff1a;
对于慢查日志和查询日志的文件&＃xff0c;滚动过后不想要了可以直接删除&＃xff0c;但是对于二进制文件来说&＃xff0c;我们不能手动实用rm命令对其进行删除。而需要使用
PURGE BINARY LOGS命令&＃xff1a;删除一个二进制日志之前的内容
mysql> PURGE BINARY LOGS TO ‘mysql-bin.00007’;
包括00007之前的所有文件都自动给你清除。
mysql> PURGE BINARY LOGS BEFORE ‘2011-3-5 23:00:00’;
删除这个时间点之前的所有二进制日志文件
二进制日志文件默认是1G:
max_binlog_size&＃61;可以定义默认大小。
日志的备份和恢复
对于我们Mysql的属于一个在线的数据库来讲&＃xff0c;我们在备份的时候&＃xff0c;可能数据也在一直被写入&＃xff0c;而如果我们强行备份的话&＃xff0c;可能会导致备份中存入的数据的损坏。以及数据的不一致性&＃xff0c;所以我们要采用不同的类型进行备份&＃xff1a;
一&＃xff1a;备份的类型&＃xff1a;&＃xff08;从对象来分&＃xff09;
逻辑备份&＃xff08;逻辑导出&＃xff09;&＃xff1a;备份的是数据库的逻辑结构&＃xff0c;备份的一般都是各种逻辑语句。&＃xff08;比如CREATE DATABESES&＃xff0c;INSERT之类的&＃xff09;也叫logical export逻辑导出。逻辑备份的力度比较小&＃xff0c;可以做到仅仅备份一个表甚至一个表中的某些行。
物理备份&＃xff08;裸备份&＃xff09;&＃xff1a;复制对应数据库的数据文件。对于不同的数据库引擎&＃xff0c;物理备份的方法也是不一样的&＃xff0c;物理备份也叫裸备份。
对于MyISAM这样的没有表空间的引擎来说&＃xff0c;物理备份的速度要远快于逻辑备份。
对于InnoDB这样的引擎来说&＃xff0c;它直接创建了固定大小的文件&＃xff0c;然后往里填充&＃xff0c;物理备份就没有优势了。
逻辑备份的优点&＃xff1a;
1.在备份速度上两种备份要取决于不同的存储引擎
2.物理备份的还原速度非常快。但是物理备份的最小力度只能做到表。
3.逻辑备份保存的结构通常都是纯ASCII的&＃xff0c;所以我们可以使用文本处理工具来处理。
4.逻辑备份有非常强的兼容性&＃xff0c;而物理备份则对版本要求非常高
5.逻辑备份也对保持数据的安全性有保证。
逻辑备份的缺点&＃xff1a;
1.逻辑备份要对RDBMS产生额外的压力&＃xff0c;而裸备份无压力
2.逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
3.逻辑备份可能会丢失浮点数的精度信息
二&＃xff1a;备份的类型&＃xff1a;&＃xff08;从数据集来分&＃xff09;
完全备份&＃xff1a;备份所有数据
增量备份&＃xff1a;只备份从上一次完全备份或增量备份以来所增加的数据&＃xff08;通过二进制日志来实现增量备份&＃xff0c;依靠二进制文件&＃xff09;
差异备份&＃xff1a;每次备份都备份从上一次完全备份以来所有新增的数据
对于Mysql来讲&＃xff0c;我们一般都是结合用&＃xff0c;比如完全&＃43;增量1&＃43;增量2&＃43;增量3&＃xff0c;完全&＃43;差异&＃xff0c;不会单独使用一种备份的。
DBA的小把戏&＃xff1a;
在每一张表背后&＃xff0c;在别人所有加入的字段后都加了一个timestamp。当有人改变数据的时候&＃xff0c;则将timestamp修改为改变数据的时间&＃xff0c;这样就可以在数据改变之后&＃xff0c;备份一段时间的数据。
三&＃xff1a;备份的类型&＃xff1a;&＃xff08;根据服务器状态&＃xff09;
冷备&＃xff1a;干净的关闭数据库&＃xff0c;直接备份&＃xff0c;此时可以做各种备份。
温备&＃xff1a;服务不关闭&＃xff0c;但是只允许查询操作&＃xff0c;然后后台进行备份。
热备&＃xff1a;不影响读写&＃xff0c;服务在线的备份。
一般来讲非事务性的引擎都不支持热备份&＃xff0c;比如MyISAM。但是热备通常是各个组织都非常需要的&＃xff0c;所以非事物引擎都采用其他的概念&＃xff0c;达到几乎是热备的概念&＃xff0c;&＃xff08;比如LVM&＃xff09;。
对于事务性引擎&＃xff0c;热备就非常简单的实现。像InnoDB来说&＃xff0c;如果想使用干净的逻辑的热备的话&＃xff0c;只要使用官方所提供的一个工具&＃xff0c;就能完全达到。
四&＃xff1a;备份都备份什么&＃xff1f;
我们备份&＃xff0c;一般备份以下几个部分&＃xff1a;
1.数据文件
2.日志文件&＃xff08;比如事务日志&＃xff0c;二进制日志&＃xff09;
3.存储过程&＃xff0c;存储函数&＃xff0c;触发器&＃xff08;这类MySQL数据库&＃xff09;
4.配置文件&＃xff08;十分重要&＃xff0c;各个配置文件都要备份&＃xff09;
5.用于实现数据库备份的脚本&＃xff0c;数据库自身清理的Croutab等……
由于备份的机制不同&＃xff0c;所以我们建议千万不要在一个表中混合使用数据引擎。
五&＃xff1a;常用的备份工具
mysqldump&＃xff1a;逻辑备份工具&＃xff0c;可以用于所有的存储引擎&＃xff0c;但是在任何一个存储引擎上只支持温备&＃xff0c;如果使用INNODB的话可以支持热备&＃xff0c;备份速度中速&＃xff0c;还原速度非常非常慢&＃xff0c;但是在实现还原的时候&＃xff0c;具有很大的操作余地。具有很好的弹性。
SELECT INTO OUTFILE&＃xff1a;MySQL的一个命令&＃xff0c;是个逻辑备份&＃xff0c;备份力度非常细&＃xff0c;可以指定某个表的某些行&＃xff0c;只支持到温备&＃xff0c;备份速度慢&＃xff0c;恢复速度慢&＃xff0c;可操作性比较灵活。
mk-parallel-backup&＃xff1a;一个perl脚本,是一个人开发的&＃xff0c;各种mk脚本&＃xff0c;它的主要作用是模拟了mysqldump的功能&＃xff0c;能够实现在多颗CPU上实现并行备份的能力&＃xff0c;比mysqldump要快一些&＃xff0c;备份速度中等&＃xff0c;恢复速度中等。
ibbackup:对于INNODB可以实现温备&＃xff0c;备份和还原速度都很快&＃xff0c;这个软件它的每服务器授权版本是5000美元……-。-
BACKUP command&＃xff1a;这个基本上已经被废弃了。
Filesystem(copy files)&＃xff1a;复制命令&＃xff0c;只能实现冷备&＃xff0c;物理备份。使用归档工具&＃xff0c;cp命令&＃xff0c;对其进行备份的&＃xff0c;备份速度快&＃xff0c;还原速度几乎最快&＃xff0c;但是灵活度很低&＃xff0c;可以跨系统&＃xff0c;但是跨平台能力很差。
Snapshot&＃xff1a;基于快照(LVM&＃xff0c;ZFS)的物理备份&＃xff0c;速度非常快&＃xff0c;几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用&＃xff0c;所以备份速度比较快&＃xff0c;恢复速度比较快&＃xff0c;没有什么弹性空间&＃xff0c;而且LVM的限制&＃xff1a;不能对多个逻辑卷同一时间进行备份&＃xff0c;所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
mysqlhostcopy&＃xff1a;基本上属于冷备的范畴&＃xff0c;而且只能用于MyISAM引擎&＃xff0c;物理备份&＃xff0c;速度比较快。
备份方式1&＃xff1a;mysqldump的使用&＃xff1a;
逻辑备份工具&＃xff1a;使用SELECT语句将数据都转换出来&＃xff0c;并将其保存成ASCII文件。
备份使用格式&＃xff1a;mysqldump db_name tbl_name
备份的结果要使用输出重定向的方式&＃xff0c;保存成一个文件比如&＃xff1a;
mysqldump db table > /root/tb1.sql
还原的时候使用&＃xff1a;
mysql
mysql> SOURCE /root/tb1.sql
它的常用选项有&＃xff1a;
--databases db1,db2,db3&＃xff1a;一次备份多个数据库
--all-databases&＃xff1a;一次备份整个数据库上的所有数据库
--events&＃xff1a;备份create events创建事件
--extended-insert&＃xff1a;备份扩展的insert语句&＃xff0c;恢复的时候使用一个insert语句进行插入&＃xff0c;要比单个使用Insert速度快&＃xff0c;默认是启用的。关闭使用 --skip-extended-insert。
--flush-logs&＃xff1a;主要用于实现刷新日志到磁盘中去&＃xff0c;为了保证一致性的&＃xff08;大多数情况下也是需要的。&＃xff09;
--lock-all-tables&＃xff1a;在备份之前对所有表进行加锁&＃xff08;这个是必须的&＃xff01;&＃xff09;
--lock-tables tb1,tb2&＃xff1a;锁定某些个表&＃xff08;一般很少用&＃xff09;
--routines&＃xff1a;同时备份存储过程存储函数
--triggers&＃xff1a;备份触发器
--single-transaction&＃xff1a;对于InnoDB的引擎提供一个一致性的视图&＃xff0c;然后备份。这样的备份完全是一个热备&＃xff0c;可以不锁表的
--master-data&＃61;n&＃xff1a;复制的&＃xff0c;为了能够启用实现新的复***务器。一般常用的是2.
--no-data&＃xff1a;偶尔有用&＃xff0c;只备份数据库和表的定义
--where “WHERE clause”只备份数据库中某些表或者某些行的数据
1.备份表hellodb中的表t1
mysqldump -uroot -p hellodb t1 > /root/t1.sql
t1.sql中使用 “--” 或者使用 “/*”都是注释信息
2.恢复表hellodb中的表t1
mysql -uroot -p hellodb
3.份份数据库hellodb
mysqldump --databases hellpdb > /root/hellodb.sql
4.恢复数据库hellodb
mysql -uroot -p
真正备份的时候&＃xff0c;要先锁定
mysql> FLUSH TABLES;
mysql> FLUSH TABLES WITH READ LOCK ##给加锁
mysqldump --databases hellpdb > /root/hellodb.sql ##快速备份
mysql> UNLOCK TABLES; ##解锁
5.也可以自动让其加锁&＃xff0c;并自动备份触发器&＃xff1a;
mysqldump --lock-all-tables --triggers --databases hellodb > /root/hellodb.sql
备份出来之后完全可以将其压缩一下&＃xff0c;比如用Gzip压缩
备份方式2&＃xff1a;SELECT INTO OUTFILE:命令
用于备份某些表中的某些数据,这里备份已经要保存在/tmp下&＃xff0c;它没有关于表格式的定义&＃xff0c;如果用它来恢复的话&＃xff0c;必须要确定表和表格式都已经存在.
备份格式&＃xff1a;SELECT * INTO OUTFILE ‘/tmp/t1.txt’FROM t1;
还原格式&＃xff1a;LOAD DATA INFILE ‘/tmp/t1.txt’INTO TABLE t1;
1.从t2表中备份所有h开头的行
mysql> SELECT * INTO OUTFILE ‘/tmp/t2.sql’FROM t2 WHERE CNAME LIKE ‘h%’
2.还原&＃xff1a;将表中的数据还原回来
mysql> LOAD DATA INFILE ‘/tmp/t2.sql’ INTO TABLE t3;
备份方式3&＃xff1a;基于LVM来进行物理备份
备份这类的前提:
1.数据文件必须在独立的LV上。
2.对数据库中的表施加读锁。
3.立即对数据在的LV做快照&＃xff1b;
4.释放数据库的读锁&＃xff1b;
5.挂载快照卷&＃xff0c;备份数据文件&＃xff08;如果是Innodb的引擎&＃xff0c;一定要备份事物日志&＃xff09;
6.删除快照卷
对于Innodb数据引擎的备份还需要确保&＃xff1a;
Innodb的数据和事务日志在同一个LVM上
sync_binlog&＃61;1
log_bin&＃61;ON
innodb_support_xa&＃61;ON
使用物理备份做一次全库备份&＃xff1a;
mysql> FLUSH TABLES WITH READ LOCK; ##给全表加读锁
mysql> \q ##快速退出
lvcreate -L 50M -n mysnap -s /dev/myvg/mydata ##快速创建快照
mysql ##快速进入Mysql
mysql> UNLOCK TABLES; ##快速解锁
mysql> \q ##退出
mount /dev/myvg/mysnap /mnt ##挂载快照卷
cd /mnt
tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile* mysql-bin.* ##使用tar压缩归档备份
umount /mnt ##卸载快照卷
lvremove --force /dev/myvg/mysnap ##删除快照卷
假设内容已经都坏了&＃xff0c;重新安装好mysql之后&＃xff0c;将刚才的所有内容恢复回来。
先别启动mysqld
cd /mydata/data ##进到目录内
tar xf /root/mysql.tar.bz2 -C ./ ##解压归档文件
service mysqld start ## 重启Mysqld
对于我们快照完之后&＃xff0c;又新增的内容的恢复&＃xff0c;我们就需要使用二进制日志文件来恢复了
查看二进制日志文件&＃xff1a;mysqlbinlog
常用的选项&＃xff1a;
--start-position&＃xff1a;从哪个事件位置开始
--end-position&＃xff1a;从哪个事件结束
--start-datetime&＃xff1a;指定从哪个时间点开始
--start-datatime&＃61;”2011-09-05 11:39:40”
--stop-datetime&＃xff1a;从哪个时间点结束
--start-datatime&＃61;”2011-09-05 12:00:00”
二进制日志文件内容&＃xff1a;
at :事件开始的位置&＃xff08;整个mysql二进制日志中的位置&＃xff09;
年月日 时分秒 服务器id 下一个事件开始的位置线程号语句发生时间 执行结果 执行内容 时间戳
SHOW BINLOG EVENTS,查看事件点
查看此时正在被使用的事物日志文件&＃xff1a;以及下一次事件开始的时候起始的位置
SHOW MASTER STATUS;
当我们需要单独备份一个正在使用的事件日志的时候&＃xff0c;
SHOW MASTER STATUS; ##查看当前事件日志
FLUSH LOGS ; ##手动滚动日志以让其写入新的日志
此时再进行将上一个日志文件进行备份
一次完整的物理备份&＃43;基于二进制的差异备份&＃xff0c;以及完全恢复的过程&＃xff1a;
mysql> FLUSH TABLES WITH READ LOCK; ##给全表加读锁
mysql> SHOW MASTER STATUS; ##记录事件日志的日志文件和时间起始位置
mysql> \q ##快速退出
lvcreate -L 50M -n mysnap -s -p r /dev/myvg/mydata ##快速创建快照
mysql ##快速进入Mysql
mysql> UNLOCK TABLES; ##快速解锁
mysql> \q ##退出
mount /dev/myvg/mysnap /mnt ##挂载快照卷
cd /mnt
tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile* ##使用tar压缩归档备份
umount /mnt ##卸载快照卷
lvremove --force /dev/myvg/mysnap ##删除快照卷
mysqlbinlog --start-position 301 mysql -bin.000006 > /root/mysql2.incr.sql ##备份二进制文件
此时如果数据库坏了&＃xff0c;需要全盘恢复。
chown -R mysql . ##重装mysql&＃xff0c;先修改属主
scripts/mysql_install_db --datadir&＃61;/mydata/data/ --user&＃61;mysql ##重装mysql&＃xff0c;指定我们的目录
chown -R root . ##重新恢复属主
cd /mydata/data/ ##进入工作目录
tar xf /root/mysql2.tar.bz2 -C . ##将备份解压至工作目录
mysql 恢复新增的二进制日志到数据库中
本文出自 “For The Dream” 博客&＃xff0c;请务必保留此出处http://foreveryan.blog.51cto.com/3508502/658012
京公网安备 11010802041100号