3

I need to have an exact copy of my databases in a clone VM of my server (just in case the server goes down, have the VM ready with the dbs). I know that mysqldump is the best alternative to migrate, but I want to have another alternative. So I decided to do it with ROBOCOPY, and copy data folder. This is my batch

@echo off
set exclude=*.pid *.err *.cnf *logfile* *log_file*
set ORIGIN="mysql_data_path\data"
set DEST="mysql_data_path_mirror\data"

NET STOP mysqld
robocopy "%ORIGIN%" "%DEST%" /E /COPYALL /PURGE /MIR /IS /IT /TEE /FFT /ETA /R:10 /W:5 /ZB /V /XF %exclude%

The problem is that I am not sure which files I should exclude/add/copy, that does not affect the operation when I do the reverse process.

For example, I have put some exclusions (*.pid *.err *.cnf *logfile* *log_file*), but I do not know if they are correct. I would like help in this point. And, apart from data folder, is there anything else that I should copy in order to function in the VM?

Thanks

acgbox
  • 157
  • 1
  • 7

3 Answers3

3

You cannot exclude the ib_logfile* files, if those aren't present, your data will be at best damaged, unless you are running with innodb_fast_shutdown=0 - which can make stopping the service take a very long time.

The only things that are safe to omit are the relay logs and binary logs. You will have to check what the names of those are set to in your config file.

Gordan Bobić
  • 1,064
  • 7
  • 12
2

mysqldump is a logical level tool that creates the SQL script reproducing DB at some state. There are some tools like Percona XtraBackup that makes a copy on a physical level. XtraBackup allows so-called incremental backups so you can do a full initial backup once and then you can save only diffs since last backup.

You have to refer manuals to decide what way is suitable for your needs.

Kondybas
  • 4,800
  • 19
  • 16
1
  1. Stop mysql.
  2. Copy the entire tree. (rsync or LVM makes this faster.)
  3. Start mysql.

Meanwhile, make a copy of the my.cnf file(s).

Rick James
  • 80,479
  • 5
  • 52
  • 119