# Proper Backup Techniques?

**URL:** <https://forums.percona.com/t/proper-backup-techniques/4383>\
**Category:** Percona XtraBackup\
**Created:** [September 3, 2015, 1:53pm UTC](https://forums.percona.com/t/proper-backup-techniques/4383 "2015-09-03T13:53:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mschwage](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mschwage/32/1084_2.png) [@mschwage](https://forums.percona.com/u/mschwage)\
**Post date:** [September 3, 2015, 1:53pm UTC](https://forums.percona.com/t/proper-backup-techniques/4383/1 "2015-09-03T13:53:51Z")

</div>

Hello,  
I would like to get a peer review of my backup script. Am I doing ok? Any recommendations?

We are backing up on the weekends, and I’m not concerned about a database outage at the time of backups. They take less than 5 minutes at this time so performance is quite acceptable.

Without further ado, here is the meaty part:

suffix=“POOH”  
BACKUP\_DIR=/home/mysql.backup  
LOG\_DIR=/var/log/mysql.backup  
BACKUP\_NAME=“full-$suffix”  
FULL\_BACKUP\_DIR=“$BACKUP\_DIR/$BACKUP\_NAME”

echo “flush tables with read lock; set global read\_only = ON;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf  
innobackupex --defaults-file $TMPFILE --no-timestamp $FULL\_BACKUP\_DIR \> $LOG\_DIR/$suffix 2\>&1  
innobackupex --defaults-file $TMPFILE --apply-log --no-timestamp $FULL\_BACKUP\_DIR \>\> $LOG\_DIR/$suffix 2\>&1  
pt-table-checksum --defaults-file=/var/lib/mysql/defaults.cnf --chunk-size=1000000 --chunk-size-limit=0 --recursion-method none \>/dev/null  
echo “set global read\_only = OFF; unlock tables;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf

cd $FULL\_BACKUP\_DIR  
mkdir external\_files

# Columns: db tbl this\_crc this\_cnt

echo “use percona; select db,tbl,this\_crc,this\_cnt from checksums;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf \> external\_files/checksums.$suffix

```auto

```

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [September 3, 2015, 6:17pm UTC](https://forums.percona.com/t/proper-backup-techniques/4383/2 "2015-09-03T18:17:24Z")

</div>

Hi GreyGnome;

You do not need to flush tables yourself or set it to read\_only. Xtrabackup/Innobackupex will handle the flushing for you, and enabling read-only on the server defeats a lot of the purpose of a hot backup.

-Scott

---

<div class="post-metadata">

**Author:** ![mschwage](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mschwage/32/1084_2.png) [@mschwage](https://forums.percona.com/u/mschwage)\
**Post date:** [September 4, 2015, 10:47am UTC](https://forums.percona.com/t/proper-backup-techniques/4383/3 "2015-09-04T10:47:26Z")

</div>

> [@scott.nemes;42820](#):
>
> Hi GreyGnome;
> 
> You do not need to flush tables yourself or set it to read\_only. Xtrabackup/Innobackupex will handle the flushing for you, and enabling read-only on the server defeats a lot of the purpose of a hot backup.
> 
> -Scott

Thanks for the reply. But then there’s a possibility of modification between the backup and checksum times, is there not?

---

<div class="post-metadata">

**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\
**Post date:** [September 4, 2015, 12:43pm UTC](https://forums.percona.com/t/proper-backup-techniques/4383/4 "2015-09-04T12:43:23Z")

</div>

Hi GreyGnome;

I guess I am unsure of what you are trying to accomplish then. Generally speaking, the backup and running of pt-table-checksum are used for separate purposes. The backup portion is fairly straight forward; you just want a valid and consistent point-in-time backup, which Xtrabackup will give you. Then when you run pt-table-checksum, you are generally making sure that the slave(s) are in-sync with the master.

So the question is, what is your intended purpose with running pt-table-checksum in this case? Is your backup on a slave, and you are wanting to check each time to make sure the slave is in-sync with the master? Or some other purpose?

-Scott