I've just developed a large database, and am trying to put it online. I want to make sure that all the foreign keys, defaults, constraints, views etc, are preserved. When I try to export from my development PC using phpMyAdmin and then import on my hosted server, it errors out on all the views. Is there any other way to clone a database?
views:
25answers:
2
+2
A:
From the command line in linux, to a linux server, you can:
mysqldump dataBasEnAmE > dump_of_dataBaSeNaMe_2010_3_30.sql
Copy the file to the server, then:
mysql serverDataBaseName < dump_of_dataBaSeNaMe_2010_3_30.sql
If you don't have mysql configured from the command line to accept your user, you may have to specify a user, e.g. root, and put in the database password, so those commands would become:
mysqldump dataBasEnAmE -u root > dump_of_dataBaSeNaMe_2010_3_30.sql
and
mysql serverDataBaseName -u server_root_user < dump_of_dataBaSeNaMe_2010_3_30.sql
respectively.
This is a relatively simple manual backup method as well, depending on the database size.
Tchalvak
2010-03-30 22:08:44
Or a more direct aproach: `mysqldump dataBasEnAm | mysql -h remote_server serverDataBaseName`
WoLpH
2010-03-30 22:10:02
What about us poor Windows users?
Joshua
2010-03-30 22:41:20
Sorry, I don't use it enough to have any easy solution for you there, other than perhaps to use half and half, i.e. exporting via phpadmin in the windows environment and (assuming you're running a linux server) importing the resulting file via the command line in linux.
Tchalvak
2010-03-30 22:51:05
A:
Yes, there are lots of ways. But I'd strongly recommend using the same vendors import functionality as supplied the export functionality. Do you have access to run mysqldump / mysql at both ends?
OTOH, you may run into file upload limits transferring a large file over HTTP and using PHPMyAdmin. Did you check the export? Have you tried exporting just the relevant database schemas without the application data? Did you export the information schema and mysql databases or just the application database?
You may be a bit premature in deciding that you need to try something esle just because what you tried in PMA failed.
C.
symcbean
2010-03-30 22:21:46