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

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
8
  • Is this enough (.pid *.err *.cnf *log_file) or should I add, remove, or copy something else?
    – acgbox
    May 10, 2020 at 15:51
  • You probably want to save the .cnf. You need to check your config file to make sure that the *log_file pattern isn't something important.
    – Gordan Bobić
    May 10, 2020 at 16:09
  • OK (Then only exclude *.pid and *.err). Anything else i need to exclude or copy to make it work on the VM??
    – acgbox
    May 10, 2020 at 20:11
  • Replication binlog and relay log can be excluded. Look at the name pattern in your log-bin configuration parameter. You cannot guess these things, you need to actually look at what the file names are for various things, as defined in your configuration file(s).
    – Gordan Bobić
    May 10, 2020 at 20:13
  • 1
    You need to find your my.ini configuration file and look for the names of various file name patterns configured for various things in there. log-bin defines the name of the replication binlog, for example. Replication binlog and relay log can be excluded. Innodb log files and tablespaces have to be backed up. If you don't know what these are and would rather not read the documentation, back up everything.
    – Gordan Bobić
    May 10, 2020 at 20:24
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.

Improve this answer
1
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).

Improve this answer
5
  • what exactly do you mean by "entire tree"? (By the way, Rsync is for Linux-based OS only and ROBOCOPY is fast enough for the task)
    – acgbox
    May 11, 2020 at 14:50
  • cwrsync is alternative rsync for windows itefix.net/cwrsync
    – acgbox
    May 12, 2020 at 15:32
  • is there a way to copy ONE database and not the whole tree? what metadata needs to be transfered as well as the actual binary directory for the database?
    – math
    Oct 16 at 18:10
  • @math - Maybe -- Are all the tables using the same ENGINE? Which Engine. Any stored routines? Foreign Keys?
    – Rick James
    Oct 16 at 19:14
  • no routines or foreign keys. same engine, just a different datastamp on the filesystem snapshot. seems if the database exists with the same tables I can copy right overtop it it works, but if there's any new/dropped tables, things get wonky.
    – math
    Oct 17 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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