Mysql: Handy commands when looking for any issues (mysqlbinlog)/diag report/mysql setting.

 

1) 

mysqlbinlog commands:

--- to start from specific time:

mysqlbinlog --start-datetime="2023-07-28 12:00:00" --base64-output=AUTO --verbose ctdayasd001-bin.000005 >/home/oracle/siva/asd_bin005.log

mysqlbinlog -d database_name --base64-output=AUTO --verbose mysqld-bin.000001 >/home/oracle/siva/test.log

--debug-check option will check for open file and memory use after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-check mysqld-bin.000001

--debug-info option will display additional debugging option after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-info mysqld-bin.000001

~

2) to run diag collect: like AWR for current time.

runs for 300 seconds(5 mins) for every 30 seconds to collect the details and report it.

mysql -u root -p -H -e"CALL sys.diagnostics(300, 30, 'current');" > /opt/mysql/current_Host_instance_report.html

3)

--- HTML report to check the mysql settings:

vi collect-mysql-det.sql

SELECT VERSION(), NOW(), @@time_zone, @@system_time_zone\G

/*!50503 SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH) AS DATA, SUM(INDEX_LENGTH) AS IDXS, ((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/(1024*1024)) AS TOTALMB

FROM information_schema.TABLES GROUP BY ENGINE*/;

select benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds

SHOW GLOBAL VARIABLES;

SELECT * FROM INFORMATION_SCHEMA.PLUGINS;

/*!80001 SELECT * FROM mysql.component*/;

-- part of an IC?

/*!50706 SELECT * FROM `performance_schema`.`replication_group_members`*/;

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

SELECT SLEEP(180);

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

STATUS;