文档章节

MySQL (二)

是江山
 是江山
发布于 2017/08/23 09:59
字数 2132
阅读 53
收藏 0

钉钉、微博极速扩容黑科技,点击观看阿里云弹性计算年度发布会!>>>

#备份恢复

##备份指定库:

mysqldump -uroot -p123456 mysql > /tmp/mysqlbak.sql

##备份所有库:

mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql

##恢复库

mysql -uroot -p123456 < /tmp/mysqlbak.sql

##备份指定表:

mysql -uroot -p123456 mysql user > /tmp/user.sql

##只备份表结构:

mysqldump -uroot -p123456 -d mysql > /tmp/mysql_tb.sql

##恢复表

mysql -uroot -p123456 mysql user < /tmp/user.sql

#innobackupex在线备份及恢复(全量和增量) ##简介

Xtrabackup是由percona开发的一个开源软件,它是innodb热备工具ibbackup(收费的商业软件)的一个开源替代品。Xtrabackup由个部分组成:xtrabackup和innobackupex,其中xtrabackup工具用于备份innodb和 xtraDB引擎的表;而innobackupex工具用于备份myisam和innodb引擎的表,本文将介绍如何用innobackupex工具做全量和增量备份。

官网:http://www.percona.com/docs/wiki/percona-xtrabackup:start

##安装 声明:以下操作最好以mysql用户执行。 首先,通过wget下载源码tar包:

wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/source/percona-xtrabackup-2.1.5.tar.gz

安装依赖包:

yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel

解压缩tar:

tar -zxvf percona-xtrabackup-2.1.5.tar.gz
cd percona-xtrabackup-2.1.5

utils/build.sh脚本会根据指定的引擎版本,自动解压缩适当的MySQL源码包并进行编译,这是最简单的安装方式。当你在命令行下不带任何参数执行该脚本时,出现如下提示:

[mysql@epay100 ~/software/percona-xtrabackup-2.1.5 ]$ ./utils/build.sh
Build an xtrabackup binary against the specified InnoDB flavor.


Usage: build.sh CODEBASE
where CODEBASE can be one of the following values or aliases:
  innodb51         | plugin                build against InnoDB plugin in MySQL 5.1
  innodb55         | 5.5                   build against InnoDB in MySQL 5.5
  innodb56         | 5.6,xtradb56,         build against InnoDB in MySQL 5.6
                   | mariadb100
  xtradb51         | xtradb,mariadb51      build against Percona Server with XtraDB 5.1
                   | mariadb52,mariadb53
  xtradb55         | galera55,mariadb55    build against Percona Server with XtraDB 5.5

根据上面提示和你使用的存储引擎及版本,选择相应的参数即可。因为我用的是MySQL 5.6,所以执行如下语句安装:

./utils/build.sh innodb56

以上语句执行成功后,表示安装完成。最后,把生成的二进制文件拷贝到一个自定义目录下(本例中为/home/mysql/admin/bin/percona-xtrabackup-2.1.5),并把该目录放到环境变量PATH中。

cp ./innobackupex /home/mysql/admin/bin/percona-xtrabackup-2.1.5
cp ./src/xtrabackup_56 ./src/xbstream /home/mysql/admin/bin/percona-xtrabackup-2.1.5  

##全备及其恢复 ###全备: 执行如下语句进行全备:

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** /backup/mysql/data

该语句将拷贝数据文件(由my.cnf里的变量datadir指定)至备份目录下(/backup/mysql/data),注意:如果不指定--defaults-file,默认值为/etc/my.cnf。 备份成功后,将在备份目录下创建一个时间戳目录(本例创建的目录为/backup/mysql/data/2013-10-29_09-05-25),在该目录下存放备份文件。

##恢复: 恢复之前,要先关闭数据库,并且删除数据文件和日志文件。

innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=*** --use-memory=4G --apply-log /backup/mysql/data/2013-10-29_09-05-25
innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=***  --copy-back /backup/mysql/data/2013-10-29_09-05-25

从什么可以看出,恢复分为两个步骤,第1步是apply-log,为了加快速度,一般建议设置--use-memory,这个步骤完成之后,目录/backup/mysql/data/2013-10-29_09-05-25下的备份文件已经准备就绪。 第2步是copy-back,即把备份文件拷贝至原数据目录下。 恢复完成之后,一定要记得检查数据目录的所有者和权限是否正确。

###增量备份及其恢复 注意:innobackupex 增量备份仅针对InnoDB这类支持事务的引擎,对于MyISAM等引擎,则仍然是全备。

