how to take backup of mysql database using php
You may use the following code pattern directly with your task
<?php
$dbhost = $_SERVER['SERVER_NAME'];
$dbuser = 'root';
$dbpass = '';
$dbname = 'marketing';
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
$backupAlert = '';
$tables = array();
$result = mysqli_query($connection, "SHOW TABLES");
if (!$result) {
$backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
} else {
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
mysqli_free_result($result);
$return = '';
foreach ($tables as $table) {
$result = mysqli_query($connection, "SELECT * FROM " . $table);
if (!$result) {
$backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
} else {
$num_fields = mysqli_num_fields($result);
if (!$num_fields) {
$backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
} else {
$return .= 'DROP TABLE ' . $table . ';';
$row2 = mysqli_fetch_row(mysqli_query($connection, 'SHOW CREATE TABLE ' . $table));
if (!$row2) {
$backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
} else {
$return .= "\n\n" . $row2[1] . ";\n\n";
for ($i = 0; $i < $num_fields; $i++) {
while ($row = mysqli_fetch_row($result)) {
$return .= 'INSERT INTO ' . $table . ' VALUES(';
for ($j = 0; $j < $num_fields; $j++) {
$row[$j] = addslashes($row[$j]);
if (isset($row[$j])) {
$return .= '"' . $row[$j] . '"';
} else {
$return .= '""';
}
if ($j < $num_fields - 1) {
$return .= ',';
}
}
$return .= ");\n";
}
}
$return .= "\n\n\n";
}
$backup_file = $dbname . date("Y-m-d-H-i-s") . '.sql';
$handle = fopen("{$backup_file}", 'w+');
fwrite($handle, $return);
fclose($handle);
$backupAlert = 'Succesfully got the backup!';
}
}
}
}
echo $backupAlert;
?>
Backup and Restore MySQL database in PHP
Script to backup using Php
<?php
define("BACKUP_PATH", "/home/abdul/");
$server_name = "localhost";
$username = "root";
$password = "root";
$database_name = "world_copy";
$date_string = date("Ymd");
$cmd = "mysqldump --routines -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";
exec($cmd);
?>
Script to restore
<?php
$restore_file = "/home/abdul/20140306_world_copy.sql";
$server_name = "localhost";
$username = "root";
$password = "root";
$database_name = "test_world_copy";
$cmd = "mysql -h {$server_name} -u {$username} -p{$password} {$database_name} < $restore_file";
exec($cmd);
?>
Backup a mysql database and download as a file
A very simple solution would be something like (first example):
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx
Naturally this will only make a Data dump of the table.
What you could do is use this code:
http://snipplr.com/view/173/mysql-dump/
What this code does is actually gets a description of the table (i.e its structure), creates all the tables and pushes data. pretty much like any other tool does.
Then its just a matter of saving it from string to a file (file_put_contents() for instance or something similar, depending on your preference and need)
Automated or regular backup of mysql data
CSV and SELECT INTO OUTFILE
http://dev.mysql.com/doc/refman/5.7/en/select-into.html
SELECT ... INTO OUTFILE writes the selected rows to a file. Column and
line terminators can be specified to produce a specific output format.
Here is a complete example:
SELECT * FROM my_table INTO OUTFILE '/tmp/my_table.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
The file is saved on the server and the path chosen needs to be writable. Though this query can be executed through PHP and a web request, it is best executed through the mysql console.
The data that's exported in this manner can be imported into another database using LOAD DATA INFILE
While this method is superior iterating through a result set and saving to a file row by row, it's not as good as using....
mysqldump
mysqldump is superior to SELECT INTO OUTFILE in many ways, producing CSV is just one of the many things that this command can do.
The mysqldump client utility performs logical backups, producing a set
of SQL statements that can be executed to reproduce the original
database object definitions and table data. It dumps one or more MySQL
databases for backup or transfer to another SQL server. The mysqldump
command can also generate output in CSV, other delimited text, or XML
format.
Ideally mysqldump should be invoked from your shell. It is possible to use exec in php to run it but since producing the dump might take a long time depending on the amount of data, and php scripts usually run only for 30 seconds, you would need to run it as a background process.
mysqldump isn't without it's fair share of problems.
It is not intended as a fast or scalable solution for backing up
substantial amounts of data. With large data sizes, even if the backup
step takes a reasonable time, restoring the data can be very slow
because replaying the SQL statements involves disk I/O for insertion,
index creation, and so on.
A classic example see this question: Server crash on MySQL backup using python where one mysqldump seems to start before the earlier one has finished and rendered the website completely unresponsive.
Mysql replication
Replication enables data from one MySQL database server (the master)
to be copied to one or more MySQL database servers (the slaves).
Replication is asynchronous by default; slaves do not need to be
connected permanently to receive updates from the master. Depending on
the configuration, you can replicate all databases, selected
databases, or even selected tables within a database.
Thus replication operates differently from SELECT INTO OUTFILE or msyqldump It's ideal keeping the data in the local copy almost upto date (Would have said perfectly in sync but there is something called slave lag) On the other hand if you use a scheduled task to run mysqldump once every 24 hours. Imagine what can happen if the server crashes after 23 hours?
Each time you run mysqldump you are producing a large amount of data, keep doing it regularly and you will find your hard disk filled up or your file storage bills are hitting the roof. With replication only the changes are passed on to the server (by using the so called binlog)
XtraBackup
An alternative to replication is to use Percona XtraBackup.
Percona XtraBackup is an open-source hot backup utility for MySQL -
based servers that doesn’t lock your database during the backup.
Though by Percona, it's compatible with Mysql and Mariadb. It has the ability to do incremental backups lack of which is the biggest limitation of mysqldump.
Related Topics
How to Download a File on Clicking the Name of File Using PHP
Explode a Paragraph into Sentences in PHP
PHP File_Get_Contents Does Not Work on Localhost
Can a Client View Server-Side PHP Source Code
How to Download a File Using Curl in PHP
Using a Variable Outside of the While Loop (Scope)
Utf-8 Not Working in HTML Forms
PHP - Sort Multi-Dimensional Array by Another Array
PHP Generate Dynamic Pdo Insert
Best Practice for Error Handling Using Pdo
Execute PHP Script Before Every PHP Script
Display PHP Array Result in an HTML Table
How to Clear Previously Echoed Items in PHP