1. Toy模板网首页
  2. > Toy博客

MySQL数据库的备份、恢复、导出、导入（bin log和mydump）

9月前 作者：李吱恩 分类：Toy博客 阅读(64) 违法举报

这篇具有很好参考价值的文章主要介绍了MySQL数据库的备份、恢复、导出、导入（bin log和mydump）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

目录

一、使用 bin log 来恢复数据

一、bin log的三种格式

1、statement：基于SQL语句的复制（statement-based replication，SBR）

2、row：基于行的复制（row-based replication，RBR）

3、mixed：混合模式复制（mixed-based replication，MBR）

4、查看模式和更改模式

二、配置bin log策略

三、获取bin log文件列表

四、生成新的bin log文件

五、查看日志中的内容

1、在mysql中使用show binlog events查看

2、在shell中使用mysqlbinlog来查看

六、利用bin log 来恢复数据

1、通过pos来恢复

2、通过时间来恢复

二、逻辑备份和恢复

一、mysqldump工具实现逻辑备份

二、逻辑恢复

三、物理备份和恢复

一、物理备份

二、物理恢复

四、数据库的导出和导入

一、导出

1、通过INTO OUTFILE导出

2、使用mysqldump导出

3、使用mysql命令导出

二、导入

 五、数据库误删除恢复步骤

一、使用 bin log 来恢复数据

一、bin log的三种格式

1、statement：基于SQL语句的复制（statement-based replication，SBR）

  • 每一条会修改数据的sql都会记录在binlog中。
  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。但是注意statement相比于row能节约多少性能与日志量，取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。
  • 缺点：由于记录的只是执行语句，为了这些语句在slave上正确运行，我们还必须记录每条语句在执行时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时相同的结果。另外，一些特定的函数功能如果要在slave和master上保持一致会有很多相关问题。

2、row：基于行的复制（row-based replication，RBR）

  • 5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
  • 优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志会非常清楚的记下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
  • 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。但是新版本的MySQL对row level模式进行了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3、mixed：混合模式复制（mixed-based replication，MBR）

  • 从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
  • 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如果一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

4、查看模式和更改模式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set binlog_format=mixed;
Query OK, 0 rows affected (0.00 sec)

二、配置bin log策略

在配置文件中添加

[mysqld]

# 指定 binary log 的路径和名称
log-bin="/var/lib/mysql/binlog"

# 存活时间
binlog_expire_logs_seconds=60000

# 单个 binlog 文件的最大大小
max_binlog_size=100M

# binlog的日志策略
binlog_format=mixed;

三、获取bin log文件列表

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| IU077-bin.000038 |       157 | No        |
| IU077-bin.000039 |      1400 | No        |
| IU077-bin.000040 |       157 | No        |
| IU077-bin.000041 |       333 | No        |
| IU077-bin.000042 |       157 | No        |
| IU077-bin.000043 |       157 | No        |
| IU077-bin.000044 |       157 | No        |
| IU077-bin.000045 |       157 | No        |
| IU077-bin.000046 |       157 | No        |
| IU077-bin.000047 |       157 | No        |
| IU077-bin.000048 |       180 | No        |
| IU077-bin.000049 |       180 | No        |
| IU077-bin.000050 |       157 | No        |
| IU077-bin.000051 |       157 | No        |
+------------------+-----------+-----------+

四、生成新的bin log文件

以下三种情况均可生成新的bin log

  1. 每当我们停止或重启服务器时，服务器会把日志文件记入下一个日志文件，MySQL会在重启时生成一个新的日志文件，文件序号递增。
  2. 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）
  3. 手动的flush logs刷新日志，会生成一个新的日志文件;
mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)

五、查看日志中的内容

1、在mysql中使用show binlog events查看

查看具体某个日志中的内容：

mysql> show binlog events in 'IU077-bin.000052';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                  |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| IU077-bin.000052 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.30, Binlog ver: 4                                     |
| IU077-bin.000052 | 126 | Previous_gtids |         1 |         157 |                                                                       |
| IU077-bin.000052 | 157 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| IU077-bin.000052 | 234 | Query          |         1 |         345 | create database db_16 /* xid=20 */                                    |
| IU077-bin.000052 | 345 | Anonymous_Gtid |         1 |         422 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| IU077-bin.000052 | 422 | Query          |         1 |         555 | use `db_16`; create table tb1(id int, lname varchar(20)) /* xid=24 */ |
| IU077-bin.000052 | 555 | Anonymous_Gtid |         1 |         634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| IU077-bin.000052 | 634 | Query          |         1 |         710 | BEGIN                                                                 |
| IU077-bin.000052 | 710 | Table_map      |         1 |         768 | table_id: 92 (db_16.tb1)                                              |
| IU077-bin.000052 | 768 | Write_rows     |         1 |         821 | table_id: 92 flags: STMT_END_F                                        |
| IU077-bin.000052 | 821 | Xid            |         1 |         852 | COMMIT /* xid=26 */                                                   |
| IU077-bin.000052 | 852 | Rotate         |         1 |         899 | IU077-bin.000053;pos=4                                                |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
12 rows in set (0.00 sec)

