mysql日志（介紹路徑修改備份）

2019獨角獸企業重金招聘Python工程師標準>>>

環境：senos6

軟件：mysql2.6.20

mysql日志：
??? 錯誤日志

??? 一般查詢日志

??? 慢查詢日志

??? 二進制日志
??????? 只記錄DDL，DML等引起數據庫改變的操作都會記錄下來
??????? 復制，即時點恢復

??? 中繼日志

??? 事務日志
??????? 只是滿足acid要求來做的
??? innodb才有事務日志

?? ?
??? 錯誤日志：
??????? mysql數據庫啟動記錄也會在錯誤日志中
??????? mysql服務器運行中產生的錯誤信息
??????? 時間調度器運行一個時間產生的信息
??????? log_error| /mydata/data/openvpn.err
??? 一般查詢日志：
??????? general_log???? | OFF # 建議關閉因數據量太大，可以查詢慢日志。系統默認關閉！
??????? general_log_file| /mydata/data/openvpn.log
??????? log_output| [FILE|TABLES|NONE]
??? 一般文件類的修改必須寫在配置文件中，并重啟mysql數據庫服務器
??? 二進制日志（建議開啟。作用很大！）：
??????? binlog_max_flush_queue_time | 0
??????? 不刷新二進制日志
??????? log_bin???????????????????? | OFF
????? ?
????? 開啟的方法：vim /etc/my.cnf
??????? [mysqld]下添加一下內容：
??????? log_bin = mysql-bin
??????? 開啟二進制日志
??? 如果要滾動二進制文件：1.重啟mysql
????????????????????????? 2.mysql>flush logs

??? 慢日志：
??????? slow_query_log????? | OFF
??????? slow_query_log_file | /mydata/data/openvpn-slow.log

??? 查看mysql中和日志相關選項
??? show global variables like '%log%'

??? 查看二進制日志相關信息
??? mysql>show master status
??? mysql>show binary logs
??? mysql>show binlog events in '二進制日志文件'
??? mysql>purge binary logs to '二進制日志文件'
??? mysql>flush logs 滾動二進制日志文件
??? mysqlbinlog用于查看二進制日志信息
??????? --start-datetime
??????? --stpo-datetime

??????? --start-position
??????? --stop-position
??? mysqlbinlog mysql-bin.000001 > 1.sql 把二進制日志文件轉為sql?? 二進制日志轉換成SQL語句后可以導入mysql里面進行數據恢復，作用很大。

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| Log_name???????? | Pos | Event_type? | Server_id | End_log_pos | Info??????????????????????????????????????????????? |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000001 |?? 4 | Format_desc |???????? 1 |???????? 120 | Server ver: 5.6.20-log, Binlog ver: 4?????????????? |
| mysql-bin.000001 | 120 | Query?????? |???????? 1 |???????? 199 | BEGIN?????????????????????????????????????????????? |
| mysql-bin.000001 | 199 | Intvar????? |???????? 1 |???????? 231 | INSERT_ID=1???????????????????????????????????????? |
| mysql-bin.000001 | 231 | Query?????? |???????? 1 |???????? 344 | use `up01`; insert into up01(name) values ('aaaaa') |
| mysql-bin.000001 | 344 | Xid???????? |???????? 1 |???????? 375 | COMMIT /* xid=24 */???????????????????????????????? |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
5 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001' from 199;
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| Log_name???????? | Pos | Event_type | Server_id | End_log_pos | Info??????????????????????????????????????????????? |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000001 | 199 | Intvar???? |???????? 1 |???????? 231 | INSERT_ID=1???????????????????????????????????????? |
| mysql-bin.000001 | 231 | Query????? |???????? 1 |???????? 344 | use `up01`; insert into up01(name) values ('aaaaa') |
| mysql-bin.000001 | 344 | Xid??????? |???????? 1 |???????? 375 | COMMIT /* xid=24 */???????????????????????????????? |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
3 rows in set (0.00 sec)

??? 中繼日志：
??????? 從服務器上，從主服務器的二進制日志文件中復制而來的事件

