mysql之冷备和mysqldump、mydumper、xtrabackup备份

1.冷备流程：

停库备份,冷备份一般用于非核心业务，这类业务一般都允许停库。

在停止数据库后，将数据文件拷贝出来，然后对原始数据文件进行备份。

流程:

1.关闭数据库备份          2.拷贝数据文件

3.压缩数据文件           4.重启数据库

2.热备

热备份分逻辑备份和物理备份.,逻辑备份的是SQL语句,将数据库里面的数据,转储为SQL文件.

在线备份,一般是使用mysqldump和xtrabackup进行备份和恢复

Mysqldump适合规模比较小的库，业务并不繁忙的数据库，一般都是选择mysqldump。

Mysqldump和mydumper都是逻辑备份工具,而xtrabackup是物理备份工具.

Mysqldump是mysql自带的工具,而xtrabackup是percona公司开发的工具.

如果数据量大于100G,逻辑备份恢复比较慢,可以优先考虑物理备份.

        2.1 mysqldump备份原理及参数介绍

mysqldump是 官方工具,本身mysql自带,不用安装.
Mysqldump备份原理:将数据库的数据,查询出来,然后通过拼接语句,形成sql文件.故mysqldump是逻辑备份.

如何保证备份的一致性,以及mysqldump参数的讲解:

--single-transaction:可以保证在不加锁的情况下,备份一致性数据.

参数原理:在备份的时候mysqldump---->mysqld(客户端连接到服务器端),在备份会中,--single-transaction会显式的将会话的事务隔离级别改为RR隔离级别,并且开启一个事务,RR隔离级别下,备份数据都是开启事务的时候的数据的快照,于是保证了备份的一致性.

故一般备份情况下加这个参数来保证数据的一致性.

优点: 1.不加锁 2.可以保证数据的备份一致性

实际在数据备份的时候,获取binlog位置的时候,是需要加锁, 加锁--获取binlog位置--解锁--备份数据.加锁只是在获取binlog位置的一瞬间然后释放,在备份开始后的时间是不加锁的.

--master-data 1|2  一般用2 , 加注释,作用是打印备份的数据的binlog pos点到备份文件头部.可通过查看备份文件知道备份的pos点.

head -n 35 test.txt 查看文本前35行一般就看查看到pos点.

于是在保证备份一致性(不加锁)的情况下语句是:

mysqldump --single-transaction  --master-data=2

可通过mysqldump --help查看其它参数信息.

-A, --all-databases 作用是备份所有数据库(包括mysql数据库)

--add-drop-database 和 --add-drop-table(--skip-add-drop-table) 参数:意思是如果库或者表已经存在,就删除掉尽量在恢复之前,就是手动删掉数据.

-c, --complete-insert  完整insert (不是简写的,全写:insert into t1 (id,name) values(1,'mxt');)

-B, --databases   指定备份的数据

-K, --disable-keys  作用是使索引失效,防止大批量数据插入的开销.

-h  作用是连接哪个主机.(host.)

-x, --lock-all-tables  ----备份加锁.(一般不用)

-l, --lock-tables   Lock all tables for read.(加锁,一般不用)

--max-allowed-packet=#  mysqldump和mysqld都需要设置,mysqld允许最大的数据包,加到配置文件max-allowed-packet=32M(在mysqld和mysqldump里都设置.)

-d, --no-data      ----------只备份表结构

-q, --quick       --------- 不经过内存,如果内存比较小,直接落盘到文件.(可以减少对内存的占用)

--replace      ------ insert into 更改成 replace  INTO.

--hex-blob     -------- 表里面有二进制的列(blob text字段) ,需要备份加这个参数.

--triggers  ------ 备份触发器

-E, --events  ---------备份事件

上面大多数参数对MYISAM存储引擎不起作用,因为其不支持事务,没有RR模式,故无法保证事务一致性,故备份需加锁.

mysqldump备份特点：

1.简单,不需要额外安装,sql文件易读(.sql  .txt ....是文本文件,文件后缀不能决定文件的性质)

2.该工具单线程,不能并发备份恢复 ,因为是逻辑备份,数据量越大,恢复越慢.

 

       2.2mysqldump备份及恢复语句

备份所有数据库:

mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --single-transaction  -A > /tmp/all.sql

 

备份单个数据库:

mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --single-transaction  -B test1 > /tmp/all.sql

 

导出表:

mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --single-transaction  -B test1 --tables t3 > t1.sql

 

导出表结构:

mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --single-transaction  -A -d > /tmp/all.sql

 

mysqldump导入数据：

方法1.  mysql -uroot -proot -S /tmp/mysql3306.sock < all.sql

方法2.  mysql > source /tmp/all.sql

对于恢复数据:  尽量不要跨机房和网段等恢复,尽量将备份文件拷贝到mysqld所在机器上,进行恢复.

  2.3 mydumper介绍

由来:Mydumper也是逻辑备份.备份时过程与mysqldump相似,都是瞬间开启RR模式然后释放锁,保证了数据的一致性.弥补了mysqldump只能单线程备份的特点,其设计中可以多线程拷贝数据,.但消耗资源.并且mysqldump是第三方开发工具,使用时需要安装.

/usr/local/bin/mydumper 备份工具

/usr/local/bin/myloader 恢复工具

Mydumper进行备份时,有一个主线程和多个备份线程完成.不用加参数-single-transaction和--master-datea=2等参数.

/usr/local/bin/mydumper 备份工具

/usr/local/bin/myloader 恢复工具

Mydumper进行备份时,有一个主线程和多个备份线程完成.不用加参数-single-transaction和--master-datea=2等参数.

 1.mydumper的安装

安装mydumper步骤:

 1.yum -y install cmake pcre-devel gcc-c++ glib2-devel zlib*

 2.tar zxvf mydumper-0.9.1.tar.gz

 3.cd mydumper-0.9.1

 4.mkdir build

 5.cd build

 6.cmake ..

 7.make

 8.make install

安装完成后再ls  /usr/local/bin里有该命令.

其中mydumper是逻辑备份工具,myloader是逻辑恢复工具.

查看使用方法报错: mydumper --help报错。

解决方法:

执行:  ls -l /usr/local/mysql/lib/libmysqlclient.so.20*

执行:  ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib/

再vim /etc/ld.so.conf在里面添加一行:  export LD_LIBRARY_PATH=/usr/local/mysql/lib.里面内容如下:

include ld.so.conf.d/*.conf

export LD_LIBRARY_PATH=/usr/local/mysql/lib