指定从某个pos开始查看

mysql> show binlog events in 'IU077-bin.000052' from 710;
+------------------+-----+------------+-----------+-------------+--------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                           |
+------------------+-----+------------+-----------+-------------+--------------------------------+
| IU077-bin.000052 | 710 | Table_map  |         1 |         768 | table_id: 92 (db_16.tb1)       |
| IU077-bin.000052 | 768 | Write_rows |         1 |         821 | table_id: 92 flags: STMT_END_F |
| IU077-bin.000052 | 821 | Xid        |         1 |         852 | COMMIT /* xid=26 */            |
| IU077-bin.000052 | 852 | Rotate     |         1 |         899 | IU077-bin.000053;pos=4         |
+------------------+-----+------------+-----------+-------------+--------------------------------+
4 rows in set (0.00 sec)

查看当前正在写入的日志状态：

mysql> show master status\G
*************************** 1. row ***************************
             File: IU077-bin.000053
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2、在shell中使用mysqlbinlog来查看

mysqlbinlog -v 日志文件的路径

例如：
mysqlbinlog -v /usr/local/mysql/data/binlog.000010

六、利用bin log 来恢复数据

注意：

        bin log不是通过回滚来恢复数据的，而是重新执行SQL语句来恢复的。

1、通过pos来恢复

方式一：在shell中执行mysql登录和切换

mysqlbinlog --start-position=100 --stop-position=300 --database=数据库名 binlog文件 | mysql -uroot -p密码 -v 数据库名

方式二：生成.sql后缀的脚本，然后在mysql中执行该脚本

# 第一步：先生成sql脚本
mysqlbinlog --start-position=100 --stop-position=300 --database=数据库名 binlog文件 > resume.sql

# 第二步：在sql中执行该脚本
mysql> source 脚本路径/脚本名称

2、通过时间来恢复

把position换成datetime或timestramp

二、逻辑备份和恢复

一、mysqldump工具实现逻辑备份

基础操作：

# 备份单个数据库
mysqldump -uroot -p密码 database_1 > database_1.sql

# 备份全部数据库
mysqldump -uroot -p密码 --all-databases
# 或者
mysqldump -uroot -p密码 --A

更细致的操作：

参数 作用
--databases 或 -B 备份部分数据库
数据库名称后面跟表名 备份部分表
--where="id < 10" 备份表中的部分数据
--ignore-table=数据库.表名 不备份这个表
--no-create-info 不备份结构，仅备份数据
--no_data 不备份数据

二、逻辑恢复

1、在shell中恢复

mysql -uroot -p密码 数据库名 < .sql脚本

2、在mysql中恢复

mysql> source 脚本路径/脚本名称.sql

三、物理备份和恢复

一、物理备份

1、先给数据库上锁，防止备份过程中数据库被修改

mysql> flush tables with read lock;

2、物理拷贝备份

cp -r database_1 /backup/database_1_bak

3、数据库解锁（一定不能忘记解锁）

mysql> unlock tables;

二、物理恢复

1、物理移动

cp 数据库 路径/名称

2、重启mysql服务

systemctl restart mysql

3、给mysql用户赋予权限

chown -R mysql.mysql /var/lib/mysql/数据库名

四、数据库的导出和导入

一、导出

1、通过INTO OUTFILE导出

1、先查看数据库是否可以导出

mysql> show variables like '%secure%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| require_secure_transport | OFF                                            |
| secure_file_priv         | D:\Program Files (x86)\MySQL\Data 8.0\Uploads\ |
+--------------------------+------------------------------------------------+

2、开始导出

mysql> select * from tb1 INTO OUTFILE "D:\Program Files (x86)\MySQL\Data 8.0\Uploads\tb1.txt";

2、使用mysqldump导出

同时生成txt文件和sql脚本

mysqldump -uroot -p密码 -T "路径" 数据库名 表名;

3、使用mysql命令导出

把查询内容导出内容到txt文件

mysql -uroot -p密码 --execute="select * from tb1;" 数据库名 > 名字.txt;

二、导入

1、LOAD DATA INFILE 导入

mysql> LOAD DATA INFILE 'txt文本文件' INTO TABLE dbname.tbname;

 五、数据库误删除恢复步骤

  1. 取最近一次的全量备份。
  2. 用全量备份恢复出一个临时库。
  3. 取出全量备份之后的bin log日志。
  4. 剔除日志中的误操作SQL，把其他语句都应用到临时库。
  5. 回复完成之后，把临时库恢复到主库。

 文章来源地址https://www.toymoban.com/news/detail-831369.html

