|Home About Us Services Clients Resources Blog Careers Orderbox™||contact us Site map links|
One of the fastest, easiest, most reliable ways to copy MySQL databases across different hosts/servers
There are many ways to copy MySQL databases across hosts. One of the easiest is to use a graphical tool such as SQLYog. However, if you want to copy from one remote server to another remote server, SQLYog imposes an annoying overhead that you could live without. The overhead involved is that SQLYog will first download data from one remote server to your local machine, and then send it from there to the other remote server.
A faster way to copy databases is to copy the MySQL database files directly from one server to another. There are at least two reasons why you might not want to use this approach. One reason is that views are often not replicated correctly in this manner. Another is that, as far as I know, you can't really do this with InnoDB based tables/databases because these are all stored within a single big file.
At any rate, the most reliable way to copy/recreate a
database is to do a
1. Login to the host where the MySQL database to be copied resides.
2. From there, create the database to copy to on the other host.
mysqladmin -h 'destination_host' -u user -p create dest_db
3. After the database is present on the destination server, it
is a simple matter to just pipe the output of
mysqldump -u user --password=XXXX --opt source_db | mysql -h 'destination_host' -u user -p dest_db
4. To apply compression and make the copying even faster, you can try the method below
mysqldump -u user --password=XXXX --opt --compress source_db | mysql -h 'destination_host' -u user -p dest_db
This tip can actually be found in the MySQL reference documentation itself, but it is cool enough to be worthy of a mention here. See http://dev.mysql.com/doc/refman/5.1/en/copying-databases.html for the original source which also shows the quickest way to dump a MySQL database to a gzipped file:
mysqldump --quick db_name | gzip > db_name.gz
Alternative tip: For very large databases, you may want to dump
to a .gz file - as shown above - and do a file transfer of the dumpfile rather than
directly piping the output of mysqldump into
gunzip -c dbname.sql.gz | mysql -D[database name] -h[database host] -u[username] -p[password]
Want to tip via bitcoin?
(address string below for double checking)
© 2021 by Andy Sy
Web Development / Rich Internet Applications (RIA) Development
|© 2003-2019 Neotitans Technologies Inc.||contact/hire us|