mariadb二進制日志和備份恢複

一、日志及其說明

1、錯誤日志

主要記錄伺服器啟動和關閉過程中的資訊，伺服器運作過程中的錯誤資訊，事務排程器運作一個事件是産生的日志資訊，在從伺服器上啟動從伺服器上啟動從伺服器程序是産生的日志資訊。

2、一般查詢日志

general_log、general_log_file 、log、log_output

3、慢查詢日志

查詢執行時長超過指定的查詢時長，即為慢查詢。主要的配置檔案有：slow_query_log，slow_query_log_file /mydata/data/hostname.log

4、二進制日志

任何引起或可能引起資料庫改變的操作如複制、即時點恢複等。二進制日志記錄着每一個ddl,dml,dcl指令及其重新開機等操作導緻資料庫發生變化的資料。

5、中繼日志

relay_log_purge={ON|OFF}#是否自動清理不再需要中繼日志

6、事務日志

将随機IO換裝為順訊IO，保證資料的一緻性、原子性、完整性

二、二進制日志詳解

1、二進制日志的格式

基于語句：statement；基于行：row;混合模式：mixed

2、二進制日志檔案

索引檔案(mysql-bin.index)，二進制日志檔案(mysql-bin.00000x)

3、二進制日志的功能

即時點恢複、複制

4、常用的二進制日志指令

顯示所有的二進制日志檔案：mysql>show binary logs;

執行滾動，即二進制日志會自動滾動一次：mysql>flush logs;

顯示目前使用的二進制日志檔案：mysql>show master status;

指令行檢視二進制日志檔案内容：#mysqlbinlog mysql-bin.000001

檢視二進制日志檔案内容：mysql>show binlog events in 'mysql-bin.000001';

mysqlbinlog常用的選項： --start-time --stop-time --start-position --stop-position

圖示：

顯示二進制日志檔案

二進制檔案格式如下：

<code>[root@chen data]</code><code># mysqlbinlog --stop-position=1451 '/mydata/binlog/master-bin.000001'</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#160929 11:41:17 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 160929 11:41:17 at startup</code>

<code>ROLLBACK/*!*/;</code>

<code>BINLOG '</code>

<code>XY3sVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAABdjexXEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAZwK5ww==</code>

<code>#160929 13:39:36 server id 1 end_log_pos 332 Querythread_id=4exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1475127576/*!*/;</code>

<code>SET @@session.pseudo_thread_id=4/*!*/;</code>

<code>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;</code>

<code>SET @@session.sql_mode=0/*!*/;</code>

<code>SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;</code>

<code>SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;</code>

<code>SET @@session.lc_time_names=0/*!*/;</code>

<code>SET @@session.collation_database=DEFAULT/*!*/;</code>

<code>create database testdb</code>

<code>#160929 13:40:40 server id 1 end_log_pos 454 Querythread_id=5exec_time=0error_code=0</code>

<code>use `testdb`/*!*/;</code>

<code>SET TIMESTAMP=1475127640/*!*/;</code>

<code>SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;</code>

<code>SET @@session.sql_mode=524288/*!*/;</code>

<code>DROP TABLE IF EXISTS `students` /* generated by server */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 752 Querythread_id=5exec_time=0error_code=0</code>

