Transfer Mysql database to another computer
Solution 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.
Solution 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.
Solution 3
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.
Solution 4
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.
Solution 5
From the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/copying-databases.html
shell> mysqldump --quick db_name | gzip > db_name.gz
Transfer the file containing the database contents to the target machine and run these commands there:
shell> mysqladmin create db_name
shell> gunzip < db_name.gz | mysql db_name
fmsf
CEO / CTO at https://temploescondido.pt/ Engineer at Palantir
Updated on April 14, 2020Comments
-
fmsf about 4 years
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 intoSQL
and insert it on the other computer. Just trying to save some time :)Thank you all.
-
Jedihomer Townend over 15 yearsIf both versions support it (I think even 4.1 does) using the "-e" option should speed up the process quite a bit.
-
Artur Kędzior about 13 yearsWhat about the situation where you can't do mysqldump as the OS is corrupted? I've heard you can copy .frm, .MYI, and .MYD files.
-
benlumley about 13 yearsYou can copy the entire /var/lib/mysql/<dbname> folder in that case. Works well for myisam tables, think there can be problems with innodb. You sometimes need to "create database <dbname>" on the new server and then replace the folder with the copied one to get mysql to notice it - not done this enough times to understand the vagaries properly!
-
bortzmeyer over 12 yearsVery convenient and works for me. Thanks.
-
zhongxiao37 almost 12 yearsThe solution is old. with the new mysql workbench, you can export/import the database easily.
-
Pratyush almost 12 yearsFor restoring in one command:
mysql -u username -p < alldbs.sql