|
Author: Daniel Schrock
Date: 16-01-01 00:10
does anyone have a good method of moving mysql databases from one server to another?
i've learned quite a bit since I built my server and would like to rebuild but am forced to leave it alone (with the exception of cvsup's) due to the fact that it has a few users using mysql with php and i can't find a way to move their databases.
the main issues are fairly minor, but i'd like to start over just the same (yes, i agree this is not a good practice, but at the time i installed all of this, i was just learning and made some mistakes).
|
|
Reply To This Message
|
|
Author: el_kab0ng
Date: 16-01-01 17:52
You could always do a mysqldump into a file, then FTP that file to your new server...then of course import that file into your new mySQL database.
That to me would seem the easiest way I would know...
|
|
Reply To This Message
|
|
Author: dartos
Date: 16-01-01 20:45
All you have to do is tar and gzip the database directory, copy it to the mysql database directory of the next server (/var/db/mysql), untar ungzip it and your all set.
I do this almost daily (via cron) as a method of backing up to a different mysql server.
|
|
Reply To This Message
|
|
Author: chris
Date: 16-01-01 22:05
mysqldump is always good if you are moving from one 3.22 to 3.23 you will have to use this function
see
|
|
Reply To This Message
|
|
Author: Dan Langille
Date: 17-01-01 02:21
I would advise against this. The database may leave the files in an inconsistant state. It's far better to use dump and restore. See <a href="http://freebsddiary.org/mysql.html">http://freebsddiary.org/mysql.html</a> for details. This is the process I use to backup the database used for this Phorum and for the home pagefor this website.
Seriously: you may not have a problem with the gzip method. But it not definitely not advised as a reliable method. If someone is concerned about this issue, I suggest you ask on a mySQL mailing list and report back.
|
|
Reply To This Message
|
|
Author: Daniel Schrock
Date: 17-01-01 18:44
thanks for the input all!
i've tried the gzip method before, but it failed due to the changes from 22 to 23
i'll give the dump a try and let you know how it works out.
thanks!
|
|
Reply To This Message
|
|
Author: dartos
Date: 17-01-01 19:14
Not reliable? What do you mean? I check md5sum each time I do this and they have not changed so far, with over 500 backups. And actually I use tar and bzip2 not tar and gzip.
|
|
Reply To This Message
|
|
Author: dartos
Date: 17-01-01 19:22
Actually the script you pointed out on http://freebsddiary.org/mysql.html uses tar and gzip to backup the database, so what is your point in saying this method is "not reliable"? Can you not read and understand the scripts that you use or are you just plain stupid?
|
|
Reply To This Message
|
|
Author: el_kab0ng
Date: 17-01-01 20:17
Calm down. No sense venting aggression over the variety of ways you can migrate a mySQL database. If you do it one way, so be it...we're all here to learn...not piss and moan when someone expresses whats easiest for them.
|
|
Reply To This Message
|
|
Author: Dan Langille
Date: 17-01-01 20:36
1 - It uses tar and gzip to backup the output of mysqldump, not the database itself. That was my point in suggesting dumping the database and restoring it.
2 - Don't use that tone in this phorum. Save it for SlashDot. If you can't act decently, take it somewhere else please. There is no room for that attitude here.
|
|
Reply To This Message
|
|
Author: Dan Langille
Date: 17-01-01 20:38
This *is* the recommended method. *Especially* when moving from one database version to another. mysqldump will convert the database to text. Loading that into the new database server will remove any chance that the underlying binary files have a new format.
If anyone doubts this advice, please consult the mySQL site and documentation.
|
|
Reply To This Message
|
|
Author: karlo
Date: 18-01-01 04:20
If you are copying databases from a 3.23.xx server to another 3.23.xx server, there is no risk in simply using tar-gzip to archive and then copy the directory. But migrating it from a 3.22.xx to a 3.23.xx server or vice versa you are forced to use mysqldump since the file formats differ somewhat. In either case, one must be sure no users are writing to the database at the time for obvious reasons.
|
|
Reply To This Message
|
|
Author: Gregory Gaskill
Date: 18-01-01 04:24
phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/) makes moving databases around pretty simple. It can dump database structure only, or structure and data to a text file, which can be easily uploaded to phpMyAdmin on the other server. I've moved many databases this way and it usually works well...
|
|
Reply To This Message
|
|
