MySQL Table Backup to CSV/XLSX Guide

This guide provides detailed instructions on generating a CSV/XLSX file from a MySQL table, covering both Windows and Linux platforms. 

Follow the outlined steps to ensure successful execution and a secure backup.

Objective

This guide's objective is to help users export data from a MySQL table to a CSV file, which will be saved at a specified path on the client machine. This process is vital for data portability, backup, and sharing.

Prerequisites

Ensure the following requirements are met before proceeding:

• MySQL Client: Installed on the client machine.
• Access Credentials: Valid credentials for the MySQL server.
• Database and Table Access: Access to the specific database and table intended for backup.
• User Permissions: Adequate permissions to execute MySQL commands and queries.

Steps for Windows

1. Navigate to MySQL's bin directory
   • Open the Command Prompt on your Windows system.
   • Change the directory to the location where MySQL is installed.
     For example: cd C:\Program Files\MySQL\MySQL Server 8.0\bin
2. Run the MySQL Command
   • In the Command Prompt, execute the following command, replacing the placeholders with your specific details:
     mysql -u {user} -p -D {database_name} -e "SELECT {fields} FROM {tablename};" > C:\path\to\save\file.csv
   • Replace the following placeholders:
     • {user}: Your MySQL username.
     • {database_name}: Name of the database.
     • {tablename}: Name of the table.
     • {fields}: Names of the fields to export, separated by commas.
   • Example Command:
     mysql -u root -p -D r63 -e "SELECT jobparmtype, jobparmkey, jobparmdescription, visible FROM jobparm;" > C:\Users\tejeswar\Downloads\jobparm.csv
3. Enter MySQL Password
   • After executing the command, the system will prompt you to enter your MySQL password. Input the password and press Enter.
4. CSV File Generation
   • If the password is correct and the command executes successfully, the selected data will be exported to the specified CSV file path:
   • Example path: C:\Users\tejeswar\Downloads\jobparm.csv
5. View the CSV File
   • Navigate to the location where the CSV file is saved.
   • Open the file using a text editor (e.g., Notepad) or a spreadsheet program (e.g., Microsoft Excel) to verify the data.

Steps for Linux

1. Navigate to MySQL's bin Directory (if needed)
   • Open the Terminal on your Linux system.
   • If required, navigate to MySQL’s bin directory: cd /usr/local/mysql/bin
2. Run the MySQL Command
   • Execute the following command, replacing the placeholders with actual values:
     mysql -u {user} -p -D {database_name} -e "SELECT {fields} FROM {tablename};" > /path/to/save/file.csv
   • Example Command:
     mysql -u root -p -D r63 -e "SELECT jobparmtype, jobparmkey, jobparmdescription, visible FROM jobparm;" > /home/username/jobparm.csv
3. Enter MySQL Password
   • The system will prompt you to enter the MySQL password for the {user}. Enter the password and press Enter.
4. CSV File Generation
   • Upon successful execution, the data will be exported as a CSV file to the specified path:
     Example path: /home/username/jobparm.csv
5. View the CSV File
   • Open the file using any text editor (e.g., nano, Vim) or a spreadsheet application (e.g., LibreOffice Calc, Excel).
     
     Notes
     • Customization:
       Replace placeholders ({user}, {database_name}, {tablename}, {fields}) with your specific MySQL credentials, database, table, and fields.
     • Permissions:
       Ensure the MySQL user has sufficient privileges to run the SELECT query and export the data.
     • Output Path:
       Verify the specified directory exists and has appropriate write permissions before exporting.

Following these detailed steps, you can efficiently back up MySQL tables as CSV files on both Windows and Linux platforms.

Copyright © 2024, OvalEdge LLC, Peachtree Corners, GA USA