MySQL备份与恢复

标签：恢复备份 MySQL mysqldump mysql 日志数据库

1.数据库备份的分类

1.1 数据备份的重要性

1.2 数据库备份的分类

4.2 实例：完全备份+增量备份恢复数据库编辑

1.数据库备份的分类

1.1 数据备份的重要性

1.2 数据库备份的分类

物理备份:直接对数据库的数据文件或者日志文件进行备份.
逻辑备份:对数据库的库或表对象进行备份.

备份策略

1.3 常见的备份方法

2.MySQL完全备份与恢复

2.1 MySQL完全备份

优点：备份与恢复操作简单方便

缺点：数据存在大量的重复；占用大量的备份空间；备份与恢复时间长

2.2 数据库完全备份分类

2.3 MySQL物理冷备份及恢复

先关闭数据库，然后打包备份相关数据库文件；

systemctl stop mysqld
cd /usr/local/mysql
tar zcf /opt/mysql_backup_$(date +%Y%m%d) data/
cd /opt/backup/
tar tf mysql_data-2023-06-19.tar.gz     #查看tar压缩包里的数据文件
tar xf mysql_data-2023-06-19.tar.gz    #解压缩tar压缩包里的数据文件

恢复数据库

###远端已备份数据文件主机（IP:192.168.9.210）
cd  /opt
scp mysql_backup_20240619 192.168.9.210:/opt

###需要恢复数据库文件主机（IP:192.168.9.210）
systemctl stop mysqld
cd /opt
tar xf mysql_backup_20240619 
mv /usr/local/mysql/data /usr/local/mysql/data.bak    #将原有的数据库文件移走
mv /opt/data/ /usr/local/mysql/
systemctl restart mysqld   #重新启动数据库服务
mysql -uroot -pabc123    #此数据库密码为之前备份数据库的密码

2.4 mysqldump进行逻辑备份

2.4.1 mysqldump备份数据库

备份指定数据库中的文件：
mysqldump -u用户名 -p密码数据库名 > 指定路径的绝对路径/数据库名.sql

备份指定数据库和文件：
mysqldump -u用户名 -p密码 --databases 数据库名 > 指定路径的绝对路径/数据库名.sql

备份多个库及文件
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 > 指定路径的绝对路径/数据库名.sql

备份所有数据库文件：
mysqldump -u用户名 -p密码 --all-databases > 指定路径的绝对路径/数据库名.sql

2.4.2 mysqldump备份数据表

备份指定数据库中的表数据文件：
mysqldump -u用户名 -p密码数据库名表名 > 指定路径的绝对路径/数据库名_表名.sql

3. 恢复数据库

3.1 使用source命令恢复数据库

mysql -u root -pabc123; 登录数据库
source /opt/backup/tan1.sql  #使用soure 指定原先库备份的路劲
show  databases;   #查看库

3.2 使用mysql命令恢复数据库

3.2.1 重定向方式恢复数据库

3.2.2 管道符方式恢复数据库

3.3 恢复表操作

4.MySQL增量备份与恢复

4.1 二进制日志文件

vim /etc/my.cnf
------------在“server-id”行下面添加mysql数据库的日志信息---------
server-id = 1
---------------------------------------------------------------
#错误日志
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_slow_query.log
long_query_time=2

systemctl restart mysqld
mysql -uroot -pabc123

刷新生成二进制日志文件的两种方式：

（1）重启数据库服务：systemctl restart mysqld;

（2）flush-logs命令刷新：mysqladmin -uroot -pabc123 flush-logs；

二进制日志(binlog)有3种不同的记录格式：

STATEMENT(基于SQL语句)：默认格式是STATEMENT。该方式记录语句快，占用内存空间少。但高并发情况下会导致记录日志顺序紊乱，造成恢复数据时发生偏差。
ROW(基于行)：基于数据内容行进行记录，不仅记录执行的命令语句，还会记录命令影响的相关数据行。
MIXED(混合模式)：高并发情况下ROW方式进行记录，一般情况下采用STATEMENT方式进行记录。

测试：

systemctl restart mysqld重启

###转换格式，查看指定序列号的二进制数据日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003
--no-defaults：不使用默认格式进行查看
--base64-output=decode-rows：使用base64密码格式进行转换，-rows：按行进行输出
-v：显示详细输出过程