<p>I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP scripts on the server.</p>

<p>How do I get at the data?</p>

<p><em>I ask this question purely to record my way to do it</em></p>

[{'answer_id': 81951, 'author': 'Jrgns', 'author_id': 6681, 'author_profile': 'https://Stackoverflow.com/users/6681', 'pm_score': 4, 'selected': False, 'text': '<p>I did it by exporting to CSV, and then importing with whatever utility is available. I quite like the use of the php://output stream.</p>\n\n<pre><code>$result = $db_con-&gt;query(\'SELECT * FROM `some_table`\');\n$fp = fopen(\'php://output\', \'w\');\nif ($fp &amp;&amp; $result) {\n    header(\'Content-Type: text/csv\');\n    header(\'Content-Disposition: attachment; filename="export.csv"\');\n    while ($row = $result-&gt;fetch_array(MYSQLI_NUM)) {\n        fputcsv($fp, array_values($row));\n    }\n    die;\n}\n</code></pre>\n'}, {'answer_id': 82016, 'author': 'Lasar', 'author_id': 9438, 'author_profile': 'https://Stackoverflow.com/users/9438', 'pm_score': 2, 'selected': False, 'text': '<p>If you have FTP/SFTP access you could just go ahead and upload phpMyAdmin yourself.</p>\n\n<p>I\'m using this little package to make automated mysql backups from a server I only have FTP access to:<br>\n<a href="http://www.taw24.de/download/pafiledb.php?PHPSESSID=b48001ea004aacd86f5643a72feb2829&amp;action=viewfile&amp;fid=43&amp;id=1" rel="nofollow noreferrer">http://www.taw24.de/download/pafiledb.php?PHPSESSID=b48001ea004aacd86f5643a72feb2829&amp;action=viewfile&amp;fid=43&amp;id=1</a><br>\nThe site is in german but the download has some english documentation as well.</p>\n\n<p>A quick google also turns up this, but I have not used it myself:<br>\n<a href="http://snipplr.com/view/173/mysql-dump/" rel="nofollow noreferrer">http://snipplr.com/view/173/mysql-dump/</a></p>\n'}, {'answer_id': 82119, 'author': 'lewis', 'author_id': 14442, 'author_profile': 'https://Stackoverflow.com/users/14442', 'pm_score': 6, 'selected': True, 'text': '<p>You could use SQL for this:</p>\n\n<pre><code>$file = \'backups/mytable.sql\';\n$result = mysql_query("SELECT * INTO OUTFILE \'$file\' FROM `##table##`");\n</code></pre>\n\n<p>Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example.</p>\n\n<p>To get it back in to your DataBase from that file you can use:</p>\n\n<pre><code>$file = \'backups/mytable.sql\';\n$result = mysql_query("LOAD DATA INFILE \'$file\' INTO TABLE `##table##`");\n</code></pre>\n\n<p>The other option is to use PHP to invoke a system command on the server and run \'mysqldump\':</p>\n\n<pre><code>$file = \'backups/mytable.sql\';\nsystem("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database | gzip &gt; ".$file);\n</code></pre>\n'}, {'answer_id': 84981, 'author': 'SeanDowney', 'author_id': 5261, 'author_profile': 'https://Stackoverflow.com/users/5261', 'pm_score': -1, 'selected': False, 'text': '<p>I use mysqldump via the command line :</p>\n\n<pre><code>exec("mysqldump sourceDatabase -uUsername -p\'password\'  &gt; outputFilename.sql");\n</code></pre>\n\n<p>Then you just download the resulting file and your done.</p>\n'}, {'answer_id': 85332, 'author': 'DreamWerx', 'author_id': 15487, 'author_profile': 'https://Stackoverflow.com/users/15487', 'pm_score': 2, 'selected': False, 'text': '<p>You might consider looking at: <a href="http://www.webyog.com" rel="nofollow noreferrer">http://www.webyog.com</a>\nThis is a great GUI admin tool, and they have a really neat HTTP-Tunneling feature (I\'m not sure if this is only in enterprise which costs a few bucks).  </p>\n\n<p>Basically you upload a script they provide into your webspace (php script) and point sqlyog manager to it and you can access the database(s).  It uses this script to tunnel/proxy the requests/queries between your home client and the server.</p>\n\n<p>I know at least 1 person who uses this method with great results.</p>\n'}, {'answer_id': 97208, 'author': 'Shinhan', 'author_id': 18219, 'author_profile': 'https://Stackoverflow.com/users/18219', 'pm_score': 4, 'selected': False, 'text': '<p>You should also consider <a href="http://phpminadmin.sourceforge.net/" rel="noreferrer">phpMinAdmin</a> which is only one file, so its easy to upload and setup.</p>\n'}, {'answer_id': 30223691, 'author': 'T.Todua', 'author_id': 2377343, 'author_profile': 'https://Stackoverflow.com/users/2377343', 'pm_score': 3, 'selected': False, 'text': '<p><strong>WORKING SOLUTION</strong> (latest version at:\n<a href="https://github.com/tazotodua/useful-php-scripts/blob/master/export-mysql-database-sql-backup.php" rel="noreferrer"><strong>Export.php</strong></a> + <a href="https://github.com/tazotodua/useful-php-scripts/blob/master/import-sql-database-mysql.php" rel="noreferrer"><strong>Import.php</strong></a> )</p>\n\n<pre><code>EXPORT_TABLES("localhost","user","pass","db_name");\n</code></pre>\n\n<p>CODE:</p>\n\n<pre><code>//https://github.com/tazotodua/useful-php-scripts\nfunction EXPORT_TABLES($host,$user,$pass,$name,  $tables=false, $backup_name=false ){\n    $mysqli = new mysqli($host,$user,$pass,$name); $mysqli-&gt;select_db($name); $mysqli-&gt;query("SET NAMES \'utf8\'");\n    $queryTables = $mysqli-&gt;query(\'SHOW TABLES\'); while($row = $queryTables-&gt;fetch_row()) { $target_tables[] = $row[0]; }   if($tables !== false) { $target_tables = array_intersect( $target_tables, $tables); }\n    foreach($target_tables as $table){\n        $result = $mysqli-&gt;query(\'SELECT * FROM \'.$table);  $fields_amount=$result-&gt;field_count;  $rows_num=$mysqli-&gt;affected_rows;     $res = $mysqli-&gt;query(\'SHOW CREATE TABLE \'.$table); $TableMLine=$res-&gt;fetch_row();\n        $content = (!isset($content) ?  \'\' : $content) . "\\n\\n".$TableMLine[1].";\\n\\n";\n        for ($i = 0, $st_counter = 0; $i &lt; $fields_amount;   $i++, $st_counter=0) {\n            while($row = $result-&gt;fetch_row())  { //when started (and every after 100 command cycle):\n                if ($st_counter%100 == 0 || $st_counter == 0 )  {$content .= "\\nINSERT INTO ".$table." VALUES";}\n                    $content .= "\\n(";\n                    for($j=0; $j&lt;$fields_amount; $j++)  { $row[$j] = str_replace("\\n","\\\\n", addslashes($row[$j]) ); if (isset($row[$j])){$content .= \'"\'.$row[$j].\'"\' ; }else {$content .= \'""\';}     if ($j&lt;($fields_amount-1)){$content.= \',\';}      }\n                    $content .=")";\n                //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler\n                if ( (($st_counter+1)%100==0 &amp;&amp; $st_counter!=0) || $st_counter+1==$rows_num) {$content .= ";";} else {$content .= ",";} $st_counter=$st_counter+1;\n            }\n        } $content .="\\n\\n\\n";\n    }\n    $backup_name = $backup_name ? $backup_name : $name."___(".date(\'H-i-s\')."_".date(\'d-m-Y\').")__rand".rand(1,11111111).".sql";\n    header(\'Content-Type: application/octet-stream\');   header("Content-Transfer-Encoding: Binary"); header("Content-disposition: attachment; filename=\\"".$backup_name."\\"");  echo $content; exit;\n}\n</code></pre>\n'}, {'answer_id': 32117725, 'author': 'Vali Munteanu', 'author_id': 4800442, 'author_profile': 'https://Stackoverflow.com/users/4800442', 'pm_score': 2, 'selected': False, 'text': '<p>Here is a <code>PHP</code> script I made which will backup all tables in your database. It is based on this <a href="http://davidwalsh.name/backup-mysql-database-php" rel="nofollow">http://davidwalsh.name/backup-mysql-database-php</a> with some improvements. First of all it will correctly set up <code>foreign key restrictions</code>.</p>\n\n<p>In my set up the script will run on a certain day of the week, let\'s say Monday. In case it did not run on Monday, it will still run on Tuesday (for example), creating the <code>.sql</code> file with the date of previous Monday, when it was supposed to run. It will erase <code>.sql</code> file from 4 weeks ago, so it always keeps the last 4 backups. Here\'s the code: </p>\n\n<pre><code>&lt;?php\n\nbackup_tables();\n\n// backup all tables in db\nfunction backup_tables()\n{\n    $day_of_backup = \'Monday\'; //possible values: `Monday` `Tuesday` `Wednesday` `Thursday` `Friday` `Saturday` `Sunday`\n    $backup_path = \'databases/\'; //make sure it ends with "/"\n    $db_host = \'localhost\';\n    $db_user = \'root\';\n    $db_pass = \'\';\n    $db_name = \'movies_database_1\';\n\n    //set the correct date for filename\n    if (date(\'l\') == $day_of_backup) {\n        $date = date("Y-m-d");\n    } else {\n        //set $date to the date when last backup had to occur\n        $datetime1 = date_create($day_of_backup);\n        $date = date("Y-m-d", strtotime($day_of_backup.\' -7 days\'));\n    }\n\n    if (!file_exists($backup_path.$date.\'-backup\'.\'.sql\')) {\n\n        //connect to db\n        $link = mysqli_connect($db_host,$db_user,$db_pass);\n        mysqli_set_charset($link,\'utf8\');\n        mysqli_select_db($link,$db_name);\n\n        //get all of the tables\n        $tables = array();\n        $result = mysqli_query($link, \'SHOW TABLES\');\n        while($row = mysqli_fetch_row($result))\n        {\n            $tables[] = $row[0];\n        }\n\n        //disable foreign keys (to avoid errors)\n        $return = \'SET FOREIGN_KEY_CHECKS=0;\' . "\\r\\n";\n        $return.= \'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";\' . "\\r\\n";\n        $return.= \'SET AUTOCOMMIT=0;\' . "\\r\\n";\n        $return.= \'START TRANSACTION;\' . "\\r\\n";\n\n        //cycle through\n        foreach($tables as $table)\n        {\n            $result = mysqli_query($link, \'SELECT * FROM \'.$table);\n            $num_fields = mysqli_num_fields($result);\n            $num_rows = mysqli_num_rows($result);\n            $i_row = 0;\n\n            //$return.= \'DROP TABLE \'.$table.\';\'; \n            $row2 = mysqli_fetch_row(mysqli_query($link,\'SHOW CREATE TABLE \'.$table));\n            $return.= "\\n\\n".$row2[1].";\\n\\n"; \n\n            if ($num_rows !== 0) {\n                $row3 = mysqli_fetch_fields($result);\n                $return.= \'INSERT INTO \'.$table.\'( \';\n                foreach ($row3 as $th) \n                { \n                    $return.= \'`\'.$th-&gt;name.\'`, \'; \n                }\n                $return = substr($return, 0, -2);\n                $return.= \' ) VALUES\';\n\n                for ($i = 0; $i &lt; $num_fields; $i++) \n                {\n                    while($row = mysqli_fetch_row($result))\n                    {\n                        $return.="\\n(";\n                        for($j=0; $j&lt;$num_fields; $j++) \n                        {\n                            $row[$j] = addslashes($row[$j]);\n                            $row[$j] = preg_replace("#\\n#","\\\\n",$row[$j]);\n                            if (isset($row[$j])) { $return.= \'"\'.$row[$j].\'"\' ; } else { $return.= \'""\'; }\n                            if ($j&lt;($num_fields-1)) { $return.= \',\'; }\n                        }\n                        if (++$i_row == $num_rows) {\n                            $return.= ");"; // last row\n                        } else {\n                            $return.= "),"; // not last row\n                        }   \n                    }\n                }\n            }\n            $return.="\\n\\n\\n";\n        }\n\n        // enable foreign keys\n        $return .= \'SET FOREIGN_KEY_CHECKS=1;\' . "\\r\\n";\n        $return.= \'COMMIT;\';\n\n        //set file path\n        if (!is_dir($backup_path)) {\n            mkdir($backup_path, 0755, true);\n        }\n\n        //delete old file\n        $old_date = date("Y-m-d", strtotime(\'-4 weeks\', strtotime($date)));\n        $old_file = $backup_path.$old_date.\'-backup\'.\'.sql\';\n        if (file_exists($old_file)) unlink($old_file);\n\n        //save file\n        $handle = fopen($backup_path.$date.\'-backup\'.\'.sql\',\'w+\');\n        fwrite($handle,$return);\n        fclose($handle);\n    }\n}\n\n?&gt;\n</code></pre>\n'}, {'answer_id': 56603821, 'author': 'Teepeemm', 'author_id': 2336725, 'author_profile': 'https://Stackoverflow.com/users/2336725', 'pm_score': 1, 'selected': False, 'text': '<p>I found that I didn\'t have enough permissions for <code>SELECT * INTO OUTFILE</code>.\nBut I was able to use enough php (iterating and imploding) to really cut down on the nested loops compared to other approaches.</p>\n\n<pre><code>$dbfile = tempnam(sys_get_temp_dir(),\'sql\');\n\n// array_chunk, but for an iterable\nfunction iter_chunk($iterable,$chunksize) {\n    foreach ( $iterable as $item ) {\n        $ret[] = $item;\n        if ( count($ret) &gt;= $chunksize ) {\n            yield $ret;\n            $ret = array();\n        }\n    }\n    if ( count($ret) &gt; 0 ) {\n        yield $ret;\n    }\n}\n\nfunction tupleFromArray($assocArr) {\n    return \'(\'.implode(\',\',array_map(function($val) {\n        return \'"\'.addslashes($val).\'"\';\n    },array_values($assocArr))).\')\';\n}\n\nfile_put_contents($dbfile,"\\n-- Table $table --\\n/*\\n");\n$description = $db-&gt;query("DESCRIBE `$table`");\n$row = $description-&gt;fetch_assoc();\nfile_put_contents($dbfile,implode("\\t",array_keys($row))."\\n",FILE_APPEND);\nforeach ( $description as $row ) {\n    file_put_contents($dbfile,implode("\\t",array_values($row))."\\n",FILE_APPEND);\n}\nfile_put_contents($dbfile,"*/\\n",FILE_APPEND);\nfile_put_contents($dbfile,"DROP TABLE IF EXISTS `$table`;\\n",FILE_APPEND);\nfile_put_contents($dbfile,array_pop($db-&gt;query("SHOW CREATE TABLE `$table`")-&gt;fetch_row()),FILE_APPEND);\n$ret = $db-&gt;query("SELECT * FROM `$table`");\n$chunkedData = iter_chunk($ret,1023);\nforeach ( $chunkedData as $chunk ) {\n    file_put_contents($dbfile, "\\n\\nINSERT INTO `$table` VALUES " . implode(\',\',array_map(\'tupleFromArray\',$chunk)) . ";\\n", FILE_APPEND );\n}\nreadfile($dbfile);\nunlink($dbfile);\n</code></pre>\n\n<p>If you have tables with foreign keys, this approach can still work if you drop\nthem in the correct order and then recreate them in the correct (reverse) order.\nThe <code>CREATE</code> statement will create the foreign key dependency for you.\nGo through <code>SELECT * FROM information_schema.referential_constraints</code> to\ndetermine that order.</p>\n\n<p>If your foreign keys have a circular dependency, then there is no possible order to drop or create. In that case, you might be able to follow the lead of phpMyAdmin, which creates all of the foreign keys at the end.  But this also means that you have to adjust the <code>CREATE</code> statements.</p>\n'}]
['https://Stackoverflow.com/questions/81934', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6681/']