MySQl日志

Mariadb日志類型：

• 查詢?nèi)罩荆篻eneral_log
• 慢查詢?nèi)罩荆簂og_slow_queries
• 錯(cuò)誤日志：log_error韩玩，log_warnings
• 二進(jìn)制日志：binlog
• 中繼日志：relay_log
• 事務(wù)日志：innodb_log

1. 查詢?nèi)罩荆?/h5>

記錄查詢語句独悴，日志存儲(chǔ)位置：

• 文件：file
• 表：table（mysql.general_log）
  general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}

2. 慢查詢?nèi)罩荆?/h5>

• 慢查詢：運(yùn)行時(shí)間超出指定時(shí)長的查詢；
  long_query_time
• 存放位置：
  • 文件：FILE
  • 表：TABLE畦木，mysql.slow_log
    log_slow_queries={ON|OFF} : 是否開啟慢查詢?nèi)罩?br> slow_query_log={ON|OFF} : 同上，為了兼容
    slow_query_log_file= : 慢查詢?nèi)罩镜拇娣怕窂?br> log_output={FILE|TABLE|NONE} : 慢查詢?nèi)罩镜挠涗浄绞絳文件|表|NONE}
    log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit
log_slow_verbosity

注意：慢查詢并不一定是語句查詢慢，并不是需要消耗更多的資源導(dǎo)致的空民，有可能是查詢語句锅铅，所需要查詢以來到的表被別的語句鎖定酪呻，被阻塞，這個(gè)語句就有可能執(zhí)行很長時(shí)間盐须，這會(huì)被記錄到慢查詢?nèi)罩疚募校?br> 一般開啟慢查詢?nèi)罩就孳饕饔梅治霾樵冋Z句執(zhí)行較慢的原因；實(shí)際中前端程序很多語句寫的與數(shù)據(jù)庫設(shè)計(jì)不融洽贼邓，經(jīng)常導(dǎo)致慢查詢有可能會(huì)導(dǎo)致死鎖發(fā)生阶冈；

3. 錯(cuò)誤日志：

記錄信息：

• mysqld啟動(dòng)和關(guān)閉過程輸出的信息
• mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息
• event scheduler運(yùn)行時(shí)產(chǎn)生的信息
• 主從復(fù)制架構(gòu)中，從服務(wù)器復(fù)制線程啟動(dòng)時(shí)產(chǎn)生的日志
  log_error=/var/log/mariadb/mariadb.log|OFF
log_warnings={ON|OFF}

4. 二進(jìn)制日志：

用于記錄引起數(shù)據(jù)改變或存在引起數(shù)據(jù)改變的潛在可能性的語句（STATEMENT)或改變后的結(jié)果（ROW）塑径，也可能是二者混合女坑；

• 注意：千萬不能使用cat命令打開查看，否則有可能會(huì)導(dǎo)致文件損壞统舀；

• 功用：“重放”匆骗；備份恢復(fù)，mysql擴(kuò)展功能中的復(fù)制都基于二進(jìn)制日志文件進(jìn)行的

• 二進(jìn)制日志記錄格式：
  binlog_format={STATEMENT|ROW|MIXED}

  • STATEMENT：語句誉简；記錄的是執(zhí)行的語句碉就；會(huì)導(dǎo)致重放結(jié)果不一樣；
  • ROW：行闷串；記錄的是語句執(zhí)行后的結(jié)果瓮钥；數(shù)量大；
  • MIXED：混編；讓系統(tǒng)自行判定該基于哪種方式進(jìn)行骏庸；

• 查看二進(jìn)制日志文件列表：
  SHOW MASTER|BINARY LOGS;

• 查看當(dāng)前正在使用的二進(jìn)制日志文件
  SHOW MASTER STATUS;

• 查看二進(jìn)制日志文件中的事件：
  SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

  • IN 'log_name' ： 指明哪個(gè)二進(jìn)制日志文件中讀取事件毛甲，如果不指明，則從第一個(gè)文件讀到最后一個(gè)文件具被；
  • FROM pos： 指明事件從哪個(gè)位置開始
  • LIMIT [offset玻募，] row_count： 顯示指定個(gè)數(shù)的事件； offset表示便宜量后顯示指定個(gè)數(shù)

