Backup database(s) using query without using mysqldump

backup, mysql

Solution

Use `mysqldump-php` a pure-PHP solution to replicate the function of the `mysqldump` executable for basic to med complexity use cases - I understand you may not have remote CLI and/or mysql direct access, but so long as you can execute via an HTTP request on a httpd on the host this will work:

So you should be able to just run the following purely PHP script straight from a secure-directory in /www/ and have an output file written there and grab it with a wget.

mysqldump-php - Pure PHP mysqldump on GitHub

PHP example:

<?php
require('database_connection.php');
require('mysql-dump.php')
$dumpSettings = array(
    'include-tables' => array('table1', 'table2'),
    'exclude-tables' => array('table3', 'table4'),
    'compress' => CompressMethod::GZIP, /* CompressMethod::[GZIP, BZIP2, NONE] */
    'no-data' => false,            
    'add-drop-table' => false,      
    'single-transaction' => true,   
    'lock-tables' => false,        
    'add-locks' => true,            
    'extended-insert' => true      
);

$dump = new MySQLDump('database','database_user','database_pass','localhost', $dumpSettings);
$dump->start('forum_dump.sql.gz');
    ?>

Problem

I'd like to dump my databases to a file. Certain website hosts don't allow remote or command line access, so I have to do this using a series of queries. All of the related questions say "use `mysqldump`" which is a great tool but I don't have command line access to this database. I'd like `CREATE` and `INSERT` commands to be created at the same time - basically, the same performance as `mysqldump`. Is `SELECT INTO OUTFILE` the right road to travel, or is there something else I'm overlooking - or maybe it's not possible?

Original source

Related problems