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>