MySQL replication parameters in a detailed

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