Why Is My Database Backup Script Not Working in PHP

Why is my database backup script not working in php?

This will not work to back up your database as an SQL script, unless your database is just a toy database, the equivalent of a "hello world" script.

That script is appalling. You should not use it to back up a database. That script has been posted before: PHP Database Dump Script - are there any issues?

• No error checking after mysql_connect() or mysql_queries(). You probably just gave a wrong password or something, but you'd never know because the script doesn't verify the connect was successful.

• It won't produce the right INSERT statement if your database contains any NULLs.

• Character sets are not handled.

• addslashes() is not suitable for escaping data.

• Table names are not delimited.

• Does not back up views, procedures, functions, or triggers.

• mysql_query() buffers results, so if you have a table with thousands of rows or more, you'll exceed your PHP memory limit. In fact, the script concatenates the series of INSERT statements into a single PHP variable. So before it finishes, you will have your entire database represented in memory.

No one should ever use that script. It's utter garbage, and I do not say that lightly.

Just use shellexec() to run mysqldump.

@Álvaro G. Vicario has a good point, there's no need for you to even use PHP for this task. I was assuming you need to make a backup from a PHP script. Here's how I would create a backup from a cron script:

Create a shell script, it can be called whatever you want, e.g. mymysqldump.sh. Here's how I would write it:

:
: ${BACKUP_HOST:="localhost"}
: ${BACKUP_DATABASE:="mydatabase"}
: ${BACKUP_DIR:="/opt/local/var/db/mysql5/backups"}
: ${BACKUP_FILE:="${DATABASE}-`date +%Y%m%d%H%M%S`"}

mysqldump -h ${BACKUP_HOST} ${BACKUP_DATABASE} > ${BACKUP_DIR}/${BACKUP_FILE}

Of course customize the values of the variables as needed for your environment.

You may notice that the username and password are not in this file. Please don't put passwords into scripts in plain text so everyone can read them. Instead, we'll put them in an options file to make it more secure.

Create a special operating system user who is going to run the backup from cron. Your system may have a special user "mysql" or "_mysql" to run the MySQL Server, but this user may be configured to have no valid home directory. You need a user that has a home directory. Let's call it "mybackup".

Under that user's home directory, create a file .my.cnf with the following content:

[mysqldump]
user = alupto_backup
password = xyzzy

Where "alupto_backup" and "xyzzy" are the MySQL username and its password (change these for your environment). Set the ownership and mode of this file so that only its owner can read it:

chown mybackup .my.cnf
chmod 600 .my.cnf

Create a bin directory under this user's home and put our shell script into it.

mkdir ~mybackup/bin
mv mymysqldump ~mybackup/bin

Now you can run the shell script to test it:

sh ~mybackup/bin/mymysqldump

Now create a cron file for this user:

crontab -u mybackup

@daily ~mybackup/bin/mymysqldump

That should be it.

PHP/ MySQL Database Backup Script Not Working Correctly

If your php version is higher than 5.2 you should replace ereg_replace with preg_match and change (“ parts as (“/

Why doesn't my PHP database backup script work?

try adding the full path in front of mysqldump

Attach Database Backup file in E-Mail send not working

I have edited this code and now it is working...

Edited code kept on github...

https://github.com/vaidyamanishjoshi/db-backup-with-pdo-and-email-it

Php calling sqlserver backup database script, backup file created and then deleted

Here is a solution, that works for me:

• execute sqlsrv_configure("WarningsReturnAsErrors", 0); to change the settings for error handling.
• remove WITH STATS = 1 from BACKUP DATABASE statement.

I'm able to reproduce this issue with a test case using Apache 2.4, PHP 7.1.12 and Microsoft PHP Driver for SQL Server (php_sqlsrv_71_ts_x86.dll, version 4.3). The only difference is that the example uses SQLSRV Driver (I can't use PDO_SQLSRV Driver in my testing environment).

PHP

<?php
    sqlsrv_configure("WarningsReturnAsErrors", 0);

    // Connection
    $serverName = "127.0.0.1\instance,1433";
    $connectionInfo = array(
        "UID"=>"user",
        "PWD"=>"password",
        "Database"=>"ProdDB"
    );
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    if ($conn === false) {
        echo "Unable to connect.</br>";
        die(var_export(sqlsrv_errors(), true));
    }

    // Backup database
    $strSQL = file_get_contents("archdata.sql");
    if (!empty($strSQL)) {
        $query = sqlsrv_query($conn, $strSQL);
        if ($query === false) {
            die(var_export(sqlsrv_errors(), true));
        } else {
            sleep(5);  
            echo "Success";
        }
    }   
?>

T-SQL (archdata.sql)

declare 
    @path varchar(100),
    @fileDate varchar(20),
    @fileName varchar(140)

SET @path = 'd:\Backup\'   
SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)  
SET @fileName = @path + 'ProdDB_' + @fileDate + '.BAK' 
BACKUP DATABASE ProdDB TO DISK=@fileName

Don't forget to give necessary rights to 'D:\Backup' folder.

unable to do backup for large amount of data in php script

You should not use PHP for that. Even despite you actually can resolve this problem - this will not be a good idea since you'll have to handle your tables and records within them one by one. So you'll change memory size to long execution time. That will be:

• Read row from table
• Write it into dump file
• Free memory for this row

(for now, you're trying to gather all table data and then dump it into file, which is impossible to allocate in memory when DB is too large)

In MySQL there's mysqldump - special tool to create SQL dumps. Unless there's a reason that prevents you of using it - I suggest you to do it (and - if you want to use PHP there are exec() and similar functions)

SQL Backup not working with PHP Script

well i used this

    $file = file_get_contents("db-backup-1316832550-6f1ed002ab5595859014ebf0951522d9.sql");
foreach(explode(';', $file) as $value => $key)
{
if($key == ''){continue;}

echo $key;
mysql_query($key) or die(mysql_error());
}

of course the file is example one

there's seems to be a problem with the delimiter = ";"

---

Related Topics