MySQL (二)
MySQL (二)
是江山 发表于4个月前
MySQL (二)
  • 发表于 4个月前
  • 阅读 6
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

#备份恢复

##备份指定库:

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 增量备份

共有 人打赏支持
粉丝 5
博文 44
码字总数 24060
×
是江山
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: