MySQL備份，恢複方案，mysqlbinlog，mysqldump，主從，主主複制

DBMS資料庫管理系統的三層模型：實體層，邏輯層以及視圖層。

  實體層：決定資料的存儲形式。

  邏輯層：是一張有一張的表，一行行的資料記錄。

  視圖層：讓使用者看起來更友善，可有可無。

  存儲引擎：使邏輯層中sql語句轉換成能在磁盤上存儲的實體形式，連接配接邏輯層與實體層。

  常用MySQL存儲引擎：

    MyISAM：

      最經典的MySQL存儲引擎，但如果資料庫一旦崩潰，再重新開機時需要對表進行修複，但MyISAM

    存儲引擎無法保證安全修複，且其不支援事務的進行。支援表級鎖。

    Innodb：

      Innodb存儲引擎，支援事務，資料庫的存儲，是以表空間的方式存儲，支援MVCC的高并發，

    支援四種隔離級别，read-uncommitted讀未送出，read-committed讀送出，repeatable-read幻讀

    serializable串行化。支援行級鎖，間隙鎖。

    Aria：

      Aria存儲引擎資料庫一旦崩潰，能夠對資料庫安全修複增強版MyISAM存儲引擎。

    Memory：

      記憶體級存儲引擎，支援自适應hash索引，查詢能力十分強大。

    MRG_MyISAM:

      将多張表邏輯層連接配接在一起，使使用者就像使用一張表一樣。

    PERFORMANCE_SCHEMA：

      展示資料庫運作時的狀态參數和統計資料。

    CSV：

      基于檔案的檔案存儲資料存儲引擎。

    ARCHIVE：

      歸檔存儲引擎，通常用于做資料倉庫。

  MySQL的日志：

  ①查詢日志：

     general_log {ON|OFF} ：查詢日志是否開啟關閉。

     general_log_file hostname.log ：該變量的生效，需要在log_output為FILE時才能生效。

     log_output  FILE：

      FILE：表示将日志記錄于檔案系統中的檔案；

      TABLE：表示将日志記錄于MySQL中指定的表；

      NONE：表示不将日志記錄輸出出去；

  ②慢查詢日志：

     運作時間超過某指定時長的操作。

     定義查詢逾時時長的變量：

       long_query_time   10.000000

     慢查詢日志是否開啟：

      log_slow_queries    OFF    

     slow_query_log_file hostname.log ：該變量的生效，需要在log_output為FILE時才能生效。

  ③錯誤日志

      log_error：儲存了錯誤日志的檔案路徑；

      log_warnings  {ON|OFF}：是否将mysqld運作過程中産生的"Warning"類的資訊一并記錄到錯誤日志中；

  ④二進制日志

     用于記錄引起資料庫改變的SQL語句，可以通過備份二進制日志中的指定内容來達到資料庫備份，還原的目的

   這一部分操作，在後邊有示例。

     需要在/etc/my.cny配置檔案中設定log_bin路徑。

     mysql>show master|binary logs：顯示目前資料庫中所有二進制日志清單。

     mysql>show master status：顯示目前資料庫中正在使用的資料庫清單，可以對指定日志的position，datetime進行備份還原。

     mysql>show binlog events in '日志path'：顯示對應二進制日志檔案的内容。

  資料庫備份方式：

    資料庫備份作為一種重要的資料儲存手段，需要根據不同環境采取不同的備份操作，最大限度保證資料的安全，畢竟資料

  才是一個企業生存的根本

  ①LVM實作MYSQL實體備份，恢複

   首先，在主機中加入一塊硬碟充當MYSQL的資料存放的邏輯卷，以及備份資料的存儲源

   #fdisk /dev/sdb

   因實驗需要，是以隻配置了一個擴充分區，及一個邏輯分區，将邏輯分區類型更改為8e即可；

   建立邏輯卷：

     #pvcreate /dev/sdb5

     #vgcreate myvg /dev/sdb5

     #lvcreate -L 5G -n mylv myvg

   對邏輯卷進行格式化：

     #mke2fs -t ext4 /dev/myvg/mylv

   建立資料庫存放的路徑

     #mkdir -pv  /mysql/data

   修改資料庫配置檔案/etc/my.cnf

    并在目前啟動mysql使用者家目錄下建立.my.cnf檔案，否則無法正常啟動mysql

   到這裡mysql服務就會在我們指定的邏輯卷中運作，lvm的備份方式主要是溫備份，但

   也可以說是幾乎熱備份，隻要對資料庫加鎖，解鎖的過程夠快，一般幾秒鐘即可，就

   不會引起資料的錯亂；

   對所有表進行加鎖操作：

   MariaDB [hellodb]> flush tables with read lock;

   緊接着拍攝快照，針對于該邏輯卷：

   [root@zabbix-agent4 ~]# lvcreate -L 5G -s -n snap /dev/myvg/mylv

   再對資料庫中的表進行解鎖操作：

   MariaDB [hellodb]> unlock tables;

   對二進制日志進行備份操作，備份目前運作的位置

   mysql -e "show master status" > /path

   （這樣在需要還原資料時可以得知在備份那個時間段後我們執行了哪些操作，從二進制日志檔案可以看出，進而進行還原）

   速度夠快的話就不會産生多大的資料損失，緊接着将快照卷挂載到指定目錄下，對資料進行打包壓縮，解除安裝快照卷即可将資料庫備份；

  ②select語句進行邏輯備份

    建立一個同classes表中資料結構一樣的表；

    使用select将資料儲存在檔案中，并導入到test表當中對資料庫進行備份

  ③使用mysqldump對資料庫進行備份

      mysqldump -uroot -p password 資料庫名 --lock-tables --flush-logs --master-data=2 > /path.sql

      使用上述方式對指定資料庫中的所有表進行加鎖，--flush-logs對二進制日志檔案僅重新整理一次，而不是重複重新整理

      --master-data将二機制日志檔案名和其所用到的時間的位置辨別，追加到備份檔案中，1為不注釋，2為注釋；

      儲存在/path.sql路徑下後，隻需要進入指定備份的資料庫，并使用source /path.sql即可還原備份資料；

      注意：上述方式需要事先建立同名資料庫，然後進入該資料庫執行source操作；

      可使用另一種musqldump格式，可将資料庫的資料格式也備份下來，這樣就無需建立資料庫進行還原

      如：

         mysqldump -uroot -p password --database 資料庫名 --lock-tables --flush-logs --master-data=2 > /path.sql

      對二進制日志進行備份操作，備份目前運作的位置

        mysql -e "show master status" > /path

      （這樣在需要還原資料時可以得知在備份那個時間段後我們執行了哪些操作，從二進制日志檔案可以看出，進而進行還原）

  ④percona xtrabackup實作資料庫備份

       xtrabackup是一款由percona提供的世界上唯一一款開源實作innodb資料庫熱備份的工具

     其備份過程快速可靠，不會打斷正在執行的事務，還原速度快，能夠實作自動檢驗；

     MYISAM完全備份

     MYISAM因為不不支援事務，是以隻能實作溫備份，即在隻讀不可寫的狀況下的資料庫備份，無法進行增量備份

     隻能進行完全備份

     熱備份：可讀可寫；

     溫備份：隻讀不可寫；

     冷備份：不可讀不可寫；

     安裝xtrabackup，在阿裡雲中的epel源中

     #yum install -y percona xtrabackup

     使用innobackupex進行備份

       --user：指定備份資料庫所用的使用者；

       --password：備份資料庫使用者所用的密碼；

       --host：備份資料庫所在的主機；

       --socket：指定備份資料庫所用的socket路徑；

       如：

         因為之前有更改過資料庫檔案的預設路徑，是以需要指定socket

       最後的路徑為完全備份存儲的路徑，innobackex指令如果不特别指定格式的話，會以日期的形式将完全備份

       存儲在該目錄下

        #mkdir -pv /mysql/backup2

        #chown mysql:mysql /mysql/backup2

        進行還原準備操作，因為資料庫在進行還原時，需要考慮在備份資料庫時是否有事務在進行卻尚未送出，是否有事務

      已經送出，但尚未同步到資料庫當中，我們需要針對這類型的資料進行一緻化操作，"準備"的主要作用正是通過復原

      未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。

       備份存儲好之後，将資料全部删除，驗證備份資料

       使用--copy-back選項即可，不管是完全備份，還是完全備份+增量備份的還原都是該選項進行

      注意：備份還原操作，需要在原資料庫檔案全部删除，mysqld程序不在運作的條件下執行

      且備份後的資料庫檔案權限均為root，需要更改為mysql

      啟動服務，資料庫完好無損。

      INNODB完全備份+增量備份

      Innodb存儲引擎支援事務，其可支援溫備份以及熱備份；這也是innobackupex的一大特點；

      增量備份是基于上一次備份後産生的資料差别進行的備份；

      首先将資料庫進行幾次修改，并逐一進行完全備份，增量備份操作

      首次完全備份

      增量備份：

      準備操作：

      --redo-only：在最後一次增量備份時不使用

      準備結束後就可執行還原操作，删除原資料庫内容，關閉資料庫服務

     采用增量備份的方式，分兩次添加了ClassID9，10資料

  MySQL主從複制：

    在MySQL中，支援單項，異步複制，而主從複制，則是由至少兩台MySQL伺服器實作，由一台MySQL

  作為主伺服器，進行寫入操作，而由另一台MySQL伺服器作為從伺服器進行讀操作，主資料庫中的資料

  會自動備份到從伺服器，可以進行讀寫分離操作，以增強資料庫的讀寫性能。而兩台伺服器之間使用不同

  的硬碟，當主伺服器的硬碟損壞時，從伺服器的資料就保留下來，進行資料恢複。

    主從伺服器搭建的原理是，從伺服器開啟資料庫線程sql_thread，io_thread并在/etc/my.cnf中建構中繼

  日志，用于存放從主伺服器複制過來的資料，io_thread線程是向主伺服器之間搭建資料複制的橋梁，當主

  伺服器中資料庫的資料改變時，會寫入二進制日志當中，由io_thread讀取，并複制，需要由主伺服器指定

  執行複制操作的資料庫使用者，并授權，在從伺服器配置相關master，指定主伺服器IP，複制資料庫使用者的

  賬号，密碼，由哪一個二進制日志檔案開始複制，從該二進制檔案當中的哪個位置開始複制等。當io_thread

  取回複制的資料庫内容後，就存放在從伺服器的中繼日志當中，由sql_thread線程将中繼日志中的資料寫入

  到執行存儲引擎，備份成功！

    主伺服器配置：IP,172.16.25.101

    配置二進制檔案，設定server_id

    對使用者進行授權，設定使用者複制權限

    主從複制需要将主伺服器中的資料庫完全備份到從伺服器，否則會報錯，無法進行主從複制

     主伺服器：

     從伺服器：

    從伺服器配置：

      配置 relay_log中繼日志，server_id在/etc/my.cnf中

   配置從伺服器中的master指向

  master_host：主伺服器IP.

  master_user：主伺服器上進行複制的資料庫使用者。

  master_password：主伺服器上進行複制的資料庫使用者密碼。

  master_log_file：從主伺服器上的哪個二進制日志檔案開始複制。這裡我標明的是最後一個二進制日志。

  master_log_pos：從主伺服器上的指定二進制日志檔案的哪個位置開始複制。

  這個時候可以使用show slave status\G檢視

  兩個線程均沒有開啟，現在開啟線程則主從複制啟用

  在主伺服器資料庫中插入一條資料，在檢視從哪個伺服器看是否進行了複制操作

  主伺服器：

  從伺服器：

  檢視資料是否複制：

  MySQL主從複制之半同步：

    半同步複制，在對主從複制的基礎上進行延伸，如主從複制時，主伺服器在向從伺服器傳輸資料時，從伺服器

  突然當機，則資料的傳輸會有兩種情況，

     1.事務還未發送到從伺服器上。

     2.事務已發到從伺服器上，但用戶端會接受到事務傳送失敗的消息，重新發送事務。

    是以針對于以上情況，MySQL資料庫推出了全新的半同步機制，在從伺服器當機後，會有一段延遲時間讓主服

  務器去聯系從伺服器，若沒有聯系上就寫入主伺服器自身，待從伺服器聯系上了再寫入從伺服器，這種異步加同

  步的操作就稱之為半同步。

     半同步需要對主從伺服器加載特殊的插件，插件儲存在/usr/lib64/mysql/plugin中

   主伺服器/etc/my.cnf

   從伺服器/etc/my.cnf

   半同步複制則需要semisync_master.so，semisync_slave.so兩種插件，分别加載到主從伺服器

   主：

   從：

   将主從伺服器的同步機制開啟：

     将從伺服器進行授權，指定master

     在從伺服器上設定隻讀，read_only開啟，并開啟另一個MySQL會話并執行

     mysql>flush tables with read lock;

     隻要該會話不關閉則讀鎖一直存在。

   如何檢視半同步是否成功：

   rpl_semi_sync_master_clients為1

   看nakahcehur在主伺服器處進行寫操作檢視是否同步到從伺服器上

    當關閉從伺服器線程時

    主伺服器再次寫入資料，延遲十秒

    這是因為主伺服器上的同步延遲設定為十秒，當十秒内無法聯系上從伺服器，就寫入自身，如：

    timeout設定為10000毫秒，即為10s

  MySQL主主複制：

    主主複制，至少兩台的資料庫伺服器 ，都作為主伺服器，每一台伺服器既是主伺服器，也是對面主伺服器

  的從伺服器，實作原理同上，不同的是每一台伺服器都需要有中繼日志和二進制日志，因為每一台伺服器都

  作為主伺服器以及從伺服器。主主複制相較于主從複制來說，多了一個mysql入口，相當于mysql的高可用

  但卻需要考慮ID增長的問題。

    主主複制相對于主從複制，需要注意的是

    1.資料不一緻，比如，一台伺服器在進行更新操作，将35歲包括以上人的工資上調3000元，當資料庫複制

    到另一台伺服器，資料進行重寫，寫入到這台伺服器時，如果這台伺服器執行了給每一個員工增加1歲時

    這樣剛巧有些人從34歲到了35歲的年齡的話，該資料就會不一緻。目前隻能使用一些資料恢複軟體來進

    行排錯.

    2.主鍵，當兩台伺服器同時都插入資料時，一些自動增長的如ID的屬性可能會重合，這樣會導緻沖突，數

    據插入删除修改也将會失敗。是以需要設定auto_incremental_incremental以及auto_incremental_offset

    用于設定每次自動增長的量，以及初次增長的基數。一般設定為奇數偶數相對應，這樣就不會重合。

  主主伺服器配置：

    主伺服器1：IP 172.16.25.101

    /etc/my.cnf

    設定為奇數增長，針對于那些auto_increment的字段

    授權，指定master為172.16.25.102

    主伺服器2：IP 172.16.25.102

    授權，指定master為172.16.25.101

   分别為兩台伺服器開啟線程

   mysql>start slave;

   分别從兩台伺服器進行讀寫操作：

   主伺服器1插入一條資料到classes中

    因為設定的是奇數增長是以由11增長到13

    到主伺服器2進行檢視，并插入一條資料：

    由圖可知複制成功，再在主伺服器2中插入一條資料，并在主伺服器1中可見。

    主主複制成功！

     本文轉自 wujunqi1996 51CTO部落格，原文連結:http://blog.51cto.com/12480612/2058997