MySQL实现备份（1）

• 完全备份和部分备份
• 冷备份、热备份、温备份
  温备份适用于：myisam
  热备份适用于：innodb

• 物理备份和逻辑备份

• 完全备份：备份所有数据

• 部分备份：只备份部分数据内容

两者第一次都需要做一次完全备份。

• 增量备份:备份上一次备份后发生变化的数据。

• 差异备份：备份上一次的完全备份后发生变化的数据文件

• 冷备：读、写操作均不可进行，数据库停止服务

• 温备：读操作可执行；但写操作不可执行
• 热备：读、写操作均可执行

 MyISAM：温备，不支持热备
 InnoDB：都支持(一般使用热备)

• 物理备份：直接复制数据文件进行备份
• 逻辑备份：导出数据库里面的数据另存到一个文件。

逻辑备份是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现

• 用户数据：例如业务数据库
• 二进制日志、innodb的事务日志
• 用户账号、权限等（系统的mysql数据库里面）

• 服务器的配置文件

• 需要备份哪些数据

• 多久备份一次（能容忍丢多少数据）
• 备份产生的数据库负载压力
• 备份需要花费的时间
• 数据恢复需要的时间

备份完了要测试一下能否正常使用

• 物理备份工具：实现冷备份,例如cp、tar、rsync等工具
• 逻辑卷（LVM）快照功能（不常用）

• 逻辑备份工具：mysqldump、xtrabackup、mysqlbackup等工具

• mysqldump：mysql数据库自带的一个客户端工具

• xtrabackup：一个专业的备份工具
• MariaDB Backup： 相当于xtrabackup改用给名

• mysqlbackup：mysql企业版（收费）里面有

• 基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中

• 支持索引存储引擎，对MyISAM存储引擎进行温备，对InnoDB存储引擎支持热备以及完全备份、部分备份等功能。

有三种命令格式：

#格式一
mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义不备份

#格式二
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份

#格式三
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

• 格式一：备份一个数据库里面的所有表：

[root@CentOS8 ~]# mysqldump -u root -p'' hellodb
将hellodb这个数据库的信息查询出来并显示到屏幕

[root@CentOS8 ~]# mysqldump -u root -p'' hellodb  > hellodb.sql
将查询到的数据库信息重定向到一个文件中

• 格式二：可以同时备份多个数据库

[root@CentOS8 ~]# mysqldump  -B hellodb  > hellodb.sql

• 格式三：备份所有数据库
  注意:备份所有数据库的时候只会备份业务数据库和mysql数据库。

information_schema:内存里面的内容
performance_schema：记录性能相关的信息
sys：performance_schema的简化版

[root@CentOS8 ~]# mysqldump -A >hello.sql备份所有数据库的时候

-E, --events：#备份相关的所有event scheduler

-R, --routines：#备份所有存储过程和自定义函数

--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器

--default-character-set=utf8 #指定字符集

-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志

--compact        #去掉注释，适合调试，节约备份占用的空间,生产不使用

-d, --no-data    #只备份表结构,不备份数据,即只备份create table

-t, --no-create-info #只备份数据,不备份表结构,即不备份create table

-n,--no-create-db #不备份create database，可被-A或-B覆盖

--flush-privileges #备份mysql或相关时需要使用

-f, --force       #忽略SQL错误，继续执行

--hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码

-q, --quick     #不缓存查询，直接输出，加快备份速度

--master-data[=num]： #此选项须启用二进制日志,默认值为1

#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，适合于主从复制多机使用

#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原

#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，

注意：数据量大时，可能会导致长时间无法并发访问数据库

-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致

#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备

--single-transaction #备份的时候会开一个单独的事务来进行备份

#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启
事务

#此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥.

备份大型表时，建议将--single-transaction选项和--quick结合一起使用

mysqldump -uroot -p -A -F --single-transaction
      --master-data=1 --flush-privileges
      --default-character-set=utf8 --hex-blob
      > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

参数说明：

-u：指定用户名  -p：指定用户密码

-A：使用mysqldump的格式三，备份整个数据库

-F:执行flush logs命令,生成新的二进制日志文件

--single-transaction: #备份的时候会开一个单独的事务来进行备份

--hex-blob：有二进制数据就需要添加这个

${BACKUP}/fullbak_${BACKUP_TIME}.sql：备份文件存放路径，文件夹/文件_备份时间.sql

注意：
1. 有了-A:备份所有的数据库（自然也就备份了时间、存储过程这些）   -E -R --triggers都不需要加了

2.不建议在mysql中创建时间、触发器、函数这些。因为这样会加大mysql的负载

mysqldump -uroot -p -A -F  -x
      --master-data=1 --flush-privileges
      --default-character-set=utf8  --hex-blob
      > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

使用模式：交互式和脚本式

交互式：
mysql [OPTIONS] [database]
脚本式：

mysql [OPTIONS] < /path/somefile.sql

cat /path/somefile.sql | mysql [OPTIONS]

mysql>source &#xA0; /path/from/somefile.sql

Original: https://www.cnblogs.com/heyongshen/p/16706435.html
Author: 背对背依靠
Title: MySQL实现备份（1）