mysqldump全备数据库 mysql数据库全量备份

备份分为逻辑备份和物理备份
逻辑备份：是备份sql，耗时较长

为什么要进行备份？

简单点说，硬盘损坏，人为误操作都会造成数据的丢失，这种后果往往是极为严重的，所以要通过备份减少风险。

备份分为什么方式？

• 备份分为全量备份和增量备份
  全量备份：对整个数据库进行备份，数据是完整的。
  增量备份：对某一个节点之后的数据进行备份，只备份该节点后的少量数据。

如何进行全量备份？

全量备份可使用mysqldump进行备份，mysqldump不仅可以备份整个库，也可以对某个表进行备份。在备份之前，我们先了解下mysqldump参数和用法。

mysqldump的常用参数(OPTOINS)：

-u：为指定用户名

-p：为指定密码

--single-transaction：Innodb下使用，为在备份之前启动一个事务，来保证数据的一致性，
不过要确保没有其他DDL操作在执行，因为Innodb隔离级别无法对ddl进行隔离

-l：即--lock-tables，非事务引擎下使用，把一个database下的所有表锁起来保证事务一致性

-x：即--lock-all-tables，对一个实例下的所有的database进行加锁，来保证数据一致性，这时数据库只能读不能写

--master-data=[1/2]：为1时只记录了change master语句，为2时change master语句以备份方式存储在文件中，
使用该参数时，不指定--single-transaction的情况下，会默认使用--lock-all-tables

-R，-routines：备份数据库中存在的存储过程

--triggers：备份数据库中所存在的触发器

-E，–events：备份数据库中的调度事件

--hex-blob：以16进制的形式来保存

-tab=path：path为指定路径，使用该参数后会在该路径下生成两个文件，一个存储表结构，一个存储表数据

-w，–where=：即where条件，用于导出指定条件的数据，不过只支持单表导出

mysqldump备份语法：

• 对所有的库进行备份

mysqldump [OPTOINS] --all-databases [OPTOINS]

• 对一个或多个库进行备份

mysqldump [OPTIONS] --databases [OPTIONS] db1 db2...

• 对一个或多个表进行备份

mysqldump [OPTIONS] database_name table_name1 table_name2...

备份前准备

• 创建备份用户

create user 'backupUser'@'localhost' identified by '密码';

• 赋权

grant select,reload,lock tables,replication client,show view,event,process,file on *.* to 'backupUser'@'localhost';

• 刷新权限

flush privileges;

• 创建用于存放备份文件的文件夹
  注意：备份时需要在linux命令行下执行（即写shell脚本），而不是在mysql命令行下执行

cd /usr/local/
mkdir backupData

开始备份

首先进入备份文件夹

cd /usr/local/backupData

1.对全部数据库进行备份

对一台主机下的全部数据库进行备份，并将内容备份到allBase_backupData.sql中

mysqldump -ubackupUser -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > allBase_backupData.sql

因为指定了–master-data=1，所以备份生成的文件中change master没有被注释

• 查看备份内容：
  最好使用more命令进行查看，按enter键即可逐行查看内容，使用/+关键字可直接定位到关键字位置（如/CREATE）

more allBase_backupData.sql

• 查看备份是否完整
  举个查看数据库的例子，存储过程等自己根据关键字自己查看

grep 'Current Database' allBase_backupData.sql

2.对指定数据库进行备份

• 对秒杀库miaosha进行备份，并将数据备份到本目录下的miaosha_backupData.sql文件中
  除了备份表结构和数据外，还备份： --routines为备份事务 --triggers为备份触发器 --events为备份调度事件

mysqldump -ubackupUser -p --master-data=2 --single-transaction --routines --triggers --events miaosha > miaosha_backupData.sql

因为指定了–master-data=2，所以备份生成的文件中change master会被注释

• 查看备份内容：
  最好使用more命令进行查看，按enter键即可逐行查看内容，使用/+关键字可直接定位到关键字位置（如/CREATE）

more miaosha_backupData.sql

• 查看备份是否完整
  举个查看表结构的例子，存储过程等自己根据关键字自己查看

grep 'CREATE TABLE' miaosha_backupData.sql

3.对指定表进行备份

对miaosha库下的goods表和user表进行备份（备份多个表需用空格分开）

mysqldump -ubackupUser -p --master-data=2 --single-transaction --routines --triggers --events miaosha goods user > table_backupData.sql

4.使用-tab备份到指定目录

备份到指定目录需要用到 -tab 参数

• 1.指定目录备份之前先查看secure_file_priv配置的路径，这个参数是来限制导入导出能在哪个文件夹下执行
  当secure_file_priv的值为null ，表示不允许导入导出
  当secure_file_priv的值为/var/lib/mysql-files/ ，表示导入导出只能发生在/var/lib/mysql-files/ 目录下
  当secure_file_priv的值为空时，表示不对导入导出做限制

show global variables like '%secure%';

我这里修改了默认值/var/lib/mysql-files/ ，重新指定了路径

• 2.如何修改secure_file_priv？
  编辑mysql的my.cnf文件，添加secure_file_priv=路径

vim /etc/my.cnf
#最后面添加,路径自行修改
secure_file_priv=/usr/local/backupData/miaosha_db/

• 3.需要注意的是，backupUser需要有写文件的权限（即file权限），上面赋权已经给了，没有请赋权.

grant file on *.* to 'backupUser'@'localhost';

• 4.还需要注意的是linux的登录者需要对指定的目录有操作权限

cd /usr/local/backupData/
chown mysql:mysql miaosha_db

• 5.开始备份

mysqldump -ubackupUser -p --master-data=2 --single-transaction --routines --triggers --events --tab='/usr/local/backupData/miaosha_db' miaosha

因为使用了-tab，查看miaosha_db下的文件，可以看到每个表都有两个文件，一个sql文件存表结构，一个txt文件存数据

5.根据where条件备份表中的指定内容

–where 后面指定条件即可，需指定哪个库的哪个表

mysqldump -ubackupUser -p --master-data=2 --single-transaction --where 'id > 100 and id < 400' miaosha goods > goods_backupData.sql

如何进行增量备份？

增量备份可以使用binlog来实现，binlog是Mysql产生的二进制文件，记录了数据库变更的SQL信息，并保存了具体的时间节点，所以我们可以使用binlog对某一时间节点到另一时间节点之间的数据进行备份。
binlog增量备份实际上是模拟了mysql复制的一个进程，是通过slave的io进程来进行binlog日志实时获取的。

• 创建拥有replication slave权限的账号

create user 'slaveUser'@'localhost' identified by '密码';

• 赋权replication slave权限

grant replication slave on *.* to 'slaveUser'@'localhost';

• 创建存放binlog的文件夹

mkdir backup_binlog
cd backup_binlog

• 进行增量备份
  binlog.000006表示从binlog.000006开始备份，前面的不进行备份

#--raw 输出的是一个 raw 格式的二进制日志
#--read-from-remote-server 从 MySQL 服务器读取日志
#--stop-never 表示不会停止，一直在后台进行实时备份
mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -uslaveUser -p密码 binlog.000006

此时该备份会作为一个子线程运行，不会中断

• 查看是否成功进行增量备份

#刷新日志
flush logs;
#查看二进制日志
show binary logs;
#进入我们创建的binlog文件夹
cd /var/lib/mysql/backup_binlog
#查看文件夹内是否有备份文件
ll