??? 事務日志：
??????? 僅僅滿足事務acid要求
??????? 不能單獨用來直接進行還原
??????? innodb_flush_log_at_trx_commit| [0,1,2]
??????? 0:每秒同步，并執行磁盤flush
??????? 1:每事務同步，并執行磁盤flush
??????? 2:每事務同步，但不執行磁盤flush，而是由操作系統絕對何時進行磁盤flush

??????? innodb_log_buffer_size 緩存大小
??????? innodb_log_file_size 日志大小
??????? innodb_log_files_in_group 事務日志中每組里面有多少個文件
??????? innodb_mirrored_log_groups有多少個事務日志的鏡像

??????? expire_logs_days 設定二進制日志文件過期天數
??????? general_log 是否啟用一般查詢日志
??????? general_log_file 一般查詢日志存儲路徑

??????? binlog_format 設定二進制日志的類型
??????? stateamment|row|mixed

??????? log-bin是否啟用二進制日志文件（名稱）
??????? sql_log_bin是否啟用二進制日志文件

?????? ?
??????? slow_query_log是否開啟慢日志查詢
??????? slow_query_log_file慢日志文件存儲路徑

??????? log_warnings把告警信息也放在錯誤日志當中
??????? long_query_time=# 設定慢日志超時時間默認10s
??????? sync_binlog 同步二進制日志文件

myisam：
??? 不支持事務
??? 表鎖
??? 支持表壓縮
??? 表格式：frm，myd，myi
??? 對于讀比較多的數據庫可以選擇myisam存儲引擎
innodb：
??? 支持事務
??? 行鎖
??? 表空間
??? 表格式：frm,idb
??? 對于讀寫操作都相當數據庫可以選擇innodb存儲引擎
CSV:
??? 把數據存為一定格式的文本文件

ARCHIVE：
??? 歸檔存儲引擎
??????? 實際使用較少，主要用于數據挖掘
MEMORY：
??? 內存型數據存儲引擎

BLACKHOLE：
??? 黑洞存儲引擎

mysql備份和還原：
??? 備份：
??????? raid0,raid1:這個方式只能保證硬件故障之后不會中斷業務
??????? drop table ....邏輯上的刪除他不能恢復

??? 50G
??????? copy

??? 服務器是否在線：
??????? 熱備：在線備份，讀寫操作都可執行
??????? 溫備：能讀不能寫
??????? 冷備：離線備份，讀寫都不可操作

??? 物理備份：拷貝數據庫實際在系統中產生數據文件，速度快
??? 邏輯備份：將數據導出至文本文件中，速度慢，移植性好

??? 完全備份：備份所有數據
??? 增量備份：僅僅備份上次完全備份或增量備份以后變化的數據
??? 差異備份：僅僅備份上次完全備份以來變化的數據

備份什么：
??? 數據，配置文件，備份二進制日志，事務日志

還原：
??? 備份最好拿來還原測試以下，保證備份可用，并有還原方案

熱備：
??? myisam表進行熱備幾乎不可能，接近熱備，使用快照，不然只能使用溫備
??? innodb表可以使用xtrabackup，mysqldump
??? mysql---->從

備份策略：
??? 完全+增量完全+差異（差異備份：一般使用

??????? mysqlbinlog
??????? --start-datetime
??????? --stpo-datetime

??????? --start-position
??????? --stop-position

）

??? 還原時長需要考慮

mysql備份工具：
??? mysqldump 邏輯備份工具
??? mysqlhotcopy物理備份工具，會鎖表，溫備

文件系統工具：
??? cp
??? 快照

innodb：
??? ibbackup（圖形化操作軟件）
??? xtrabackup（圖形化操作軟件）

mysqldump：
??? 進行完全備份+二進制日志
??? 完全+增量（需要手動進入mysql界面使用：flush logs；）

mysqldump db_name [tb1] [tb2]??????????? # 默認不會備份創建數據庫的sql語句

備份步驟：
mysql>flush tables with read lock;?? #鎖表（建議鎖住所有表，可以防止表表之間的關聯關系）
mysqldump -uroot -p123456 db_name > file.sql? #備份
mysqldump -uroot -p123456 db_name tb_name tb_name > file.sql?? #備份

