当前位置： 正文

MySQL备份与恢复

2024-06-27 建站教程 57

1 备份概览

按备份方法分类：

  • Hot Backup（热备）

    在线备份，不影响数据库运行

  • Cold Backup（冷备）

    离线备份，通过拷贝数据库物理文件

  • Warm Backup（温备）

    在线进行，但需要加读锁保证数据一致性

按备份文件内容分类：

  • 逻辑备份

    备份后的内容是可读的，通常形式为SQL语句的文本文件，或是表内的实际数据

    优势：呈现直观，适用于升级、迁移等工作，缺点：恢复用时较长

  • 裸文件备份

    拷贝数据库的物理文件，不关心数据库的运行状态，通常这类备份恢复的时间较短

按备份数据库的内容分类：

  • 完全备份：对当前数据库的完整备份

  • 增量备份：在完全备份的基础上，对更新的数据进行备份

  • 日志备份：指的是对数据库二进制日志的备份

2 冷备

备份内容：数据表结构定义文件(*.frm)、共享表空间文件(ibdata)、独立表空间文件(*.ibd)、重做日志文件(ib_logfile)，建议增加配置文件(my.cnf)

优势：

  • 备份简单，仅需拷贝相关文件
  • 备份文件有利于在不同操作系统、不同MySQL版本之间恢复
  • 恢复简单，仅需把文件恢复到指定位置
  • 恢复速度快，无需执行SQL语句，无需重建索引

劣势：

  • 备份文件较大
  • 跨平台、操作系统、软件版本可能会出现问题

3 逻辑备份

3.1 mysqldump

mysqldump -uroot -p [arguments] > file_name
#arguments参数
–all-databases #备份所有数据库
–databases dbx #备份指定数据库
–single-transaction dbx #备份数据库的整体架构，事务控制保证备份的一致性
–lock-tables #一般用于MyISAM，备份时仅可读
–lock-all-tables #对所有架构所有表上锁
–add-drop-databases #在create前先运行drop，需配合–alldatabases使用
#通过转存的文件建立一个salve replication
#value=1 转存文件中记录CHANGE MASTER语句
#value=2 CHANGE MASTER语句写成注释
–master-data[=value] 
–events #备份事件调度器
–routines #备份存储过程和函数
–triggers #备份触发器
–hex-blob #将binary、varbinary、blog、bit列类型备份为16进制的格式
–tab=path #产生tab分割的数据文件，可使用–fields-terminated-by改变分隔符
–where #导出给定条件的数据

3.2 SELECT … INTO OUTFILE

SELECT [column1],[column2] …
INTO
#导出的文件名称需拥有mysql用户权限且文件不存在
OUTFILE ‘filename’
{#fields or columns
#列分隔符
TERMINATED BY ‘string’
#对于字符串的包含符
[OPTIONALLY] ENCLOSED BY ‘char’
#转译符
ESCAPED BY ‘char’
}
{#lines
STARTING BY ‘string’
TERMINATED BY ‘string’
}
FROM TABLE WHERE …

3.3 逻辑备份的恢复

mysqldump备份的文件为逻辑SQL语句，可用以下两种方法恢复

#在shell中执行
mysql -uroot -p < xxx.sql
#在MySQL交互命令行执行
mysql> source xxx.sql

3.4 LODA DATA INFILE

通过mysqldump –tab或select into outfile导出的数据需通过以下的命令导入

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘filename’
[REPLACE | IGNORE]
INTO TABLE tb_name
[CHARACTER SET charset_name]
#[FIELDS | COLUMNS]
TERMINATED BY ‘string’
[OPTIONALLY] ENCLOSED BY ‘char’
ESCAPED BY ‘char’
#[LINES]
STARTING BY ‘string’
TERMINATED BY ‘string’
#[IGNORE NUMBER LINES]
col_name or user_var
SET col_name = expr

使用LOAD DATA INFILE导入需拥有FILE权限

mysql> load data infile ‘xx.txt’ into table a;

mysql> load data infile ‘xx.txt’ into table a;

4 binlog备份

在配置文件启动binlog日志服务

#查看
mysqlbinlog mysqlbin-xxx.xxx
#还原
mysqlbinlog binlog.000001 | mysql -uroot -p test

因binlog为逻辑备份日志，可在MySQL命令行窗口使用source命令导入

也可设置偏移量指定二进制日志的位置，以跳过某些不想被恢复的语句

mysqlbinlog –start-position=10000 –stop-position=12000 | mysql -uroot =p test

也可使用–start-datetime和–stop-datetime

5 热备

5.1 Ibackup

