mysql一主一从配置文件
主3306my.cnf
[client] port = 3306 socket = /var/lib/mysql/3306-mysql.sock [mysqld] user = hdfs port = 3306 socket = /var/lib/mysql/3306-mysql.sock datadir = /export/data/mysql/data/3306/data pid_file = /export/mysql/pids/3306-mysqld.pid bind-address = 0.0.0.0 lower_case_table_names = 1 collation_server = utf8_bin character_set_server = utf8 skip-name-resolve skip-external-locking explicit_defaults_for_timestamp tmp_table_size = 24M tmpdir = /export/data/mysql/data/3306/tmp back_log = 1024 max_connections = 1000 max_user_connections = 0 max_connect_errors = 2000 table_open_cache = 2048 table_definition_cache = 2048 table_open_cache_instances = 16 open_files_limit = 65534 binlog_cache_size = 512K bulk_insert_buffer_size = 64M ft_min_word_len = 4 join_buffer_size = 6M key_buffer_size = 32M max_allowed_packet = 1024M max_heap_table_size = 32M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_sort_buffer_size = 24M query_cache_limit = 2M query_cache_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 3M sort_buffer_size = 4M thread_cache_size = 512 thread_stack = 192K sql_mode = NO_ENGINE_SUBSTITUTION # log general_log = off general_log_file = /export/mysql/logs/3306-general-log.log log_error_verbosity = 2 log_error = /export/mysql/logs/3306-mysqld-err.log log_timestamps = SYSTEM slow_query_log = on slow_query_log_file = /export/mysql/logs/3306-mysqld-slow.log long_query_time = 0.1 log_slow_admin_statements = on log_slow_slave_statements = on binlog_rows_query_log_events = on # replication #read_only #super_read_only log_slave_updates skip-slave-start slave_compressed_protocol = off slave_parallel_type = database binlog_group_commit_sync_delay = 50 binlog_group_commit_sync_no_delay_count =100 binlog_format = row binlog_row_image = full expire_logs_days = 10 gtid_mode = on enforce_gtid_consistency = on gtid_executed_compression_period = 1000 log-bin = /export/data/mysql/data/3306/binlog/mysql-bin master_info_repository = table relay_log = /export/data/mysql/data/3306/binlog/mysqld-relay-bin relay_log_info_repository = table server-id = 1 slave-net-timeout = 60 slave_checkpoint_group = 512 slave_checkpoint_period = 300 slave_load_tmpdir = /export/data/mysql/data/3306/tmp slave_parallel_workers = 5 slave_pending_jobs_size_max = 128M sync_binlog = 1 report_port = 3306 report_host = 192.168.0.2 # innodb innodb_print_all_deadlocks default-storage-engine = InnoDB innodb_autoextend_increment = 64 innodb_buffer_pool_dump_at_shutdown = on innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = on innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:64M:autoextend innodb_data_home_dir = /export/data/mysql/data/3306/ibdata innodb_fast_shutdown = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 3000 innodb_io_capacity_max = 5000 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 32M innodb_log_file_size = 1024M innodb_log_files_in_group = 4 innodb_log_group_home_dir = /export/data/mysql/data/3306/iblog innodb_max_dirty_pages_pct = 75 innodb_online_alter_log_max_size = 1342177280 innodb_open_files = 64535 innodb_purge_threads = 1 innodb_read_io_threads = 16 innodb_sort_buffer_size = 4M innodb_thread_concurrency = 2 innodb_undo_directory = /export/data/mysql/data/3306/iblog innodb_undo_logs = 128 innodb_undo_tablespaces = 2 innodb_write_io_threads = 16 transaction-isolation = READ-COMMITTED innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_large_prefix = 1 # Plugin # Semi_sync plugin-load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_master_enabled = 1 rpl_semi_sync_slave_enabled = 1 rpl_semi_sync_master_timeout = 1000 # Server_audit # loose_server_audit_events = CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL # loose_server_audit_excl_users = monitor # loose_server_audit_file_path = pattern_logdir/mysqld-audit.log # loose_server_audit_file_rotate_size = 524288000 # loose_server_audit_file_rotations = 20 # loose_server_audit_query_log_limit = 10240 # loose_server_audit_logging = 1 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\v \\d \\r:\\m:\\s> default-character-set=utf8
3307my.cnf
[client] port = 3307 socket = /var/lib/mysql/3307-mysql.sock [mysqld] user = hdfs port = 3307 socket = /var/lib/mysql/3307-mysql.sock datadir = /export/data/mysql/data/3307/data pid_file = /export/mysql/pids/3307-mysqld.pid bind-address = 0.0.0.0 lower_case_table_names = 1 collation_server = utf8_bin character_set_server = utf8 skip-name-resolve skip-external-locking explicit_defaults_for_timestamp tmp_table_size = 24M tmpdir = /export/data/mysql/data/3307/tmp back_log = 1024 max_connections = 1000 max_user_connections = 0 max_connect_errors = 2000 table_open_cache = 2048 table_definition_cache = 2048 table_open_cache_instances = 16 open_files_limit = 65534 binlog_cache_size = 512K bulk_insert_buffer_size = 64M ft_min_word_len = 4 join_buffer_size = 6M key_buffer_size = 32M max_allowed_packet = 1024M max_heap_table_size = 32M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_sort_buffer_size = 24M query_cache_limit = 2M query_cache_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 3M sort_buffer_size = 4M thread_cache_size = 512 thread_stack = 192K sql_mode = NO_ENGINE_SUBSTITUTION # log general_log = off general_log_file = /export/mysql/logs/3307-general-log.log log_error_verbosity = 2 log_error = /export/mysql/logs/3307-mysqld-err.log log_timestamps = SYSTEM slow_query_log = on slow_query_log_file = /export/mysql/logs/3307-mysqld-slow.log long_query_time = 0.1 log_slow_admin_statements = on log_slow_slave_statements = on binlog_rows_query_log_events = on # replication read_only super_read_only log_slave_updates skip-slave-start slave_compressed_protocol = off slave_parallel_type = database binlog_group_commit_sync_delay = 50 binlog_group_commit_sync_no_delay_count =100 binlog_format = row binlog_row_image = full expire_logs_days = 10 gtid_mode = on enforce_gtid_consistency = on gtid_executed_compression_period = 1000 log-bin = /export/data/mysql/data/3307/binlog/mysql-bin master_info_repository = table relay_log = /export/data/mysql/data/3307/binlog/mysqld-relay-bin relay_log_info_repository = table server-id = 1 slave-net-timeout = 60 slave_checkpoint_group = 512 slave_checkpoint_period = 300 slave_load_tmpdir = /export/data/mysql/data/3307/tmp slave_parallel_workers = 5 slave_pending_jobs_size_max = 128M sync_binlog = 1 report_port = 3306 report_host = 192.168.0.2 # innodb innodb_print_all_deadlocks default-storage-engine = InnoDB innodb_autoextend_increment = 64 innodb_buffer_pool_dump_at_shutdown = on innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = on innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:64M:autoextend innodb_data_home_dir = /export/data/mysql/data/3307/ibdata innodb_fast_shutdown = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 3000 innodb_io_capacity_max = 5000 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 32M innodb_log_file_size = 1024M innodb_log_files_in_group = 4 innodb_log_group_home_dir = /export/data/mysql/data/3307/iblog innodb_max_dirty_pages_pct = 75 innodb_online_alter_log_max_size = 1342177280 innodb_open_files = 64535 innodb_purge_threads = 1 innodb_read_io_threads = 16 innodb_sort_buffer_size = 4M innodb_thread_concurrency = 2 innodb_undo_directory = /export/data/mysql/data/3307/iblog innodb_undo_logs = 128 innodb_undo_tablespaces = 2 innodb_write_io_threads = 16 transaction-isolation = READ-COMMITTED innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_large_prefix = 1 # Plugin # Semi_sync plugin-load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_master_enabled = 1 rpl_semi_sync_slave_enabled = 1 rpl_semi_sync_master_timeout = 1000 # Server_audit # loose_server_audit_events = CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL # loose_server_audit_excl_users = monitor # loose_server_audit_file_path = pattern_logdir/mysqld-audit.log # loose_server_audit_file_rotate_size = 524288000 # loose_server_audit_file_rotations = 20 # loose_server_audit_query_log_limit = 10240 # loose_server_audit_logging = 1 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@\\v \\d \\r:\\m:\\s>
