Log-bin binary Log
Server-id early version must be added 1-pow (2,32)-1 recommended port number +IP Last 5.6 can be modified dynamically
Server-uuid (after 5.6) The default exists $datadir/AUTO.CNF can be modified dynamically after 5.6
Log-bin-index contains the binary log file name
Binlog-format statement row mixed three kinds of dynamic modification
Binlog_cache_size=1m-2m
Max_binlog_size default is 1G generation interval of more than 2 minutes recommended 128M or 256M
Restricting the size of a single binlog can be modified dynamically
Sync_binlog=n
Binlog after synchronization, commit whether to brush to disk on the default higher performance set to 0 by the operating system control whether to brush to disk
If it is an education system, set it to 1 for safety recommendations
After how many SQL, call the Fdatasync () function to flush Binlog to disk
Fsync ()/Fdatasync () What's the difference?
Fsync () is fully flushed to disk, Fdatasync only refresh data is not refreshed metadata can be dynamically modified
Expire_logs_days=n Recommended Save 7 days
Automatically delete binlog after n days
Can be modified dynamically
Log_bin_trust_function_creators
Whether to allow the creation of a stored program when Binlog is turned on (unless you have super permissions, or you specify Deterministic,reads SQL Data,nosql)
can be dynamically modified
Log_warnings
If this value is set greater than 1, an abnormally disconnected connection (aborted connection) or a rejected request is seen in the error log
Special Instructions
After mysql5.6.5,mysql5.7.5, the BINLOGV2 format Gtid binlog_checksum
mysql5.5--> MySQL5.6 no problem.
If later have MySQL5.6--------> MySQL5.5 synchronization needs to find that synchronization is not an error,
Need to find two parameters
Binlog_checksum defaults to CRC32 because 5.5 does not have this parameter so it needs to be set to none
Log_bin_use_v1_row_events default to OFF set to on
This avoids some of the issues during the upgrade process (it is recommended that you restore the parameters back to the original settings)
Binlog_error_action when cannot write Binlog, can report out wrong, default does not open: Ignore_error
Open to use: Abort_server
5.6 Introduction of Gtid
Bin_log_image three modes full minimal noblob default to full
Binlog_rows_query_log_events Audit function
The crash recovery based on MySQL5.6 Gtid is there Mysql-5.6.21 support to 5.6.23 Stable
Traditional replication can also support
Master_info_repository |file| Modify to Table
Relay_log_info_repository |file| Modify to Table
Binlog_gtid_recovery_simpliefied "5.6.23--5.6.21 simplified_binlog_gtid_recovery" This feature just came out soon
When you hang up, you can find yourself synced to the sync Gtid, then sync, pull the log again.
If this feature is not enabled, it will be searched from Binlog, if the log is many, it takes more time.
Relay-master-log-file
Exec-master-log-pos
Master Other Parameters
Auto_increment_increment Offset
Auto_increment_offset Initial value
Special Disclaimer: All filter rules are not recommended in the main library settings
Binlog-do-db
Binlog record DB, except in addition, Binlog on other db are not recorded, there is a risk
Binlog-ignore-db
Contrary to the above rules
Max_binlog_cache_size
Binlog largest cachesize with large SQL write-in required, or big data load
Binlog_stmt_cache_size
The SQL statement that is used to cache those things that are non-object tables. If a non-object table is often involved in a thing, you need to increase the parameter
Max_binlog_stmt_cache_size
The SQL statement generated by the non-transaction table for the cache is the maximum cache
Binlog_direct_non_transactional_updates
In the case of a transaction table and a non-transactional table, when data changes occur, the non-object table writes the Binlog directly, not the same as the transaction table in buffer
This parameter only affects the SBR mode RBR MBR does not affect
Gtid Related parameters
Gtid_mode on open GITD parameters
Enforce_gtid_consistency
Gtid_next skipping the wrong arguments
Gtid_purged
To specify that the transaction has been ignored from Binlog, reset master will be emptied
Gtid_executed executed to the Gtid number and all executed Gtid number
And the Executed_gtid_set (show Master status,show slave status) Output This column means the same
Reset Master This value will be emptied
Summarize:
Main Library Open minimum parameter (required parameter)
Server-id
Server-uuid
Log-bin
Gtid-mode
Enforce-gtid-consistency
Other parameters
Max_binlog_size
Expire_logs_days
Binlog_format (row format)
Parameters on the Salve
Server-id
Server-uuid
Relay-log IO thread reads a log that is stored locally
Relay-log-index
Read-only settings are read-only from the library but do not work with super permissions
Configuration parameters (Ignore/do rules)
Server-id
Relay-log
Relaylog filename prefix can be a full path
Cannot be modified dynamically
Relay-log-index
The relay-log index file prefix name and Relay-log can also be fullpath
Cannot be modified dynamically
Read-only
Data cannot be modified unless you have super privileges, but it does not affect autonomic creation of temporary tables (temporary,table) Analyze table optimize table is unaffected
can be dynamically modified
Slave Other parameters
log-slow-slave-statements slave copy produced by slow log is also to be recorded
Log-slave-updates the change operation that the master transmits, records the binlog of the cost again, for two copies, as a trunk distribution point.
Max_relay_log_size recommended not to modify the size of the relay log
Relay-log-info-file
Relay-log-purge =1 After the application has finished removing the application is now deleted
Relay-log-recovery = 1 crash recover process slave crash or normal restart, unused relay log will be deleted and re-generated Binlog log from master request relay
Replicate-same-server-id
Skip-slave-start do not automatically enable synchronization after the database is up
Slave_load_tmpdir
Slave_transaction_retries
Start_parallel_workers default is off by 5.6 introducing parallel assignments based on the Gtid library level
Master-info-file/relay-log-info-file will slowly replace the following two
Master_info_repository file---table
Relay_log_info_repository File---> table
Record Relay-log Latest information
Sync_master_info =n Keep the default
How many things/events after calling Fdatasync () to refresh the Master.info file
Relay_log_purge Relay
Filter parameters of Slave
Replicate-do-table
Replicate-ignore-table
Replicate-rewrite-db the operation of DB1 to DB2.
Replicate-wild-do-table wildcards to define which tables are replicated which tables are not replicated
Replicate-wild-ignore-table
Replicate-do-db
Replicate-ignore-db
Slave_net_timeout
Slave_skip_errors
Sql_slave_skip_counter
, filter conditions can be changed online after 5.7.3
Slave_net_timeout if the master-slave copy thread is yes but the data is too far, the recommended 20-30 default is 60.
Slave_skip_errors
Some errors are ignored during replication
1032 No records found
1053 means that the server is in the process of shutting down
1062 Repeating primary key
1050 tables already exist.
1051 table not found
1054 columns are not right.
1146 table does not exist
Sql_slave_skip_counter
Ignoring the number of replication events, encountering individual errors (primary key violations, records not present, and so on), can ignore these events to continue the replication process
Based on Binlog+position error handling
Stop slave
Set Global Sql_slave_skip_counter=n
Start slave
show slave status\g;
One move only ignores one event, unless you are sure, do not set greater than 1
In order to use the Crash-safe-replication feature, you must use
Relay-log-info-repository =table
Relay-log-recovery
Using an engine that supports transactions
Use Crash-safe replication function: After MySQL (slave) failure hangs up
When the Relay-log-recovery=1 is set, it cleans up the existing relay log read from the relay-log-info-repository.
Sync location, re-request a copy of Relay-log actually did it once: set Read_master_log_pos to Exec_mater_log_pos,
Master_log_file is set to Relay_master_log_file.
Note: On slave If a log is given an incorrect binlog position start slave is doing nothing
MySQL replication parameters in a detailed