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

Wander(Brazil)

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)

Dave Stokes-MySQL Community Team-Oracle

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.

Wander(Brazil)

Hi Dave,

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

Alexandre Almeida

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.