mysql数据备份与恢复

1，通过数据文件备份与恢复

      1，文件备份数据（这个是无法备份表结构的，只能备份数据）

        复制C:\ProgramData\MySQL\MySQL Server 8.0\Data\demo02  目录中的数据库名称的文件夹

 文件夹就是数据库的名称，  文件就是表名字。 这个整个文件夹都给复制出来。

注意：如果是数据迁移的话，需要把 通过 mysqldump -u root -proot -P3306 demo02 -t>d:demo02.sql  备份表的结构。

2，文件恢复数据

    1,如果库和表都有的情况下，直接替换 整个目录替换，数据就恢复了。

 

2，如果数据库和表都被删除了，需要手动建库和库中的表需要和要恢复的 表名字以及编码，存储引擎都要一样，否则恢复可能会mysql无法启动。

恢复步骤：

1.在新库里面创建结构相同的数据库和表（这个可以根据服务器备份数据或本地数据库来弄）

2.使用  ALTER TABLE test DISCARD TABLESPACE; 丢弃表空间（test=表名，需要对每张表执行一次该命令，可以先在本地把每条命令编辑好 一次执行）

　　PS:执行完后发现新库数据文件夹里 tsst.idb 已被删除

3.把老数据文件夹下的 test.idb 文件复制到新数据文件夹下（test=表名，需要对每张表执行一次该操作）

4.再执行 ALTER TABLE test IMPORT TABLESPACE;  导入表空间（test=表名，需要对每张表执行一次该命令）

这下数据就恢复新的库里面了

学习来自这篇文章，，

学习来自这篇文章，mysql通过数据文件恢复数据方法 - yzeng - 博客园

2 通过mysqldump来导出文件进行备份与恢复

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========================

原文链接：https://blog.csdn.net/z13615480737/article/details/83000563