Copy MySQL database

dulpit

Hey, so I have a mysql database on machine at work, i want to be able to work on it @ home too, is there an easy way of copying the database onto an external hard drive & then copy it back onto computer @ home?

It would take too long to create all the tables again...

Oh, and I'm not worried about the data stored in the tables, just more want to copy the full structure of db over...

Cheers...

lynchie

mysqldump -u <username> -p <database name> > backup.sql

Then on machine at home..

mysqladmin -u <username> -p create <database name>

then mysql -u <username> -p <database name> < backup.sql

danbhala

do you have phpMyAdmin ?

if so, its pretty easy to export the db's data & structure to a sql text file.

*Above Post > My Post

dulpit

lynchie wrote:

mysqldump -u <username> -p <database name> > backup.sql

Then on machine at home..

mysqladmin -u <username> -p create <database name>

then mysql -u <username> -p <database name> < backup.sql

Ok, so if my username is root, password is pass, the database name is data would i do this:

1. Open command prompt
2. Type: mysqldump -u root -p pass data > backup.sql

All on one line? Should I specify a place to put backup.sql? (i.e. C:\backup.sql)?

Then do the reverse on second machine?

1. Open command prompt
2: Type: mysqladmin -u root -p pass create data
3: mysql -u root -p pass data < backup.sql

All on one line? Should I specify where backup.sql is? (i.e. F:\backup.sql)?

Sorry for all questions, i'm rubbish at using command prompt and stuff...

Edit: I presume this won't delete the original db, will it?

lynchie

yup specify the full path i.e. c:\backup.sql

No, it wont delete the source DB. mysqldump is used for creating backups of the db.

What you have above is all fine. Type it into a command prompt window. If it doesnt work, you may not have the mysql bin directory on your path. If this is the case, make sure you add it to your path. Alternatively, download the mysql workbench and use the mysql administrator to create the backup for you.

dulpit

Never realised that MySQL administrator could do that... (it's kinda obvious now that it would)

Did a test moving from comp. to laptop @ work, worked a treat... Cheers lads!

Hyzepher

I always connent remotely and copy it that way - use something like Navicat

amen

are you allowed to take a database home with you?

Most places have Data security policies that would prohibit you from doing this.

dulpit

amen wrote:

are you allowed to take a database home with you?

Most places have Data security policies that would prohibit you from doing this.

Working in a tiny office, i'm the only one working on this...