InnoDB tables cannot be copied the same way that MyISAM tables can.
Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.
I wrote two posts in DBA StackExchange about this tablespace id concept
Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.
You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms
database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.
Since you have done the following:
- SHOW VARIABLES LIKE 'innodb%';
- No InnoDB-related variables exist in my.cnf
and nothing comes up, please place this in my.cnf
[mysqld]
innodb
and start mysql and see if it comes up
UPDATE 2012-05-15 15:59 EDT
Since it fails to start up, you may need uninstall MySQL 5.5.24 and go back to MySQL 5.5.23 or whichever version you were last running with.
UPDATE 2012-05-15 16:06 EDT
Please run tail -30
on the mysql error log and paste that in the question.f it is not configured, go to the data folder and do ls -l *.err
to find it.
UPDATE 2012-05-15 17:54 EDT
Based on the display you pasted in, you evidently had MySQL 5.1 running before. It just so happens that MySQL 5.1 has a table called mysql.plugin. It may have been populated before. It probably had something concerning the InnoDB plugin. MySQL 5.1.38 introduced the InnoDB plugin which has new features that are now native to MySQL 5.5's InnoDB. In other words, MySQL 5.5 does not need any plugin to run the new InnoDB.
Please run this query:
SELECT COUNT(1) FROM mysql.plugin;
If you get 1, that's probably the MySQL 5.1 InnoDB Plugin interfering. Try moving it over like this:
CREATE TABLE mysql.plugin_new LIKE mysql.plugin;
ALTER TABLE mysql.plugin RENAME mysql.plugin_old;
ALTER TABLE mysql.plugin_new RENAME mysql.plugin;
Afterwards, please restart mysql and see if InnoDB comes back
Best Answer
Your interests would be best served if you'd investigate and come up with an explanation of why this is happening. It's not acceptable for a tool or utility to behave mysteriously, so either the tool has a defect, or isn't intended to be used as you are using it, or there is a quirk in your database structure that is interacting with something in the utility that you're using to cause these problems.
As a DBA, I like to be as "operationally close" to the server as possible, without tools making decisions and being "helpful." I use a graphical query browser for only three things: Running queries where I want to scroll through the results, editing stored programs, and adding comments to large numbers of undocumented table columns. Everything else, I do by hand because I like to understand exactly what the server is being asked to do, and I like the server to understand me, as well. So, here's how I do what I think you are describing.
I assume you're talking about cloning a schema on a single server, to a different schema on the same server.
To state it procedurally, "for each table (or other object) in `prod_db`, create an identically named object in `dev_db` including all of the indexes, foreign key constraints referencing the appropriate table in the new schema, and, optionally, copy all the data."
Here's how I do that.
This generates a human-readable dumpfile, with every instance of `prod_db` (including the backticks) replaced with `dev_db`. Perl handles that filtering as the dumpfile passes through the
|
pipeline. Then I review the file's contents for sanity and pipe it right back in the mysql:Done.
Now for the details.
If I take the first few lines of the file generated by this command:
I see this near the beginning of the file:
Now this:
Magic. This process carries one major caveat: you can't have a table with the same name as the original database, because the table name `sakila` will be changed to `dolphin` just like the database name was changed.
*options for
mysqldump
are very important here, because if you make a mistake, you run the risk of trashing your live database. This option is particularly useful.If you do a normal
mysqldump
, before eachCREATE TABLE
in the resulting file, there's aDROP TABLE IF EXISTS
statement.When you add the
--skip-add-drop-table
tomysqldump
it does write theDROP TABLE IF EXISTS
statement into the dumpfile, so there is no danger of dropping the wrong table. The worst case is that it will try to create a table that already exists, and if that happens, the script will halt. Then you can figure out what the problem was.You should also add the
--verbose
option tomysqldump
because it shows you progress as the dump is proceeding.If you have MySQL Event Scheduler events, or triggers, or stored functions/procedures ("routines") these flags will ensure that mysqldump copies those, as well. You need to review them, however, to be sure that any references to the schema are correct, because the schema name may not be properly backticked in these. That shouldn't be a major problem, because you don't need to reference the schema when defining these -- they run in the context of the schema in which they are defined.
Use this option if you only want to clone the structures, not the rows in the tables. You can also use this option for a dry run to examine the resulting "dump.sql" file for sanity. Remember, these files contain nothing more or less than the SQL statements needed to recreate a database, and are therefore, human-readable.
You want to use this when the tables you're dumping are InnoDB because it won't lock the tables or block other operations, but it will ensure that the data you get is consistent from table to table, because it issues
START TRANSACTION WITH CONSISTENT SNAPSHOT
against the original server before extracting the dump file.What's Perl doing here?
What we're essentially saying is perl,
-e
execute the following mini-script (in quotes) and-p
pretend that I've wrapped this instruction in a loop that says "read from standard in", "apply changes to data", "print resulting data to standard out".s/
substitute, when you see `prod_db` replace/
it with `dev_db` and/
do thisg
globally (instead of only replacing the first occurrence on any given line, replace every occurrence on every given line). Perl does this replacement very efficiently and is on virtually every system, and freely available for systems where it isn't installed, including Windows. You could do the same thing withsed
, and the command would be similar, though not identical.The steps above are essentially how I perform this operation when my developers need an entire schema cloned to the same server but with a different schema (database) name.