MySQL ‐ More Scripts - shaysalomon12/Data-Engineer GitHub Wiki

Databases Size

SELECT 
  table_schema AS "Database", 
  round(SUM(data_length + index_length) / 1024 / 1024/ 1024, 2) AS "Size (GB)" 
FROM 
  information_schema.TABLES 
GROUP BY 
  table_schema
ORDER BY 
  2 desc;

Locking information (MySQL 5.7)

SELECT * FROM (
	SELECT `r`.`trx_wait_started` AS `wait_started`,
	TIMEDIFF(NOW(), `r`.`trx_wait_started`) AS `wait_time`,
	'Occurring' AS STATUS,
	`b`.`trx_mysql_thread_id` AS `blocking_pid`,
	CONCAT(tb.processlist_user, '@',  tb.processlist_host) AS blocking_user,
	tb.processlist_db AS blocking_db,
	`b`.`trx_id` AS `blocking_trx_id`,
	esc.sql_text,
	TIMEDIFF(NOW(), `b`.`trx_started`) AS `blocking_trx_age`,
	`r`.`trx_mysql_thread_id` AS `waiting_pid`,
	CONCAT(tl.processlist_user, '@',  tl.processlist_host) AS waiting_user,
	tl.processlist_db AS waiting_db,
	`r`.`trx_id` AS `waiting_trx_id`,
	`r`.`trx_query` AS `waiting_query`,
	`rl`.`lock_table` AS `locked_table`,
	`rl`.`lock_index` AS `locked_index`,
	TIMEDIFF(NOW(), `r`.`trx_started`) AS `waiting_trx_age`,
	CONCAT('SessionID ', b.trx_mysql_thread_id, ' Is Blocking SessionID ', r.trx_mysql_thread_id) AS spec,
	CONCAT('KILL ',`b`.`trx_mysql_thread_id`) AS kill_statement 
FROM ((((`information_schema`.`innodb_lock_waits` `w` JOIN `information_schema`.`innodb_trx` `b` -- Blocker
	ON ((`b`.`trx_id` = `w`.`blocking_trx_id`))) 
JOIN `information_schema`.`innodb_trx` `r` -- Locked
	ON ((`r`.`trx_id` = `w`.`requesting_trx_id`))) 
JOIN `information_schema`.`innodb_locks` `bl` 
	ON ((`bl`.`lock_id` = `w`.`blocking_lock_id`))) 
JOIN `information_schema`.`innodb_locks` `rl` 
	ON ((`rl`.`lock_id` = `w`.`requested_lock_id`)))
JOIN `performance_schema`.`threads` tb
	ON tb.processlist_id = b.trx_mysql_thread_id
JOIN `performance_schema`.`threads` tl
	ON tl.processlist_id = r.trx_mysql_thread_id
JOIN `performance_schema`.`events_statements_current` esc
	ON esc.thread_id = tb.thread_id
ORDER BY `r`.`trx_wait_started`) a
;

Locking information (MySQL 8)

SELECT
  r.trx_id waiting_trx_id,
  trx.trx_started,
  lw.wait_started,
  lw.wait_age_secs wait_in_sec,
  trx.trx_wait_started,
  trx.trx_operation_state,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  esc.sql_text blocking_sql_text,
  trx.trx_state,
  trx.trx_wait_started,
  b.trx_rows_locked rows_locked,
  b.trx_isolation_level,
  dl.object_schema locked_object_schema,
  dl.object_name locked_object_name,
  dl.partition_name locked_partition_name,
  dl.SUBPARTITION_NAME locked_subpartition_name,
  dl.index_name locke_index_name,
  dl.lock_type,
  dl.lock_mode,
  dl.lock_status
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
LEFT JOIN performance_schema.events_statements_current esc ON esc.thread_id = w.blocking_thread_id
LEFT JOIN information_schema.innodb_trx trx ON trx.trx_id = r.trx_id
LEFT JOIN performance_schema.data_locks dl ON dl.ENGINE_TRANSACTION_ID = r.trx_id AND dl.LOCK_STATUS = 'waiting'
LEFT JOIN sys.innodb_lock_waits lw ON lw.waiting_trx_id = r.trx_id
;

Deadlocks:

Set global parameter innodb_print_all_deadlocks=1

Deadlock information will be written to MySQL error log

An InnoDB Deadlock Example

Authenticating MySQL 8.0 (Enterprise) Against Active Directory

Backup Scripts

  • Add the following line to crontab to run a daily job at 01:30
30 1 * * * /root/mariadb_jobs/mariadb_backup_each_schema.sh
  • Backup Script (bash)
#! /bin/bash

TIMESTAMP=$(date +"%F")
HOST=`echo $HOSTNAME | sed 's%\..*%%'`
BACKUP_DIR="/backup/$TIMESTAMP"
MARIADB_USER="root"
MARIADB_PASSWORD="********"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR"

databases=`mysql -u$MARIADB_USER -p$MARIADB_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do

        dumpname=${HOST}_${db}_${TIMESTAMP}

        echo Create database dump
        echo ==============================================
        echo

        mysqldump -h localhost -u$MARIADB_USER -p$MARIADB_PASSWORD --databases $db \
                --master-data --max_allowed_packet=1G --single_transaction --force \
                --add-drop-table -q --triggers --routines \
                --events | gzip > "$BACKUP_DIR/${dumpname}.gz"

        if [ $? != 0 ]
        then
                echo "Error database dump."
                exit 1
        else
                echo "$0 ended at: `date`"
                echo "Database dump is finished successfully."
        fi
done

#tmpwatch 168 $BACKUP_DIR
size=`du -sh $BACKUP_DIR`
countdb=`ls -l $BACKUP_DIR |wc -l`

echo "BACKUP SIZE = $size  --- NUMBER OF DB=$countdb" | mailx -s "BACKUPSTATUS MYSQLPROF" -S smtp://mr.cet.ac.il -S from="[email protected]" \ [email protected] [email protected]
find /backup/ -mtime +20 |xargs rm -rf