MySql Table wise Backup and Restore

Question

I'm working on Mysql data backup and restore system but stuck that how to go with it. An Option which i may think of

Table Wise Backup Method

Creating A Directory With Dateandtime and directoy will have one definition text file with all databases and its table names plus seperate file for each table contaning table structure and table INSERTS.

Table Wise Restore Method

Reading directory and definition file to Sort backups with respect to dates and table names and user can select either all tables or one specific table to restore.

I'll be using PHP for this purpose as i have to upload these backup files automatically on different servers.

Questions

1- Is above backup and restore method is valid?

2- Is there a way by which i can write single file for each Database but still have some way to restore only selected or all tables in database?

3- What are important points i must to keep in mind for such applications?

Please let me know if anything Ambiguous?

It's a pain to get the correct table definitions programmatically. Not to mention when using InnoDB. Don't you have access to mysqldump? That would make everything so much easier. — Bart
– Bart, Commented Mar 9, 2013 at 13:07
yes i do have but is it possible to restore that dump with selected tables only? — Muhammad Haseeb Khan
– Muhammad Haseeb Khan, Commented Mar 9, 2013 at 14:43
you can dump tables individually with mysqldump. The same for restoring. The single table restore option is a bit tricky when using InnoDB since you rely on the relationships. — Bart
– Bart, Commented Mar 9, 2013 at 14:47
@Bart: I don't think it's hard to get table definitions. There's SHOW CREATE TABLE for one. — siride
– siride, Commented Mar 9, 2013 at 14:53
@siride Thanks. I just learned something as well :-) But still it's better to use the tools that work and not implementing it yourself if you don't really need too. — Bart
– Bart, Commented Mar 9, 2013 at 14:58

Bart · Accepted Answer · 2013-03-09 16:10:32Z

4

Try mysqldump with a simple shell script. It can be extended to dump tables. It now only dumps databases.

#!/bin/bash

USER="username"
PASSWORD="password"
OUTPUTDIR="./"
MYSQLDUMP=`which mysqldump`
MYSQL=`which mysql`

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do

    # skip internal databases
    if [ "$db" == "information_schema" ] || [ "$db" == "mysql" ]; then
        continue
    fi  

    # dump whole database 
    echo "$db"
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.sql.bak"

    # get a list of tables inside database
    tables=`$MYSQL --user=$USER --password=$PASSWORD \
     -e "USE $db;SHOW TABLES;" | tr -d "| " | grep -v Tables_in_$db`

    # dump tables
    for tbl in $tables; do
        echo "$db.$tbl"
        $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
         $db $tbl> "$OUTPUTDIR/$db.$tbl.sql.bak"
    done
done

edited Mar 9, 2013 at 16:10

answered Mar 9, 2013 at 15:37

Bart

17.4k5 gold badges63 silver badges81 bronze badges

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

2 Comments

Muhammad Haseeb Khan Over a year ago

thanks Bart. may be i need to find the way to creat a definition for each database first

Bart Over a year ago

Updated the script so it fits the question better

Collectives™ on Stack Overflow

MySql Table wise Backup and Restore

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related