MySQL : How to check ,enable & disable binary logging in mysql.
MySQL : How to Check MYSQL 8.0 binary logging in Linux system, disable and enable it
****************************************************************************
The binary log contains “events” that describe database changes such as table creation operations or changes to table data.
It also contains events for statements that potentially could have made changes.
For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded
after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
mysql -u root -p
Mysql@1234
mysql SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 2173 | No |
| binlog.000002 | 4850 | No |
| binlog.000003 | 2232 | No |
| binlog.000004 | 155 | No |
| binlog.000005 | 155 | No |
| binlog.000006 | 155 | No |
| binlog.000007 | 178 | No |
| binlog.000008 | 6283 | No |
| binlog.000009 | 178 | No |
| binlog.000010 | 155 | No |
+---------------+-----------+-----------+
10 rows in set (0.02 sec)
mysql show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)
show variables like 'max_binlog_size';
Step 1 ) Disable binary logging in mysql 8
stop MYSQL server
service mysqld status
service mysqld stop
cp /etc/my.cnf /etc/my.cnf_bkp_23April2020
vi /etc/my.cnf
and uncomment disable_log_bin
save my.cnf
start mysqld service
service mysqld start
Check logging status
mysql -u root -p
Mysql@1234
show variables like '%log_bin%';
Step 2 ) How to enable and provide a non default location to bin-log
service mysqld stop
cp /etc/my.cnf /etc/my.cnf_bkp_1
vi /etc/my.cnf
and add
log-bin=/mysql/logs/mysql-bin
show variables like '%log_bin%';
Step 3 ) In case you want to purge binary logs
PURGE BINARY LOGS