日志

11.25

事物隔離級别

事物日志redo undo

事物鎖 begin; update

事物日志性能優化 innodb_flush_log_at_trx_commit=0|1|2|3

innodb事務日志相關配置； show variables like '%innodb_log%';

通用日志

通用日志：記錄對資料庫的通用操作，包括錯誤的SQL語句

通用日志可以儲存在：file（預設值）或 table

通用日志相關設定

general_log=ON|OFF

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE

慢查詢（重點）

slow_query_log=on|off show profile for query 2;

long_query_time=N;

set global log_queries_not_using_indexes=ON;

二進制日志（重點）

記錄導緻資料改變或潛在導緻資料改變的SQL語句

記錄已送出的日志

不依賴于存儲引擎類型

功能：通過“重放”日志檔案中的事件來生成資料副本

注意：建議二進制日志和資料檔案分開存放

二進制日志記錄三種格式；

基于“語句”記錄：statement，記錄語句，預設模式

基于“行”記錄：row，記錄資料，日志量較大 （可恢複資料）

混合模式：mixed, 讓系統自行判定該基于哪種方式進行

二進制日志隻記錄已送出的事物 相當于離線日志

還原資料 臨時關閉二進制日志 set sqllog

顯示二進制記錄檔案 show binary logs

檢視使用中的二進制檔案 show master status

檢視二進制檔案指定内容 show binlog events in 'mysql-bin.000010';

删除000003以前的二進制檔案 purge binary logs to 'mysql-bin.000003';

建立一個新的二進制檔案 flush logs;

資料庫備份還原（重點）

完全備份

增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的資料，備份較

快，還原複雜

差異備份：僅備份最近一次完全備份以來變化的資料，備份較慢，還原簡單

前面必須有個完全備份

注意：二進制日志檔案不應該與資料檔案放在同一磁盤

冷、溫、熱備份

冷備：讀寫操作均不可進行

溫備：讀操作可執行；但寫操作不可執行

熱備：讀寫操作均可執行

MyISAM：溫備，不支援熱備 （不支援事務）

InnoDB：都支援 （不包括DDL語句）

實體和邏輯備份

實體備份：直接複制資料檔案進行備份，與存儲引擎有關，占用較多的空間，速度快

邏輯備份：從資料庫中“導出”資料另存而進行的備份，與存儲引擎無關，占用空間少，速度慢，可

能丢失精度

備份什麼

資料

二進制日志、InnoDB的事務日志

程式代碼（存儲過程、函數、觸發器、事件排程器）

伺服器的配置檔案

備份工具

cp, tar等複制歸檔工具：實體備份工具，适用所有存儲引擎；隻支援冷備；完全和部分備份

LVM的快照：先加鎖，做快照後解鎖，幾乎熱備；借助檔案系統工具進行備份

mysqldump：邏輯備份工具，适用所有存儲引擎，溫備；支援完全或部分備份；對InnoDB存儲

引擎支援熱備，結合binlog的增量備份

xtrabackup：由Percona提供支援對InnoDB做熱備(實體備份)的工具，支援完全備份、增量備份

MariaDB Backup： 從MariaDB 10.1.26開始內建，基于Percona XtraBackup 2.3.8實作

mysqlbackup：熱備份， MySQL Enterprise Edition元件

mysqlhotcopy：PERL 語言實作，幾乎冷備，僅适用于MyISAM存儲引擎，使用LOCK TABLES、

FLUSH TABLES和cp或scp來快速備份資料庫

重點mysqldump xtrabackup

基于LVM備份

1 請求鎖定所有表

mysql>flush tables with read lock;

2 記錄二進制日志檔案及事物位置

mysql>flush logs; mysql>show master status;

mysql-e 'show master status'>/path/to/somefile

3 建立快照

lvcreate -L # -s -p r -n name /dev/vg_name/lvname

4 釋放鎖

mysql>unlock tables;

(5) 挂載快照卷，執行資料備份(6) 備份完成後，删除快照卷(7) 制定好政策，通過原卷備份二進制日志

邏輯備份工具 mysqldump,mydumper,phpmyadmin

Schema和資料存儲在一起、巨大的SQL語句、單個巨大的備份檔案

mysqldump備份工具

mysqldump database [tables] mysqldump -B DB1[DB2 DB3...] mysqldump -A

分庫備份并壓縮

利用二進制日志還原資料庫

二進制日志獨立存放

完全備份，并記錄備份的二進制位置

mysqldump -A --master-data=2 | gzip > /backup/all

date +%F

.sql.gz

11.27

mysql的mylsam相關備份

-x

innoDB相關備份

mysqldump -uroot -A -F -E -R

備份

mysqldump -A -F --single-transetciong --master -data=2 |gzip > /backup/all -`date +%F·.sql.gz

還原

停止資料庫通路（加鎖） 從完全備份中，找到二進制位置

grep '-- change master to' /backup

xtrabackup用法

1 備份；對資料庫做完全或增量備份 2 預制闆；還原前，先對備份的資料，整理至一個臨時目錄

3 還原（複制）；複制會資料庫目錄中

新版xtrabackup備份及還原

1 完全備份；xtrabackup -uroot -p123 --backup --target-dir=/backup/base

2 修改資料

3 第一次增量備份 xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

4 第二次修改資料

5 第二次增量 xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

6 scp -r /backup/* 目标主機；/backup/

#備份過程生成三個備份目錄

/backup/{base,inc1;inc2}

還原過程

mysql叢集

分區

實作mysql主從複制配置

啟動二進制檔案

[mysqld]配置一個唯一ID号server_id=#

建立有複制權限的使用者帳号

grant replication slave on . to 'repluser'@HOST' INDENTIFIED BY 'replpas''

節點配置 1啟動中繼日志 [mysqld] server_id=# read_only=on relay_log=relay-log

relay_log_index=relay-log.index

2 使用複制權限的使用者帳号連接配接至主伺服器，并啟動複制線程

CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='repluser',

MASTER_PASSWORD='replpass', MASTER_LOG_FILE=' mariadb-bin.xxxxxx',

MASTER_LOG_POS=#;

START SLAVE [IO_THREAD|SQL_THREAD];

主從複制相關 限制從伺服器為隻讀 read_only=ON 對super權限使用者無效

2 在從節點清楚資訊 以下都需要先STOP SLAVE

RESET SLAVE 從伺服器清楚master.info relay-log.info,

11.29

主從複制

mysql複制加密

主伺服器開啟SSL,配置證書私鑰路徑

[mysqld]

log-bin

server_id=1

ssl

ssl-ca=/etc/my.cnf.d/ssl/cacert.pem

ssl-cert=/etc/my.cnf.d/ssl/master.crt

ssl-key=/etc/my.cnf.d/ssl/master.key

需要修改權限chown -R mysql.mysql