MySQL增量備份及恢複

MySQL自帶備份指令->mysqldump

一 文法格式及指令參數

mysqldump -u 使用者名-p 資料庫名> 備份的檔案名字

例子：mysqldump-uroot -p123456 hadoop > /opt/data/mysql_hadoop_

bak.sql

備份過程：它會将将老資料删掉，然後插入或者删除才釋放鎖他會将将老資料删掉，然後插入或者删除才釋放鎖

-A或者--all-databases:備份所有資料庫

mysqldump-uroot -p123456 -A -B -E|gzip > /opt/bak/all.sql.gz

-B:導出腳本會建立庫并加上use 資料庫的過程;并且-B 還可以指定多個庫

預設是不會use 你要導出的資料庫，否則我們在恢複的時候還需要手動建立庫，而且還不了解以前參數

mysqldump-uroot -p123456 -B hadoop > /opt/data/mysql_hadoop_bak.sql

備份多個資料庫：

mysqldump-uroot -p123456 -B hadoop nicky|gzip > /opt/data/hadoop_nicky_gzip_bak.sql

--compact:可以優化輸出内容，讓容量更少，隻是适合調試

mysqldump-uroot -p123456 --compact -B hadoop >

/opt/data/mysql_hadoop_bak.sql

-Y:備份所有表

-y:不備份表空間

-E--events：dumpevents

-F或者--flush-logs ：dump之前重新整理bin log

mysqldump-uroot -p123456 -A -B -E -F|gzip > /opt/bak/all.sql.gz

--flush-privileges：dump之後重新整理權限表

-h： 主機名

-P:port

-p：密碼

-R：dump存儲過程

-S指定socket

-t:訓示導出資料

--ignore-table=name不dump指定的表

-x,--lock-all-tables

-d：隻是dump結構，不dump資料

--master-data=1可以保證不重新整理binlog，就是全備的那個點，自動找到，自動找到bin log位置

mysqldump-uroot -p123456 --master-data=1 -B hadoop >/opt/data/hadoop_master_date_bak.sql

--default-charactor-set：指定字元集的mysqldump

mysqldump-uroot -p123456 --default-charactor-set=utf-8 hadoop >

/opt/data/mysql_hadoop_bak.sql

--single-transaction：适合innodb事務資料庫備份

InnoDB表在備份的時候，經常啟用選項：--single-transaction保證備份的一緻性，實際上他的工作原理是設定本次會話隔離級别為

REPEATABLEREAD(可重複讀),以確定本次會話不會看到其他會話已經送出了的資料

Linuxegrep指令用于在檔案内查找指定的字元串。

資料庫名|壓縮格式:支援壓縮

mysqldump-uroot -p123456 -B hadoop|gzip > /opt/data/mysql_hadoop_bak_gzip.sql.gz

此時sql就是一個二進制檔案

恢複：

先删掉表student

mysql-uroot -p123456 -e "use hadoop;drop table student;"

恢複必須指定庫

>>結論：

1導出資料最好使用-B參數

2可以對備份資料進行gzip壓縮

二 mysqldump 的工作原理

我們利用mysql指令備份資料庫的過程，實際上就是把資料從mysql庫裡以邏輯的sql語句形式直接輸出或者生成備份檔案的過程

恢複就是把備份是sql語句在mysql重新執行一次的過程

三 如何做分庫備份

分庫備份：執行一個備份語句備份一個庫，入鍋資料庫有多個庫，就執行多條相同的備份的單個庫備份語句

檢視所有資料庫：

mysql-uroot -p123456 -e "show databases;"|grep -Evi"database|info|test|perf"

grep

-E:支援使用擴充的正則

-e:支援使用标準的正則

-i:忽略大小寫

-v:選中非比對的行

-c:統計行數

字元串結果交給bash運作

echo"ls -al /opt/data"|bash

mysql-uroot -p123456 -e "show databases;"|grep -Evi"database|info|perf|mysql"

hadoop

nicky

test

然後開始循環

#！/bin/bash

DB_PATH=/opt/bak

MYSQL_USER=root

MYSQL_PASSWD=123456

SOCKET=/data/3306/mysql.sock

MYSQL_CMD="mysql-u$MYSQL_USER -p$MYSQL_PASSWD -S $SOCKET"

