Link to home
Start Free TrialLog in
Avatar of link087
link087

asked on

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.
Avatar of arnold
arnold
Flag of United States of America image

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.
Avatar of link087
link087

ASKER

I can't backup master, because I can't bring it offline.
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.
Avatar of link087

ASKER

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?
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.
Avatar of link087

ASKER

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.
Avatar of link087

ASKER

And there are no off peak times, since in the task I need to make backup every hour.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial