Keeping it Small and Simple

2008.03.18

Copying mysql databases the simple way

Filed under: MySQL — Tags: , , , , — Lorenzo E. Danielsson @ 22:55

Some people seem to be bent on making their own lives as difficult as possible. I’ve seen friends got through some very complex procedures (complete with heavy breathing and all) just to copy a mysql database from one host to another. I do admit that some solutions I have seen have been really creative.

I lack imagination, so I just do it the simple way instead. First of all, I have a server called, amazingly enough, db. This server happens to be running Debian, but in this case that is not important whatsoever (apart from the fact that it never crashes). I have set up a special account for myself that gives me global access from any host:

mysql> GRANT ALL ON *.* TO led@'%' IDENTIFIED BY 'somethingthatyoucanrem3mber';

No, that is not my actual password. Now let’s suppose that I have been toying around with a database called volcanoes, that holds loads of data relating to volcanoes. I have been developing this on my laptop and now I want to copy it over to the database server. On the laptop, which only I use, the mysql root user doesn’t even have a password set (bad, bad me).

In a terminal (on the laptop):

% mysqldump -u root -B volcanoes | mysql -h db -u led -p
Enter password:
%

Anything more complex than this is just pure torture. The -B volcanoes part will ensure that the dump contains the CREATE DATABASE statement. Without this you would have to ensure that there is already an (empty) database on the database server before issuing the command.

Of course, you have lots and lots of options. For example, if you want only want to copy the tables structures and not the data, add -d to mysqldump.

% mysqldump -u root -d -B volcanoes | mysql -h db -u led -p
Enter password:
%

If I instead wanted to copy a database from the server to my laptop, perhaps to work on it at home over the weekend, that is equally simple. Let’s say I have another database kanji located on the server.

% mysqldump -h db -u led -p -B kanji | mysql -u root
Enter password: 
%

You can do a lot more as well. The mysql and mysqldump man pages will give you all the available options.

Note: If you are running mysql on a public server, such as a webhost, it is a good idea to at least think about it for a minute before you issue a GRANT ALL ON *.* TO myuser@'%'. If you are aware of the implications and don’t care cool. Your database, your free will.

Advertisements

5 Comments »

  1. Cool stuff.. thxs, I will try this..

    Comment by Rajesh — 2008.03.19 @ 18:36

  2. Good approach! Simple and right to the point! There’s just one thing. Sometimes you can’t have full access to the database server, like some hosting servers, where you should use cPanel to create or manage your databases. Anyway, I think database duplication can’t be simpler than this!
    Regards,
    Felipe Giotto.

    Comment by Felipe Giotto — 2008.03.19 @ 18:44

  3. @Felipe: Yeah, web hosts can be a problem. They normally restrict mysql connections to localhost, so you would have to use cPanel/phpMyAdmin.

    Of course, if you *really* wanted to, you could set up an XML-RPC service using PHP or Perl or something that can recieve/send mysqldumps. But, (1) you’d have to look into security implications and (2) if its just a matter of copying databases once or twice its more complexity than necessary. Kind of like killing a mosquito with a sledgehammer.

    Comment by Lorenzo E. Danielsson — 2008.03.19 @ 18:51

  4. I add one step — write the export to a file instead of piping it directly into mysql writing to the other database.

    You mentioned security at the end of your post, and a commenter mentioned webhosts not offering direct remote access to MySQL… well, that’s a pretty good idea. Normally your database server isn’t used by anything on the general internet, just by your application running on the same server, or on the local network. So it’s safer for you (or your host) to prevent direct access to your database from the general internet, including your laptop at home.

    Fortunately, copying the database is still simple; instead of | mysql…, just use the -r argument of mysqldump — it redirects the output to a file. Like this:
    -r “volcanos_$(date +%Y-%m-%d).sql”
    …if you want the date in there.

    Then zip that up, ftp it to your server, unzip it, and do the second half of your command there:
    mysql -h db -u led -p < volcanos.sql

    …and you’re done. This will also work with webhosts that lock down mysql access (as they should…) as long as they give you shell access.

    If you’re transferring large databases between hosts, check into scp for an easy way to send the export from one server to another.

    Comment by Rob Whelan — 2008.03.19 @ 20:19

  5. @Rob: thanks for adding that. If the host gives you ssh access then you are right, export to file, scp/sftp (whichever is your poison) is preferable. Unfortunately, as we all, know, not all hosts offer ssh. It might be high time to call for a general boycott of hosts that don’t. 😉

    Also, even though you don’t *need* to dump to a file on the local network in order to copy a database file, there could be other reasons why you would *want* to. Backups, storing the database structure in ${your_favorite_revision_control_system}, etc.

    Comment by Lorenzo E. Danielsson — 2008.03.19 @ 21:16


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: