UPDATED: A simple method of transferring MySQL Database(s)/Tables between servers
We’ve recently been working on a Web-Server migration for a regular client at work, part of which involved transferring the MySQL Databases (or strictly the schemas) for each of their websites from their old Web-Server to the shiny new replacement. In doing this, I rediscovered a really handy method which really simplifies transferring MySQL data between servers, it goes something like this:
mysqldump -u SOURCE_MYSQL_USER -pSOURCE_MYSQL_PASSWORD --opt SOURCE_DATABASE_NAME | mysql --host=DESTINATION_SERVER_IP -u DESTINATION_MYSQL_USER -pDESTINATION_MYSQL_PASSWORD -C DESTINATION_DATABASE_NAME
To use the above command, you need to shell in to the “Source” Web-Server and run the above shell command (replacing the items in capitals with the relevant details).
Make sure you don’t leave a space between the “-p” and the MySQL password otherwise it won’t work!
The basic principal is this: Perform a MySQL Dump (which outputs the database data as importable SQL queries) and use the UNIX pipe to redirect that output SQL to another MySQL command which logs in to the destination MySQL server and imports the SQL which was output by MySQL Dump.
If you only have shell access to shell in to the destination server, the following is what you need:
mysqldump -u SOURCE_MYSQL_USER -pSOURCE_MYSQL_PASSWORD --host=SOURCE_SERVER_IP --opt SOURCE_DATABASE_NAME | mysql -u DESTINATION_MYSQL_USER -pDESTINATION_MYSQL_PASSWORD -C DESTINATION_DATABASE_NAME
Things to bear in mind:
- This will only work on Linux/UNIX/BSD type servers – but we don’t use Windows toy servers anyway do we
- You will need to have mysqldump installed and available on the server you’re shelling in on
- This is specific to MySQL
- The MySQL users you use must have relevant permissions on each server (the “Source” user must have select permissions on the relevant database(s)/schema(s) and table(s), the “Destination” user must have create permissions). I usually use the root MySQL user or a user with equivalent permissions.
- Make sure you don’t leave a space between the “-p” and the password
- You’d be wise to ensure that the Database(s)/Schema(s)/Table(s) you are transferring do not exist on the “Destination” server as I believe by default they will be overwritten
- As ever…MAKE A BACKUP OF SOURCE AND DESTINATION BEFORE YOU START!!! I cannot emphasise that enough! Be safe or prepare to be in a world of pain!
- Also, I’d generally recommend that if you haven’t already, you should set a password for your MySQL root user – You can do this from the Linux/UNIX/BSD using:
mysqladmin -u root password NEW_ROOT_PASSWORD
That’s it…Hopefully that’ll be useful to somebody
|This entry was posted by Neil Craig on April 15, 2010 at 8:07 am, and is filed under General Web-Development, MySQL. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
No trackbacks yet.
about 1 year ago - 3 comments
AS I type, i’m setting up an Ubuntu server to use a virtual server host on my test rig. Slight problem though, after getting bored/frustrated with command line configuring (odd because usually command line is my preference) I got lazy and decided to install Gnome and virt-manager. All good, so I thought, I added xrdp More >
about 1 year ago - 4 comments
It’s been quite some time since I’ve had the pleasure of using BackupPC, a delightfully simple to use yet very effective, free, open source, cross-platform backup system. My test rig was sorely overdue some proper backups…So I decided to install BackupPC on a central server to back up the others (which are all Debian or More >
about 1 year ago - No comments
It’s been quite some time since I’ve posted an article here and this one will be very brief… If you feel so inclined and are able, please consider sponsoring the UK National Computer Archives Colossus display. More information can be found here: http://www.colossusonline.org/ I’ll soon be posting on my findings over the last year or More >
about 2 years ago - 1 comment
This post is a really quick “how to” – working with ProFTPd on Debian Linux and will explain how to chroot your FTP users, speed up connections and enable passive connections which can be very handy for those behind firewalls. You’ll first need to locate your main ProFTPd config file which on my system is More >
about 3 years ago - No comments
Lighttpd is an alternative to the very popular Apache web-server – I have just found out that Lighttpd can draw it’s vhost configuration from a MySQL Database which is definitely a one-up on Apache, at least as far as I know.
about 3 years ago - 5 comments
A brief article describing how I set up Qmail (on Debian Etch) running virtual domains to forward Email to another Email server using a simple addition to a Qmail config file.