copying a mysql database

I would like to copy an entire database to a different server. I have it set up on my machine, can I simply make a copy of the "data" directory? what if there are other databases on the new server? how can I add mine without overwriting the files that are associated with these other databases?

Any ideas?

Thanks,

Peter

use phpmyadmin to do an export of the db structure and data. You will need to create the actual database that the tables will be created in. If you don't have PHPMyAdmin, you can do the same thing with mysqldump at the command prompt.

I'd rather copy the directories if that's possible - this database is several gigs.

tar up the specific DB from your server. cp it over to the other and untar it on the new server where your current DBs' are stored.

I have the database already created on the second machine, but empty. So can I just copy the directory for that database? should I create tables first?

Anonymous, that's what I would like to do, what I am asking is how? I can't send it over the net, I have to burn it. I am not concerned about the zipping technique, that I can figure out. I just need to know what to copy and how to ensure that I don't damage any other existing databases.

Do you know what the files 'ib_arch_log_0000000000', 'ib_logfile0', 'ib_logfile1' and 'ibdata1' are for?

This is what I plan, let me know what you think:
within the "dbname" directory I will copy all files. I will then zip them and burn them, unzip them into the new "dbname" directory and they should be good to go (?) Will this work?

I think copying should work. There is no need to create tables because they would be overwritten anyway.

I have just moved my database (locally) away and then moved it back and everything works.

Ashkhan that is wonderful news. I will try and create a sample remotely and then see if it works too.

Thanks

Well, I am not sure how to do this. I don't have access to the directories on my second machine, so I can't test using that...

Could someone running mysql locally please send me a sample table? All I would need are the .frm, .MYD and .MYI files, and the database name & type (I assume MyISAM).

Thanks!

Peter

stop the mysql server, pack all of the files from db directory, burn it on the CD, get it over to the other server, stop that server, unpack all of the files from the CD to the db directory, and start that mysql server again...

simple, eh? ;)

well, I don't own the other database server so I must be absolutely sure that this process will not interfere with the other databases on that server. Ideally I would not want to overwrite ANY files at all, except those that I know are part of my database (within the '../data/databasename' directory).

Locally, when I removed .frm, .MYD and .MYI files the respective table dissappeared, and when I replaced them it showed up again (I did not have to stop & restart the server). I just need to test to make sure that I can do this with a database table that has not been created locally.

If you want to test it yourself, I have created a zip file with a copy of a small database table. The database name is testdb, so if you want to see if this works, follow these steps:
1. create a new database called "testdb"
2. download http://mugane.220kv.com/docs/testdb.zip
3. Unzip and place the three files in your ../data/testdb folder (this should be created when you create the database)
4. using phpmyadmin, navisoft, or whatever, check to see if the database contains a table "testtable" with two records. If it does, then this procedure works. If it does not, please let me know.

You should ask the webmaster of that server what is the best way to do it.

I wish that there was a webmaster of that site. Unfortunately there are only technicians that don't know how to do anything really so I have to tell them, hence the need to make sure it works... Also I pretty much established that this is the best way to do it if it can be done, I'm just trying to run a control, so to speak. All it would take is 1 minute of your time to create a test database, test table, enter a record and zip the files - please?

I have tested this procedure and this is a note to confirm that it works. Hurrah!

Thank you very much Blindeddie!

Anytime!!!