How to backup a table or entire database by query as SQL format

Question

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 ?

Answer 1

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.