<code>CREATE TABLE `students` (</code>

<code> </code><code>`</code><code>id</code><code>` int(10) unsigned NOT NULL,</code>

<code> </code><code>`name` varchar(20) DEFAULT NULL,</code>

<code> </code><code>`age` tinyint(3) unsigned DEFAULT NULL,</code>

<code> </code><code>`gender` enum(</code><code>'f'</code><code>,</code><code>'m'</code><code>) DEFAULT NULL,</code>

<code> </code><code>PRIMARY KEY (`</code><code>id</code><code>`)</code>

<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8</code>

<code>#160929 13:40:40 server id 1 end_log_pos 864 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `students` DISABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 975 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `students` ENABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1091 Querythread_id=5exec_time=0error_code=0</code>

<code>DROP TABLE IF EXISTS `t2` /* generated by server */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1240 Querythread_id=5exec_time=0error_code=0</code>

<code>CREATE TABLE `t2` (</code>

<code> </code><code>`</code><code>id</code><code>` int(11) DEFAULT NULL</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1346 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `t2` DISABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1451 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `t2` ENABLE KEYS */</code>

<code>DELIMITER ;</code>

<code>ROLLBACK /* added by mysqlbinlog */;</code>

<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</code>

上述格式說明：

事件發生的日期和時間

伺服器id

時間的結束位置

事件的類型

原伺服器生成此事件的線程id

語句的時間戳和寫入二進制日志檔案的時間差

錯誤代碼

事件内容

下一個事件的開始位置

常用伺服器參數：

log_bin = {ON|OFF},此可以為一個檔案路徑

log_bin_trust_funcition_creators

sql_log_bin = {ON|OFF}

sync_binlog

binlog_format = mixed {statement|row|mixed}

max_binlog_cache_size = #二進制日志的緩沖區大小，僅用于緩沖事務類的語句

max_binlog_stmt_cache_size = #狀态緩沖區大小

max_binlog_size = #二進制日志檔案的上限，超過則會自動滾動

set session sql_log_bin=0; #可使得不記錄二進制日志檔案

注意：切将二進制日志檔案和資料檔案存放在同一檔案中

所有與日志相關變量

遷移二進制檔案至其他目錄

三、資料備份及其恢複

資料備份的主要目的是為了資料恢複，對備份的資料做恢複測試，審計，及其測試等相關操作。

1、備份的類型

冷備：cold backup 溫備：warm backup 熱備：hot backup

2、根據備份的資料集分為

完全備份：full backup 部分備份：partial backup

3、根據備份時的接口(直接備份資料檔案還是通過mysql伺服器導出資料)分為

實體備份：直接複制(歸檔)資料檔案的備份方式，physical backup(對資料量很大時适用)

邏輯備份：把資料從資料庫中提取出來儲存為檔案，logical backup(檔案大小大于10G不适用)，主要的備份工具為(mysqldump)。MYISAM隻支援溫備，且不支援增量備份，而INNODB則為熱備，為增量備份。

4、備份工具(mysqldump)

myslqdump為邏輯備份工具，備份和恢複速度比較慢，隻适合資料量不是很大的資料庫。

mysqldump指令：

mysqldump [options] [db_name [tbl_name ...]]

備份單個資料庫：mysqldump db_name

執行個體：mysqldump -uroot -hlocalhost -p testdb >/tmp/testdb.sql

恢複資料時需事先建立庫再執行資料恢複：mysql -uroot -p testdb</tmp/testdb.sql

備份所有資料庫：mysqldump --all-databases >/tmp/all.sql

備份多個資料庫：mysqldump -utestuser -h10.1.10.1 -p --databases testdb test >/tmp/testdbs.sql

恢複時無需先建立庫：mysql -utestuser -h10.1.10.1 -p </tmp/testdbs.sql

單表備份恢複時需事先準備庫才能恢複到資料庫

備份多個資料庫及其恢複

注意：上訴備份方案存在一定的弊端如：使用者正在執行資料操作時，資料則會丢失等情況。

解決方案：在備份是給庫表加上鎖，保證資料的完整性。

--lock-all-tables:請求鎖定所有表

執行個體：mysqldump --databases testdb --lock-all-tables >/tmp/testdb.sql

--single-transaction:單事務，能夠對innodb存儲引擎做熱備

--events:備份事件排程器代碼 --routines:備份存儲過程和存儲函數 --triggers:備份觸發器

備份時請求鎖之後滾動日志：--flush-logs

複制時同步位置标記：--master-data=[0|1|2]

注意：恢複時需關閉二進制檔案，恢複成功後則開啟

關閉二進制日志：set session sql_log_bin=0

開啟二進制日志：set session sql_log_bin=1

鎖表備份步驟：

1)鎖表并且給隻讀權限

flush tables with read lock;

2)滾動日志

flush logs;

3)檢視二進制日志資訊

show master status;

4)使用mysqldump進行資料備份

mysqldump --databases testdb >/tmp/testdb.sql

5)備份完成之後則需解鎖

unlock tables;

指令行備份執行個體如下：

mysqldump -p --databases testdb --lock-all-tables --flush-logs >/tmp/testdb2.sql

mysqldump -p --databases testdb --single-trasaction --flush-logs >/tmp/testdb3.sql

mysqldump -p --databases testdb --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb4.sql

鎖表并在此實行備份，備份完後解鎖

本文轉自chengong1013 51CTO部落格，原文連結：http://blog.51cto.com/purify/1860516，如需轉載請自行聯系原作者

mariadb二進制日志和備份恢複

繼續閱讀

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++驅動概述