mysql数据库备份心得

叁歲伎倆 2022-06-03 04:00 260阅读 0赞

# mysql数据库备份/恢复等心得-Navicat for mysql #

--------------------

#### **一、数据传输** ####

1.  如下图所示，选择”工具”->”数据传输”。  
    ![这里写图片描述][SouthEast]
2.  “**源**“中选择你要导入的连接来源和并选择相应的带导入的数据库，”**目标**“中选择你需要将数据恢复到的数据库连接和数据库名称。
3.  在“数据传输”的“**高级**”选项中，可以设置是否包含索引，**遇到错误继续**（建议选上）等。  
    ![这里写图片描述][SouthEast 1]
4.  最后点击“开始”进行导入即可。  
    ![这里写图片描述][SouthEast 2]
5.  优势：可以传输大量数据，推荐。

--------------------

#### **二、转存数据库的SQL结构和数据** ####

1.  如下图所示，选择本地数据库如”test”，右键选择“转存SQL文件”。如果再选择“结构和数据”，就保存SQL表结构的同时会保存里面的数据。如果选择“仅结构”，只保存表结构，数据不保存。  
    ![这里写图片描述][SouthEast 3]
2.  之后，以SQL的形式保存到本地（test.sql）。查看该SQL文件，可以看到其实是一堆SQL语句，首先是判断表格如果存在就先DROP TABLE，然后创建表格CREATE TABLE，最后向表格INSERT INTO原有的数据。对每个表格大致都是这样的过程。（疑问：当数据量是100万的时候，这个SQL文件可能很大，执行SQL会出问题）  
    ![这里写图片描述][SouthEast 4]  
    ![这里写图片描述][SouthEast 5]

--------------------

#### **三、备份** ####

1.  如下图所示，选择本地数据库如”test”，选择“备份”->“新建备份”，在“注释”中添加对本次备份的详细说明。然后保存到本地  
    ![这里写图片描述][SouthEast 6]
2.  还原备份：右键选择需要还原的备份文件“2017-12-18 10:42:38”->“还原备份”即可。  
    ![这里写图片描述][SouthEast 7]

--------------------

#### **四、mysqldump定时备份** ####

1.  mysqldump脚本备份：创建脚本“mysql\_backup\_tool.bat”文件，文件内容为：
    
        rem ******MySQL backup start********
        @echo off
        
        forfiles /p "E:\mysql_backup" /m emsc_backup_*.sql -d -30 /c "cmd /c del /f @path"
        
        set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%0%time:~1,1%%time:~3,2%%time:~6,2%"
        C:\xampp\mysql\bin\mysqldump --opt --single-transaction=TRUE --user=root --password=root --host=localhost --protocol=tcp --port=9220 --default-character-set=utf8 --single-transaction=TRUE --routines --events "test" > E:\mysql_backup\yotta_backup_%Ymd%.sql
        
        @echo on
        rem ******MySQL backup end********
2.  其中，forfiles后面是查询本地路径下的sql备份文件，删除30天以上的备份文件。set后面设置的时间信息，然后设置本地mysql环境下mysqldump.exe的路径，设置数据库连接的主机IP(–host)，用户名(–user)，密码(–password)，端口(–port)。设置需要备份的数据库名称“test”和保存到本地的文件信息：E:\\mysql\_backup\\yotta\_backup\_%Ymd%.sql。
3.  运行.bat脚本文件即可在路径下看到备份的文件信息。

--------------------

#### **五、定时备份软件：MySQLBackupFTP** ####

1.  下载最新版的MySQLBackupFTP软件，连接本地数据库，选择需要备份的数据库“test”。
2.  设置保存的路径，支持保存到本地文件夹，ftp服务器等。
3.  设置通知邮箱信息，向该邮箱发送备份的信息。
4.  设置定时备份计划，建议每日凌晨3点备份。
5.  将该计划保存到本地，即可实现定时备份计划。
6.  优势：比脚本方便操作，更加便捷。

![这里写图片描述][SouthEast 8]

--------------------

#### **六、备份mysql/data文件夹下数据库中的文件** ####

1.  以表”Table”为例:  
    如类型是MyISAM, 数据文件则以”Table.frm””Table.MYD””Table.MYI””三个文件存储于”/data/databasename/”目录中.如类型是InnoDB,数据文件则存储在”  d a t a b a s e n a m e / ” 目 录 中 . 如 类 型 是 I n n o D B , 数 据 文 件 则 存 储 在 ” innodb\_data\_home\_dir/″中的ibdata1文件中(一般情况)，结构文件存在于table\_name.frm中.  
    MySQL的数据库文件直接复制便可以使用，但是那是指“MyISAM”类型的表。  
    而使用MySQL-Front直接创建表，默认是“InnoDB”类型，这种类型的一个表在磁盘上只对应一个“*.frm”文件，不像MyISAM那样还“*.MYD,\*.MYI”文件。  
    MyISAM类型的表直接拷到另一个数据库就可以直接使用，但是InnoDB类型的表却不行。解决方法就是:  
    同时拷贝innodb数据库表“\*.frm”文件和innodb数据“ibdata1”文件到合适的位置。启动MySQL的Windows服务  
    由于MySQL这样数据混杂的形式, 往往很容易让使用者在备份时忘记了备份InnoDB, 从而导致了上述错误.
2.  如果原数据库引擎是MyISAM，直接拷贝原有数据库文件”*.frm”、”*.MYD”、”\*.MYI”等文件；如果原数据库引擎是InnoDB，切记还需拷贝ibdata1文件
3.  一般时候建议使用逻辑备份，即之前的sql文件备份。当数据表格数据量较大或者执行sql恢复时候报错时，可以使用物理备份，即将数据库表格文件直接拷贝恢复。之前遇到过一个数据库表格的大小为19个G，执行sql语句恢复的时候由于插入语句报错而丢失很多数据。

--------------------

#### **总结** ####

mysql数据备份是必要的，之前实验室有个组的数据在阿里云服务器上，然后某天忽然服务器被搞了，数据库数据丢失，然后没有备份，雪崩。建议大家养成良好的每日备份数据库的习惯。

[SouthEast]: /images/20220603/8fed65e0f89c4047a7a6bbe4eeb35a13.png
[SouthEast 1]: /images/20220603/135db1cad8d9410ea3529056a0185ead.png
[SouthEast 2]: /images/20220603/7c391a0ce6474aa2a82ceee5dc30ef4c.png
[SouthEast 3]: /images/20220603/c098bcf1ff604322af806c70b3c42246.png
[SouthEast 4]: /images/20220603/2110f991362948778b08f3f98673b647.png
[SouthEast 5]: /images/20220603/09f62a40018b496dad0dc4d8ede0c9f5.png
[SouthEast 6]: /images/20220603/ac21b2a7a6ba4a2f8522ca9a860bae97.png
[SouthEast 7]: /images/20220603/abde129850d84725a06d893648391132.png
[SouthEast 8]: /images/20220603/008f24351db54200a1cdf4688727b400.png