• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MySQL/Type of Logs in MySQL
Popular Search:Oracle, SQL Server, MongoDB

Type of Logs in MySQL

108 views 0 March 10, 2024 sandeep

How many types of logs are in MySQL?

1. Error Log

2. General Log

3. Binary Log

==> Error Log

mysql> select @@log_error;
+---------------------------+
| @@log_error |
+---------------------------+
| /u01/mysql/logs/error.log |
+---------------------------+
1 row in set (0.00 sec)

mysql>

– Entry in the my.cnf file.

log_error=/u01/mysql/logs/error.log
log_error_verbosity=1 (used for detailed capture of errors)

==> General Log: General log about what mysqld is doing. Records everything including connect/ disconnet.
By default it is off

general_log
general_log_file

select event_time, user_host, convert(argument using utf8) from mysql.general_log;

mysql> show global variables like '%general%';
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | /u01/mysql/data/Server.log |
+------------------+--------------------------------+
2 rows in set (0.01 sec)

mysql>
mysql> set global general_log_file='/u01/mysql/logs/general/gen.log';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like '%general%';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log | ON |
| general_log_file | /u01/mysql/logs/general/gen.log |
+------------------+---------------------------------+
2 rows in set (0.01 sec)

mysql>

==> Binary log: Records all the database changes, used in Replication and Point-in-time-recovery

mysql> select @@log_bin ;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 501 | No |
| binlog.000002 | 180 | No |
| binlog.000003 | 180 | No |
| binlog.000004 | 660 | No |
| binlog.000005 | 180 | No |
| binlog.000006 | 157 | No |
+---------------+-----------+-----------+
6 rows in set (0.01 sec)

mysql>
mysql> flush binary logs; -- switch to new bin log.

FLUSH LOGS (it will flush all the below)
-FLUSH BINARY LOGS
-FLUSH SLOW LOGS
-FLUSH GENERAL LOGS
-FLUSH ERROR LOGS

To change the name of the bin log put entry in my.cnf file as => log_bin=tt
To disable the bin log put entry in my.cnf file as => skip-log-bin

* binlog_format
- ROW
- STATEMENT
- MIXED

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> set global binlog_format=statement ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.01 sec)

mysql>
mysql> show global variables like '%expire%';
+--------------------------------+---------+
| Variable_name | Value |
+--------------------------------+---------+
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 2592000 |
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 |
+--------------------------------+---------+
4 rows in set (0.07 sec)

mysql>

==> Read the bin log:
mysqlbinlog --base64-output=decode-rows -vv binlog.000012 | less

* binlog_row_image
– FULL : bin log capture all details
– MINIMAL : bin log capture only few details
– NOBLOB :

mysql> show global variables like '%image%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
1 row in set (0.01 sec)

mysql>
mysql> set global binlog_row_image=minimal ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show global variables like '%image%';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| binlog_row_image | MINIMAL |
+------------------+---------+
1 row in set (0.01 sec)

mysql>
Tags:MySQL

Was this helpful?

Yes  No
Related Articles
  • Configure Master Slave Replication in MySQL
  • Purge Old Logs in MySQL
  • Install and Remove Plugin into MySQL
  • MySQL user Management
  • Start and Stop MySQL using the systemctl
  • Install MySQL in Linux / Ubuntu
Leave A Comment Cancel reply

MySQL
  • Type of Logs in MySQL
  • Configure Master Slave Replication in MySQL
  • Purge Old Logs in MySQL
  • Install and Remove Plugin into MySQL
  • MySQL user Management
  • Start and Stop MySQL using the systemctl
View All 6  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org