MySQL Copy parameters explained

MySQL Copy parameters explained

1. Parameters on Master

Log-bin #必须

Server-id #1-pow (2,32)-1#必须 (Port +ip)

Server-uuid #路径为 $datadir/auto.cnf

Log-bin-index#必须

Binlog-format

Binlog_cache_size

Max_binlog_size

Sync_binlog

Expire_logs_days

Log_bin_trust_function_creators

2, configuration parameters (Ignore/do rules of the Kang)

Log-bin

Binlog filename prefix, can be full path

Log-bin=mybinlog

Log-bin=/data/mysql/mysql3306/mybinlog

Cannot be modified dynamically

Server-id

Unique distinguished ID, non-repeatable within the same cluster

Can be dynamically modified starting from mysql5.6

Log-bin-index

Binlog index file prefix name, same as Log-bin, can also be full path

Cannot be modified dynamically

Binlog-format

Binlog log format: statement|row|mixed three kinds

Can be modified dynamically

Binlog_cache_size recommended 1M to 2M

Binlog Write Buffer

Parameters can be modified dynamically

Max_binlog_size default is 1G recommended 128M or 256M

Limit the size of a single binlog

Can be modified dynamically

Sync_binlog=n

#交易系统为了安全 sync_binlog=1

After how many SQL, call the Fdatasync function to flush binlog to disk

What is the difference between fsync ()/fdatasync?

Fsync is completely sad to disk, Fdatasync only refreshes data, does not refresh metadata

Can be modified dynamically

Expire_logs_days=n Recommended Save 7 days

Automatically delete binlog after n days

Can be modified dynamically

Log_bin_trust_function_creators=n 1 is normally the case.

Whether to allow the creation of stored procedures when Binlog is turned on

Can be modified dynamically

3. Special Instructions:

Log_warnings=n Best for 1

Otherwise, you will see an abnormally disconnected connection in the error log (aborted connection)

MySQL5.6.5 MySQL5.7.5 after binlog v2 format GTID binlog_checksum

Migration error 5.6, 5.5 5.5, 5.6

Binlog_checksum=none

Log_bin_use_v1_row_events=on

Appears after binlog_row_image5.6.2

Default to Full

Binlog_rows_query_log_events

Binlog_gtid_recovery_simpliefied

Crash Recovery

The crash recovery based on Mysql5.6gtid is mysql-5.6.21 supported to 5.6.23 Stable

Traditional replication can also be supported.

[Email protected] [Test]>show Global variables like "%repo%";

+---------------------------+-------+

| variable_name | Value |

+---------------------------+-------+

| Master_info_repository | FILE |

| Relay_log_info_repository | FILE |

FILE------>table

Friendly error message settings

binlog_error_action | Abort_server

Binlogging_impossiable_mode----->binlog_error_action

[Email protected] [Test]>show Global variables like "%binlog%";

+-----------------------------------------+----------------------+

| variable_name | Value |

+-----------------------------------------+----------------------+

| Binlog_cache_size | 1048576 |

| Binlog_checksum | CRC32 |

| Binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | Abort_server |

| Binlog_format | ROW |

| Binlog_group_commit_sync_delay | 0 |

| Binlog_group_commit_sync_no_delay_count | 0 |

| Binlog_gtid_simple_recovery | On |

| Binlog_max_flush_queue_time | 0 |

| Binlog_order_commits | On |

| Binlog_row_image | Full |

| binlog_rows_query_log_events | OFF |

| Binlog_stmt_cache_size | 32768 |

| Innodb_api_enable_binlog | OFF |

| Innodb_locks_unsafe_for_binlog | OFF |

| Log_statements_unsafe_for_binlog | On |

| Max_binlog_cache_size | 1048576 |

| Max_binlog_size | 268435456 |

| Max_binlog_stmt_cache_size | 18446744073709547520 |

| Sync_binlog | 0 |

+-----------------------------------------+----------------------+

Rows in Set (0.00 sec)

4, Master other parameters

Auto_increment_increment

Auto_increment_offset

Bin-do-db

Bin-ignore-db

Max_binlog_cache_size

Binlog_stmt_cache_size

Max_binlog_stmt_cache_size

Biglog_direct_non_transactional_updates

5, the specific configuration parameters of the Kang:

Auto_increment_increment: increments per increment

Auto_increment_offset: The offset from the increment start PXC

BIN-DO-DB: Record db, other DB Binlog are not recorded, there is a risk not recommended setting

Configuration case:

Bin-do-db=mydb1

Bin-do-db=mydb2

Bin-ignore-db

Bin-ignore-db=test indicates that the Binlog record of the test library is not recorded.

Recommendation: All filtering rules are not recommended to be set in the main library (BIN-DO-DB, bin-ignore-db)