###增量备份: 增量备份需要基于全备,先假设我们已经有了一个全备(/backup/mysql/data/2013-10-29_09-05-25),我们需要在该全备的基础上做增量备份。

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** --incremental-basedir=/backup/mysql/data/2013-10-29_09-05-25 --incremental /backup/mysql/data

其中--incremental-basedir指向全备目录,--incremental指向增量备份的目录。 上面语句执行成功之后,会在--incremental执行的目录下创建一个时间戳子目录(本例中为:/backup/mysql/data/2013-10-29_09-52-37),在该目录下存放着增量备份的所有文件。 在备份目录下,有一个文件xtrabackup_checkpoints记录着备份信息,全备的信息如下:

backup_type = full-backuped
from_lsn = 0
to_lsn = 563759005914
last_lsn = 563759005914
基于该全备的增量备份的信息如下:
backup_type = incremental
from_lsn = 563759005914
to_lsn = 574765133284
last_lsn = 574765133284

从上面可以看出,增量备份的from_lsn正好等于全备的to_lsn。 那么,我们是否可以在增量备份的基础上再做增量备份呢?答案是肯定的,只要把--incremental-basedir执行上一次增量备份的目录即可,如下所示:

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** --incremental-basedir=/backup/mysql/data/2013-10-29_09-52-37 --incremental /backup/mysql/data
它的xtrabackup_checkpoints记录着备份信息如下:
backup_type = incremental
from_lsn = 574765133284
to_lsn = 574770200380
last_lsn = 574770200950

可以看到,该增量备份的from_lsn是从上一次增量备份的to_lsn开始的。

##恢复:

增量备份的恢复比全备要复杂很多,第一步是在所有备份目录下重做已提交的日志,如:

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

其中BASE-DIR是指全备目录,INCREMENTAL-DIR-1是指第一次的增量备份,INCREMENTAL-DIR-2是指第二次的增量备份,以此类推。 这里要注意的是:最后一步的增量备份并没有--redo-only选项!还有,可以使用--use_memory提高性能。 以上语句执行成功之后,最终数据在BASE-DIR(即全备目录)下。 第一步完成之后,我们开始第二步:回滚未完成的日志:

innobackupex --apply-log BASE-DIR

上面执行完之后,BASE-DIR里的备份文件已完全准备就绪,最后一步是拷贝:

innobackupex --copy-back BASE-DIR

同样地,拷贝结束之后,记得检查下数据目录的权限是否正确。

常见错误及解决方法 错误:

131028 17:45:57  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
innobackupex: Error: Failed to connect to MySQL server as DBD::mysql module is not installed at /home/mysql/admin/bin/percona-xtrabackup-2.1.5/innobackupex line 2913.

解决方法:

yum -y install perl-DBD-MySQL.x86_64

错误:

sh: xtrabackup_55: command not found
innobackupex: Error: no 'mysqld' group in MySQL options at /home/mysql/admin/bin/percona-xtrabackup-2.1.6/innobackupex line 4341.

解决方法:

cp xtrabackup_innodb55 xtrabackup_55

#实践 创建mysql用户

创建mysql用户:
mysql -uroot -p'123456'
Welcome to the MySQL monitor.
mysql> grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *.* to 'bakuser'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.13 sec)
#该用户只需要有备份权限即可,所以在创建用户时只授予其部分权限
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
#刷新!
mysql> quit
Bye

备份

创建一个目录用于存放备份文件:

mkdir /data/backup

开始备份:

innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock /data/backup
##备份完成

说明:

  • -defaults-file:指定mysql的配置文件(该参数必须放在首位)
  • -user:指定用于备份的mysql用户
  • -password:mysql用户密码
  • -S:=socket,指定MySQL的socket文件(也可以使用-h,该参数非必须!)

查看备份文件:

[root@jiangshan]# cd /data/backup/ && ls
2017-08-23_08-49-22
[root@jiangshan]# # ls 2017-08-23_08-49-22/
backup-my.cnf  mysql               test                    xtrabackup_info
ibdata1        performance_schema  xtrabackup_checkpoints  xtrabackup_logfile
注: ibdata1为核心文件,其中存放的是:储存格式;INNODB类型数据状态下,ibdata用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已。

恢复数据库

在恢复数据库之前需要先停止MySQL服务!

终止mysql服务:

[root@jiangshan]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!

恢复准备:

[root@jiangshan]# mv /data/mysql /data/mysql.bak
[root@jiangshan]# mkdir /data/mysql
[root@jiangshan]# chown mysql:mysql /data/mysql
#进行备份前将原/data/mysql中文件清空
#在此只为学习,所以只对该文件进行更名,然后重建该目录并更改权限

