The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]
FreeBSD Support
 New Topic  |  Go to Top  |  Go to Topic  |  Search  |  Log In   Newer Topic  |  Older Topic 
 mysql question
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
 
 RE: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
Author: chris 
Date:   16-01-01 22:05

see
http://www.mysql.com/doc/m/y/mysqldump.html

Reply To This Message
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 
 Re: mysql question
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
 Forum List  |  Threaded View   Newer Topic  |  Older Topic 


 Forum List  |  Need a Login? Register Here 
 User Login
 User Name:
 Password:
 Remember my login:
   
 Forgot Your Password?
Enter your email address or user name below and a new password will be sent to the email address associated with your profile.
How to get the most out of the forum

phorum.org