Max_binlog_cache_size default on the line

Biglog_direct_non_transactional_updates is not moving by default

6, Gtid related parameters

Show global variables like "%gtid%";

Current Database:test

+----------------------------------+------------------------------------------+

| variable_name | Value |

+----------------------------------+------------------------------------------+

| Binlog_gtid_simple_recovery | On |

| enforce_gtid_consistency | On |

| gtid_executed | 6e5d664c-c7e5-11e6-88b1-000c296ae445:1-9 |

| Gtid_executed_compression_period | 1000 |

| Gtid_mode | On |

|                                          gtid_owned | |

|                                          gtid_purged | |

| Session_track_gtids | OFF |

+----------------------------------+------------------------------------------+

If Gtid is turned on (to control only transaction-safe statements to be recorded in log)

The Gtid_mode is used to control whether Gtid is enabled

If set to ON, it must be Log-bin, Log-salve-updates,enforce_gtid_consistency also enable

Gtid_purged: used to specify that the transaction was ignored from Binlog. Reset Master This value will be emptied.

gtid_executed: executed to the Gtid number and all executed Gtid number. and (show Master status,show slave status) output value executed Gtid set meaning.

Rest Master: This value will be emptied.

Summary: The main library opens with minimal parameters

Server-id

Server-uuid

Log-bin

Gtid_mode

Enforce_gtid_consistency

Max_binlog_size

Expire_logs_days

Binlog-format

############################################################################################################### ##############################

7, the parameters on the slave

Server-id

Server-uuid

Relay-log #io thread Read and save to local file.

Relay-log-index

Read-only #从库只读, but does not work for users of super permissions This setting is not very meaningful, generally do not set.

8, the specific configuration parameters of the Kang:

Server-id:

Relay-log:relaylog filename prefix, which can be a full path and cannot be changed dynamically

The relay-log-index:relaylog index file prefix name is the same as Relay-log, which can be full-path and cannot be changed dynamically.

Read-only: You cannot modify the data unless you have super privileges, but you can modify it dynamically without affecting the autonomic creation of temporary tables (temporary table, analyze Table,optimize table).

9. Salve other parameters

Log-slow-salve-statements

Log_slave_updates #建议这个参数无论如何都要设置. Role: The main library is updated and logs are logged from the library.

Max_relay_log_size

Relay_log_info_file

Relay_log_purge

Relay_log_recovery

Replicate-same-server-id #不建议使用

Skip-slave-start=1 #不要自动同步 preferably set to 1

Slave_load_tmpdir

Slave_transaction_retries #默认就行, retry

Slave_parallel_workers #默认是关闭的

#例子

Log_slave_updates is open.

[Email protected] [Wu]>show Global variables like "%log%"

[Email protected] [Test]>show Master status;

/usr/local/mysql/bin/mysqlbinlog-v--base64-output=decode-rows/data/mysql/mysql3306/logs/mysql-bin.000002 >1. Log

Relay_log_space_limit is not set by default. There is a problem before setting

The following parameters are not recommended for setup:

Show global variables like "%report%";

|       Report_host | |

|       Report_password | |

| Report_port | 3306 |

| Report_user

Whether the slow log generated by the Log-slow-slave-statements:slave copy needs to be recorded;

Max_relay_log_size Recommended 128M

The master_info_repository relay_log_info_repository parameter is set as follows:

Master-info-file/relay-log-info-file/relay-log-info

----->master_info_repository Relay_log_info_repository

[Email protected] [Wu]>show Global variables like "%info%";

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection id:17

Current DATABASE:WU

+--------------------------------+----------------+

| variable_name | Value |

+--------------------------------+----------------+

| Master_info_repository | FILE |

| Relay_log_info_file | Relay-log.info |

| Relay_log_info_repository | FILE |

| Session_track_transaction_info | OFF |

| Sync_master_info | 10000 |

| Sync_relay_log_info | 10000 |

+--------------------------------+----------------+

6 rows in Set (0.00 sec)

Development trend file will be replaced with table

Sync_master_info=n: Do not move normally unless the security requirements are very well paid set.

Relay_log_purge:relay application finished. Do I need to delete it automatically now?

Relay_log_recovery:salve crashes or starts normally, the unused relay log is deleted, re-requested from master Binlog, relay log is generated again

10, slave filter parameters

In general, the filtering rules are set from the library. Do not set in the main library.

Replicate-do-table

Replicate-ignore-table

Replicate-rewrite-db=DB1->DB2

Replicate-wild-do-table

Replicate-wild-ignore-table

Replicate-do-db

Replicate-ignore-db

Slave_net_timeout

Slave_skip_errors

Sql_slave_skip_count

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1893617

MySQL Copy parameters explained