3

I created a database using 'CREATE DATABASE Gameshop;' for my class and I need to hand it in as an '.sql' file but, I don't know where to look for the file or how to create it.
I am worried I will lose all of my work if I attempt something and fail. Thank you!

ImportanceOfBeingErnest
  • 321,279
  • 53
  • 665
  • 712
Anthony Herrera
  • 31
  • 1
  • 1
  • 2
  • 2
    Possible duplicate of [MySQL: How to export and import an .sql file from command line?](http://stackoverflow.com/questions/11407349/mysql-how-to-export-and-import-an-sql-file-from-command-line) – Stuti Rastogi May 12 '17 at 04:46
  • Is this on your own machine? What operating system are you using? – kojow7 May 12 '17 at 06:25

2 Answers2

4

In your command prompt (NOT mySQL command line):

Use $ mysqldump -u <username> -p<passwprd> db_name > db_backup.sql in order to dump the entire database on to an SQL file from the command line.

To import back use $ mysql -u <username> -p -h localhost db_name < db_backup.sql

See manual for more details.

Stuti Rastogi
  • 1,162
  • 2
  • 16
  • 26
  • I get this error "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump' at line 1" when i try to use mysqldump in the command prompt – Anthony Herrera May 12 '17 at 05:35
  • @AnthonyHerrera , Are you sure you are typing this at the command prompt of your OS and not in your MySQL server? Be sure not to use the $. – kojow7 May 12 '17 at 05:39
  • 1
    You have to use shell command prompt not mysql command prompt – Kuldeep Dubey May 12 '17 at 05:41
  • '$' sign is useless – denny May 12 '17 at 05:48
  • It is in the mysqld prompt. how do i get to the shell command prompt without losing my work? – Anthony Herrera May 12 '17 at 05:50
  • @AnthonyHerrera Even if you close MySQL your work should still be there. Any SQL commands are saved automatically so will be there even if your system gets shut down. The main exceptions here are if you disabled autocommit or started a transaction that you did not commit. – kojow7 May 12 '17 at 06:23
4

You could export your database using the export wizard of your Database IDE(MySQL Workbench,phpmyadmin, Sequel Pro etc...) or else if you are using command line for single database use 

mysqldump database_name > database_name.sql

for Multiple databases

mysqldump --databases database_one database_two > two_databases.sql

for all databases on server

mysqldump --all-databases > all_databases.sql
Kuldeep Dubey
  • 1,097
  • 2
  • 11
  • 33