mysql数据库读写分离

grant replication slave on *.* to 'rep'@'172.17.1.%' identified by '12QAZXCvfr34';

change master to master_host='172.17.1.150',master_user='rep',master_password='12QAZXCvfr34',master_log_file=' mysql -bin.000001',master_log_pos=107;

master配置：

[mysql] port = 3306 socket = /mysql/mysqld.sock prompt="\u@\h \d>"

[mysqld] #Instance server-id = 44170 port = 3306 socket = /mysql/mysqld.sock pid-file = /mysql/mysqld.pid datadir = /mysql/data/ tmpdir = /tmp/ log-error = /mysql/mysqld.err

#Bin-log parameter log-bin = /mysql/binlog/mysql-bin log-bin-index = /mysql/binlog/mysql-bin.index binlog_cache_size = 12m binlog_format = mixed sync_binlog = 0 max_binlog_cache_size = 1024m max_binlog_size = 512m expire_logs_days = 7

#Server parameter skip_name_resolve sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES' max_allowed_packet = 64m max_connections = 8192 table_open_cache = 1024 open_files_limit = 8192 thread_cache_size = 512 query_cache_size = 0 tmp_table_size = 512m max_heap_table_size = 2048m

#MyISAM parameter skip-external-locking join_buffer_size = 16m sort_buffer_size = 16m read_buffer_size = 64m read_rnd_buffer_size = 64m key_buffer_size = 16384m

#Innodb parameter innodb_data_file_path = ibdata1:1024m:autoextend innodb_file_per_table = 1 innodb_log_file_size = 64m innodb_log_files_in_group = 2 innodb_log_buffer_size = 4m innodb_flush_log_at_trx_commit = 2

innodb_buffer_pool_size = 256m innodb_additional_mem_pool_size = 16m innodb_io_capacity = 200 innodb_max_dirty_pages_pct = 50 innodb_lock_wait_timeout = 30

long_query_time = 3 slow_query_log = ON

#Slave GTID gtid-mode = on log-slave-updates = true enforce-gtid-consistency = true master-info-repository = TABLE relay-log-info-repository = TABLE sync-master-info = 1 slave-parallel-workers = 2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1

[mysqldump] quick max_allowed_packet = 64m socket=/mysql/mysqld.sock

[myisamchk] key_buffer_size = 2048m sort_buffer_size = 256m read_buffer = 256m write_buffer = 64m

[mysqlhotcopy] socket = /mysql/mysqld.sock

[mysqladmin] port = 3306 socket = /mysql/mysqld.sock

slave配置：

[mysql] port = 3306 socket = /mysql/mysqld.sock prompt="\u@\h \d>"

[mysqld] #Instance server-id = 44170 port = 3306 socket = /mysql/mysqld.sock pid-file = /mysql/mysqld.pid datadir = /mysql/data/ tmpdir = /tmp/ log-error = /mysql/mysqld.err

#Bin-log parameter log-bin = /mysql/binlog/mysql-bin log-bin-index = /mysql/binlog/mysql-bin.index relay-log = /mysql/binlog/relay-log relay-log-index = /mysql/binlog/relay-log.index binlog_cache_size = 12m binlog_format = mixed sync_binlog = 0 max_binlog_cache_size = 1024m max_binlog_size = 512m expire_logs_days = 7

#Server parameter skip_name_resolve sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES' max_allowed_packet = 64m max_connections = 8192 table_open_cache = 1024 open_files_limit = 8192 thread_cache_size = 512 query_cache_size = 0 tmp_table_size = 512m max_heap_table_size = 2048m

#MyISAM parameter skip-external-locking join_buffer_size = 16m sort_buffer_size = 16m read_buffer_size = 64m read_rnd_buffer_size = 64m key_buffer_size = 16384m

#Innodb parameter innodb_data_file_path = ibdata1:1024m:autoextend innodb_file_per_table = 1 innodb_log_file_size = 64m innodb_log_files_in_group = 2 innodb_log_buffer_size = 4m innodb_flush_log_at_trx_commit = 2

innodb_buffer_pool_size = 256m innodb_additional_mem_pool_size = 16m innodb_io_capacity = 200 innodb_max_dirty_pages_pct = 50 innodb_lock_wait_timeout = 30

long_query_time = 3 slow_query_log = ON

#Slave GTID gtid-mode = on log-slave-updates = true enforce-gtid-consistency = true master-info-repository = TABLE relay-log-info-repository = TABLE sync-master-info = 1 slave-parallel-workers = 2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1

[mysqldump] quick max_allowed_packet = 64m socket=/mysql/mysqld.sock

[myisamchk] key_buffer_size = 2048m sort_buffer_size = 256m read_buffer = 256m write_buffer = 64m

[mysqlhotcopy] socket = /mysql/mysqld.sock

[mysqladmin] port = 3306 socket = /mysql/mysqld.sock