MySQL Live incremental backup Binlog log backup

Enable the Binlog log for incremental backups of data:

Log storage location:/var/lib/mysql/

Log name: Host name-bin.000001 or mysqld-bin.000001

Binlog Log Overview: Binary log, record all changes to the operation of data, by default more than 500M automatically generate new logs;

Modify Master profile Enable Binlog log

Vim/etc/my.cnf

[Mysqld]

Log-bin (or specify log name log-bin=x.000001 or specify directory and file name log-bin=/logdir/x.000001)

max-binlog-size=200m (set log max 200m, default = 500m)

: Wq

Restart the MySQL service,/var/lib/mysql/a log file 1 log index files:

x-bin.000001 MySQL binlog log file

X-bin.index record the currently existing Binlog log name

Mysqlbinlog is the MySQL log view exclusive command

View Binlog log file contents: Mysqlbinlog log filename

Binlog how the SQL statements are logged:

1 Based on character offset (POS)

Start character offset--start-positon= number

End character offset--stop-position= number

2 based on point in time

Start time--start-datetime= "Yyyy-mm-dd hh:mm:ss"

End time--stop-datetime= "Yyyy-mm-dd hh:mm:ss"

Execute the SQL statement in the Binlog log to recover the data:

Mysqlbinlog Options x-bin.000001 | mysql-uroot-p123456 [Database name]

To manually generate a new Binlog log file:

1: Restart MySQL service (rarely used)

2:mysql> flush logs;

3: [[email protected] mysql]# mysql-uroot-p123456-e "flush Logs" (arbitrary SQL statements can be executed in quotation marks)

4:mysqldump-uroot-p123456--flush-logs Data name > Xxx.sql

Clean up the Binlog log

Delete an existing Binlog log

1 Delete the specified version of the Binlog log

(1) mode one:mysql> purge master logs to "x-bin.000004"; (delete log before 000004, X-bin.index sync Update)

(2) mode two: Rm-rf x-bin.000001 (x-bin.index update)

2 Delete all Binlog logs, rebuild the log; mysql> reset master;

Scripting allbak.sh A full backup of data every Monday: * * 1/shell/allbak.sh

Vim allbak.sh

#!/bin/bash

Bakdir=/datadir

Dbname=haha

day=$ (Date +%f)

if [!-e $bakdir]

Then mkdir $bakdir

Fi

mysqldump-uroot-p123456--flush-logs $dbname > $bakdir/allbak-$day. sql

Scripting newbak.sh Incremental backups every Tuesday to day (backs up only newly generated and Binlog logs that are not backed up and in use): Do not back up the last binlog log * * * 2-7/shell/newbak.sh

Vim newbak.sh

#!/bin/bash

if [!-e/backdir];then

Mkdir/backdir

Fi

Cd/var/lib/mysql

For file in ' sed ' $d '/var/lib/mysql/a-bin.index '

Do name= ' echo $file | Awk-f "/" ' {print $} '

if [!-e/backdir/$name];then

CP $name/backdir

Fi

Done

This article is from "Linux Cloud Computing" blog, reprint please contact the author!

MySQL Live incremental backup Binlog log backup