• 二進(jìn)制日志文件得構(gòu)成：
  - 日志文件：mysql-bin.文件名后綴一姿，二進(jìn)制格式
  - 索引文件：mysql-bin.index,文本格式

• 二進(jìn)制日志相關(guān)的服務(wù)器變量：

  • sql_log_bin=ON|OFF：是否記錄二進(jìn)制日志七咧；
  • log_bin=/PATH/TO/BIN_LOG_FILE：記錄的文件位置；數(shù)據(jù)庫中通常表現(xiàn)為ON
  • binlog_format=STATEMENT|ROW|MIXED：二進(jìn)制日志記錄的格式
  • max_binlog_size=1073741824：單個(gè)二進(jìn)制日志文件的最大體積叮叹，默認(rèn)為1G
    • 注：(1)到達(dá)最大值后悔自動(dòng)滾動(dòng)艾栋；(2)文件達(dá)到上限時(shí)的大小未必為指定的精確值就滾動(dòng)了；
  • sync_binlog=1|0：設(shè)定是否啟用二進(jìn)制日志同步功能蛉顽；1表示當(dāng)遇到commit時(shí)蝗砾，則觸發(fā)一次二進(jìn)制同步；

二進(jìn)制日志查看工具

mysqlbinlog命令：
用法：mysqlbinlog [options] log-files
- --start-datetime=name: Start reading the binlog at first event having a datetime; equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).
- -j, --start-position=# : Start reading the binlog at position N. Applies to the first binlog passed on the command line.
- --stop-datetime=name: Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).
- --stop-position=# Stop reading the binlog at position N. Applies to the last binlog passed on the command line.
- --user, --host, --password

二進(jìn)制日志的格式：

# at 553
            #160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
            SET TIMESTAMP=1472608568/*!*/;
            BEGIN
            /*!*/;

            事件的起始位置：# at 553
            事件發(fā)生的日期時(shí)間：#160831  9:56:08
            事件發(fā)生的服務(wù)器id：server id 1
            事件的結(jié)束位置：end_log_pos 624
            事件的類型：Query
            事件發(fā)生時(shí)所在服務(wù)器執(zhí)行此事件的線程的ID： thread_id=2 
            語句的時(shí)間戳與將其寫入二進(jìn)制日志文件中的時(shí)間差：exec_time=0
            錯(cuò)誤代碼：error_code=0
            事件內(nèi)容：SET TIMESTAMP=1472608568/*!*/;

5. 中繼日志：

從服務(wù)器上記錄下來從主服務(wù)器的二進(jìn)制日志文件同步過來的事件携冤；一般用于復(fù)制架構(gòu)中

6. 事務(wù)日志：

主要用于幫助事務(wù)性存儲(chǔ)引擎能滿足acid檢試悼粮；事務(wù)型存儲(chǔ)引擎innodb用于保證事務(wù)特性的日志文件：

    mariadb默認(rèn)的事務(wù)存儲(chǔ)引擎為InnoDB；事務(wù)日志參數(shù)：
            innodb_log_group_home_dir ./  #數(shù)據(jù)目錄曾棕，文件名為ib_logfile0
            innodb_log_files_in_group 2 一個(gè)日志文件組中有2個(gè)文件
            innodb_log_file_size 5242880 單個(gè)事務(wù)日志文件最大值
            
            redo log ：重做日志扣猫；事務(wù)提交了但沒同步到磁盤數(shù)據(jù)文件中，則使用redo log
            undo log ：撤銷日志翘地；事務(wù)沒提交成功要撤銷申尤；

MyISAM存儲(chǔ)引擎不能夠安全從崩潰中恢復(fù)，但是InnoDB引擎可以衙耕，因?yàn)镮nnodb有事務(wù)日志昧穿；事務(wù)日志可以把此前所有事件，按照事務(wù)提交或定義的比如每秒一次從內(nèi)存中同步到磁盤上的事務(wù)日志中橙喘；當(dāng)服務(wù)器崩潰粤咪，下次啟動(dòng)時(shí)可以通過讀取事務(wù)日志來判斷此前有沒有事務(wù)沒能正常提交，這樣的事務(wù)教會(huì)滾渴杆，如果正常提交了寥枝，但沒有同步到磁盤文件中，就需要把這個(gè)時(shí)間從事務(wù)日志中讀出來磁奖，存放到數(shù)據(jù)文件中囊拜；
所謂事務(wù)日志就是在磁盤上能把用戶的所有操作，不直接修改數(shù)據(jù)的原處比搭，而是把所有的修改操作直接順序的保存在指定的文件中冠跷；（建議最好使用另外一塊磁盤，可以分散io壓力）
順序的保存在文件中的不是數(shù)據(jù)內(nèi)容本身，因此事務(wù)日志會(huì)有問題：所有操作提交了蜜托，可能剛同步到事務(wù)日志文件中抄囚，還沒同步到磁盤中，此時(shí)如果有人做查詢操作橄务，則只悔針對(duì)原數(shù)據(jù)操作查詢幔托，新修改的數(shù)據(jù)就查不到了；
因此蜂挪，真正執(zhí)行查詢時(shí)重挑，既要根據(jù)數(shù)據(jù)文件做查詢，還要根據(jù)事務(wù)日志做查詢棠涮，這兩種操作特別麻煩谬哀，為了避免這樣的問題，事務(wù)型存儲(chǔ)引擎如Innodb严肪，就會(huì)在主機(jī)內(nèi)存空間中史煎，打開一個(gè)非常大的內(nèi)存空間，把事務(wù)日志中記錄的所有事件都在內(nèi)存中做緩沖/緩存驳糯；查詢語句則都針對(duì)于對(duì)應(yīng)的內(nèi)存空間中所緩存的數(shù)據(jù)進(jìn)行劲室，如果內(nèi)存緩存中沒了，也要從原數(shù)據(jù)和事務(wù)日志中讀進(jìn)來合并在內(nèi)存中執(zhí)行结窘；
Innodb中的這個(gè)內(nèi)存空間叫做buffer pool，并且是innodb修改數(shù)據(jù)的重要組件充蓝，在管理mysql服務(wù)器時(shí)隧枫，這個(gè)內(nèi)存空間需要自行定義：指明打算使用多大內(nèi)存來緩沖這類數(shù)據(jù)；
這個(gè)內(nèi)存空間越大就意味著在內(nèi)存空間中所能夠緩存的數(shù)據(jù)量越大谓苟，在時(shí)間其對(duì)應(yīng)的數(shù)據(jù)操作時(shí)的性能也越好官脓；
但是，這個(gè)事務(wù)日志文件不能太大涝焙，意味如果文件太大卑笨，mysql為了從崩潰中恢復(fù)過來，需要很長時(shí)間仑撞，而且這個(gè)文件還不能太小赤兴，如果太小了，有些數(shù)據(jù)還沒來的及同步到磁盤上事務(wù)日志文件就滿了隧哮；
因此桶良，實(shí)際上事務(wù)日志文件是一組同時(shí)工作的，寫滿了第一個(gè)文件沮翔，就寫第二個(gè)同時(shí)把第一個(gè)同步到磁盤上的數(shù)據(jù)文件中陨帆；這樣等第二個(gè)寫滿了在使用第一個(gè)寫，同時(shí)把第二個(gè)同步到磁盤上，來達(dá)到循環(huán)利用疲牵；一個(gè)日志文件組內(nèi)最少應(yīng)該有兩個(gè)承二，但組內(nèi)的單個(gè)文件不要太大。

備份和恢復(fù)（數(shù)據(jù)）

• 備份：存儲(chǔ)的數(shù)據(jù)副本纲爸；（原始數(shù)據(jù)：持續(xù)改變）
• 恢復(fù)：把副本應(yīng)用到線上系統(tǒng)亥鸠；（僅能恢復(fù)至備份操作時(shí)刻的數(shù)據(jù)狀態(tài)）
  • 時(shí)間點(diǎn)恢復(fù)：可以通過二進(jìn)制日志（binary log）來做時(shí)間點(diǎn)恢復(fù)

