MariaDB 10.2 - SQL Dump of specific columns

Question

I am aware of the --where condition while taking dump of table where we can give condition on rows. But I have a table of the size 15GB and have over 100 columns.I need a dump of few columns out of it, and I want to do this as scheduled job where I will take dump of few columns of the source DB and store it to another server for reporting purpose.I also tried INTO OUTFILE but It won't save output as .sql and as INSERT statement.

Answer 1

To dump specific columns from a large MariaDB 10.2 table (15GB, 100+ columns) as a .sql file with INSERT statements for a scheduled reporting job, you can't directly use mysqldump to select columns, as its --where option only filters rows. Instead, use a combination of a VIEW or a SELECT query with mysqldump, or generate INSERT statements via a query and redirect to a file.

The simplest approach is to create a VIEW with the desired columns and use mysqldump to export it:

CREATE OR REPLACE VIEW reporting_view AS
SELECT column1, column2, column3 -- specify your columns
FROM your_table;

Then, schedule a mysqldump command to export the view as a .sql file:

mysqldump -u [user] -p[password] --no-create-info --complete-insert [database] reporting_view > /path/to/dump.sql

The --no-create-info flag ensures only INSERT statements are included, and --complete-insert includes column names. Transfer the dump.sql file to the reporting server.

Alternatively, if you prefer not to use a view, use a temporary table to store the selected columns:

CREATE TEMPORARY TABLE temp_reporting
SELECT column1, column2, column3 FROM your_table;
mysqldump -u [user] -p[password] --no-create-info --complete-insert [database] temp_reporting > /path/to/dump.sql

For scheduling, create a shell script with the mysqldump command and schedule it using cron (e.g., 0 2 * * * /path/to/script.sh for daily 2 AM runs). Ensure the script handles file transfer to the reporting server (e.g., via scp).

If you need a pure SQL approach, use SELECT ... INTO OUTFILE to generate a CSV, then convert it to INSERT statements on the reporting server using a script, but this is less direct than mysqldump. The view-based mysqldump method is most efficient for your use case.

Could you share the column names you need or details about the reporting server setup?

Also why are you in 2025 still running MariaDB 10.2? You should upgrade to 11.4 and take advantage of all the new features and latest mariadb-backup features.

Answer 2

Create a view v_ip that contains only the columns you want, and then dump that view into a csv file.

select * from v_ip into outfile '/tmp/test.sql'
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n';

Importing should be done with "load data".