Individual Table Backup

[davidz]

I am currently running a cron job that dumps my MYSQL databases every night with a command like this:

mysqldump --opt -uUsername -pPassword DBName > /home/sqldump/DBName.sql

This is working great, and I've even restored from it successfully a couple times.  But now the files are getting too large.  My main database is over 3 gigs when its dumped.

 

So what I want to do is dump each individual table to separate .sql files.  But I don't want to have some list that I have to maintain.  I found this bit of code that is close:

echo "SHOW TABLES" | mysql -uUsername -pPassword -D DBName | grep ^tablename | xargs mysqldump -uUsername -pPassword DBName > /home/sqldump/tablename.sql

 

Does anyone have a better solution that does not require a tablename, or dynamically gets the list of table names?

 

 

Thanks!!

David

[fenway]

First, you should be wary of putting the password in plaintext, since it will be visible in log files, top, etc.... but SHOW TABLES is "dynamic", so to speak... besides, only MyISAM tables are individual (well, new innodb has table-based datafiles, too, but you can't mysqldump them easily).

[davidz]

Thanks for the tip on the password issue, I'll come up with something there.

 

As for the tables, we use all MyISAM tables, so that's not a problem.

 

How about this,  use the command:

echo "SHOW TABLES" |mysql -uUSERNAME -pPASSWORD -D DBNAME

This will produce output like the following:

Tables_in_DBNAME
tblUserData
tblUserPrefs
tblStats

...This will list all the tables in the DB. 

So what I can then do is drop this output to a file instead:

echo "SHOW TABLES" |mysql -uUSERNAME -pPASSWORD -D DBNAME > tablelist

Then use perl to go through the file 'tablelist' line by line (skipping the first line) and perform:

mysqldump -uUSERNAME -pPASSWORD DBNAME > $tablename.sql

Substituting $tablename for the line that I'm on.

 

 

Thanks for the input!

[fenway]

I'm pretty sure you can read the password from a cnf file.