【Mysql】备份一个mysql my.cnf配置文件

记录一个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 如果同时使用replicate-wild-do-table replicate-wild-ignore-table 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也不会同步     ===等同于第一种模式！

再备份一个

[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 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2085199/