Moving MySQL DBs from one server to another
 

How do I move some MySQL databases from one server to another? Is there a way to copy the files without doing MySQL commands?

yeah you can but ,

you should use mysqldump

something like this


mysqldump -u username -p dbname > database.db

If it's the same OS version and filesystems on both you can do db transfers without dumping the database. You _have_ to stop the database on both ends though while you're transferring.

http://www.mysql.com/

All you need to know about dumping.


If systems are the same just copy the data files over as already mentioned.

The database in question is no longer being changed, but it *is* being access. Also, both servers are the same OS/Webserver, just different versions:

Redhat Linux 6.1 / Apache -> Red Hat Linux 7.2 / Apahce

It depends on the size of your DB. If you have a DB that is small, you may be able to use phpMyAdmin to move the database. Otherwise, you can use phpMyAdmin to export the data (save it as a file), FTP the .sql file to the new server, then log in to the new server and type

/path/to/bin/mysql -u $mysqlusername -p$mysqlpassword $mysqldatabasename < $file.sql

I have done it a few zillion times so if you need some help, drop me a line and I will see what I can do.

Armed & Hammered, hit me up on ICQ, it's in my sig.

I do not have phpMyAdmin, I have Webmin. The database is not very big at all, maybe 1 MB in size. Where are these MySQL databases normally kept in Linux?

Nevermind, I found them in /var/lib/mysql/. Can't FTP to it, no access. Damn.

HQ,
If you don't have command line access, you can find a PHP or CGI script that will do the backup for you. Then simply FTP the backup file out to your new server.

Here is an example, it's a little much though for your needs:
http://geeklog.sourceforge.net/sampl...ackup-database

This is command line PHP, but you can change that easily by removing the first line, etc.


Good luck,

PxG

Thanks PxG, but I have command line access. I can get superuse access but I wish I could just FTP to the location to make my life easier. Will a simply copy from one server to the next suffice?

Since you have root access you can simply tar the var/ directory inside mysql and untar it on the new server....as long as the new version of MySQL supports the table type you used to use you should be fine. I use this method regularly for both backups and database duplication

Excellent thanks.

Will I have to shut down mysql first?

.: bump :.

http://www.phpmyadmin.net

It's free. Use it to move your DB with ease.

No, it doesn't need to be shutdown to do the backup....of course with it not being shutdown you do run the risk of the original and the copy not being in sync if more data is added/updated to the original. But since you're only regurgitating data, you should have no problems

The only problem I have with PHPmyadmin is that it wasn't designed to handle backups of large databases (both backing up and restoring). Not that this couldn't be resolved...heck if I put some effort into it I'm sure I could create a patch for it. But the really large databases that take more than a minute or so to backup will typically cause the browser to time out....it's one of the main reasons I stopped using it for backup purposes since most of my databases are 1GB+ in size.

However, for the average database, PHPmyadmin is a great program to use. Especially if you're not very unix oriented

Edit: Fixed a few grammatical mistakes that made me sound like I was on crack

Hey HQ.
Its that simple...
1. Shutdown the MySQL server (on both machine). [mysqladmin shutdown]
2. Copy the DB files of the specific Databases/Tables you want to copy to the other server.
3. start the MySQL server again.

Only reservation - Not working on INNODB Tables (for that you will need either to alter the table type or copy it to a duplicate table of type INNODB or do a dump) - but im not sure you are using this.

:thumbsup

What happens if the database is being accessed or updated while I am copying or downloading it?

Yeah that could be a bad problem. There are fixes for things like that, but I do not know the technicality of them. Webmin does not time out, for example, on mysql queries that take a couple minutes to calculate.

That's basically correct. In the newer versions of phpMyAdmin, you can do a dump of the DB and save it as a tar. Granted, you can't import that massive .sql file into phpMyAdmin with any degree of success but it works great for dumping the DB. You'll still need to SSH into the server to import the data and structure to the new DB. I have had years of MySQL experience and I still prefer to use phpMyAdmin over command line.

Yes, I think I will shutdwon mysql, just in case. Then just copy the database files found in /var/lib/mysql/ to /var/lib/mysql/ of the new server? To be sure, in /var/lib/mysql/ in my old server, I see a bunch of directories, wach directory being the name of the database i have. There are also a few .err and .pid files that appear to be useless. Is this true? I also see a mysql.sock file. What is that? Once I go inside the directories of the databases in question, I see .MYD, .MYI, and .frm files named after each of the tables in the databases. Does all this sound proper? Do I just have to copy the directories of the databases I have to copy over and that's it? Or do I have to be concerned with these other files?

Really man, use phpMyAdmin to dump the database and import it to the new server with the good 'ol command line method I described above. There are WAY too many variables to make the way you described a viable option. You could potentially screw things up to the point that you will need to reinstall MySQL.

