Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have several tables that are 10's of millions of rows, and have multiple indexes. When dumping and reloading the data it takes quite a while to reinsert because of the indexes, or to insert without indexes then re-index.

I understand that an index gives a database engine a quick way to locate a record on disk, and since a database dump and reload would or could effectively change the location of data on the disk, the index could potentially be invalidated. However, it still seems like you should be able to optimize a table by contiguously and sequentially locating all it's records on disk, then just have the index refer to a start location plus offset. Has anyone anywhere done any work on this type of thing?

Desired functionality: I could dump the data raw, load it in a sequential uninterrupted disk area, then just load in the indexes and tell the engine to update with the new table start position. After googling around on this, I see nothing...

share|improve this question

2 Answers 2

up vote 2 down vote accepted

MySQL stores the raw data and index files in the data directory; there's usually 3 of them per table, each named with the same name as the table and a different file extension, and all contained within a folder bearing the name of the database itself. These files can be copied elsewhere for your backups (and can be compressed and/or encrypted, based on your needs) just like any other file, HOWEVER you must either stop your database or acquire a read lock on all tables -- if you copy a table's files while data is being written to it, your backups will likely be corrupted and unusable! (Alternatively, stop MySQL or acquire the locks, take a snapshot (if you're lucky enough to be on LVM), then re-start/release the locks while you take the backup.)

When restoring from backup with these files:

  1. Stop MySQL. This is critically important! You cannot directly modify the raw table files while MySQL is running. Just trust me on this one.
  2. Copy the relevant files (remembering to maintain the directory structure, i.e. a directory for a database) into MySQL's data directory.
  3. Start up MySQL. You should have the data there now, as it was when you made the backup.

I've used this procedure many times to backup and restore databases on MySQL, versions 3, 4, and 5, with no issues. Probably a good idea to run a check on each restored table just in case, however.

Important: This procedure will only work if you restore to the same major version as you backed up from! That is, if you make a copy of a MySQL 5 database in this manner, you must restore to MySQL 5. Going up (and only up) by 1 version (e.g. copy for 4, restore to 5) should work as well, but may require updating the file structure and thus may not be any faster/better than your method of dumping them.

share|improve this answer
If you're still around, can you elaborate on the requirement to stop mysql when replacing the files? Is this absolutely necessary, or is there some workaround (for example, creating a table with the same name, locking it, and then replacing its files with the backed up ones, or something along those lines?) I would like to use a technique like this to offload some non-time-critical index building from our production server to an offline server, but taking down the mysql daemon on the prod server to import would be tricky. – Nathan Stretch Feb 3 at 6:46
@NathanStretch Database servers are very complex beasts, and they do a lot to ensure data integrity. While the database is running, there are several things working against you, which include (but are not limited to): The files are locked against anyone else writing to them; MySQL caches a lot of data in RAM, and any discrepancy between that and what's on disk will result in the cached data overwriting the on-disk data (best case) or outright table corruption (worst case). – Kromey Feb 3 at 17:56
@NathanStretch It may be possible to place your backed-up files in a new folder and hope MySQL picks it up as a new database, but I'd expect it won't rescan the data folder until it is restarted, and even if it does before then you run the risk of it picking up your only-partially-restored directory mid-copy, and again you've got a corruption-prone mess. Even if you test this successfully, you're running the risk that you just happened to catch the right side of a race condition. In short, I (and MySQL!) strongly encourage you not to try this. – Kromey Feb 3 at 17:58

You may want to look into disk snapshots... and/or bulk transactions.

With most linux flavors systems you can use the disk manager to create a snapshot of the current file-system... and then backup/copy/move/do-whatever with the raw database files. The only down-side to this method is that the mysql server will need to be offline for a brief moment while the snapshot is created. This will ensure that you have a database that is correctly committed to disk. After you are done with the snapshot... you can simply delete the snapshot & move on. (no need to restart mysql or anything else) A restore process would be simply a matter of copying/moving the raw data files to the correct data directory & firing mysql up.

You can alternatively use "bulk transaction" commands with mysql in order to bypass all consistency checks & so-on. With most bulk-transactions, there will still be some period of lag while it rebuilds the indexes. The advantage of this method is that there will only be 1 big consistency check at the end rather than 1 for each record being inserted.

When all is said & done, there really is no great method for dealing with databases over the 2 million record mark. This is where you begin to see the flaws in a relational database and start looking at nosql alternatives. (And yes, I do know there's a lot of arguments pushing both ways on this subject)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.