2

I currently have a rather large database that I create dumps from the our websites Live Server to my Test Server (and vice versa).

Creating the dump is fairly quick (takes about 30 seconds). However, executing the dump file on my Test Server (or Live Server) in order to setup a copy of the dumped database takes AGES (roughly 2-4 hours).

Is there anyway to make this faster? I currently generate the MySQL Database Dumps using Navicat, and then execute them again using Navicat.

  • If you realize that "executing a MySQL dump" is in fact "performing mass INSERT statements", you will find many useful answers here that discuss INSERT performance optimization. – mustaccio Jan 11 '17 at 18:35
1

Set up the OS with LVM Snapshots. Preferably have MySQL in a separate partition or drive that is to be snapshotted. Then to take a dump:

  • Stop mysqld
  • take snapshot (a minute or so, regardless of dataset size)
  • start mysqld

Copy (suggest rsync) the snapshot to the other machine (this takes time) into an identical snapshot area.

  • start mysqld

(Caveat: I have never tried this, so there could be some rough edges.)

  • Yes, possible way, just think with - dump taking 30 seconds and normal stop -> copy all mysql files on same machine (not network) -> start, will be fast, need better understand size of database. I can suggest - use same version of MySQL on dev machine (mandatory) and test Persona Xtrabackup, this is I use – a_vlad Jan 12 '17 at 20:21
  • The snapshot means that the copy can be done after mysqld is restarted. Or, if using the same machine, you could probably avoid any copy. Caution: A snapshot is links to the same blocks, plus "Copy on Write". So, as the two instances make change, disk fills up. (No problem if you have 2x the disk space.) – Rick James Jan 12 '17 at 22:38
1

Addition to comment, as separate answer:

  1. Persona XTrabackup - https://www.percona.com/software/mysql-database/percona-xtrabackup
  2. Stop - copy - Start
  3. (preferred) Attach Slave server with ROW based replication, than at any moment stop slave - copy files - start slave

in case of Slave, ROW based replication - highly recommended. In case of mixed or statement based replication, You must be sure not have opened temporary tables on Slave, or replication will be broken, with ROW based replication - temporary tables not involved in replication process.

Of course - same version of MySQL on all machines

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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