Restoring a dump file, fundamentally, involves inserting all of the rows and building all of the indexes for all of the data. In that light, it's not particularly relevant how the dump file is generated, whether you use one or several files, or which of the available options you select (though some of them, like extended-insert could possibly make it worse of not selected).
When you're restoring a file, you're also:
- writing the data to the transaction log
- writing the data to the double-write buffer
- writing the data to the tablespace files
- writing the indexes to the double-write buffer
- writing the indexes to the tablepace files
- writing the data to the binary log if that's enabled ... and if you have the default
binlog_format setting of "statement," then you're literally writing almost every byte in the dump file plus overhead... but if your binlog_format is set to "row," you're writing a more compact version to the binlogs... and if your binlog_format is set to "mixed," then which format is actually used in the binlog depends on your default transaction isolation level.
Setting innodb_flush_log_at_trx_commit to the slightly less safe value of 2 or significantly less safe value of 0 from the very expensive but ACID-compliant default value of 1 will speed up your insert for sure, though it isn't likely to reduce actual I/O because that value doesn't change what's written to the transaction log, it merely changes how often InnoDB insists on confirmation from the operating system that the contents of the log have been persisted to disk. I use "safe" in the sense of safety against the loss of recent transactions if a crash occurs during the time the value is set to 2 or 0; 1 protects against data loss if either MySQL or the system crashes; 2 protects against data loss if MySQL crashes but not the system, and 0 protects against neither. Once you set it back, it doesn't have any after-effects.
Quick side note, some of the comment-looking things shown are not comments. The /*!mnnrr format is a MySQL backwards-compatibility extension that tells the server "If you're MySQL version m.nn.rr or higher, execute this statement, otherwise disregard."
/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;
Each table is surrounded by these in a dump file. These were more useful with MyISAM than with InnoDB, because DISABLE KEYS directed the storage engine not to update any non-unique indexes until ENABLE KEYS was issued, allowing all of the row data to be written, and then indexed in a batch. With InnoDB, the indexes are built as the inserts are processed... so there's a lot of I/O potential there as the index trees are being built and shuffled around.
The size of the InnoDB buffer pool is going to play a role in the amount of disk I/O -- possibly a significant one if it's relatively small -- because whatever can't stick around in memory will have to be promptly flushed to disk, only to be read back in when it's needed again, and this will be particularly true with indexes, less so with the actual rows, because in InnoDB, rows are physically stored in primary key order, and mysqldump writes them to the file in primary key order... so they're inserted in the order in which they'll be stored... but the secondary indexes will have to be shuttled back and forth to disk as index pages are updated as the insert operations on a given table progresses.
So there's a baseline of "extra" activity that every insert that happens, because of all the different logging, safety, and ACID mechanisms... and indexes seem like a likely wildcard candidate for potentially creating a substantial amount of additional I/O.
OK here is your situation: your INNODB_FILE_PER_TABLE is OFF.
The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK.
Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko)
These days, many use the default innodb_file_per_table = 1. This used to be 0 for the default back in the days of MySQL 5.1. If you have innodb_file_per_table = 0 in your my.cnf, then MySQL is leaving all InnoDB tables inside ibdata1.
For more info on this, see my posts
You can leave the InnoDB tables inside ibdata1. If you want them stored outside, see the CAVEAT of my post How can I determine which Innodb table is being written?
Best Answer
The file
ibdata1has "tablespace ids" in it that need to correspond to the .ibd files containing the data, etc. Since these ids are generated by the instance, when you reloaded the dump, they may be different than the ones on the production machine. Hence, the rsync could be copying wrong ids.Moral of the story: Don't mix source dumps (mysqdump, xtrabackup, etc) with file backups (rsync, etc).
Your backup is hosed; you will need to start over.
Some backup techniques (starting with least-invasive):
Zero downtime (after set up): Master-Slave; the Slave is continually updated as a viable backup.
Near zero downtime: LVM. After setting up a snapshot area, you (1) stop mysqld, (2) "snapshot" the filesystem, (3) restart mysqld, and (4) rsync the snapshot to wherever. Step 4 is the only slow part; it does not impact the running of the db other than chewing up lots of I/O.
incremental backup via binlog
Xtrabackup, with some of the non-trivial features.