mysql 資料儲存檔案及6類日誌

czd1947發表於2021-01-25
MySql

資料儲存檔案

配置檔案:  
    windows系統 : my.ini
    linux系統 : my.cnf
物理檔案:
    資料庫檔案儲存目錄: mysql安裝目錄/data/  ; 新建一個資料庫, data目錄下會生成一個對應資料庫名的資料夾; 
    不同儲存引擎對應的表檔案:
        myisam儲存引擎的表: 表名.MYD (表資料檔案) ;  表名.MYI (表索引資訊檔案) ; 表名.frm (mysql8.0之前主要儲存表結構資訊的檔案)
        innodb儲存引擎的表: 表名.idb (獨享表的資料和索引資訊檔案) 或者 表名.idbata (共享表的資料和索引檔案)

六類日誌檔案

查詢日誌:
    主要記錄查詢語句, 預設關閉, 不推薦開啟 (耗資源)
    show variables like "%general_log%";   查詢 查詢日誌是否開啟和日誌檔案位置
        general_log             查詢日誌是否開啟變數
        general_log_file        查詢日誌位置
    set global general_log = "on";        開啟查詢日誌
    開啟查詢日誌後, 沒執行一次查詢, 查詢日誌會記錄下對應的資訊, 如下圖

mysq 資料儲存檔案及6類日誌

使用set設定變數的值只會對當前資料庫生效,如果mysql重啟就會失效,永久生效的方法是將變數加到配置檔案中. (mysqld選項中增加)

慢查詢日誌:
    記錄mysql中執行時間超過long_query_time值的sql. 建議開啟, 有利於排查sql的問題
    show variables like "%slow%";
        slow_query_log        慢查詢是否開啟
        slow_query_log_file    慢查詢日誌位置
    show variables like "long_query_time";    慢查詢閾值
    set gloabal long_query_time = 3;    設定慢查詢閾值
    測試: 
        select sleep(3);
        檢視對應日誌檔案資訊

錯誤日誌: 
    記錄mysql服務執行中產生的異常錯誤 (注: 這個異常不是指sql異常,指的是服務異常)
    show variables like "log_error";  檢視錯誤日誌位置

二進位制日誌:
    記錄資料庫的變化, 包含DML,DDL語句. 誤刪資料庫時, 可以通過二進位制檔案恢復資料
    show variables like "log_bin";    檢視二進位制日誌是否開啟
    show binary logs;    檢視所有的二進位制日誌檔案
    show master status; 檢視當前二進位制日誌檔案
    reset master;    刪除所有的二進位制日誌檔案
    flush logs;    清空所有的日誌檔案
    show binlog events in "二進位制日誌檔案";    查詢指定二進位制檔案資訊,包含資料變化記錄的節點
    使用二進位制檔案恢復資料:
    先模擬操作:
        create database mytest;    建立test資料庫
        use test;    進入test資料庫
        建立表 插入資料
        CREATE TABLE `user` (
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `username` varchar(20) DEFAULT NULL,
          `age` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8

        insert into `user`(username,age)values('name1',10);
        insert into `user`(username,age)values('name2',20);
        insert into `user`(username,age)values('name3',30);
        //刪除表資料 刪除表 刪除資料庫
        delete from user where id > 0;
        drop table user;
        drop database test;
    開始資料恢復
        /usr/local/mysql/bin/mysqlbinlog --start-position=初始節點 --stop-position=結束節點 /usr/local/mysql/bin/mysql-bin.000007 | mysql -uroot -p 
        輸入root使用者的密碼,執行完成資料恢復
        注: 開始節點和結束節點可以通過 show binlog events in "二進位制日誌檔案" 命令檢視, "mysql-bin.000007" 是我正在使用的二進位制日誌檔案. 如下圖

mysq 資料儲存檔案及6類日誌

中繼日誌: (暫不做解釋)
    主要是在主從中做運用,同時這個檔案會記錄主節點binlog日誌,再到從節點根據這個檔案進行資料恢復

事務日誌:
    1.重做日誌: 主要是在事務已經提交了但是資料還未重新整理到磁碟,mysql當機了,在一次開啟mysql服務的執行, 保證資料的一致性.
    2.回滾日誌: 
        主要是在事務沒有提交,在下一次mysql服務重啟的時候執行
        當我們執行rollback命令的時候
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章