summaryrefslogtreecommitdiff
path: root/scripts
diff options
context:
space:
mode:
Diffstat (limited to 'scripts')
-rw-r--r--scripts/Makefile.am4
-rw-r--r--scripts/explain_log.sh383
-rw-r--r--scripts/make_binary_distribution.sh2
-rw-r--r--scripts/mysql_config.sh13
-rw-r--r--scripts/mysql_explain_log.sh383
-rw-r--r--scripts/mysql_fix_privilege_tables.sh32
-rw-r--r--scripts/mysql_install_db.sh16
7 files changed, 814 insertions, 19 deletions
diff --git a/scripts/Makefile.am b/scripts/Makefile.am
index d2cca98ce63..c83a7e2315f 100644
--- a/scripts/Makefile.am
+++ b/scripts/Makefile.am
@@ -28,6 +28,7 @@ bin_SCRIPTS = @server_scripts@ \
mysql_find_rows \
mysqlhotcopy \
mysqldumpslow \
+ mysql_explain_log \
mysqld_multi
EXTRA_SCRIPTS = make_binary_distribution.sh \
@@ -43,6 +44,7 @@ EXTRA_SCRIPTS = make_binary_distribution.sh \
mysql_find_rows.sh \
mysqlhotcopy.sh \
mysqldumpslow.sh \
+ mysql_explain_log.sh \
mysqld_multi.sh \
mysqld_safe.sh
@@ -95,6 +97,8 @@ SUFFIXES = .sh
-e 's!@''CXXFLAGS''@!@SAVE_CXXFLAGS@!'\
-e 's!@''LDFLAGS''@!@SAVE_LDFLAGS@!'\
-e 's!@''CLIENT_LIBS''@!@CLIENT_LIBS@!' \
+ -e 's!@''LIBS''@!@LIBS@!' \
+ -e 's!@''innodb_system_libs''@!@innodb_system_libs@!' \
-e 's!@''VERSION''@!@VERSION@!' \
-e 's!@''MYSQL_SERVER_SUFFIX''@!@MYSQL_SERVER_SUFFIX@!' \
-e 's!@''COMPILATION_COMMENT''@!@COMPILATION_COMMENT@!' \
diff --git a/scripts/explain_log.sh b/scripts/explain_log.sh
new file mode 100644
index 00000000000..c4a4ef21568
--- /dev/null
+++ b/scripts/explain_log.sh
@@ -0,0 +1,383 @@
+#!@PERL@ -w
+use strict;
+use DBI;
+
+use Getopt::Long;
+$Getopt::Long::ignorecase=0;
+
+print "explain_log provided by http://www.mobile.de\n";
+print "=========== ================================\n";
+
+my $Param={};
+
+$Param->{host}='';
+$Param->{user}='';
+$Param->{password}='';
+$Param->{PrintError}=0;
+
+if (!GetOptions ('date|d:i' => \$Param->{ViewDate},
+ 'host|h:s' => \$Param->{host},
+ 'user|u:s' => \$Param->{user},
+ 'password|p:s' => \$Param->{password},
+ 'printerror|e:s' => \$Param->{PrintError},
+ )) {
+ ShowOptions();
+}
+else {
+ $Param->{UpdateCount} = 0;
+ $Param->{SelectCount} = 0;
+ $Param->{IdxUseCount} = 0;
+ $Param->{LineCount} = 0;
+
+ $Param->{Init} = 0;
+ $Param->{Field} = 0;
+ $Param->{Refresh} = 0;
+ $Param->{QueryCount} = 0;
+ $Param->{Statistics} =0;
+
+ $Param->{Query} = undef;
+ $Param->{ALL} = undef ;
+ $Param->{Comment} = undef ;
+
+ @{$Param->{Rows}} = (qw|possible_keys key type|);
+
+ if ($Param->{ViewDate}) {
+ $Param->{View} = 0;
+ }
+ else {
+ $Param->{View} = 1;
+ }
+
+ #print "Date=$Param->{ViewDate}, host=$Param->{host}, user=$Param->{user}, password=$Param->{password}\n";
+
+ $Param->{dbh}=DBI->connect("DBI:mysql:host=$Param->{host}",$Param->{user},$Param->{password},{PrintError=>0});
+ if (DBI::err()) {
+ print "Error: " . DBI::errstr() . "\n";
+ }
+ else {
+ $Param->{Start} = time;
+ while(<STDIN>) {
+ $Param->{LineCount} ++ ;
+
+ if ($Param->{ViewDate} ) {
+ if (m/^(\d{6})\s+\d{1,2}:\d\d:\d\d\s.*$/) { # get date
+ #print "# $1 #\n";
+ if ($1 == $Param->{ViewDate}) {
+ $Param->{View} = 1;
+ }
+ else {
+ $Param->{View} = 0;
+ }
+ }
+ }
+ if ($Param->{View} ) {
+ #print "->>>$_";
+
+ if (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
+ #print "C-$1--$2--$3------\n";
+ RunQuery($Param);
+ if (defined $3) {
+ $Param->{CID}->{$2} = $3 ;
+ #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+$/i) { # get connection ID($2) and database($3)
+ #print "\n <<<<<<<<<<<<<<<<<<----------------------------<<<<<<<<<<<<<<<< \n";
+ #print "Connect \n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Change user .*\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
+ #print "C-$1--$2--$3------\n";
+ RunQuery($Param);
+ if (defined $3) {
+ $Param->{CID}->{$2} = $3 ;
+ #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Quit\s+$/i) { # remove connection ID($2) and querystring
+ #print "Q-$1--$2--------\n";
+ RunQuery($Param);
+ delete $Param->{CID}->{$2} ;
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(select.+)$/i) { # get connection ID($2) and querystring
+ #print "S1-$1--$2--$3------\n";
+ RunQuery($Param);
+ unless ($Param->{CID}->{$2}) {
+ #print "Error: No Database for Handle: $2 found\n";
+ }
+ else {
+ $Param->{DB}=$Param->{CID}->{$2};
+
+ my $s = "$3";
+ $s =~ s/from\s/from $Param->{DB}./i;
+ $Param->{Query}="EXPLAIN $s";
+
+ #$s =~ m/from\s+(\w+[.]\w+)/i;
+ #$Param->{tab} =$1;
+ #print "-- $Param->{tab} -- $s --\n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(update.+)$/i) { # get connection ID($2) and querystring
+ #print "S2--$1--$2--$3------\n";
+ RunQuery($Param);
+ unless ($Param->{CID}->{$2}) {
+ #print "Error: No Database for Handle: $2 found\n";
+ }
+ else {
+ $Param->{DB}=$Param->{CID}->{$2};
+
+ my $ud = $3;
+ $ud =~ m/^update\s+(\w+).+(where.+)$/i;
+ $Param->{Query} ="EXPLAIN SELECT * FROM $1 $2";
+ $Param->{Query} =~ s/from\s/from $Param->{DB}./i;
+
+ #$Param->{Query} =~ m/from\s+(\w+[.]\w+)/i;
+ #$Param->{tab} =$1;
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Statistics\s+(.*)$/i) { # get connection ID($2) and info?
+ $Param->{Statistics} ++;
+ #print "Statistics--$1--$2--$3------\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(.+)$/i) { # get connection ID($2)
+ $Param->{QueryCount} ++;
+ #print "Query-NULL $3\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Refresh\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Refresh} ++;
+ #print "Refresh\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Init\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Init} ++;
+ #print "Init $3\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Field\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Field} ++;
+ #print "Field $3\n";
+ RunQuery($Param);
+ }
+
+ elsif (m/^\s+(.+)$/ ) { # command could be some lines ...
+ #print "multi-lined ($1)\n";
+ my ($A)=$1;
+ chomp $A;
+ $Param->{Query} .= " $1";
+ #print "multi-lined ($1)<<$Param->{Query}>>\n";
+ }
+
+
+ }
+
+ }
+
+ $Param->{dbh}->disconnect();
+
+ if (1 == 0) {
+ print "\nunclosed handles----------------------------------------\n";
+ my $count=0;
+ foreach (sort keys %{$Param->{CID}}) {
+ print "$count | $_ : $Param->{CID}->{$_} \n";
+ $count ++;
+ }
+ }
+
+ print "\nIndex usage ------------------------------------\n";
+ foreach my $t (sort keys %{$Param->{Data}}) {
+ print "\nTable\t$t: ---\n";
+ foreach my $k (sort keys %{$Param->{Data}->{$t}}) {
+ print " count\t$k:\n";
+ my %h = %{$Param->{Data}->{$t}->{$k}};
+ foreach (sort {$h{$a} <=> $h{$b}} keys %h) {
+ print " $Param->{Data}->{$t}->{$k}->{$_}\t$_\n";
+ }
+ }
+ }
+
+ $Param->{AllCount}=0;
+ print "\nQueries causing table scans -------------------\n\n";
+ foreach (@{$Param->{ALL}}) {
+ $Param->{AllCount} ++;
+ print "$_\n";
+ }
+ print "Sum: $Param->{AllCount} table scans\n";
+
+ print "\nSummary ---------------------------------------\n\n";
+ print "Select: \t$Param->{SelectCount} queries\n";
+ print "Update: \t$Param->{UpdateCount} queries\n";
+ print "\n";
+
+ print "Init: \t$Param->{Init} times\n";
+ print "Field: \t$Param->{Field} times\n";
+ print "Refresh: \t$Param->{Refresh} times\n";
+ print "Query: \t$Param->{QueryCount} times\n";
+ print "Statistics:\t$Param->{Statistics} times\n";
+ print "\n";
+
+ print "Logfile: \t$Param->{LineCount} lines\n";
+ print "Started: \t".localtime($Param->{Start})."\n";
+ print "Finished: \t".localtime(time)."\n";
+
+ }
+}
+
+
+###########################################################################
+#
+#
+#
+sub RunQuery {
+ my $Param = shift ;
+
+ if (defined $Param->{Query}) {
+ if (defined $Param->{DB} ) {
+
+ $Param->{Query} =~ m/from\s+(\w+[.]\w+|\w+)/i;
+ $Param->{tab} =$1;
+ #print "||$Param->{tab} -- $Param->{Query}\n";
+
+ my $sth=$Param->{dbh}->prepare("USE $Param->{DB}");
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ $sth->execute();
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ $sth->finish();
+
+ $sth=$Param->{dbh}->prepare($Param->{Query});
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ #print "$Param->{Query}\n";
+ $sth->execute();
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "[$Param->{LineCount}]<<$Param->{Query}>>\n";}
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ my $row = undef;
+ while ($row = $sth->fetchrow_hashref()) {
+ $Param->{SelectCount} ++;
+
+ if (defined $row->{Comment}) {
+ push (@{$Param->{Comment}}, "$row->{Comment}; $_; $Param->{DB}; $Param->{Query}");
+ }
+ foreach (@{$Param->{Rows}}) {
+ if (defined $row->{$_}) {
+ #if (($_ eq 'type' ) and ($row->{$_} eq 'ALL')) {
+ if ($row->{type} eq 'ALL') {
+ push (@{$Param->{ALL}}, "$Param->{Query}");
+ #print ">> $row->{$_} $_ $Param->{DB} $Param->{Query}\n";
+ }
+ $Param->{IdxUseCount} ++;
+ $Param->{Data}->{$Param->{tab}}->{$_}->{$row->{$_}} ++;
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ $sth->finish();
+ }
+ $Param->{Query} = undef ;
+ }
+}
+
+###########################################################################
+#
+#
+#
+sub ShowOptions {
+ print <<EOF;
+Usage: $0 [OPTIONS] < LOGFILE
+
+--date=YYMMDD select only entrys of date
+-d=YYMMDD
+--host=HOSTNAME db-host to ask
+-h=HOSTNAME
+--user=USERNAME db-user
+-u=USERNAME
+--password=PASSWORD password of db-user
+-p=PASSWORD
+
+Read logfile from STDIN an try to EXPLAIN all SELECT statements. All UPDATE statements are rewritten to an EXPLAIN SELECT statement. The results of the EXPLAIN statement are collected and counted. All results with type=ALL are collected in an separete list. Results are printed to STDOUT.
+
+EOF
+}
+
+1;
+
+__END__
+
+=pod
+
+=head1 NAME
+
+explain_log.pl
+
+Feed a mysqld general logfile (created with mysqld --log) back into mysql
+and collect statistics about index usage with EXPLAIN.
+
+=head1 DISCUSSION
+
+To optimize your indices, you have to know which ones are actually
+used and what kind of queries are causing table scans. Especially
+if you are generating your queries dynamically and you have a huge
+amount of queries going on, this isn't easy.
+
+Use this tool to take a look at the effects of your real life queries.
+Then add indices to avoid table scans and remove those which aren't used.
+
+=head1 USAGE
+
+explain_log.pl [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] < logfile
+
+--date=YYMMDD select only entrys of date
+
+-d=YYMMDD
+
+--host=HOSTNAME db-host to ask
+
+-h=HOSTNAME
+
+--user=USERNAME db-user
+
+-u=USERNAME
+
+--password=PASSWORD password of db-user
+
+-p=PASSWORD
+
+=head1 EXAMPLE
+
+explain_log.pl --host=localhost --user=foo --password=bar < /var/lib/mysql/mobile.log
+
+=head1 AUTHOR
+
+ Stefan Nitz
+ Jan Willamowius <jan@mobile.de>, http://www.mobile.de
+
+=head1 RECRUITING
+
+If you are looking for a MySQL or Perl job, take a look at http://www.mobile.de
+and send me an email with your resume (you must be speaking German!).
+
+=head1 SEE ALSO
+
+mysql documentation
+
+=cut
diff --git a/scripts/make_binary_distribution.sh b/scripts/make_binary_distribution.sh
index b97d8f204db..35be819cd2e 100644
--- a/scripts/make_binary_distribution.sh
+++ b/scripts/make_binary_distribution.sh
@@ -88,7 +88,7 @@ do
fi
done
-for i in libmysql/.libs/libmysqlclient.a libmysql/.libs/libmysqlclient.so* libmysql/libmysqlclient.* libmysql_r/.libs/libmysqlclient_r.a libmysql_r/.libs/libmysqlclient_r.so* libmysql_r/libmysqlclient_r.* mysys/libmysys.a strings/libmystrings.a dbug/libdbug.a $BASE/lib
+for i in libmysql/.libs/libmysqlclient.a libmysql/.libs/libmysqlclient.so* libmysql/libmysqlclient.* libmysql_r/.libs/libmysqlclient_r.a libmysql_r/.libs/libmysqlclient_r.so* libmysql_r/libmysqlclient_r.* mysys/libmysys.a strings/libmystrings.a dbug/libdbug.a libmysqld/.libs/libmysqld.a libmysqld/.libs/libmysqld.so* libmysqld/libmysqld.a libmysqld/libmysqld.a
do
if [ -f $i ]
then
diff --git a/scripts/mysql_config.sh b/scripts/mysql_config.sh
index ed344f4b1e3..b2a09173760 100644
--- a/scripts/mysql_config.sh
+++ b/scripts/mysql_config.sh
@@ -31,16 +31,18 @@ client_libs='@CLIENT_LIBS@'
libs="$ldflags -L'$pkglibdir' -lmysqlclient $client_libs"
cflags="-I'$pkgincludedir'"
+embedded_libs="$ldflags -L'$pkglibdir' -lmysqld @LIBS@ @innodb_system_libs@"
usage () {
cat <<EOF
Usage: $0 [OPTIONS]
Options:
- --cflags [$cflags]
- --libs [$libs]
- --socket [$socket]
- --port [$port]
- --version [$version]
+ --cflags [$cflags]
+ --libs [$libs]
+ --socket [$socket]
+ --port [$port]
+ --version [$version]
+ --libmysqld-libs [$embedded_libs]
EOF
exit 1
}
@@ -54,6 +56,7 @@ while test $# -gt 0; do
--socket) echo "$socket" ;;
--port) echo "$port" ;;
--version) echo "$version" ;;
+ --embedded-libs | --embedded | libmysqld-libs) echo "$embedded_libs" ;;
*) usage ;;
esac
diff --git a/scripts/mysql_explain_log.sh b/scripts/mysql_explain_log.sh
new file mode 100644
index 00000000000..d7f6bb97b40
--- /dev/null
+++ b/scripts/mysql_explain_log.sh
@@ -0,0 +1,383 @@
+#!@PERL@ -w
+use strict;
+use DBI;
+
+use Getopt::Long;
+$Getopt::Long::ignorecase=0;
+
+print "explain_log provided by http://www.mobile.de\n";
+print "=========== ================================\n";
+
+my $Param={};
+
+$Param->{host}='';
+$Param->{user}='';
+$Param->{password}='';
+$Param->{PrintError}=0;
+
+if (!GetOptions ('date|d:i' => \$Param->{ViewDate},
+ 'host|h:s' => \$Param->{host},
+ 'user|u:s' => \$Param->{user},
+ 'password|p:s' => \$Param->{password},
+ 'printerror|e:s' => \$Param->{PrintError},
+ )) {
+ ShowOptions();
+}
+else {
+ $Param->{UpdateCount} = 0;
+ $Param->{SelectCount} = 0;
+ $Param->{IdxUseCount} = 0;
+ $Param->{LineCount} = 0;
+
+ $Param->{Init} = 0;
+ $Param->{Field} = 0;
+ $Param->{Refresh} = 0;
+ $Param->{QueryCount} = 0;
+ $Param->{Statistics} =0;
+
+ $Param->{Query} = undef;
+ $Param->{ALL} = undef ;
+ $Param->{Comment} = undef ;
+
+ @{$Param->{Rows}} = (qw|possible_keys key type|);
+
+ if ($Param->{ViewDate}) {
+ $Param->{View} = 0;
+ }
+ else {
+ $Param->{View} = 1;
+ }
+
+ #print "Date=$Param->{ViewDate}, host=$Param->{host}, user=$Param->{user}, password=$Param->{password}\n";
+
+ $Param->{dbh}=DBI->connect("DBI:mysql:host=$Param->{host}",$Param->{user},$Param->{password},{PrintError=>0});
+ if (DBI::err()) {
+ print "Error: " . DBI::errstr() . "\n";
+ }
+ else {
+ $Param->{Start} = time;
+ while(<STDIN>) {
+ $Param->{LineCount} ++ ;
+
+ if ($Param->{ViewDate} ) {
+ if (m/^(\d{6})\s+\d{1,2}:\d\d:\d\d\s.*$/) { # get date
+ #print "# $1 #\n";
+ if ($1 == $Param->{ViewDate}) {
+ $Param->{View} = 1;
+ }
+ else {
+ $Param->{View} = 0;
+ }
+ }
+ }
+ if ($Param->{View} ) {
+ #print "->>>$_";
+
+ if (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
+ #print "C-$1--$2--$3------\n";
+ RunQuery($Param);
+ if (defined $3) {
+ $Param->{CID}->{$2} = $3 ;
+ #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Connect.+$/i) { # get connection ID($2) and database($3)
+ #print "\n <<<<<<<<<<<<<<<<<<----------------------------<<<<<<<<<<<<<<<< \n";
+ #print "Connect \n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Change user .*\s+on\s+(.*)$/i) { # get connection ID($2) and database($3)
+ #print "C-$1--$2--$3------\n";
+ RunQuery($Param);
+ if (defined $3) {
+ $Param->{CID}->{$2} = $3 ;
+ #print "DB:$Param->{CID}->{$2} .. $2 .. $3 \n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Quit\s+$/i) { # remove connection ID($2) and querystring
+ #print "Q-$1--$2--------\n";
+ RunQuery($Param);
+ delete $Param->{CID}->{$2} ;
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(select.+)$/i) { # get connection ID($2) and querystring
+ #print "S1-$1--$2--$3------\n";
+ RunQuery($Param);
+ unless ($Param->{CID}->{$2}) {
+ #print "Error: No Database for Handle: $2 found\n";
+ }
+ else {
+ $Param->{DB}=$Param->{CID}->{$2};
+
+ my $s = "$3";
+ $s =~ s/from\s/from $Param->{DB}./i;
+ $Param->{Query}="EXPLAIN $s";
+
+ #$s =~ m/from\s+(\w+[.]\w+)/i;
+ #$Param->{tab} =$1;
+ #print "-- $Param->{tab} -- $s --\n";
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(update.+)$/i) { # get connection ID($2) and querystring
+ #print "S2--$1--$2--$3------\n";
+ RunQuery($Param);
+ unless ($Param->{CID}->{$2}) {
+ #print "Error: No Database for Handle: $2 found\n";
+ }
+ else {
+ $Param->{DB}=$Param->{CID}->{$2};
+
+ my $ud = $3;
+ $ud =~ m/^update\s+(\w+).+(where.+)$/i;
+ $Param->{Query} ="EXPLAIN SELECT * FROM $1 $2";
+ $Param->{Query} =~ s/from\s/from $Param->{DB}./i;
+
+ #$Param->{Query} =~ m/from\s+(\w+[.]\w+)/i;
+ #$Param->{tab} =$1;
+ }
+ }
+
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Statistics\s+(.*)$/i) { # get connection ID($2) and info?
+ $Param->{Statistics} ++;
+ #print "Statistics--$1--$2--$3------\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Query\s+(.+)$/i) { # get connection ID($2)
+ $Param->{QueryCount} ++;
+ #print "Query-NULL $3\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Refresh\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Refresh} ++;
+ #print "Refresh\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Init\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Init} ++;
+ #print "Init $3\n";
+ RunQuery($Param);
+ }
+ elsif (m/^(\d{6}\s+\d{1,2}:\d\d:\d\d\s+|\s+)(\d+)\s+Field\s+(.+)$/i) { # get connection ID($2)
+ $Param->{Field} ++;
+ #print "Field $3\n";
+ RunQuery($Param);
+ }
+
+ elsif (m/^\s+(.+)$/ ) { # command could be some lines ...
+ #print "multi-lined ($1)\n";
+ my ($A)=$1;
+ chomp $A;
+ $Param->{Query} .= " $1";
+ #print "multi-lined ($1)<<$Param->{Query}>>\n";
+ }
+
+
+ }
+
+ }
+
+ $Param->{dbh}->disconnect();
+
+ if (1 == 0) {
+ print "\nunclosed handles----------------------------------------\n";
+ my $count=0;
+ foreach (sort keys %{$Param->{CID}}) {
+ print "$count | $_ : $Param->{CID}->{$_} \n";
+ $count ++;
+ }
+ }
+
+ print "\nIndex usage ------------------------------------\n";
+ foreach my $t (sort keys %{$Param->{Data}}) {
+ print "\nTable\t$t: ---\n";
+ foreach my $k (sort keys %{$Param->{Data}->{$t}}) {
+ print " count\t$k:\n";
+ my %h = %{$Param->{Data}->{$t}->{$k}};
+ foreach (sort {$h{$a} <=> $h{$b}} keys %h) {
+ print " $Param->{Data}->{$t}->{$k}->{$_}\t$_\n";
+ }
+ }
+ }
+
+ $Param->{AllCount}=0;
+ print "\nQueries causing table scans -------------------\n\n";
+ foreach (@{$Param->{ALL}}) {
+ $Param->{AllCount} ++;
+ print "$_\n";
+ }
+ print "Sum: $Param->{AllCount} table scans\n";
+
+ print "\nSummary ---------------------------------------\n\n";
+ print "Select: \t$Param->{SelectCount} queries\n";
+ print "Update: \t$Param->{UpdateCount} queries\n";
+ print "\n";
+
+ print "Init: \t$Param->{Init} times\n";
+ print "Field: \t$Param->{Field} times\n";
+ print "Refresh: \t$Param->{Refresh} times\n";
+ print "Query: \t$Param->{QueryCount} times\n";
+ print "Statistics:\t$Param->{Statistics} times\n";
+ print "\n";
+
+ print "Logfile: \t$Param->{LineCount} lines\n";
+ print "Started: \t".localtime($Param->{Start})."\n";
+ print "Finished: \t".localtime(time)."\n";
+
+ }
+}
+
+
+###########################################################################
+#
+#
+#
+sub RunQuery {
+ my $Param = shift ;
+
+ if (defined $Param->{Query}) {
+ if (defined $Param->{DB} ) {
+
+ $Param->{Query} =~ m/from\s+(\w+[.]\w+|\w+)/i;
+ $Param->{tab} =$1;
+ #print "||$Param->{tab} -- $Param->{Query}\n";
+
+ my $sth=$Param->{dbh}->prepare("USE $Param->{DB}");
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ $sth->execute();
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ $sth->finish();
+
+ $sth=$Param->{dbh}->prepare($Param->{Query});
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ #print "$Param->{Query}\n";
+ $sth->execute();
+ if (DBI::err()) {
+ if ($Param->{PrintError}) {print "[$Param->{LineCount}]<<$Param->{Query}>>\n";}
+ if ($Param->{PrintError}) {print "Error: ".DBI::errstr()."\n";}
+ }
+ else {
+ my $row = undef;
+ while ($row = $sth->fetchrow_hashref()) {
+ $Param->{SelectCount} ++;
+
+ if (defined $row->{Comment}) {
+ push (@{$Param->{Comment}}, "$row->{Comment}; $_; $Param->{DB}; $Param->{Query}");
+ }
+ foreach (@{$Param->{Rows}}) {
+ if (defined $row->{$_}) {
+ #if (($_ eq 'type' ) and ($row->{$_} eq 'ALL')) {
+ if ($row->{type} eq 'ALL') {
+ push (@{$Param->{ALL}}, "$row->{$_} $_ $Param->{DB} $Param->{Query}");
+ #print ">> $row->{$_} $_ $Param->{DB} $Param->{Query}\n";
+ }
+ $Param->{IdxUseCount} ++;
+ $Param->{Data}->{$Param->{tab}}->{$_}->{$row->{$_}} ++;
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ $sth->finish();
+ }
+ $Param->{Query} = undef ;
+ }
+}
+
+###########################################################################
+#
+#
+#
+sub ShowOptions {
+ print <<EOF;
+Usage: $0 [OPTIONS] < LOGFILE
+
+--date=YYMMDD select only entrys of date
+-d=YYMMDD
+--host=HOSTNAME db-host to ask
+-h=HOSTNAME
+--user=USERNAME db-user
+-u=USERNAME
+--password=PASSWORD password of db-user
+-p=PASSWORD
+
+Read logfile from STDIN an try to EXPLAIN all SELECT statements. All UPDATE statements are rewritten to an EXPLAIN SELECT statement. The results of the EXPLAIN statement are collected and counted. All results with type=ALL are collected in an separete list. Results are printed to STDOUT.
+
+EOF
+}
+
+1;
+
+__END__
+
+=pod
+
+=head1 NAME
+
+explain_log.pl
+
+Feed a mysqld general logfile (created with mysqld --log) back into mysql
+and collect statistics about index usage with EXPLAIN.
+
+=head1 DISCUSSION
+
+To optimize your indices, you have to know which ones are actually
+used and what kind of queries are causing table scans. Especially
+if you are generating your queries dynamically and you have a huge
+amount of queries going on, this isn't easy.
+
+Use this tool to take a look at the effects of your real life queries.
+Then add indices to avoid table scans and remove those which aren't used.
+
+=head1 USAGE
+
+explain_log.pl [--date=YYMMDD] --host=dbhost] [--user=dbuser] [--password=dbpw] < logfile
+
+--date=YYMMDD select only entrys of date
+
+-d=YYMMDD
+
+--host=HOSTNAME db-host to ask
+
+-h=HOSTNAME
+
+--user=USERNAME db-user
+
+-u=USERNAME
+
+--password=PASSWORD password of db-user
+
+-p=PASSWORD
+
+=head1 EXAMPLE
+
+explain_log.pl --host=localhost --user=foo --password=bar < /var/lib/mysql/mobile.log
+
+=head1 AUTHOR
+
+ Stefan Nitz
+ Jan Willamowius <jan@mobile.de>, http://www.mobile.de
+
+=head1 RECRUITING
+
+If you are looking for a MySQL or Perl job, take a look at http://www.mobile.de
+and send me an email with your resume (you must be speaking German!).
+
+=head1 SEE ALSO
+
+mysql documentation
+
+=cut
diff --git a/scripts/mysql_fix_privilege_tables.sh b/scripts/mysql_fix_privilege_tables.sh
index 8e9ef509d66..7226840b475 100644
--- a/scripts/mysql_fix_privilege_tables.sh
+++ b/scripts/mysql_fix_privilege_tables.sh
@@ -3,8 +3,8 @@
echo "This scripts updates the mysql.user, mysql.db, mysql.host and the"
echo "mysql.func table to MySQL 3.22.14 and above."
echo ""
-echo "This is needed if you want to use the new GRANT functions or"
-echo "want to use the more secure passwords."
+echo "This is needed if you want to use the new GRANT functions,"
+echo "CREATE AGGREAGATE FUNCTION or want to use the more secure passwords in 3.23"
echo ""
echo "If you get Access denied errors, you should run this script again"
echo "and give the MySQL root user password as a argument!"
@@ -15,13 +15,12 @@ host="localhost"
# Fix old password format, add File_priv and func table
echo ""
echo "If your tables are already up to date or partially up to date you will"
-echo "get some warnings about 'Duplicated column name' or"
-echo "'Table 'func' already exists'. You can safely ignore these!"
+echo "get some warnings about 'Duplicated column name'. You can safely ignore these!"
@bindir@/mysql -f --user=root --password="$root_password" --host="$host" mysql <<END_OF_DATA
alter table user change password password char(16) NOT NULL;
alter table user add File_priv enum('N','Y') NOT NULL;
-CREATE TABLE func (
+CREATE TABLE if not exists func (
name char(64) DEFAULT '' NOT NULL,
ret tinyint(1) DEFAULT '0' NOT NULL,
dl char(128) DEFAULT '' NOT NULL,
@@ -57,6 +56,13 @@ END_OF_DATA
echo ""
fi
+echo "Adding columns needed by GRANT .. REQUIRE (openssl)"
+echo "You can ignore any Duplicate column errors"
+@bindir@/mysql --user=root --password="$root_password" --host="$host" mysql <<END_OF_DATA
+ALTER TABLE user ADD ssl_type enum('NONE','ANY','X509', 'SPECIFIED') DEFAULT 'NONE' NOT NULL, ADD ssl_cipher BLOB NOT NULL, ADD x509_issuer BLOB NOT NULL, ADD x509_subject BLOB NOT NULL
+END_OF_DATA
+echo ""
+
#
# Create tables_priv and columns_priv if they don't exists
#
@@ -64,7 +70,7 @@ fi
echo "Creating the new table and column privilege tables"
@bindir@/mysql -f --user=root --password="$root_password" --host="$host" mysql <<END_OF_DATA
-CREATE TABLE tables_priv (
+CREATE TABLE IF NOT EXISTS tables_priv (
Host char(60) DEFAULT '' NOT NULL,
Db char(60) DEFAULT '' NOT NULL,
User char(16) DEFAULT '' NOT NULL,
@@ -75,7 +81,7 @@ CREATE TABLE tables_priv (
Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL,
PRIMARY KEY (Host,Db,User,Table_name)
);
-CREATE TABLE columns_priv (
+CREATE TABLE IF NOT EXISTS columns_priv (
Host char(60) DEFAULT '' NOT NULL,
Db char(60) DEFAULT '' NOT NULL,
User char(16) DEFAULT '' NOT NULL,
@@ -97,6 +103,7 @@ echo "You can ignore any errors from this"
@bindir@/mysql -f --user=root --password="$root_password" --host="$host" mysql <<END_OF_DATA
ALTER TABLE columns_priv change Type Column_priv set('Select','Insert','Update','References') DEFAULT '' NOT NULL;
END_OF_DATA
+echo ""
#
# Add the new 'type' column to the func table.
@@ -108,3 +115,14 @@ echo "You can ignore any Duplicate column errors"
@bindir@/mysql --user=root --password=$root_password mysql <<EOF
alter table func add type enum ('function','aggregate') NOT NULL;
EOF
+echo ""
+
+echo "Converting all privilege tables to MyISAM format"
+@bindir@/mysql -f --user=root --password="$root_password" --host="$host" mysql <<END_OF_DATA
+ALTER TABLE user type=MyISAM;
+ALTER TABLE db type=MyISAM;
+ALTER TABLE host type=MyISAM;
+ALTER TABLE func type=MyISAM;
+ALTER TABLE columns_priv type=MyISAM;
+ALTER TABLE tables_priv type=MyISAM;
+END_OF_DATA
diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh
index 7e232692ba1..c99126cdf53 100644
--- a/scripts/mysql_install_db.sh
+++ b/scripts/mysql_install_db.sh
@@ -224,18 +224,22 @@ then
c_u="$c_u References_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
c_u="$c_u Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
c_u="$c_u Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,"
+ c_u="$c_u ssl_type enum('NONE','ANY','X509', 'SPECIFIED') DEFAULT 'NONE' NOT NULL,"
+ c_u="$c_u ssl_cipher BLOB NOT NULL,"
+ c_u="$c_u x509_issuer BLOB NOT NULL,"
+ c_u="$c_u x509_subject BLOB NOT NULL,"
c_u="$c_u PRIMARY KEY Host (Host,User)"
c_u="$c_u )"
c_u="$c_u comment='Users and global privileges';"
- i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
- INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
+ i_u="INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','NONE','','','');
+ INSERT INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','NONE','','','');
- REPLACE INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
- REPLACE INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
+ REPLACE INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','NONE','','','');
+ REPLACE INTO user VALUES ('$hostname','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','NONE','','','');
- INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
- INSERT INTO user VALUES ('$hostname','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');"
+ INSERT INTO user VALUES ('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','NONE','','','');
+ INSERT INTO user VALUES ('$hostname','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','NONE','','','');"
fi
if test ! -f $mdata/func.frm
View Lorry Controller Status