Keeping it Small and Simple


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.

Blog at