MySQL Hot Backup or Slave Database

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
B.Thomas
Posts: 160
Joined: Mon Jul 23, 2007 11:00 pm

MySQL Hot Backup or Slave Database

Post by B.Thomas » Sun Apr 06, 2014 2:47 am

For a "What if my server crashes plan" I would like to set up a second duplicate server to be ready to go "on the fly" if our server crashes. I can synchronize most every other file with a number of programs except locked MySQL files. The user manual eludes to the ability of MySQL to make hot backups or allow for a slave database. "MySQL also keeps logs of changes to the database and can maintain a slave database which is always an exact copy of the master."

Is there an easy way to set this up or is it a complicated process? The MySQL looks rather daunting.

https://dev.mysql.com/doc/refman/5.5/en ... howto.html

Any instructions or suggestions would be appreciated.

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: MySQL Hot Backup or Slave Database

Post by jsalmon » Mon Apr 07, 2014 11:35 am

I tend to steer users away from MySQL replication. It's complicated, takes monitoring, and is usually a big hassle for little payoff. The time and money you spend setting it up and maintaining it will most likely cost more money than the 15 minutes / day of data (depending on backup intervals you use) that the typical backup software solutions take.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
drtech
Posts: 1647
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: MySQL Hot Backup or Slave Database

Post by drtech » Mon Apr 07, 2014 2:50 pm

Best way and simplest iMO is to use something like an "automysqlbackup.sh.2.5" script (this is for a linux server) that runs every half hour on my system and saves a snapshot of the database. In the event of a crash, I have only lost a maximum of 30 minutes worth of database data that can be imported into a standby second server and used quickly. (It pauses and locks the tables for a few seconds while it dumps the data) I also have my data shared drives syncing (rsync) with my backup server every fifteen minutes that copies all my OD images, XDR xray images, etc so that the backup server is always up to date as well. I think this is the least expensive and least hassle way to be ready to go again in an "emergency" computer crash situation.
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

User avatar
B.Thomas
Posts: 160
Joined: Mon Jul 23, 2007 11:00 pm

Re: MySQL Hot Backup or Slave Database

Post by B.Thomas » Mon Apr 07, 2014 11:26 pm

David, have you tested Open Dental on the backup server? I am using a program called "Second Copy" but it gets stuck on 4 MySQL files with the warning "file is in use". As a result the Open dental files on the backup server are not completely copied. I assume this is because the MYSQL service is running. I am trying to figure out a way where I can have the backup program send a command to stop the MySQL service than restart it.

Having an automated backup feature on OpenDental would be nice, but I am sure it's somewhere on the request list already.

EDIT: It wasn't as difficult as I thought;
- Create 2 txt files named MySQLStop.txt and MySQLStart.txt
- Edit the stop file text to say "net stop MySQL"
- Edit the start file text to say "net start MySQL"
- Change the MySQLStop.txt file name to MySQLStop.bat
- Change the MySQLStart.txt file name to MySQLStart.bat
- Add the Stop batch file to the commands to run before copying the files in your backup program
- Add the Start batch file to the commands to run after copying the files in your backup program

In the program I use (Second Copy) the command area is under "How" and the Advanced Properties tab.
Also, the MySQL service needs to always be off and set to Manual start on the backup computer.

User avatar
drtech
Posts: 1647
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: MySQL Hot Backup or Slave Database

Post by drtech » Tue Apr 08, 2014 7:12 am

yes, these backups work when restoring. I believe it locks the tables like you are saying there (and the reason for the five second delay in network access for us while the script runs).
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

Post Reply