Extracting Contents from WFA MySQL Database Table into CSV (Text) File

Logged into mysql.

From command prompt:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u{USERNAME} -p{PASSWORD}

If you want to browse databases and tables, you can use:

show databases;

use {DATABASE_NAME};

show tables;

To extract the contents of the cm_storage.aggregate say into a CSV file (just a text file really), you can run:

mysql> select * from cm_storage.aggregate INTO OUTFILE 'C:/tmp/aggregates.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"';

Further reading: https://www.mysqltutorial.org/mysql-export-table-to-csv/

This will not give you headings unfortunately. To get headings from a MySQL table in a separate file, you can run:

mysql> SELECT column_name FROM information_schema.columns WHERE table_name = 'aggregate' AND table_schema = 'cm_storage' INTO OUTFILE 'C:/tmp/aggregates_columns.txt';

Credit: https://stackoverflow.com/questions/5525970/mysql-show-columns-but-exclude-everything-except-the-field-names

There is another way you could get columns for your output, and that is to create a custom table just for columns. Then union the table with just columns in with the select * to get the contents of your table. Example (not a very good example really, I picked a table with lots of headings):

CREATE TABLE cm_storage.aggregate_headings (id CHAR(30),node_id CHAR(30),name CHAR(30),size_mb CHAR(30),used_size_mb CHAR(30),available_size_mb CHAR(30),raid_type CHAR(30),raid_status CHAR(30),block_type CHAR(30),state CHAR(30),number_of_disks CHAR(30),volume_count CHAR(30),is_hybrid CHAR(30),hybrid_enabled CHAR(30),hybrid_cache_size_mb CHAR(30),snapshot_total_size_mb CHAR(30),snapshot_used_size_mb CHAR(30),has_local_root CHAR(30),has_partner_root CHAR(30),daily_growth_rate_mb CHAR(30),days_until_full CHAR(30),snaplock_type CHAR(30),is_snaplock CHAR(30),type CHAR(30),is_composite CHAR(30));

INSERT INTO cm_storage.aggregate_headings VALUES ('id','node_id','name','size_mb','used_size_mb','available_size_mb','raid_type','raid_status','block_type','state','number_of_disks','volume_count','is_hybrid','hybrid_enabled','hybrid_cache_size_mb','snapshot_total_size_mb','snapshot_used_size_mb','has_local_root','has_partner_root','daily_growth_rate_mb','days_until_full','snaplock_type','is_snaplock','type','is_composite');

select * from cm_storage.aggregate_headings UNION select * from cm_storage.aggregate INTO OUTFILE 'c:/tmp/aggrs_with_headings.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"';

Image: Example of output aggrs_with_headings.csv