Create MySql Database Backup / Rollback

Question

I understand that I can use MySql's command BACKUP and RESTORE to backup a database and rollback when needed.

My question is, would I be able to execute it this way:

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

And the restoration made in the same fashion:

sql="RESTORE DATABASE my_db FROM DISK my_backup_folder WITH FILE #";

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

And in each case what does # stand for, is that .bak ? And is there anything else I should add besides what's in there ?

Have BACKUP and RESTORE commands been introduced at all? — Mchl
– Mchl, Commented Jan 31, 2012 at 7:08
Per my answer and @Mchl's comment, these are deprecated commands and probly not the best way to approach a practical backup system for MySQL. — quickshiftin
– quickshiftin, Commented Feb 1, 2012 at 2:42

quickshiftin · Accepted Answer · 2012-02-01 02:32:53Z

1

A quick peak on the command line shows these are deprecated commands.

mysql> help backup; Name: 'BACKUP TABLE' Description: Syntax: BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

*Note*: This statement is deprecated and is removed in MySQL 5.5. As an alternative, mysqldump or mysqlhotcopy can be used instead.

I'd say any advice on how to use deprecated commands is a bit of a misnomer. Take a peak at mysqldump. There are other options as well such as LVM snapshots etc.

answered Feb 1, 2012 at 2:32

quickshiftin

70.4k12 gold badges72 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roland Over a year ago

OK. I've tried to use mysqldump, but it doesn't work on my local server, and I couldn't find why. But now I'm trying another solution, I'm looping through the db and getting all the existing tables and anything inside them. And I'm making files with fopen and I'm writing the data into them, also with sql commands like CREATE TABLE IF NOT EXISTS and other. Anyway, this method I asked didn't work as well.

quickshiftin Over a year ago

Just my opinion, but I would recommend figuring out why mysqldump isn't working. It's a standard tool and it sounds like you're rewriting it at this point. I bet in the time you spend getting mysqldump to work you won't be able to replace it with your own stuff. But who knows, sometimes there is merit to new solutions.

Roland Over a year ago

Indeed. Well, some said that it's because I'm testing in Windows environment, but I have XAAMP which makes all processes work, anything else works I mean but this command. Some say I don't have access to write, but for other procedures which write works. So I couldn't figure out why it isn't working. And I decided to work with what I know it will work.

Collectives™ on Stack Overflow

Create MySql Database Backup / Rollback

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related