目录
备份
为什么要备份
备份类型
备份方式
MySQL的日志及作用
配置文件
实站操作
冷备份
mysqldump 备份与恢复
完全备份与恢复
增量备份及恢复
断点恢复
基于位置恢复
基于时间点
备份
为什么要备份
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为
灾难：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
1、能够容忍丢失多少数据
2、恢复数据需要多长时间
3、需要恢复哪一些数据
备份类型
备份类型分为：完全备份和部分备份
完全备份：就是备份整个数据集
部分备份：就是只备份数据子集，如部分库或表
部分备份还分为增量备份和差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：差异备份指的是备份自上一次完全备份以来变化的数据 浪费空间、还原比增量备份简单
备份方式
在MySQL中有三种备份方式
冷、温、热备份
冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
MySQL的日志及作用
MySQL日志默认保存位置为/usr/local/mysql/data
1.redo 重做日志：达到事务一致性（每次重启会重做）
作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性
2.undo 回滚日志
作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现
3.errorlog 错误日志
作用：Mysql本身启动，停止，运行期间发生的错误信息
4.slow query log 慢查询日志
作用：记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功 另一个作用：在于提醒优化
5.bin log 二进制日志
作用：用于主从复制，实现主从同步 记录的内容是：数据库中执行的sql语句
6.relay log 中继日志
作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
7.general log 普通日志
作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能
配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin
或
log_bin=mysql-bin
##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin
或
log_bin=mysql-bin
##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒
在配置文件添加几种日志，回到数据库查看日志是否开启
查看通用查询日志是否开启
(root@localhost) [(none)]> show variables like 'general%'; +------------------+-----------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------+ | general_log | ON | | general_log_file | /usr/local/mysql/data/mysql_general.log | +------------------+-----------------------------------------+ 2 rows in set (0.00 sec)
查看二进制日志是否开启
(root@localhost) [(none)]> show variables like 'log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+---------------------------------------+ 5 rows in set (0.01 sec)
查看慢查询日功能是否开启
(root@localhost) [(none)]> show variables like '%slow%'; +---------------------------+--------------------------------------------+ | Variable_name | Value | +---------------------------+--------------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/mysql_slow_query.log | +---------------------------+--------------------------------------------+ 5 rows in set (0.01 sec)
查看慢查询时间设置
(root@localhost) [(none)]> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
在数据库中设置开启慢查询的方法
(root@localhost) [(none)]> set global slow_query_log=ON; Query OK, 0 rows affected (0.00 sec)
variables 表示变量 like 表示模糊查询
二进制日志开启后，重启mysql 会在目录中查看到二进制日志
索引文件：记录更新语句
索引文件刷新方式：
1、重启mysql的时候会更新索引文件，用于记录新的更新语句
2、刷新二进制日志mysql-bin.index：
二进制日志文件的索引[root@localhost /usr/local/mysql/data]# cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003
实站操作
冷备份
物理冷备份与恢复
关闭MySQL服务
[root@localhost ~]# systemctl stop mysqld
安装xz软件包，我已经有了
[root@localhost ~]# rpm -q xz xz-5.2.2-1.el7.x86_64
开始压缩备份
[root@localhost ~]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
解压恢复
[root@localhost /usr/local/mysql]# mv /opt/data/ /usr/local/mysql/data [root@localhost ~]# tar Jxvf /opt/mysql_all_2021-11-30.tar.xz -C /usr/local/mysql/data
查看
[root@localhost /usr/local/mysql]# cd data/ [root@localhost /usr/local/mysql/data]# ls auto.cnf ibdata1 mysql-bin.000001 mysql_error.log sys bbs ib_logfile0 mysql-bin.000002 mysql_general.log usr hellodb ib_logfile1 mysql-bin.000003 mysql_slow_query.log xb ib_buffer_pool mysql mysql-bin.index performance_schema
注意是移动不是复制，复制会无法启动mysql，复制注意权限问题。
mysqldump 备份与恢复
完全备份与恢复
完全备份一个或多个完整的库 (包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
完全备份一个或多个完整的库 (包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
完全备份xb库
[root@localhost ~]# mysqldump -u root -p --databases xb > /data/xb.sql Enter password: [root@localhost ~]# cd /data/ [root@localhost /data]# ls xb.sql
完全备份mysql服务器中所有的库
[root@localhost /data]# mysqldump -u root -p --all-databases > /data/all.sql
完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
[root@localhost /data]# mysqldump -u root -p xb school_db students > /data/xb_sch.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d"选项，说明表数据也进行备份
#做为一个表结构模板
查看备份文件
[root@localhost /data]# grep -v "^--" /data/xb_sch.sql | grep -v "^/" | grep -v "^$"
模拟删除
删除库
(root@localhost) [xb]> drop database xb; Query OK, 4 rows affected (0.03 sec)
恢复
mysql -u root -p < /data/xb.sql Enter password:
去数据库查看
数据库的所有数据就都回来了
增量备份及恢复
MySQL数据库增量恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始
MySQL数据库增量恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始
实际操作
修改配置文件启用二进制日志
[root@localhost /data]# vim /etc/my.cnf ... [mysqld] log-bin=mysql-bin binlog_format = MIXED ...
重启
[root@localhost /data]# systemctl restart mysqld.service
可以看到MySQL日志文件生成了一个mysql-bin.000001文件
[root@localhost /usr/local/mysql/data]# cat mysql-bin.index ./mysql-bin.000001
进入数据库实际操作
(root@localhost) [bbs]> create database bank; Query OK, 1 row affected (0.01 sec) (root@localhost) [bbs]> use bank; Database changed (root@localhost) [bank]> create table bank (id int primary key,name varchar(10),address varchar(20),hobby char(20)); Query OK, 0 rows affected (0.01 sec) (root@localhost) [bank]> desc bank; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | | address | varchar(20) | YES | | NULL | | | hobby | char(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) (root@localhost) [bank]> insert into bank values (1,'亚索','艾欧尼亚','刮风'); Query OK, 1 row affected (0.01 sec) (root@localhost) [bank]> insert into bank values (2,'李青','艾欧尼亚','踢人'); Query OK, 1 row affected (0.01 sec) (root@localhost) [bank]> insert into bank values (3,'金克斯','诺克萨斯','玩枪'); Query OK, 1 row affected (0.00 sec) (root@localhost) [bank]> select * from bank ; +----+-----------+--------------+--------+ | id | name | address | hobby | +----+-----------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | +----+-----------+--------------+--------+ 3 rows in set (0.00 sec)
创建存放备份文件的目录
[root@localhost /usr/local/mysql/data]# mkdir /backup
备份bank 下的表bank
[root@localhost /usr/local/mysql/data]# mysqldump -u root -p bank bank > /backup/bank.sql
刷新日志，查看是否生成了新的二进制日志
[root@localhost /usr/local/mysql/data]# mysqladmin -u root -p flush-logs Enter password: [root@localhost /usr/local/mysql/data]# ls auto.cnf ib_buffer_pool ibtmp1 mysql-bin.000003 mysql_slow_query.log xb bank ibdata1 mysql mysql-bin.index performance_schema bbs ib_logfile0 mysql-bin.000001 mysql_error.log sys hellodb ib_logfile1 mysql-bin.000002 mysql_general.log usr
数据库插入新的数据
查看日志文件
(root@localhost) [bank]> show binlog events in 'mysql-bin.000003';
或者
[root@localhost /usr/local/mysql/data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003
模拟删库
(root@localhost) [bank]> drop database bank ; Query OK, 1 row affected (0.01 sec) (root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | hellodb | | mysql | | performance_schema | | sys | | usr | | xb | +--------------------+ 8 rows in set (0.00 sec)
恢复表
首先先创建一个bank库，把备份的数据导进去
[root@localhost /usr/local/mysql/data]# mysql -u root -p bank < /backup/bank.sql Enter password:
(root@localhost) [(none)]> create database bank; Query OK, 1 row affected (0.00 sec) (root@localhost) [(none)]> use bank Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [bank]> select * from bank; +----+-----------+--------------+--------+ | id | name | address | hobby | +----+-----------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | +----+-----------+--------------+--------+ 3 rows in set (0.00 sec)
恢复 增量备份
[root@localhost /usr/local/mysql/data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure.
回到数据库
(root@localhost) [bank]> select * from bank; +----+--------------+--------------+--------+ | id | name | address | hobby | +----+--------------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | | 4 | 卡特琳娜 | 诺克萨斯 | 匕首 | | 5 | 卢锡安 | 诺克萨斯 | 玩枪 | +----+--------------+--------------+--------+
数据就回来了！！！
断点恢复
基于位置恢复
删除表中的数据
(root@localhost) [bank]> delete from bank where id =5; Query OK, 1 row affected (0.01 sec) (root@localhost) [bank]> delete from bank where id =4; Query OK, 1 row affected (0.00 sec) (root@localhost) [bank]> select * from bank; +----+-----------+--------------+--------+ | id | name | address | hobby | +----+-----------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | +----+-----------+--------------+--------+ 3 rows in set (0.00 sec)
只恢复其一
先查看日志文件
[root@localhost /backup]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003
找到那个断点
查看 mysql-bin.000003的序号
[root@localhost /backup]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003 >/backup/mysqlbin.log [root@localhost /backup]# ls bank.sql mysqlbin.log
断点
从头开始 到36946结束
[root@localhost /backup]# mysqlbinlog --no-defaults --stop-position='35946' /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123123
进到数据库
(root@localhost) [bank]> select * from bank; +----+--------------+--------------+--------+ | id | name | address | hobby | +----+--------------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | | 4 | 卡特琳娜 | 诺克萨斯 | 匕首 | +----+--------------+--------------+--------+ 4 rows in set (0.00 sec)
4已经恢复了
从34033开始
[root@localhost /backup]# mysqlbinlog --no-defaults --start-position='34033' /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123123
进入数据库查看就全部恢复了
(root@localhost) [bank]> select * from bank; +----+--------------+--------------+--------+ | id | name | address | hobby | +----+--------------+--------------+--------+ | 1 | 亚索 | 艾欧尼亚 | 刮风 | | 2 | 李青 | 艾欧尼亚 | 踢人 | | 3 | 金克斯 | 诺克萨斯 | 玩枪 | | 4 | 卡特琳娜 | 诺克萨斯 | 匕首 | | 5 | 卢锡安 | 诺克萨斯 | 玩枪 | +----+--------------+--------------+--------+ 5 rows in set (0.00 sec)
基于时间点
格式只将position 改为datetime 时间日期 格式 年-月-日 时：分：秒
[root@localhost /backup]# mysqlbinlog --no-defaults --start-datetime='2021-11-30 21:00:14' /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure.
查看日志
[root@localhost /backup]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003
