Export MySql table by query selecting few rows ordered descending

Question

my issue is this
I have table in my database which have more than 1 million rows. Sometimes i need have sql dump of my table in my PC and export whole table taking very long. Actually i need exported table for example with last 5000 rows. So is there a way to export MySql table by selecting last X rows. I know some ways to do it by terminal commands, but i need poor MySql query if it is possible. Thanks

Answer 1

If I understand well, you could try by using the INTO OUTFILE functionality provided by MySQL. of course I don't know what's your current query but you can easily change my structure with yours:

SELECT * 
FROM table_name
INTO OUTFILE '/tmp/your_dump_table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
ORDER BY id DESC
LIMIT 5000

Since the user @Hayk Abrahamyan has expressed preference to export the dump as .sql file, let's try to analyze a valid alternative:

• We can run the query from phpmyadmin or (it's for sure a better solution) MysqlWorkbench SQL editor console and save it by press the export button (see the picture below):

As .sql output file result you will have something like the structure below:

/*
-- Query: SELECT * FROM mytodo.todos
ORDER BY id DESC
LIMIT 5000
-- Date: 2018-01-07 13:15
*/
INSERT INTO `todos` (`id`,`description`,`completed`) VALUES (3,'Eat something',1);
INSERT INTO `todos` (`id`,`description`,`completed`) VALUES (2,'Buy something',1);
INSERT INTO `todos` (`id`,`description`,`completed`) VALUES (1,'Go to the store',0);