备份InnoDB表原理：

  • 记录备份开始的重做日志文件LSN
  • 拷贝共享表空间文件和独立表空间文件
  • 拷贝后记录重做日志的检查点LSN
  • 拷贝备份期间的重做日志

优点：

  • 在线备份，不阻塞SQL语句
  • 备份性能好，通过复制数据库物理表空间文件和重做日志文件
  • 支持压缩备份
  • 跨平台支持

恢复流程：

  • 恢复表空间文件
  • 应用重做日志文件恢复InnoDB存储引擎表

5.2 Xtrabackup

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品

5.2.1 全量备份
  • 备份

  • #备份
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root –password=pass –host=127.1 –slave-info –tmpdir=/mysql/bak/full/ /mnt/sdfs/xxxx

    • 恢复

    • #1.停库，清空data目录
      mysqladmin -uroot -h127.1 -P3306 -p’123456′ shutdown;
      rm -rf /mysql/data/*;

      #2.恢复-应用日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –apply-log –user=root –password=pass –host=127.1 /mnt/sdfs/***

      #3.恢复-拷贝文件
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root  –password=pass –host=127.1 –copy-back /mnt/sdfs/***

      #4.文件修改权限
      chown -R mysql:mysql /mysql

      #5.启动实例
      mysqld_safe –defaults-file=/home/mysql/etc/my.cnf &

      #6.[从库]从库需重新连接master
      change master to
          master_host=’192.168.100.20′,
          master_user=’repl’,
          master_port=3306,
          master_password=’123456′,
          master_log_file=’mysql-bin.000001′,
          master_log_pos=307;

      #7.[从库]从库启动复制进程
      start slave;   启动连接

      5.2.2 增量备份

      增量备份依赖于全备份，xtrabackup通过比较全备的终点与当前的LSN，生成区间内的备份文件，备份时也存在redo日志监控线程，备份过程中的增加的LSN也会写入日志中

    • /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –user=root –host=’127.0.0.1′ –password=’123456′ –incremental  /root/back –incremental-basedir=/opt/back/2022-05-13_17-46-53

      可进行多次增备，恢复需依次准备

    • #准备全备份日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only  /opt/back/2022-05-13_17-33-14
      #准备增备日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only  /opt/back/2022-05-13_17-33-14 –incremental-dir=/opt/back/2022-05-13_17-33-14
      #再次准备整体
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log  /opt/back/2022-05-13_17-33-14

      5.3 快照备份

      基于LVM对分区执行快照工作，Linux在硬盘和分区上建立一个逻辑层提高分区管理的灵活性，将离散的磁盘分区连接成一个整块的卷组，形成存储池

      LVM使用写时复制技术创建快照，创建时仅拷贝元数据，不存在数据操作，创建过程较快。创建成功后，原始卷上有写操作时快照会跟踪原始卷的变化，将数据在改变之前拷贝到快照预留空间中，称之写时复制

      6 复制

      6.1 复制原理

      复制是MySQL提供的一种高可用、高性能的方案，用于建立大型应用，步骤分为三步：

      • 主服务器将数据更新到二进制日志中
      • 从服务器把主服务器的而二进制日志拷贝到自己的中继日志中
      • 从服务器重做中继日志，将更新应用到自己的数据库中

      同步的过程是异步的，基本上也是实时进行的，取决于主服务器的压力

      从服务器有两个线程，

      • I/O线程：读取主服务器的二进制日志，并保存为中继日志
      • SQL线程：复制执行中继日志

      6.2 快照+复制的备份架构

      复制用于备份的主要功能：

      • 数据分布
      • 读取负载均衡
      • 数据库备份
      • 高可用与故障转移

      为解决数据误操作的问题，可以在从服务器建立分区快照，避免复制对误操作的处理能力

0

MySQL备份与恢复

2024-06-27 建站教程 59

1 备份概览

按备份方法分类：

  • Hot Backup（热备）

    在线备份，不影响数据库运行

  • Cold Backup（冷备）

    离线备份，通过拷贝数据库物理文件

  • Warm Backup（温备）

    在线进行，但需要加读锁保证数据一致性

按备份文件内容分类：

  • 逻辑备份

    备份后的内容是可读的，通常形式为SQL语句的文本文件，或是表内的实际数据

    优势：呈现直观，适用于升级、迁移等工作，缺点：恢复用时较长

  • 裸文件备份

    拷贝数据库的物理文件，不关心数据库的运行状态，通常这类备份恢复的时间较短

按备份数据库的内容分类：

  • 完全备份：对当前数据库的完整备份

  • 增量备份：在完全备份的基础上，对更新的数据进行备份

  • 日志备份：指的是对数据库二进制日志的备份

2 冷备

备份内容：数据表结构定义文件(*.frm)、共享表空间文件(ibdata)、独立表空间文件(*.ibd)、重做日志文件(ib_logfile)，建议增加配置文件(my.cnf)

优势：

  • 备份简单，仅需拷贝相关文件
  • 备份文件有利于在不同操作系统、不同MySQL版本之间恢复
  • 恢复简单，仅需把文件恢复到指定位置
  • 恢复速度快，无需执行SQL语句，无需重建索引

劣势：

  • 备份文件较大
  • 跨平台、操作系统、软件版本可能会出现问题

3 逻辑备份

3.1 mysqldump

mysqldump -uroot -p [arguments] > file_name
#arguments参数
–all-databases #备份所有数据库
–databases dbx #备份指定数据库
–single-transaction dbx #备份数据库的整体架构，事务控制保证备份的一致性
–lock-tables #一般用于MyISAM，备份时仅可读
–lock-all-tables #对所有架构所有表上锁
–add-drop-databases #在create前先运行drop，需配合–alldatabases使用
#通过转存的文件建立一个salve replication
#value=1 转存文件中记录CHANGE MASTER语句
#value=2 CHANGE MASTER语句写成注释
–master-data[=value] 
–events #备份事件调度器
–routines #备份存储过程和函数
–triggers #备份触发器
–hex-blob #将binary、varbinary、blog、bit列类型备份为16进制的格式
–tab=path #产生tab分割的数据文件，可使用–fields-terminated-by改变分隔符
–where #导出给定条件的数据

3.2 SELECT … INTO OUTFILE

SELECT [column1],[column2] …
INTO
#导出的文件名称需拥有mysql用户权限且文件不存在
OUTFILE ‘filename’
{#fields or columns
#列分隔符
TERMINATED BY ‘string’
#对于字符串的包含符
[OPTIONALLY] ENCLOSED BY ‘char’
#转译符
ESCAPED BY ‘char’
}
{#lines
STARTING BY ‘string’
TERMINATED BY ‘string’
}
FROM TABLE WHERE …

3.3 逻辑备份的恢复

mysqldump备份的文件为逻辑SQL语句，可用以下两种方法恢复

#在shell中执行
mysql -uroot -p < xxx.sql
#在MySQL交互命令行执行
mysql> source xxx.sql

3.4 LODA DATA INFILE

通过mysqldump –tab或select into outfile导出的数据需通过以下的命令导入

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘filename’
[REPLACE | IGNORE]
INTO TABLE tb_name
[CHARACTER SET charset_name]
#[FIELDS | COLUMNS]
TERMINATED BY ‘string’
[OPTIONALLY] ENCLOSED BY ‘char’
ESCAPED BY ‘char’
#[LINES]
STARTING BY ‘string’
TERMINATED BY ‘string’
#[IGNORE NUMBER LINES]
col_name or user_var
SET col_name = expr

使用LOAD DATA INFILE导入需拥有FILE权限

mysql> load data infile ‘xx.txt’ into table a;

mysql> load data infile ‘xx.txt’ into table a;

4 binlog备份

在配置文件启动binlog日志服务

#查看
mysqlbinlog mysqlbin-xxx.xxx
#还原
mysqlbinlog binlog.000001 | mysql -uroot -p test

因binlog为逻辑备份日志，可在MySQL命令行窗口使用source命令导入

也可设置偏移量指定二进制日志的位置，以跳过某些不想被恢复的语句

mysqlbinlog –start-position=10000 –stop-position=12000 | mysql -uroot =p test

也可使用–start-datetime和–stop-datetime

5 热备

5.1 Ibackup

备份InnoDB表原理：

  • 记录备份开始的重做日志文件LSN
  • 拷贝共享表空间文件和独立表空间文件
  • 拷贝后记录重做日志的检查点LSN
  • 拷贝备份期间的重做日志

优点：

  • 在线备份，不阻塞SQL语句
  • 备份性能好，通过复制数据库物理表空间文件和重做日志文件
  • 支持压缩备份
  • 跨平台支持

恢复流程：

  • 恢复表空间文件
  • 应用重做日志文件恢复InnoDB存储引擎表

5.2 Xtrabackup

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品

5.2.1 全量备份
  • 备份

  • #备份
    /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root –password=pass –host=127.1 –slave-info –tmpdir=/mysql/bak/full/ /mnt/sdfs/xxxx

    • 恢复

    • #1.停库，清空data目录
      mysqladmin -uroot -h127.1 -P3306 -p’123456′ shutdown;
      rm -rf /mysql/data/*;

      #2.恢复-应用日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –apply-log –user=root –password=pass –host=127.1 /mnt/sdfs/***

      #3.恢复-拷贝文件
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –defaults-file=/home/mysql/etc/my.cnf –user=root  –password=pass –host=127.1 –copy-back /mnt/sdfs/***

      #4.文件修改权限
      chown -R mysql:mysql /mysql

      #5.启动实例
      mysqld_safe –defaults-file=/home/mysql/etc/my.cnf &

      #6.[从库]从库需重新连接master
      change master to
          master_host=’192.168.100.20′,
          master_user=’repl’,
          master_port=3306,
          master_password=’123456′,
          master_log_file=’mysql-bin.000001′,
          master_log_pos=307;

      #7.[从库]从库启动复制进程
      start slave;   启动连接

      5.2.2 增量备份

      增量备份依赖于全备份，xtrabackup通过比较全备的终点与当前的LSN，生成区间内的备份文件，备份时也存在redo日志监控线程，备份过程中的增加的LSN也会写入日志中

    • /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –user=root –host=’127.0.0.1′ –password=’123456′ –incremental  /root/back –incremental-basedir=/opt/back/2022-05-13_17-46-53

      可进行多次增备，恢复需依次准备

    • #准备全备份日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only  /opt/back/2022-05-13_17-33-14
      #准备增备日志
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log –redo-only  /opt/back/2022-05-13_17-33-14 –incremental-dir=/opt/back/2022-05-13_17-33-14
      #再次准备整体
      /usr/local/percona-xtrabackup-2.4.3-Linux-x86_64/bin/innobackupex –apply-log  /opt/back/2022-05-13_17-33-14

      5.3 快照备份

      基于LVM对分区执行快照工作，Linux在硬盘和分区上建立一个逻辑层提高分区管理的灵活性，将离散的磁盘分区连接成一个整块的卷组，形成存储池

      LVM使用写时复制技术创建快照，创建时仅拷贝元数据，不存在数据操作，创建过程较快。创建成功后，原始卷上有写操作时快照会跟踪原始卷的变化，将数据在改变之前拷贝到快照预留空间中，称之写时复制

      6 复制

      6.1 复制原理

      复制是MySQL提供的一种高可用、高性能的方案，用于建立大型应用，步骤分为三步：

      • 主服务器将数据更新到二进制日志中
      • 从服务器把主服务器的而二进制日志拷贝到自己的中继日志中
      • 从服务器重做中继日志，将更新应用到自己的数据库中

      同步的过程是异步的，基本上也是实时进行的，取决于主服务器的压力

      从服务器有两个线程，

      • I/O线程：读取主服务器的二进制日志，并保存为中继日志
      • SQL线程：复制执行中继日志

      6.2 快照+复制的备份架构

      复制用于备份的主要功能：

      • 数据分布
      • 读取负载均衡
      • 数据库备份
      • 高可用与故障转移

      为解决数据误操作的问题，可以在从服务器建立分区快照，避免复制对误操作的处理能力

0

猜你喜欢

Ubuntu 22.04服务器怎么修改登录密码
投稿分享

在Ubuntu 22.04服务器上修改登录密码，你可以通过SSH连接到服务器，然后使用passwd命令来更改密码。下面是...
2024-07-18 82
服务器怎么修改密码
投稿分享

修改服务器密码的具体步骤会根据你使用的操作系统和服务器环境（如物理服务器、云服务器、VPS等）有所不同。下面我将分别给出...
2024-07-18 12
为什么服务器连接不上
投稿分享

服务器连接不上的原因可能涉及多个方面，以下是一些常见的原因及相应的解决方法： 一、网络问题 网络连接不稳定或断开： 确保...
2024-07-11 75
虚拟机怎么安装 Linux 系统
投稿分享

虚拟化技术目前主要有两种：一、原生架构，这种虚拟机产品直接安装在计算机硬件上，不需要操作系统的支持，这类虚拟机拥有强大的...
2024-07-04 85
Linux系统下与磁盘空间和文件尺寸相关的命令
投稿分享

在Linux系统下，与磁盘空间和文件尺寸相关的命令非常丰富，这些命令可以帮助用户有效地管理和监控磁盘使用情况。以下是关于...
2024-07-04 12
linux的常用命令
投稿分享

Linux的常用命令非常丰富，以下是一些基本的和常用的命令，按照它们的用途和功能分类： 文件和目录操作 ls：列出目录内...
2024-07-04 79

评论0

请先
显示验证码
没有账号？注册  忘记密码？