copy database in phpMyAdmin

I have got a live databse on one server which i administer via phpMyAdmin. I dont want to experiment with it too much so i want to make a copy and transfer onto another server. Does anyone know how i do this using phpMyAdmin or otherwise?
Seb
sebastizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jonesy2kCommented:
you can just copy the binary database files from one machine to another...
On the live database server:
stop mysql
cd /var/lib/mysql (or whatever your mysql database directory is)
tar czvf datbase.tar.gz datbasename/
then copy that tarball to your other machine...
scp database.tar.gz user@server2:~
start mysql

on server 2:
stop mysql
cp ~/database.tar.gz /var/lib/mysql/
cd /var/lib/mysql
tar zxvf database.tar.gz (assuming the tarball is in the /var/lib/mysql directory)
start mysql
Hope this helps,
Jonesy
geir_andersenSystems engineerCommented:
in phpMyAdmin you have the option to export the database.
if you select the database you want to export, select compression zip and check the box: Save as file

when you press the button Go, you'll be asked to download a ZIP file.
That archive will contain a .sql file which holds your database structure and data in the tables.

-Geir
sebastizAuthor Commented:


I cant stop the mysql on the second server as it is owned by a hosting company on a shared server. I understand how to download the server, but how do i then reupload it if i cant stop the mysql server?
Seb
gamebitsCommented:
On the server where you use phpmyadmin use the export function, structure and data, then it's a matter of copy and paste, copy the file from your export then go to the other server open the query window and paste the result of the export, run the query, you will have the exact same database set up on the other server.

I did that with a db where I had a couple thousand entries, I first ran a query with just the create table query than I ran subsequent insert queries, 100 at a time.

If you have a couple millions entries this might be a problem.

Gamebits
Andrew WilkinsonOwnerCommented:
Try SQLyog at

http://www.webyog.com

its a good tool and allows you to synchronise two db's. there is a free version and whilst this won't allow you to do a sync you can do an eport then an import. Its a little easier than phpmyadmin, but I would prefer to do it athe MySQL command prompt for speed..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.