When you have an incident of database crash or any issue we may need to collect detailed log for further investigation. Will be good to have these details
You can run from the unix host. This command has been tested and confirmed on Centos Linux. Make sure you are able to login to mysql instance using “mysql -P 3306” in case it is not working you have to change the command accordingly
Set filename
outfile=”CrashLog-$(hostname)-$(date +%Y%m%d)-globvar-info.out”
date>>$outfile
echo>>$outfile
Global Variables
echo “Global Variables:”>>$outfile
mysql -P 3306 -ABNe “show global variables;” | sort | sed ‘s/; /;~\&/g’ | tr -s ‘~’ ‘\n’ | tr -s ‘\&’ ‘\t’ >>$outfile
Global Status x2 60s apart
echo >>$outfile
echo “Global Status x2:”>>$outfile
mysql -P 3306 -ABNe “show global status; select sleep(60); show global status;” | sort >>$outfile
Replication Status
echo >>$outfile
echo “Replication Status:”>>$outfile
mysql -P 3306 -ABNe “show master status;” >>$outfile
echo >>$outfile
mysql -Ae “show all slaves status\G” >>$outfile
Plugins
echo >>$outfile
echo “Plugins:”>>$outfile
mysql -P 3306 -ABNe “show plugins;”>>$outfile
Tables with no primary key
echo >>$outfile
echo “Tables with no Primary Key:”>>$outfile
mysql -P 3306 -ABNe “select t.table_schema, t.table_name from information_schema.tables as t left join information_schema.key_column_usage as c \
on ( t.table_name = c.table_name and c.constraint_schema = t.table_schema and c.constraint_name = ‘PRIMARY’ ) \
where t.table_schema not in (‘information_schema’, ‘performance_schema’, ‘mysql’) and t.table_type <> ‘VIEW’ and c.constraint_name IS NULL \
order by t.table_schema, t.table_name;” >>$outfile
Data size
echo >>$outfile
echo “Dataset Size:”>>$outfile
mysql -P 3306 -ABNe “select ifnull(B.engine,’Total’) \”Storage Engine\”, concat(lpad(format( \
B.DSize/power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Data Size\”, \
concat(lpad(format(B.ISize/power(1024,pw),3),17,’ ‘),’ ‘, \
substr(‘ KMGTP’,pw+1,1),’B’) \”Index Size\”,concat(lpad(format(B.TSize/ \
power(1024,pw),3),17,’ ‘),’ ‘,substr(‘ KMGTP’,pw+1,1),’B’) \”Table Size\” \
from (select engine,sum(data_length) DSize, \
sum(index_length) ISize,SUM(data_length+index_length) TSize from information_schema.tables \
where table_schema not in (‘mysql’,’information_schema’,’performance_schema’) AND \
engine is not null group by engine with rollup) B,(SELECT 2 pw) A order by TSize;” >>$outfile
Service relevant values
echo >>$outfile
echo “systemctl service timeouts:”>>$outfile
systemctl show mariadb | grep “^Timeout” >>$outfile
Kernel
echo >>$outfile
uname -r >>$outfile
Available RAM
echo >>$outfile
echo “MemInfo and CPU Core Count”>>$outfile
cat /proc/meminfo | grep MemTotal >>$outfile
CPU cores
echo >>$outfile
echo “CPU cores” >>$outfile
cat /proc/cpuinfo | egrep “core id|physical id” | tr -d “\n” | sed s/physical/\nphysical/g | grep -v “^$” | sort | uniq | wc -l >>$outfile
Disk space
echo >>$outfile
echo “Disk space” >>$outfile
df -h >>$outfile
Installed packages
echo >>$outfile
echo “Installed packages” >>$outfile
yum list installed | egrep -i “mariadb|percona” >>$outfile