MySQL数据库备份还原

时间:2020-12-20 06:28:13

本文以CentOS 7 yum安装的MariaDB-server5.5为例,说明MySQL的几种 备份还原方式

将服务器A(192.168.1.100)上的数据库备份,还原到B(192.168.1.200)服务器上

一、冷备份

冷备份是关闭数据库,直接拷贝数据库的datadir目录中的所有文件到新主机上。

1、关闭A的数据库

  # systemctl stop mariadb

2、将数据库目录打包压缩

  # tar zcvf /tmp/back.tar.gz /var/lib/mysql

3、将数据库文件和配置文件都拷贝到B主机

  # scp /etc/my.cnf  192.168.1.200:/tmp

  # scp /tmp/back.tar.gz 192.168.1.200:/tmp

4、在B上解压文件,并将文件拷贝到对应目录

  # tar xf /tmp/back.tar.gz

  # rm /var/lib/mysql/* -rf

# cp -r /tmp/var/lib/mysql/*  /var/lib/mysql/

  # cp /tmp/my.cnf /etc/my.cnf

5、修改B数据目录权限

  # chown -R mysql:mysql /var/lib/mysql

6、启动B的数据库服务

  # systemctl start mariadb

二、mysqldump备份

mysqldump命令是mysql客户端自带的备份软件,可以实现数据库在线备份。

mysqldump支持三种用法

  1、mysqldump [OPTIONS] database [tables]

    选择需要备份的一个数据库,或一个表 。没有创建数据库命令,适用于备份某个表的情况。

  2、mysqldump [OPTIONS] –B DB1 [DB2 DB3...]

-B | --databases       选择需要备份的库,可以选择多个

  3、mysqldump [OPTIONS] –A [OPTIONS]

      把当前实例的所有数据库都进行备份。

mysqldump常见选项:

  -A, --all-databases 备份所有数据库,含create database

-B , --databases db_name… 指定备份的数据库,包括create database语句

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

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

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

--default-character-set=utf8 指定字符集  #工作中要查看实际的字符集

--master-data[=#]: 1或2,此选项须启用二进制日志 会在二进制日志中增加记录点

    1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定

    2:记录为注释的CHANGE MASTER TO语句

   此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--single-transaction)

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

--compact 去掉注释,适合调试,生产不使用

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

-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 不缓存查询,直接输出,加快备份速度

InnoDB建议备份策略

  mysqldump -uroot -A -F -E -R --single-transaction --master-data=1  --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

举例说明:

备份:

在数据库A上进行在线备份
# mysqldump -uroot -A -F -E -R --single-transaction --master-data= --flush-privileges --triggers --default-character-set=utf8 --hex-blob > /tmp/backup.sql
将备份的数据库拷贝到B主机上
# scp /tmp/backup.sql 192.168.1.200:/tmp

还原:
注意:B主机的数据库应该是全新的 启动B主机的数据库
# systemctl start mariadb 导入数据库
# mysql < /tmp/backup.sql

三、通过mysqlbinlog进行差值的备份

此备份方法是配合方法二使用的,通过mysqldump进行定期全备份。再通过mysqlbinlog进行差值备份。还原数据库的时候先还原全备份,然后再还原差值备份的数据。

mysqlbinlog的命令用法

mysqlbinlog [OPTIONS] log_file…

--start-position=#             指定开始位置

--stop-position=#              指定结束为止

--start-datetime=

--stop-datetime=

举例说明

备份:

在数据库A上进行全备份
# mysqldump -uroot -A -F -E -R --single-transaction --master-data= --flush-privileges --triggers --default-character-set=utf8 --hex-blob > /tmp/backup.sql 通过less命令查看全备份备份到的日志位置
# less /tmp/backup.sql
。。。。。
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=;
。。。。。
由此可以看出全备份的时候备份到了'mariadb-bin.000003',的245位置。 通过刚查看到的备份位置进行差值备份
# mysqlbinlog --start-position=245 mariadb-bin.000003 >> /tmp/inc.sql
如果有往后的其他binlog日志也都通过>>追加到/tmp/inc.sql文件中 拷贝备份的数据库到服务器B
# scp /tmp/backup.sql 192.168.1.200:/tmp
# scp /tmp/inc.sql 192.168.1.200:/tmp 还原 安装数据库,启动数据库服务
# systemctl start mariadb 导入数据
# mysql < /tmp/backup.sql
# mysql < /tmp/inc.sql

四、通过xtrabackup备份

xtrabackup是percona公司提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具,可通过EPEL源yum安装,或者去官网下载源码包编译安装。

技术手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

特点:

备份还原过程快速、可靠

备份过程不会打断正在执行的事务

能够基于压缩等功能节约磁盘空间和流量

自动实现备份检验

开源,免费

举例1:xtrabackup实现全备份和还原

备份:
在A做完全备份到/backup
# xtrabackup --backup --target-dir=/backup/
# scp -r /backup/* 192.168.1.200:/backup
# scp /etc/my.cnf 192.168.1.200:/backup 还原:
在B主机上
1)预准备:确保数据一致,提交完成的事务,回滚未完成的事务
# xtrabackup --prepare --target-dir=/backup/
2)复制到数据库目录
注意:数据库目录 (mysql的datadir) 必须为空,MySQL服务不能启动
#xtrabackup --copy-back --target-dir=/backup/
3)还原属性,B主机用的是yum安装的mariadb,数据目录在/var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql
4) 还原配置文件
# cp /backup/my.cnf /etc/my.cnf
4)启动服务
# systemctl start mariadb

举例2:xtrabackup实现全备份+增量备份和还原

备份:

在A做完全备份到/backup/base目录
# xtrabackup --backup --target-dir=/backup/base A数据库变化后做第一次增量备份到/backup/inc1目录
# xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base A数据库变化后做第二次增量备份到/backup/inc2目录
# xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 将A上备份的数据拷贝到B上
# scp -r /backup/* 192.168.1.200:/backup
# scp /etc/my.cnf 192.168.1.200:/backup

还原: 在B主机上
1)预准备:此选项--apply-log-only 阻止回滚未完成的事务
# xtrabackup --prepare --apply-log-only --target-dir=/backup/base 2)合并第1次增量备份到完全备份
# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1 3)合并第2次增量备份到完全备份:最后一次还原不需要加选项--apply-log-only
# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2 4)复制到数据库目录
注意:数据库目录 (mysql的datadir) 必须为空,MySQL服务不能启动
# xtrabackup --copy-back --target-dir=/backup/ 5)还原属性,B主机用的是yum安装的mariadb,数据目录在/var/lib/mysql
# chown -R mysql:mysql /var/lib/mysql 6) 还原配置文件
# cp /backup/my.cnf /etc/my.cnf 7)启动服务
# systemctl start mariadb