MYSQL parameter definition including mysqld.conf and lookups including bin log, relay log, bin log log position

MYSQL parameter look up

https://dev.mysql.com/doc/refman/5.6/en/replication-options-replica.html

https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#max_relay_log_size

\G

    . It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’. 

https://pento.net/2009/02/27/the-g-modifier-in-the-mysql-command-line-client/#:~:text=A%20little%20publicized%2C%20but%20exceedingly,query%20with%20'%5CG'.

MYSQL conf

https://vitux.com/mysql-master-slave-replication-on-ubuntu/

located in /etc/mysql/mysql.conf.d/mysqld.conf

sample values:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log server-id = 1 log-bin = /var/log/mysql/mysql-bin.log tmpdir = /tmp binlog_format = ROW max_binlog_size = 800M sync_binlog = 1 expire-logs-days = 5 slow_query_log=1 slow_query_log_file=/var/lib/mysql/mysqld-slow.log

• server-id – Unique ID of the MySQL server. This ID can not be re-used in any nodes in the cluster.
• log-bin – This is the file in which all the replication information is stored.
• max_binlog_size – Size of the binlog file.

What is position in  mysql> SHOW MASTER STATUS\G

File: mysql-bin.00002

Position: 156

https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html

Each replica (Slave mysql db) keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the source. This means that multiple replicas can be connected to the source and executing different parts of the same binary log. Because the replicas control this process, individual replicas can be connected and disconnected from the server without affecting the source's operation. Also, because each replica records the current position within the binary log, it is possible for replicas to be disconnected, reconnect and then resume processing.

The source and each replica must be configured with a unique ID (using the server_id system variable). In addition, each replica must be configured with information about the source's host name, log file name, and position within that file

Key terms definition 

https://serverfault.com/questions/405726/max-binlog-size-log-bin-size 

   max_binlog_size = 1073741824

log-bin = mysql-bin
max_relay_log_size = 1G
relay_log_space_limit = 2G

bin_log 

is The binary log is a set of log files that contain information about data modifications

 made to a MySQL server instance. The log is enabled by starting the server with the --log-bin option.

https://dev.mysql.com/doc/internals/en/binary-log-overview.html

max_bing_log_size

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

If a write to the binary log causes the current log file size to exceed the value of this variable, 

the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.

Bin log file growing more than max_bin_log_size ?

https://dba.stackexchange.com/questions/172807/mysql-binary-log-file-size-growing-more-than-the-maximum-limit

A transaction is written in one chunk to the binary log,

 so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

max_relay_log_size

Slave retrieves master bin log and writes it in relay_log file. When maxium reached, it will create another file

If a write by a replica to its relay log causes the current log file size to exceed the value of this variable, the replica rotates the relay logs (closes the current file and opens the next one). 

If max_relay_log_size is 0, the server uses max_binlog_size for both the binary log and the relay log.

 If max_relay_log_size is greater than 0, it constrains the size of the relay log,

 which enables you to have different sizes for the two logs. You must set max_relay_log_size to between 4096 bytes and 1GB (inclusive), or to 0. The default value is 0. See Section 17.2.1, “Replication Threads”.

relay_log_space_limit

This option places an upper 

limit on the total size in bytes of all relay logs on the replica. 

A value of 0 means “no limit”. This is useful for a replica server host that has limited disk space. When the limit is reached, the replication I/O thread stops reading binary log events from the replication source server until the SQL thread has caught up and deleted some unused relay logs. 

How to choose a good value for MySQL relay-log-space-limit?

https://dba.stackexchange.com/questions/35118/how-to-choose-a-good-value-for-mysql-relay-log-space-limit