Enable and disable the Binary Logs in MySQL for incomplete recovery

Enable and disable the Binary Logs in MySQL for incomplete recovery

Note: For change this effect, we need to restart the MySQL.

Enable the Binary Log
Note: By default it is activated.
1. Check the parameter “log_bin” set in ini configuration file.

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
log_bin="KKK"
#expire_logs_days = 2 -- used for expire the logs

2. Check the Status of Binary Logs.

mysql> show variables like '%log_bin%';

+---------------------------------+------------------------------------------------------+
| Variable_name                   | Value         |
+---------------------------------+------------------------------------------------------+
| log_bin                         | ON         |
| log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\KKK       |
| log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\KKK.index |
| log_bin_trust_function_creators | OFF         |
| log_bin_use_v1_row_events       | OFF         |
| sql_log_bin                     | ON         |
+---------------------------------+------------------------------------------------------+

Disable the Binary Logs
1. Change the ini.my file configuration under heading “Group Replication Relate” comment all other log parameter and add “skip-log-bin” parameter.

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
#log_bin="KKK"
#expire_logs_days = 2 -- used for expire the logs
skip-log-bin

2. Check the Status of it.

mysql> show variables like '%log_bin%';

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

Change location by specifying the location in this parameter

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
#log_bin="C:\programdata\archive\KKK"
#expire_logs_days = 2 -- used for expire the logs
skip-log-bin

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.