mysql>unlock tables?? #解鎖表

--master-data=0|1|2
0:不記錄二進制日志文件極其事件位置
1：以change master to方式記錄位置，可以用于恢復后直接啟動從服務器
2：以change master to方式記錄位置，默認為注釋

--lock-all-tables#所定所有表
--lock-tables#鎖表
--flush-logs#二進制日志文件滾動
--single-transaction#如果你確定你所有的表都所innodb存儲引擎的就可以使用此選項完成innodb的熱備

--all-databases備份所有數據庫
--databases備份單庫
備份的時候同時備份創建數據庫sql

--routines備份存儲過程和函數的

--triggers備份觸發器的

myisam：
mysqldump -uroot -p456789 --lock-all-tables（鎖住所有表） --flush-logs （滾動二進制日志）--all-databases（備份所有庫） --master-data=2 （方式記錄位置，默認為注釋）>file.sql?? （完全備份）

innodb熱備:（熱備不許要鎖表）
mysqldump -uroot -p456789 --single-transaction --master-data=2 --all-databases > file.sql

備份還原：
??? mysql>source /path/file.sql ????
??? mysql -uroot -p < /path/file.sql
??? password:
??? 以上備份文件是包含了創建數據庫的sql語句

??? mysql>create database db_name
??? mysql>use db_name;
??? mysql>source /path/file.sql
?? ?
??? mysql -uroot -p -D db_name < /path/file.sql
??? 以上備份文件是沒有包含創建數據庫的sql語句

快速備份單表：
??? mysql>select * into outfile '/path/file' from tb_name

快速還原單表:
??? mysql>load data infile '/path/file' into table tb_name

lvm:使用快照的方式進行備份主要是用于myisam的幾乎熱備
??? 前提：
??????? 數據文件在邏輯卷上
??????? 此邏輯卷必須要有足夠的空間來存儲快照
??????? 數據文件和事務日志必須在同一個邏輯卷上
??? 步湊：
??????? 1.打開會話，添加所有表的鎖操作，讀鎖
????????? mysql>flush tables with read lock;
????????? mysql>flush logs
??????? 2.通過另一給終端，保存二進制日志文件及其位置信息
????????? mysql -uroot -p123456 -e "show master status\G" >> /path/file
??????? 3.創建快照卷
????????? lvcreate -L # -s -p -n LV_NAME /path/to/source_lv
??????? 4.釋放鎖
????????? mysql>unlock tables

??????? 5.掛載快照卷，備份
????????? mount
????????? cp

??????? 6.刪除快照卷

??????? 7.增量備份二進制日志

復制：
?? mysql ab復制
??? mysql server1----U,D,T------>mysql server2
?? 原理：
??? 當主庫有更新操作的時候，主庫會把更新操作的sql，寫如到二進制日志文件當中，并維護一個二進制日志文件的索引文件，以便日志文件輪詢
??? 在從庫啟動異步復制的時候，從庫會開啟兩個I/O線程
??? 其中一個用于連接主庫
??? 另外一個用于傳輸sql

注意：
??? 必須打開二進制日志文件
??? 從庫必須知道主庫是從那一給位置偏移量開始的
??? 主庫和從庫的數據庫名稱可以不一樣，推薦使用一樣
??? 主庫和從庫的mysql版本必須一致
192.168.1.1:master
192.168.1.2:slave

vim /etc/my.cnf
master:
???? log-bin??? = mysql-bin
???? relay-log? = mysql-relay-bin
???? server-id? = 1
???? binlog-do-db= up01
???? binlog-ignore-db = mysql
???? binlog-ignore-db = test
slave:
??? log-bin = mysql-bin
??? relay-log = mysql-relay-bin
??? server-id = 2
??? master-host=192.168.1.1
??? master-port=3306
??? master-user=repl
??? master-password=repl
??? master-connect-retry=30
??? replicate-do-db=up01
??? replicate-ingore-db=mysql
??? replicate-ingore-db=test