Copying a MySQL database FAST

Steven Alligood steve at bluehost.com
Wed Aug 6 11:15:19 MDT 2008


Dave Smith wrote:
> Matthew Frederico wrote:
>> # /etc/init.d/mysql stop
>> # cd /var/lib/mysql (or wherever your mysql data files are located)
>> # cp -a database_x database_y
>> # /etc/init.d/mysql start
>>
>> And voila, you have an exact copy of your database and it's pretty fast.
>>   
>
> No, you have a corrupt database. Remember, these are InnoDB tables. 
> You can't just do the copy like that unless you have ISAM or MyISAM 
> tables.
>
> --Dave
>

You CAN do a funky version of this.

1) you have to be using separate files for each innodb table 
(innodb_file_per_table=1)
2) you have to already have the new database created in innodb (so the 
database references exist inside ibdata1)
3) you have to already have the new tables created (same reason as #2)
4) you down the MySQL server, copy the tables' files to the new DB's 
table names, and up the server.


Kind of funky, and I am not sure I would want to do it in production, 
but it worked when I tested it a few months back for a proof of concept 
on a project.

-Steve
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3241 bytes
Desc: S/MIME Cryptographic Signature
Url : http://plug.org/pipermail/plug/attachments/20080806/2a07e6f5/attachment.bin 


More information about the PLUG mailing list