為什么要備份：

• 災(zāi)難恢復(fù)：硬件故障（冗余）、軟件故障（bug）缩焦、自然災(zāi)害读虏、黑客攻擊、誤操作袁滥、...
• 測試

備份時(shí)應(yīng)該注意事項(xiàng)：

• 能容忍最多丟失多少數(shù)據(jù)盖桥；
• 恢復(fù)數(shù)據(jù)需要在多長時(shí)間內(nèi)完成；
• 需要恢復(fù)哪些數(shù)據(jù)
  • 做恢復(fù)演練：
    • 測試備份的可用性
    • 增強(qiáng)恢復(fù)操作效率

備份類型：

• 按備份的數(shù)據(jù)集的范圍劃分：
  • 完全備份：整個(gè)數(shù)據(jù)集
  • 部分備份：數(shù)據(jù)集的一部分题翻，比如部分表
• 全量備份揩徊、增量備份、差異備份
  • 完全備份：
  • 增量備份：僅備份自上一次完全備份或增量備份以來變量的那部分?jǐn)?shù)據(jù)嵌赠；
  • 差異備份：僅備份自上一次完全備份以來的變量的那部分?jǐn)?shù)據(jù)
• 物理備份塑荒、邏輯備份
  • 物理備份：復(fù)制數(shù)據(jù)文件進(jìn)行的備份
  • 邏輯備份：從數(shù)據(jù)庫導(dǎo)出數(shù)據(jù)另存在一個(gè)或多個(gè)文件中
• 根據(jù)數(shù)據(jù)服務(wù)是否在線劃分：
  • 熱備：讀寫操作均可進(jìn)行的狀態(tài)下所做的備份
  • 溫備：可讀但不可寫狀態(tài)下進(jìn)行的備份
  • 冷備：讀寫操作均不可進(jìn)行的狀態(tài)下所做的備份

備份需要考慮的因素：

• 鎖定資源多長時(shí)間？
• 備份過程的時(shí)長姜挺？
• 備份時(shí)服務(wù)器的負(fù)載齿税？
• 恢復(fù)過程的時(shí)長？

備份策略：

• 全量+差異+binlogs
• 全量+增量+binlogs
• 備份手段：物理炊豪、邏輯

備份什么：

• 數(shù)據(jù)
• 二進(jìn)制日志凌箕、Innodb的事務(wù)日志
• 代碼（存儲(chǔ)過程、存儲(chǔ)函數(shù)词渤、觸發(fā)器牵舱、時(shí)間調(diào)度器）
• 服務(wù)器的配置文件

備份工具

1. mysqldump：mysql服務(wù)自帶的備份工具；邏輯備份工具
mysqldump事實(shí)上就是向mysql服務(wù)器發(fā)起一個(gè)全量查詢操作缺虐，把所有數(shù)據(jù)拿到本地以后芜壁，并將讀取到的數(shù)據(jù)保存在文件中從而完成備份操作的；

• 支持完全備份高氮、部分備份

• InnoDB支持：熱備

