Copying a MySQL Database From One Machine to Another
Copying a database from one server to another is
relatively simple in MySQL......
Updated 10/26/2006: Note on
single-transaction option
In our example we assume we have a database named "mydb" that we want to copy from one MySQL server to another. Also assume we want to dump it to the admin user's home directory.
Open terminal or log in via ssh to the source server.
Dump the database
% mysqldump -u root -p --databases --opt mydb > /Users/admin/mydb.sql
Copy the database to the destination server using scp or whatever
If you wish to edit the name of the database in the destination server (perhaps to prevent overwriting an existing one), edit the mydb.sql file. NOTE: the --opt feature creates very long INSERT lines in the sql file that are generally unreadable by most text editors. So, if you wish to open the sql file and view or edit it, use the skip-extended-insert option to create a single INSERT statement for every table row. While this meakes the file size bigger, it makes the file easily readable by BBEdit or command line editors. Here is how the command would look with that option:
% mysqldump -u root -p --databases --opt --skip-extended-insert mydb > /Users/admin/mydb.sql
Finally import into the mysql server on the destination server
% mysql -u root -p < mydb.sql
... done.
If your database consists only of transactional tables such as InnoDB and you want an integral snapshot of an instant in time without locking out current users, then we can use the single-transaction option which is mutually exclusive to the lock-tables option. The single-transaction option is available since MySQL version 4.0.2. So out improved mysqldump becomes:
% mysqldump -u root -p --databases --opt --skip-lock-tables --single-transaction mydb > /Users/admin/mydb.sql
As we say there are "may ways to skin a cat", so if like me, you have your admin user and password in your local machine's my.cnf file and that same username and password has sufficient privileges on the server, then you can use a unix pipe and one single command to simultaneously dump from the server across the netowkr and load into your local development mysql. Generally after each release of my actively developed app (about every 1 or 2 weeks), I take a complete dump of the database from the server to my local machine, so I am always dealing with the full "real life" data rather than "made up" database seed data during development. Here is how to to do it, and it may take a while, so don't panic if your 500MB of data takes 10 minutes to dump and load:
% mysqldump -h myserver.local --opt --skip-lock-tables --single-transaction --databases mydb | mysql -h localhost
In our example we assume we have a database named "mydb" that we want to copy from one MySQL server to another. Also assume we want to dump it to the admin user's home directory.
Open terminal or log in via ssh to the source server.
Dump the database
% mysqldump -u root -p --databases --opt mydb > /Users/admin/mydb.sql
Copy the database to the destination server using scp or whatever
If you wish to edit the name of the database in the destination server (perhaps to prevent overwriting an existing one), edit the mydb.sql file. NOTE: the --opt feature creates very long INSERT lines in the sql file that are generally unreadable by most text editors. So, if you wish to open the sql file and view or edit it, use the skip-extended-insert option to create a single INSERT statement for every table row. While this meakes the file size bigger, it makes the file easily readable by BBEdit or command line editors. Here is how the command would look with that option:
% mysqldump -u root -p --databases --opt --skip-extended-insert mydb > /Users/admin/mydb.sql
Finally import into the mysql server on the destination server
% mysql -u root -p < mydb.sql
... done.
If your database consists only of transactional tables such as InnoDB and you want an integral snapshot of an instant in time without locking out current users, then we can use the single-transaction option which is mutually exclusive to the lock-tables option. The single-transaction option is available since MySQL version 4.0.2. So out improved mysqldump becomes:
% mysqldump -u root -p --databases --opt --skip-lock-tables --single-transaction mydb > /Users/admin/mydb.sql
As we say there are "may ways to skin a cat", so if like me, you have your admin user and password in your local machine's my.cnf file and that same username and password has sufficient privileges on the server, then you can use a unix pipe and one single command to simultaneously dump from the server across the netowkr and load into your local development mysql. Generally after each release of my actively developed app (about every 1 or 2 weeks), I take a complete dump of the database from the server to my local machine, so I am always dealing with the full "real life" data rather than "made up" database seed data during development. Here is how to to do it, and it may take a while, so don't panic if your 500MB of data takes 10 minutes to dump and load:
% mysqldump -h myserver.local --opt --skip-lock-tables --single-transaction --databases mydb | mysql -h localhost
Posted: Thursday - December 23, 2004 at 05:02 PM
Quick Links
Statistics
Total entries in this blog:
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM
Total entries in this category:
Published On: Dec 29, 2008 02:27 PM