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 restart
Welcome 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 机制也是有问题的，除了真正的大量用户使用下，搬迁数据的时候需要打开日志，其他的时候折腾这个划不来。
暂无