• MyISAM支持：溫備

    mysqldump：
    用法： mysqldump [OPTIONS] database [tables]
    OR      mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]
    OR       mysqldump [OPTIONS] --all-databases [OPTIONS]
  
    MyISAM存儲(chǔ)引擎：支持溫備慧妄，備份時(shí)要鎖定表
            -x, --lock-all-tables:  鎖定所有庫的所有表，讀鎖
            -l剪芍，--lock-tables：鎖定指定庫所有表
    InnoDB存儲(chǔ)引擎：支持溫備和熱備
            --single-transaction:  創(chuàng)建一個(gè)事務(wù)腰涧，基于此快照?qǐng)?zhí)行備份
            其它選項(xiàng)：
                -R, --routines：存儲(chǔ)過程和存儲(chǔ)函數(shù)
                --triggers：備份表相關(guān)的觸發(fā)器
                --skip-triggers：不備份觸發(fā)器
                -E, --events：備份指定數(shù)據(jù)庫相關(guān)的所有event scheduler
                
                --master-data[=#]:#表示1或2
                    1：記錄為CHANGE MASTER TO 語句，此語句不被注釋紊浩；為從服務(wù)器時(shí)使用窖铡；
                    2：記錄為CHANGE MASTER TO語句疗锐，此語句被注釋；建議使用--master-data=2费彼；
                    0：表示不啟用
                -F, --flush-logs:  鎖定表完成后滑臊，即進(jìn)行日志刷新操作
  
  
                示例方案一：mysqldump+復(fù)制binlog
                                        mysqldump：做完全備份
                                        復(fù)制binlog中指定時(shí)間范圍內(nèi)的event；做增量備份
                                        2018-11-3 14:46做一次完全備份：
                                        [root@localhost ~]# mysqldump --all-databases -R --master-data=2 -F --single-transaction -E > all.sql
                                        14:46之后數(shù)據(jù)發(fā)生改變箍铲，再做一次增量備份：
                                        [root@localhost ~]# mysqlbinlog /var/lib/mysql/ON.000010 > binlog.sql
                                        把a(bǔ)ll.sql和binlog.sql拷貝到在另外一臺(tái)mysql服務(wù)器上恢復(fù)：
                                        [root@localhost ~]# scp all.sql binlog.sql root@192.168.43.13:
                                        root@192.168.43.13's password: 
                                        all.sql                                                                                                    100%  511KB   9.6MB/s   00:00    
                                        binlog.sql         
                                        [root@zabbix ~]# mysql -uroot -p < all.sql
                                        Enter password: 
                                        [root@zabbix ~]# mysql -uroot -p < binlog.sql
                                        Enter password: 
                                        [root@zabbix ~]# mysqladmin flush-logs   #手動(dòng)滾動(dòng)二進(jìn)制日志雇卷，也可以重啟mariadb服務(wù)實(shí)現(xiàn)滾動(dòng)
  

注意：二進(jìn)制日志文件不應(yīng)該與數(shù)據(jù)文件放在同一磁盤；
事務(wù)日志可以跟數(shù)據(jù)文件放在同一磁盤颠猴，但有條件還是把事務(wù)日志放在非數(shù)據(jù)磁盤上关划；
任何關(guān)鍵性數(shù)據(jù)都放在有冗余能力是磁盤設(shè)備上，一般用raid10翘瓮；可以做多組贮折，4塊硬盤做raid10，用來放數(shù)據(jù)资盅，另外兩塊硬盤做raid1调榄，用來放二進(jìn)制日志；把事務(wù)日志放在非數(shù)據(jù)磁盤上呵扛；
還有InnoDB的事務(wù)日志非常關(guān)鍵的數(shù)據(jù)每庆，用來做數(shù)據(jù)庫崩潰后恢復(fù)；事務(wù)日志也應(yīng)該放在有冗余能力的磁盤設(shè)備上今穿；

2. cp/tar：基于lvm備份：
第一步：連接到mysql上缤灵，請(qǐng)求鎖定所有表：

請(qǐng)求施加全局鎖：
> FLUSH TABLES WITH READ LOCK;
施加讀鎖，把所有表中的數(shù)據(jù)從內(nèi)存同步到磁盤上蓝晒；如果能請(qǐng)求到讀鎖腮出，其它線程只能讀操作；如果此時(shí)拔创，正有人在執(zhí)行一個(gè)大事務(wù)，且正在做寫操作富蓄，此時(shí)就可能會(huì)等很長時(shí)間才能請(qǐng)求到讀鎖剩燥；
> SHOW MASTER STATUS;

第二步：鎖定后，滾動(dòng)一次二進(jìn)制日志立倍；
記錄二進(jìn)制日志文件及事件位置：
> FLUSH LOGS;
手動(dòng)記錄下來：

    ]# mysql -e 'show master status;'
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000004 |      245 |              |                  |
    +------------------+----------+--------------+------------------+

