数据库的备份和恢复

1.备份

完全备份：将整个数据库完整的进行备份。

增量备份：在完全备份的基础之上，对后续新增的内容进行备份。

2.备份的需求

1.在生产环境中，数据的安全至关重要，任何数据的丢失都可能产生非常严重的后果。

2.数据为什么会丢失，程序操作，运算错误，磁盘故障，不可预期的事件（地震之类），人为操作。

3.冷热备份的区别

冷备份：关机备份，停止mysql服务进行备份

热备份：开机备份，无需关闭mysql服务，进行备份

4.物理备份和逻辑备份

物理备份：对数据库系统的物理文件（数据文件，日志文件），进行备份。

逻辑备份：只是对数据库的逻辑组进行备份，（表结构），以sql语句的形式，把库，表结构，表数据进行备份保存。（直接在数据库系统中删除全部文件，逻辑备份无法恢复）

5.物理备份

物理备份：完全备份，对整个数据库进行完整的打包备份

优点：操作简单

缺点：数据库文件占用量是很大的，占用空间太大，备份和恢复时间都很长，而且需要暂定数据库服务.打包备份最好是把服务器关掉，避免有新的数据进入，被覆盖，也可能会导致恢复失败。

cd /usr/local/mysql

yum -y install xz

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

cd /opt

tar Jxvf /opt/mysql_all_2023-11-06.tar.xz

cd /usr/local/mysql

rm -rf data/

ls

cp -a /opt/usr/local/mysql/data /usr/local/mysql

systemctl restart mysqld.service

MySq2:远程备份

cd /usr/local/mysql

cp -a /usr/local/mysql/data/ /opt/

rm -rf /usr/local/mysql/data

scp -r root@192.168.66.15:/opt/usr/local/mysql/data /opt

cd /opt/

ll

cp -a data/ /usr/local/mysql/data

cd /usr/local/mysql/data

chown -R mysql.mysql kgc kgc1

6. 热备份当中的逻辑备份

这是mysql自带的工具：

mysqldump

mysqldump -u root -p --databases kgc > /opt/kgc.sql

mysqldump -u root -p --databases kgc kgc1 > /opt/kgc1.sql 备份多个库

mysqldump -u root -p --all-databases > /opt/kgc3.sql

systemctl restart mysql

netstart -antp | grep 3306

mysql -u -root -p --databases kgc > /opt/kgc.sql

123456

cd /usr/local/mysql/data/

rm -rf kgc

mysql -u root -p < /opt/kgc.sql

123456

cd kgc

ls

mysql -u root -p123456 -e 'show databases;' #执行一次命令自动退出

-e :指定连接mysql之后执行完命令，自动退出

mysql -u root -p123456 -e 'drop database kgc;'

mysql -u root -p < /opt/kgc.sql

mysqldump -u root -p -databases kgc kgc1 > /opt/kfc_all/sql

cd /opt/

ls

mysql -u root -p123456 -e 'drop database kgc;'

mysql -u root -p123456 -e 'drop database kg1;'

mysql -u root -p < /opt/kgc_all.sql

mysqldump -u root -p --all-databases > /opt/all_database.sql

cd /opt

ls

7. 如何只恢复数据表？

mysqldump -u root -p kgc info1 > /opt/kgc_info1.sql

123456

cd /opt

ls

mysql -u root -p -e 'drop table kgc.info1;''

mysql -u root -p kgc < /opt/kgc_info1.sql

mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql

mysql -u root -p -e 'drop table kgc.info1;''

mysql -u root -p -e 'drop table kgc.info2;''

mysql -u root -p kgc < /opt/kgc_info-2.sql

Mysql2:

scp root@192.168.66.15:/opt/all_info.sql /opt/

123

cd /opt

ls

mysql -u root -p < /opt/all_database.sql

8. 增量备份

mysqldump 支持增量备份

没有重复数据，备份量小，时间端。

mysqldump 增量备份恢复数据期间，表会锁定。

缺点：备份时锁表，必然会影响业务，超时10G，耗时会比较长，导致服务不可用。

增量备份的过程：

1、mysql提供的二进制日志间接的实现增量备份。

二进制文件怎么来？

修改配置文件：

vim /etc/my.cnf

log-bin=mysql-bin

binlog_format=MIXED

8.1 mysql二进制日志记录格式有三种：

1.STATEMNET：基于sql语句

记录修改的sql语句，但是在高并发的情况下，记录sql语句时候的顺序可能会出错，恢复数据时可能会导致丢失和误差，效率比较高。

2.ROW：基于行

精准记录每一行的数据，准确率高，但是恢复的时效率低

3.MIXED：既可以根据sql语句，也可以根据

在正常情况下使用STATEMENT，一旦发送高并发，会只能自动切换到ROW行。

8.2 修改日志文件

vim /etc/my.cnf

log-bin=mysql-bin

binlog_format=MIXED

systemctl restart mysqld.service

cd /usr/local/mysql/data

ls

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 #查看备份的二进制内容

mysqladmin -u root -p flush-logs #刷新

要把数据删除

mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p #恢复

123456

mysqladmin -u root -p flush-logs

8.3 基于位置点来进行恢复：

从某一个点开始，恢复到最后

mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p

从开头，一直恢复到某个位置

mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

从指定点---指点结束点

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

8.4 基于时间点进行恢复：

1.从某个时间点开始：

mysqlbinlog --no-default--start-datetime='时间点' 文件 | mysql -u root -p

2.从开头，到指定的结尾时间点：

mysqlbinlog --no-default--stop-datetime='时间点' 文件 | mysql -u root -p

3.指定时间范围

mysqlbinlog --no-default--start-datetime='时间点' --no-default--start-datetime='时间点' 文件 | mysql -u root -p

mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p

总结：

在生产中，通过binlog进行增量恢复是非常好用的方法。

我们只要需要对binlog文件进行备份，随时可以进行备份和恢复。

9.错误日志备份

log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件

general_log=ON ##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的

general_log_file=/usr/local/mysql/data/mysql_general.log

slow_query_log=ON ##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query_log

long_query_time=5 #慢查询时间为5

show variables like 'general%'; #查看通用查询日志是否

show variables like 'log_bin%'; #查看二进制日志是否开

show variables like '%slow%'; #查看慢查询日功能是否开启

show variables like 'long_query_time'; #查看慢查询时间设置

set global slow_query_log=ON; #在数据库中设置开启慢查询的方法