Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Programming/Scripts

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 17th August 2012, 20: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 23:07.
Reply With Quote
The Following User Says Thank You to dannypb For This Useful Post:
falko (18th August 2012)
Sponsored Links
  #2  
Old 17th August 2012, 21:13
dannypb dannypb is offline
Junior Member
 
Join Date: Oct 2011
Posts: 15
Thanks: 0
Thanked 4 Times in 3 Posts
 
Default

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.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
issues in /var/log/apache2/error.log denpark Installation/Configuration 1 22nd November 2011 10:12
Can't receive mails baicunko Server Operation 12 3rd August 2011 23:02
ISPConfig3 Debian Squeeze Crontab - Log petrichbg Installation/Configuration 5 22nd March 2011 13:25
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11
Problems with Postfix Mysql Courier PatrickAdrichem Installation/Configuration 3 13th April 2007 16:44


All times are GMT +2. The time now is 07:11.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.