0

I know that we can backup a table by query as CSV format like this:

SELECT * FROM db.table
INTO OUTFILE 'C:/backup'
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

But this is creating the backup as CSV format. Is there any way to make it as SQL format ?

Kitiara
  • 343
  • 6
  • 21
  • What do you mean by SQL format? Do you want to copy the contents of a table to another table? Or do you want the result to be a series of insert statements? Or what? – Honeyboy Wilson Jun 13 '20 at 19:57
  • The table needs to be backed up in an sql file as opposed to his current method's csv file. @HoneyboyWilson – Martin Jun 13 '20 at 20:12
  • https://stackoverflow.com/questions/3978326/get-insert-statement-for-existing-row-in-mysql – Honeyboy Wilson Jun 14 '20 at 00:24

1 Answers1

0

There is currently no way to export your database and/or tables into an sql file through a query command. You will need to make use of the mysqldump command-line function.

Simple export:

mysqldump db_name tbl_name > backup-file.sql;

Note that without giving any destination path, your file will be located in the same directory where you performed the dump.

Export to specified path:

mysqldump db_name tbl_name > C:\"backup"\backup-file.sql;

Simple import:

mysql> use db_name;
mysql> source backup-file.sql;

Import from specified path:

mysql> use db_name;
mysql> source C:\"backup"\backup-file.sql;

Using double-quotes " around your path is only necessary in case you make use of white spaces in your directory path.

Martin
  • 2,326
  • 1
  • 12
  • 22
  • Thanks for the info but i already know about the mysqldump. I'm just looking for a way of doing the same thing by just using queries. I'm not gonna use any command line interface. – Kitiara Jun 13 '20 at 19:44
  • That's the way to do it if you want an sql file. – Martin Jun 13 '20 at 19:56
  • It doesn't necessarily have to be an .sql file. But the contents inside the backup file has to be sql queries. Like you know `INSERT INTO table (column1, ...) VALUES (value1, ...)`. – Kitiara Jun 13 '20 at 20:24