Category : MySQL


This requires that metadata lock instrumentation be enabled. UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’ WHERE NAME = ‘wait/lock/metadata/sql/mdl’; Once that’s in place, you can do this: SELECT   ml.object_schema,   ml.object_name,   p.id,   p.user,   p.state,   ml.lock_type,   ml.lock_duration,   ml.lock_status,   p.time,   LEFT(p.info, 100) FROM   performance_schema.metadata_locks ml   JOIN performance_schema.threads ..

Read more


Here is the standard mysqldump syntax I always use.  This dumps routines and triggers, does so in the most expeditious manner possible, and adds DROP xxxxx statements before all CREATE statements, just in case.  It also adds the FORCE parameter in case it runs into issues with views. The “xxxx” at the end can be ..

Read more


MySQL 8.x not only requires that the datadir be empty when initializing, but also the directory designated as the temp directory.  If the temp directory is not empty, the –initialize flag will not throw an error as it does if the datadir isn’t clean; rather, it will cause a “Data Dictionary initialization failed” message on ..

Read more


In attempting to use the mysqldump utility provided with MySQL 8.x against a 5.7 server, you may receive the following error message: mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘) This is due to the INFORMATION_SCHEMA in 8.0 having more columns to support several new features in 8.x.  This error can be silenced by adding the ..

Read more


A sample service check, annotated:    host_name                       dbsrp2076                  <– name of server     service_description             SSH                        <– service being monitored     servicegroups                   PROD-ssh                   <– service groups     is_volatile                     0                          <– Does this service spontaneously start and stop (always 0 for “no”)     check_period                    24×7                       <– during what hours is this service checked?     max_check_attempts              10                         <– ..

Read more


After you configure all PXC nodes, initialize the cluster by bootstrapping the first node. The initial node should be the one that contains all your data, which you want to be replicated to other nodes. Bootstrapping implies starting the node without any known cluster addresses. If the wsrep_cluster_address variable is empty, Percona XtraDB Cluster assumes ..

Read more


#!/usr/bin/env perl # mysqltuner.pl – Version 1.7.9 # High Performance MySQL Tuning Script # Copyright (C) 2006-2018 Major Hayden – major@mhtx.net # Inspired by Matthew Montgomery’s tuning-primer.sh script: # http://forge.mysql.com/projects/view.php?id=44 # # This version further hacked and slashed by rlbyrd off and on since 2009. package main; use 5.005; use strict; use warnings; use diagnostics; ..

Read more


— SQL snippet to calculate table fragmentation. SELECT table_schema, TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024) AS data_length, ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length, ROUND(DATA_FREE / 1024 / 1024) AS data_free, CONCAT( ROUND( ( data_free / (index_length + data_length) ) * 100 ), ‘%’ ) AS frag_ratio FROM information_schema.tables WHERE DATA_FREE > 0 AND TABLE_SCHEMA ..

Read more


#!/bin/bash # Expects: # If no *.last exists, make one. # if one does, diff it and report # Set this manually. Everything else will branch off this. BASEDIR=”/data/backups/ddl/” DBUSER=”svc_dba_adhoc” DBPASS=”ADXEZKZLbgdvcB24nvPu” TZ=”America/Los_Angeles” EMAILTO=”richard.byrd@example.com,mark.butler@example.com,rlbyrd@rlbyrd.com” if [ “$2” == “” ] then echo ” ” echo “USAGE: ddldiff ” echo ” ” fi HOST=${1} SCHEMA=${2} TMPFILE=”/tmp/${HOST}-${SCHEMA}.tmp” /bin/rm ..

Read more


Traverses all integer columns checking for capacity limits. #!/usr/bin/env perl #use 5.010; #use strict; #use warnings; # Richard L. Byrd, written sometime in the 90s and improved bit by bit over the next 20 years # # 2009-01-27: While originally written to take a configfile input on the CLI (–configfile=xxxxx.cfg) I’ve hacked # that out ..

Read more


NORMALIZATION DIAGRAM   ACID Atomicity Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must ..

Read more


Let us face it. The following mysql> prompt is boring. Nobody wants to see it. Let us change the default mysql> prompt to something functional and useful. $ mysql -u root -pyour-password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.25-rc-community MySQL Community Server ..

Read more


(2012-05-12 update:  Please replace all mentions of “utf8” with “utf8mb4” to get a full utf8 character set.  utf8_general_ci is just a half-assed implementation of utf8. Similarly, replace all representations of utf8_xxxxxx_xx collations with their utf8mb4 variant.  In for a penny, in for a pound.  NO ONE wants to do two utf8 migrations, ever. The methodology ..

Read more