1. 背景
一个好的软件总是让用户的使用体验越来越好，这次 MySQL-8.0.x 的升级有两点改进，都让它更加易用；其一是添加了不可见主键，其二就是 redo 日志大小可以在线调整。
我们这里先说第二点；在 MySQL-8.0.30 之前为了优化写入的性能我们通常会适当的调大 redo 日志的大小。 但是这个要求我们重启 MySQL 才能生效，特别是在生产环境上不是想重启就能重启的。MySQL-8.0.30 给这个划上了句号。
2. 大江东去
时代抛弃了两个参数！之前我们是通过 innodb_log_files_in_group 参数来控制 redo 日志包含多少个文件，innodb_log_file_size 参数用于指定单个文件的大小。也就是说以前 redo 日志的大小是通过这两个参数的乘积来控制的。
代码语言：javascript复制总大小 = innodb_log_files_in_group * innodb_log_file_size现在这两个参数已经合二为一了，新的版本中 innodb_redo_log_capacity 这一个参数就能解决。重要的是这个新参数支持动态调整。
3. 体验 8.0.30 版本
3.1 安装 8.0.30
代码语言：javascript复制dbma-cli-single-instance --port=3308 --max-mem=128
--pkg=mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz install
-- 检查
ps -ef | grep 3308
mysql33 1841862 1 0 Sep16 ? 00:01:45 /usr/local/mysql-8.0.30-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/etc/my-3308.cnf3.2 新版本的变化
新的版本会在 datadir 目录下生成一个 '#innodb_redo' 的目录，里面就保存着若干的 redo 文件。
代码语言：javascript复制[root@git-sqlpy-com #innodb_redo]# ll
total 1032192
-rw-r----- 1 mysql3308 mysql 16777216 Sep 16 23:38 '#ib_redo1'
-rw-r----- 1 mysql3308 mysql 33554432 Sep 16 23:36 '#ib_redo10_tmp'
...
// 总大小 1G
du -sh .
1G .3.3 体验动态调整的功能，在线把 redo 日志调整到 2G
代码语言：javascript复制-- 检查版本
mysql> select @@version;
-----------
| @@version |
-----------
| 8.0.30 |
-----------
1 row in set (0.00 sec)
-- 调整到 2G
mysql> set @@global.innodb_redo_log_capacity = 2147483648;
Query OK, 0 rows affected (0.00 sec)
-- 检查是否成功
mysql> select @@innodb_redo_log_capacity;
----------------------------
| @@innodb_redo_log_capacity |
----------------------------
| 2147483648 |
----------------------------
1 row in set (0.00 sec)
检查文件系统中的 redo 日志是否同步调整到了 2G
代码语言：javascript复制-- 观察日志文件的大小
[root@git-sqlpy-com #innodb_redo]# du -sh .
2.0G
[root@git-sqlpy-com #innodb_redo]# ll
total 2048004
-rw-r----- 1 mysql3308 mysql 16777216 Sep 16 23:38 '#ib_redo1'
-rw-r----- 1 mysql3308 mysql 67108864 Sep 16 23:54 '#ib_redo10_tmp'
-rw-r----- 1 mysql3308 mysql 67108864 Sep 16 23:54 '#ib_redo11_tmp'
好吧！从文件系统上看 redo 日志的大小也确认是由 1G 调整到 2G 了。
4. 8.0.30 完整的配置文件
代码语言：javascript复制[mysqld]
#### for basic
user = mysql3308
basedir = /usr/local/mysql-8.0.30-linux-glibc2.12-x86_64
datadir = /database/mysql/data/3308
server_id = 361
port = 3308
bind_address = *
admin_address = 127.0.0.1
mysqlx_port = 33080
admin_port = 33082
socket = /tmp/mysql-3308.sock
mysqlx_socket = /tmp/mysqlx-33080.sock
pid_file = /tmp/mysql-3308.pid
character_set_server = utf8mb4
open_files_limit = 102000
max_prepared_stmt_count = 1048576
skip_name_resolve = 1
super_read_only = OFF
log_timestamps = system
event_scheduler = OFF
auto_generate_certs = ON
activate_all_roles_on_login = ON
end_markers_in_json = OFF
tmpdir = /tmp/
max_connections = 128
autocommit = ON
sort_buffer_size = 256K
join_buffer_size = 256K
eq_range_index_dive_limit = 200
authentication_policy = '*,*,*'
show_gipk_in_create_table_and_information_schema = ON
#### for table
big_tables = OFF
sql_require_primary_key = OFF
lower_case_table_names = 1
auto_increment_increment = 1
auto_increment_offset = 1
table_open_cache = 4000
table_definition_cache = 2000
table_open_cache_instances = 32
#### for net
max_allowed_packet = 1G
connect_timeout = 10
interactive_timeout = 28800
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
net_buffer_length = 32K
#### for logs
log_output = FILE
general_log = OFF
general_log_file = general.log
## error
log_error = err.log
log_statements_unsafe_for_binlog = ON
## slow
slow_query_log = ON
slow_query_log_file = slow.log
long_query_time = 2
log_queries_not_using_indexes = OFF
log_slow_admin_statements = OFF
log_slow_replica_statements = ON
## binlog
log_bin = /binlog/mysql/binlog/3308/mysql-bin
binlog_checksum = none
log_bin_trust_function_creators = ON
binlog_direct_non_transactional_updates = OFF
binlog_expire_logs_seconds = 604800
binlog_error_action = ABORT_SERVER
binlog_format = ROW
max_binlog_stmt_cache_size = 1G
max_binlog_cache_size = 1G
max_binlog_size = 1G
binlog_order_commits = ON
binlog_row_image = FULL
binlog_row_metadata = MINIMAL
binlog_rows_query_log_events = ON
binlog_stmt_cache_size = 32K
log_replica_updates = ON
binlog_transaction_compression = ON
binlog_transaction_dependency_history_size =25000
binlog_transaction_dependency_tracking = WRITESET
sync_binlog = 1
binlog_cache_size = 96K
binlog_group_commit_sync_delay = 0
binlog_group_commit_sync_no_delay_count = 0
#### for replication
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_no_slave = ON
rpl_semi_sync_master_wait_for_slave_count = 1
sync_source_info = 1000
skip_replica_start = ON
replica_load_tmpdir = /tmp/
plugin_load_add = semisync_master.so
plugin_load_add = semisync_slave.so
relay_log = relay
sync_relay_log = 10000
sync_relay_log_info = 10000
slave_preserve_commit_order = ON
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 2
replica_max_allowed_packet = 1G
#### for gtid
gtid_mode = ON
binlog_gtid_simple_recovery = ON
enforce_gtid_consistency = ON
gtid_executed_compression_period = 1000
#### for clone
plugin-load-add = mysql_clone.so
clone = FORCE_PLUS_PERMANENT
#### for engines
default_storage_engine = innodb
default_tmp_storage_engine = innodb
internal_tmp_mem_storage_engine = TempTable
#### for innodb
innodb_data_home_dir = ./
innodb_data_file_path = ibdata1:64M:autoextend
innodb_page_size = 16K
innodb_default_row_format = dynamic
innodb_log_group_home_dir = ./
innodb_redo_log_encrypt = OFF
innodb_online_alter_log_max_size = 256M
innodb_undo_directory = ./
innodb_undo_log_encrypt = OFF
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G
innodb_rollback_on_timeout = OFF
innodb_rollback_segments = 128
innodb_log_checksums = ON
innodb_checksum_algorithm = crc32
innodb_log_compressed_pages = ON
innodb_doublewrite = ON
innodb_commit_concurrency = 0
innodb_read_only = OFF
innodb_dedicated_server = OFF
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_random_read_ahead = OFF
innodb_read_ahead_threshold = 56
innodb_max_dirty_pages_pct_lwm = 20
innodb_max_dirty_pages_pct = 90
innodb_lru_scan_depth = 1024
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10
innodb_flushing_avg_loops = 30
innodb_buffer_pool_dump_pct = 50
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_filename = ib_buffer_pool
innodb_stats_persistent = ON
innodb_stats_on_metadata = ON
innodb_stats_method = nulls_equal
innodb_stats_auto_recalc = ON
innodb_stats_include_delete_marked = ON
innodb_stats_persistent_sample_pages = 20
innodb_stats_transient_sample_pages = 8
innodb_status_output = OFF
innodb_status_output_locks = OFF
innodb_buffer_pool_dump_now = OFF
innodb_buffer_pool_load_abort = OFF
innodb_buffer_pool_load_now = OFF
innodb_thread_concurrency = 0
innodb_concurrency_tickets = 5000
innodb_thread_sleep_delay = 15000
innodb_adaptive_max_sleep_delay = 150000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_use_native_aio = ON
innodb_flush_sync = OFF
innodb_spin_wait_delay = 6
innodb_purge_threads = 4
innodb_purge_batch_size = 300
innodb_purge_rseg_truncate_frequency = 128
innodb_deadlock_detect = ON
innodb_print_all_deadlocks = ON
innodb_lock_wait_timeout = 50
innodb_table_locks = ON
innodb_sync_array_size = 1
innodb_sync_spin_loops = 30
innodb_print_ddl_logs = OFF
innodb_replication_delay = 0
innodb_cmp_per_index_enabled = OFF
innodb_disable_sort_file_cache = OFF
innodb_numa_interleave = OFF
innodb_strict_mode = ON
innodb_sort_buffer_size = 1M
innodb_fast_shutdown = 1
innodb_force_load_corrupted = OFF
innodb_force_recovery = 0
innodb_temp_tablespaces_dir = ./#innodb_temp/
innodb_tmpdir = ./
innodb_temp_data_file_path = ibtmp1:64M:autoextend
innodb_page_cleaners = 4
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_parts = 8
innodb_flush_log_at_timeout = 1
innodb_fsync_threshold = 0
innodb_fill_factor = 90
innodb_file_per_table = ON
sql_generate_invisible_primary_key = ON
innodb_autoextend_increment = 64
innodb_open_files = 100000
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_instances = 1
innodb_log_files_in_group = 8
innodb_log_file_size = 64M
innodb_redo_log_capacity = 1G
innodb_flush_neighbors = 0
innodb_io_capacity = 4000
innodb_io_capacity_max = 20000
innodb_autoinc_lock_mode = 2
innodb_change_buffer_max_size = 25
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 128M
#### for performance_schema
performance_schema =OFF
performance_schema_consumer_global_instrumentation =ON
performance_schema_consumer_thread_instrumentation =ON
performance_schema_consumer_events_stages_current =ON
performance_schema_consumer_events_stages_history =ON
performance_schema_consumer_events_stages_history_long =OFF
performance_schema_consumer_statements_digest =ON
performance_schema_consumer_events_statements_current =ON
performance_schema_consumer_events_statements_history =ON
performance_schema_consumer_events_statements_history_long =OFF
performance_schema_consumer_events_waits_current =ON
performance_schema_consumer_events_waits_history =ON
performance_schema_consumer_events_waits_history_long =OFF
performance-schema-instrument ='memory/%=COUNTED'
# -- ~ _ ~ ~ _ ~ ~ _ ~ --
# base on mysql-8.0.30
# generated by https://www.sqlpy.com 2022年09月16日 23:45
# wechat: jianglegege
# email: 1721900707@qq.com
# -- ~ _ ~ --
