Combining much of above here is my real practical example, selecting records based on both meterid & timestamp. I have needed this command for years. Executes really quickly.
mysqldump -uuser -ppassword main_dbo trHourly --where="MeterID =5406 AND TIMESTAMP<'2014-10-13 05:00:00'" --no-create-info --skip-extended-insert | grep '^INSERT' > 5406.sql
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 > 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.
Dump a table using a where query:
mysqldump mydatabase mytable --where="mycolumn = myvalue" --no-create-info > data.sql
Dump an entire table:
mysqldump mydatabase mytable > data.sql
Notes:
mydatabase
, mytable
, and the where statement with your desired values.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.-h
, -u
, and -p
options to the example commands above in order to specify your desired database host, user, and password, respectively.mysql Export the query results command line:
mysql -h120.26.133.63 -umiyadb -proot123 miya -e "select * from user where id=1" > mydumpfile.txt
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" > arquivo.sql
At most 100 rows from test.t1 will be dumped from database table.
Cheers, WB
This should work
mysqldump --databases X --tables Y --where="1 limit 1000000"
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'
Source: Stackoverflow.com