MySQL – Files to Exclude/Add/Copy Without Using mysqldump

batch processingMySQLwindows

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

Best Answer

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.

Related Question