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