Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replicate MySQL database once per day

Posted on 2012-08-13
5
Medium Priority
?
955 Views
Last Modified: 2012-08-13
I have a MySQL database that we use for IDS systems.  We would like to create a second database in a remote location and replicate DB1 with DB2.  I found plenty of information about setting up database replication with MySQL, but it seems they are do replication in near real time.

Is there a way I can set up replication to only take place at night?  This would help reduce stress to our WAN link.

Thanks

Both databases would be running on RHEL6.
0
Comment
Question by:savone
  • 3
5 Comments
 
LVL 7

Expert Comment

by:multimac
ID: 38289820
You could make an initial dump and play it into the second database server. Then once per day you run "flush logs" and script-copy the latest binary log file  to the second server and import it there.
http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38289837
Use the same replication setup, but just turn it off when you don't want it to run.  Have cron  jobs that will call the following:

When you want it to start replicating:
mysql -u root -proot_password -e "start slave"

When it's time to stop replicating:
mysql -u root -proot_password -e "stop slave"

You may want to put those to shell scripts so that you don't put the passwords in the crontab.  Also, might be helpful to do some checks before stopping the slave by calling "show slave status\G" then grep the "Seconds_Behind_Master" line - check if the value is zero to make sure that that slave has fully caught up with the master.  In my setup it usually takes an hour to catch up with a day's worth of transaction.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38289851
multimac's solution will also work, but it involves multiple steps including transferring of binary logs.  I see it has one advantage though - once you run the binary logs you know you're done - unlike the master-slave replication where you need to check the status.
0
 
LVL 23

Author Comment

by:savone
ID: 38290160
@joahnntagle,

So I would put those script on the slave server correct?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38290165
Yes that's correct.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question