MYSQL数据库之备份与恢复

一.数据备份的重要性

    

1.数据丢失的原因

1.程序错误

2.人为操作

3.磁盘故障

二.备份类型

1.物理备份

含义：对文件作操作

1.冷备份（关闭进行）

2.热备份（运行进行）

3.温备份（数据库锁定表格）

2.逻辑备份

含义：对数据库逻辑组件

1. 对数据库，或者表通过命令进行倒出来写在文本中

三.数据库备份分类

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)    替换