Import Database

Hi there,

I’ve copied an existing database to /var/lib/mysql/ and wanted to import it with Virtualmin GPL.

This form allows you to bring existing manually created databases on the system under the ownership of this virtual server.

Unfortunately, I don’t see any databases in the selector box - only information_schema (MySQL).

What do I have to do to make the new database appear in the selector box?

Cheers, Nick

Hey Nick,

This form is merely for bringing an unattached database under control of a virtual server–it does not import a database into MySQL. The database must already exist in MySQL for this to form to do anything with it. There is a way to import databases into MySQL from Webmin, though. But there are some caveats.

It isn’t safe to copy a raw MySQL database from one system to another, unless they are running [em]identical[/em] versions of MySQL (and I mean exactly the same–not even a minor version difference). If there is any difference you need to do a dump on the other system–the MySQL module can help you with that as well (you can even connect to it remotely, if you don’t have Webmin installed on the other box…but MySQL would have to be listening on a public IP and port).

Anyway, to transfer a database from one system to another assuming you have Webmin on both, here’s how you do it:

On the source server, browse to the MySQL Database module, and click on the database you want to migrate.

Click "Backup Database".

Fill in the form–generally you just need to give it a filename. All of the other stuff makes the backup selective, compress it, schedule periodic backups, etc. Click "Save and backup now".

When the dump finishes, copy the file you just created over to the destination server.

Now browse to Webmin’s MySQL module on the destination machine, and click on “Create a new database”. Give it a name and create an empty database.

Now click on the new database in the list of databases, and click "Execute SQL".

In that form, you can select the file you copied over from the other server.

Done. After this, you can then import the database under control of a Virtual Server in Virtualmin.

Hey Joe,

Thanks for the quick and detailed answer to my question.

Good to know that it’s not that easy to simply copy the raw database files over to the new system. The problem is that I have over 50 databases to migrate to Virtualmin. I’ll try to do a mysqldump directly from the command line for all databases and then import them with the mysql command on the new Debian Etch server.

I hope I’ll then be able to see them in the import tool’s selector box.

Best, Nick

Hey Joe,

This worked like a charm:

First [code:1]mysqldump -uroot -ppassword --all-databases > alldbs.sql[/code:1] on the old server and then [code:1]mysql -uroot -ppassword < alldbs.sql[/code:1] on the new one.

I now see all imported databases in the selector box and can bring them under the ownership of the desired virtual server.

Now the only problem that remains is the fact that a Virtualmin end user cannot seem to create new databases although all permissions seem to be set appropriately (see

Cheers, Nick

Hey Nick,

Webmin has a backup databases form, as well, that lets you backup all of your databases in one fell swoop. It does a proper dump, so those are files that can be transfered and restored on your new system. Just browse to the MySQL module and click "Backup Databases".

This requires Webmin on the source server, obviously, but installing Webmin is far less dramatic than installing Virtualmin. It doesn’t do anything to the system at all until you explicitly do something within Webmin–so it’s completely safe to install it on pretty much any system, even temporarily or for just one task. I install Webmin whenever I have database work to do, because I’m helpless without it. It’s also easier to get working than phpMyAdmin, and does more. :wink:

But, performing the dump from the command line is also fine–the results are the same. Scripting the dump/restore is probably the most efficient way to do what you’re after, because even with Webmin and Virtualmin, you’ll still need to create all of the empty databases on the destination server. It’s certainly possible to automate that away with minimal scripting. I’d probably go to the trouble to figure it out if I had 50 databases to move. :wink: