server-cfg

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

#!/usr/bin/perl
# -*- perl -*-
# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Library General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this library; if not, write to the Free
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
# MA 02111-1307, USA
#
# The configuration file for the DBI/DBD tests on different databases ....
# You will need the DBD module for the database you are running.
# Monty made this bench script and I (Luuk de Boer) rewrote it to DBI/DBD.
# Monty rewrote this again to use packages.
#
# Each database has a different package that has 3 functions:
# new		Creates a object with some standard slot
# version	Version number of the server
# create	Generates commands to create a table
#

#
# First some global functions that help use the packages:
#

sub get_server
{
  my ($name,$host,$database,$odbc,$machine,$socket,$connect_options)=@_;
  my ($server);
  if ($name =~ /mysql/i)
  { $server=new db_MySQL($host, $database, $machine, $socket,$connect_options); }
  elsif ($name =~ /pg/i)
  { $server= new db_Pg($host,$database); }
  elsif ($name =~ /msql/i)
  { $server= new db_mSQL($host,$database); }
  elsif ($name =~ /solid/i)
  { $server= new db_Solid($host,$database); }
  elsif ($name =~ /Empress/i)
  { $server= new db_Empress($host,$database); }
  elsif ($name =~ /FrontBase/i)
  { $server= new db_FrontBase($host,$database); }
  elsif ($name =~ /Oracle/i)
  { $server= new db_Oracle($host,$database); }
  elsif ($name =~ /Access/i)
  { $server= new db_access($host,$database); }
  elsif ($name =~ /Informix/i)
  { $server= new db_Informix($host,$database); }
  elsif ($name =~ /ms-sql/i)
  { $server= new db_ms_sql($host,$database); }
  elsif ($name =~ /sybase/i)
  { $server= new db_sybase($host,$database); }
  elsif ($name =~ /Adabas/i)			# Adabas has two drivers
  {
    $server= new db_Adabas($host,$database);
    if ($name =~ /AdabasD/i)
    {
      $server->{'data_source'} =~ s/:Adabas:/:AdabasD:/;
    }
  }
  elsif ($name =~ /DB2/i)
  { $server= new db_db2($host,$database); }
  elsif ($name =~ /Mimer/i)
  { $server= new db_Mimer($host,$database); }
  elsif ($name =~ /Sapdb/i)
  { $server= new db_sapdb($host,$database); }
  elsif ($name =~ /interBase/i)
  { $server= new db_interbase($host,$database); }
  else
  {
      die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, InterBase, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid, SAPDB or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n";
  }
  if ($name =~ /_ODBC$/i || defined($odbc) && $odbc)
  {
    if (! ($server->{'data_source'} =~ /^([^:]*):([^:]+):([^:]*)/ ))
    {
      die "Can't find databasename in data_source: '" .
	  $server->{'data_source'}. "'\n";
    }
    if ($3) {
      $server->{'data_source'} = "$1:ODBC:$3";
    } else {
      $server->{'data_source'} = "$1:ODBC:$database";
    }
  }
  return $server;
}

sub all_servers
{
  return ["Access", "Adabas", "DB2", "Empress", "FrontBase", "Oracle",
	  "Informix", "InterBase", "Mimer", "mSQL", "MS-SQL", "MySQL", "Pg","SAPDB",
	  "Solid", "Sybase"];
}

#############################################################################
#	     First the configuration for MySQL off course :-)
#############################################################################

package db_MySQL;

