· 8 years ago · Apr 30, 2018, 01:58 PM
1<?php
2$dbname = 'db';
3
4 // old server mysql id
5 $DBIUser = 'someuser';
6 $DBIPass = 'thepassword';
7
8
9 // new server mysql id
10 $NewUser = 'someloser';
11 $NewPass = 'thepassword';
12
13 // server names
14 $oldServer = 'my crappy old mysql server domain';
15 $newServer = 'localhost';
16
17 echo "Starting copy of the $dbname database.\n";
18 $dbpre = mysql_connect($oldServer, $DBIUser, $DBIPass);
19 mysql_select_db($dbname, $dbpre);
20 $sql = "SHOW TABLES FROM $dbname";
21 echo $sql."\n";
22 $result = mysql_query($sql);
23
24 if (!$result)
25 {
26 echo "DB Error, could not list tables\n";
27 echo 'MySQL Error: ' . mysql_error();
28 exit;
29 }
30
31 $dbtbl = mysql_connect($oldServer, $DBIUser, $DBIPass);
32 mysql_select_db($dbname, $dbpre);
33 $dbnew = mysql_connect($newServer, $NewUser, $NewPass);
34 mysql_select_db("mysql", $dbnew);
35
36 $res2 = mysql_query("CREATE DATABASE IF NOT EXISTS ".$dbname,$dbnew);
37 if (!$res2)
38 {
39 echo "DB Error, could not create database\n";
40 echo 'MySQL Error: ' . mysql_error();
41 exit;
42 }
43 mysql_select_db($dbname, $dbnew);
44
45
46 if($result === FALSE)
47 {
48 die(mysql_error());
49 }
50
51 $f = fopen($dbname.'.log', 'w');
52 fwrite($f, "Copy all tables in database $dbname on server $oldServer to new database on server $newServer.\n\n");
53 while ($row = mysql_fetch_row($result))
54 {
55 echo "Table: {$row[0]}\n";
56 fwrite($f, "Table ".$row[0]."\n");
57 $tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $row[0] ",$dbtbl));
58 $createsyntax = "CREATE TABLE IF NOT EXISTS ";
59 $createsyntax .= substr($tableinfo[1], 13);
60
61 //echo $row[0]."\n";
62
63 mysql_query(" $createsyntax ",$dbnew);
64
65
66 $res = mysql_query("SELECT * FROM $row[0] ",$dbpre); // select all rows
67 $oldcnt = mysql_num_rows($res);
68 echo "Count: ".$oldcnt." - ";
69
70 $errors = 0;
71 while ($roz = mysql_fetch_array($res, MYSQL_ASSOC) )
72 {
73 $query = "INSERT INTO $dbname.$row[0] (".implode(", ",array_keys($roz)).") VALUES (";
74 $cnt = 0;
75 foreach (array_values($roz) as $value)
76 {
77 if ($cnt == 0)
78 {
79 $cnt++;
80 } else
81 {
82 $query .= ",";
83 }
84 $query .= "'";
85 $query .= mysql_real_escape_string($value);
86 $query .= "'";
87
88 }
89 $query .= ")";
90
91 $look = mysql_query($query,$dbnew);
92 if ($look === false)
93 {
94 // write insert to log on error
95 $errors = $errors + 1;
96 fwrite($f, mysql_error()." - ".$query."\n");
97 }
98
99 }
100 $sql = "select count(*) as cnt from $dbname.$row[0] ";
101 $res = mysql_query($sql, $dbnew);
102 $roz = mysql_fetch_array($res);
103 echo $roz['cnt']." - Errors: ".$errors."\n";
104 fwrite($f, "Old Record Count: ".$oldcnt." - New Record Count: ".$roz['cnt']." - Errors: ".$errors."\n");
105 fwrite($f,"End table copy for table $row[0].\n\n");
106
107 }
108 fclose($f);
109
110
111 }
112
113
114
115?>