一.数据备份的重要性
1.数据丢失的原因
1.程序错误
2.人为操作
3.磁盘故障
二.备份类型
1.物理备份
含义:对文件作操作
1.冷备份(关闭进行)
2.热备份(运行进行)
3.温备份(数据库锁定表格)
2.逻辑备份
含义:对数据库逻辑组件
-
对数据库,或者表通过命令进行倒出来写在文本中
三.数据库备份分类
1.完全备份
完全备份:每次对数据库进行完整的备份
缺点:对内存消耗比较大
2.差异备份
注释:从完全备份之后再进行备份
恢复时:恢复最新的差异备份,在恢复完全备份
3.增量备份
注释:只备份完全备份后的数据
优点:节省磁盘空间
缺点: 恢复时比较复杂,步骤更多(一个一个恢复)
四.常见的备份方法
1.物理冷备
tar打包数据库文件
2.专用备份工具
mysqldump或mysqlhotcopy常用逻辑备份工具
mysqlhotcopy 仅拥有备份myISAM和ARCHIVE表
3.启用二进制日志备份
进行增量备份时,需刷新二进制日志
4.第三方工具备份
五.MYSQL完全备份
优点:备份与恢复操作简单
缺点:数据存在大量重复
1.物理冷备份
操作:先关闭数据库
mkdir /backup 创建备份文件目录
tar zcf /backup/mysql_all-$(date+%F).tar.gz /usr/local/mysql/data
2.完全备份所有库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例:
mysqldump -u root -p --all-databases > /opt/all.sql
完全备份指定库中的部分表
mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“ -d ”选项,说明只保存数据库的表结构
#不使用“ -d ”选项,说明表数据也进行备份
例:
mysqldump -u root -p school class > /opt/school_class.sql
查看备份文件
cat /opt/备份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"
例:
cat /opt/mysql_backup |grep -v "^--" | grep -v "^/" | grep -v "^$"
2. MySQL 完全恢复
恢复数据表
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
mysql -u root -p -e 'drop table test.class;'
mysql -u root -p -e 'show tables from test;'mysql -u root -p class < /opt/test.class
mysql -u root -p -e 'show tables from test;'
mysql日志:
慢查询日志:(用来记录所有执行时间超过long_query_time)
/etc/my.cnf
错误日志
log_error=/usr/local/mysql/data/mysql_error.log
######通用查询日志
general_log=on
general_log_file=/usr/local/mysql/data/mysql_general.log
###二进制日志
log-bin=mysql-bin
slow_query_log=on
slow_query_log_file=/usr/local/mysql/data/mysql_general.log
设置完重启一下mysql
cat mysql-bin.index(查看索引文件号)
二进制日志的三种格式
STATEMENT(基于sql语句记录) ROW(基于行记录) MIXED(混合模式)
在低并发时负载比较低时基于STATEMENT进行数据记录
在高并发时使用ROW基于行进行记录
MIXED可以弥补这两种模式的缺陷
————————————————————————————
MYSQL增量备份与恢复
1.默认是基于行记录的(准确性很高,效率低,影响的数据比较多)
2.设置为MIXED模式
如何增量备份?
1.mysqladmin -u root -p flush-logs #####刷新出新的二进制日志文件
2.cp ########把旧的二进制文件移动到专门保存日志的目录中
3.查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v binlog.xxxxxxxxx
4.mysqlbinlog --no-defaults 二进制日志文件
通过二进制日志进行恢复(基于位置点,时间点,进行恢复两种方式)
—————基于位置点进行恢复————————
mysqlbinlog --no-defaults --start-position='1844' mysql-log.000006 |mysql -u root -pabc123
#从1844位置点之后进行恢复数据
如何恢复中间数据?(断点恢复。。。。从恢复位置点开始,到位置点结束)
mysqlbinlog --no-defaults --start-position='1844' -- stop-position='2118' /opt/db_bak/mysql-bin.000005 | mysql -uroot -pabc123
————————基于时间点进行恢复————————
mysqlbinlog --no-defaults --start-datetime='2022-10-26 14:10:08' --stop-datetime='2022-10-26 14:20:08' 路径 ###断点恢复
mysqlbinlog --no-defaults --start-datetime='2022-10-26 14:10:08' 路径 ###恢复指定时间后面所有
1.可每周对数据库或表进行完全备份
2.使用计划性任务每周三2点对数据库和表进行完全备份
3、可每天进行增量备份操作,生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -uroot -p123123 flush-logs #####生成新的日志
高级语句:
1.去重: distinct
2.条件:and or可混合使用
3.IN: 挨个查找出来 in (‘1’, '2')1字段 ,2字段 反着查找 not in
4.between: 范围 between 300 and 1000 在300到1000之间(可根据数值日期进行取值)
—————————————— -- ——————————————————————
1.通配符
% :0个,1个,多个字符(%多搭配like模糊查询)
_:为单个字符串
'A_Z': 所有以A开头以Z结尾
————————————————- mysql函数 -————————————————————
order by : 默认以(ASC升序) DESC(降序排序)按关键字排序
数学函数:
abs(x) 绝对值
rand() 随机数
mod(x,y) 返回x除以Y的余数
power(x,y) x的y次方
round(x) 离x最近整数
round(x,y) 保留x的y的小数四舍五入后的数
sqrt(x) 保留x的平方根
truncate(x,y) 返回数字截断y位小数
ceil(x)
floor(x)
greatest(字段1,字段2)
least(字段1,字段2)
聚合函数
avg(字段) ##平均数
count(字段) ##只能统计非空行数
min(字段) ###最小数
max(字段) ##最大数
sum(字段) ###求和
count(*)统计所有行数包括null行全表扫描 指定字段会忽略null值的行
先去重在统计行数
-—————————————字符串函数-------——————————
trim(leading/trailing,both 'xxx' from 字段、字符串)
concat(x,y) 拼接字符串
replace(x,y,z) 替换