We help IT Professionals succeed at work.

Export / Import MySQL database

Richard Korts
on
I have a MySQL database on a web server with about 30 tables. I need to export it and import it into another database. The new database has a different name than the old database.

I exported the database to my local machine using phpmyadmin, selecting ALL tables.

See attached as the first part of the exported SQL file.

Can I just import that file into the other (empty, no tables) database & change Database: `db203755816` in the first line to the new database name?

Thanks
dbbeg.sql
Comment
Watch Question

consultant
Commented:
You should be able to do that. There is no need to change Database: `db203755816', as that is just a comment.
CERTIFIED EXPERT

Commented:
Is it a linux machine that is running MySQL?  If so you can EASILY export/import like so:

Export the database:

mysqldump -u root -p *dbname* > outputfile.sql

Of course replace *dbname* with the name of the database and you can call the output file whatever you like.

Import the database:
Copy the output.sql file to other server and run command:

mysql  -u root -p *dbname* < outputfile.sql

Same as above, use the new database name that you want to import to.
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
You can simply use PhpMyAdmin to import the whole database: i did it svereal times.

Cheers
Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:
There are many way you can use, you can use PhpMyAdmin, or you can use a custom PHP script, you might also be able to use cPanel if you have cPanel installed.

To use a custom PHP script:
http://faq.1and1.com/web_space__access/mysql/13.html

To use PhpMyAdmin:
http://drupal.org/node/81993
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Just a note - the script you posted is incomplete and will fail on the last line. It looks like the file was truncated.

The only things to watch out for (assuming a complete script) are

a) Use database statements - remove if moving to another DB
b) Views and stored procedures need to be exported and imported correctly

Also there is no drop table statements in the script so if the tables already exist this will generate an error but the data should still be imported.
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
Worked like a charm.

Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.