MySQL 主从复制时的配置解释

博主： zmx
发布时间：2021 年 08 月 31 日
621 次浏览
暂无评论
11157字数
分类： MySQL

[官方文档---系统变量](https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html)
<div class="tip inlineBlock info">

这里的信息均来源于官方文档以及网络博客中，还有一些是自己的理解，如有错误，敬请指出。谢谢。

</div>

# [mysqld]

> skip-slave-start

Tells the replica server not to start the replication threads when the server starts. To start the threads later, use a [`START SLAVE`](https://dev.mysql.com/doc/refman/5.7/en/start-slave.html "13.4.2.5 START SLAVE Statement") statement.

看样子应该是启动从服务器时不启动从服务器的复制线程吧

> skip-external-locking

This is `OFF` if [**mysqld**](https://dev.mysql.com/doc/refman/8.0/en/mysqld.html "4.3.1 mysqld — The MySQL Server") uses external locking (system locking), `ON` if external locking is disabled. This affects only [`MyISAM`](https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html "16.2 The MyISAM Storage Engine") table access.
看样子应该和单机和集群模式有关，并且还是多进程的。

> server-id

这个就不看官方文档了，相当于服务ID，要保证主从服务器的id不同，一般可以使用服务器ip的部分作为唯一标识

> binlog_format

二进制日志格式，一共有三种
STATEMENT，ROW，MIXED

`STATEMENT` 日志记录基于语句。

`ROW`日志记录基于行。这是默认设置。

`MIXED` 日志记录使用混合格式。
详见：[官方文档--binary-log-setting](https://dev.mysql.com/doc/refman/8.0/en/binary-log-setting.html)

> log_bin_trust_function_creators

This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that may cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the [`SUPER`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super) privilege in addition to the [`CREATE ROUTINE`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_create-routine) or [`ALTER ROUTINE`](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_alter-routine) privilege. A setting of 0 also enforces the restriction that a function must be declared with the `DETERMINISTIC` characteristic, or with the `READS SQL DATA` or `NO SQL` characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See [Section 25.7, “Stored Program Binary Logging”](https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html "25.7 Stored Program Binary Logging").

应该与创建存储过程和触发器有关，如果对数据有修改，并且不是基于行或者混合时就没有警告，如果基于语句的话，会有警告。

Specifies the number of days before automatic removal of binary log files. [`expire_logs_days`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_expire_logs_days) is deprecated, and you should expect it to be removed in a future release. Instead, use [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds), which sets the binary log expiration period in seconds. If you do not set a value for either system variable, the default expiration period is 30 days. Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in [Section 5.4, “MySQL Server Logs”](https://dev.mysql.com/doc/refman/8.0/en/server-logs.html "5.4 MySQL Server Logs").

Any non-zero value that you specify for [`expire_logs_days`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_expire_logs_days) is ignored if [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds) is also specified, and the value of [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds) is used instead as the binary log expiration period. A warning message is issued in this situation. A non-zero value for [`expire_logs_days`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_expire_logs_days) is only applied as the binary log expiration period if [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds) is not specified or is specified as 0.

> expire_logs_days

To disable automatic purging of the binary log, specify a value of 0 explicitly for [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds), and do not specify a value for [`expire_logs_days`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_expire_logs_days). For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly for [`expire_logs_days`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_expire_logs_days) and do not specify a value for [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds). In that case, the default for [`binlog_expire_logs_seconds`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds) is not applied.
二进制日志文件过期时间(天)

> relay-log

The base name for relay log files. For the default replication channel, the default base name for relay logs is `<em class="replaceable"><code>host_name</code></em>-relay-bin`. For non-default replication channels, the default base name for relay logs is `<em class="replaceable"><code>host_name</code></em>-relay-bin-<em class="replaceable"><code>channel</code></em>`, where *`channel`* is the name of the replication channel recorded in this relay log.

中转日志的位置和名称，如果值为空，则默认位置在数据文件的目录

> log_slave_updates

From MySQL 8.0.26, use log_replica_updates in place of log_slave_updates, which is deprecated from that release. In releases before MySQL 8.0.26, use log_slave_updates.

log_replica_updates specifies whether updates received by a replica server from a replication source server should be logged to the replica's own binary log.
从机在复制主机时，记录到从机的二进制日志

> gtid_mode

Controls whether GTID based logging is enabled and what type of transactions the logs can contain. You must have privileges sufficient to set global system variables. See [Section 5.1.9.1, “System Variable Privileges”](https://dev.mysql.com/doc/refman/8.0/en/system-variable-privileges.html "5.1.9.1 System Variable Privileges"). [`enforce_gtid_consistency`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency) must be set to `ON` before you can set [`gtid_mode=ON`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_mode). Before modifying this variable, see [Section 17.1.4, “Changing GTID Mode on Online Servers”](https://dev.mysql.com/doc/refman/8.0/en/replication-mode-change-online.html "17.1.4 Changing GTID Mode on Online Servers").

Logged transactions can be either anonymous or use GTIDs. Anonymous transactions rely on binary log file and position to identify specific transactions. GTID transactions have a unique identifier that is used to refer to transactions. The different modes are:

* `OFF`: Both new and replicated transactions must be anonymous.
* `OFF_PERMISSIVE`: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
* `ON_PERMISSIVE`: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
* `ON`: Both new and replicated transactions must be GTID transactions.

Changes from one value to another can only be one step at a time. For example, if [`gtid_mode`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_mode) is currently set to `OFF_PERMISSIVE`, it is possible to change to `OFF` or `ON_PERMISSIVE` but not to `ON`.

The values of [`gtid_purged`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_purged) and [`gtid_executed`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_executed) are persistent regardless of the value of [`gtid_mode`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_mode). Therefore even after changing the value of [`gtid_mode`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_mode), these variables contain the correct values.

控制是否启用基于GTID的日志记录

> enforce_gtid_consistency

Depending on the value of this variable, the server enforces GTID consistency by allowing execution of
only statements that can be safely logged using a GTID. You must set this variable to ON before enabling
GTID based replication.
The values that enforce_gtid_consistency can be configured to are:
• OFF: all transactions are allowed to violate GTID consistency.
• ON: no transaction is allowed to violate GTID consistency.
• WARN: all transactions are allowed to violate GTID consistency, but a warning is generated in this case.
强制GTID一致性

> slave-parallel-type

From MySQL 8.0.26, slave_parallel_type is deprecated and the alias replica_parallel_type
should be used instead. In releases before MySQL 8.0.26, use slave_parallel_type.
For multithreaded replicas (replicas on which replica_parallel_workers or
slave_parallel_workers is set to a value greater than 0), slave_parallel_type specifies the
policy used to decide which transactions are allowed to execute in parallel on the replica. The variable
has no effect on replicas for which multithreading is not enabled. The possible values are:
• LOGICAL_CLOCK: Transactions that are part of the same binary log group commit on a source
are applied in parallel on a replica. The dependencies between transactions are tracked based on
their timestamps to provide additional parallelization where possible. When this value is set, the
binlog_transaction_dependency_tracking system variable can be used on the source to
specify that write sets are used for parallelization in place of timestamps, if a write set is available for
the transaction and gives improved results compared to timestamps.
• DATABASE: Transactions that update different databases are applied in parallel. This value is only
appropriate if data is partitioned into multiple databases which are being updated independently and
concurrently on the source. There must be no cross-database constraints, as such constraints may be
violated on the replica.
When replica_preserve_commit_order=1 or slave_preserve_commit_order=1 is set, you
can only use LOGICAL_CLOCK.

用于决定允许哪些事务在副本上并行执行的策略，逻辑时钟：基于组提交的并行复制方式，DATABASE 为默认值，意为基于库的并行复制方式，不同库的事务，没有锁冲突，但是效率慢，因为粒度粗。而LOGICAL_CLOCK只要事务之间没有锁冲突就可以并发。

> relay_log_recovery

Whether automatic recovery of relay log files from source at startup is enabled;must be enabled for crash-safe replica.
是否启用了在启动时从源自动恢复中继日志文件;必须启用崩溃安全复制。

# [mysqldump]

> quick

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

该选项用于转储大的表。它强制**mysqldump** 从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

zunmx.fans
我 www.hao123.com实名申请友链OωO
zunmx.fans
作者小小骗人，我登陆了也看不到( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
zunmx.fans
please many update 算法 (´இ皿இ｀)boke
zunmx.fans
df1/df2 df1+df2 转置，累加----*。。。...
zunmx.fans
为何无人评论？OωO

MySQL 主从复制时的配置解释

zmx • 2021 年 08 月 31 日

[官方文档---系统变量](https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html)
<div class="tip inlineBlock info">

这里的信息均来源于官方文档以及网络博客中，还有一些是自己的理解，如有错误，敬请指出。谢谢。

</div>

# [mysqld]

> skip-slave-start

看样子应该是启动从服务器时不启动从服务器的复制线程吧

> skip-external-locking

> server-id

这个就不看官方文档了，相当于服务ID，要保证主从服务器的id不同，一般可以使用服务器ip的部分作为唯一标识

> binlog_format

二进制日志格式，一共有三种
STATEMENT，ROW，MIXED

`STATEMENT` 日志记录基于语句。

`ROW`日志记录基于行。这是默认设置。

`MIXED` 日志记录使用混合格式。
详见：[官方文档--binary-log-setting](https://dev.mysql.com/doc/refman/8.0/en/binary-log-setting.html)

> log_bin_trust_function_creators

应该与创建存储过程和触发器有关，如果对数据有修改，并且不是基于行或者混合时就没有警告，如果基于语句的话，会有警告。

> expire_logs_days

> relay-log

中转日志的位置和名称，如果值为空，则默认位置在数据文件的目录

> log_slave_updates

From MySQL 8.0.26, use log_replica_updates in place of log_slave_updates, which is deprecated from that release. In releases before MySQL 8.0.26, use log_slave_updates.

> gtid_mode

控制是否启用基于GTID的日志记录

> enforce_gtid_consistency

> slave-parallel-type

> relay_log_recovery

# [mysqldump]

> quick

该选项用于转储大的表。它强制**mysqldump** 从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

MySQL 主从复制时的配置解释

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

Typecho 插件开发 ZSecurity

个人空间使用规则[拟定] 23/3/9修订

企业微信-摸鱼必备，避免离开状态

基于Lively wallpaper的壁纸

中公教育CTS视频下载

typecho sitemap 生成脚本

Chrome 不检查跨域

蜜罐威胁IP订阅服务

Windows aria2 配置 HTTPS

CrackME-0x01 Acid burn

MySQL 主从复制时的配置解释

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。

MySQL 主从复制时的配置解释

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款了昂。