关键词：
二进制日志
format
statement
row
mixed
| sql_log_bin | ON |
#关闭二进制日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
#查看二进制日志内容
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query | 1 | 220 | create database testdb |
| mysql-bin.000004 | 220 | Query | 1 | 311 | drop database hellodb |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
逻辑备份：
1. 浮点数据丢失精度
2. 备份出的数据更占用存储空间；压缩后可大大节省空间
3. 不适合对大数据库做完全备份
mysqldump对InnoDB执行备份时，使用热备
mysql> show engine innodb status \\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-07-02 19:02:40 7f8e7c469700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 386 srv_idle
srv_master_thread log flush and writes: 387
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 3077
Purge done for trx's n:o < 2215 undo n:o < 0 state: running but idle
History list length 101
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7f8e7c469700, query id 5 localhost root init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
492 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2008688
Log flushed up to 2008688
Pages flushed up to 2008688
Last checkpoint at 2008688
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 59602
Buffer pool size 8191
Free buffers 7866
Database pages 325
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0 single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 325, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 325, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 1869, id 140249940915968, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
ERROR:
No query specified
#通过select备份数据---【一般用来做单张表的备份】
mysql> select * into outfile '/tmp/tutors.txt' from tutors;
Query OK, 14 rows affected (0.02 sec)
#将select备份的数据导入
#创建表结构
mysql> create table tutor like tutors;
Query OK, 0 rows affected (0.00 sec)
mysql> desc tutor;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| TID | tinyint(4) | NO | PRI | NULL | auto_increment |
| Tname | varchar(30) | YES | | NULL | |
| Gender | enum('M','F') | YES | | NULL | |
| Age | tinyint(4) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> drop table tutors;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from tutor;
Empty set (0.00 sec)
mysql> load data infile '/tmp/tutors.txt' into table tutor;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from tutor;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 2 | HuangYaoshi | M | 63 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | M | 49 |
| 10 | Tom | F | 30 |
| 11 | DingDian | M | 25 |
| 12 | HuFei | M | 31 |
| 13 | Xuzhu | M | 26 |
| 14 | LingHuchong | M | 70 |
| 15 | tye | M | 34 |
| 16 | L1 | F | 12 |
| 17 | L2 | M | 34 |
| 18 | L3 | F | 87 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
几乎热备：LVM
snapshot
前提：
1、数据文件要在逻辑卷上
2、此逻辑卷所在卷组必须有足够空间使用快照卷
3、事务日志和数据文件需放在同一个逻辑卷上
步骤：
1.打开会话，施加读锁，锁定所有表
mysql>flush tables with read lock;
mysql>flush logs;
2.通过另一个终端，保存二进制日志文件及相关位置信息
$ mysql -uroot -p -e ‘show master status \\G’ > /path/to/master.info
3.创建快照卷
lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv
4.释放锁
mysql>unlock tables;
5.挂载快照卷，备份
mount
cp
6.删除快照卷
7.增量备份二进制日志
二进制日志相关的几几个选项
innodb_support_xa=true|false
sync_binlog=#
mysql> show global variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'innodb_support%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_support_xa | ON |
+-------------------+-------+
1 row in set (0.00 sec)
percona:
ibbackup:InnoDB onlin physical backup
full
incremental
MyISAM：warm backup,full
xtrabackup:
xtradb:innodb的增强版
innodb:
安装xtrabackup
#通过rpm包安装xtrabackup时会出现以下报错
[root@mail ~]# rpm -ivh percona-xtrabackup-80-8.0.25-17.1.el6.x86_64.rpm
warning: percona-xtrabackup-80-8.0.25-17.1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.25-17.1.el6.x86_64
perl(DBD::mysql) is needed by percona-xtrabackup-80-8.0.25-17.1.el6.x86_64
#解决方法
[root@mail ~]# wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
--2021-07-03 05:15:57-- ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
=> “libev-4.04-2.el6.x86_64.rpm”
Resolving rpmfind.net... 195.220.108.108
Connecting to rpmfind.net|195.220.108.108|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD (1) /linux/atrpms/el6-x86_64/atrpms/stable ... done.
==> SIZE libev-4.04-2.el6.x86_64.rpm ... 38140
==> PASV ... done. ==> RETR libev-4.04-2.el6.x86_64.rpm ... done.
Length: 38140 (37K) (unauthoritative)
100%[====================================================================================================================>] 38,140 116K/s in 0.3s
2021-07-03 05:16:02 (116 KB/s) - “libev-4.04-2.el6.x86_64.rpm” saved [38140]
[root@mail ~]# rpm -ivh lib
libev-4.04-2.el6.x86_64.rpm libffi-devel-3.0.13-19.el7.x86_64.rpm
[root@mail ~]# rpm -ivh libev-4.04-2.el6.x86_64.rpm
warning: libev-4.04-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 66534c2b: NOKEY
Preparing... ########################################### [100%]
1:libev ########################################### [100%]
[root@mail ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#再执行安装操作，安装成功
备份数据
[root@mail ~]# innobackupex --user=root --password=password --socket=/tmp/mysql.sock --port=3306 /backup
#查看备份文件信息
[root@mail 2021-07-03_05-37-41]# file xtrabackup_binlog_info
xtrabackup_binlog_info: ASCII text
#xtrabackup_binlog_info---mysql服务器当前正在使用的二进制日志文件及备份这一刻为止的二进制日志事件的位置
[root@mail 2021-07-03_05-37-41]# cat xtrabackup_binlog_info
mysql-bin.000007 120
[root@mail 2021-07-03_05-37-41]# file xtrabackup_info
xtrabackup_info: ASCII text
[root@mail 2021-07-03_05-37-41]# cat xtrabackup_info
uuid = bcc4e486-db7d-11eb-a2fe-000c29781840
name =
tool_name = innobackupex
tool_command = --user=root --password=... --socket=/tmp/mysql.sock --port=3306 /backup
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.6.10-log
start_time = 2021-07-03 05:37:42
end_time = 2021-07-03 05:37:44
lock_time = 1
binlog_pos = filename 'mysql-bin.000007', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 2029003
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@mail 2021-07-03_05-37-41]# file xtrabackup_checkpoints
xtrabackup_checkpoints: ASCII text
#xtrabackup_checkpoints---备份类型(完全或增量)、备份状态和LSN(日志序列号)范围信息
[root@mail 2021-07-03_05-37-41]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2029003
last_lsn = 2029003
compact = 0
recover_binlog_info = 0
flushed_lsn = 2029003
#备份mysql的配置文件
[root@mail 2021-07-03_05-37-41]# vim backup-my.cnf
xtrabackup还原前的准备工作：
一般情况下，备份完成后，数据尚不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交的尚未同步至数据文件中的事务，因此此时数据文件仍处于不一致状态，“准备”则通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致状态。
innobackupex --apply-log /backup/目录
[root@mail backup]# innobackupex --apply-log /backup/2021-07-03_05-37-41/
linux12-mysql数据库--＞12日志管理(代码片段)
...g_error_verbosity参数二、一般查询日志1、一般查询日志三、二进制日志1、二进制日志简介2、不要混淆以下三种日志：3、开启binlog日志的好处4、二进制日志工作模式1、查 查看详情
mysql-日志(代码片段)
...线图】获取学习路线图。文章目录前言一、日志简介二、二进制日志1、启动和设置二进制日志2、查看二进制日志3、删除二进制日志4、使用二进制日志还原数据库5、 查看详情
mysql-日志(代码片段)
...线图】获取学习路线图。文章目录前言一、日志简介二、二进制日志1、启动和设置二进制日志2、查看二进制日志3、删除二进制日志4、使用二进制日志还原数据库5、 查看详情
mysql-日志(代码片段)
...线图】获取学习路线图。文章目录前言一、日志简介二、二进制日志1、启动和设置二进制日志2、查看二进制日志3、删除二进制日志4、使用二进制日志还原数据库5、 查看详情
mysql日志分类：错误日志二进制日志查询日志慢查询日志(代码片段)
MySQL性能强劲，是目前使用最广泛的数据库之一，以 MySQL为学习原型也方便之后掌握其他数据库，下面就给大家全面讲解下MySQL8.0的新特性，从零基础到高阶一站式学习，结合实际案例让大家有所收获！▼M... 查看详情
（万字超详细总结纯手打）mysql深度学习分析(代码片段)
...#xff1a;选取-投影-联接策略日志文件错误日志通用查询日志二进制日志（binlog）——记录对数据的修改操作慢查询日志——记录所有超时的执行sql，主要是select 查看详情
mysql高级学习笔记(代码片段)
... [2]、通用查询日志（Generalquerylog）。 [3]、二进制日志（bi 查看详情
mysql日志管理(代码片段)
...查询的信息慢查询日志记录执行时间超过指定时间的操作二进制日志又称binlog日志，以二进制文件的方式记录数据库中除select以外的操作中继日志备库将主库的二进制日志复制到自己的中继日志中，从而在本地中进行重... 查看详情
mysql日志(代码片段)
...xff0c;建议首先查看此日志。showvariableslike"log_error";2.二进制日志二进制日志（BINLOG）记 查看详情
mysql运维日志--错误日志二进制日志查询日志慢查询日志(代码片段)
文章目录1.错误日志2.二进制日志2.1介绍2.2格式2.3查看2.4删除3.查询日志4.慢查询日志（可以记录用时较长的SQL）4.1开启慢查询日志4.2慢查询日志测试4.3补充说明1.错误日志错误日志是MySQL中最重要的日志之一，它记录了... 查看详情
mysql二进制日志详解(代码片段)
一、什么是二进制日志二进制日志主要记录mysql数据库的变化，二进制日志包含所有更新了数据或者潜在更新了数据（如没有匹配到任何行的delete语句），语句以时间的形式保存，描述了数据的更改。二进制日志还包含执行每个... 查看详情
mysql高级篇——日志(代码片段)
...志文件，用来存储不同类型和功能的日志，分为二进制日志、错误日志、通用查询日志、慢查询日志。Mysql8.0又新增两种日志：中继日志和数据定义语句日志。2.弊端日志会降低mysql数据库的性能，需要花费额外时... 查看详情
误删mysql数据后，如何通过mysql二进制日志恢复数据(代码片段)
问题出现！！！！！由于程序内bug，导致关联子表数据被无辜删除了，数据库又没有备份，只能通过找到mysql日志来恢复了mysql日志一般存放在server/data/目录下的文件名为mysql-bin.*的文件内，如果... 查看详情
误删mysql数据后，如何通过mysql二进制日志恢复数据(代码片段)
问题出现！！！！！由于程序内bug，导致关联子表数据被无辜删除了，数据库又没有备份，只能通过找到mysql日志来恢复了mysql日志一般存放在server/data/目录下的文件名为mysql-bin.*的文件内，如果... 查看详情
mysqlbinlog日志操作详解(代码片段)
MySQLbinlog日志操作详解MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog日志的作用其... 查看详情
mysql-四大类日志(代码片段)
...志分为4大类🍁错误日志🍃修改系统配置🍁二进制日志🍃查看二进制日志🍃删除二进制日志🍃暂时停止二进制日志的功能🍁事务日志(或称redo日志)🍁慢查询日志：slowquerylog 🦐博客主... 查看详情
mysql主从搭建(代码片段)
#1主从同步的流程或原理1）master会将变动记录到二进制日志里面；2）master有一个I/O线程将二进制日志发送到slave;3)slave有一个I/O线程把master发送的二进制写入到relay日志里面；4）slave有一个SQL线程，按照relay日志处理slave的数据；#... 查看详情
mysql中的日志管理日志备份与恢复(代码片段)
...MySQL常用日志类型及开启1、错误日志2、通用查询日志3、二进制日志（binlog)4、慢查询日志 二、查看日志状态1、查看通用查询日志是否开启2、查看二进制日志是否开启3、查看慢查询日功能是否开启查看慢查询时间设置在数... 查看详情