MySQL数据库应如何备份与恢复?

  • 回答 (6)
  • 关注 (0)
  • 查看 (205)

数据库一般存放着企业最为重要的数据。但数据库服务器总会遇到一些不可抗拒因素,导致数据丢失或损坏。我在用MySQL,我想问问数据库应如何备份与恢复?

毛莹毛莹提问于
砸蛋大叔回答于

mysqldump+binlog

命令的语法格式

mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库mysqldump [OPTIONS] --all-databases [OPTIONS]:备份所有库

其他选项

-x, --lock-all-tables:锁定所有表-l, --lock-tables:锁定备份的表--single-transaction:启动一个大的单一事务实现备份-C, --compress:压缩传输-E, --events:备份指定库的事件调度器-R, --routines:备份存储过程和存储函数--triggers:备份触发器--master-data={0|1|2} 0:不记录 1:记录CHANGE MASTER TO语句;此语句未被注释 2:记录为注释语句-F,--flush-logs:锁定表之后执行flush logs命令

备份过程

1.准备备份目录

2.准备备份数据库及表

3.进行完整备份

4.向表中插入数据

5.进行增量备份,备份二进制日志

6.继续插入数据,在没备份的情况下删除数据库,模拟误操作

数据恢复

#建议关闭二进制日志,关闭其它用户连接MariaDB [(none)]> set session sql_log_bin=0;

首先要保护最后的二进制日志,查看删除操作之前的position值

[root@MariaDB ~]# mysqlbinlog /mydata/data/mysql-bin.000015

将最后操作的二进制日志进行备份 ,再导入之前所有的备份,最后查看数据库即可。

这个不适用于大型数据库,因为备份速度较慢

lvm2快照+binlog

备份过程

#请求锁定所有表MariaDB [test]> flush tables with read lock;#滚动日志MariaDB [test]> flush logs;#记录二进制日志位置MariaDB [test]> show master status;#创建快照卷[root@MariaDB ~]# lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r #释放全局锁MariaDB [test]> unlock tables;#创建快照挂载点[root@MariaDB ~]# mkdir /snap#挂载快照卷[root@MariaDB ~]# mount /dev/myvg/mydata-snap /snap#备份数据库[root@MariaDB ~]# cp -a /snap /backup/#增量备份,查看完整备份之前的二进制日志位置和最后出错操作前一位置[root@MariaDB ~]# mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mysql-bin.000016 > /backup/binlog/binlog-`date +%F_%T`.sql

数据恢复

[root@MariaDB ~]# service mysqld stop[root@MariaDB ~]# rm -rf /mydata/data/*[root@MariaDB ~]# cp -a /backup/snap/* /mydata/data[root@MariaDB ~]# service mysqld start[root@MariaDB ~]# mysql < /backup/binlog/binlog-2015-05-21_20\:23\:41.sql

基于物理备份,数据已成功恢复

xtrabackup

Xtrabackup是mysql数据库备份工具,这是惟一一款开源的且能对innodb和xtradb数据库进行热备的工具。

安装

[root@MariaDB ~]# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm -y

创建最小权限备份用户

备份过程

完全备份

[root@MariaDB ~]# innobackupex --user=bakupuser --password=bakuppass /backup/innobackupex: Backup created in directory '/backup/2015-05-21_21-55-08'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 245150521 21:55:16 innobackupex: Connection to database server closed150521 21:55:16 innobackupex: completed OK!

如果出现下面的错误,请在my.cnf文件[mysqld] 中添加innodb_log_file_size = 5M ,并重启服务

InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytesInnoDB: than specified in the .cnf file 50331648 bytes!innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.

增量备份

每个InnoDB的页面都会有一个LSN信息,当相关的数据发生改变,相关的页面的LSN就会自动增长。

对于MyISAM表而言,执行增量备份其实进行的是完全备份

添加数据后,做增量备份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2015-05-21_21-55-08/innobackupex: Backup created in directory '/backup/2015-05-21_22-26-42'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 788150521 22:26:57 innobackupex: Connection to database server closed150521 22:26:57 innobackupex: completed OK!

再次添加数据 ,再次做增量备份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2015-05-21_22-26-42/ #在第一次增量备份的基础上做增量备份innobackupex: Backup created in directory '/backup/2015-05-21_22-32-01'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 1056150521 22:32:10 innobackupex: Connection to database server closed150521 22:32:10 innobackupex: completed OK!

数据恢复

# innobackupex --apply-log --redo-only BASE-DIR

继续执行:

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

接着是第二个增量:

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

完整备份准备

