使用mysqldump進行MariaDB 的備份

使用mysqldump進行MariaDB 的備份

mysqldump備份介紹
mysqldump是mysql用於轉儲存資料庫的公用程式。它主要產生一個SQL指令碼，其中包含從頭重新建立資料庫所必需的命令CREATE TABLE INSERT等，適用於備份資料量不大的資料庫。
優點：備份簡單，恢複容易。
備份缺點：schema和資料存放區在一起，巨大的SQL語句、單個巨大的備份檔案（備份的庫和表都在一個檔案中）。

mysqldump: 是一個mysql用戶端命令，通過mysql協議串連至mysqld，實現Database Backup
命令的文法格式：
  mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表
mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫
mysqldump[OPTIONS] --all-databases [OPTIONS]：備份所有庫
常用選項：
    -A,--all-databases      #備份所有資料庫
    InnoDB:
        --single-transaction：啟動一個大的單一事務實現備份
    -B,--databases db_name1 db_name2 ...：備份指定的資料庫
    -C,--compress：壓縮傳輸；
    -x, --lock-all-tables：鎖定所有表
    -l, --lock-tables：鎖定備份的表

    --flush-logs,-F：鎖定表之後執行flush logs命令；
其它選項：
    -E,--events：備份指定庫的事件調度器；
    -R,--routines：備份預存程序和儲存函數；
    --triggers：備份觸發器
 
    --master-data[=#]：
        1：記錄CHANGE MASTER TO語句；此語句未被注釋；
        2：記錄為備註陳述式；

特別說明：Mariadb的資料恢複嚴重依賴與bin-log日誌，所以為了防止磁碟故障導致資料檔案和bin-log檔案一起丟失，所以最好將bin-log日誌存放到共用儲存中。
設定方法：修改Mariadb設定檔，將記錄檔存放位置指向本地掛載網路儲存的路徑，然後重啟Mariadb服務即可。
[root@MariaDB ~]# vim /etc/my.cnf
log-bin="/backup/bin-log/mysql-bin.x"

驗證：重啟服務之後/backup/bin-log/目錄下就有了Mysql的二進位記錄檔和日誌索引檔案。
[root@MariaDB ~]# ll /backup/bin-log/mysql-bin.*
-rw-rw---- 1 mysql mysql 245 Jun 16 00:00/backup/bin-log/mysql-bin.000001
-rw-rw---- 1 mysql mysql  33 Jun 16 00:00/backup/bin-log/mysql-bin.index

單個庫實現備份恢複
資料庫中有hellodb表一個，需要做對hellodb表的備份，以實現資料庫故障或者發生誤刪除操作時可以及時恢複資料庫。
備份方案為：每周日完全備份一次資料庫，周一到周六增量備份資料庫
備份過程如下
第一天備份：完全備份hellodb資料庫，並且在完全備份的時候鎖定表和滾動二進位日誌
[root@MariaDB~]# mysqldump -B hellodb -u root -p --lock-all-tables --flush-logs  --master-data=2 > /backup/hellodb-`date+%F`.sql
Enterpassword: 
[root@MariaDB~]# ll /backup/
total8
-rw-r--r--1 root root 7950 Jun 16 11:59 hellodb-2015-06-16.sql

由於在做完全備份的時候滾動了二進位日誌，所以在做一些關於資料庫更改的操作都會記錄到新的二進位日誌中，從查看中得知後續日誌會記錄到mysql-bin.000002中。
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |      288 |
| mysql-bin.000002 |      245 |
+------------------+-----------+
2 rows in set (0.00 sec)

備份完成之後的一天時間裡，建立了一張表，並且向表中插入了一些資料
MariaDB[(none)]> use hellodb;
MariaDB[hellodb]> create table tb1 (id int);
MariaDB[hellodb]> insert into tb1 values (1),(2),(3);
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
+------+

第一天增量備份：當天的所有對資料庫變更的語句都會記錄到二進位記錄檔中，只需要滾動二進位日誌，把二進位日誌進行備份即可，二進位滾動之後，第二天的所有對資料庫變更的語句，都會記錄到新的二進位記錄檔中。
MariaDB[hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |      288 |
| mysql-bin.000002 |      577 |
| mysql-bin.000003 |      245 |
+------------------+-----------+
3 rows in set (0.00 sec)

將二進位記錄檔轉換為sql檔案
[root@MariaDB ~]# mysqlbinlog/backup/bin-log/mysql-bin.000002 > /backup/1.sql

第二天操作：繼續向tb1中插入資料
MariaDB[hellodb]> insert into tb1 values (21),(22),(23);
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
|  21 |
|  22 |
|  23 |
+------+

故障類比
hellodb資料庫遭到誤刪除：
MariaDB[hellodb]> DROP database hellodb;

恢複前的準備
查看二進位日誌是記錄到了第三個日誌
MariaDB[(none)]> show master logs;
+------------------+-----------+
|Log_name        | File_size |
+------------------+-----------+
|mysql-bin.000001 |      288 |
|mysql-bin.000002 |      577 |
|mysql-bin.000003 |      533 |    #當前日誌的記錄位置
+------------------+-----------+
3rows in set (0.00 sec)
MariaDB[(none)]> show binlog events in 'mysql-bin.000003'\G;
***************************5. row ***************************
  Log_name: mysql-bin.000005
        Pos: 446
 Event_type: Query
  Server_id: 1
End_log_pos:533
      Info: DROP database hellodb      #記錄的刪除語句
5rows in set (0.00 sec)
 
ERROR:No query specified

由於是整個hellodb資料庫遭到誤刪除，所以需要用一開始的完全備份檔案，以及第一天的增量備份檔案，恢複hellodb資料庫和第一天對資料庫進行修改的內容。
第二天對資料庫進行修改的內容恢複：如果直接把當天的二進位日誌匯入到資料庫，由於資料庫中包含了刪除語句，所以資料庫還是會被刪除；所以在匯入第二天二進位日誌時，需要刪除日誌中的DROP語句。
 
將第二天的二進位記錄檔轉換成sql檔案，放到backup目錄下
[root@MariaDB~]# mysqlbinlog /backup/bin-log/mysql-bin.000003 > /backup/2.sql

開啟2.sql檔案可以看到DROP語句，刪除這個語句
# at446
#15061612:15:22 server id 1  end_log_pos 533  Query  thread_id=20    exec_time=0    error_code=0
SETTIMESTAMP=1434428122/*!*/;
DROP database hellodb  #刪除或者注釋這一行

恢複過程：
恢複之前為了避免產生沒有用的二進位日誌，可以關閉二進位日誌的記錄
MariaDB[(none)]> SET SESSION sql_log_bin=0;
將第一次的完全備份資料匯入到資料庫
MariaDB [hellodb]> SOURCE /backup/hellodb-2015-06-16.sql;

查看資料庫已經匯入，但是tb1表不存在
MariaDB[(none)]> use hellodb;
Databasechanged
MariaDB[hellodb]> show tables;
+-------------------+
|Tables_in_hellodb |
+-------------------+
|classes          |
|coc              |
|courses          |
|scores            |
|students          |
|teachers          |
|toc              |
+-------------------+

匯入第一天增量備份的1.sql檔案。
MariaDB [hellodb]> SOURCE /backup/1.sql;

查看tb1表，發現第一天插入的資料都存在
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
+------+

匯入當天的2.sql檔案
MariaDB [hellodb]> SOURCE /backup/2.sql;

查看資料已經完全恢複
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
|  21 |
|  22 |
|  23 |
+------+

恢複完成，啟用記錄二進位日誌
MariaDB[hellodb]> SET SESSION sql_log_bin=1;

特別說明：如果在恢複中關閉二進位日誌，匯入資料必須在Mariadb命令列匯入，如果在shell命令列匯入還是會記錄二進位日誌的。

全庫的備份恢複
備份
第一次完全備份，備份時鎖定表，並滾動二進位日誌
[root@MariaDB ~]# mysqldump -A -u root -p--lock-all-tables --flush-logs --master-data=2 > /backup/ALL-`date +%F`.sql
Enter password:
[root@MariaDB ~]# ll /backup/
total 532
-rw-r--r-- 1 root root    1980 Jun 16 00:46 1.sql
-rw-r--r-- 1 root root    1957 Jun 16 00:52 2.sql
-rw-r--r-- 1 root root  521774 Jun 16 01:04ALL-2015-06-16.sql
drwxr-xr-x 2 mysql mysql  4096 Jun 16 01:04 bin-log
-rw-r--r-- 1 root root    7950 Jun 16 00:43hellodb-2015-06-16.sql

由於備份的時候滾動了二進位日誌，所以備份之後所有對資料庫產生更改的操作都會記錄到mysql-bin.000004中。
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |      288 |
| mysql-bin.000002 |      577 |
| mysql-bin.000003 |      8833 |
| mysql-bin.000004 |      245 |
+------------------+-----------+
4 rows in set (0.00 sec)

備份完成的當天做了一些操作。
MariaDB[hellodb]> delete from tb1 where id=21;
MariaDB[hellodb]> delete from tb1 where id=22;
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
|  23 |
+------+

第一天��量備份
當天的所有對資料庫變更的語句都會記錄到二進位記錄檔中，只需要滾動二進位日誌，把二進位日誌進行備份即可，二進位滾動之後，第二天的所有對資料庫變更的語句，都會記錄到新的二進位記錄檔中。
MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |      288 |
| mysql-bin.000002 |      577 |
| mysql-bin.000003 |      8833 |
| mysql-bin.000004 |      670 |
| mysql-bin.000005 |      245 |
+------------------+-----------+
5 rows in set (0.00 sec)

將第一天的二進位記錄檔轉換為sql檔案
[root@MariaDB ~]# mysqlbinlog /backup/bin-log/mysql-bin.000004> /backup/all.1.sql

第二天對資料進行了一些操作
MariaDB[hellodb]> insert into tb1 values (1000),(9000);
MariaDB[hellodb]> select * from tb1;
+------+
|id  |
+------+
|    1 |
|    2 |
|    3 |
|  23 |
|1000 |
|9000 |
+------+

類比故障
刪除資料目錄下的所有檔案，類比資料庫故障：
[root@MariaDB ~]# rm -rf /mydata/data/*

資料庫這個時候還可以登入，但是資料庫都不再了
MariaDB [hellodb]> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
+--------------------+

恢複前的準備
探索資料庫故障先關閉資料庫，但是資料庫無法正常關閉，只能關閉進程
[root@MariaDB ~]# service mysqld stop
MySQL server PID file could not be found!                  [FAILED]
[root@MariaDB ~]# killall mysqld

 
由於資料庫資料目錄的所有內容被刪除，就算是匯入了完全備份檔案也是缺少一些檔案，解決方案就是重新初始化一下資料庫。
[root@MariaDB ~]# cd /usr/local/mysql/
[root@MariaDB mysql]# scripts/mysql_install_db--user=mysql --datadir=/mydata/data/

初始化完成之後，一些基本的檔案都存在了
[root@MariaDB mysql]# ll /mydata/data/
total 36
-rw-rw---- 1 mysql mysql 16384 Jun 16 01:22aria_log.00000001
-rw-rw---- 1 mysql mysql    52 Jun 16 01:22 aria_log_control
-rw-r----- 1 mysql root    80 Jun 16 01:18 MariaDB.err
drwx------ 2 mysql root  4096 Jun 16 01:22 mysql
drwx------ 2 mysql mysql  4096 Jun 16 01:22 performance_schema
drwx------ 2 mysql root  4096 Jun 16 01:22 test

 
將第二天的二進位記錄檔轉換為sql檔案
[root@MariaDB ~]# mysqlbinlog/backup/bin-log/mysql-bin.000005 > /backup/all.2.sql

恢複過程
恢複之前啟動Mysql服務，否則無法匯入備份檔案
[root@MariaDB ~]# service mysqld start

匯入第一天的完全備份檔案，由於剛剛初始化完成資料，這個時候的資料庫還沒有密碼
[root@MariaDB ~]# mysql </backup/ALL-2015-06-16.sql

登入資料庫查看，所有的資料庫都已經恢複，但是第一天對資料庫更改的內容還是沒有恢複
MariaDB [(none)]> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| hellodb          |
| mysql            |
| performance_schema |
| test              |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from tb1;
+------+
| id  |
+------+
|    1 |
|    2 |
|    3 |
|  21 |
|  22 |
|  23 |
+------+
6 rows in set (0.00 sec)

匯入第一天的增量備份檔案
[root@MariaDB ~]# mysql -u root -p < /backup/all.1.sql

發現第一天刪除的檔案已經不存在，但是第二天添加的內容還是沒有
MariaDB [hellodb]> select * from tb1;
+------+
| id  |
+------+
|    1 |
|    2 |
|    3 |
|  23 |
+------+
4 rows in set (0.00 sec)

匯入第二天的二進位日誌轉換後的sql檔案。
1 [root@MariaDB ~]# mysql -u root -p </backup/all.2.sql

匯入完成之後，資料庫就回到了故障之前的狀態
MariaDB [hellodb]> select * from tb1;
+------+
| id  |
+------+
|    1 |
|    2 |
|    3 |
|  23 |
| 1000 |
| 9000 |
+------+
6 rows in set (0.00 sec)

基於mysqldump快速搭建從庫 

恢複mysqldump建立的備份組 

mysqldump缺失-q參數導致MySQL被oom幹掉

在 CentOS/RHEL/Scientific Linux 6 下安裝 LAMP (Apache with MariaDB and PHP)

MariaDB Proxy讀寫分離的實現

Linux下編譯安裝配置MariaDB資料庫的方法

CentOS系統使用yum安裝MariaDB資料庫

安裝MariaDB與MySQL並存

Ubuntu 上如何將 MySQL 5.5 資料庫遷移到 MariaDB 10 

[翻譯]Ubuntu 14.04 (Trusty) Server 安裝 MariaDB