CODEWITHSUNDEEP
phpbackupmysql
Frederick Behrends
Frederick Behrends

Reputation: 3095

create MySQL dump in pure PHP5

I'm creating a backup script for my project, which will be executed via cron.

Right now I'm using:

<?php
include("config.php");

/* Misc */
$pathtobackup = realpath('.');
$backupfolder = realpath("backup");
$sqlfilename = "SQLDUMP-".date("d-m-Y--H-i").".sql";
$backupfilename = "BACKUP-".date("d-m-Y--H-i").".zip";
$backupfilepath = $backupfolder."/".$backupfilename;

/* Systemcall */
exec("/usr/bin/mysqldump -u{$mysqluser} -p{$mysqlpw} -h {$mysqlserver} {$mysqldb}", $sqldata, $fp);

$sqldump = implode("\n", $sqldata);

if ($fp!=0){
    die("could not create mysql dump\n");
}

/* Create Zip */
$zip = new ZipArchive();

if ($zip->open($backupfilepath, ZIPARCHIVE::CREATE)!==TRUE) {
    die("cannot open <{$backupfilepath}>\n");
}


$path = realpath('.');

$iterator  = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($pathtobackup, FilesystemIterator::SKIP_DOTS));

foreach($iterator as $name=>$object){

    $zip->addFile($name);

}

$zip->addFromString($sqlfilename, $sqldump);

if ($zip->close()!==TRUE) {
    die("cannot write <{$backupfilepath}>\n");
}

?>

But i would like to create the MySQL Dump with pure PHP to make it more compatible and be able to use it on other servers too, where there is no way to use system and mysqldump.

I hope you know a solution with out using an 150 file libary :)

Greetings, Frederick

Upvotes: 4

Views: 1961

Answers (2)

diego
diego

Reputation: 539

Take a look here: https://github.com/ifsnop/mysqldump-php ! It is a native solution written in php, with tests, that support stored procedures, triggers, views hexblobs, regexps...

You can install it using composer, and it is as easy as doing:

<?php

use Ifsnop\Mysqldump as IMysqldump;

try {
    $dump = new IMysqldump\Mysqldump('database', 'username', 'password');
    $dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
    echo 'mysqldump-php error: ' . $e->getMessage();
}

?>

It supports advanced users, with lots of options copied from the original mysqldump.

All the options are explained at the github page, but more or less are auto-explicative (small snippet from documentation):

$dumpSettingsDefault = array(
    'include-tables' => array(),
    'exclude-tables' => array(),
    'compress' => 'None',
    'no-data' => false,
    'add-drop-database' => false,
    'add-drop-table' => false,
    'single-transaction' => true,
    'lock-tables' => false,
    'add-locks' => true,
    'extended-insert' => true,
    'disable-foreign-keys-check' => false,
    'where' => '',
    'no-create-info' => false
);

Upvotes: 0

WojtekT
WojtekT

Reputation: 4775

If you really have to do it this way then here are few mysql queries that you should consider while creating such script:

SHOW TABLES - returns all database table names

SHOW CREATE TABLE table_name returns the sql query used to create a table

SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS - returns list of stored procedures and functions

SHOW CREATE PROCEDURE procedure_name and SHOW CREATE FUNCTION function_name -return queries used to create stored procedures and functions

The rest are usual SELECT and INSERT queries.

Upvotes: 1

Related Questions