MySQL dump by query

Question

Is it possible to do mysqldump by single SQL query   I mean to dump the whole database  like phpmyadmin does when you do export to SQL

User · Answer

This should work  mysqldump --databases X --tables Y --where  1 limit 1000000

User · Answer

You could use --where option on mysqldump to produce an output that you are waiting for   mysqldump -u root -p test t1 --where  1 1 limit 100   gt  arquivo sql   At most 100 rows from test t1 will be dumped from database table   Cheers  WB

User · Answer

If you want to export your last n amount of records into a file  you can run the following   mysqldump -u user -p -h localhost --where  1 1 ORDER BY id DESC LIMIT 100  database table  gt  export file sql   The above will save the last 100 records into export file sql  assuming the table you re exporting from has an auto-incremented id column   You will need to alter the user  localhost  database and table values  You may optionally alter the id column and export file name

User · Answer

mysql Export the query results command line   mysql -h120 26 133 63 -umiyadb -proot123 miya -e  select   from user where id 1   gt  mydumpfile txt

User · Answer

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  gt  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  lt  1000

User · Answer

MySQL Workbench also has this feature neatly in the GUI  Simply run a query  click the save icon next to Export Import     Then choose  SQL INSERT statements    sql   in the list     Enter a name  click save  confirm the table name and you will have your dump file

User · Answer

Combining much of above here is my real practical example  selecting records based on both  meterid  amp  timestamp  I have needed this command for years  Executes really quickly   mysqldump -uuser -ppassword main dbo trHourly --where  MeterID  5406 AND TIMESTAMP lt  2014-10-13 05 00 00   --no-create-info --skip-extended-insert   grep    INSERT   gt  5406 sql

User · Answer

You can dump a query as csv like this   SELECT   from myTable INTO OUTFILE   tmp querydump csv  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n

User · Answer

Dump a table using a where query   mysqldump mydatabase mytable --where  mycolumn   myvalue  --no-create-info  gt  data sql   Dump an entire table   mysqldump mydatabase mytable  gt  data sql   Notes    Replace mydatabase  mytable  and the where statement with your desired values  By default  mysqldump will include DROP TABLE and CREATE TABLE statements in its output   Therefore  if you wish to not delete all the data in your table when restoring from the saved data file  make sure you use the --no-create-info option  You may need to add the appropriate -h  -u  and -p options to the example commands above in order to specify your desired database host  user  and password  respectively

[sql] MySQL dump by query

Examples related to sql

Examples related to mysql

Examples related to database