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 ?
Found some simpler code here
ReplyDeleteMethod 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);
The start post:
ReplyDelete-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.
Use try-catch, something like:
ReplyDeletetry{
$stmt = $this->connect->prepare($sql);
$stmt->execute();
$stmt->close();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
Check log files for errors.
The first one is faster if you have exec permissions for php.
ReplyDeleteCheck 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.
Make sure that the user have the permission to this database.
ReplyDeleteBelow is the command to backup the mysql database
mysqldump -h [host_name] -u [username] -p[password] [database_name] > dumpfilename.sql