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