How to get a list of all posts and their categories?

If you want to export the data to a CSV or JSON file directly from the SQL query, you can use the MySQL INTO OUTFILE clause.

SELECT wpd9_posts.post_title, GROUP_CONCAT(wpd9_terms.name) AS categories
FROM wpd9_posts
LEFT JOIN wpd9_term_relationships ON (wpd9_posts.ID = wpd9_term_relationships.object_id)
LEFT JOIN wpd9_term_taxonomy ON (wpd9_term_relationships.term_taxonomy_id = wpd9_term_taxonomy.term_taxonomy_id)
LEFT JOIN wpd9_terms ON (wpd9_term_taxonomy.term_id = wpd9_terms.term_id)
WHERE wpd9_posts.post_type="post" 
AND wpd9_posts.post_status="publish"
GROUP BY wpd9_posts.ID
INTO OUTFILE '/path/to/export/posts_categories.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';