sub new
{
  my ($type,$host,$database,$machine,$socket,$connect_options)= @_;
  my $self= {};
  my %limits;
  bless $self;

  $self->{'cmp_name'}		= "mysql";
  $self->{'data_source'}	= "DBI:mysql:database=$database;host=$host";
  $self->{'data_source'} .= ";mysql_socket=$socket" if($socket);
  $self->{'data_source'} .= ";$connect_options" if($connect_options);
  $self->{'limits'}		= \%limits;
  $self->{'blob'}		= "blob";
  $self->{'text'}		= "text";
  $self->{'double_quotes'}	= 1; # Can handle:  'Walker''s'
  $self->{'vacuum'}		= 1; # When using with --fast
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 0; # Transactions disabled by default

  $limits{'NEG'}		= 1; # Supports -id
  $limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int;
  $limits{'alter_table'}	= 1; # Have ALTER TABLE
  $limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column
  $limits{'column_alias'}	= 1; # Alias for fields in select statement.
  $limits{'func_extra_%'}	= 1; # Has % as alias for mod()
  $limits{'func_extra_if'}	= 1; # Have function if.
  $limits{'func_extra_in_num'}	= 1; # Has function in
  $limits{'func_odbc_floor'}	= 1; # Has func_odbc_floor function
  $limits{'func_odbc_mod'}	= 1; # Have function mod.
  $limits{'functions'}		= 1; # Has simple functions (+/-)
  $limits{'group_by_position'}  = 1; # Can use 'GROUP BY 1'
  $limits{'group_distinct_functions'}= 1; # Have count(distinct)
  $limits{'group_func_extra_std'} = 1; # Have group function std().
  $limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings
  $limits{'group_functions'}	= 1; # Have group functions
  $limits{'having_with_alias'}  = 1; # Can use aliases in HAVING
  $limits{'having_with_group'}	= 1; # Can use group functions in HAVING
  $limits{'insert_multi_value'} = 1; # Have INSERT ... values (1,2),(3,4)
  $limits{'insert_select'}	= 1;
  $limits{'join_optimizer'}	= 1; # Can optimize FROM tables
  $limits{'left_outer_join'}	= 1; # Supports left outer joins
  $limits{'like_with_column'}	= 1; # Can use column1 LIKE column2
  $limits{'limit'}		= 1; # supports the limit attribute
  $limits{'truncate_table'}	= 1;
  $limits{'load_data_infile'}	= 1; # Has load data infile
  $limits{'lock_tables'}	= 1; # Has lock tables
  $limits{'max_column_name'}	= 64; # max table and column name
  $limits{'max_columns'}	= 2000;	# Max number of columns in table
  $limits{'max_conditions'}	= 9999; # (Actually not a limit)
  $limits{'max_index'}		= 16; # Max number of keys
  $limits{'max_index_parts'}	= 16; # Max segments/key
  $limits{'max_tables'}		= (($machine || '') =~ "^win") ? 5000 : 65000;
  $limits{'max_text_size'}	= 1000000; # Good enough for tests
  $limits{'multi_drop'}		= 1; # Drop table can take many tables
  $limits{'order_by_position'}  = 1; # Can use 'ORDER BY 1'
  $limits{'order_by_unused'}	= 1;
  $limits{'query_size'}		= 1000000; # Max size with default buffers.
  $limits{'select_without_from'}= 1; # Can do 'select 1';
  $limits{'subqueries'}		= 0; # Doesn't support sub-queries.
  $limits{'table_wildcard'}	= 1; # Has SELECT table_name.*
  $limits{'unique_index'}	= 1; # Unique index works or not
  $limits{'working_all_fields'} = 1;
  $limits{'working_blobs'}	= 1; # If big varchar/blobs works
  $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b).. 

  # Some fixes that depends on the environment
  if (defined($main::opt_create_options) &&
      $main::opt_create_options =~ /engine=heap/i)
  {
    $limits{'working_blobs'}	= 0; # HEAP tables can't handle BLOB's
  }
  if (defined($main::opt_create_options) &&
      $main::opt_create_options =~ /engine=innodb/i)
  {
    $self->{'transactions'}	= 1;	# Transactions enabled
  }
  if (defined($main::opt_create_options) &&
      $main::opt_create_options =~ /engine=ndb/i)
  {
    $self->{'transactions'}	= 1;	# Transactions enabled
    $limits{'max_columns'}	= 90;	# Max number of columns in table
    $limits{'max_tables'}	= 32;   # No comments
  }
  if (defined($main::opt_create_options) &&
      $main::opt_create_options =~ /engine=bdb/i)
  {
    $self->{'transactions'}	= 1;	# Transactions enabled
  }
  if (defined($main::opt_create_options) &&
      $main::opt_create_options =~ /engine=gemini/i)
  {
    $limits{'working_blobs'}	= 0; # Blobs not implemented yet
    $limits{'max_tables'}	= 500;
    $self->{'transactions'}	= 1;	# Transactions enabled
  }

  return $self;
}

