Forum Stats

  • 3,838,084 Users
  • 2,262,326 Discussions
  • 7,900,498 Comments

Discussions

How to make a correct hot physical backup of MySql Innodb database with binary-log activeted

Wander(Brazil)
Wander(Brazil) Member Posts: 128
edited Dec 26, 2017 5:20PM in MySQL Community Space

Hi everyone,

I'm Oracle DBA and now I'm learning Mysql. I've been read lots of docs about Mysql backup. But I having a doubt about how to make a correct hot physical backup of a MySql Innodb database with binary-log activeted.

Actualy I'd like to understand how to do this. I saw that tables are locked during the process. It seems a bit confuse to me.

Could you help me giving the correct way to make this kind of backup. Also, I will make a test to restore this backup. Then I will use the binary-logs.

I'm using MySQL Community Edition version 5.7 on Oracle linux 64 bits.

kind regards,

Wander(Brazil)

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Aug 8, 2017 1:44PM

    The best place to start is Chapter 7 of the MySQL manual.  Depending on the size of your data, the criticality, and your resources, there are several options.  My favorite is to have a slave server and us that copy of the data for the backup.

    Dave Stokes

    MySQL Community Manager

  • Wander(Brazil)
    Wander(Brazil) Member Posts: 128
    edited Aug 8, 2017 2:45PM

    Hi Dave,

    I don't have a slave server. How do you would do in this case ?

  • Alexandre Almeida
    Alexandre Almeida Member Posts: 6 Red Ribbon
    edited Dec 26, 2017 5:20PM

    Wander,

         I am native portuguese speaker, however I'm going to answer in english to make this answer available for everybody.

         Considering you do not have any Slave, there are many ways to do that: dump + binaries, or, full datadir backup + log rotation + binaries.

         But, all of this demands a lot of work and/or complex scripts you want to automate the whole thing.

         Keep it Simple!

         If you own MySQL Enterprise Subscriptions, be happy with MEB (MySQL Enterprise Backup). If you don't give a look at Xtrabackup (https://www.percona.com/software/mysql-database/percona-xtrabackup

        MEB and Xtrabackup are online and non-blocking backup. You can compare these two with RMan.