开始恢复:

匹配数据文件:

[root@jiangshan]# innobackupex --use-memory=256M --apply-log /data/backup/2017-08-23_08-49-22

说明: 进行数据库恢复时,先匹配用于恢复的数据文件。 --use-memory:指定执行数据库恢复操作时的运行内存(添加该选项的目的通过指定其运行内存来加快恢复速度,可不加该参数)。 --apply-log:指定要恢复的数据文件(来自备份文件)

恢复:

[root@jiangshan]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/2017-08-23_08-49-22/

至此,数据库恢复完成! 说明:

--copy-back:指定用于恢复的数据文件目录

#扩展 SQL语句教程

什么是事务?事务的特性有哪些?

根据binlog恢复指定时间段的数据

mysql字符集调整

使用xtrabackup备份innodb引擎的数据库

innobackupex 备份 Xtrabackup 增量备份

上一篇: NFS
是江山
粉丝 4
博文 44
码字总数 24060
作品 0
苏州
运维
私信 提问
加载中
请先登录后再评论。
ubuntu16.04安装mysql5.6

ubuntu16.04安装mysql5.6 apt-get install software-properties-common sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe' sudo apt-get update sudo apt i......

osc_ogi0qclx
2019/08/26
1
0
转:配置mysql允许远程连接的方法

转自:https://www.cnblogs.com/linjiqin/p/5270938.html 默认情况下,mysql只允许本地登录,如果要开启远程连接,则需要修改/etc/mysql/my.conf文件。 一、修改/etc/mysql/my.conf 找到bin...

osc_zj67f075
2018/11/22
2
0
ubuntu下mysql的一些相关常用命令

查看mysql状态,是否启动 sudo service mysql status 查看所有服务的状态: sudo service --status-all 启动mysql: 方式一:sudo /etc/init.d/mysql start 方式二:sudo start mysql 方式三:...

木子丰
2014/06/05
105
0
MYSQL中添加、更新、删除数据

一:添加数据 1.准备工作,和以前一样,要先使用一个数据库,并添加一张数据表,具体操作如下: 1)使用数据库 mysql> USE itcsat; Database changed 2)创建数据表 mysql> CREATE TABLE st...

osc_r1i0f044
2019/11/09
1
0
浅析MySQL 8忘记密码处理方式

对MySQL有研究的读者,可能会发现MySQL更新很快,在安装方式上,MySQL提供了两种经典安装方式:解压式和一键式,虽然是两种安装方式,但我更提倡选择解压式安装,不仅快,还干净。在操作系统...

osc_ayf3s08t
2019/02/11
2
0

没有更多内容

加载失败,请刷新页面

加载更多

还在用Swagger(丝袜哥)生成接口文档?我推荐你试试它.....

JApiDocs是一个无需额外注解、开箱即用的SpringBoot接口文档生成工具。 编写和维护API文档这个事情,对于后端程序员来说,是一件恼人但又不得不做的事情,我们都不喜欢写文档,但除非项目前后...

路人甲Java
07/09
7
0
智能仓储的独角兽逻辑

智能仓储的主要应用市场在哪里?客户的付费意愿和付费能力如何? 1、仓储设备具备标准化和通用化特点 由于电商和新零售的快速发展,轻工业品零售仓库的需求量大幅增加。而中国又是全球轻工业...

logiter
2019/08/23
14
0
可是小腿哪能扭过大腿

父亲是一个特别勤苦的人,他从不睡懒觉,每天天麻麻亮,或是下地干活,或是在家搞副业,或是拿着铁锨、粪筐,到路边,到村子周围,到牲畜常出入的地方,去拾粪蛋子,为庄稼积攒肥料,父亲不仅...

瑾123
35分钟前
16
0
一个volatile跟面试官扯了半个小时

《安琪拉与面试官二三事》系列文章,本文是此系列第三篇 一个HashMap能跟面试官扯上半个小时 一个synchronized跟面试官扯了半个小时 欢迎关注Wx公众号:【安琪拉的博客】—揭秘Java后端技术,...

osc_6ls9vwji
36分钟前
0
0
内网渗透靶机-VulnStack 2

WEB服务器:windows2008系统 外网网卡IP:192.168.1.152 内网网卡IP:10.10.10.80 域成员:windows server 2003系统 网卡IP:10.10.10.200 域控服务器:windows server 2008系统 网卡IP:192...

dnsil
07/10
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部