CODEWITHSUNDEEP
phpmysql
Cookiimonstar
Cookiimonstar

Reputation: 421

Transferring MySQL DB To New Server

I am trying to transfer Data from my old MySql Server to my new server, I have tried using SQL Dumper, but i keeping getting Error 500 The tables are a mess half been MyISAM and the other half been InnoDB and the server is on its last legs to be honest.

I am thinking of using iframes and GET requests to transfer the data (example below). If anyone has any better ideas it would be much appreciated.

The table i am transferring has 36 Million records and i have tested it and a rough calculation it will take over to months to transfer all the records. I knows the method below is probably the most inefficient way to do it but i can't think of any other way to do it. 

     $result = mysql_query("SELECT * FROM nu_list $limit");
        while($row = mysql_fetch_array( $result )) {

    print "<iframe src="http://mynewsite.com/add.php?row1=$row[1]&row2=$row[2]&row3=$row[3]" 
frameborder="0" scrolling="no" WIDTH="1" HEIGHT="1">\n";  } 


        $nxt = $_GET["pageno"] + 1;

        if($nxt < $lastpage) {
        print "<BODY>
        <script type=\"text/javascript\">
        <!--
        window.location = \"/ripw3.php?pageno=$nxt&lastpage=$lastpage\"
        //-->
        </script>
        </BODY>
        </HTML>\n";

Upvotes: 2

Views: 123

Answers (2)

Jage
Jage

Reputation: 8096

If you have access to the server and can shell to it, you should use mysql dump to dump a copy of the database. Then transfer that sql file to the new server and import it.

This site provides a solid mysql dump import / export tutorial.

If you're hell-bent on getting this done with php, there are some folks who have written scripts to backup mysql with php. The basic idea is to show all tables, loop through them and describe each table in order to build create table statements, and then select the data in each table in small chunks and manually building insert statements. That's an awful pain in comparison to the dump though.

If you're on a shared host you probably can't use mysql dump, which might explain your error 500. If you're on a shared host though, you probably could download a copy of your database from your control panel.

Upvotes: 2

user188654
user188654

Reputation:

You can use MySQL Workbench to do a complete export of your databases and pt-show-grants from Percona Tookit to export users and privileges.

Upvotes: 1

Related Questions