binlog
MariaDB [test]> show variables like "%bin%";
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events | OFF |
| binlog_cache_size | 32768 |
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_optimize_thread_scheduling | ON |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
15 rows in set (0.00 sec)
binlog的删除
binlog的删除可以手工删除或自动删除
自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days';
mysql> set global expire_logs_days=3;
手工删除binlog
mysql> reset master; //删除master的binlog
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002'; //删除指定日志文件的日志索引中binlog日志文件
或者直接用操作系统命令直接删除
mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
mysql> show master logs; //查看master的binlog日志
mysql> show binary logs; //查看master的binlog日志
mysql> show master status; //用于提供master二进制日志文件的状态信息
mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中
binglog的查看
通过mysqlbinlog命令可以查看binlog的内容
[root@localhost ~]# mysqlbinlog /var/lib/mysql/binlog.000003 | more
//查看指定binlog的最后位置
mysql>show binlog events in 'binlog.000166';
复制实现级别 Statement Level、Row Level、Mixed Level
//Statement Level (精简模式(快))
这种模式的优点是Master端不需要记录每一行数据的变化，二进制日志文件量小，IO成本低，速度快。
//Row Level (详尽模式(慢))
修改内容会非常详细,质量的成倍增加。例如：执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。这样就大增加了复制过程的IO成本，导致速度下降、性能下降。
//Mixed Level (混合模式)
该模式结合了之前两种模式的优点，规避了二者的缺点。在该模式下，MySQL会根据执行的每一条语句来区分记录日志文件的格式。举例说明，当涉及到复杂的存储过程时，采用Row Level，规避Statement Level存在的某些场景无法复制的问题；当涉及到Alter table等操作时，采用Statement Level来规避Row Level带来的日志量巨大的问题。
//查看binlog的复制模式
show global variables like '%binlog_format%';
//修改:
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志,将mysql二进制日志取名为mysql-bin
binlog_format=mixed //二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed
server-id=101 //[必须]服务器唯一ID，默认是1，一般取IP最后一段记一次-表结构损坏出现不停重启的现像
当mysqld服务进程访问损坏的表时，数据库会直接崩溃退出。通过:show global status like ‘uptime’;来查看数据库是否有重启;
badblocks -s -v /dev/sdb1 ssd硬盘不存在坏道一说,不需要进行这种扫盘//查看数据库重启日志:
#vi /var/log/mysql/mysqld.log
2023-10-13T01:58:50.172656Z 8 [Warning] [MY-012637] [InnoDB] 16384 bytes should have been read. Only 12288 bytes read. Retrying for the remaining bytes.
2023-10-13T01:58:50.184442Z 8 [Warning] [MY-012638] [InnoDB] Retry attempts for reading partial data failed.
2023-10-13T01:58:50.184461Z 8 [ERROR] [MY-012642] [InnoDB] Tried to read 16384 bytes at offset 1619951616, but was only able to read 12288
2023-10-13T01:58:50.184469Z 8 [ERROR] [MY-012592] [InnoDB] Operating system error number 5 in a file operation.
2023-10-13T01:58:50.184475Z 8 [ERROR] [MY-012596] [InnoDB] Error number 5 means 'Input/output error'
2023-10-13T01:58:50.185094Z 8 [ERROR] [MY-012646] [InnoDB] File ./db_article_novel/tbl_novel_version_part.ibd: 'read' returned OS error 105. Cannot continue operation
2023-10-13T01:58:50.185108Z 8 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-10-13T01:58:50.632909Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 10010)
2023-10-13T01:58:50.632912Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 3995 (requested 4000)
2023-10-13T01:58:50.792978Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2023-10-13T01:58:50.793000Z 0 [Warning] [MY-011068] [Server] The syntax 'slave_net_timeout' is deprecated and will be removed in a future release. Please use replica_net_timeout instead.
2023-10-13T01:58:50.793010Z 0 [Warning] [MY-011068] [Server] The syntax 'slave_skip_errors' is deprecated and will be removed in a future release. Please use replica_skip_errors instead.
2023-10-13T01:58:50.793033Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-10-13T01:58:50.793895Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.30) starting as process 230397
2023-10-13T01:58:50.797011Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-13T01:58:54.466102Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-13T01：58：50.172656Z 8 [警告] [MY-012637] [InnoDB] 应该已读取 16384 字节。仅读取 12288 字节。重试剩余字节。
2023-10-13T01：58：50.184442Z 8 [警告] [MY-012638] [InnoDB] 重试读取部分数据失败。
2023-10-13T01：58：50.184461Z 8 [错误] [MY-012642] [InnoDB] 尝试在偏移量1619951616读取 16384 字节，但只能读取 12288
2023-10-13T01：58：50.184469Z 8 [错误] [MY-012592] [InnoDB] 文件操作中的操作系统错误号 5。
2023-10-13T01：58：50.184475Z 8 [错误] [MY-012596] [InnoDB] 错误号 5 表示“输入/输出错误”
2023-10-13T01：58：50.185094Z 8 [错误] [MY-012646] [InnoDB] 文件 ./db_article_novel/tbl_novel_version_part.ibd：“读取”返回操作系统错误 105。无法继续操作
2023-10-13T01：58：50.185108Z 8 [错误] [MY-012981] [InnoDB] 无法继续操作。
2023-10-13T01：58：50.632909Z 0 [警告] [MY-010139] [服务器] 更改的限制： max_open_files：10000（请求 10010）
2023-10-13T01：58：50.632912Z 0 [警告] [MY-010142] [服务器] 更改的限制： table_open_cache：3995（请求 4000）
2023-10-13T01：58：50.792978Z 0 [警告] [MY-011068] [服务器] 语法“过期日志天数”已弃用，并将在将来的版本中删除。请改用binlog_expire_logs_seconds。
2023-10-13T01：58：50.793000Z 0 [警告] [MY-011068] [服务器] 语法“slave_net_timeout”已弃用，将在将来的版本中删除。请改用replica_net_timeout。
2023-10-13T01：58：50.793010Z 0 [警告] [MY-011068] [服务器] 语法“slave_skip_errors”已弃用，将在将来的版本中删除。请改用replica_skip_errors。
2023-10-13T01：58：50.793033Z 0 [警告] [MY-010915] [服务器] “NO_ZERO_DATE”、“NO_ZERO_IN_DATE”和“ERROR_FOR_DIVISION_BY_ZERO”SQL 模式应与严格模式一起使用。它们将在将来的版本中与严格模式合并。
2023-10-13T01：58：50.793895Z 0 [系统] [MY-010116] [服务器] /usr/libexec/mysqld （mysqld 8.0.30） 作为进程230397启动
2023-10-13T01：58：50.797011Z 1 [系统] [MY-013576] [InnoDB] InnoDB 初始化已开始。
2023-10-13T01：58：54.466102Z 1 [系统] [MY-013577] [InnoDB] InnoDB 初始化已结束。