Is phpMyAdmin secure?

Why the hell would you like to install something just o move a database from place to place- nontheless a PHP-remote-admin-tool?
HQ: Yes - what you said is correct. Just copy the directories you want to copy including EVERYTHING in it.
Dont need to copy mysql.sock

I dont see what might go so wrong that you will have to reinstall mysql like Armed & Hammered said.
Armed & Hammered - If you care to elaborate...

The server is brand new with nothing on it... If I have to reinstall mysql, it would be no big deal. If I have to reinstall mysq in a month's time, once it is in full swing, then that would suck.

Yeah, I would rather not install new software on 2 boxes just to copy directories, if that is all I need to do.

I recently moved my DB: I used mysqldump to create a dump SQL file, FTP:ed it to the new server and read it in into my new DB.
One command at each localtion, from command line.

You have to shutdown mysql before running mysqldump, right? What command reads in the dump into the new DB?

/path/to/bin/mysql -u $mysqlusername -p$mysqlpassword $mysqldatabasename < $file.sql

And there is no need to shut down mysql to dump the DB.

Thanks.

Armed & Hammered, hit me on ICQ.

Please give me an example of how to dump the file.sql with mysqldump command. I am not sure which options to use. Should I use "--add-drop-table"?

I do not want to delete the databases, I just want a perfect copy of them for the new server.

When migrating and old mysql database on an old server to a new server should I copy the "mysql" database over too? Or should I just try to manually set that one up the same afterwards?

I'd like to know the answer to this last question, as well...

Anyone?

Let me understand what you're saying: you're asking whether or not you should move the database named "mysql" over to your new server (from the old). You can do that, since the password hashing algorithm is the same from version to version, but you might have problems with permissions and stuff.

In case you didn't know, the mysql db houses all of your user and privilege information. So the users should be OK, everyone will have the same passwords as before, including root. But for things like privileges and such, if you are missing some of the databases that the data in these tables refer to you might have some problems. That's not to say you will, but you have references to things that aren't really there, which is never too good. The other problem is hosts... you might not want the same hosts access to the dbs in your system, etc., but then again maybe everything remains roughly the same for you after the move.

Look, it's not that big of a deal to add some users with privileges, I have always done it manually.

Hope this helps,

PxG

Ok, here is what you want:

/path/to/mysqldump -u username -p dbname > dbdumpfile.sql

Now you have a dump of the entire database, as of that moment. You don't need to stop your db server, you don't need to reboot or anything like that, just run this command.

Then, SSH to you new server and run mysql:
/path/to/mysql -u root -p
(once logged in and create your db...)
> CREATE DATABASE dbname;

(now create the user you want)
> GRANT all on dbname.* to username@localhost identified by 'passwordhere';

(you can modify the privileges if you dont want to give all)

(now log out and dump your old db in)
> quit
/path/to/mysql -u username -p dbname < dbdumpfile.sql

and presto your db is now in your new mysql.
(note it will always prompt you for a password when doing this.)

If you need more help, just contact us through our URL and we'll
set you up.

PxG

I think you perfectly understand what I am saying. Every single database and user on the old system has to be transported to the new system, therefore the table named "mysql" should work fine (according to what you say.)

Thanks PxG, but before I read your post I tar'ed everything up and FTPed it from the old server to my home computer to the new server... all the databases were properly unpacked with proper file permissions and ownerships. So far everything works but I have yet to actually copy the "mysql" database in, I was waiting on that one (because I had to overwrite it, the other ones I did not have to overwrite because the new server is a fresh clean install of mysql.)

One last thing, once you add new users make SURE you reload mysql so the changes can take effect. I can't tell you how many times I have shot myself in the foot because I didn't reload mysql and therefore couldn't get the username to log in to the DB.

also since the database isn't big it doesn't hurt to run a isamchk or myisamchk - won't take much time at all - just to verify that the data is OK.

isamchk *.ISM i think and myisamchk *.MYI

Being accessed isn't an issue...if it's being updated than the worst that can happen is your backup will be outdated and not have that new updated information in it...but really that'll be a problem regardless of how you backup the database =)

How do you reload mysql? Shut it off and turn it back on?

Oh yeah, when I tar'ed the whole mysql directory up, I turned off mysql while doing that just to make sure that the files were not being changed as they were being copied. I was not sure if that could even happen, but it was a precaution I took.

Great, I'll do that... isamchk exists, but not myisamchk... unless it is a mysql command and is not available in linux.

salsbury, or any one, what is the difference between *.ISM and *.MYI databases? I noticed that some of my older databases are *.ISM and my newer ones are *.MYI. What is the difference?

Bump.

I started a new thread on problems with migration the "mysql" database (all others worked). Here it is:
http://www.gofuckyourself.com/showth...threadid=80312