#
# Get the version number of the database
#

sub version
{
  my ($self)=@_;
  my ($dbh,$sth,$version,@row);

  $dbh=$self->connect();
  $sth = $dbh->prepare("select VERSION()") or die $DBI::errstr;
  $version="MySQL 3.20.?";
  if ($sth->execute && (@row = $sth->fetchrow_array))
  {
    $row[0] =~ s/-/ /g;			# To get better tables with long names
    $version="MySQL $row[0]";
  }
  $sth->finish;

  $sth = $dbh->prepare("show status like 'ssl_version'") or die $DBI::errstr;
  if ($sth->execute && (@row = $sth->fetchrow_array))
  {
    $version .= "/$row[1]";
  }
  $sth->finish;
  $dbh->disconnect;
  $version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);
  return $version;
}

#
# Connection with optional disabling of logging
#

sub connect
{
  my ($self)=@_;
  my ($dbh);
  $dbh=DBI->connect($self->{'data_source'}, $main::opt_user,
		    $main::opt_password,{ PrintError => 0}) ||
		      die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

  $dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");
  return $dbh;
}

#
# Returns a list of statements to create a table
# The field types are in ANSI SQL format.
#
# If one uses $main::opt_fast then one is allowed to use
# non standard types to get better speed.
#

sub create
{
  my($self,$table_name,$fields,$index,$options) = @_;
  my($query,@queries);

  $query="create table $table_name (";
  foreach $field (@$fields)
  {
#    $field =~ s/ decimal/ double(10,2)/i;
    $field =~ s/ big_decimal/ double(10,2)/i;
    $query.= $field . ',';
  }
  foreach $index (@$index)
  {
    $query.= $index . ',';
  }
  substr($query,-1)=")";		# Remove last ',';
  $query.=" $options" if (defined($options));
  $query.=" $main::opt_create_options" if (defined($main::opt_create_options));
  push(@queries,$query);
  return @queries;
}

sub insert_file {
  my ($self,$dbname, $file, $dbh) = @_;
  my ($command, $sth);

  $file =~ s|\\|/|g;			# Change Win32 names to Unix syntax
  $command = "load data infile '$file' into table $dbname columns optionally enclosed by '\\'' terminated by ','";
#  print "$command\n";
  $sth = $dbh->do($command) or die $DBI::errstr;
  return $sth;			# Contains number of rows
}

#
# Do any conversions to the ANSI SQL query so that the database can handle it
#

sub query {
  my($self,$sql) = @_;
  return $sql;
}

sub drop_index {
  my ($self,$table,$index) = @_;
  return "DROP INDEX $index ON $table";
}

#
# Abort if the server has crashed
# return: 0 if ok
#	  1 question should be retried
#

sub abort_if_fatal_error
{
  return 0;
}

#
# This should return 1 if we to do disconnect / connect when doing
# big batches
#

sub small_rollback_segment
{
  return 0;
}

#
# reconnect on errors (needed mainly be crash-me)
#

sub reconnect_on_errors
{
  return 0;
}

sub fix_for_insert
{
  my ($self,$cmd) = @_;
  return $cmd;
}

#
# Optimize tables for better performance
#

