Re: mysqldump - dump file per table?
On Friday, 7 July 2006 at 14:53:11 -0500, Dan Buettner wrote: I'm preparing to implement some mysqldump-based backups, and would really like to find an easy way to dump out one SQL file per table, rather than single massive SQL file with all tables from all databases. In other words, if I have database DB1 with tables TBL1 and TBL2, and database DB2 with tables TBL3 and TBL4, I'd end up with files named something like this, containing just the table create and data for each: 20060707.DB1.TBL1.sql 20060707.DB1.TBL2.sql 20060707.DB2.TBL3.sql 20060707.DB2.TBL4.sql This would make selective restores a lot easier, and would also allow us to set up development/testing environments more easily than one big file. I'd use mysqlhotcopy but we're in an InnoDB environment. I can implement this with a little perl script but wondered if anyone was aware of a tool out there already? As has been discussed, there's no current method. It's certainly as an enhancement. doable (though we'd need to think about how to name the dump files). You could put in a bug report asking for the functionality, though honestly I don't see much likelihood of us doing it in the near future. But then, I don't decide the priorities. On the other hand, if the bug report is accompanied by working code to implement this functionality, there's a much better chance of it being accepted. If you or somebody else want to do this, please contact me first so that we can discuss the approach. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpBt1YxNlABd.pgp Description: PGP signature
mysqldump - dump file per table?
I'm preparing to implement some mysqldump-based backups, and would really like to find an easy way to dump out one SQL file per table, rather than single massive SQL file with all tables from all databases. In other words, if I have database DB1 with tables TBL1 and TBL2, and database DB2 with tables TBL3 and TBL4, I'd end up with files named something like this, containing just the table create and data for each: 20060707.DB1.TBL1.sql 20060707.DB1.TBL2.sql 20060707.DB2.TBL3.sql 20060707.DB2.TBL4.sql This would make selective restores a lot easier, and would also allow us to set up development/testing environments more easily than one big file. I'd use mysqlhotcopy but we're in an InnoDB environment. I can implement this with a little perl script but wondered if anyone was aware of a tool out there already? Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump - dump file per table?
Yes, that's what I'm after, as I know it will do individual tables ... but I'd like it to do one file for each and every table within each and every database, without having to maintain a batch script with multiple calls to mysqldump specifying them all. It'd be something like mysqldump -u user -psecret --all-databases --file-per-table --output-dir=/path/to/backups Dan On 7/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: mysqldump will dump a database or just a table, just depends on what you specify. mysqldump [OPTIONS] database [tables] of course, if you want to automate this and don't know the table (or database) names in advance you'd need to do something (e.g., a mysqlshow) to get that list first. is that what you're after, or am i missing something? - Rick Original Message Date: Friday, July 07, 2006 02:53:11 PM -0500 From: Dan Buettner [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: mysqldump - dump file per table? I'm preparing to implement some mysqldump-based backups, and would really like to find an easy way to dump out one SQL file per table, rather than single massive SQL file with all tables from all databases. In other words, if I have database DB1 with tables TBL1 and TBL2, and database DB2 with tables TBL3 and TBL4, I'd end up with files named something like this, containing just the table create and data for each: 20060707.DB1.TBL1.sql 20060707.DB1.TBL2.sql 20060707.DB2.TBL3.sql 20060707.DB2.TBL4.sql This would make selective restores a lot easier, and would also allow us to set up development/testing environments more easily than one big file. I'd use mysqlhotcopy but we're in an InnoDB environment. I can implement this with a little perl script but wondered if anyone was aware of a tool out there already? Dan -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
