Exporting Problems!

influx

I am trying to transfer my website from one server host to another.

The only problem I am facing right now is that my old PhpMyAdmin will not export databases correctly for some reason (I have tried restarting it with no result).

Is there a different approach to exporting from old server/importing to new server, like using the command line to do it?

Since I am not too familiar with SQL statements, I was wondering if you all could help me throw together an SQL statement or two to export my whole database (called 'ws', with 12 tables) from my old to my new server.

Note, my old server had: phpMyAdmin 2.6.1, MySQL 3.23.58
my new server has: phpMyAdmin 2.6.4-pl2, MySQL 4.1.13-standard-log

I hope this discrepancy doesn't cause any problems, I don't imagine it would.

Thanks a lot!
-influxer

leatherback

Whip, whip, whip..
He throws and..

try this:

<?php

function get_structure($server, $user, $pass, $db)
{

mysql_connect($server, $user, $pass);
mysql_select_db($db);
$tables = mysql_list_tables($db);
while ($td = mysql_fetch_array($tables))
{
$table = $td[0];
$delete .= "DROP TABLE IF EXISTS $table;
";
$r = mysql_query("Show CREATE TABLE `$table`");
if ($r)
  {
  $insert_sql = "";
  $d = mysql_fetch_array($r);
  $d[1] .= ";";
  $SQL[] = str_replace("\n", "", $d[1]);
  $table_query = mysql_query("SELECT * FROM `$table`");
  $num_fields = mysql_num_fields($table_query);
  while ($fetch_row = mysql_fetch_array($table_query))
    {
    $insert_sql .= "INSERT INTO $table VALUES(";
    for ($n=1;$n<=$num_fields;$n++)
      {
      $m = $n - 1;
      $insert_sql .= "'".mysql_escape_string($fetch_row[$m])."', ";
      }

$insert_sql = substr($insert_sql,0,-2);
$insert_sql .= ");\n";
}

  if ($insert_sql!= "")
    {
    $SQL[] = $insert_sql;
    }
  }
}
$out = $delete.implode("\r", $SQL);
return $out;

}

  //*************************************************************
  // END OF DECLARATIONS and function definition ****************
  // ************************************************************

// We maken een bestand aan
header("Content-type: application/txt");

// Met datum in de naam
$filename = "Database_backup_".date("F_j_Y").".txt";
header("Content-Disposition: attachment; filename=$filename");


echo get_structure($db_host, $db_user, $db_password, $db_name);

exit();

//phpinfo();

?>

influx

thanks a lot.

would you mind telling me where you found this and whether it's trusted or not? Is it widely-used?

I have a very big database I need to transfer and any small discrepancy can cause major problems.

Thanks!

Roger_Ramjet

You can do it from the command line either as a binary dump to a tar file, or by using mysqldump

From the manual

The easiest way to create a snapshot is to use an archiving program to make a binary backup of the databases in your master's data directory. For example, use tar on Unix, or PowerArchiver, WinRAR, WinZip, or any similar software on Windows. To use tar to create an archive that includes all databases, change location into the master server's data directory, then execute this command:

shell> tar -cvf /tmp/mysql-snapshot.tar .

If you want the archive to include only a database called this_db, use this command instead:

shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db

Then copy the archive file to the /tmp directory on the slave server host. On that machine, change location into the slave's data directory, and unpack the archive file using this command:

shell> tar -xvf /tmp/mysql-snapshot.tar

Or:

An alternative that works for both MyISAM and InnoDB tables is to take an SQL dump of the master instead of a binary copy as described in the preceding discussion. For this, you can use mysqldump --master-data on your master and later load the SQL dump file into your slave. However, this is slower than doing a binary copy.

leatherback

Hm.. I am not sure whether I found it anywhere.
If I recall correctly I wrote it myself for a database management toolkit I use.
ergo: use at own risk :-)

influx

Thanks for your help Roger Ramjet.

I would like to take the .tar approach to transferring my MySQL database from one server to the other.

My question is this, in the command:

shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db

is there a certain directory I have to be in to do this? Is there a MySQL directory on my server (it is a friend's server, I am not too familliar with running a server).

You said I need to unpack the .tar in the "slave's data directory", where could I find that?

Thanks!

Roger_Ramjet

You need to be in the actual directory that the database files are in on both servers when you run those commands. OR use the fully qualified path from any other directory. You would also substitue the file name of your database file for the ./this_db

(./ means this directory)