常用数据库状态查询语句

数据库全局状态

SHOW GLOBAL status

1. 查看binglog相关变量

SHOW VARIABLES LIKE '%log_bin%'

Variable_name	Value
log_bin	ON
log_bin_basename	/usr/local/mysql/data/mysql-bin
log_bin_index	/usr/local/mysql/data/mysql-bin.index
log_bin_trust_function_creators	OFF
log_bin_use_v1_row_events	OFF
sql_log_bin	ON

SHOW VARIABLES LIKE '%binlog%'

Variable_name	Value	comment
binlog_cache_size	32768
binlog_checksum	NONE
binlog_direct_non_transactional_updates	OFF
binlog_format	ROW
binlog_max_flush_queue_time	0
binlog_order_commits	ON
binlog_row_image	FULL
binlog_rows_query_log_events	OFF
binlog_stmt_cache_size	32768
innodb_api_enable_binlog	OFF
innodb_locks_unsafe_for_binlog	OFF
max_binlog_cache_size	18446744073709547520
max_binlog_size	1073741824	单个binlog文件的最大值
max_binlog_stmt_cache_size	18446744073709547520
sync_binlog	0

2. 查看binlog状态

SHOW MASTER STATUS 

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000006	7090901

3. 查看binlog内容

SHOW BINLOG EVENTS IN 'mysql-bin.000006'

Log_name	Pos	Event_type	Server_id	End_log_pos	Info
mysql-bin.000006	7090300	Delete_rows	2	7090431	table_id: 145 flags: STMT_END_F
mysql-bin.000006	7090431	Xid	2	7090458	COMMIT /* xid=715784 */
mysql-bin.000006	7090458	Query	2	7090529	BEGIN
mysql-bin.000006	7090529	Table_map	2	7090633	table_id: 162 (onekick.onekick_user_info)
mysql-bin.000006	7090633	Delete_rows	2	7090874	table_id: 162 flags: STMT_END_F
mysql-bin.000006	7090874	Xid	2	7090901	COMMIT /* xid=716165 */

4. 查看binlog所在目录

SHOW VARIABLES LIKE 'datadir'

Variable_name	Value
datadir	/usr/local/mysql/data/