Re: How To bakcup Databse per table ?

[Jay Alverson]

Something like this ?

Make sure you test first and you may have to tweak it a bit...

mysql> 
mysql> use information_schema;
Database changed
mysql> 
mysql> select table_name, table_schema from tables
    -> where table_schema = "test" order by 1 asc;
+----------------------+--------------+
| table_name           | table_schema |
+----------------------+--------------+
| a77                  | test         | 
| ab                   | test         | 
| accounts             | test         | 
| active_forums        | test         | 
| adotest              | test         | 
| append_data          | test         | 
| a_psposts            | test         | 
| a_ththreads          | test         | 
| baseball             | test         | 
| battingstats         | test         | 
| book                 | test         | 
| books                | test         | 
| candc                | test         | 
| carry_null           | test         | 
| cc                   | test         | 
| chapters             | test         | 
| colors               | test         | 
| contacts             | test         | 
| containertype        | test         | 
| cult_inno            | test         | 
| daycount             | test         | 
| dict_sort            | test         | 
| dir2                 | test         | 
| dirduplicate         | test         | 
| directory            | test         | 
| dt                   | test         | 
| dt_test              | test         | 
| exceltable           | test         | 
| filenames            | test         | 
| file_name            | test         | 
| fixed_width_import   | test         | 
| freq_id              | test         | 
| froots               | test         | 
| gene                 | test         | 
| gene_2               | test         | 
| gene_3               | test         | 
| hla                  | test         | 
| ids                  | test         | 
| id_test              | test         | 
| import               | test         | 
| info                 | test         | 
| input_filenames      | test         | 
| ip_customers         | test         | 
| lead                 | test         | 
| log_test             | test         | 
| mantis_bug_table     | test         | 
| mantis_contact_table | test         | 
| matches1             | test         | 
| measure              | test         | 
| mydates              | test         | 
| mysqlforums          | test         | 
| mysqlhtml            | test         | 
| mytable              | test         | 
| newtable             | test         | 
| newtime              | test         | 
| new_products         | test         | 
| nn_test              | test         | 
| numbers              | test         | 
| orders               | test         | 
| orgreport            | test         | 
| orgs                 | test         | 
| parametervalue       | test         | 
| parts                | test         | 
| pet                  | test         | 
| physicalcontainers   | test         | 
| points               | test         | 
| posts                | test         | 
| prepare_test         | test         | 
| products             | test         | 
| products_sold        | test         | 
| psionics             | test         | 
| psiunlimited         | test         | 
| random_num1          | test         | 
| random_num2          | test         | 
| random_numbers       | test         | 
| rifts                | test         | 
| rows_to_cols         | test         | 
| services_sold        | test         | 
| skills               | test         | 
| skill_numbers        | test         | 
| skill_values         | test         | 
| spells               | test         | 
| survey               | test         | 
| t                    | test         | 
| table1               | test         | 
| tablea               | test         | 
| tableb               | test         | 
| table_backup         | test         | 
| tbl1                 | test         | 
| test                 | test         | 
| testtable            | test         | 
| test_getstring       | test         | 
| test_one             | test         | 
| test_ti              | test         | 
| text_null            | test         | 
| times                | test         | 
| time_test            | test         | 
| t_view               | test         | 
| urltable             | test         | 
| user_msg             | test         | 
| xtest                | test         | 
| xx                   | test         | 
+----------------------+--------------+
102 rows in set (0.09 sec)

mysql> 
mysql> drop table if exists test.table_backup;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> create table test.table_backup
    -> 
    -> #---------------------------------------------------------#
    -> #    just 10 for now...
    -> #---------------------------------------------------------#
    -> 
    -> select concat("test.", table_name) as tablename from tables
    -> where table_schema = "test" limit 10;
Query OK, 10 rows affected (0.16 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> select * from table_backup;
+--------------------+
| tablename          |
+--------------------+
| test.a77           | 
| test.a_psposts     | 
| test.a_ththreads   | 
| test.ab            | 
| test.accounts      | 
| test.active_forums | 
| test.adotest       | 
| test.append_data   | 
| test.baseball      | 
| test.battingstats  | 
+--------------------+
10 rows in set (0.00 sec)

mysql> 
mysql> select concat("mysqldump -uroot -ppassword --tables ", tablename, " > Backup_", tablename, ".sql") as Command
    -> from table_backup;
+-----------------------------------------------------------------------------------------+
| Command                                                                                 |
+-----------------------------------------------------------------------------------------+
| mysqldump -uroot -ppassword --tables test.a77 > Backup_test.a77.sql                     | 
| mysqldump -uroot -ppassword --tables test.a_psposts > Backup_test.a_psposts.sql         | 
| mysqldump -uroot -ppassword --tables test.a_ththreads > Backup_test.a_ththreads.sql     | 
| mysqldump -uroot -ppassword --tables test.ab > Backup_test.ab.sql                       | 
| mysqldump -uroot -ppassword --tables test.accounts > Backup_test.accounts.sql           | 
| mysqldump -uroot -ppassword --tables test.active_forums > Backup_test.active_forums.sql | 
| mysqldump -uroot -ppassword --tables test.adotest > Backup_test.adotest.sql             | 
| mysqldump -uroot -ppassword --tables test.append_data > Backup_test.append_data.sql     | 
| mysqldump -uroot -ppassword --tables test.baseball > Backup_test.baseball.sql           | 
| mysqldump -uroot -ppassword --tables test.battingstats > Backup_test.battingstats.sql   | 
+-----------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> 
mysql> #---------------------------------------------------------#
mysql> #  then select the above INTO OUTFILE and run that as the #
mysql> #  the backup program... .BAT file/copy & paste whatever  #
mysql> #---------------------------------------------------------#
mysql> 
mysql> notee

>

Thanks, Jay