Moving a large Mysql database

will this work.

[mack]

I need to move a large database from one computer to another. I have tar.gz the entire /var/lib/mysql directory and copied it to www root. I then move to var/lib/mysql on the other system and use wget to bring the tar.gz archive into the correct location on the new system. I then uncomress the archive into it's new location. Question is will this preserve usernames/passwords etc. I am using the same os and same build of mysql on each system to avoid version diferences.

Is this a safe way to make the change.

Mack.

[Lord Majestic]

AFAIK all user/passwords etc data should be in the databases themselves (as requires by relational database definition?), so you should be okay but if I were you I'd test that before switching off old computer!

It is not safe however to transfer those in plain text over HTTP - I'd use something more secure, even if its just SSL (password protected).

[ppg]

I don't know if that will work since I've never tried it, but when I need to transfer databases I use the mysqldump command (mysqldump database_name > filename.sql) and then read it back into the new database by creating the database on the new machine (create database database_name) then reading it back in with the mysql client proram (mysql database_name < filename.sql)

You may need to specify a root username/password along with the mysql command for the mysql to work ok.

I've never tried this with the mysql databse where the usernames/passwords etc are kept though.

Course, you may well already know this. Any help?

[mack]

I think it will be ok from a security point of view because the http part will be across local network. I tried mysql dump but have been having issues getting it to work properly. The DB is in excess of 40gig lol

Mack.

[killroy]

Just plain cobies of the DB dolders always worked fine for me. In fact, instead of CREATEing the databases, you can just create the folders.

SN

[mcavic]

Yep, just copying the mysql folder will work. You might want to shut down mysql while you're doing it, or issue a Flush Tables before and after.

If tar is happy extracting the file when it gets there, then you know that the transfer was successful.

[coopster]

>>Question is will this preserve usernames/passwords

Yes it will preserve the usernames but the passwords may be another issue depending on how the passwords (or any other encrypted values for that matter) were created. Was MySQL used for encryption via the PASSWORD() function or did you let the OS handle the encryption in any of your table columns via ENCRYPT() [dev.mysql.com] or other application-based encryption functions?

Also, a note of caution:
The password hashing mechanism was updated in MySQL 4.1 [dev.mysql.com] so be ready if you are porting to this version.