is it possible to "backup" row/table with mysqldump or whatever

Suppose a row (several rows) or table 开发者_如何学编程in database. Can I backup them, to restore as quick as possible only them if the rows/table will be corrupted.

thank you in advance!

I use the following perl-script to backup my databases:

#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect( "dbi:mysql:database=;mysql_client_found_rows=0;host=your.database.host", "username", "password", {RaiseError => 1});
my $databases = $dbh->selectcol_arrayref('SHOW databases;');
foreach my $t (@{$databases}) {
   system('/usr/local/bin/mysqldump -h your.database.host --add-drop-database --add-drop-table --add-locks --extended-insert=false --databases --allow-keywords -c -e -f -u username  --password=password \''.$t.'\' > /path/to/backupfiles/'.$t.'.sql');
   print "$t done\n";
   sleep(2);
}

The mysql-dump command is:

/usr/local/bin/mysqldump -h your.database.host --add-drop-database --add-drop-table --add-locks --extended-insert=false --databases --allow-keywords -c -e -f -u username --password=password 'databasename' > /path/to/backupfiles/databasename.sql

The result are single insert-statements for the whole table. You have to extract the lines you want to restore. Since you do not know which row will be damaged, you'll need them all. To restore a single row, just find it in the backup-file and execute the command.

I found it very difficult to deal with a backup file, that contains several databases. That's the reason to write this little script and backup each database into a single file.

You can alter the script easily, to dump each table into a single backup-file if needed.

To back up one table

mysqldump -u -p mydatabase table1 > table1.sql

or add the --where option for specific rows.

To restore from your backup

mysql -u -p mydatabase < table1.sql

If you want to do this through a GUI, you can use PhpMyAdmin to construct a SELECT, and then use the "export this query" function - this gives you the option to export in various formats, including SQL (which you can then directly execute when you need to restore)