MySQL常用工具、日誌及讀寫分離

来源:https://www.cnblogs.com/haizai/archive/2019/07/25/11246951.html
-Advertisement-
Play Games

MySQL常用工具、日誌及讀寫分離 1.MySQL中常用工具 1.1 mysql 1.1.1連接選項 1.1.2 執行選項 1.2 mysqladmin 1.3 mysqlbinlog 1.4 mysqldump 1.4.1 連接選項 1.4.2 輸出內容選項 1.5 mysqlimport/sou ...


MySQL常用工具、日誌及讀寫分離

 

1.MySQL中常用工具

1.1 mysql

該mysql不是值mysql服務，而是指mysql的客戶端工具。
語法 ：
mysql [options] [database]

1.1.1連接選項

在這裡插入圖片描述

1.1.2 執行選項

-e, --execute=name : 執行sql語句並退出
在這裡插入圖片描述

1.2 mysqladmin

mysqladmin是一個執行管理操作的客戶端程式。可以用它來檢查伺服器的配置和當前狀態、創建並刪除資料庫等。
可以通過 ：mysqladmin --help 指令查看幫助文檔。
在這裡插入圖片描述

1.3 mysqlbinlog

由於伺服器生成的二進位日誌文件以二進位格式保存，所以如果想要檢查這些文本的文本格式，就會使用到mysqlbinlog日誌管理工具。
語法 ：
在這裡插入圖片描述

1.4 mysqldump

mysqldump客戶端工具用來備份資料庫或在不同資料庫之間進行數據遷移。備份內容包含創建表，及插入表的sql語句。
語法 ：
在這裡插入圖片描述

1.4.1 連接選項

在這裡插入圖片描述

1.4.2 輸出內容選項

在這裡插入圖片描述
示例 ：
mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a.sql
mysqldump -uroot -p2143 -T /tmp test city
在這裡插入圖片描述
在這裡插入圖片描述
在這裡插入圖片描述

1.5 mysqlimport/source

mysqlimport是客戶端數據導入工具，用來導入mysqldump 加 -T 參數後導出的文本文件。
語法 ：
mysqlimport [options] db_name textfilel [textfile2…]
示例 ：
mysqlimport -urrot -p2143 test /tmp/city.txt
如果需要導入sql文件，可以使用mysql中的source指令：
source /root/tb_book.sql

1.6 mysqlshow

mysqlshow客戶端對查找工具，用來很快的查找存在那些資料庫、資料庫中的表、表中的列或者索引。
語法 ：
mysqlshow [options] [db_name [table_name] [col_name]]]
參數 ：
–count ：顯示資料庫及表的統計信息（資料庫，表均可以不指定）
-i ：顯示指定資料庫或者指定表的狀態信息
示例 ：
在這裡插入圖片描述

2. MySQL日誌

在任何一種資料庫中，都會有各種各樣的日誌，記錄著資料庫工作的方方面面，以幫助資料庫管理員追蹤資料庫曾經發生過的各種事件。MySQL也不例外，在MySQL中，有四種不同的日誌，分別是錯誤日誌、二進位日誌（BINLOG日誌）、查詢日誌和慢查詢日誌，這些日誌記錄著資料庫在不同方面的蹤跡。

2.1 錯誤日誌

