mysql資料庫的備份和恢複

標籤：mysql資料庫的備份和恢複

mysql5.5

工作原理：實際上就是把資料從mysql庫裡以邏輯的sql語句形式直接輸出或者產生備份檔案的過程

文法：

                                    備份    

    mysqldump -u 使用者名稱 -p 密碼 資料庫名、表名 > 備份的位置

    範例： mysqldump -uroot -p‘123456‘ test>/opt/mysql_bak.sql

    查看：egrep -v "#|\*|^$|--" /opt/mysql_bak.sql

    

    備份單個表：mysqldump -u使用者 -p密碼 資料庫名 表名>位置

    備份多個表：mysqldump -u使用者 -p密碼 資料庫名 表1 表2 >位置

    範例： mysqldump -uroot -p123456 test test_1>/back/sql

    意義：如果備份時把所有資料庫都備份成一個資料檔案的話，恢複某一個庫的資料時比較麻煩

####備份就是以資料庫實際語句匯出；如果是亂碼，是因為匯出的格式沒加字元集，一般恢複到資料庫裡就會正常，只是系統外查看不正常而已。另外insert是批量插入的的方式。這樣恢複時的效率就很高。###### 多執行個體的話就是指定sock的然後備份。

    工作中的使用

    innodb 推薦使用

     mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction  --events |gzip >/opt/mysql.all.gz

  

    myisam

    mysqldump -uroot -p123456 -A -B --master-data=2 -x --events |gzip >/opt/mysql.all.gz

    如果是混合引擎以innodb為主；

  

 內建備份命令mysqldump中常見的參數：

-A 備份整個資料庫；                               注意添加：--events；

-B 增加建立資料庫和串連資料庫的命令； 指定多個庫備份；

 

  #實際就是增加use db和create database db的資訊，在備份表的時候不要添加-B選項；

|gzip 備份資料庫時壓縮;提高壓縮的效率；

   範例：

   備份單庫test並壓縮範例：

   mysqldump -uroot -p123456 -B test|gzip >/kong/mysql.back.gz

   備份多個分庫並壓縮範例：

   mysql -uroot -p123456 -e "show databases;"  | grep -Evi "infor|perfor|database|mysql" |sed "s#^#mysqldump -uroot -p123456 -B >/kong/#g" |bash

-d 備份資料庫表結構；

-F重新整理binlog日誌

   範例：mysqldump -uroot -p123456 -A -B -F  --events >/opt/mysql.back

   #如備份沒有指定-F ，當恢複的時候，會無法寫入資料，因為不知道從哪裡寫入資料，原有一部分資料已存在時會衝突；

   #使用mysqlbinlog 查看在mysql/data下的binlog，（需要提前在my.cnf檔案中開啟binlog）

   #binlog的記錄是通過位置點（檔案大小）或者時間來記錄的

-t 只備份資料；

-l唯讀鎖表；

-x ，--lock-all-tables 鎖表，所有的人都不可以操作，保持資料的一致性； 

--compact，去掉注釋，適合debug調試時減少輸出；

--master-data，增加binlog記錄檔名及對應的位置點

    --master-data=1

    --master-data=2 備份時加入注釋

--defult-character-set=指定字元集匯出： 

     範例：

     mysqldump -ukong -p‘123456‘ --defult-character-set=latin1 test>/opt/mysql_bak1.sql

--single-transaction適合innodb事物Database Backup，保證資料的一致性；

    #就是一個隔離等級，備份時其它使用者提交的資料是它是不進行處理的

備份多個庫不使用shell的步驟：

1、利用-e 在命令列顯示出都有哪些資料庫：

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

2、使用egrep過濾掉不需要備份的庫；

3、利用sed的替換功能，在庫名前添加mysqldump的備份命令；

   範例：sed "s#^#mysqldump -uroot -p123456 -B -A#  #^#g"

         sed  -r "s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B  \1 >/opt/\1.sql#g" 

4、然後傳遞給bash執行即可；

     mysql -uroot -p123456 -e "show databases" | egrep -v "Database|information_schema" |sed ‘s#^#mysqldump -uroot -p123456 -B  --events |gzip >/back/#‘ |bash

     mysql  -uroot -p123456 -e "show databases;" | egrep -iv "Database|information_schema"  |sed  -r "s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B  \1 >/opt/\1.sql#g" | bash

傻瓜式shell備份：

for dbname in ` mysql -uroot -p123456 -e "show databases" | egrep -v "Database|information_schema" `
do
 mysqldump -uroot -p123456 -B  --events $adbname |gzip >/back/${dbname}_back.sql.gz
done

分庫備份：

#!/bin/bash
USER=root
PASSWD=123456
MYSQLCMD="mysql -u$USER -p$PASSWD "
MYDUMP="mysqldump -u$USER -p$PASSWD -B  |gzip"
for database in test mysql name 

#for database in `$MYSQLCMD -e "show databases;"|sed ‘2,4p‘`

do
  $MYDUMP $database >/back/${database}_$(date +%F).sql.gz
done

注意：變數後邊有多餘內容的時候，要使用{}號括起來，例如：${dbnamel}_back.sql.gz

分表備份：

#!/bin/bash
USER=root
PASSWD=123456
MYSQLCMD="mysql -u$USER -p$PASSWD "
MYDUMP="mysqldump -u$USER -p$PASSWD "
for database in `$MYSQLCMD -e "show databases;"|egrep -v "Database|information_schema|mysql"`
do
  for tables in  `$MYSQLCMD -e  "show tables from $database;" |sed "1d"`
  do
        mkdir /back/${database} -p
        $MYDUMP $database  $tables |gzip >/back/${database}s_${tables}_$(date +%F).sql.gz
        done
done       

                                    恢複

1、 source 命令（需登入到資料庫裡）：

    查看資料備份的位置: system ls /kong/

    恢複：source  備份檔案的位置

    恢複: mysql -uroot -p123456 </kong/mysql 

    #如果在備份的時候沒有添加-B 選項，在恢複的時候需指定恢複資料庫的名。

    #如果是壓縮檔，需gzip -d解壓備份的資料庫（會把原檔案刪除），接著直接恢複即可

大量復原：

傻瓜恢複範例：

for dbname in `ls *.sql | awk -F  . ‘{print $1}‘`; do mysql -uroot -p123456 < ${dbname}.sql ;done    

#ls *.sql | awk -F  . ‘{print $1}‘截取資料庫名

-------------------------------------------------------------------------------------------

本文出自 “一如既往” 部落格，謝絕轉載！

mysql資料庫的備份和恢複