Mysql线上生产环境常见配置

下面是对于Mysql线上生产环境的配置和一些基本的解释：

    [client]

    ###
    ### client section - used for "mysql", "mysqladmin" and similar command line
    ### clients.
    ###

    #port = 3306
    #socket = /tmp/mysql.sock
    ### Just in case your current configuration is not using default values.

    [mysqld]

    ###
    ### mysqld section - used by MySQL Server (also applies to Percona Server,
    ### MariaDB etc.)
    ###

    #port = 3306
    #socket = /tmp/mysql.sock
    #user = mysql
    ### Just in case your current configuration is not using default values.

    datadir = /var/lib/mysql
    ### This must point to the main MySQL data directory.


    ###
    ### General Server Options:
    ###

    max_allowed_packet = 32M
    ### Default packet limit is almost always too small.

    max_connections = 2000
    ### Max connections as well (sleeping threads are okay to have)

    #table_open_cache = 2000
    ### Table open cache - defaults only good in MySQL 5.7 and 5.6
    ### On 5.1 or 5.5 set the above.

    #table_open_cache_instances = 16
    ### Table open cache instances - be sure to set this on MySQL 5.6.
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#table_open_cache_instances

    open_files_limit = 10000
    ### Increase the number of file descriptors, we're not in stone age now.

    tmp_table_size = 64M
    max_heap_table_size = 64M
    ### Incease limits for implicit and explicit temporary tables.

    tmpdir = /tmp
    ### Don't use tmpfs for tmpdir, or AIO will be disabled. And make sure
    ### There's enough room for temporary tables and files (usually 2-5GB)

    #thread_cache_size = 100
    ### Thread cache - this is now autosized in MySQL 5.6 and 5.7
    ### But on MySQL 5.1 and 5.5 do set the above

    default_storage_engine = InnoDB
    ### Default storage engine in most cases should be InnoDB. If in doubt:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#default_storage_engine

    skip_name_resolve
    ### Disabling DNS resolution - DNS based logins will stop working:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#skip_name_resolve

    query_cache_type=0
    query_cache_size=0
    ### Explicitly disabling the query cache. If you have a light workload, you
    ### may reconsider:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#query_cache_type

    #back_log = 512
    ### Consider increasing this if you have a high number of new connections
    ### (> 1000/sec) and you are running on MySQL 5.6 or older
    ### https://www.percona.com/blog/2011/04/19/mysql-connection-timeouts/

    #thread_concurrency = 0
    ### Do not tune this. This does nothing. And I have included it here only
    ### because I've seen too many people obsess over it.

    #join_buffer_size = 256k
    #sort_buffer_size = 256k
    #read_buffer_size = 128k
    #read_rnd_buffer_size = 256k
    ### Leave these at their defaults, do not change server-wide settings for them
    ### Instead, use session variable when you really need it like that:
    ### mysql> SET session read_buffer_size = 2 * 1024 * 1024;
    ### mysql> RUN YOUR QUERY;


    ###
    ### Binary logging section
    ###

    log_bin
    server_id = 1
    ### Enabling binlog as well as unique server_id for
    ### point in time recovery and, potentially, replication.

    max_binlog_size = 100M
    ### Don't have large binary logs, with file systems like ext3 this could
    ### cause stalls.

    #master_info_repository = TABLE
    #relay_log_info_repository = TABLE
    ### If you're on MySQL 5.6 or MySQL 5.7, store the binlog position
    ### to TABLE rather than FILE (that way position is in sync with db)

    expire_logs_days = 7
    ### Control the amount of binary logs server is allowed to keep (in days)

    sync_binlog = 0
    ### Disabling sync-binlog for better performance, but do consider the
    ### durability issues:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#sync_binlog

    binlog_format = MIXED
    ### For the binary log format, I recommend MIXED, but it's up to you.

    ###
    ### MyISAM Specific Options:
    ###

    ### Assuming MyISAM is not going to be used, therefore defaults used for MyISAM
    ### Still if you do plan to use it, this is probably the only variable you
    ### want to tune:
    #key_buffer_size = 128M


    ###
    ### InnoDB Specific Options:
    ###

    #innodb_buffer_pool_size = 96G
    ### Set the innodb buffer pool size to 75-80% of total memory (dedicated):
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_size
    ### The example above would be used on a dedicated 128GB MySQL server.

    #innodb_log_file_size = 2047M
    ### Allow 1-2h worth of writes to be buffered in transaction logs:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_log_file_size
    ### Helper command:
    ### mysql> pager grep seq
    ### mysql> show engine innodb status\G select sleep(60); show engine innodb status\G

    innodb_log_buffer_size = 16M
    ### Don't sweat about about this, just set it to 16M.

    innodb_flush_log_at_trx_commit = 0
    ### This, OTOH is really important. See:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_log_at_trx_commit

    innodb_flush_method = O_DIRECT
    ### On Linux, just leave it set to O_DIRECT.
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_method

    #innodb_buffer_pool_instances = 4
    ### Set this depending on MySQL version. Available since 5.6
    ### In 5.6, set to 4
    ### In 5.7, set to 8
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_instances

    innodb_thread_concurrency = 8
    ### Yes, this is not an error. Use 0 only for benchmarks and if you're nowhere
    ### near saturating your server.
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_thread_concurrency

    #innodb_io_capacity = 1000
    #innodb_io_capacity_max = 3000
    ### Base these on your server radom write IO capabilities
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_io_capacity

    innodb_stats_on_metadata = 0
    ### On 5.6 and 5.7 this is already turned off by default.
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_stats_on_metadata

    #innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
    #innodb_buffer_pool_load_at_startup  = 1 # MySQL 5.6+
    #innodb_buffer_pool_dump_pct = 75        # MySQL 5.7 only
    ### Enable these for faster warm-up
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_dump_at_shutdown

    #innodb_adaptive_hash_index_parts = 16      # MySQL 5.7 only
    #innodb_adaptive_hash_index_partitions = 16 # Percona Server only
    ### AHI is a common bottle-neck, however few versions of MySQL
    ### support AHI partitions:
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_adaptive_hash_index_parts

    #innodb_checksum_algorithm = crc32     # MySQL 5.6 or newer
    ### Hardware acceleratorion for checksums
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_checksum_algorithm

    innodb_read_io_threads = 16
    innodb_write_io_threads = 16
    ### This isn't super important to fine tune, but it's good to aling
    ### it to number of availble read / write spindles
    ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_read_io_threads

    innodb_file_per_table
    ### There's a number of reasons to use file per table, but beware that
    ### it doesn't convert tables automatically, and ibdata1 will never shrink anyways

    #innodb_open_files = 3000
    ### Set this on MySQL 5.1 and 5.5. On 5.6 and beyond it's autotuned
    ### specifies max number of .ibd files that MySQL can keep open at one time

    #innodb_flush_neighbors = 0
    ### Set to 0 if you're using SSD. For magnetic drives, keep it at 1
    ### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

    #innodb_flushing_avg_loops = 100
    ### Use this if you don't like how flushing activity behaves
    ### (e.g. if you get stalls)
    ### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flushing_avg_loops

    #innodb_page_cleaners = 8 # MySQL 5.7 only
    ### On a write-heavy environment, use this to gain even more control
    ### over the flushing activity. This is not yet tested too wildly.
    ### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners

    #innodb_old_blocks_time = 1000
    ### Use this only on 5.1 and 5.5 since 5.6 and 5.7 already have 1000 as a default
    ### This basically protects your buffer pool from occasional scans
    ### Although the way it works is quite complex, definitely not for my.cnf

    #innodb_max_dirty_pages_pct
    ### If you're in a write-heavy environment, but want to limit how much of it
    ### you use for dirty pages, this is the variable to configure. Defaults are
    ### version specific, but range in 75-90 %


    ###
    ### Slow query log (mostly Percona XtraDB Specific)
    ###

    long_query_time = 1.0
    ### Log queries that take > 1s to execute

    slow_query_log = 1
    ### Enable the slow query logging

    ### Only to be used on Percona Server:

    #log_slow_slave_statements = ON
    #log_slow_verbosity = full
    #slow_query_log_timestamp_always = ON
    #slow_query_log_timestamp_precision = microsecond
    #slow_query_log_use_global_control = all
    ### See here for more information:
    ### https://www.percona.com/doc/percona-server/5.6/diagnostics/slow_extended.html