Mysql database read/write splitting

Mysql database read/write splitting

Grant replication slave on *. * to 'rep '@ '192. 172. %' identified by '12qazxcvfr34 ';

Change master to master_host = '192. 17.1.150 ', master_user = 'rep', master_password = '12qazxcvfr34', master_log_file = 'mysql-bin.000001 ', master_log_pos = 172;

Master Configuration:

[Mysql]
Port = 3306
Socket =/mysql/mysqld. sock
Prompt = "\ u @ \ h \ d>"

[Mysqld]
# Instance
Servers-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 = 12 m
Binlog_format = mixed
Sync_binlog = 0
Max_binlog_cache_size = 1024 m
Max_binlog_size = 512 m
Expire_logs_days = 7

# Server parameter
Skip_name_resolve
SQL _mode = 'only _ FULL_GROUP_BY, STRICT_TRANS_TABLES'
Max_allowed_packet = 64 m
Max_connections = 8192
Table_open_cache = 1024
Open_files_limit = 8192
Thread _ cache_size = 512
Query_cache_size = 0
Tmp_table_size = 512 m
Max_heap_table_size = 2048 m

# MyISAM parameter
Skip-external-locking
Join_buffer_size = 16 m
Sort_buffer_size = 16 m
Read_buffer_size = 64 m
Read_rnd_buffer_size = 64 m
Key_buffer_size = 16384 m

# Innodb parameter
Innodb_data_file_path = ibdata1: 1024 m: autoextend
Innodb_file_per_table = 1
Innodb_log_file_size = 64 m
Innodb_log_files_in_group = 2
Innodb_log_buffer_size = 4 m
Innodb_flush_log_at_trx_commit = 2

Innodb_buffer_pool_size = 256 m
Innodb_additional_mem_pool_size = 16 m
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 = 64 m
Socket =/mysql/mysqld. sock

[Myisamchk]
Key_buffer_size = 2048 m
Sort_buffer_size = 256 m
Read_buffer = 256 m
Write_buffer = 64 m

[Mysqlhotcopy]
Socket =/mysql/mysqld. sock

[Mysqladmin]
Port = 3306
Socket =/mysql/mysqld. sock

Slave Configuration:

[Mysql]
Port = 3306
Socket =/mysql/mysqld. sock
Prompt = "\ u @ \ h \ d>"

[Mysqld]
# Instance
Servers-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 = 12 m
Binlog_format = mixed
Sync_binlog = 0
Max_binlog_cache_size = 1024 m
Max_binlog_size = 512 m
Expire_logs_days = 7

# Server parameter
Skip_name_resolve
SQL _mode = 'only _ FULL_GROUP_BY, STRICT_TRANS_TABLES'
Max_allowed_packet = 64 m
Max_connections = 8192
Table_open_cache = 1024
Open_files_limit = 8192
Thread _ cache_size = 512
Query_cache_size = 0
Tmp_table_size = 512 m
Max_heap_table_size = 2048 m

# MyISAM parameter
Skip-external-locking
Join_buffer_size = 16 m
Sort_buffer_size = 16 m
Read_buffer_size = 64 m
Read_rnd_buffer_size = 64 m
Key_buffer_size = 16384 m

# Innodb parameter
Innodb_data_file_path = ibdata1: 1024 m: autoextend
Innodb_file_per_table = 1
Innodb_log_file_size = 64 m
Innodb_log_files_in_group = 2
Innodb_log_buffer_size = 4 m
Innodb_flush_log_at_trx_commit = 2

Innodb_buffer_pool_size = 256 m
Innodb_additional_mem_pool_size = 16 m
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 = 64 m
Socket =/mysql/mysqld. sock

[Myisamchk]
Key_buffer_size = 2048 m
Sort_buffer_size = 256 m
Read_buffer = 256 m
Write_buffer = 64 m

[Mysqlhotcopy]
Socket =/mysql/mysqld. sock

[Mysqladmin]
Port = 3306
Socket =/mysql/mysqld. sock