Transfer Mysql database to another computer [closed]

Question

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.

Answer 1

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.

Worth noting that the table names may become case sensitive on one system when they weren't on the original. It depends on the config at both ends - in particular the case sensitivity (or otherwise) of the filesystem.

Answer 2

Rather than exporting the databases one by one, you can export them all with a single command. Then import them. eg.

mysqldump -u username -p --all-databases > c:\alldbs.sql 

PS- The biggest advantage is that you do not lose the user privileges.