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

 

oh also, btw.... this is really ugly, but i used this to add records to the mysql.user table and the other tables in the mysql database that were missing on the move. This script will not over write a database, table or record so I used it on the mysql database. I then ran:

update user set password = PASSWORD(user) where not user in('root','ispconfig');
flush privileges;

This added the permissions necessary for my users, resetting all of their passwords to their id (which they will be forced to change when we go live).

There may be some null fields that were missing in the insert, since I was going from mysql 4 to mysql 5, but it all seems to work so far, and I can certainly handle the changes later.

Be sure before you do this very stupid thing that you run the query:

select user from user;

and use that list of IDs for your "not user in" part of the password change update.