mysql数据库备份

最新推荐文章于 2023-04-23 19:30:41 发布

m0_64896523

最新推荐文章于 2023-04-23 19:30:41 发布

阅读量430

点赞数

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/m0_64896523/article/details/122049467

版权

mysql数据库备份

mysqldump命令

备份单个数据库

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

-u 指定用户 -p 指定密码 > /opt/dgf.sql指定备份目录一定要.sql结尾多个数据库备份加空格隔开

备份多个数据库

mysqldump -u root -p --databases dgf linuxfan > /opt/dgf.sql

出现这个错误解决方法

vim /etc/my.cnf

加入skip-name-resolve

就可以解决了

还原数据库

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

数据备份所有的库

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

备份dgf库中的users表和student表

mysql -u root -p dgf users student > /opt/dgf_table.sql

还原数据表

mysql -u root -p dgf < /opt/dgf-users.sql

备份表结构不备份数据

默认将dgf库下所有表的表结构全部导出

mysqldump -u root -p -d dgf > /opt/jiegou.sql

导出单个数据表的表结构

mysqldump -u root -p -d dgf users > /opt/jiegou_users.sql

备份表数据不备份表结构

默认将dgf库下所有表的表数据全部导出

mysqldump -u root -p -t dgf >/opt/shuju.sql

导出单个数据表的表数据

mysqldump -u root -p -t dgf users > /opt/shuju.sql

二进制日志文件

一、binlog二进制日志通常作为备份的重要资源，所以再说备份方案之前先总结一下binlog日志~~
binlog日志内容
1）引起mysql服务器改变的任何操作(增删改没查)。
2）复制功能依赖于此日志。Mysql主从
3）slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中。
4）slave服务器通常可以关闭二进制日志以提升性能。

2.binlog日志文件的文件表现形式
1）默认在安装目录下，存在mysql-bin.00001, mysql-bin.00002的二进制文件（binlog日志文件名依据my.cnf配置中的log-bin参数后面的设置为准）

什么时候二进制日志会重新生成？

重启mysql服务 systemctl restart mysqld
mysql> flush logs;
根据二进制文件的大小来切割

2）还有mysql-bin.index用来记录被mysql管理的二进制文件列表
3）如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使得mysql管理混乱。

4.MySQL二进制文件读取工具mysqlbinlog
命令格式：mysqlbinlog [参数] log-files

有以下四种参数选择：
--start-datetime       #用来指定二进制日志的起始日期
--stop-datetime        #用来指定二进制日志的结束日期
--start-position         #用来指定二进制日志的起始位置
--stop-position          #用来指定二进制日志的结束位置

Mysqlbinlog可以完成mysql的增量备份
[root@test-huanqiu ~]# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001
截取一下结果：
# at 30225
#151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1448858615/*!*/;
SET @@session.pseudo_thread_id=1/*!*/

根据以上截取结果第二行，进行解释二进制日志内容
1）时间点： 151130 12:43:35
2）服务器ID： server id 1
服务器ID主要用于标记日志产生的服务器，主要用于双主模型中，互为主从，确保二进制文件不会被相互循环复制
3）记录类型： Query
4) 线程号： thread_id = 1
5) 语句的时间戳和写入二进制日志文件的时间差； exec_time=0
6) 事件内容
7）事件位置 #at 30225
8) 错误代码 error_code=0
9) 事件结束位置 end_log_pos也就是下一事件开始的位置

二进制日志格式
由bin_log_format={statement|row|mixed}定义
1）statement: 基于语句，记录生成数据的语句
缺点在于如果当时插入信息为函数生成，有可能不同时间点执行结果不一样，
例如： INSERT INTO t1 VALUE (CURRENT_DATE());
2）row: 基于行数据缺点在于，有时候数据量会过大
3）mixed：混合模式，由mysql自行决定何时使用statement, 何时使用row 模式

查看当前二进制日志记录格式

mysql> show variables like 'binlog_format';

mysql> set session binlog_format=statement;

5.7之前是statement，5.7之后是基于行的

建议：切勿将二进制日志与数据文件放在一同设备；可以将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；

3.备份类型：
（1）根据备份时，mysql服务是否在线
1）冷备(cold backup)：停掉mysql这个服务，读写操作都不能进行
2）温备份：全局施加共享锁，只能读不能写
3）热备(hot backup)：不停掉mysql这个服务，读写照样进行

（2）根据备份时的数据集分类
1）完全备份(full backup)
2）增量备份(partial backup)
（3）根据备份时的接口
1）物理备份（physical backup）：直接复制数据文件，打包归档，就是对mysql的数据存放路径进行打包
特点：
不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个G，则适用于物理备份

