MySQL master-slave synchronous read-write separation configuration

#mysql主库配置添加:

# Cache User table and index data of the most important cache space, generally recommended 50%~80%, but should be based on the actual situation of MySQL server, if running other

#的应用服务或者有 the MyISAM table, you need to subtract thememory required by the system + application + MyISAM engine before innodb_buffer_pool_size

#实际值, concrete analysis of specific circumstances.

Innodb_buffer_pool_size = 4096M

#打开bin-log log for slave machine to read the synchronization log.

Log-bin=/mnt/mysql/log/mysql-bin.log

# is used to identify which server the statement was originally written from and can be identified with the last segment of the LAN IP.

server-id=174

#二进制日志自动删除日志过期的天数, the default value of 0 means no deletion.

Expire_logs_day=3

# once every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

#等于0时, after the transaction commits, MySQL does not do fsync such as disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem

#自行决定什么时候来做同步, or the cache is full before synchronizing to disk.

Sync_binlog = 30

# STATEMENT ROW MIXED

Binlog_format = MIXED

#打开慢查询日志

Slow_query_log = On

#指定慢查询日志文件路径与文件名称

Slow_query_log_file =/mnt/mysql/lib/mysql/db01-slow.log

#sql执行时间超过多少秒会记录到慢查询日志中

Long_query_time = 1

#指定数据库字符集

Character-set-server=utf8

#指定客户端字符集

Character-set-client=utf8

Collation-server=utf8_unicode_ci

#最大连接数

max_connections=1000

#打开Semi-synchronous replication plug -in the plug-in installation instructions: After logging into the database administrator account execution:

#INSTALL PLUGIN rpl_semi_sync_master SONAME ' Semisync_master.dll ';

#innodb的主从同步默认是异步的, using a semi-synchronous plug-in, the main library waits after a transaction is executed, as long as there is a synchronization library

#返回成功就会提交事务, if you wait longer than the set time limit, it becomes asynchronous and commits the transaction.

Rpl_semi_sync_master_enabled=1

#毫秒单位, how much time the master server is not waiting for slave acknowledgment to become asynchronous execution.

rpl_semi_sync_master_timeout=1000

#接收的最大数据包

Max_allowed_packet = 8M

#mysql从库配置添加:

# used to identify which server the statement was originally written from, and can be identified with the last segment of the LAN IP.

Server-id=41

# This is the parameter that can be written to the Binlog log when copying data from the library from the main library

Log-slave-updates = On

#二进制日志自动删除日志过期的天数, the default value of 0 means no deletion.

Expire_logs_days = 2

# once every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

#等于0时, after the transaction commits, MySQL does not do fsync such as disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem

#自行决定什么时候来做同步, or the cache is full before synchronizing to disk.

Sync_binlog = 30

# STATEMENT ROW MIXED

Binlog_format = Mixed

#指定数据库字符集

Character-set-server=utf8

#指定客户端字符集

Character-set-client=utf8

Collation-server=utf8_unicode_ci

#最大连接数

max_connections=1000

#打开独立表空间, all tables are in a table space when they are not opened.

Innodb_file_per_table

Innodb_buffer_pool_size = 4096M

Innodb_additional_mem_pool_size = 20M

Innodb_log_file_size = 512M

Innodb_log_buffer_size = 16M

Slow_query_log = On

Slow_query_log_file =/mnt/mysql/bin-log/db02-slow.log

Long_query_time = 1

Rpl_semi_sync_slave_enabled=1

Max_allowed_packet = 8M

Innodb_flush_log_at_trx_commit = 2

This article is from the "11403817" blog, please be sure to keep this source http://11413817.blog.51cto.com/11403817/1903971

MySQL master-slave synchronous read-write separation configuration