backing up database tables separately

[travbacon]

ok so i am using the command line in ssh to backup the database

mysqldump -u [USERNAME] -p[PASSWORD] [DATABASE NAME] > [BACKUPFILE NAME]

i am also backing up each table individually with the command line

mysqldump -u <db_username> -p db_name table_name > table_name.sql

the problem is there are 144 tables and it took me 15 minutes

is there a swift one command liner to do this where each database table is also named the table name

address_book > address_book.sql
address_format > address_format.sql
etc.

our server also has a nightly backup for the entire site and database

the reasoning behind is last time we moved to a different server and imported the entire database, there were errors galore in a lot of tables and luckily it was fixed. i'm thinking on our next server move, if there is a problem importing the entire db in one chunk, i can import each table since they are smaller files

thanks in advance

[swguy]

You can just build a script to do this. The table names are in includes/database_tables.php.

[travbacon]

there are other database tables though created by "plugins" that aren't listed in that file

[mc12345678]

You can just build a script to do this. The table names are in includes/database_tables.php.

Ehhh, not 100% true otherwise there would not be 144 tables. In that script would suggest pulling the list of tables and then cycling through those. Small "problem" with that though too is that a single database may be used for more than one store and also a plugin may not use/enforce the DB_PREFIX.
Personally, it sounds like the issue really was that when transferring from one database to the other that the source database was operated looser than the destination database. That allowed the source database to have problems that were undetected until either initially loaded (even into the same server) into a database or otherwise so accessed. Sounding like a nay sayer, I would perceive that this individual table scenario would actually introduce more potential issues. e.g. a table gets updated in the database, that table is independently exported, but the records in it now point to either non-existent information because in updating the one table there actually were others that were updated and not also exported...

[carlwhat]

i am also of the opinion that solving the database import is the best solution. the fact that you had a problem (i'm guessing) there is more about the mysql config than of your db backup. a full on db backup is always, IMO, your best solution.

if you want to do a script; i too am not a fan of hard coded table names in a file.

you can get all of the your table names by using a php script as such:

    require 'includes/application_top.php';
    $name = 'Tables_in_' . DB_DATABASE;
    $tables = $db->Execute('show tables');
    foreach ($tables as $table) {
        // here you can do you backup of each table
        echo "-------->" . $table[$name] . "<---------<br>";
    } 

hopefully that gives you enough to go on.