mysql8 binlog,MySQL8 关闭 binlog

MySQL8 除了默认密码不好用之外，还默认打开了 binlog，简直完全不符合正常人思维，算是越来越不好用了。root@localhost:/var/lib# du -sh ./*

32K./apache2

113M./apt

8.0K./aspell

4.0K./dbus

4.0K./dhcp

24K./dictionaries-common

19M./dpkg

24K./emacsen-common

4.0K./git

12K./grub

8.0K./initramfs-tools

3.2M./ispell

8.0K./logrotate

4.0K./man-db

91M./mecab

4.0K./misc

9.7G./mysql

4.0K./mysql-files

4.0K./mysql-keyring

1.5M./nginx

4.0K./os-prober

28K./pam

44K./php

16K./polkit-1

4.0K./private

4.0K./python

8.0K./sudo

288K./systemd

240K./ucf

8.0K./vim

root@localhost:/var/lib/mysql# du -sh ./*

4.0K./auto.cnf

4.0K./binlog.000001

4.0K./binlog.000002

1.1G./binlog.000003

1.1G./binlog.000004

157M./binlog.000005

1.1G./binlog.000006

1.1G./binlog.000007

1.1G./binlog.000008

1.1G./binlog.000009

679M./binlog.000010

4.0K./binlog.index

4.0K./ca-key.pem

4.0K./ca.pem

4.0K./client-cert.pem

4.0K./client-key.pem

2.7G./lmlphp

192K./#ib_16384_0.dblwr

8.2M./#ib_16384_1.dblwr

20K./ib_buffer_pool

12M./ibdata1

48M./ib_logfile0

48M./ib_logfile1

12M./ibtmp1

164K./#innodb_temp

36K./mysql

25M./mysql.ibd

1.5M./performance_schema

4.0K./private_key.pem

4.0K./public_key.pem

4.0K./server-cert.pem

4.0K./server-key.pem

84K./sys

12M./undo_001

12M./undo_002

view log：mysql> show global variables like 'log_bin%';

+---------------------------------+-----------------------------+

| Variable_name | Value |

+---------------------------------+-----------------------------+

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog |

| log_bin_index | /var/lib/mysql/binlog.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

+---------------------------------+-----------------------------+

5 rows in set (0.11 sec)

mysql> show global variables like '%logs%';

+----------------------------+---------+

| Variable_name | Value |

+----------------------------+---------+

| binlog_expire_logs_seconds | 2592000 |

| expire_logs_days | 0 |

| innodb_print_ddl_logs | OFF |

+----------------------------+---------+

3 rows in set (0.01 sec)

mysql> show global variables like '%binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show global variables like '%bin%';

+------------------------------------------------+-----------------------------+

| Variable_name | Value |

+------------------------------------------------+-----------------------------+

| bind_address | * |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_encryption | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_expire_logs_seconds | 2592000 |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_rotate_encryption_master_key_at_startup | OFF |

| binlog_row_event_max_size | 8192 |

| binlog_row_image | FULL |

| binlog_row_metadata | MINIMAL |

| binlog_row_value_options | |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_compression | OFF |

| binlog_transaction_compression_level_zstd | 3 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

| innodb_api_enable_binlog | OFF |

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binlog |

| log_bin_index | /var/lib/mysql/binlog.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_statements_unsafe_for_binlog | ON |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| mysqlx_bind_address | * |

| sync_binlog | 1 |

+------------------------------------------------+-----------------------------+

36 rows in set (0.00 sec)

mysql>

add skip-log-bin, then restartWelcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'log_bin%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

+---------------------------------+-------+

5 rows in set (0.01 sec)

mysql> show global variables like '%logs%';

+----------------------------+---------+

| Variable_name | Value |

+----------------------------+---------+

| binlog_expire_logs_seconds | 2592000 |

| expire_logs_days | 0 |

| innodb_print_ddl_logs | OFF |

+----------------------------+---------+

3 rows in set (0.00 sec)

mysql> show global variables like '%binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show global variables like '%bin%';

+------------------------------------------------+----------------------+

| Variable_name | Value |

+------------------------------------------------+----------------------+

| bind_address | * |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_encryption | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_expire_logs_seconds | 2592000 |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_rotate_encryption_master_key_at_startup | OFF |

| binlog_row_event_max_size | 8192 |

| binlog_row_image | FULL |

| binlog_row_metadata | MINIMAL |

| binlog_row_value_options | |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlog_transaction_compression | OFF |

| binlog_transaction_compression_level_zstd | 3 |

| binlog_transaction_dependency_history_size | 25000 |

| binlog_transaction_dependency_tracking | COMMIT_ORDER |

| innodb_api_enable_binlog | OFF |

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_statements_unsafe_for_binlog | ON |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| mysqlx_bind_address | * |

| sync_binlog | 1 |

+------------------------------------------------+----------------------+

36 rows in set (0.00 sec)

mysql>

binlog 是必须要关掉的，不到几天就 10 几 G 的量，也没做什么修改的操作，这 log 机制也是有问题的，除了真正的大量用户使用下，搬迁数据的时候需要打开日志，其他的时候折腾这个划不来。