mysql大数据库的备份方法

一是mysqldump,二是xtrabackup备份工具。

mysqldump

mysqldump是一款mysql逻辑备份的工具,它将数据库里面的对象(表)导出作为SQL脚本文件。它是mysql备份常用的备份方法,适合于不同版本mysql之间的升级、迁移等,不过在数据库比较大的时候,效率不高。

mysqldump命令的常见用法:

# 备份单个的数据库,如备份db1这个数据库
mysqldump -uroot -p123456 db1 > /backup/db1_`date +%F`.sql

# 备份所有数据库,-A参数
mysqldump -uroot -p123456 -A > /backup/all_db.sql

# 备份远程主机的数据库,-h指定IP地址,-P(大写P)指定端口
mysqldump -uroot -p123456 -h192.168.30.4 -P3306 db1 > /backup/db1.sql

# 只备份表结构,不备份数据,使用-d参数
mysqldump -uroot -p123456 -d db1 > /backup/db1.sql

# 只备份数据,不备份表结构,使用-t参数
mysqldump -uroot -p123456 -t db1 > /backup/db1.sql 

# 备份指定的多个库,-B参数
mysqldump -uroot -p123456 -B db1 db2 db3 > /backup/db123.sql

# 备份指定的表,如备份db1库里面的stu表
mysqldump -uroot -p123456 db1 stu > /backup/db1_stu.sql

# 备份多个表
mysqldump -uroot -p123456 库1 表1 表2 表3... > db_tables.sql

# 恢复数据
mysqldump -uroot -p123456 dbname < xxx.sql
#或者
mysqldump -uroot -p123456 < xxx.sql

其他参数:

参数说明
-R, –routines备份存储过程和函数数据
–triggers备份触发器数据
–master-data={1或者2}告诉你备份后时刻的binlog位置,如果等于1,则将其打印为CHANGE MASTER命令; 如果等于2,那么该命令将以注释符号为前缀。
–single-transaction对innodb引擎进行热备

一条完整的备份语句(适用innodb引擎):

mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/all_$(date +%F).sql.gz

适用多引擎混合的一条完整备份语句:

mysqldump -A -R --triggers --master-data=2 |gzip  >/backup/all_$(date +%F).sql.gz  

备份脚本
日常可以根据需求进行备份工作,常见是使用shell脚本结合任务计划,下面是一个mysql数据库备份脚本示例,mysql_backup.sh脚本内容如下:

#!/bin/bash 
bakdir=/data/backup # 定义备份文件存放目录
d=`date +%F` # 以日期为命名格式

# 备份三个数据库bbs,blog,db2
for db in bbs blog db2
do
    mysqldump -uroot -p123456 $db > $bakdir/$db\_$d.sql
done
cd $bakdir
# 对备份文件进行压缩
gzip *_$d.sql
# 对备份时间超过一个月的文件进行删除
find ./ -name "*.gz" -mtime +30 |xargs rm

添加任务计划:

[[email protected]_linux1 data]$ chmod +x /usr/local/sbin/mysql_backup.sh
[[email protected]_linux1 data]$ crontab  -e
# 添加内容
30 3 * * * /bin/bash /usr/local/sbin/mysql_backup.sh >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log
# 任务计划内容是:
# 每天凌晨3点30分执行备份脚本,并将正确与错误输出写进mysql_backup.log文件

xtrabackup备份工具

Xtrabackup是由percona开源的免费数据库热备份软件,它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份,也支持MyISAM表和服务器的其他部分。

mysqldump对于导出几个G的数据库或几个表,还是不错的,速度并不慢。一旦数据量达到几十上百G,
无论是对原库的压力还是导出的性能,mysqldump就力不从心了。Percona-Xtrabackup备份工具,
是实现MySQL在线热备工作的不二选择,可进行全量、增量、单表备份和还原

xtrabackup的安装

环境说明:CentOS Linux release 7.5.1804 (Core) IP=192.168.30.3

安装xtrabackup2.4版本:

# 添加yum源
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
# 安装依赖包
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# 下载rpm包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 安装
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

Xtrabackup安装完成后有4个可执行文件,其中2个比较重要的备份工具是innobackupexxtrabackup:

  1. .xtrabackup只能备份InnoDB和XtraDB 两种数据表(在2.4版本之后支持备份MyISAM)
  2. .innobackupex是一个封装xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁

innobackupex的全量备份

innobackupex做全量备份的命令是:

innobackupex --defaults-file=/etc/my.cnf  --host=192.168.30.3  --port=3306 \
--user=root --password=123456  /data/backup/mysql

说明:

  • --defaults-file指定mysql的配置文件,要从配置文件找到datadir的路径;
  • --host指定IP, --port指定端口;
  • --user指定用户,--password指定用户密码;
  • /data/backup/mysql是备份文件所在的目录,使用绝对路径。

备份的数据会存放在指定的目录里,并且自动生成一个以当前日期、时间为名字的目录,例如2019-06-01_15-05-31

