MySQL階段六——MySQL備份與增量備份MySQL備份與增量備份

-B

1.會備份建立和删除庫的語句；2.備份多個庫；

--compact

去注釋，适合調試

gzip

壓縮：# mysqldump -uroot -pqb123 -S /data/3306/mysql.sock -F -B --master-data=2 mydb|gzip > /server/backup/bak_$(date +%F).sql.gz

-A

備份所有庫

-F

重新整理binlog日志

--master-data=1/2

增加binlog日志檔案及對應的位置點

當為2的時候表示在dump過程中記錄主庫的binlog和pos點，并在dump中注釋這一行；為1表示不注釋

在general_log中：

flush tables with read lock(分為讀鎖和寫鎖)

show master status;

unlock tables;(如果是不鎖表備份，使用了--single-transaction，那麼在開啟事務之後再解鎖)

-l

鎖表

-d

備份表結構

-t

備份表資料

--single-transaction

适合innodb資料庫引擎進行備份

設定這個參數後，整個dump過程使用的都是同一個連接配接id，這個連接配接id的作用是不影響其他連接配接

（set session transaction isolation level repeatable read）

這個參數的作用是，設定事務的隔離級别為可重複讀，即repeatable read,這樣就可以保證在一個事務中所有相同的查詢讀取到相同的資料，也就大概保證了在dump期間，如果其他innodb引擎的線程修改了表資料并送出，對該dump線程的資料并沒有影響。

(start transaction /*with consistent snapshot*/)

這是開啟了一個事務，并設定with consistent snapshot（一緻的快照），如果隻是可重複讀，那麼在開啟事務的時候，還沒有dump資料時，這時其他線程修改了資料，那麼這時第一次查詢到的結果是其他線程送出後的結果，而with consistent snapshot能保證事務開啟的時候，第一次查詢的結果就是事務開啟時的資料。

--events

記錄資料庫事件

myisam引擎壓根不支援事務，是以不能用--single-transaction操作；myisam實作資料一緻采用參數--lock-all-tables,這樣在flush tables with read lock後，知道整個dump結束，才會unlock tables;

如果myisam強制使用--single-transaction，做備份和恢複，會導緻：

01）對于基于某個時間的恢複，會導緻資料重複；

02）對于建庫建表，會報錯；

03）對應更新操作，可能會操作兩次，資料會亂；

解壓備份時壓縮的檔案：

gzip -d 檔案名;

恢複方法：

mysql -uroot -p*** < 備份檔案完整路徑

通過echo實作（這個比較常見）

echo "show databases;" | mysql -uroot -p'qb' -S /data/3308/mysql.sock

提示：此法适合單行字元串比較少的情況。

通過mysql -e參數實作

mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show databases;"

Show status

檢視目前狀态的資料庫狀态資訊

Show global status

檢視整個資料庫運作狀态資訊

Show processlist

檢視正在執行的sql語句

Show full processlist

檢視全部正在執行的完整sql語句

Show global key_buffer_size=1

Show variables;

檢視資料庫的參數資訊

mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show full processlist;"|grep -v Sleep

<code>BAKPATH=</code><code>/opt/dbbak</code>

<code>MYUSER=root</code>

<code>MYPASS=qb123</code>

<code>SOCKET=</code><code>/data/3306/mysql</code><code>.sock</code>

<code>MYCMD=</code><code>"mysql -u$MYUSER -p$MYPASS -S $SOCKET"</code>

<code>MYDUMP=</code><code>"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -B -F -R"</code>

<code>[ ! -d $BAKPATH ] && </code><code>mkdir</code> <code>-p $BAKPATH</code>

<code>DBLIST=`$MYCMD -e </code><code>"show databases;"</code><code>|</code><code>sed</code> <code>1d|</code><code>egrep</code> <code>-</code><code>v</code> <code>"_schema|mysql"</code><code>`</code>

<code>for</code> <code>dbname </code><code>in</code> <code>$DBLIST</code>

<code> </code><code>$MYDUMP $dbname|</code><code>gzip</code> <code>>$BAKPATH/${dbname}_$(</code><code>date</code> <code>+%F).sql.gz</code>

<code>MYDUMP=</code><code>"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"</code>

<code> </code><code>TBLIST=`$MYCMD -e </code><code>"show tables from $dbname"</code><code>|</code><code>sed</code> <code>1d`</code>

<code> </code><code>for</code> <code>tbname </code><code>in</code> <code>$TBLIST</code>

<code> </code><code>[ ! -d $BAKPATH/${dbname} ] && </code><code>mkdir</code> <code>-p $BAKPATH/${dbname}</code>

<code> </code><code>$MYDUMP $dbname $tbname|</code><code>gzip</code> <code>>$BAKPATH/${dbname}/${dbname}_${tbname}_$(</code><code>date</code> <code>+%F).sql.gz</code>

通過防火牆禁止web等應用程式寫資料或者鎖表。讓主庫停止更新，然後進行恢複。

首先要做到正常全備和增量備份：

<code># mysqldump -uroot -pqb123 -S /data/3306/mysql.sock -F -B --master-data=2 mydb|gzip > /server/backup/bak_$(date +%F).sql.gz</code>

發生了删除資料庫的操作：

進行檢查增量備份，由于全備加了-F是以之後增量備份的資料在最後一個binlog中mysql-bin.000003