或：
> SHOW MASTER STATUS;
或：
]# mysql -e 'SHOW MASTER STATUS' > /root/Ppos.date +%F
]# ls
其中顯示內(nèi)容：
pos-2016-06-05

]# cat pos-2016-06-05
顯示內(nèi)容：
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000004 245

注意：二進(jìn)制日志文件要從原卷中備份：

]# cp -a /data/mysql/binlogs/ /tmp

第三步：為邏輯創(chuàng)建快照：

]# lvcreate -L 2G -n mydata-snap -s -p r /dev/myvg/mydata

第四步：釋放全局讀鎖

> UNLOCK TABLES;

模擬數(shù)據(jù)庫變化灭红，做些修改操作；

> USE hellodb;
> DELETE FROM students WHERE StuID=5;
> DELETE FROM students WHERE StuID=6;
> DELETE FROM students WHERE StuID=11;
> SELECT * FROM students;

> SHOW MASTER STATUS;

第五步：掛載快照卷并備份
掛載快照卷：

]# mount -r /dev/myvg/mydata-snap /mnt
]# ls /mnt
binlogs  lost+found  mysql

]# cd /mnt/
]# ls mysql/
aria_log.00000001  hellodb  ib_logfile0  mydb   performance_schema
aria_log_control   ibdata1  ib_logfile1  mysql  test

]# ls binlogs/
mysql-bin.000001  mysql-bin.000003  mysql-bin.index
mysql-bin.000002  mysql-bin.000004

備份數(shù)據(jù)文件

]# cp -a mysql/ /tmp

注意：二進(jìn)制日志文件要從原卷中備份：

第六步：刪除快照卷

]# umount /mnt
]# lvremove /dev/myvg/mydata-snap

模擬數(shù)據(jù)庫服務(wù)器崩潰：

]# systemctl stop mariadb.service
]# rm -rf /data/mysql/*

模擬數(shù)據(jù)文件崩潰

]# rm -rf /data/binlogs/*

模擬二進(jìn)制日志文件崩潰口注；

演示基于lvm還原：

第一步：還原數(shù)據(jù)文件：

]# cp -a /tmp/mysql/* /data/mysql/

還原數(shù)據(jù)文件变擒；即直接拷貝回來；

]# ll /data/mysql/
總用量 28712
-rw-rw---- 1 mysql mysql    16384 6月   5 20:16 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 6月   5 20:16 aria_log_control
drwx------ 2 mysql mysql     4096 6月   5 20:22 hellodb
-rw-rw---- 1 mysql mysql 18874368 6月   5 20:23 ibdata1
-rw-rw---- 1 mysql mysql  5242880 6月   5 20:23 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 6月   5 20:16 ib_logfile1
drwx------ 2 mysql mysql     4096 6月   5 20:22 mydb
drwx------ 2 mysql mysql     4096 6月   5 20:22 mysql
drwx------ 2 mysql mysql     4096 6月   5 20:16 performance_schema
drwx------ 2 mysql mysql     4096 6月   5 20:16 test

確保復(fù)制回來以后寝志，文件的屬主娇斑、屬組是mysql策添；

]# systemctl start mariadb.service

> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |
+------------------+-----------+

> USE hellodb;

> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
崩潰前的數(shù)據(jù)；

第二步：讀取二進(jìn)制日志文件中備份的事件位置

]# cat /pos-2016-06-05 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000004    245

在備份的二進(jìn)制日志文件中找到mysql-bin.000004文件毫缆，從245位置后面唯竹，讀出所有事件，然后保存在一個(gè)sql文件中苦丁；

]# mysqlbinlog --start-position=245 mysql-bin.000004 > recovery.sql

重放事件：

]# mysql < recovery.sql

以上就是通過lvm完成幾乎熱備份浸颓；

3. xtrabackup：由Percona提供的開源工具，支持InnoDB做熱備旺拉，物理備份工具
官方：https://www.percona.com/software/mysql-database/percona-xtrabackup 下載xyrabackup程序包
安裝：

[root@mysql ~]# yum -y install ./percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@mysql ~]# rpm -ql percona-xtrabackup-24-2.4.12-1.el7
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.12
/usr/share/doc/percona-xtrabackup-24-2.4.12/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

xtrabackup是使用C語言開發(fā)的产上，鏈接到InnoDB庫和標(biāo)準(zhǔn)MySQL客戶端庫，因此蛾狗，能通過mysql客戶端（協(xié)議）應(yīng)用程序鏈接至mysql服務(wù)器端完成遠(yuǎn)程備份晋涣；所以，這就也就是為什么xtrabackup必須在線備份的原因淘太；能實(shí)現(xiàn)基于完全奔備份+增量方式還原姻僧；還可以讀取/etc/my.cnf來獲取有關(guān)自己的配置；
只需在/etc/my.cnf中定義[xtrabackup]配置段蒲牧；
它會(huì)讀取兩段配置[mysqld]和[xtrabackup]撇贺；讀取mysqld配置段是了解服務(wù)器端相關(guān)配置參數(shù)配置從而知道怎么去鏈接mysql服務(wù)器端；其次就是讀取自己的配置段冰抢；

在my.cnf配置文件中添加：
[xtrabackup]
target_dir=/data/backups/mysql/

備份示例：
]# xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/
]# ls /data/backups/mysql/
backup-my.cnf  ibdata1  mysql               xtrabackup_binlog_info  xtrabackup_info
hellodb        mydb     performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

--backup: 表示為備份操作
--datadir=/var/lib/mysql/: 指明要備份的數(shù)據(jù)文件存放路徑松嘶；
--target-dir=/data/backups/mysql/ 指明備份文件存放路徑；
--apply-log：表示將多個(gè)增量備份合并到主干備份后以便完成一次性還原操作挎扰；
--prepare：能夠把備份出來的數(shù)據(jù)當(dāng)中那些該提交的事務(wù)翠订，從事務(wù)日志文件中，合并至數(shù)據(jù)文件遵倦，以便在后面合并時(shí)能直接合并成一致的數(shù)據(jù)

所有的備份操作都是基于log sequence number（LSN）日志序列號(hào)實(shí)現(xiàn)的尽超，熱備就是通過讀取日志序列號(hào)來實(shí)現(xiàn)；
備份工具除了xtrabackup命令梧躺，還可以使用innobackupex命令

• innobackupex命令：
  是對(duì)xtrabackup的C程序作了二次封裝的perl腳本似谁，其實(shí)innobackupex只是xtrabackup的前段工具，建議使用innobackupex掠哥；
  用法: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]

  備份：
  ]# innobackupex --user=root /data/backups/mysql
  不用指明datadir巩踏，因?yàn)樗麜?huì)通過mysql server讀取mysqld配置段或server配置段中的datadir選項(xiàng)，并從而知道要備份的數(shù)據(jù)在什么地方续搀；
  /data/backups/mysql/: 指定的備份路徑
  備份壓縮:
  ]# innobackupex --stream=tar ./ | gzip - > backup.tar.gz
  ]# innobackupex --stream=tar ./ | bzip2 - > backup.tar.bz2
  增量備份：
  ]# innobackupex --incremental /backup --incremental-basedir=BASEDIR
  --incremental：表示指明做增量備份塞琼；
  /backup：表示指明增量備份存儲(chǔ)的路徑；
  --incremental-basedir=BASEDIR：表示指明基于哪個(gè)目錄做增量備份禁舷；可基于上一次完全備份做增量彪杉，也可基于上一次增量備份做增量毅往；真正的增量備份是基于最近一次完全備份或增量做的；如果每一次都基于完全備份做增量實(shí)際做的是差異備份在讶；
  因此煞抬，這里的BASEDIR如果每次指的都是完全備份的路徑，做的就是差異備份构哺；
  注意：增量備份僅能應(yīng)用于InnoDB或XtraDB表革答，對(duì)于MyISAM表而言，執(zhí)行增量備份時(shí)其實(shí)進(jìn)行的是完全備份曙强。

  準(zhǔn)備：innobackupex --apply-log --redo-only BASEDIR
  innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR
  恢復(fù)：innobackupex --copy-back BASEDIR

