MySQL Dump by Query

MySQL dump by query

not mysqldump, but mysql cli...

mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase

you can redirect it out to a file if you want :

mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt

Update:
Original post asked if he could dump from the database by query. What he asked and what he meant were different. He really wanted to just mysqldump all tables.

mysqldump --tables myTable --where="id < 1000"

How to dump result of a sql query into a file

The following command line (using --where option) should work:

mysqldump -u root -p database_name products --where="brand_id=5" > dumpfile.sql

More information about the mysqldump command: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

Mysqldump from a query file

Mysqldump doesn't run custom queries, it only runs the equivalent of select * from MyTable with optional where clause. So you could do this:

mysqldump --where 'c in (...)' MyDatabase MyTable > dump.sql

But you'd get all the columns of your table, not just a, b.

You can use the mysql client to run custom queries, but it outputs CSV text, not SQL like mysqldump does.

mysql -e 'select a,b from MyTable where c in (...)' MyDatabase > dump.csv

I'm omitting other options like --host, --user, --password because I prefer to keep those in the ~/.my.cnf file.

Exporting SQL query results with mysqldump

You need to list the table names in your command, and not the select query.

Like so (note this will output SQL and not CSV):

mysqldump -u(username) -p(password) (dbname) (tablename1) (tablename2) > output.sql

If you want to output CSV with mysqldump you can use the --tab option:

mysqldump --tab=/tmp --fields-enclosed-by='"' --fields-terminated-by="," --lines-terminated-by="\n" --no-create-info  (dbname) (tablename)

If you do need to run a query and output CSV you can use the mysql client:

mysql --batch -e "select * from (tablename) where x=y" > output.csv

This will create a TAB delimeted CSV.

mysqldump from a query

mysqldump has a --where parameter: Manual

Dump only rows selected by the given WHERE condition. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.

Examples:

--where="user='jimf'"

-w"userid>1"

-w"userid<1"

I don't know what they use, but phpMyAdmin can do this too, Just make the query, select all rows and choose the "export" button to the bottom.

MySQL Dump - meta queries

Comments starting with /*! are MySQL-specific SQL statements that are still executed by MySQL but ignored by other SQL databases. For reference see the MySQL docs on comments.

In your case, it seems like the @saved_cs_client is NULL, which is not a valid value for character_set_client. You could set @saved_cs_client, or modify the line to set character_set_client to the character set that you use.

(Btw. I would recommend to always use utf8mb4 as your character set.)

mysql dump by complex query

I think this will accomplish what you're looking for:

SELECT tbl2.*
FROM tbl1, tbl2
WHERE tbl2.parent = tbl1.child
AND tbl1.id = 1
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

This will save your data into a CSV file. You can also save into other formats. I found a helpful tutorial on this topic a while back from here

dump query on multiple table

To use a query like that you could send it into the mysql client, (not the mysqldump utility) and then redirect that output to the file instead:

echo "SELECT v.ITEM_ID , v.CODE ,\
s.SENSOR , d.DESTINATIONCODE \
FROM  V_TABLE v, S_TABLE s ,D_TABLE d \
WHERE s.ITEM_ID = v.ITEM_ID \
AND s.CREATIONDATETIME <  '2014-2-16 00:00:00'\
AND v.DESTINATION_ID=d.ID\
" | mysql --user=user --password=password --host=localhost dbname > /var/www/dumps/output_15_12_2017.sql

If you're going this route then the --batch and --table options can be useful.

---

Related Topics