import/attach some mysql databases

Question

I have a directory that looks like:

ar_wiki_latest ibdata1 ib_logfile1 ko_wiki_latest ps_wiki_20100302 ru_wiki_latest zh_wiki_latest en_wiki_latest ib_logfile0 ja_wiki_latest mysql ps_wiki_latest test

left over from a decommissioned server. Is there a way to persuade mysql to adopt some of these databases?

Answer 1

The most reliable way to do this is to temporarily replace your mysql data directory with the one you want to import. Start up the mysql server and do a mysqldump of the tables you want to import (note you'll be using your old mysql login). Shut down MySQL again, replace the temporary, old data dir with your current one, restart MySQL, and then use the mysql command-line tool to import the dump file.

If the database you want to import only uses MyISAM, then you can usually just copy the database's directory (e.g. "en_wiki_latest") into your new mysql data dir, and it will "just work". However, if the database uses InnoDB, then using this method will cause a disaster. So, to be on the safe side, I'd recommend the method mentioned above.

A quick recap on how to import/export:

Export:

mysqldump -uUSER -pPASSWORD database_name > database_name.sql

Import:

mysql -uUSER -pPASSWORD <<< "create database database_name"
mysql -uUSER -pPASSWORD database_name < database_name.sql

Answer 2

I agree, except for one addition.

Never modify a running system/configuration to do testing or development. Always test and develop on another system or an alternate server or instance. You can run many instances of the same server or many versions of MySQL on the same machine.

Start another instance of MySQL on an alternate port and the alternate data dir. Now you can dump and import while both servers or instances are running.

Excerpt from the manual: http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html

You need not compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options:

shell> mysqld_safe --socket=file_name --port=port_number

To start a second server, provide different --socket and --port option values, and pass a --datadir=path option to mysqld_safe so that the server uses a different data directory.

Same idea for windows, see the manual for syntax.

When making changes for performance or to fix "bugs", prove/test the changes on another server/instance before final testing on a running/production server. This applies to configuration, indices, field types, constraints, procedures, etc..