How to safely migrate huge InnoDB Database

Creating a dump file

mysqldump -uroot -p -R -q --single-transaction -v dbname | gzip -q -9 - > /var/www/dbbackup/dbname.sql.gz


-R Dump stored routines (functions and procedures) from the dumped databases. (Reference.)

-q This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. (Reference.)

--single-transaction This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time whenBEGIN was issued without blocking any applications. (Reference.)

Extracting .gz file:

gzip -d dbname.sql.gz

Importing dump file


mysql -uDBUSER -pDBPASSWORD DBNAME < DBNAME.sql