錯誤日誌是MySQL中最重要的日誌之一，它記錄了當MySQL的啟動和停止時，以及伺服器在運行過程中發生任何嚴重錯誤時的相關信息。當資料庫出現任何故障導致無法正常使用時，可以首先查看此日誌。
該日誌是預設開啟的，預設存放目錄為mysql的數據目錄（var/lib/mysql），預設的日誌文件為hostname.err(hostname是主機名）。
查看日誌位置指令 ：
show variables like ‘log_error%’;
在這裡插入圖片描述
查看日誌內容 ：
tail -f /var/lib/mysql/xaxh-server.err
在這裡插入圖片描述

2.2 二進位日誌

2.2.1 概述

二進位日誌（BINLOG）記錄了所有的DDL（數據定義語言）語句和DML（數據操作語言）語句，但是不包括數據查詢語句。此日誌對於災難時的數據恢復起著極其重要的作用，MySQL的主從複製，就是通過該binlog實現的。
二進位日誌，預設情況下是沒有開啟的，需要MySQL的陪著文件中開啟，並配置MySQL日誌的格式。
配置文件位置 ：/usr/my.cnf
日誌存放位置 ：配置時，給定了文件名但是沒有指定路徑，日誌預設寫入MySQL的數據目錄。
配置開始binlog日誌，日誌的文件首碼為mysqlbin ------》 生成的文件名如 ：
mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
配置二進位日誌的格式
binlog_format=STATEMENT

2.2.2 日誌格式

STATEMENT
該日誌格式在日誌文件中記錄的都是SQL語句（statmeent），每一條對數據進行修改的SQL都會記錄在日誌文件中，通過MySQL提供的MySQLbinlog工具，可以清晰的查看到每條語句的文本。主從複製的時候，從庫（slave）會將日誌解析為原文本，併在從庫重新執行一次。
ROW
該日誌格式在日誌文件中記錄的是每一行的數據變更，而不是記錄SQL語句。比如，執行SQL語句 ：update tb_book set status = ‘1’，如果是STATEMENT日誌格式，在日誌中會記錄一行SQL文件；如果是ROW，由於是對全表進行更新，也就是每一行記錄都會發生變更，ROW格式的日誌中會記錄每一行的數據變更。
MIXED
這是目前MySQL預設的日誌格式，即混合了STATEMENT和ROW兩種格式。預設情況下採用STATEMENT，但是在一些特殊情況下採用ROW來進行記錄。MIXED格式能儘量利用兩種模式的優點，而避開他們的缺點。

2.2.3 日誌讀取

由於日誌以二進位方式存儲，不能直接讀取，需要用mysqlbinlog工具來查看，語法如下 ：
mysqlbinlog log-file;
查看STATEMENT格式日誌 ：
查看日誌文件 ：預設在/var/lib/mysql目錄下
在這裡插入圖片描述
mysqlbin.index : 該文件是日誌索引文件，記錄日誌的文件名；
mysqlbing.000001 : 日誌文件
查看日誌內容 ：
mysqlbinlog mysqlbing.000001;
在這裡插入圖片描述
查看ROW格式日誌
配置 ：
配置開啟binlog日誌，日誌的文件首碼為mysqlbin —》生成的文件名如：mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
配置二進位日誌的格式
binlog_format=ROW
如果日誌格式是ROW，直接查看數據，是查看不懂的；可以在mysqlbinlog後面加上參數 -w
mysqbinlog -vv mysqlbin.000002
在這裡插入圖片描述

2.2.4 日誌刪除

對於比較繁忙的系統，由於每天生成日誌量大，這些日誌如果長時間不清除，將會占用大量的磁碟空間。下麵我們將會講解幾種刪除日誌的常見方法：
方式1:
通過Reset Master指令刪除全部binlog日誌，刪除之後，日誌編號，將從xxxx.000001重新開始。
查詢之前，先查詢下日誌文件 ：
在這裡插入圖片描述
執行刪除日誌命令 ：
Reset Master
執行之後，查看日誌文件 ：
在這裡插入圖片描述
方式2：
執行指令 purge master logs to 'mysqlbin.’ ，該命令將刪除編號之前的所有日誌。
方式3：
執行指令 purge master logs before ‘yyyy-mm-dd hh24:mi:ss’，該命令將刪除日誌為“yyyy-mm-dd hh24:mi:ss”之前產生的所有日誌。
方式4:
設置參數 --expire_logs_days=#，此參數的含義是設置日誌的過期天數，過了指定的天數後日誌將會被自動刪除，這樣將有利於減少DBA管理日誌的工作量。
配置如下 ：
在這裡插入圖片描述

2.3 查詢日誌

查詢日誌中記錄了客戶端的所有操作語句，而二進位日誌不包含查詢數據的SQL語句。
預設情況下，查看日誌是未開啟的。如果需要開啟查詢日誌，可以設置以下配置 ：
該選項用來開啟查詢日誌 ，可選值 ：0或者 1；0代表關閉，1代表開啟
general_log=1
設置日誌的文件名，如果沒有指定，預設的文件名為host_name.log
general_log_file=file_name
在mysql的配置文件/usr/my.cnf中配置如下內容：
在這裡插入圖片描述
執行完查詢語句以後，再次查看查詢日誌 ：
在這裡插入圖片描述

2.4 慢查詢日誌

慢查詢日誌記錄了所有執行時間超過參數long_query_time 設置值並且掃描記錄數不小於min_examined_row_limit的所有的SQL語句的日誌。
long_query_time預設為10秒，最小為0，精度可以到微妙。

2.4.1 文件位置和格式

慢查詢日誌預設是關閉的。可以通過兩個參數來控制慢查詢日誌：
該參數用來控制慢查詢日誌是否開啟，可取值 ：1和0，1代表開啟，0代表關閉
slow_query_log=1
該參數用來指定慢查詢日誌的文件名
slow_query_log_file=slow_query.log
該選項用來配置查詢的時間限制，超過這個時間將認為值慢查詢，將需要進行日誌記錄，預設10s
long_query_time=10

2.4.2 日誌的讀取

和錯誤日誌、查詢日誌一樣，慢查看日誌記錄的格式也是純文本，可以被直接讀取。
1）查詢long_query_time的值。
在這裡插入圖片描述
2）查看慢查詢日誌文件
直接通過cat指令查詢該日誌文件 ：
在這裡插入圖片描述
如果慢查詢日誌內容很多，直接查看文件，比較麻煩，這個時候可以藉助於mysql自帶的mysqldumpslow工具，來對慢查詢日誌進行分類彙總。
在這裡插入圖片描述

3. MySQL複製

3.1 複製概述

複製是指將主資料庫的DDL和DML操作通過二進位日誌傳到從庫伺服器中，然後在從庫上對這些日誌重新執行（也叫重做），從而使得從庫和主庫的數據保持同步。

3.2 複製原理

MySQL的主從複製原理如下。
在這裡插入圖片描述
從上層來看，複製分成三步 ：

  1. Master主庫在事務提交時，會把數據變更作為時間Events記錄在二進位日誌文件Binlog中。
  2. 主庫推送二進位日誌文件Binlog中的日誌事件到從庫的中級日誌Relay Log。
  3. Slave重做中繼日誌中的事件，將改變反映它自己的數據。

3.3 複製優勢

MySQL複製的優勢主要包含以下三個方面 ：

  1. 主庫出現問題，可以快速切換到從庫提供服務。
  2. 可以在從庫上執行查詢操作，從主庫中更新，實現讀寫分離，降低主庫的訪問壓力。
  3. 可以在從庫中執行備份，以避
  4. 免備份期間影響主庫的服務。

3.4 搭建步驟

3.4.1 master

1）在master的配置文件（/usr/my.cnf)中，配置如下內容 ：
#mysql 服務ID，保證整個集群環境中唯一
server-id=1