2）逻辑备份(logical backup)：把数据抽取出来保存在sql脚本中，mysqldump就属于逻辑备份
特点：
可以使用文本编辑器编辑；导入方便，直接读取sql语句即可；逻辑备份恢复时间慢，占据空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。

（4）根据备份整个数据还是变化数据
1）全量备份 full backup

完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

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

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

2）增量备份 incremental backup
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
3）差异备份 differential backup
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

(7)Mysql最常用的三种备份工具：
1）mysqldump：
通常为小数据情况下的备份
innodb：热备，温备
MyISAM, Aria: 温备
单线程备份恢复比较慢
2）Xtrabackup（通常用innobackupex工具）：
备份mysql大数据
InnoDB热备，增量备份；
MyISAM温备，不支持增量，只有完全备份
属于物理备份，速度快；
3）lvm-snapshot：
接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；
使用cp、tar等工具进行物理备份；
备份和恢复速度较快；
很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此；

两者对比如下

XtraBackup

Percona XtraBackup（简称PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，并且全部开源

工具集

软件包安装完后一共有4个可执行文件，如下：

其中最主要的是 innobackupex 和 xtrabackup，前者是一个 perl 脚本，后者是 C/C++ 编译的二进制。

xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 mysqld server 没有交互；

innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 mysqld server 发送命令进行交互，如加读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。简单来说，innobackupex 在 xtrabackup 之上做了一层封装。

一般情况下，我们是希望能备份 MyISAM 表的，虽然我们可能自己不用 MyISAM 表，但是 mysql 库下的系统表是 MyISAM 的，因此备份基本都通过 innobackupex 命令进行；另外一个原因是我们可能需要保存位点信息。

另外2个工具相对小众些，xbcrypt 是加解密用的；xbstream 类似于tar，是 Percona 自己实现的一种支持并发写的流文件格式。两都在备份和解压时都会用到（如果备份用了加密和并发）

原理

通信方式

2个工具之间的交互和协调是通过控制文件的创建和删除来实现的，主要文件有：

xtrabackup_suspended_1
xtrabackup_suspended_2
xtrabackup_log_copied

举个栗子，我们来看备份时 xtrabackup_suspended_2 是怎么来协调2个工具进程的

innobackupex 在启动 xtrabackup 进程后，会一直等 xtrabackup 备份完 InnoDB 文件，方式就是等待 xtrabackup_suspended_2 这个文件被创建出来；
xtrabackup 在备完 InnoDB 数据后，就在指定目录下创建出这个文件，然后等这个文件被 innobackupex 删除；
innobackupex 检测到文件 xtrabackup_suspended_2 被创建出来后，就继续往下走；
innobackupex 在备份完非 InnoDB 表后，删除 xtrabackup_suspended_2 这个文件，这样就通知 xtrabackup 可以继续了，然后等 xtrabackup_log_copied 被创建；
xtrabackup 检测到 xtrabackup_suspended_2 文件删除后，就可以继续往下了。

备份过程

整个备份过程如下图：

Xtrabackup innodb(redo文件（日志） + Ibd 文件(数据和索引))

Redo文件:是存储引擎层(innodb)生成的日志，主要为了保证数据的可靠性；

Ibd文件：Inodb引擎开启的表空间，用来存储表的数据和索引

增量备份

PXB 是支持增量备份的，但是只能对 InnoDB 做增量，InnoDB 每个 page 有个 LSN 号，LSN 是全局递增的，page 被更改时会记录当前的 LSN 号，page中的 LSN 越大，说明当前page越新（最近被更新）。每次备份会记录当前备份到的LSN（xtrabackup_checkpoints 文件中），增量备份就是只拷贝LSN大于上次备份的page，比上次备份小的跳过，每个 ibd 文件最终备份出来的是增量 delta 文件。

MyISAM 是没有增量的机制的，每次增量备份都是全部拷贝的。

增量备份过程和全量备份一样，只是在 ibd 文件拷贝上有不同。

innobackupex针对mysql数据库的备份环境部署

安装xtrabackup

[root@localhost ~]# tar xf percona-xtrabackup2.4.5-Linux-x86-_64.tar.gz -C /usr/src/

[root@localhost ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/

[root@localhost percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/

[root@localhost ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5

[root@localhost ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm

m0_64896523

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库备份

mysql数据库备份mysqldump命令备份单个数据库mysqldump -u root -p --databases dgf > /opt/dgf.sql-u 指定用户 -p 指定密码 > /opt/dgf.sql指定备份目录一定要.sql结尾多个数据库备份加空格隔开备份多个数据库mysqldump -u root -p --databases dgf linuxfan > /opt/dgf.sql出现这个错误解决方法...
复制链接

扫一扫