Provide instructions how to configure MySQL replication for backup.

link087
link087 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

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

Author

Commented:
I can't backup master, because I can't bring it offline.
Distinguished Expert 2017

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
Distinguished Expert 2017

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

Author

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.

Author

Commented:
And there are no off peak times, since in the task I need to make backup every hour.
Distinguished Expert 2017
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial