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.

Ricky
  • 121
  • 2

2 Answers2

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.)

Rick James
  • 80,479
  • 5
  • 52
  • 119
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

a_vlad
  • 3,705
  • 2
  • 13
  • 17