【Mysql】備份一個mysql my.cnf配置檔案

小亮520cl發表於2016-04-21
MySql
記錄一個my.cnf吧 以免不時之需

  1. [client]
    port = 3307
    socket = /home/data/mydata/3307/mysql.sock


    [mysqld]
    port = 3307
    socket = /home/data/mydata/3307/mysql.sock


    basedir = /home/data/mysql
    datadir = /home/data/mydata/3307
    pid-file = /home/data/mydata/3307/mysql.pid
    server-id = 0013307


    skip-name-resolve
    back_log = 600


    ft_min_word_len          = 1
    max_connections = 8096
    max_connect_errors = 99999999
    #open_files_limit = 65535
    table_open_cache = 2048 
    max_allowed_packet = 128M
    binlog_cache_size = 8M
    max_heap_table_size = 128M
    tmp_table_size = 128M
    read_buffer_size = 16M
    read_rnd_buffer_size = 32M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    key_buffer_size = 128M
    thread_cache_size = 8
    query_cache_size = 0
    query_cache_limit = 2M
  2. table_open_cache_instances = 128
  3. innodb_online_alter_log_max_size=1G
  4. innodb_io_capacity=2000



    log-bin = mysql-bin
    binlog_format = ROW
    replicate-wild-do-table=test.%
    replicate-wild-do-table=mysql.%
    replicate-wild-do-table= information_schema.%
    replicate-wild-do-table=interface_hd_com.%
    replicate-wild-ignore-table=test2.%
    replicate-wild-ignore-table= performance_schema.%
    log_slave_updates
    relay-log=mysql-relay-bin
    skip_slave_start


    gtid_mode=ON
    enforce-gtid-consistency
    report-host=192.168.1.157
    report-port=3307
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
    #sync-master-info = 1


    expire_logs_days = 3
    log_error = /home/data/mydata/3307/mysql-error.log
    slow_query_log = 1
    long_query_time = 1
    slow_query_log_file = /home/data/mydata/3307/mysql-slow.log
    performance_schema = 0
    skip-external-locking


    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_open_files = 1500
    innodb_buffer_pool_size = 10240M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 0
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 32M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120


    #innodb_undo_tablespaces = 16
    #innodb_undo_directory = /home/data/mydata/3307/undo


    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1


    interactive_timeout = 28800
    wait_timeout = 28800


    [mysqldump]
    quick
    max_allowed_packet = 16M


    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

備註：關於replicate-wild-do-table

  1. 1 如果同時使用replicate-wild-do-table replicate-wild-ignore-table

  1. replicate-wild-do-table=test.%
    replicate-wild-ignore-table=test2.%
    那麼：主庫上的只有test的操作會同步至從庫，test2和其它的庫都不會同步，新建db 也不會同步


 2.只有replicate-wild-ignore-table 

 replicate-wild-ignore-table= liuliang.%
  那麼：主庫上只有liuliang庫的操作不同步，其它的都同步，並且新建庫也會同步！

 

 3.只有replicate-wild-do-table

 replicate-wild-do-table=oauth.%
那麼：主庫上只有oauth的操作才同步，其它的都不同步，新建的db也不會同步     ===等同於第一種模式！

再備份一個
  1. [client]
    port    = 3306
    socket  = /data/3306/mysql.sock
    default-character-set=utf8mb4




    [mysql]
    no-auto-rehash
    default-character-set=utf8mb4




    [mysqld]
    init-connect='SET NAMES utf8mb4'
    character-set-server = utf8mb4
    skip-character-set-client-handshake 
    lower_case_table_names = 1
    federated
    secure_file_priv=''




    server-id=139
    port    = 3306
    socket  = /data/3306/mysql.sock
    basedir = /usr/local/mysql
    datadir = /data/3306/data
    transaction_isolation = REPEATABLE-READ
    log-error = /data/3306/error.log
    slow_query_log_file=/data/3306/slow-log.log
  2. slow_query_log = 1
    pid-file = /data/3306/mysql.pid
    log-bin = /data/3306/binlog/mysql-bin
    binlog_format=ROW
    slave-skip-errors = 1032,1062




    skip-external-locking
    skip-name-resolve
    default-storage-engine = INNODB
    wait_timeout=200
    connect_timeout = 20
    interactive_timeout=200
    back_log = 500    
    event_scheduler = ON   --開啟event事件支援
    long_query_time = 1
    max_binlog_size = 256M
    binlog_cache_size = 8M
    expire_logs_days = 7
    relay_log_purge = 0      ----relay lof 自動清除關閉，開啟後可透過flush logs手動清除
    relay_log_recovery = 1
    skip-slave-start = 1
    log_slave_updates = 1
    max_connections = 3000
    table_open_cache = 6144
    table_definition_cache = 4096
    sort_buffer_size = 512K
    read_buffer_size = 512K
    read_rnd_buffer_size = 512K
    tmp_table_size = 64M    --記憶體臨時表的最大值
    max_heap_table_size = 64M  
    bulk_insert_buffer_size = 32M   -- 批次插入暫存使用記憶體
    thread_cache_size = 8M
    thread_stack = 256K
    max_allowed_packet = 500M




    join_buffer_size = 4M
    innodb_buffer_pool_size = 20G
    innodb_buffer_pool_instances = 4
    innodb_additional_mem_pool_size = 16M
    innodb_log_file_size = 1024M
    innodb_log_buffer_size = 64M
    innodb_log_files_in_group = 3
    innodb_flush_log_at_trx_commit = 1
    innodb_sync_spin_loops = 40    --上下文切換自旋鎖
    innodb_max_dirty_pages_pct = 90
    innodb_support_xa = 1
    innodb_thread_concurrency = 0
    innodb_thread_sleep_delay = 500
    innodb_file_io_threads = 4
    innodb_lock_wait_timeout = 200
    innodb_concurrency_tickets = 1000
    log_bin_trust_function_creators = 1
    innodb_flush_method = O_DIRECT
    innodb_file_per_table = 1
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8
    innodb_io_capacity = 1500
    innodb_file_format = Barracuda
    innodb_purge_threads = 4
    innodb_purge_batch_size = 32
    innodb_old_blocks_pct=75
    innodb_change_buffering = all
    group_concat_max_len=102400
    loose_max_statement_time=600000
    innodb_print_all_deadlocks = 1


    [mysqldump]
    quick
    max_allowed_packet = 500M


    [mysqld_safe]
    log-error=/data/3306/mysql.err
    pid-file=/data/3306/mysqld.pid

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2085199/，如需轉載，請註明出處，否則將追究法律責任。

相關文章