[[email protected]_linux1 mysql]$ pwd
/data/backup/mysql
[[email protected]_linux1 mysql]$ ll
总用量 0
drwxr-x--- 2 root root   6 6月   1 15:03 2019-06-01_15-03-22
drwxr-x--- 6 root root 188 6月   1 15:05 2019-06-01_15-05-31
[[email protected]_linux1 mysql]# 

关于备份用户

使用xtrabackup备份可以直接使用root用户,但是生产环境中是很少见的不符合安全规则,因此可以创建一个备份用户进行数据备份的工作,这个备份用户拥有reload, lock tables, replication client, process, super等权限。

mysql  -uroot  -p123456
> grant reload,lock tables,replication client on *.* to 'backupuser'@'localhost'  identified  by  '123456';
> flush  privileges;

创建后,后续的备份工作都使用这个备份用户,mysqldump也可以类似地使用非root用户。

innobackupex全量备份的恢复

innobackupex全量备份的恢复:

# 1.预备恢复:
innobackupex --apply-log /data/backup/mysql/2019-06-01_15-05-31 --user-memroy=2G

# 2.停止MySQL/MariaDB,将datadir目录里面的数据清空(或者挪走)
/etc/init.d/mysql stop
mv /data/mysql /data/mysql_bak

# 3.恢复
innobackupex --defaults-file=/etc/my.cnf  --copy-back  /data/backup/mysql/2019-06-01_15-05-31

# 4.改权限,这个/data/mysql是my.cnf里datadir的目录
chown -R mysql:mysql /data/msyql

#5.启动MySQL/MariaDB
/etc/init.d/mysql start

innobackupex的增量备份

innobackupex --host=192.168.30.3  --port=3306 --user=bakuser --password=your_pass \
 --incremental /data/backup/mysql --incremental-basedir=/data/backup/mysql/last-backup-file

说明:

  • --incremental :表示本次备份是一个增量备份(若针对的上次备份为一个全量备份,这里也可以认为是个差异备份)
  • --incremental-basedir:指定本次增量备份针对的哪一个备份(可以是上个增量,也可以是上个全量) 增量备份可以一环扣一环,比如可以周一做一个全量备份,然后周二做周一的增量,周三做周二的增量…

innobackupex增量备份的恢复
准备阶段:
第一步:停止数据库服务
第二步:删除或者备份datadir里面的数据
第三步:准备全量备份恢复:

innobackupex --apply-log --redo-only  /PATH/TO/全量备份目录

第四步:准备增量备份恢复1:

innobackupex --apply-log --redo-only  /PATH/TO/全量备份目录 --incremental-dir=/PATH/TO/第一次增量备份目录

第五步:准备增量备份恢复2:

innobackupex --apply-log --redo-only /PATH/TO/全量备份目录  --incremental-dir=/PATH/TO/第二次增量备份目录

……
第N步:准备最后一个增量备份恢复:

innobackupex --apply-log /PATH/TO/全量备份目录  --incremental-dir=/PATH/TO/最后一次增量备份目录

准备阶段的最后一步,再次准备全量:

innobackupex --apply-log   /PATH/TO/全量备份目录   #(这次不用加--redo-only了)

恢复阶段:

  1. innobackupex --copy-back /PATH/TO/全量备份目录
  2. 改权限:chown -R mysql:mysql /PATH/TO/datadir
  3. 启动MySQL/MariaDB

说明:–redo-only:表示进行准备(应用日志)工作时,只进行redo操作,只会重做已提交但未应用的事务,不会回滚未提交的事务。原因是后面还有个增量备份,未提交的可能在后面增量备份时进行提交。需要注意的是,最后一个增量备份不需要加这个选型。

mariadb10.3.x以上版本进行备份恢复

MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。
原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。
解决方案是,使用mariabackup,它是MariaDB提供的一个开源工具,用于对InnoDB,Aria和MyISAM表进行物理在线备份。
这个工具是基于Percona的XtraBackup(版本2.3.8)的解决方案:

全量备份:
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01

全量恢复:
停止数据库
清空datadir
mariabackup --prepare --target-dir /data/backup/2019-06-01/ 
mariabackup --copy-back --target-dir /data/backup/2019-06-01/
改属主、属组
启动

增量备份:
mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-06-01
第一个增量:mariabackup --backup --user=xxx --password=xxx  --target-dir /data/backup/2019-06-01_inc1 --incremental-basedir /data/backup/2019-06-01
第二个增量:mariabackup --backup --user=xxx --password=xxx  --target-dir /data/backup/2019-06-01_inc2 --incremental-basedir /data/backup/2019-06-01_inc1

增量恢复:
先准备全备
mariabackup --prepare --target-dir /data/backup/2019-06-01 --apply-log-only
准备第一个增量:
cd /data/backup
mariabackup --prepare --target-dir ./2019-06-01 --incremental-dir ./2019-02-21_inc1 --apply-log-only
准备第二个增量:
mariabackup --prepare --target-dir .2019-06-01 --incremental-dir ./2019-02-21_inc2 --apply-log-only
恢复:
mariabackup --copy-back  --target-dir ./2019-06-01

原文链接:https://segmentfault.com/a/1190000019305858

本文链接: https://www.168itw.com/web-server/mysql-backup/
转载请注明转载自:168itw

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注