[client] port = 3306 socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=true port = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data tmpdir = /data/mysql/mysql3306/tmp pid-file = /data/mysql/mysql3306/tmp/mysql.pid socket = /data/mysql/mysql3306/tmp/mysql.sock #skip-grant-tables #character set character_set_server = utf8 open_files_limit = 65535 back_log = 500 #event_scheduler = ON #lower_case_table_names=1 skip-external-locking skip_name_resolve = 1 default-storage-engine = InnoDB #timeout wait_timeout=1000 interactive_timeout=1000 connect_timeout = 20 server-id =1293306 #ip最后一位+端口号= 1003376 #percona 的--recursion-method slavehost模式 #report_host = 10.105.9.115 #report_port = 3306 #plugin plugin-load="semisync_master.so;semisync_slave.so" #########error log############# log-error = /data/mysql/mysql3306/logs/error.log log-warnings = 2 #########general log############# #general_log=1 #general_log_file=/data/mysql/mysql3306/logs/mysql.log #########slow log############# slow_query_log = 1 long_query_time=1 slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow ############# for replication################### log-bin = /data/mysql/mysql3306/logs/mysql-bin binlog_format = row max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 2M expire-logs-days = 7 slave-net-timeout=30 log_bin_trust_function_creators = 1 log-slave-updates = 1 skip-slave-start = 1 #read_only =1 #从上设置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1 enforce_gtid_consistency=1 #relay log relay-log = /data/mysql/mysql3306/logs/mysql-relay relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index max-relay-log-size = 500M #replication crash safe sync_master_info = 1 sync_relay_log_info = 1 sync_relay_log = 1 relay_log_recovery = 1 master_info_repository = TABLE relay_log_info_repository = TABLE #semisync 动态开启 主从切换的时候用 #rpl_semi_sync_master_enabled = 1 #rpl_semi_sync_master_wait_no_slave = 1 #rpl_semi_sync_master_timeout = 1000 #rpl_semi_sync_slave_enabled = 1 #rpl_semi_sync_master_timeout = 100000000 #不要超时,防止切异步,保证主从数据的完全一致性,默认是10000 #rpl_semi_sync_master_wait_point = 'after_sync' #5.7新增参数 after_sync 5.7增强半同步 after_commit5.6普通半同步 #rpl_semi_sync_master_wait_for_slave_count = 2 #5.7新增参数 等待多少个从库接收到binlog #ignore #replicate-ignore-db = 'school','school2' #replicate-do-db = 'school','school2' #Multi-threaded Slave #slave_parallel_workers=16 #slave-parallel-type=LOGICAL_CLOCK #slave-parallel-type=DATABASE #replication error #slave-skip-errors=1007,1051,1062 #######per_thread_buffers##################### max_connections=1100 max_user_connections=1000 max_connect_errors=1000 #myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096 table_open_cache = 6144 table_definition_cache = 4096 read_buffer_size = 1M join_buffer_size = 128K read_rnd_buffer_size = 1M #myisam sort_buffer_size = 128K myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0 query_cache_size = 0 bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_stack = 192K ###############InnoDB########################### innodb_data_home_dir = /data/mysql/mysql3306/data innodb_log_group_home_dir = /data/mysql/mysql3306/logs innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 1G #根据内存大小设置 innodb_buffer_pool_instances = 8 #innodb_additional_mem_pool_size = 16M innodb_log_file_size = 500M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 80 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_concurrency_tickets = 1000 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800 #机械盘800 ssd 2000 innodb_flush_neighbors = 1 innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一个，5.7可以设置多个 innodb_purge_batch_size = 32 innodb_old_blocks_pct=75 innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1 #innodb_status_output=1 #innodb_status_output_locks=1 performance_schema=0 #是否打开P_S库进行性能收集 transaction_isolation = READ-COMMITTED #innodb_force_recovery=0 #innodb_fast_shutdown=1 #innodb_status_file = 1 #实时输出show engine innodb status到innodb_status文件 ############# PXC ##################### innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-dongzheng wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 wsrep_node_address=192.168.3.129 wsrep_slave_threads=2 wsrep_sst_auth=sst:dongzheng wsrep_sst_method=xtrabackup-v2 [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash max_allowed_packet = 128M prompt = '(product)\u@\h:\p [\d]> ' default_character_set = utf8 [myisamchk] key_buffer_size = 64M sort_buffer_size = 512k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
View Code128