MYSQL_DUMP="mysqldump-u$MYSQL_USER -p$MYSQL_PASSWD -S $SOCKET -x -B -F -R"

DB_LIST=`$MYSQL_CMD-e "show databases;"|grep -Evi "database|info|perf|mysql"`

[! -d $DB_PATH ] && mkdir -p $DB_PATH

fordbname in $DB_LIST

do

    $MYSQL_DUMP $dbname|gzip >$DB_PATH/${dbname}_$(date +%F).sql.gz

done

>>備份單個表

mysqldump-uroot -p123456 資料庫 表名1 >/opt/bak/hadoop_student.sql

>>備份多個表

mysqldump-uroot -p123456 資料庫 表名1 表名2...> /opt/bak/tables.sql

一般備份方案：

myisam:

mysqldump-uroot -p123456 -A -B --master-data=1 -x -R |gzip > /opt/all.sql.gz

mysqldump-uroot -p123456 -A --flush-privileges --lock-all-tables \

--master-data=1-F --triggers -R -E --hex-blob > /opt/bak/all.sql

innodb:

mysqldump-uroot -p123456 -A -B --master-data=1 --single-transaction -R |gzip >/opt/all.sql.gz

mysqldump-uroot -p123456 -A --flush-privileges --single-transaction \

--master-data=1-F --triggers -R -E --hex-blob > /opt/bak/all.sql

如果兩者都有，以myisam為準

四 利用source恢複MySQL資料

在mysql 執行 sourcedump檔案路徑

如果時gzip格式的，我們需要先解壓檔案

'gzip-d /opt/data/mysql_hadoop_bak.sql.gz'

mysql>source /opt/data/mysql_hadoop_bak.sql

五 檢視目前資料庫有哪些程序正在連接配接mysql

mysql-uroot -p123456 -e "show processlist"

mysql-uroot -p123456 -e "show full processlist"

如果連續兩次show,還有那個程序在哪帶着，可能是慢查詢

六 檢視目前資料庫參數資訊showvariables

mysql-uroot -p123456 -e "show variables;"

$mysql -uroot -p123456 -e "show variables;"|grep log_bin

log_bin     OFF

log_bin_trust_function_creators     OFF

sql_log_bin    ON

七 檢視整個資料庫狀态showglobal status

>>檢視目前會話資料庫狀态showglobal status，很重要，并要做好監控

八 修改資料庫參數不重新開機就生效

key_buffer:myisam引擎存放索引的緩沖區,記憶體空間大了，就減少了去磁盤查找index

setkey_buffer_size=1024*1024*32;

然後配置檔案修改

這樣無論重新開機與否，都生效

九 mysqlbinlog 解析mysqlbin log日志的

作用：

用來記錄mysql内部增删改等對mysql所有資料庫有更新的内容的記錄，隻查不改的不會記錄

指令：

==>檢視binlog日志

如果我們想以可視化的形式檢視日志，需要使用mysqlbinlog來檢視,否則你看到的式二進制檔案 

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001

現在有一個問題，如果全備份已經分庫了,但是使用mysqlbinlog恢複資料的時候,binlog記錄的時所有庫的所有表資料

别的庫沒有問題，然後你在插入資料，别的庫就會報錯。

是以我們需要針對binlog拆庫

==>mysqlbinlog拆庫： -d 指定資料庫名字

我隻想恢複 hadoop庫 的bin log

mysqlbinlog-d hadoop mysql-bin.000001 > /opt/bak/hadoop.sql

==>指定時間和位置點恢複 -r 重定向

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 --start-position=177 --stop-position=286-r /opt/bak/hadoop_bin.sql

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 --start-datetime='2016-11-29 11:11:11' \

--stop-datetime='2016-11-2911:11:13' -r /opt/bak/hadoop_bin.sql

比如假設某一個資料庫在早上9點全備份，mysqldump，這時候bin log會記錄一個位置

然後12點資料庫不小心删掉了，那麼先回複全備份，然後9-12點的恢複就靠這期間的

binlog檔案

mysql-uroot -p123456 hadoop < /opt/data/mysql_hadoop_bak.sql

#查找目前目錄下包含字元串“Linux”的檔案

egrepLinux *