LMLPHP后院

MySQL8 关闭 binlog技术

maybe yes 发表于 2020-09-03 11:18

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

2020-10-22 19:29:09 1603366148 0.034002