Дамп mysql через php

Using a .php file to generate a MySQL dump

Here’s the information I have: I am working with a Linux based system using MySQL and PHP5. I need to be able to generate a mysqldump from within a .php file, and then have that dump be stored in a file on the server in a location I would specify. As I’m a PHP nooblet, I’d like someone to give me some assistance, guidance, or code, that would do what I require. This would have to be run remotely from the Internet.

Do not rely on exec() or system(), because most times they are disabled on shared hostings. Answer should implement a proper way to generate a database dump without running external programs.

13 Answers 13

You can use the exec() function to execute an external command.

Note: between shell_exec() and exec() , I would choose the second one, which doesn’t return the output to the PHP script — no need for the PHP script to get the whole SQL dump as a string : you only need it written to a file, and this can be done by the command itself.

That external command will :

mysqldump --user=. --password=. --host=. DB_NAME > /path/to/output/file.sql 

Which means your PHP code would look like this :

exec('mysqldump --user=. --password=. --host=. DB_NAME > /path/to/output/file.sql'); 

Of course, up to you to use the right connection information, replacing the . with those.

To get this to work, I had to replace mysqldump with /usr/bin/mysqldump . I also had to change password=mystrongpassword to password=»mystrongpassword» .

If you want to create a backup to download it via the browser, you also can do this without using a file.

The php function passthru() will directly redirect the output of mysqldump to the browser. In this example it also will be zipped.

Pro: You don’t have to deal with temp files.

Con: Won’t work on Windows. May have limits with huge datasets.

Security concern: is that command «mysqldump . » stored somewhere in the system? I am thinking about the fact there is the password, included, in clear.

Take a look here: https://github.com/ifsnop/mysqldump-php ! It is a native solution written in php.

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

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:

$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 ); 

Источник

Дамп mysql через php

В этом разделе помещены уроки по PHP скриптам, которые Вы сможете использовать на своих ресурсах.

• 

Фильтрация данных с помощью zend-filter

Когда речь идёт о безопасности веб-сайта, то фраза «фильтруйте всё, экранируйте всё» всегда будет актуальна. Сегодня поговорим о фильтрации данных.

Контекстное экранирование с помощью zend-escaper

Обеспечение безопасности веб-сайта — это не только защита от SQL инъекций, но и протекция от межсайтового скриптинга (XSS), межсайтовой подделки запросов (CSRF) и от других видов атак. В частности, вам нужно очень осторожно подходить к формированию HTML, CSS и JavaScript кода.

Подключение Zend модулей к Expressive

Expressive 2 поддерживает возможность подключения других ZF компонент по специальной схеме. Не всем нравится данное решение. В этой статье мы расскажем как улучшили процесс подключение нескольких модулей.

Совет: отправка информации в Google Analytics через API

Предположим, что вам необходимо отправить какую-то информацию в Google Analytics из серверного скрипта. Как это сделать. Ответ в этой заметке.

Подборка PHP песочниц

Подборка из нескольких видов PHP песочниц. На некоторых вы в режиме online сможете потестить свой код, но есть так же решения, которые можно внедрить на свой сайт.

Совет: активация отображения всех ошибок в PHP

При поднятии PHP проекта на новом рабочем окружении могут возникнуть ошибки отображение которых изначально скрыто базовыми настройками. Это можно исправить, прописав несколько команд.

Источник

micc83 / mysqldump.php

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

ini_set( ‘display_errors’ , 1 );

ini_set( ‘display_startup_errors’ , 1 );

error_reporting( E_ALL );

$ database = ‘db’ ;

$ user = ‘user’ ;

$ pass = ‘pass’ ;

$ host = ‘localhost’ ;

$ dir = dirname(__FILE__) . ‘/dump.sql’ ;

echo » Backing up database to ` ` «;

exec(» mysqldump —user= < $ user >—password= < $ pass >—host= < $ host > < $ database >—result-file= < $ dir >2>&1 «, $ output );

var_dump( $ output );

Excellent script. Thanks a lot ..
By the way, if you i want to just dump the tables structures of the DB. How to do that? thanks in advance.

Error occur while running above script please help me to solve this.

array (size=2)
0 => string »mysqldump’ is not recognized as an internal or external command,’ (length=65)
1 => string ‘operable program or batch file.’ (length=31)

You have to add MySQL_Installation_Path/bin to PATH. For example if you are using MariaDB and your installation path is:

Well you have to add C:/MariaDB/bin to system path.

Just use mysqldump-php within your php code. Check out the examples or the wiki, but it is as easy as:

error_reporting(E_ALL); require_once __DIR__ . '/vendor/autoload.php'; include_once(dirname(__FILE__) . '/vendor/mysqldump-php/src/Ifsnop/main.inc.php'); use Ifsnop\Mysqldump as IMysqldump; try < $dump = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password'); $dump->start('storage/work/dump.sql'); > catch (\Exception $e) < echo 'mysqldump-php error: ' . $e->getMessage(); >

Just use mysqldump-php within your php code. Check out the examples or the wiki, but it is as easy as:

error_reporting(E_ALL); require_once __DIR__ . '/vendor/autoload.php'; include_once(dirname(__FILE__) . '/vendor/mysqldump-php/src/Ifsnop/main.inc.php'); use Ifsnop\Mysqldump as IMysqldump; try < $dump = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password'); $dump->start('storage/work/dump.sql'); > catch (\Exception $e) < echo 'mysqldump-php error: ' . $e->getMessage(); > 

If you’re in windows like me and don’t want create a ENVIRONMENT VARIABLE, you can use the mysql dir before the dump command

Backing up database to ` `"; exec(" --user= --password= --host=  --result-file= 2>&1", $output); var_dump($output); 

The script works well locally, now how can I use it externally?

Thanks a lot for this script.

I improved it with a bit of bash and a desktop launcher, it's written in French but this may interest someone:

first of all let me thank you for the amazing simple script.. it works great, but i needed to add some features to fit my project, such as save file from input form in php, where we select de name of the backup and post it to the execut backup script, and then download file in webrowser directly. attached below is a working example of the code.

And yeahhh keep up the good work lads.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

include_once("conect-db.php");

$name_backup = $_REQUEST['name_backup'];

$command='mysqldump --opt -h' .$databaseHost .' -u' .$databaseUsername .' -p' .$databasePassword .' ' .$databaseName .' > ' .$name_backup;
exec($command,$output,$worked);

$file_name = basename($name_backup);
//save the file by using base name
$fn = file_put_contents($file_name,file_get_contents($name_backup));
header("Expires: 0");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header("Content-type: application/file");
header('Content-length: '.filesize($file_name));
header('Content-disposition: attachment; filename="'.basename($file_name).'"');
readfile($file_name);

switch($worked) case 0:
echo 'The database ' .$databaseName .' was successfully stored in the following path '.getcwd().'./' .$name_backup .'';
break;
case 1:
echo 'An error occurred when exporting ' .$databaseName .' zu '.getcwd().'/' .$mysqlExportPath .'';
break;
case 2:
echo 'An export error has occurred, please check the following information:

MySQL Database Name:	' .$databaseName .'
MySQL User Name:	' .$databaseUsername .'
MySQL Password:	NOTSHOWN
MySQL Host Name:	' .$databaseHost .'

';
break;
>
?>

Источник