<code>[root@qbPC backup]</code><code># cd /data/3306/</code>

<code>data mysql mysql-bin.000002 mysql-bin.index mysql_qb3306.err</code>

<code>my.cnf mysql-bin.000001 mysql-bin.000003 mysqld.pid mysql.sock</code>

然後進行恢複：

<code>[root@qbPC backup]</code><code># gzip -d bak_2017-05-27.sql.gz</code>

<code>[root@qbPC 3306]</code><code># grep -i "change" /server/backup/bak_2017-05-27.sql</code>

<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysql-bin.000003'</code><code>, MASTER_LOG_POS=107;</code>

更新binlog。

<code>[root@qbPC 3306]</code><code># mysqladmin -uroot -pqb123 -S /data/3306/mysql.sock flush-logs</code>

<code>[root@qbPC 3306]</code><code># cp mysql-bin.000003 /server/backup/</code>

<code>/server/backup</code>

<code>[root@qbPC backup]</code><code># ls</code>

<code>bak_2017-05-27.sql bak.sql.gz mysql-bin.000003</code>

<code>[root@qbPC backup]</code><code># mysqlbinlog -d mydb mysql-bin.000003 > bin.sql</code>

<code>[root@qbPC backup]</code><code># vim bin.sql</code>

<code>進去删掉錯誤操作的sql語句。</code>

進行恢複

<code>[root@qbPC backup]</code><code># mysql -uroot -pqb123 -S /data/3306/mysql.sock <bak_2017-05-27.sql</code>

<code>[root@qbPC backup]</code><code># mysql -uroot -pqb123 -S /data/3306/mysql.sock mydb <bin.sql</code>

使用主從複制延遲方法！

l 停止一個從庫，然後主庫重新整理binlog，把增量備份mysql-bin.000003的資料恢複成bin.sql（去掉drop語句）；

l 把全備以及增量恢複到從庫；

l 重新整理以後的binlog資料mysql-bin.000004；

l 這個時候停止主庫，快速把重新整理以後到binlog資料恢複到從庫；然後切換從庫為主庫提供服務。

l 問題：當不停主庫，将從庫快速切換到主庫的時候，就可能有crud操作，這個時候将主庫之前重新整理的資料mysql-bin.000004恢複到現在的主庫的時候，可能會有不可預知的問題；最好的方案是将現在主庫停一下，快速恢複資料然後開啟服務，盡可能少停止主庫。

08) 解析MySQL的binlog日志

l 日志作用：用來記錄mysql内部對資料庫更新内容的操作記錄（不會記錄select語句）

l Binlog拆庫： mysqlbinlog -d mydb mysql-bin.000003 > bin.sql（-d指定拆的庫名）

l 指定時間和位置點恢複：mysqlbinlog mysql-bin.000001 --start-position=356 --stop-position=456 -r pos.sql

（指定時間一般不準确）

l Mysqlbinlog不能使用：

原因：my.cnf配置檔案[client]下面配置了default-character-set=utf8，這是mysql的一個bug；

解決：變成loose-default-character-set=utf8或者将其注釋。（修改my.cnf之後不需要重新開機服務，因為mysqlbinlog是從磁盤上讀my.cnf，不是從記憶體讀）

09）防止人為誤操作MySQL資料庫

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

1、mysql幫助說明

<code>[oldboy_c64 ~]</code><code># mysql --help|grep dummy </code>

<code> </code><code>-U, --i-am-a-dummy Synonym </code><code>for</code> <code>option --safe-updates, -U.</code>

<code>i-am-a-dummy FALSE</code>

在mysql指令加上選項-U後，當發出沒有WHERE或LIMIT關鍵字的UPDATE或DELETE時，mysql程式就會拒絕執行

2、指定-U登入測試

<code>[oldboy_c64 ~]</code><code># mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U</code>

<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>

<code>Your MySQL connection </code><code>id</code> <code>is 14</code>

<code>Server version: 5.5.32-log MySQL Community Server (GPL)</code>

<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</code>

<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>

<code>mysql> delete from oldboy.student;</code>

<code>ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column</code>

<code>mysql> quit</code>

提示：不加條件無法删除，目的達到。

3、做成别名防止老大和DBA誤操作

<code>[oldboy_c64 ~]</code><code># alias mysql='mysql -U'</code>

<code>[oldboy_c64 ~]</code><code># mysql -uroot -poldboy123 -S /data/3306/mysql.sock</code>

<code>Your MySQL connection </code><code>id</code> <code>is 15</code>

<code>mysql> delete from oldboy.student where Sno=5;</code>

<code>Query OK, 1 row affected (0.02 sec)</code>

<code>[oldboy_c64 ~]</code><code># echo "alias mysql='mysql -U'" >>/etc/profile</code>

<code>[oldboy_c64 ~]</code><code># . /etc/profile</code>

<code>[oldboy_c64 ~]</code><code># tail -1 /etc/profile</code>

<code>alias</code> <code>mysql=</code><code>'mysql -U'</code>

結論：

在mysql指令加上選項-U後，當發出沒有WHERE或LIMIT關鍵字的UPDATE或DELETE時，mysql程式拒絕執行

本文轉自叫我北北 51CTO部落格，原文連結:http://blog.51cto.com/qinbin/1930364

MySQL階段六——MySQL備份與增量備份MySQL備份與增量備份

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述