[mysqld]
#Set mysql installation directory
basedir =/usr/local/mysql
#Set the data storage directory of mysql database
datadir = /usr/local/mysql/data
#Set port
port = 3306
socket = /tmp/mysql.sock
#Set character set
character-set-server=utf8
#Log storage directory
log-error = /usr/local/mysql/data/mysqld.log
pid-file = /usr/local/mysql/data/mysqld.pid
#Allow time type data to be zero (remove NO_ZERO_IN_DATE,NO_ZERO_DATE)
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#===============================================Master (slave library configuration)=========================================================#
#Node ID to ensure uniqueness
server-id = 885
#Enable the binlog logging function of mysql
log-bin = mysql-bin
#The binlog of the control database is brushed to the disk. 0 is not controlled and the performance is the best. 1 is brushed to the log file every time the transaction is submitted. The performance is the worst and the safest
sync_binlog = 1
#binlog log format, mysql adopts statement by default, and mixed is recommended
binlog_format = mixed
#binlog expiration cleanup time
expire_logs_days = 30
#binlog size of each log file
max_binlog_size = 1000m
#binlog cache size
binlog_cache_size = 4m
#Maximum binlog cache size
max_binlog_cache_size= 512m
#Database to synchronize
binlog-do-db = efb
#For databases that do not generate log files, multiple ignored databases can be spliced with commas, or copy this sentence and write multiple lines
binlog-ignore-db=mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
#Offset from increment
auto-increment-offset = 1
#Self increment
auto-increment-increment = 1
#Skip from library error
slave-skip-errors = all
#===============================================Slave (slave configuration)=========================================================#
Server id = 890 # must be unique
log_ Bin = MySQL bin # these paths can be full path or no path relative to datadir. Open and set the binary log file name
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7 # days for automatic deletion / expiration of binary logs. The default value is 0, which means it will not be deleted automatically.
log_slave_updates = 1 # [key point] updates received from the master server shall be written to the binary log at the same time
#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M
Binlog do DB = EFB # database to synchronize
Binlog ignore DB = MySQL # does not need to synchronize the database
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
master_info_repository=TABLE
relay_log_info_repository=TABLE
read_only=1 # read / write separation, configure read-only from the library to avoid write operations from the library (only valid for ordinary users, not for super administrators)
super_read_only=1
#mysql5.7 added a new parameter super_read_only, this parameter makes the super administrator unable to write. It is not recommended. If the master server goes down, salve cannot write data after switching to the master database.
#===========================================Description of MASTER-SLAVE key configuration parameters===========================================#
log-bin:
To set up master-slave replication, binary logs must be enabled
server-id:
The unique identifier of mysql in the same group of master-slave structures. Each server must be different
server-uuid:
From mysql5 6. With this parameter, it is automatically generated during the database startup process. Each machine is different, and uuid is stored in auto CNF file
read only:
Set the read-only state of the slave library to avoid writing on the slave library, but this instruction is invalid for the super administrator, mysql5 7 added a new parameter super_read_only, this parameter makes the super administrator unable to write.
binlog_format:
Binary log format, which must be row mode
log_slave_updates:
Re write the change information obtained from the master library to the binlog log of the slave library.
log_error_action:
This parameter is used to control what happens to MySQL when the binlog file cannot be written. This parameter is mysql5 Added after 7. There's abort_server and ignore_error, abor_server means that MySQL exits when it encounters slow disk or unavailable file system when writing binlog; And ignore_error means that when binlog cannot be written, MySQL will record errors in the error log and force the binlog function to be turned off, which will affect the function of obtaining logs from the master database, resulting in inconsistent data between the master and slave databases. Mysql5 After 7, abort is used by default_ server
binlog-do-db:
Use this parameter to selectively copy the database (set on the primary database). For example, binlog do DB = ceshi, it means that only ceshi libraries are synchronized, and other libraries are not replicated and synchronized.
gtid_mode:
Determines whether the gtid mode is enabled. If the gtid copy mode is used, the gtid must be enabled_ mode=on
enforce-gtid-consistency:
When using gtid mode, this parameter should be enabled to ensure the consistent line of gtid enforce gtid consistency = on
gtid_next:
This parameter is a session level variable, the next gtid. The default is automatic
gtid_purged:
Discarded gtid
relay_log:
Record the binlog content from the I/O dump thread of the main library
replicate_do_table:
Only the specified table is copied and used on the slave library
replicate_ignore_table:
Tables that need to be ignored in replication are set and used on the slave library
replicate_ignore_db:
Ignore the copied library and set the usage from the library
replicate_do_db:
The library to be copied only can be set and used from the library
replicate_wild_do_table:
Use wildcard characters to specify the table to be copied. For example, the table beginning with TT under zs library is -- replicate_wild_do_table=zz.tt%
replicate_wild_ignore_table:
Use wildcards to specify tables to ignore
master_info_repository:
Put the master Info (master-slave status, configuration information) is recorded. By default, it is recorded in the file. It is recommended to use the table to record the master_info_repository=table
relay_log_recovery:
In order for the slave library to be crash safe, relay must be set_ log_ Recovery = 1, the meaning of this parameter is: when the slave database crashes or restarts, it will delete those relay logs that are finished, and retrieve the logs from the master database to generate the recovery of relay logs again. It is recommended to start relay on the slave database_ log_ The recovery parameter is off by default
relay_log_purge:
Clear the relay log that has been executed. It is recommended to open it from the library
slave_net_timeout:
This parameter sets the number of seconds after the binlog is not obtained from the master database, the slave database is considered to be timeout, and the slave I/O thread will reconnect to the master database. This value is from mysql5 7.7 the default value for starting is 60s
slave_parallel_type:
This parameter is from msyql5 Introduced in 7.2, there are two values, one is database and the other is logical_clock, in mysql5 After 7, the parallel replication based on group submission is introduced by setting the parameter slave_ parallel_ Workers > 0 and slave_parallel_type='LOGICAL_CLOCK 'implementation
slave_parallel_workers:
Set multiple processes to execute the transactions submitted by the main database in the relay log concurrently. The maximum value is 1024
[mysqld] #Set mysql installation directory basedir =/usr/local/mysql #Set the data storage directory of mysql database datadir = /usr/local/mysql/data #Set port port = 3306 socket = /tmp/mysql.sock #Set character set character-set-server=utf8 #Log storage directory log-error = /usr/local/mysql/data/mysqld.log pid-file = /usr/local/mysql/data/mysqld.pid #Allow time type data to be zero (remove NO_ZERO_IN_DATE,NO_ZERO_DATE) sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #===============================================master(Configure from library)=========================================================# # Node ID to ensure uniqueness server-id = 885 #Enable the binlog logging function of mysql log-bin = mysql-bin #The binlog of the control database is brushed to the disk. 0 is not controlled and the performance is the best. 1 is brushed to the log file every time the transaction is submitted. The performance is the worst and the safest sync_binlog = 1 #binlog log format, mysql adopts statement by default, and mixed is recommended binlog_format = mixed #binlog expiration cleanup time expire_logs_days = 30 #binlog size of each log file max_binlog_size = 1000m #binlog cache size binlog_cache_size = 4m #Maximum binlog cache size max_binlog_cache_size= 512m #Database to synchronize binlog-do-db = efb #For databases that do not generate log files, multiple ignored databases can be spliced with commas, or copy this sentence and write multiple lines binlog-ignore-db=mysql binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys # Offset from increment auto-increment-offset = 1 # Self increment auto-increment-increment = 1 #Skip from library error slave-skip-errors = all #===============================================slave(Configure from library)=========================================================# server-id = 890 #Must be unique log_bin = mysql-bin #Compared with datadir, these paths can be full path or no path. Open and set the binary log file name binlog_format = MIXED sync_binlog = 1 expire_logs_days =7 #The number of days that binary logs are automatically deleted / expired. The default value is 0, which means it will not be deleted automatically. log_slave_updates = 1 #[key point] updates received from the master server should be written to the binary log at the same time #binlog_cache_size = 128m #max_binlog_cache_size = 512m #max_binlog_size = 256M binlog-do-db = efb #Database to synchronize binlog-ignore-db = mysql #Databases that do not need to be synchronized binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys master_info_repository=TABLE relay_log_info_repository=TABLE read_only=1 #Read / write separation, configure read-only from the library, and avoid write operations from the library (only valid for ordinary users, not for super administrators) super_read_only=1 #mysql5.7 added a new parameter super_read_only, this parameter makes the super administrator unable to write. It is not recommended. If the master server goes down, salve cannot write data after switching to the master database. #===========================================Master slave configuration MASTER-SLAVE Description of key configuration parameters===========================================# log-bin: To set up master-slave replication, binary logs must be enabled server-id: mysql The unique identification in the same set of master-slave structures must be different for each server server-uuid: from mysql5.6 With this parameter, it is automatically generated during database startup. Each machine is different, uuid Store in auto.cnf In the file read only: Set the slave library read-only status to avoid writing on the slave library, but this instruction is invalid for super administrators, mysql5.7 Added a new parameter super_read_only,This parameter makes the super administrator unable to write. binlog_format: Binary log format, here must be row pattern log_slave_updates: The change information obtained from the master library is rewritten in the slave library binlog In the log. log_error_action: This parameter is used to control when cannot write binlog At the time of filing, mysql What will happen? The parameter is mysql5.7 Added later. have abort_server and ignore_error, abor_server Representative when mysql Writing binlog Exit when the disk is slow or the file system is unavailable; and ignore_error Is encountered binlog When unable to write, mysql Errors are logged in the error log and forced shut down binlog Function, which will affect the function of obtaining logs from the master database, resulting in inconsistent data between the master and slave databases, mysql5.7 Used by default after abort_server binlog-do-db: Use this parameter to selectively copy the database (set on the primary database), such as binlog-do-db=ceshi,It means only synchronization ceshi Other libraries are not replicated and synchronized. gtid_mode: decision gtid Whether the mode is on, if used gtid Copy mode, you must gtid_mode=on enforce-gtid-consistency: use gtid When in mode, this parameter should be turned on to ensure gtid Consistent row of enforce-gtid-consistency=on gtid_next: This parameter is session Level variable, next gtid. The default value is automatic gtid_purged: discarded gtid relay_log: Record from master library I/O dump thread come here binlog content replicate_do_table: Only the specified table is copied and used on the slave library replicate_ignore_table: Tables that need to be ignored in replication are set and used on the slave library replicate_ignore_db: Ignore the copied library and set the usage from the library replicate_do_db: The library to be copied only can be set and used from the library replicate_wild_do_table: Use wildcards to specify the tables to be copied, such as zs Kuxia tt If the table at the beginning is--replicate_wild_do_table=zz.tt% replicate_wild_ignore_table: Use wildcards to specify tables to ignore master_info_repository: hold master.info(Master slave status, configuration information)Record, default to file Inside, it is recommended to use the record form master_info_repository=table relay_log_recovery: In order for the slave library to be crash safe Must be set relay_log_recovery=1,The meaning of this parameter is: when the slave database crashes or restarts, it will delete the finished relay logs, retrieve the logs from the master database, and generate the recovery of relay logs again. It is recommended to start on the slave database relay_log_recovery Parameter. It is off by default relay_log_purge: Clear already executed relay log，It is recommended to open from the library slave_net_timeout: This parameter is to set the number of seconds not obtained from the main database binlog After that, the slave library is considered as timeout I/O thread The main library is reconnected, and the value is from mysql5.7.7 The start default is 60 s slave_parallel_type: This parameter is from msyql5.7.2 Introduced, there are two values, one is database，The other is logical_clock,stay mysql5.7 Then the parallel replication based on group submission is introduced by setting parameters slave_parallel_workers>0 also slave_parallel_type='LOGICAL_CLOCK'realization slave_parallel_workers: Set up multiple processes to execute concurrently relay log The maximum value of transactions committed by the master database in is 1024