#mysql binlog 日誌的存儲路徑和文件名
log-bin=/var/lib/mysql/mysqlbin

#錯誤日誌，預設以及開啟
#log-err
#mysql的安裝目錄
#basedir
#mysql的臨時目錄
#tmpdir
#mysql的數據存放目錄
#datadir
#是否只讀，1代表只讀，0代表讀寫
read-only=0
#忽略的數據，指不需要同步的資料庫
binlog-ignore-db=mysql

#指定同步的資料庫
#binlog-do-db=db01
2）執行完畢之後，需要重啟MySQL ：
service mysql restart;
3）創建同步數據的賬戶，並且進行授權操作：
grant replication slave on . to ‘demo’@‘192.168.192.131’ identified by ‘demo’;
flush privileges;
4）查看master狀態 ：
在這裡插入圖片描述
在這裡插入圖片描述

3.4.2 slave

1）在slave端配置文件中，配置如下內容 ：
#mysql服務端ID，唯一
server-id=2
#指定binlog日誌
log-bin=/var/lib/mysql/mysqlbin
2）執行完畢之後，需要重啟MySQL：
service mysql restart;
3）執行如下指令：
change master to master_host=‘192.168.192.130’,master_user=‘demo’,master_password=‘demo’,
master_log_file=‘mysqlbin.000001’,master_log_pos=413;
指定當前從庫的主庫的IP地址，用戶名，密碼，從那個日誌文件開始的那個位置開始同步推送日誌。
4）開啟同步操作
start slave;
show slave status;
在這裡插入圖片描述
5）停止同步操作
stop slave;

4. 通過AOP記錄操作日誌

4.1 自定義註解

