mysqlDatabase Backup

標籤：form   提示   insert   系統   commit   please   補充   got   總結   

前一段時間因為誤操作刪除了一張表的幾條資料，弄得很尷尬，正好這周有空就折騰了下資料備份的知識，現把mysql的資料備份相關實踐和心得總結如下：

一.使用mysqldump命令備份資料庫：

備份整個資料庫（包括表結構和資料）,用法如下

mysqldump -h10.38.14.143 -uroot -prootroot  weixin > weixin.sql

其中 weixin 是資料庫名，可以一次備份多個資料庫 (用空格隔開)，預設儲存在目前的目錄下

這個wexin.sql檔案裡面主要由四部分組成：資料庫及作業系統的資訊、表結構、具體的資料（實際上是insert語句）、鎖表以及釋放表的命令語句

補充：

1.讀鎖定：本人和其他人只能讀取資料庫

   寫鎖定：只有本人能進行增刪改查，其他人不能進行任何操作

2.有時用mysqldump命令會提示沒許可權去操作資料庫相關的提示 如 mysqldump: Got error: 1044: Access denied for user ‘php‘@‘10.38.%‘ to database ‘cms_fgw‘ when using LOCK TABLES

  這是因為mysql資料庫限制了遠程伺服器對他的相關操作，請和DBA溝通開放許可權(主要是執行下面類似的命令去授權)

grant all privileges  on *.*  to  ‘root‘@‘%‘  identified by ‘123456‘ with grant  option;

  flush privileges;

3.如若忘記mysql命令的一些用法可以多多使用？操作,相當於man協助，這個很有用，如下

4.用mysqldump也可以只備份表結構，用法如下

mysqldump -h10.38.14.143 -uroot -prootroot --no-data --databases weixin cjdaily > /tmp/table_structure.sql

table_structure.sql中的部內容如下

5.可以根據mysqldump命令進行一些擴充寫一個備份資料庫的shell指令碼，如下是根據我們自己的需求寫的一個指令碼（shell指令碼的具體文法就不講了）

#!/bin/bash

#backup database

if [ $# -ge 1 ] ; then

  [email protected]

else

  databaselist="cjdaily cms_rmrb guangzhou pladaily"

fi

for database in $databaselist

do

if [ ! -e /home/pengyudi/backup_databases/$database ]

then

   mkdir -p /home/pengyudi/backup_databases/$database

fi

mysqldump --opt -h10.38.10.5 -uchen -pab#@c-123 $database |gzip > /home/pengyudi/backup_databases/$database/$database$(date +"%Y-%m-%d %H:%M:%S").sql.gz

if [ $? -eq 0 ]

then

   echo "$(date +"%Y-%m-%d %H:%M:%S") Database ---$database---  Backup Successfully \r\n"

   find /home/pengyudi/backup_databases/$database/ -name $database\* -ctime +1 -exec rm -rf {} \;

else

    echo "$(date +"%Y-%m-%d %H:%M:%S") Database  ---$database---  Backup unsuccessfully,please check out the reason\r\n"

fi

done

6.備份某台主機上的所有資料庫 mysqldump --opt -h10.38.10.5 -uchenliangliagasd  -pabcdeqasdadfa -1238  --all-databases> test.dump

二.資料的恢複

1.切換到mysql的工作環境
use test;

source /tmp/table_structure.sql ;

2.不切換到mysql環境

/usr/bin/mysql -h192.168.4.47 -um_bbs_test_admin -P3309 -p89603d5a test</tmp/table_structure.sql ;    (注意得指定具體的資料庫)

三.使用load匯入資料 和select **** from tabelName into outfile ‘file_name‘的方式匯出資料

補充

1.注意儲存資料的檔案要打引號以及儲存資料的檔案路徑，要特別留意一些細節，如上，確實很容易出錯

  select * from weixin_data_rmrb into outfile ‘weixin_outfile.sql‘;

2.提示： 

當用load命令匯入資料時可以通過適當資料提高匯入速度，對於MyISAM儲存引擎的表

   (1)可以通過以下方式快速匯入大量資料(disable keys 和enable keys 可以用來開啟或關閉非唯一索引的更新，提高匯入速度，但是對InnoDB表無效)

   alter table weixin_data_rmrb  disable keys

load data infile ‘weixin_outfile.sql‘ into table weixin_data_rmrb

   alter table weixin_data_rmrb enable keys  

   (2)關閉唯一性校正可以提高匯入速度

    在匯入資料前後分別執行 set unique_checks=0（關閉唯一性校正） 和 set unique_checks=1來提高匯入速度

對於Innodb表可以使用一下方式提高匯入速度：

   (1)將匯入的資料按照主鍵的順序排列，可以提高匯入速度，因為Innodb表是按照主鍵順序儲存的

   (2)在匯入之前執行set autocommit=0關閉自動認可事物，匯入結束後執行set autocommit=1回複自動認可事物

四.使用二進位日誌恢複資料

1.開啟binlog日誌

修改/etc/my.cnf設定檔

vim /etc/my.cnf

在[mysqld]下面添加如下兩行用於開啟及設定binlog日誌的儲存路徑

log-bin=/home/logs/mysql/mysql-bin #記得要修改許可權，不然mysql無法啟動;  mysql-bin為記錄檔的名稱的組成部分(mysql-bin.000001)

binlog_format=mixed

log=/home/logs/mysql/mysql.log

儲存退出

chown mysql.mysql /home/logs/mysql -R #修改目錄許可權

其中mysql-bin.index記錄了產生了哪些二進位記錄檔

另外跟日誌相關的參數：

expire_logs_days = 7 #保留七天的日誌

slow-query-log = on #開啟慢查詢

slow_query_log_file = /home/logs/mysql/slow.log #慢查詢日誌儲存路徑

long_query_time = 1 #慢查詢的時間，超過一秒的記錄下來

log-queries-not-using-indexes = on #記錄沒用使用到索引的SQL語句

修改配置後重啟mysql：service mysqld restart

進入到mysql命令介面查看二進位日誌相關資訊：

show variables like "%bin%"

2.與binlog日誌有關的命令

flush logs: 執行完之後在存放binlog日誌的目錄下多一個最新的binlog記錄檔

show master status 查看最後一個bin日誌

reset master 清空所有的binlog日誌

通過binlog日誌恢複到某一個記錄檔的操作命令：

/usr/bin/mysqlbinlog --no-defaults -f  /home/logs/mysql/mysql-bin.000008| mysql -h10.38.14.143 -uroot -prootroot

查看某個binlog日誌命令： /usr/bin/mysqlbinlog --no-defaults -f  /home/logs/mysql/mysql-bin.000008

說明：可以根據需要恢複到某一個點的資料，mysqlbinlog後面的主要參數有：

           --stop-position="120"

           --start-positon="20"

           --stop-date="2016-11-19 18:50:42"

--start-date="2016-11-19 18:30:21"

友情連結：

binlog詳細的操作例子請參考如下部落格：

http://blog.chinaunix.net/uid-20494084-id-3753682.html

mysql5.7官方文檔地址http://dev.mysql.com/doc/refman/5.7/en/preface.html

mysqlDatabase Backup