Categories
DBA

mysqldump for 764000 myisam tables

I am running Mysql 5.1 DB for WordPress Application with Hyperdb plugin enabled. I have 93000 blogs and for this I have 93000 8tables per blog = 764000 tables MyISAM in the DB. I created a backup script to take backup each blog separately and its working fine. But the problem is about timing. Mysqldump is taking almost 2/3 seconds per blog and most of the time are consumed by “SHOW TABLES LIKE” to get the actual tables name get_actual_table_name funciton at source code. I changed it for query from INFORMATION_SCHEMA rather that SHOW TABLES and now each log is taking maximum 1 second. I want to know if you guyes have any other good idea for this huge DB 300GB backup so that I can go with the default source code.

via MySQL :: mysqldump for 764000 myisam tables.

Say WHAT?