通過自定義註解，來標識方法需不需要進行記錄日誌，如果該方法在訪問時需要記錄日誌，則在該方法上標識該註解即可。
@Inherited
@Document
@Target(Elementype.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface OperateLog{
}
@Component
@Aspect
public class OperateAdvice {
private static Logger log = Logger.getLogger(OperateAdvice.class);

@Around("execution(* cn.demo.controller.*.*(..)) && @annotation(operateLog)")
public Object insertLogAround(ProceedingJoinPoint pjp,OperateLog operateLog) throws Throwable {
		@Around("execution(* com.example.demo.controller.*.*(..)) && @annotation(operateLog)")
		public Object logAround(ProceedingJoinPoint pjo,OperateLog operateLog) {
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// 訪問方法的時間
			String dateTime = sdf.format(new Date());
			// 訪問的方法
			String methodName = pjp.getSignature().getName();
			// 訪問哪一個類
			String className = pjp.getClass().getName();
			// 方法入參
			String paramAndValue = Arrays.toString(pjp.getArgs());
			// 統計耗時
			long startTime = System.currentTimeMillis();
			// 列印方法入參日誌
			log.info("輸出日誌");
			// 放行，並獲取返回值
			Object returnValue= new Object();
			try {
				returnValue= pjp.proceed();
			} catch (Throwable throwable) {
				log.error("");
			}
			long endTime = System.currentTimeMillis();
			// 列印請求耗時日誌
			log.info();
			// 列印返回值日誌
			return returnValue;
	}
}

}

4.2 性能優化 -分頁

4.8.1 優化count

創建一張表用來記錄日誌表的總數據量 ：
create table log_counter (
logcount bigint not null
)engine = innodb default CHARSET = utf8;
在每次插入數據之後，更新該表 ：

update log_counter set logcount = logcount + 1

在進行分頁查詢時，獲取總記錄數，從該表中查詢即可。

select logcount from log_counter limit 1


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Ubuntu18.04安裝PHP7.3
    因為近期需要做畢業設計，需要用到Linux系統，在此分享一下在Linux-Ubuntu系統下安裝PHP環境的一小點知識，如有偏差錯誤的，請各位學友多多指教哈！ sudo apt-get install software-properties-common python-software-proper ...
  • linux命令之sed
    sed 是一種線上編輯器，它一次處理一行內容。處理時，把當前處理的行存儲在臨時緩衝區中， 稱為“模式空間”（pattern space），接著用 sed 命令處理緩衝區中的內容，處理完成後，把緩衝區的內容送往屏幕。接著處理下一行，這樣不斷重覆，直到文件末尾。文件內容並沒有 改變，除非你使用重定向存儲 ...
  • linux命令之grep
    grep (global search regular expression(RE) and print out the line,全面搜索正則表達式 並把行列印出來)是一種強大的文本搜索工具，它能使用正則表達式搜索文本，並把匹配的行列印出來。Unix 的 grep 家族包括 grep、egrep ...
  • Ubuntu：一個部署好的tomcat應用(war包)怎麼用Nginx實現動靜分離？
    ubuntu nginx tomcat 虛擬目錄 反向代理 動靜分離 ...
  • 使用Docker運行SQL Server
    現在.net core已經跨平臺了，大家也都用上了linux用上了docker。跟.net經常配套使用的SQL SERVER以前一直是windows only，但是從SQL Server 2017開始已經支持運行在docker上，也就說現在SQL Serer已經可以運行在linux下了。 下麵在Ub ...
  • MySQL必知必會3
    創建和操縱表 創建表 輸入 分析 表名緊跟在CREATE TABLE 關鍵字後面 ，實際的表定義括在圓括弧中。各列之間用逗號分隔。這個表由9列組成。每列的定義以列名開始，後跟列的數據類型。表的主鍵可以在創建表時用PRIMARY KEY關鍵字指定，這裡，列cust_id指定作為主鍵列。整條語句由右圓括 ...
  • Linux Mysql操作命令
    第一篇隨筆，暫水平有限，僅作學習記錄。如果能幫助到你那就更好了！ 登陸Mysql服務mysql -u root -p ... 一、基礎命令 (命令以' ; '號結束)資料庫列表show databases; 切換/使用某個庫use 庫名; 查看數據表列表 (需切換至某個庫使用，否則會報錯ERROR ...
  • Java入門 資料庫第一天
    資料庫概述 本菜鳥使用的資料庫軟體為Mariadb，以下內容都是以Mariadb資料庫軟體來寫的學習總結。 資料庫 所謂的資料庫就是用於存儲、管理數據的倉庫，資料庫根據底層存儲數據結構的不同可以分為很多類，但現在流行的是關係型資料庫和非關係型資料庫。 關係型資料庫 關係型資料庫底層是以二維表的形式保 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類