How to use mysqldump for part of a table?

Question

Therefore, I can only export a table like this:

mysqldump -u root -p db_name table_name > table_name.sql 

Is there a way to export only part of a table with mysqldump? For example, 0-1000000 lines, 1,000,000 - 2,000,000 lines, etc.

Should I do this with mysqldump or query?

Answer 1

 mysqldump -uroot -p db_name table_name --where='id<1000000' 

or you can use

 SELECT * INTO OUTFILE 'data_path.sql' from table where id<100000 

Answer 2

 mysqldump --skip-triggers --compact --no-create-info --user=USER --password=PASSWORD -B DATABASE --tables MY_TABLE --where='SOME_COLUMN>=xxxx' > out.sql 

Answer 3

The dumped file is different from the file you use to select SQL. For the second approach, you cannot simply use: mysql database <Table to unload the table into the database.

Answer 4

In my case, I do this:

 SELECT * INTO OUTFILE 'C:\Documents and Settings\Anton.Zarkov\Desktop\joomla_export\data_AZ.sql' FROM `jos_glossary` WHERE id>6000 

• there is no syntax error - the request passes.
  • The result is NULL - no lines were written. (I'm sure the last identifier is 6458).
  • If I repeat the request a n error occurs => #1086 - File 'C:Documents and SettingsAnton.ZarkovDesktopjoomla_exportdata_AZ.sql' already exists
  • Unfortunately, I cannot find the "existing" file anywhere on drive C. Where is it?

Conditions: phpMyAdmin SQL Dump; version 3.4.5; host: localhost; server version: 5.5.16; PHP Version: 5.3.8

Answer 5

 mysqldump -uroot -p db_name table_name --where'id<1000000' > yourdumpname.sql 

Answer 6

Below is a query to select from a range of identifiers, you can use date_created or any instead of an identifier

 mysqldump --opt --host=dns --user=user_name --password db_name --tables table_name --where "id > 1 and id < 100 " > /file_name.sql 

for example: --where="date_created > '2019-01-18' " → insted of id