sub vacuum
{
  my ($self,$full_vacuum,$dbh_ref,@tables)=@_;
  my ($loop_time,$end_time,$dbh);
  if ($#tables >= 0)
  {
    $dbh=$$dbh_ref;
    $loop_time=new Benchmark;
    $dbh->do("OPTIMIZE TABLE " . join(',',@tables)) || die "Got error: $DBI::errstr when executing 'OPTIMIZE TABLE'\n";
    $end_time=new Benchmark;
    print "Time for book-keeping (1): " .
      Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";
  }
}

#############################################################################
#		     Definitions for mSQL
#############################################################################

package db_mSQL;

sub new
{
  my ($type,$host,$database)= @_;
  my $self= {};
  my %limits;
  bless $self;

  $self->{'cmp_name'}		= "msql";
  $self->{'data_source'}	= "DBI:mSQL:$database:$host";
  $self->{'limits'}		= \%limits;
  $self->{'double_quotes'}	= 0;
  $self->{'drop_attr'}		= "";
  $self->{'transactions'}	= 0;  # No transactions
  $self->{'blob'}		= "text(" . $limits{'max_text_size'} .")";
  $self->{'text'}		= "text(" . $limits{'max_text_size'} .")";

  $limits{'max_conditions'}	= 74;
  $limits{'max_columns'}	= 75;
  $limits{'max_tables'}		= 65000;	# Should be big enough
  $limits{'max_text_size'}	= 32000;
  $limits{'query_size'}		= 65535;
  $limits{'max_index'}		= 5;
  $limits{'max_index_parts'}	= 10;
  $limits{'max_column_name'} = 35;

  $limits{'join_optimizer'}	= 0;		# Can't optimize FROM tables
  $limits{'load_data_infile'}	= 0;
  $limits{'lock_tables'}	= 0;
  $limits{'functions'}		= 0;
  $limits{'group_functions'}	= 0;
  $limits{'group_distinct_functions'}= 0;	 # Have count(distinct)
  $limits{'multi_drop'}		= 0;
  $limits{'select_without_from'}= 0;
  $limits{'subqueries'}		= 0;
  $limits{'left_outer_join'}	= 0;
  $limits{'table_wildcard'}	= 0;
  $limits{'having_with_alias'}  = 0;
  $limits{'having_with_group'}	= 0;
  $limits{'like_with_column'}	= 1;
  $limits{'order_by_position'}  = 1;
  $limits{'group_by_position'}  = 1;
  $limits{'alter_table'}	= 0;
  $limits{'alter_add_multi_col'}= 0;
  $limits{'alter_table_dropcol'}= 0;
  $limits{'group_func_extra_std'} = 0;
  $limits{'limit'}		= 1;		# supports the limit attribute
  $limits{'unique_index'}	= 1; # Unique index works or not
  $limits{'insert_select'}	= 0;

  $limits{'func_odbc_mod'}	= 0;
  $limits{'func_extra_%'}	= 0;
  $limits{'func_odbc_floor'}	= 0;
  $limits{'func_extra_if'}	= 0;
  $limits{'column_alias'}	= 0;
  $limits{'NEG'}		= 0;
  $limits{'func_extra_in_num'}	= 0;
  $limits{'working_blobs'}	= 1; # If big varchar/blobs works
  $limits{'order_by_unused'}	= 1;
  $limits{'working_all_fields'} = 1;
  $limits{'multi_distinct'}     = 1; # allows select count(distinct a),count(distinct b).. 
  return $self;
}

#
# Get the version number of the database
#

sub version
{
  my ($tmp,$dir);
  foreach $dir ("/usr/local/Hughes", "/usr/local/mSQL","/my/local/mSQL",
		"/usr/local")
  {
    if (-x "$dir/bin/msqladmin")
    {
      $tmp=`$dir/bin/msqladmin version | grep server`;
      if ($tmp =~ /^\s*(.*\w)\s*$/)
      {				# Strip pre- and endspace
	$tmp=$1;