Menu
I'm doing a regular dump of a database that uses the database for logging. I need to create a mysqldump command that dumps everything from the database but excludes the row information for the log tables.
I see the no-data parameter, but that doesn't seem to support selecting only certain tables.
873
Use the --no-data switch with mysqldump to tell it not to dump the data, only the table structure. This will output the CREATE TABLE statement for the tables. To target specific tables, enter them after the database name. Save this answer.
By default, mysqldump locks all the tables it's about to dump. This ensure the data is in a consistent state during the dump.
The --single-transaction flag will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.
No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql.
you can combine with shell script to help better
#/bin/bash
# dump all except for table log
tables=$(mysql -N <<< "show tables from your_db" | grep -Ev "^log$" | xargs);
mysqldump your_db $tables > backup.sql
# dump structure for table log
mysqldump -d your_db log >> backup.sql
Run 2 commands. One where you list all tables that you want a full dump of, one where you dump only the table definition
#structure only
mysqldump -d -q mydb table1 table2 table3
#all data too
mysqldump -q mydb table4 table5 table6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
