Provide instructions how to configure MySQL replication for backup.

I have 2 CentOS 6.7 servers. One of them has MySQL installation with Attlasian JIRA database. I need to replicate Attlasian Jira MySQL database to the second server so that I can properly make a backup. Replication interval should be 1 hour.
link087Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
run show create database to see the type.

Have a look at a replication example.  https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

Not sure what you mean by an hourly interval.
When binary log is update, the event it sent to the secondary/slave.

Replication and backup?
you can backup the DB on the server where it is running. need response to the first question posed.
mysqldump is a tool to backup Dbs.

note replication process, starts by backing up the existing after enabling binary logging. and then restoring the full backup on the replication node where .....


See the documentation and lets proceed from there.
link087Author Commented:
I can't backup master, because I can't bring it offline.
arnoldCommented:
you do not need to bring it offline
The only time one had to bring a db offline us when they would backup the db files in /var/lib/MySQL

Try the following

mysqldump --add-drop-database --add-drop-table --add-drop-index -u username -p --databases databasename >databasename.sql

You should see the raw create directives for database, tables, as well as the data.
Enabling bin-log will extend your ability to restore data to a point in time by replaying the data in the binary log.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

link087Author Commented:
By bringing offline I mean that application need to have access to database almost every second, so freezing database for like 10 seconds is also not good. Isn't it necessary to freeze database so it will be in consistent state during mysql dump?
arnoldCommented:
Mysqldump is not freezing the database unless you use --lock table/db type directives.
You can/should run the backup at off peak times along with enabling binary log ..........

please try and see whether you actually experience issues.
link087Author Commented:
So that's why I choose to replicate it to slave MySQL server and then for some time stop replication at slave, make backup and start replication again.

arnold, thank you for your time on this question

It's more theoretical than practical question for me. Actually I have now very good possible employer who gave me the task to develop Jira backup strategy.

I will accept your answer probably tomorrow. I am keeping it open only to understand as much possible about databases backup, as possible.
link087Author Commented:
And there are no off peak times, since in the task I need to make backup every hour.
arnoldCommented:
You can have a combination using full backups with binary logs as transactional incremental backups.

Look at the link posted that provides guidance on setting up replication.

If you need this solely for backup, master/slave setup will do for backup purposes only.
If you need a more robust setup that you can transition handle failure, look at a master/master replication/circular replication. Note the change requires the use of setting an offset such that a record added on one can never step on a record added by another.
Common use use increment of 10 allowing for max possible replication nodes of 9 in a circular replication.

Monitoring is important as there are times replication issues cone up and need to be resolved or node connection modified.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.