Can the MySQL database be manually Hard copied over to a different DMA? We have many system s with large, >100G, databases and when trying to take a full backup and then using that backup to restore it on a replacement DMA can take many hours, the last case took about 7 hours. I would like to know if I can just copy/move the database from the source DMA to the target DMA to speed this process up. I know that a innodb dbase can have issues with trying to simply move the database files to a different server if not done correctly. Can this be done? Will the following procedure work %100 of the time.
- Stop MySQL service on both DMA
- Copy the entire “C:\ProgramData\MySQL\MySQL Server 5.5\data” folder from the source DMA to the target DMA
- Restart MySQL
Thanks in advance for any feedback
Hi Jeff,
In the past, I’ve used mysqlpump.exe or mysqldump.exe to do this.
For example:
mysqldump.exe -u[username] -p[password] SLDMADB > backup.sql
You can then import again by using mysql.exe (typically located in C:\Program Files\MySQL\MySQL Server 5.5\bin):
mysql.exe -u[username] -p[password] SLDMADB < backup.sql
I tried importing again through tools like HeideSQL UI but that gave corrupted databases so using the command-line worked better for me.
Hi Jeff,
i tried copying the files manually before but this gave me trouble with corrupted system files in MySQL, if i want to move manually the DB i use the export functionality in the workbench into a self contained file and afterwards just do a DataMiner backup restore without the database
would that be an option in your use case?
Marlies, I ran into the same problem when trying to copy/move the sldmadb folder only, but my last test I moved the entire Data folder and that appeared to work. Various google searches on this topic returned mixed advice. Many stated that if the HW and MySql installations are the same then moving the entire Data folder will work but I was hoping you all, as the SW vendor that built their system around MySQL, originally, could verify this.
Thanks again.
Indeed in most cases moving the entire folder works but we had a couple of weeks ago a case that it still resulted in a fatal error at a customer where we tried it for recovery purposes and this isn’t the first time, my assumption is that the ibdata gets corrupt due to a race condition in MySQL itself at startup
so i would say you can “try” it, if MySQL doesn’t give you trouble it should be fine but there are no guarantees
Ok, not the answer I was looking for but thank you for your response.
Jens, thanks for your response. We already use the dump feature, in fact, from what I can tell that is how the DM backup utility works. This is also how the workbench export feature works. This is why the process takes so long with large database. This is what is considered a logical backup and works by creating a backup file that contains all the insert sql statements needed to recreate the database. It works but is slow on large databases so i am looking for a way to Hard copy the dbase files to different server to speed things up.