Hey,
I have a mysql database filled up and running on a windows computer, is there any tool to transfer the database to another computer (running ubuntu)?
Else I'll just write a script to take all the data base into SQL and insert it on the other computer. Just trying to save some time :)
Thank you all.
You can make a backup using any gui tool, like Mysql Administrator (http://dev.mysql.com/downloads/gui-tools/ on Windows, aptitude install mysql-admin on Ubuntu) or phpmyadmin (http://www.phpmyadmin.net/home_page/index.php on Windows, aptitude install phpmyadmin on Ubuntu), and then recover it in the other computer.
The tool you speak of already exists: mysqldump
It dumps out to sql, which you can then copy to another machine and re-load.
eg:
on source:
mysqldump -u username -p databasename > dumpfile.sql
Then use ftp/rsync/whatever to move the file to the destination machine, and on there, create an empty database to import into and run:
mysql -u username -p databasename < dumpfile.sql
You'll also need to set up permissions on any users that may have been transferred as well, as they aren't held within the database.
Alternatively, you can copy the files from the mysql data dir - but mysqldump is the easiest/most reliable way.
A note on going windows/linux is that the table names may become case sensitive on linux when they weren't on windows. It depends on the config at both ends. But its worth remembering.
The on-disk files are 100% compatible between all editions of MySQL. You just have to watch out for the case of the filenames because it matters on Unix, whilst it only sometimes does on Windows.
And remember to stop MySQL before you take a copy. MyISAM files are okay to take a copy whilst running, but InnoDB files are not really safe to do that and Windows' MySQL defaults to InnoDB files.
Be careful with character sets when using mysqldump, especially on windows. I prefer to explicitly set the charset my db uses and use the --result-file=file instead of using the > operator lest something becomes mangled.
mysqldump should dump it out correctly and preserve the character set of anything in the database. It is advisable to move to the same version of mysql if you don't want problems.
The file produced by mysqldump is NOT a text file, despite appearances, don't edit it with notepad.exe etc or you'll end up in trouble.
Dumps produced by third party gui tools, especially phpmyadmin, are generally inaccurate and won't necessarily restore correctly.
I often find myself using some variation of this one-liner to copy a database over the network.
mysqldump --opt --compress --user=username database | mysql --user=username2 --password=p2 --host=hostB -D database -C database
Which I originally read about here:
http://www.igvita.com/2007/10/10/hands-on-mysql-backup-migration/