Easiest way to print a MySQL database to text file, and reupload it

Question

I am working with a MySQL database, and would like to have the functionality of dumping a table to a plaintext file, being able to make some edits, and reuploading the file causing the table to be changed to the contents of the file. What would be the easiest way to do this? I know I can SELECT * INTO file.txt, but I am not sure if I can upload that file easily. I can just write a python script to run the commands, but it seems kind of clunky and I would ideally like some kind of integrity protection as well to make sure I don't erase the database.

Answer 1

What you are looking for is mysqldump

An example:

mysqldump db_name > backup-file.sql

Answer 2

Export it as a CSV:

SELECT
    *
INTO
    OUTFILE '/path/file.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM
    `table`;

That will create a standard CSV file

To read it back into a table (with the same structure)

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` LIKE `table`;

LOAD DATA INFILE
    '/path/file.csv'
INTO
    TABLE `table2`
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

I wouldn't import back into the same table. I would import into a new table to make sure it all works properly, drop the initial table if successful, then rename table2 to table:

DROP TABLE table;
RENAME TABLE table2 TO table;

If you really want to import back into the same table replacing the initial data, specify REPLACE:

LOAD DATA INFILE
    '/path/file.csv'
REPLACE
INTO TABLE `table`
...

The advantage of using a CSV is that you can import into a spreadsheet pretty easily for editing