Managed Server MySQL Database Backup

[deeem]

Hi All,

I have recently been having troubles with my managed server so have decided to "switch package" (upgrade) to a better server (Enterprise Server II).

I have been downloading all of my sites via FTP just in case they dont migrate with the change over, but have been told that mySQL databases do not get transferred!!

Is there a simple way that I can backup all my databases and download them ready to put onto the new server?

Thanks in advance

Damon

[celerityfm]

Yeah, thats something that really bums me out about 1and1- they generally have a hands off policy when it comes to transferring information, packages, etc.

Anyways- all you need to do is SSH into your current server and use a program called mysqldump (details below) to save a copy of your database to a file, then download the file via FTP. Then you login to your new server, once its ready, with FTP and upload the file you saved. Then login with SSH and use the mysql command (details, again, below) on the server to load the file into a new database. Note that you'll want to take down your website on your current server after you make the backup, because any new transactions to the database posted after the backup will of course not be saved.

The process of creating the file is detailed here http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/ --- and the process of restoring the file is detailed here http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/2/

Hope this helps!

[Guga-NYC]

I recently switched from a FC4 to CentOS 5.

The problem with the mysql is that in the FC4 (default mysql version) uses latin1 as character sets and the CentOS 5 uses UTF.

Using mysqldump only do not suffice since the new mysql will abort in the middle of the dump to the database.

I'm not sure if this is your issue but I was able to successfully transfer all my mysql databases over following this steps

From the FC4 using mysqldump forcing it to use latin1 as the charaset of the output file. Them use iconv command (still in the FC4) to change from latin1 to UTF8 and also editing the dumpfile and replacing the latin1 for the utf8.

Now you mysqldump file to the new server will work.

If you need more detailed instructions please ask and I'll create more detailed ones.

Cheers

[Guga-NYC]

on Source machine:

- dump database using mysqldump
mysqldump -u admin -p databasename --default-character-set=latin1 > databasename.latin1.sql

- convert file to utf8
iconv --from-code=ISO-8859-1 --to-code=UTF-8 database.latin1.sql > database.utf8.sql

-use vi to edit and replace "SET NAMES latin1" to "SET NAMES utf8" and "DEFAULT CHARSET=latin1" to "DEFAULT CHARSET=utf8"
vi database.utf8.sql
:%s/latin1/utf8/g
:wq



Now you can transfer the database.utf8.sql to the destination machine

on Destination machine:

cat databaseSQL.file.utf8 | mysql --default-character-set=utf8 -u username -p database

REF LINKS
http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
http://mediakey.dk/~cc/howto-convert-text-file-from-utf-8-to-iso-8859-1-encoding/