mysql数据库备份 与恢复 win下的mysql数据备份

一、1 概述

面试DBA只要他能说出 mysqldump后再,再能说出xtrabackup就是完美答案了,说

出mysqldump,不在说话,就PASS,想想就可怕.如果几百G的库用,mysqdump是不是要凉凉了

所以生产中我们常用的备份方式就这两种

1. mysqldump

2. xtrabackup

二、 备份方法

当然我们还是要搞清楚整套的大概备份思路

mysql备份的方式一般有两种:冷备和热备

2-1 冷备

冷备最简单,就是在mysql关闭的情况下,将整个数据库目录给拷贝走.恢复就是将备份的数据库文件替换现在已经有

的文件.重启mysql服务,就这么简单

cp xx xxx

2-2 热备

热备就是数据库在运行的状态下备份,不影响现有的业务的正常进行

分为逻辑备份和裸文件备份

(1)逻辑备份

逻辑备份就是通过mysqldump,select into outfile,mydump等等来实现的备份

这些备份方式今天时间比较紧,就不说了.大家可以自行百度

这个备份方式紧紧推荐备份一些测试数据的时候来用

MySQL数据的导出和导入工具:mysqldump

导出数据：

语法： mysqldump [TMPIONS] database [tables] >导出的文件名.sql

A:导出所有数据库

mysqldump -uroot -p123456 -A >all.sql

mysqldump -uroot -p123456 --all-databases >all2.sql

参数-A代表所有，等同于—all-databases

B:导出某个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql  # 注意是-p空格后是数据库名，不是密码。

mysqldump -uroot -p123456 book >book.sql

vim book.sql

C:导出单张表

mysqldump -uroot -p123456 book books >books.sql  #导出book库books表

D:导出库的表结构

mysqldump -uroot -p123456 -d book>booktable.sql  #只导出book库的表结构

E：只导出数据

mysqldump -uroot -p123456 -t book>bookdata.sql  #只导出book库中的数据

F:导出数据库，并自动生成库的创建语句

mysqldump -uroot -p123456 -B book2 >book2.sql

mysql -uroot -p123456 < book2.sql  导入不用指定数据名

导入数据：

A：导入所有数据库

mysql -uroot -p123456 <all.sql

B：导入数据库

Mysql -uroot -p123456 book <book.sql  #如果导入时，没有对应的数据库，需要你手动创建一下：mysql> create database book;

使用source导入

mysql> create database book;

mysql> use book;

mysql> source /root/book.sql

c:导入表

mysql> drop table books;

mysql> source /root/books.sql;   ##导入表时，不需要重新，创建表。要先进到相应的数据库中

mysql> select * from books;

D：导入表结构和数据

mysql> create database book;

mysql -uroot -p123456 book<booktable.sql

mysql -uroot -p123456 book<bookdata.sql

2. xtrabackup

针对InnoDB存储引擎，MySQL本身没有提供合适的热备工具

Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：

Xtrabackup只能备份InnoDB和XtraDB两种引擎表，而不能备份MyISAM数据表。

innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能

xtrbackup 安装 （mysql5.7.20需安装最新版XtraBackup2.4.9）

下载安装包

  [root@xuegod70 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar

解压包

[root@146 ~]# tar -xvf Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar 

Yum安装并解决依赖：

yum -y install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

注意：安装完成以后记得更改你的 /etc/my.cnf  配置文件制定数据目录，因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件，比如在/etc/my.cnf的[mysqld] 下添加datadir=/var/lib/mysql，然后重启mysql

xtrbackup使用

我们一般使用innobackupex脚本

innobackupex是perl脚本对xtrabackup的封装，和功能扩展。

备份准备工作：

权限和链接

xtrabackup需要连接到数据库和datadir操作权限。

xtrabackup或者innobackupex在使用过程中涉及到2类用户权限：

1.系统用户，用来调用innobackupex或者xtrabackup

连接到服务：innobackupex或者xtrabackup通过—user和—password连接到数据库服务

$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/

$ innobackupex --user=LUKE  --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -   压缩

$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

三、 其他的一些备份方式

表空间传输

5.6+版本的表空间传输,可以把一张表从一个数据库移动到另一个数据库或者另一台机器上.这种方式一般用来做大

表的迁移.

利用binlog2sql进行闪回

windos下数据库备份

（1）新建一个文件备份的bat文件 mysqlback.bat  

精简版配置

set dateTime=%date:~0,4%%date:~5,2%%date:~8,2%
mysqldump  -uroot -proot   securityd > E:\mysqlback\securityd%dateTime%.sql

（2.）测试 直接用下面的文件 改用户名密码 路径  最好先配置mysql的环境变量

（遇到问题： 备份的文件试空的 0kb 解决：他是找不到mysqldump ，配置mysql的环境变量就可以了）

先直接运行看能不备份成功之后再做定个定时任务就可以了

（3）做个任务计划

详细的配置

rem ========================MySQL backup start========================
@echo off
 
::设置变量
::拼接时间参数
set dateTime=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%
::早上10点以前时间变量中会出现空格,把时间变量中的空格替换为0
set "dateTime=%dateTime: =0%"
::本机安装的mysql的bin目录路径
set mysqlBinPath=C:\Program Files\MySQL\MySQL Server 5.7\bin\
::备份文件存储的目标路径
set backupPath=F:\MySQL_bakup
::备份文件的名字前半部分-后半部分使用的是上面的时间参数
set backupFileName=MyTestDB_
::备份文件的保存天数
set days=7
::要备份的数据库服务器的ip
set host=172.168.10.11
::要备份的数据库服务器的端口
set port=3306
::要备份的数据库登录名
set user=root
::要备份的数据库登录密码
set password=123456
::要备份的数据库名
set dbName=MyTestDB
::默认的字符集
set charSet=utf8
 
 
::根据上面设置的days参数删除以前的备份数据
forfiles /p "%backupPath%" /m %backupFileName%*.sql /d -%days% /c "cmd /c del @path"
 
::进入mysql安装目录的bin目录下
cd %mysqlBinPath%
::执行备份操作
mysqldump --opt --single-transaction=TRUE --user=%user% --password=%password% --host=%host% --protocol=tcp --port=%port% --default-character-set=%charSet% --routines --events %dbName% > %backupPath%\%backupFileName%%dateTime%.sql
 
@echo on
rem ========================MySQL backup end========================