到了这里，关于MySQL数据库的备份、恢复、导出、导入（bin log和mydump）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • 如何备份与恢复MySQL数据库数据

    如何备份与恢复MySQL数据库数据

    目录 一、MySQL备份 备份方式 完全备份 差异备份 增量备份 二、常见的备份方法 物理冷备 专用备份工具 mysqldump 或 mysqlhotcopy 启用二进制日志进行增量备份 第三方工具备份 三、MySQL完全备份 四、数据库完全备份分类 物理冷备份与恢复 mysqldump备份与恢复 五、物理冷备份与恢复

    2024年02月16日
    浏览(73)

  • 数据库（MySQL的备份和恢复）

    目录 1.1 MySQL 日志管理 1.1.1 MySQL日志类型 1.1.2 错误日志 错误日志中主要记录的几种日志 错误日志的定义 1.1.3 通用查询日志 1.1.4 慢查询日志 和慢查询相关的变量设置 1.1.5 二进制日志 二进制日志是记录执行的语句还是执行后的数据 日志滚动  1.2 MySQL备份 1.2.1 备份类型 1.2.2

    2024年01月25日
    浏览(64)

  • Mysql数据库增量备份与恢复

    使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间长。 而增量备份就是备份自上一次备份之后增加或改变的文件或内容。 1、增量备份的特点: 没有重复数据，备份量不大，时间短 恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复

    2024年02月07日
    浏览(90)
  • MySQL数据库的备份与恢复

    MySQL数据库的备份与恢复

    备份的主要目的是灾难恢复。 在生产环境中，数据的安全性至关重要。 任何数据的丢失都可能产生严重的后果。 造成数据丢失的原因： 程序错误 人为操作错误 运算错误 磁盘故障 灾难（如火灾、地震）和盗窃 1）物理备份 物理备份：对数据库操作系统的物理文件（如数据

    2024年02月04日
    浏览(242)
  • 【数据库四】MySQL备份与恢复

    【数据库四】MySQL备份与恢复

    数据库备份 物理备份 :直接对数据库的 数据文件或者日志文件 进行备份. 逻辑备份 :对 数据库的库或表对象 进行备份. 备份策略 完全备份 :每次备份 都备份完整的数据库 . 是对整个数据库、数据库结构和文件结构的备份。 保存的是 备份完成时刻的数据库 。 是 差异备份与增

    2024年02月11日
    浏览(63)
  • 数据库应用：MySQL备份与恢复

    数据库应用：MySQL备份与恢复

    目录 一、理论 1.数据备份 2.完全备份与恢复 3.完全备份与恢复应用 4.增量备份与恢复 5.增量备份与恢复应用 6.使用脚本备份 7.日志管理 二、实验 1.完全备份与恢复 2.增量备份与恢复 3.使用脚本备份 三、问题 1.mysqldump报错 四、总结 （1）重要性 ①  备份的主要目的是灾难恢复

    2024年02月16日
    浏览(71)
  • 9-MySQL数据库 数据的备份与恢复

    9-MySQL数据库 数据的备份与恢复

    1.date文件的备份 2.mysqldump 备份 说明： mysqldump是MySQL数据库中的一个实用程序，它主要用于转储（备份）数据库。mysqldump通过生成一个SQL脚本文件，包含从头开始重新创建数据库所必需的（如 CREATE TABLE和INSERT等），来实现数据库的备份和转储。这样，你可以在任何时候通过运

    2024年02月08日
    浏览(71)
  • MySQL基础（三十八）数据库备份与恢复

    MySQL基础（三十八）数据库备份与恢复

    物理备份 ：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。 逻辑备份 ：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。

    2024年02月06日
    浏览(66)
  • 【Linux】Mysql的数据库备份及恢复

    【Linux】Mysql的数据库备份及恢复

            备份就是为了防止原数据丢失，保证数据的安全。当数据库因为某些原因造成部分或 者全部数据丢失后，备份文件可以帮我们找回丢失的数据。因此，数据备份是很重要 的工作。 常见数据库备份的应用场景如下： 数据丢失应用场景： 人为操作失误造成某些数据

    2023年04月10日
    浏览(53)
  • MySQL-备份+日志：介质故障与数据库恢复

    MySQL-备份+日志：介质故障与数据库恢复

    本关任务: 备份数据库，然后再恢复它。 为了完成本关任务，你需要掌握： 1.MySQL的恢复机制； 2.MySQL提供的备份与恢复工具。 和大多数DBMS一样，MySQL利用备份、日志文件实现恢复。 具体理论知识在此不详细介绍。 MySQL提供了以下工具： 逻辑备份工具：mysqldump 物理备份工具

    2024年02月05日
    浏览(89)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包