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/source
      • 1.6 mysqlshow
    • 2. MySQL日誌
      • 2.1 錯誤日誌
      • 2.2 二進位日誌
        • 2.2.1 概述
        • 2.2.2 日誌格式
        • 2.2.3 日誌讀取
        • 2.2.4 日誌刪除
      • 2.3 查詢日誌
      • 2.4 慢查詢日誌
        • 2.4.1 文件位置和格式
        • 2.4.2 日誌的讀取
    • 3. MySQL複製
      • 3.1 複製概述
      • 3.2 複製原理
      • 3.3 複製優勢
      • 3.4 搭建步驟
        • 3.4.1 master
        • 3.4.2 slave
    • 4. 通過AOP記錄操作日誌
      • 4.1 自定義註解
      • 4.2 性能優化 -分頁
        • 4.8.1 優化count

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.sqlmysqldump -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.000002log_bin=mysqlbin配置二進位日誌的格式binlog_format=ROW如果日誌格式是ROW，直接查看數據，是查看不懂的；可以在mysqlbinlog後面加上參數 -wmysqbinlog -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.loggeneral_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該選項用來配置查詢的時間限制，超過這個時間將認為值慢查詢，將需要進行日誌記錄，默認10slong_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=db012）執行完畢之後，需要重啟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/mysqlbin2）執行完畢之後，需要重啟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@Aspectpublic 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
推薦閱讀：
相關文章