1

I have asked this question before but I haven't got an answer, so I'm rephrasing it.

I want to back up a db using either:

system("mysqldump -h DB_HOST -u DB_USER -p DB_PASS logindb > $location/$backup");

or:

sql="BACKUP my_db TO DISK my_backup_folder WITH FORMAT #";

if ($stmt = $this->connect->prepare($sql)) {    
    $stmt->execute();
    $stmt->close();
} else {
    $error              = true;
    $message['error']   = true;
    $message['message'] = CANNOT_PREPARE_DATABASE_CONNECTION_MESSAGE;
    return json_encode($message);
}

But the first gives me an empty sql file and the second gives me nothing. Why is that, and if there is a way to find out what error occurred how would I do it ?

Also which method is better ?

Improve this question

5 Answers 5

Reset to default
1

Found some simpler code here

Method 1:

$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

Method 2:

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Already tried it, doesn't work as well, the first, the second I'll try it now.
I just tried the second method, the same error, that mysqldump is not recognized as an internal or external command. One question though, $backupFile should it have the location as well ? Because I have the location as well. For example the php file that executes it is in the same folder with the folder that I have the $backupFile point to.
You need to have location. However you cannot use mysqldump under windows. Do you have a linux server to try?
No, but any other operation works fine with XAAMP, why isn't this one working ? And this is a app in development so I won't be able to upload it on server because the code is a mess and I don't have the db there.
But then, why cannot I use this ? $backup = 'backup_'.date('Y').'_'.date('m').'_'.date('d').'.sql'; $sql = "BACKUP DATABASE logindb TO DISK = '$location/$backup' WITH FORMAT"; error_reporting(E_ALL); try{ $stmt = $this->connect->prepare($sql); $stmt->execute(); $stmt->close(); } catch(PDOException $e) { echo $e->getMessage(); } It tells me Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\login\assets\class\login\loginsys.php on line 527 that line being $stmt->execute(); , but if I don't use try it outputs an empty file.
|
1

The start post:

-p DB_PASS

And the right form:

-p[password]

I don't know why but the -p is agains the password, not equal to the other parameters. So be aware that you take over the examples well. I have seen different cases where this was the issue so take note of it.

Improve this answer

8 Comments

OK. DB_PASS is a defined var, and it looks like this: define('DB_PASS','mydbpass');. But you're saying I must have -p[DB_PASS] instead -p DB_PASS ?
Yes, just tested it at a MAMP installation and with the " " space in between it does not work. It looks very not making any sense but it seems to be so.
I get this error in the log: 'mysqldump' is not recognized as an internal or external command, operable program or batch file and I execute this line: system("mysqldump -hDB_HOST -uDB_USER -pDB_PASS logindb > $location/$backup"); from a PHP file on XAAMP.
First test with the absolute direct path to mysql, like: C:\mysql\bin\mysqldump If that works you might change your PATH variables. Considering you are on Windows since you did not mention that explicitly but XAAMP is mostly used on Windows.
Ok, then try to use the absolute path. It is most of the time easier to use some terminal, like CMD to test this kind of actions instead of running it directly from PHP. So open CMD, CD to the mysql bin folder and then try to run your command again to see if it is working at that point.
|
1

Use try-catch, something like:

try{
    $stmt = $this->connect->prepare($sql);
    $stmt->execute();
    $stmt->close();
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

Check log files for errors.

Improve this answer

2 Comments

If I do that this is what I get: Fatal error: Call to a member function execute() on a non-object in C:\xampp\htdocs\login\assets\class\login\loginsys.php on line 527 that line being $stmt->execute();.
$this->connect->prepare($sql); should be changed in $stmt = $this->connect->prepare($sql); otherwise the $stmt object doesn't exists
0

The first one is faster if you have exec permissions for php.

Check the SQL error log file.

/var/log/mysql.err - MySQL Error log file

/var/log/mysql.log - MySQL log file

For php you might put error_reporting(E_ALL); this to throw php errors if any.

Improve this answer

5 Comments

I got some error 32767. Where do I check the log, I'm testing on localhost ( XAAMP ) ?
And when I used @NarcisRadu's method I checked the log, I had to search for it in the xaamp folders, but this is what I got: 'mysqldump' is not recognized as an internal or external command, operable program or batch file.
execute this query to see show variables like '%log_file%';
OK, so I won't be able to use it ? Than should I use the second method ? And how exactly can I make it work ?
Try a linux environment and try the mysqldump method. However the first method from here should work stackoverflow.com/a/9063524/1168944 but I cannot say why is not working in your case.
0

Make sure that the user have the permission to this database.

Below is the command to backup the mysql database

mysqldump -h [host_name] -u [username] -p[password] [database_name] > dumpfilename.sql
Improve this answer

1 Comment

This is the only one that actually worked, the second method didn't do anything. This one outputs an empty sql file.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.