使用innobackupex全量備份残拐、增量備份，還原數(shù)據(jù)示例：

第一步：全量備份數(shù)據(jù)
創(chuàng)建備份文件路徑：
[root@node3 ~]# mkdir -pv /data/backups/mysql/
[root@node3 ~]# cd /data/backups/mysql/
[root@node3 mysql]# innobackupex --user=root --backup /data/backups/mysql/

第二步：連接上數(shù)據(jù)庫做寫修改后再做增量備份
[root@node3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| mytest             |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)

MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> CREATE TABLE students FROM 'SELECT * FROM hellodb.students';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM 'SELECT * FROM hellodb.students'' at line 1
MariaDB [mydb]> CREATE TABLE students (id int not null, name varchar(50),age int );
Query OK, 0 rows affected (0.05 sec)

MariaDB [mydb]> desc students;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

MariaDB [mydb]> INSERT INTO students (id,name,age) VALUE (1,'TOM',34);
Query OK, 1 row affected (0.01 sec)

MariaDB [mydb]> SELECT * FROM students;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | TOM  |   34 |
+----+------+------+
1 row in set (0.03 sec)

MariaDB [mydb]> exit
Bye

增量備份：
[root@node3 ~]# innobackupex --incremental /data/backups/mysql/ --incremental-basedir=/data/backups/mysql/2018-11-04_14-49-30/ 

第三步： 壓縮并復(fù)制到新的數(shù)據(jù)庫服務(wù)器準(zhǔn)備還原
[root@node3 ~]# cd /data/backups/mysql/ 
[root@node3 mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09
[root@node3 mysql]# tar -zcf backup.tar.gz ./*
[root@node3 mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09  backup.tar.gz
[root@node3 mysql]# scp backup.tar.gz root@192.168.43.14:
root@192.168.43.14's password: 
backup.tar.gz  

[root@localhost ~]# tar xf backup.tar.gz -C /data/backups/mysql/
[root@localhost ~]# cd /data/backups/mysql/
[root@localhost mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09    
[root@localhost mysql]# less 2018-11-04_14-49-30/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1849407
last_lsn = 1849407
compact = 0
recover_binlog_info = 0
[root@localhost mysql]# less 2018-11-04_14-57-09/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1849407
to_lsn = 1852838
last_lsn = 1852838
compact = 0
recover_binlog_info = 0

準(zhǔn)備：
[root@localhost mysql]# innobackupex --apply-log --redo-only 2018-11-04_14-49-30/
[root@localhost mysql]# innobackupex --apply-log --redo-only 2018-11-04_14-49-30/ --incremental-dir=./2018-11-04_14-57-09/
[root@localhost mysql]# less 2018-11-04_14-49-30/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1852838
last_lsn = 1852838
compact = 0
recover_binlog_info = 0

還原：
[root@localhost mysql]# innobackupex --copy-back 2018-11-04_14-49-30/
注意：還原時(shí)要保證新數(shù)據(jù)庫服務(wù)器數(shù)據(jù)文件路徑下為空
[root@localhost mysql]# chown -R mysql.mysql /var/lib/mysql/
[root@localhost mysql]# ll /var/lib/mysql/
total 18464
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 hellodb
-rw-r----- 1 mysql mysql 18874368 Nov  4 15:11 ibdata1
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mydb
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mysql
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mytest
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 performance_schema
-rw-r----- 1 mysql mysql       20 Nov  4 15:11 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      535 Nov  4 15:11 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Nov  4 15:11 xtrabackup_master_key_id
[root@localhost mysql]# systemctl start mariadb.service
驗(yàn)證數(shù)據(jù)：
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mydb               |
| mysql              |
| mytest             |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students       |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> select * from students;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | TOM  |   34 |
+----+------+------+
1 row in set (0.00 sec)

4. mysqlhotcopy：幾乎冷備工具碟嘴，不在使用
5. select：

• 備份：SELECT cluase INTO OUTFILE 'FILENAME';
• 恢復(fù)：CREATE TABLE
• 導(dǎo)入：LOAD DATA