V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
s609926202
V2EX  ›  MySQL

Mysql 修改 my.cnf binlog_format 配置为 ROW 后,重启不生效

  •  
  •   s609926202 ·
    shangdev · 2022-03-19 15:47:45 +08:00 · 1638 次点击
    这是一个创建于 965 天前的主题,其中的信息可能已经有所发展或是发生改变。
    也尝试了 SET GLOBAL binlog_format = 'MIXED' ,然后重启 Mysql 。

    贴上 my.cnf 配置,是被其他什么配置给影响了吗?

    ```
    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysql]
    no-auto-rehash

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/var
    skip-external-locking
    character-set-server = utf8mb4
    # skip_name_resolve = 1

    #若你的 MySQL 数据库主要运行在境外,请务必根据实际情况调整本参数
    default_time_zone = "+8:00"

    open_files_limit = 65535
    back_log = 1024
    max_connections = 1000
    max_connect_errors = 1000000
    table_definition_cache = 400
    table_open_cache_instances = 64
    thread_stack = 512K
    max_allowed_packet = 32M
    sort_buffer_size = 16M
    join_buffer_size = 16M
    thread_cache_size = 1500
    interactive_timeout = 600
    wait_timeout = 600
    tmp_table_size = 96M
    max_heap_table_size = 96M
    slow_query_log = 1
    log_timestamps = SYSTEM
    slow_query_log_file = slow.log
    log-error = error.log
    long_query_time = 2
    log_queries_not_using_indexes =1
    log_throttle_queries_not_using_indexes = 60
    min_examined_row_limit = 100
    log_slow_admin_statements = 1
    log_slow_slave_statements = 1

    # binlog
    server_id = 1
    log-bin = mysql-bin
    binlog_format = ROW

    #注意:MySQL 8.0 开始,binlog_expire_logs_seconds 选项也存在的话,会忽略 expire_logs_days 选项
    expire_logs_days = 30

    master_info_repository = TABLE
    relay_log_info_repository = TABLE
    gtid_mode = on
    enforce_gtid_consistency = 1
    log_slave_updates
    slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
    binlog_format = mixed
    binlog_checksum = 1
    relay_log_recovery = 1
    relay-log-purge = 1
    key_buffer_size = 15M
    read_buffer_size = 8M
    read_rnd_buffer_size = 16M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    lock_wait_timeout = 3600
    explicit_defaults_for_timestamp = 1
    innodb_thread_concurrency = 0
    innodb_sync_spin_loops = 100
    innodb_spin_wait_delay = 30

    transaction_isolation = REPEATABLE-READ
    #innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 25088M
    innodb_buffer_pool_instances = 4
    innodb_buffer_pool_load_at_startup = 1
    innodb_buffer_pool_dump_at_shutdown = 1
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 32M
    innodb_log_file_size = 2G
    innodb_log_files_in_group = 2
    innodb_max_undo_log_size = 4G

    # 根据您的服务器 IOPS 能力适当调整
    # 一般配普通 SSD 盘的话,可以调整到 10000 - 20000
    # 配置高端 PCIe SSD 卡的话,则可以调整的更高,比如 50000 - 80000
    innodb_io_capacity = 4000
    innodb_io_capacity_max = 8000
    innodb_flush_sync = 0
    innodb_flush_neighbors = 0
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_purge_threads = 4
    innodb_page_cleaners = 4
    innodb_open_files = 65535
    innodb_max_dirty_pages_pct = 50
    innodb_flush_method = O_DIRECT
    innodb_lru_scan_depth = 4000
    innodb_checksum_algorithm = crc32
    innodb_lock_wait_timeout = 10
    innodb_rollback_on_timeout = 1
    innodb_print_all_deadlocks = 1
    innodb_file_per_table = 1
    innodb_online_alter_log_max_size = 4G
    innodb_stats_on_metadata = 0
    innodb_undo_log_truncate = 1

    # some var for MySQL 5.7
    innodb_checksums = 1
    #innodb_file_format = Barracuda
    #innodb_file_format_max = Barracuda
    query_cache_size = 8M
    query_cache_type = 0
    innodb_undo_logs = 128

    #注意:MySQL 8.0.16 开始删除该选项
    internal_tmp_disk_storage_engine = InnoDB

    innodb_status_file = 1
    #注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致 log-error 文件增长较快
    innodb_status_output = 0
    innodb_status_output_locks = 0

    #performance_schema
    performance_schema = 1
    performance_schema_instrument = '%memory%=on'
    performance_schema_instrument = '%lock%=on'

    #innodb monitor
    innodb_monitor_enable="module_innodb"
    innodb_monitor_enable="module_server"
    innodb_monitor_enable="module_dml"
    innodb_monitor_enable="module_ddl"
    innodb_monitor_enable="module_trx"
    innodb_monitor_enable="module_os"
    innodb_monitor_enable="module_purge"
    innodb_monitor_enable="module_log"
    innodb_monitor_enable="module_lock"
    innodb_monitor_enable="module_buffer"
    innodb_monitor_enable="module_index"
    innodb_monitor_enable="module_ibuf_system"
    innodb_monitor_enable="module_buffer_page"
    innodb_monitor_enable="module_adaptive_hash"

    default_storage_engine = InnoDB

    sql_mode = NO_ENGINE_SUBSTITUTIO
    ```
    1 条回复
    s609926202
        1
    s609926202  
    OP
       2022-03-19 16:02:27 +08:00
    结贴了,,应该是被下面还有一个 binlog_format 给覆盖了,愣是没找到
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   944 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 20:47 · PVG 04:47 · LAX 12:47 · JFK 15:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.