Creating Tab-Separated Output Files with mysqldump

The mysqldump command in MySQL is used to create backups of databases. One useful option is -T, --tab=name, which directs mysqldump to produce tab-separated text files for each dumped table. This can be particularly handy when you need to extract data in a format that’s easy to import into other systems or for further analysis.

Let’s explore how to use -T, --tab=name with some examples:

Example 1: Dump a single table into a tab-separated file:
mysqldump -T /path/to/output/directory dbname tablename

This command dumps the table tablename from database dbname into a tab-separated file located at /path/to/output/directory/tablename.txt.
Verification: Check if the file /path/to/output/directory/tablename.txt exists.

Example 2: Dump all tables from a database into separate tab-separated files:
mysqldump -T /path/to/output/directory dbname

This command dumps all tables from database dbname into tab-separated files in the directory /path/to/output/directory.
Verification: Check if files for each table exist in the output directory.

Example 3: Dump multiple tables into a single directory with different file formats:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory2 dbname tablename1 -T /path/to/output/directory3 --tab=/path/to/output/directory4 dbname tablename2

This command dumps tablename1 into /path/to/output/directory/tablename1.txt and tablename2 into /path/to/output/directory4/tablename2.txt.
Verification: Check if the specified files exist in their respective directories.

Example 4: Dump tables and include the database name in the output filenames:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory dbname.*

This command dumps all tables from dbname into tab-separated files, naming each file with the format dbname.tablename.txt.
Verification: Check if the generated filenames follow the pattern dbname.tablename.txt.

Example 5: Dump tables from multiple databases:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory dbname1.* dbname2.*

This command dumps all tables from dbname1 and dbname2 into tab-separated files in /path/to/output/directory.
Verification: Check if files for each database and table combination exist in the output directory.

Example 6: Dump tables with specific options (e.g., include headers):
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory --fields-terminated-by='\t' dbname.*

This command dumps all tables from dbname into tab-separated files, using tab as the field separator and including headers.
Verification: Open one of the dumped files and check for tab-separated values with headers.

Example 7: Dump tables with compression:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory --compress dbname.*

This command dumps all tables from dbname into tab-separated files and compresses them using gzip.
Verification: Check if the compressed files exist with the .gz extension.

Example 8: Dump tables with specific table options:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory --tables "tablename1, tablename2" dbname

This command dumps tablename1 and tablename2 from dbname into tab-separated files.
Verification: Check if the specified tables are dumped into the output directory.

Example 9: Dump tables with specific WHERE conditions:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory --where="column='value'" dbname tablename

This command dumps rows from tablename where column='value' into a tab-separated file.
Verification: Check if the dumped file contains only rows matching the specified condition.

Example 10: Dump tables using a custom SQL query:
mysqldump -T /path/to/output/directory --tab=/path/to/output/directory --query="SELECT * FROM tablename WHERE column='value'" dbname

This command dumps rows from tablename based on the custom SQL query into a tab-separated file.
Verification: Check if the dumped file contains rows as per the specified query.

Also check similar articles.

Handling Failed SSL Session Data Reuse in mysqldump
Setting SSL Session Data File in mysqldump
Setting TLS 1.3 Cipher in mysqldump
Configuring SSL FIPS Mode in mysqldump (OpenSSL Only)
Setting TLS Version in mysqldump