Full copy of mysql

Is this possible over a network.

[mack]

I have been trying to work out a method of replicating a large mysql database into another box also running mysql.

I tried using phpmyadmin to create an export version of the database. I want to download a compressed gzip version to a lan computer then use move to the phpmyadmin service for the database that is to receive the new data/structure. I can then browse to the sql file and import all data.

My database is about 3gig and the download always stalls at aroung 30 meg.

I think using shell may be the best option using mysqldump.

Any ideas?

Mack.

[jcoronella]

You can do a dump, but for a database that big I stop mysql gzip the actual database files and transfer them. Much faster and lighter on the server than mysqldump.

I think there might be a perl utility out there for this.

[mack]

Thanks yery much for the reply.

So, should I copy the entire contents of /var/mysql/databasename to a temp location then transfere it to the other machine? using nfs/ftp etc and place it into the new location var/mysql....

Mack.

[denisdekat]

a mysql dump goes like this on the command line:

server_old# mysqldump name_of_DB > newfile.sql

newfile.sql should then be a text file, check it out.

Then in the new server, assuming you created the db

server_new# mysql database_name < newfile.sql

If you have a web based gui like phpMyAdmin, it should be easier :)

The uglier way to do it is to take the directory tar zip it, then move it to new server, have the zip file unpacked and replace the directory of the copy you started on the new server :)

Hope this helps!

[JasonD]

MySQL has the inbuilt ability to replicate across servers by utiising a master slave relationship between them.

A nice tutorial on this can be found at [phpbuilder.com...]