记录一波MySQL 通用日志的日常操作,general log
1.查看通用日志是否已经打开:
root@localhost((none)) 11:33:42>show variables like '%general%';
+------------------+----------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------+
| general_log | OFF |
| general_log_file | /apps/dbdat/mysql57_3306/localhost.log |
+------------------+----------------------------------------+
2 rows in set (0.01 sec)
2.打开通用日志:
set global general_log=1 \g #打开通用日志;
3.tail -f /apps/dbdat/mysql57_3306/localhost.log #查看通用日志记录
4.分别运行show databases,use mysql命令以及mysqldump -uroot -p -d cute --single-transaction --master-data=2 > /tmp/cute22.sql,
查看MySQLServer生成的general_log。
发现use mysql 执行了一堆操作。 位于以下show database 和root@localhost on using Socket之间
[root@localhost mysql57_3306]# tail -f /apps/dbdat/mysql57_3306/localhost.log
/apps/svr/mysql57/bin/mysqld, Version: 5.7.31-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql3306.sock
Time Id Command Argument
2021-04-10T10:56:57.848484+08:00 3 Query show databases
2021-04-10T10:57:02.362054+08:00 3 Query SELECT DATABASE()
2021-04-10T10:57:02.362273+08:00 3 Init DB mysql
2021-04-10T10:57:02.364397+08:00 3 Query show databases
2021-04-10T10:57:02.365008+08:00 3 Query show tables
2021-04-10T10:57:02.365979+08:00 3 Field List columns_priv
2021-04-10T10:57:02.366682+08:00 3 Field List db
2021-04-10T10:57:02.367552+08:00 3 Field List engine_cost
2021-04-10T10:57:02.368121+08:00 3 Field List event
2021-04-10T10:57:02.369214+08:00 3 Field List func
2021-04-10T10:57:02.369667+08:00 3 Field List general_log
2021-04-10T10:57:02.370396+08:00 3 Field List gtid_executed
2021-04-10T10:57:02.371156+08:00 3 Field List help_category
2021-04-10T10:57:02.469596+08:00 3 Field List help_keyword
2021-04-10T10:57:02.522812+08:00 3 Field List help_relation
2021-04-10T10:57:02.544344+08:00 3 Field List help_topic
2021-04-10T10:57:02.600780+08:00 3 Field List innodb_index_stats
2021-04-10T10:57:02.613251+08:00 3 Field List innodb_table_stats
2021-04-10T10:57:02.652055+08:00 3 Field List ndb_binlog_index
2021-04-10T10:57:02.685629+08:00 3 Field List plugin
2021-04-10T10:57:02.685957+08:00 3 Field List proc
2021-04-10T10:57:02.702357+08:00 3 Field List procs_priv
2021-04-10T10:57:02.703304+08:00 3 Field List proxies_priv
2021-04-10T10:57:02.704207+08:00 3 Field List server_cost
2021-04-10T10:57:02.704691+08:00 3 Field List servers
2021-04-10T10:57:02.705405+08:00 3 Field List slave_master_info
2021-04-10T10:57:02.707600+08:00 3 Field List slave_relay_log_info
2021-04-10T10:57:02.708343+08:00 3 Field List slave_worker_info
2021-04-10T10:57:02.709401+08:00 3 Field List slow_log
2021-04-10T10:57:02.734715+08:00 3 Field List tables_priv
2021-04-10T10:57:02.735628+08:00 3 Field List time_zone
2021-04-10T10:57:02.736291+08:00 3 Field List time_zone_leap_second
2021-04-10T10:57:02.736975+08:00 3 Field List time_zone_name
2021-04-10T10:57:02.737502+08:00 3 Field List time_zone_transition
2021-04-10T10:57:02.738421+08:00 3 Field List time_zone_transition_type
2021-04-10T10:57:02.739453+08:00 3 Field List user
2021-04-10T10:58:16.201913+08:00 4 Connect root@localhost on using Socket
2021-04-10T10:58:16.202232+08:00 4 Query /*!40100 SET @@SQL_MODE='' */
2021-04-10T10:58:16.202452+08:00 4 Query /*!40103 SET TIME_ZONE='+00:00' */
2021-04-10T10:58:16.202792+08:00 4 Query FLUSH /*!40101 LOCAL */ TABLES
2021-04-10T10:58:16.209100+08:00 4 Query FLUSH TABLES WITH READ LOCK
2021-04-10T10:58:16.209327+08:00 4 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-04-10T10:58:16.209482+08:00 4 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-04-10T10:58:16.209911+08:00 4 Query SHOW VARIABLES LIKE 'gtid\_mode'
2021-04-10T10:58:16.216581+08:00 4 Query SELECT @@GLOBAL.GTID_EXECUTED
2021-04-10T10:58:16.217002+08:00 4 Query SHOW MASTER STATUS
2021-04-10T10:58:16.217307+08:00 4 Query UNLOCK TABLES
2021-04-10T10:58:16.217835+08:00 4 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('cute'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2021-04-10T10:58:16.368554+08:00 4 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('cute')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2021-04-10T10:58:16.370292+08:00 4 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2021-04-10T10:58:16.375599+08:00 4 Init DB cute
2021-04-10T10:58:16.376001+08:00 4 Query SAVEPOINT sp
2021-04-10T10:58:16.376318+08:00 4 Query show tables
2021-04-10T10:58:16.376822+08:00 4 Query show table status like 'myts'
2021-04-10T10:58:16.377459+08:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-04-10T10:58:16.377760+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.378144+08:00 4 Query show create table `myts`
2021-04-10T10:58:16.378526+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.378901+08:00 4 Query show fields from `myts`
2021-04-10T10:58:16.379840+08:00 4 Query show fields from `myts`
2021-04-10T10:58:16.380786+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.381041+08:00 4 Query use `cute`
2021-04-10T10:58:16.381154+08:00 4 Query select @@collation_database
2021-04-10T10:58:16.381333+08:00 4 Query SHOW TRIGGERS LIKE 'myts'
2021-04-10T10:58:16.382211+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.382874+08:00 4 Query ROLLBACK TO SAVEPOINT sp
2021-04-10T10:58:16.383404+08:00 4 Query show table status like 'myts\_file\_per\_table'
2021-04-10T10:58:16.383939+08:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-04-10T10:58:16.384072+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.384488+08:00 4 Query show create table `myts_file_per_table`
2021-04-10T10:58:16.384843+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.385105+08:00 4 Query show fields from `myts_file_per_table`
2021-04-10T10:58:16.386006+08:00 4 Query show fields from `myts_file_per_table`
2021-04-10T10:58:16.386859+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.387160+08:00 4 Query use `cute`
2021-04-10T10:58:16.387444+08:00 4 Query select @@collation_database
2021-04-10T10:58:16.387787+08:00 4 Query SHOW TRIGGERS LIKE 'myts\_file\_per\_table'
2021-04-10T10:58:16.388613+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.388848+08:00 4 Query ROLLBACK TO SAVEPOINT sp
2021-04-10T10:58:16.389080+08:00 4 Query show table status like 'myts\_system'
2021-04-10T10:58:16.389503+08:00 4 Query SET SQL_QUOTE_SHOW_CREATE=1
2021-04-10T10:58:16.389772+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.389918+08:00 4 Query show create table `myts_system`
2021-04-10T10:58:16.390119+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.390267+08:00 4 Query show fields from `myts_system`
2021-04-10T10:58:16.391160+08:00 4 Query show fields from `myts_system`
2021-04-10T10:58:16.391860+08:00 4 Query SET SESSION character_set_results = 'binary'
2021-04-10T10:58:16.392005+08:00 4 Query use `cute`
2021-04-10T10:58:16.392141+08:00 4 Query select @@collation_database
2021-04-10T10:58:16.392296+08:00 4 Query SHOW TRIGGERS LIKE 'myts\_system'
2021-04-10T10:58:16.392986+08:00 4 Query SET SESSION character_set_results = 'utf8'
2021-04-10T10:58:16.393133+08:00 4 Query ROLLBACK TO SAVEPOINT sp
2021-04-10T10:58:16.393245+08:00 4 Query RELEASE SAVEPOINT sp
2021-04-10T10:58:16.397071+08:00 4 Quit
5.由此,可以用此法测试,查看某些DBA命令的执行原理;
==========general_log 表级别存储=========
1.上述通用日志里看到mysql数据库里有个general_log表;
2.查看是否设置为表级别,以及设置日志为表级别存储;
mysql> show variables like '%output%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| log_output | FILE |
+----------------------------+-------+
3 rows in set (0.01 sec)
mysql> set global log_output='TABLE' ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%output%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| log_output | TABLE |
+----------------------------+-------+
3 rows in set (0.11 sec)
3.查看general_log表是否存储通用日志;
mysql> select * from mysql.general_log \g
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2021-04-09 23:46:19.175795 | root[root] @ localhost [] | 7 | 20033806 | Query | show variables like '%output%' |
| 2021-04-09 23:46:38.028362 | root[root] @ localhost [] | 7 | 20033806 | Query | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)
4.关闭通用日志;
set global general_log=0;
通用日志特点,不记录二进制日志里的行级别更新操作,通用日志以语句为单位进行记录;
可用于短暂开启以收集这段时间的操作记录;
=========End,帅的靓的都喜欢点赞后关闭~~~~===========