View Single Post
  #1  
Old 17th August 2012, 19:26
dannypb dannypb is offline
Junior Member
 
Join Date: Oct 2011
Posts: 15
Thanks: 0
Thanked 4 Times in 3 Posts
Default PhP script to copy mysql tables from one server to another by database

Hi,

I had to copy all tables and records from a mysql 4.1 server with php 4.3.4 to our new server with mysql 5.1 with php 5.3.3. I was having issues with dumps and import and mysqldump because of the different versions. The last time I tried it, I got no errors, but a ton of records were missing. So I decided to find a script to do this for me on a record by record basis. I only found pieces of scripts, so I stole all of you guyzez work and slapped it together with a few of my own things.

This script worked successfully for me (in linux RHEL 6.3). Feel free to use it and take credit for it as you like, it's all your work anyway . Please use it on the command line on the new server (the PHP DBI on the old server will not connect to the new server's more advanced interface). I created it's own directory, since the log files for each database will be created where the script is located. I'm a "by the numbers" guy, so:

1. Save this code to it's own file, for instance "dbcopy.php".
2. Replace the $DBIuser and $DBIpass with your own userid and password for the old mysql server.
3. Replace the $newUser and $newPass with your own userid and password for the new server.
4. Replace the $oldServer and the $oldServer with the IPs or domain names of your old and new servers.
5. Create a directory for the file on your server, for instance "/tmp/db".
6. Copy or move the file to that directory.
7. in that directory at the command prompt type "php [file name] [database name]".
8. The script will give feedback as it's working on the command line.
9. The script will create the database and tables if they don't exist.
10. It will attempt to insert records into each table.
11. There will be a log file with the name [database name].log in the directory.
12. Any inserts that fail will be there in the log to rerun after you correct them. They will also have the mysql error for review.
13. The log also contains the old table record count, the new tables record count and the number of insert errors.

Here is the script, it has minimal error checking, no OO and is basic. But I thought it might help someone else save some time.

Code:
<?php

  // old server mysql id
  $DBIUser = 'someuser';
  $DBIPass = 'thepassword';

  // new server mysql id
  $NewUser = 'someloser';
  $NewPass = 'thepassword';
  
  // server names
  $oldServer = 'my crappy old mysql server domain';
  $newServer = 'localhost';

  if ($argv[0] > " ")
  {
    $dbname = $argv[1];
    echo "Starting copy of the $argv[1] database.\n";
    $dbpre = mysql_connect($oldServer, $DBIUser, $DBIPass);
    mysql_select_db($dbname, $dbpre);
    $sql = "SHOW TABLES FROM $dbname";
    echo $sql."\n";
	$result = mysql_query($sql);

	if (!$result)
	{
	    echo "DB Error, could not list tables\n";
	    echo 'MySQL Error: ' . mysql_error();
	    exit;
	}

    $dbtbl = mysql_connect($oldServer, $DBIUser, $DBIPass);
    mysql_select_db($dbname, $dbpre);
    $dbnew = mysql_connect($newServer, $NewUser, $NewPass);
    mysql_select_db("mysql", $dbnew);

    $res2 = mysql_query("CREATE DATABASE IF NOT EXISTS ".$dbname,$dbnew);
    if (!$res2)
	{
		    echo "DB Error, could not create database\n";
		    echo 'MySQL Error: ' . mysql_error();
		    exit;
	}
    mysql_select_db($dbname, $dbnew);


    if($result === FALSE)
    {
	    die(mysql_error());
    }

    $f = fopen($dbname.'.log', 'w');
    fwrite($f, "Copy all tables in database $dbname on server $oldServer to new database on server $newServer.\n\n");
	while ($row = mysql_fetch_row($result))
	{
	    echo "Table: {$row[0]}\n";
	    fwrite($f, "Table ".$row[0]."\n");
	    $tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $row[0]  ",$dbtbl));
		$createsyntax = "CREATE TABLE IF NOT EXISTS ";
		$createsyntax .= substr($tableinfo[1], 13);

		//echo $row[0]."\n";

        mysql_query(" $createsyntax ",$dbnew);


        $res = mysql_query("SELECT * FROM $row[0]  ",$dbpre); // select all rows
        $oldcnt = mysql_num_rows($res);
        echo "Count: ".$oldcnt." - ";

		$errors = 0;
		while ($roz = mysql_fetch_array($res, MYSQL_ASSOC) )
		{
		  $query =  "INSERT INTO $dbname.$row[0] (".implode(", ",array_keys($roz)).") VALUES (";
		  $cnt = 0;
		  foreach (array_values($roz) as $value)
		  {
		    if ($cnt == 0)
		    {
		      $cnt++;
		    } else
		    {
		      $query .= ",";
		    }
		    $query .= "'";
		    $query .= mysql_real_escape_string($value);
		    $query .= "'";

		  }
		  $query .= ")";

		  $look = mysql_query($query,$dbnew);
		  if ($look === false)
		  {
		    // write insert to log on error
		    $errors = $errors + 1;
		    fwrite($f, mysql_error()." - ".$query."\n");
		  }

        }
        $sql = "select count(*) as cnt from $dbname.$row[0] ";
        $res = mysql_query($sql, $dbnew);
        $roz = mysql_fetch_array($res);
        echo $roz['cnt']." - Errors: ".$errors."\n";
        fwrite($f, "Old Record Count: ".$oldcnt." - New Record Count: ".$roz['cnt']." - Errors: ".$errors."\n");
        fwrite($f,"End table copy for table $row[0].\n\n");

    }
    fclose($f);


  } else
  {
    var_dump($argv);


  }



?>
Don't ever say I never gave back

Last edited by dannypb; 17th August 2012 at 22:07.
Reply With Quote
Sponsored Links