diff options
| -rw-r--r-- | mysql-test/lib/init_db.sql | 100 | ||||
| -rw-r--r-- | scripts/mysqldumpslow.sh | 43 |
2 files changed, 90 insertions, 53 deletions
diff --git a/mysql-test/lib/init_db.sql b/mysql-test/lib/init_db.sql index cc44165405f..902af0b0842 100644 --- a/mysql-test/lib/init_db.sql +++ b/mysql-test/lib/init_db.sql @@ -4,18 +4,18 @@ CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, - Select_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Update_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL, - References_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Index_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM @@ -30,18 +30,18 @@ INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y', CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, - Select_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Update_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL, - References_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Index_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin @@ -52,28 +52,28 @@ CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) binary DEFAULT '' NOT NULL, - Select_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Update_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Process_priv enum('N','Y') DEFAULT 'N' NOT NULL, - File_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL, - References_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Index_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Super_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL, - Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL, - ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL, + Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, + ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, @@ -97,7 +97,7 @@ CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, - type enum ('function','aggregate') NOT NULL, + type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin @@ -111,8 +111,8 @@ CREATE TABLE tables_priv ( Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp(14), - Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') DEFAULT '' NOT NULL, - Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL, + Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') COLLATE utf8_general_ci DEFAULT '' NOT NULL, + Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name),KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin @@ -126,7 +126,7 @@ CREATE TABLE columns_priv ( Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp(14), - Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL, + Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin diff --git a/scripts/mysqldumpslow.sh b/scripts/mysqldumpslow.sh index e8f73c25b55..ccb006f692d 100644 --- a/scripts/mysqldumpslow.sh +++ b/scripts/mysqldumpslow.sh @@ -17,8 +17,9 @@ my %opt = ( ); GetOptions(\%opt, - 'v+', # verbose - 'd+', # debug + 'verbose|v+',# verbose + 'help+', # write usage info + 'debug|d+', # debug 's=s', # what to sort by (t, at, l, al, r, ar etc) 'r!', # reverse the sort order (largest last instead of first) 't=i', # just show the top n queries @@ -28,8 +29,9 @@ GetOptions(\%opt, 'h=s', # hostname of db server for *-slow.log filename (can be wildcard) 'i=s', # name of server instance (if using mysql.server startup script) 'l!', # don't subtract lock time from total time -) or die "Bad option"; +) or usage("bad option"); +$opt{'help'} and usage(); unless (@ARGV) { my $defaults = `my_print_defaults mysqld`; @@ -141,3 +143,38 @@ foreach (@sorted) { printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows=%.1f (%d), $user\@$host\n%s\n\n", $c, $at,$t, $al,$l, $ar,$r, $_; } + +sub usage { + my $str= shift; + my $text= <<HERE; +Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] + +Parse and summarize the MySQL slow query log. Options are + + --verbose verbose + --debug debug + --help write this text to standard output + + -v verbose + -d debug + -s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default + -r reverse the sort order (largest last instead of first) + -t NUM just show the top n queries + -a don't abstract all numbers to N and strings to 'S' + -n NUM abstract numbers with at least n digits within names + -g PATTERN grep: only consider stmts that include this string + -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), + default is '*', i.e. match all + -i NAME name of server instance (if using mysql.server startup script) + -l don't subtract lock time from total time + +HERE + if ($str) { + print STDERR "ERROR: $str\n\n"; + print STDERR $text; + exit 1; + } else { + print $text; + exit 0; + } +} |