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

一、使用 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

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

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

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

申请自己的网站常平网站建设网站开发语言有哪些软件开发公司排行榜 dw制作简单网站网站不备案有什么后果

上一篇：SpringBoot集成 ElasticSearch

下一篇：MySQL笔记——MySQL数据库介绍以及在Linux里面安装MySQL数据库，对MySQL数据库的简单操作，MySQL的外接应用程序使用说明

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

目录

一、使用 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命令导出

二、导入

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