Mysql: Easy way to export a SQL table without access to the server or phpMyADMIN

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.

How do I get at the data?

I ask this question purely to record my way to do it

 Answers

You could use SQL for this:

$file = 'backups/mytable.sql';
$result = mysql_query("SELECT * INTO OUTFILE '$file' FROM `##table##`");

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.

To get it back in to your DataBase from that file you can use:

$file = 'backups/mytable.sql';
$result = mysql_query("LOAD DATA INFILE '$file' INTO TABLE `##table##`");

The other option is to use PHP to invoke a system command on the server and run 

'mysqldump':

$file = 'backups/mytable.sql';

system("mysqldump --opt -h ##databaseserver## -u ##username## -p ##password## ##database |

 gzip > ".$file);

---

You should also consider phpMinAdmin which is only one file, so its easy to upload 

and setup.

---

If you have FTP/SFTP access you could just go ahead and upload phpMyAdmin 

yourself.

I'm using this little package to make automated mysql backups from a server I 

only have FTP access to:
A quick google also turns up this, but I have not used it myself:

https://thiscode4u.blogspot.com/2018/11/creates-backup-of-mysql-database-in-sql.html

---

Here is a PHP script I made which will backup all tables in your database. It is based 

on this

 https://thiscode4u.blogspot.com/2018/11/backup-your-mysql-database-using-php.html 

with some improvements. 

First of all it will correctly set up foreign key restrictions.

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 .sql file with the date of previous Monday, when it was supposed to run.

 It will erase .sql file from 4 weeks ago, so it always keeps the last 4 backups. 

Here's the code:

<?php



backup_tables();



// backup all tables in db

function backup_tables()

{

    $day_of_backup = 'Monday'; 

//possible values: `Monday` `Tuesday` `Wednesday` `Thursday` `Friday` `Saturday` `Sunday`

    $backup_path = 'databases/'; //make sure it ends with "/"

    $db_host = 'localhost';
$db_user = 'root';

    } else {

    $db_pass = '';

    $db_name = 'movies_database_1';

    //set the correct date for filename



    if (date('l') == $day_of_backup) {

        $date = date("Y-m-d");

        $date = date("Y-m-d", strtotime($day_of_backup.' -7 days'));

        //set $date to the date when last backup had to occur

        $datetime1 = date_create($day_of_backup);
}


        //connect to db

    if (!file_exists($backup_path.$date.'-backup'.'.sql')) {

        $result = mysqli_query($link, 'SHOW TABLES');

        $link = mysqli_connect($db_host,$db_user,$db_pass);

        mysqli_set_charset($link,'utf8');

        //get all of the tables

        mysqli_select_db($link,$db_name);
$tables = array();

        $return.= 'START TRANSACTION;' . "\r\n";

        while($row = mysqli_fetch_row($result))
{

            $tables[] = $row[0];

        }

        //disable foreign keys (to avoid errors)

        $return = 'SET FOREIGN_KEY_CHECKS=0;' . "\r\n";

        $return.= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . "\r\n";

        $return.= 'SET AUTOCOMMIT=0;' . "\r\n";
//cycle through

            $row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table));

        foreach($tables as $table)

        {

            $result = mysqli_query($link, 'SELECT * FROM '.$table);

            $num_fields = mysqli_num_fields($result);

            //$return.= 'DROP TABLE '.$table.';'; 

            $num_rows = mysqli_num_rows($result);
$i_row = 0;


                $return = substr($return, 0, -2);

            $return.= "\n\n".$row2[1].";\n\n"; 



            if ($num_rows !== 0) {

                $row3 = mysqli_fetch_fields($result);

                $return.= 'INSERT INTO '.$table.'( ';

                    $return.= '`'.$th->name.'`, '; 

                foreach ($row3 as $th) 
{ 
                }

                            $row[$j] = addslashes($row[$j]);

                $return.= ' ) VALUES';



                for ($i = 0; $i < $num_fields; $i++) 

                {

                    while($row = mysqli_fetch_row($result))

                    {

                        $return.="\n(";

                        {

                        for($j=0; $j<$num_fields; $j++) 

                            $return.= ");"; // last row

                            $row[$j] = preg_replace("#\n#","\\n",$row[$j]);

                            if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }

                            if ($j<($num_fields-1)) { $return.= ','; }
}

            mkdir($backup_path, 0755, true);

                        if (++$i_row == $num_rows) {
} else {

                            $return.= "),"; // not last row

                        }   
}

            $return.="\n\n\n";

                }
}

        $return .= 'SET FOREIGN_KEY_CHECKS=1;' . "\r\n";
}


        // enable foreign keys
$return.= 'COMMIT;';


        //delete old file

        //set file path

        if (!is_dir($backup_path)) {
}


?>

        $old_date = date("Y-m-d", strtotime('-4 weeks', strtotime($date)));

        $old_file = $backup_path.$old_date.'-backup'.'.sql';

        $handle = fopen($backup_path.$date.'-backup'.'.sql','w+');

        if (file_exists($old_file)) unlink($old_file);
//save file
        fwrite($handle,$return);




        fclose($handle);
}

}