Importing MySQL Datbase files -.sql/.txt

Anon

Hello,
I have been running into problems trying to copy over my database from another server to my new one. I use PhpMyAdmin often and I created backups of each DB which outputs as a text .sql file. It seems if file or amount of data is not extremely large it works very well. If large like some of mine are, it will say it was executed but, no information or tables exist. I have had to cut and paste sections of these files into the text box and submit them and add data this way to get the data loaded into the new DB. I've even tryed cutting and pasting the text file ane executing it via my webmin panel on my new server, but it locks up the browser.

So here is my question. Can I move these .sql file or DB backups on to my new server and type in a command to load them into the database directly? Is this normal, why is it giving me such a hard time?

What other methods could I use? I'd really appreciate your help. Keep in mind, I'm vert new to shell commands.

JohnS

Anon

JohnS
At the command prompt type
MYSQL yourdatabase < yourfile.sql

good day
gd

Anon

Do I need a path with the filename?

like < /home/user/public_html/data.sql

JohnS

dmcinnis

you actually need to add -u username --password=password

ex mysql -u username --password=password < file

[deleted]

phpmyadmin is nice, but not good for backup-restore functions. It F's up at regular intervals.

Instead, issue the mysqldump command on your server (through telnet for example) to get a properly formatted dump.

Then use that output as input for your new database.

If you do a mysql-connect to the 'original' database from the 'new' server, you can do this:

mysqldump database -h <database_host>
-u <username> -p --add-drop-table | mysql <database>

this will connect to the remote server (source) and do a dump of database 'database' then it will pipe all output to your local mysql server and execute it on database 'database'. Et presto, an instant copy of your database.