[root@MariaDB ~]# innobackupex --apply-log /backup/2015-05-21_21-55-08/InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2766618150521 23:02:43 innobackupex: completed OK!

增量备份准备

[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ --incremental-dir=/backup/2015-05-21_22-26-42/[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ --incremental-dir=/backup/2015-05-21_22-32-01/

恢复阶段

还原备份,即完全备份

[root@MariaDB ~]# innobackupex --copy-back /backup/2015-05-21_21-55-08/[root@MariaDB ~]# chown -R mysql.mysql /mydata/data/[root@MariaDB ~]# service mysqld start

看看数据是否已经恢复。

好好看解密右转天后不带记忆与方向生活回答于

以MySQL为例,Navicat可以实现异地自动备份MySQL数据库

打开Navicat,点击“连接”按钮 -> 在弹出的新“连接”窗口中输入所需信息,确定 -> 选择创建的连接 -> 选择要备份的数据库,点击“计划任务”按钮 -> 选择“创建批处理任务” -> 选择“Backup”,点击需要备份的数据库 -> 输入备份相关信息并保存 -> 右键选择任务,“设置计划任务” -> 选择“计划”选项卡 -> 新建一个计划,点击"高级”选项,设置备份方式 -> 输入本机管理员密码

若恢复数据库,双击备份文件,点击“开始”即可。

大叔也犯二为了此岸的完整构建永无法到达的彼岸回答于

1.请准备FTP备份服务器:安装vsftpd软件包>创建vsftpd 用户>配置管理vsftpd>允许本地用户登入和写,只有/etc/vsftpd/user_list文件下用户可以登入ftp服务器>开启虚拟用户,其对应本地用户>创建虚拟用户文件>添加允许登入ftp的用户到/etc/vsftpd/user_list文件>启动vsftpd服务

2.创建MYSQL备份脚本:备份数据库密码>备份当前时间>本地备份保留时间>备份服务器备份保留时间>mysql备份执行命令文件路径>进程文件存放目录>本地备份目录>判断备份目录是否存在,如果不存在则创建>判断当前时间目录是否存在,如果不存在则创建>判断旧目录是否存在,存在则删除>将备份数据备份到FTP备份服务器

3.执行代码:./mysql_bak.sh > mysqlbak.log   

查看是否完成异地备份

为啥吃兔兔工程师回答于

可以进行异地备份。

异地备份的数据复制目前有如下实现方式:

基于主机。基于主机的数据复制技术,可以不考虑存储系统的同构问题,只要保持主机是相同的操作系统即可,而目前也存在支持异构主机之间的数据复制软件,如BakBone NetVault Replicator就可以支持异构服务器之间的数据复制,可以支持跨越广域网的远程实时复制。缺点是需要占用一点主机资源。

基于存储系统。利用存储系统提供的数据复制软件,复制的数据流通过存储系统之间传递,和主机无关。这种方式的优势是数据复制不占用主机资源,不足之处是需要灾备中心的存储系统和生产中心的存储系统有严格的兼容性要求,一般需要来自同一个厂家的存储系统,这样对用户的灾备中心的存储系统的选型带来了限制。

基于光纤交换机。这项技术正在发展中,利用光纤交换机的新功能,或者利用管理软件控制光纤交换机,对存储系统进行虚拟化,然后管理软件对管理的虚拟存储池进行卷管理、卷复制、卷镜像等技术,来实现数据的远程复制。比较典型的有Storag-age,Falcon等。

基于应用的数据复制。这项技术有一定局限性,都是针对具体的应用。主要利用数据库自身提供的复制模块来完成,比如OracleDataGuard,Sybase Replication 等。

隨心之所願冒泡算法工程师。回答于

备份方案

①mysqldump+binlog: 完全备份,通过备份二进制日志实现增量备份

②lvm2快照+binlog:几乎热备,物理备份

③xtrabackup: 对InnoDB:热备,支持完全备份和增量备份;对MyISAM:温备,只支持完全备份

须知

备份某一个数据库和备份所有库是有区别的,要备份某一个库要确保所有的InnoDB存储引擎的表都是存放在单个表空间中,否则必须执行全库备份

女朝会电商回答于

可以使用异地备份,将数据在另外的地方实时产生一份可用的副本。此副本的使用不需要做数据恢复,可以将副本立即投入使用。

可能回答问题的人

  • 西风

    renzha.net · 站长 (已认证)

    7 粉丝1 提问77 回答
  • HKC

    红客学院 · 创始人 (已认证)

    26 粉丝7 提问15 回答
  • 小书虫

    0 粉丝1 提问12 回答
  • 四无君

    0 粉丝0 提问11 回答

扫码关注云+社区

领取腾讯云代金券