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.
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.
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.
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.
You can/should run the backup at off peak times along with enabling binary log ..........
please try and see whether you actually experience issues.
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.
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.
ASKER
And there are no off peak times, since in the task I need to make backup every hour.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.