I have a big database with 100 tables I'm need to create dump. 98 tables with data and 2 tables only structure
like this
mysqldump -u root -p {--no-data db.table99, table10 } dbname > dump.sql
how can i do it with one request ?
In order to tell mysqldump you want to exclude a single table, all you have to do is add the flag --ignore-table followed by the name of the table you want to exclude. After executing the command from the example above, the output file my_backup.
mysqldump includes all the tables of the database by default. In order to dump only a specific set of tables using mysqldump , you need to specify the database name followed by the name of the tables you want to include in the dump. After running the command from the example above, the output file my_backup.
With the utility, you can dump tables, one or several databases, or the entire server with all databases and tables along with their objects or migrate them to another server. In addition, the mysqldump utility can output the dump to the CSV or XML file formats.
The --single-transaction option of mysqldump does do a FLUSH TABLES WITH READ LOCK prior to starting the backup job but only under certain conditions. One of those conditions is when you also specify the --master-data option. In the source code, from mysql-5.6. 19/client/mysqldump.
mysqldump either includes the data, or doesn't. You can't do it with one query.
However, you can safely combine two mylsqdumps
request into ONE file on bash. First one excludes the tables you don't want but has data, second one has only the 2 tables with no data:
{ command1 & command2; } > new_file
command1
=> mysqldump -u root -p --ignore-table=dbname.table99 --ignore-table=dbname.table100 dbname
command2
=> mysqldump --no-data -u root -p dbname table99 table100
Unfortunately, you'd have to supply the password twice. But since you want one line, you can put this in a bash script
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