mysql物理檔案 02

噠噠-blog發表於2020-10-30
MySql

mysql物理檔案

一、資料庫的資料儲存檔案

MySQL資料庫會在data目錄下面簡歷一個以資料庫為名的資料夾，用來儲存資料庫中的表檔案資料。不同的資料庫引擎，每個表的副檔名也不一樣 ，例如： MyISAM用“.MYD”作為副檔名，Innodb用“.ibd”，Archive 用“.arc”，CSV 用“.csv”

1 .FRM 檔案

無論是那種儲存引擎，建立表之後就一定會生成一個以表明命名的’.frm’檔案。frm檔案主要存放與表相關的資料資訊，主要包括表結構的定義信 息。當資料庫崩潰時，使用者可以通過frm檔案來恢復資料表結構。

2 .MYD 檔案

“.MYD”檔案是MyISAM儲存引擎專用，存放MyISAM表的資料。每一個MyISAM表都會有一個“.MYD”檔案與之對應，同樣存放於所屬資料庫的資料夾 下， 和“.frm”檔案在一起。

3 .MYI 檔案

“.MYI”檔案也是專屬於MyISAM儲存引擎的，主要存放MyISAM表的索引相關資訊。對於MyISAM儲存來說，可以被cache 的內容主要就是來源 於“.MYI”檔案中。 每一個MyISAM表對應一個“.MYI”檔案，存放於位置和“.frm”以及“.MYD”一樣。

4 .ibd 檔案與 .ibdata 檔案

這兩種檔案都是存放Innodb資料的檔案，之所以有兩種檔案來存放Innodb的資料（包括索引），是因為Innodb的資料儲存方式能夠通過配置來決 定是使用共享 表空間存放儲存資料，還是獨享表空間存放儲存資料。獨享表空間儲存方式使用“.ibd”檔案來存放資料，且每個表一個“.ibd”檔案 ，檔案存放在和MyISAM資料相 同的位置。如果選用共享儲存表空間來存放資料，則會使用ibdata檔案來存放，所有表共同使用一個（或者多個， 可自行配置）ibdata檔案。

ibdata檔案可以通過innodb_data_home_dir（資料存放目錄）和innodb_data_file_path(配置每個檔案的名稱)兩個引數配置組成

innodb_data_file_path中可以一次配置多個ibdata檔案 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方式

共享表空間以及獨佔表空間都是針對資料的儲存方式而言的。
共享表空間: 某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中。
獨佔表空間: 每一個表都將會生成以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有一個.ibd檔案。其中這個檔案包括了 單獨一個表的資料 內容以及索引內容。

兩者對比

（1）共享表空間：
優點： 可以放表空間分成多個檔案存放到各個磁碟上。資料和檔案放在一起方便管理。
缺點： 所有的資料和索引存放到一個檔案中，多個表及索引在表空間中混合儲存，這樣對於一個表做了大量刪除操作後表空間中將會有大量的空 隙，特別是對於統計分 析，日值系統這類應用最不適合用共享表空間。

（2）獨立表空間：
優點:

  1. 每個表都有自已獨立的表空間。
  2. 每個表的資料和索引都會存在自已的表空間中。
  3. 可以實現單表在不同的資料庫中移動。
  4. 空間可以回收
    a) Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量資料後可以通過:altertable TableName engine=innodb;回 縮不用的空間。
    b) 對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響效能，而且還有機會處理。 缺點：單表增加過大，如超過100 個G。 相比較之下，使用獨佔表空間的效率以及效能會更高一點

共享表空間和獨立表空間之間的轉換

show variables like “innodb_file_per_table”; ON代表獨立表空間管理，OFF代表共享表空間管理；
修改資料庫的表空間管理方式 修改innodb_file_per_table的引數值即可，但是修改不能影響之前已經使用過的共享表空間和獨立表空間；
innodb_file_per_table=1 為使用獨佔表空間
innodb_file_per_table=0 為使用共享表空間

二、慢查詢日誌

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是執行10S以上的語句。預設情況下，Mysql資料庫並不啟動慢查詢日誌，需要我們手動來設定這個引數，當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案，也支援將日誌記錄寫入資料庫表。

1. 慢查詢相關引數

MySQL 慢查詢的相關引數解釋：

slow_query_log ：是否開啟慢查詢日誌，1表示開啟，0表示關閉。

log-slow-queries：舊版（5.6以下版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統則會預設給一個預設的檔案host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統則會預設給一個預設的檔案host_name-slow.log

long_query_time ：慢查詢閾值，當查詢時間多於設定的閾值時，記錄日誌。

log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。

log_output：日誌儲存方式。log_output=‘FILE’表示將日誌存入檔案，預設值是’FILE’。log_output='TABLE’表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。MySQL資料庫支援同時兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output=‘FILE,TABLE’。日誌記錄到系統的專用日誌表中，要比記錄到檔案耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統效能，那麼建議優先記錄到檔案。

2. 慢查詢日誌配置

預設情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的，可以通過設定slow_query_log的值來開啟，如下所示：

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)

使用set global slow_query_log=1開啟了慢查詢日誌只對當前資料庫生效，如果MySQL重啟後則會失效。如果要永久生效，就必須修改配置檔案my.cnf（其它系統變數也是如此）。

修改my.cnf檔案，增加或修改引數slow_query_log 和slow_query_log_file後，然後重啟MySQL伺服器，如下所示
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3

在這裡插入圖片描述

那麼開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裡面呢？ 這個是由引數long_query_time控制，預設情況下long_query_time的值為10秒，可以使用命令修改，也可以在my.cnf引數裡面修改。關於執行時間正好等於long_query_time的情況，並不會被記錄下來。也就是說，在mysql原始碼裡是判斷大於long_query_time，而非大於等於。從MySQL 5.1開始，long_query_time開始以微秒記錄SQL語句執行時間，之前僅用秒為單位記錄。如果記錄到表裡面，只會記錄整數部分，不會記錄微秒部分。

mysql> show variables like "long_query_time%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> set session long_query_time=10.0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long_query_time%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000  |
+-----------------+-----------+
1 row in set (0.04 sec)

如上所示，修改了變數long_query_time，但是查詢變數long_query_time的值還是10，難道沒有修改到呢？ 注意：使用命令 set global long_query_time=4修改後，需要重新連線或新開一個會話才能看到修改值。你用show variables like 'long_query_time’檢視是當前會話的變數值，你也可以不用重新連線會話，而是用show global variables like ‘long_query_time’; 如下所示：
在這裡插入圖片描述
在MySQL裡面執行下面SQL語句，然後我們去檢查對應的慢查詢日誌，就會發現類似下面這樣的資訊。

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> quit
Bye

[root@localhost ~]# cat /www/server/data/mysql-slow.log
/www/server/mysql/bin/mysqld, Version: 8.0.19 (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2020-09-24T22:46:31.232753Z
# User@Host: root[root] @ localhost []  Id:   442
# Query_time: 10.000493  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1600987581;
select sleep(10);

log_output 引數是指定日誌的儲存方式。log_output=‘FILE’表示將日誌存入檔案，預設值是’FILE’。log_output='TABLE’表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。MySQL資料庫支援同時兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output=‘FILE,TABLE’。日誌記錄到系統的專用日誌表中，要比記錄到檔案耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲得更高的系統效能，那麼建議優先記錄到檔案。

mysql> show variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select sleep(15) ;
+-----------+
| sleep(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.01 sec)

mysql> mysql> select * from mysql.slow_log\G;
*************************** 1. row ***************************
    start_time: 2020-09-25 06:50:13.094222
     user_host: root[root] @ localhost []
    query_time: 00:00:15.000808
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 1
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: 0x73656C65637420736C65657028313529
     thread_id: 443
1 row in set (0.00 sec)

ERROR:
No query specified

3. 日誌分析工具

在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具mysqldumpslow

檢視mysqldumpslow的幫助資訊：

[root@localhost bin]# ./mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

引數詳解：

-s, 是表示按照何種方式排序:
c: 訪問計數
l: 鎖定時間
r: 返回記錄
t: 查詢時間
al:平均鎖定時間
ar:平均返回記錄數
at:平均查詢時間
-t, 是top n的意思，即為返回前面多少條的資料；
-g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；
比如：
得到返回記錄集最多的10個SQL。
mysqldumpslow -s r -t 10 /www/server/data/mysql-slow.log
得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /www/server/data/mysql-slow.log
得到按照時間排序的前10條裡面含有左連線的查詢語句。
mysqldumpslow -s t -t 10 -g “left join” /www/server/data/mysql-slow.log
另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現刷屏的情況。
mysqldumpslow -s r -t 20 /www/server/data/mysql-slow.log | more

三、錯誤日誌與二進位制檔案

1. 錯誤日誌

錯誤日誌（Error Log）是 MySQL 中最常用的一種日誌，主要記錄 MySQL 伺服器啟動和停止過程中的資訊、伺服器在執行過程中發生的故障和異常情況等。

1.1 啟動錯誤日誌

在 MySQL 資料庫中，預設開啟錯誤日誌功能。一般情況下，錯誤日誌儲存在 MySQL 資料庫的資料資料夾下，通常名稱為 hostname.err。其中，hostname 表示 MySQL 伺服器的主機名。

在 MySQL 配置檔案中，錯誤日誌所記錄的資訊可以通過 log-error 和 log-warnings 來定義，其中，log-err 定義是否啟用錯誤日誌功能和錯誤日誌的儲存位置，log-warnings 定義是否將警告資訊也記錄到錯誤日誌中。

將 log_error 選項加入到 MySQL 配置檔案的 [mysqld] 組中，形式如下：
[mysqld]
log-error=dir/{filename}

其中，dir 引數指定錯誤日誌的儲存路徑；filename 引數指定錯誤日誌的檔名；省略引數時檔名預設為主機名，存放在 Data 目錄中。

重啟 MySQL 服務後，引數開始生效，可以在指定路徑下看到 filename.err 的檔案，如果沒有指定 filename，那麼錯誤日誌將直接預設為 hostname.err。

注意：錯誤日誌中記錄的並非全是錯誤資訊，例如 MySQL 如何啟動 InnoDB 的表空間檔案、如何初始化自己的儲存引擎等，這些也記錄在錯誤日誌檔案中。

1.2檢視錯誤日誌

錯誤日誌中記錄著開啟和關閉 MySQL 服務的時間，以及服務執行過程中出現哪些異常等資訊。如果 MySQL 服務出現異常，可以到錯誤日誌中查詢原因。

在 MySQL 中，通過 SHOW 命令可以檢視錯誤日誌檔案所在的目錄及檔名資訊。

mysql> show variables like "log_error";
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| log_error     | ./localhost.localdomain.err |
+---------------+-----------------------------+
1 row in set (0.01 sec)

錯誤日誌以文字檔案的形式儲存，直接使用普通文字工具就可以檢視。這裡通過記事本開啟，從上面可以知道錯誤日誌的檔名。該檔案在預設在mysql安裝目錄下或者是mysql目錄data下，開啟 localhost.localdomain.err 檔案，部分內容如下：

2020-09-23T05:26:20.198963Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2020-09-23T05:26:20.290501Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2020-09-23T05:26:20.461211Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-09-23T05:26:20.527833Z 0 [System] [MY-010931] [Server] /www/server/mysql/bin/mysqld: ready for connections. Version: '8.0.19'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.
2020-09-23T05:26:20.606839Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-09-23T05:59:19.305255Z 8 [Warning] [MY-010055] [Server] IP address '192.168.199.172' could not be resolved: Name or service not known
2020-09-23T05:59:54.822964Z 9 [Warning] [MY-010055] [Server] IP address '192.168.199.186' could not be resolved: Name or service not known
2020-09-23T11:48:13.352488Z 10 [Warning] [MY-010055] [Server] IP address '192.168.199.1' could not be resolved: Name or service not known
2020-09-23T14:19:33.749679Z 93 [Warning] [MY-010055] [Server] IP address '192.168.199.187' could not be resolved: Name or service not known
2020-09-24T00:03:16.041761Z 104 [Warning] [MY-010058] [Server] Hostname 'localhost.localdomain' does not resolve to '192.168.199.188'.

以上是錯誤日誌檔案的一部分，主要記載了系統的一些執行錯誤。

1.3 刪除錯誤日誌

在 MySQL 中，可以使用 mysqladmin 命令來開啟新的錯誤日誌，以保證 MySQL 伺服器上的硬碟空間。mysqladmin 命令的語法如下：

mysqladmin -uroot -p flush-logs

執行該命令後，MySQL 伺服器首先會自動建立一個新的錯誤日誌，然後將舊的錯誤日誌更名為 filename.err-old。

MySQL 伺服器發生異常時，管理員可以在錯誤日誌中找到發生異常的時間、原因，然後根據這些資訊來解決異常。對於很久之前的錯誤日誌，檢視的可能性不大，可以直接將這些錯誤日誌刪除。

2. 二進位制檔案

二進位制日誌（Binary Log）也可叫作變更日誌（Update Log），是 MySQL 中非常重要的日誌。主要用於記錄資料庫的變化情況，即 SQL 語句的 DDL 和 DML 語句，不包含資料記錄查詢操作。

如果 MySQL 資料庫意外停止，可以通過二進位制日誌檔案來檢視使用者執行了哪些操作，對資料庫伺服器檔案做了哪些修改，然後根據二進位制日誌檔案中的記錄來恢復資料庫伺服器。

預設情況下，二進位制日誌功能是關閉的。可以通過以下命令檢視二進位制日誌是否開啟，命令如下：

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

從結果可以看出，二進位制日誌是開啟的。

啟動和設定二進位制日誌
在 MySQL 中，可以通過在配置檔案中新增 log-bin 選項來開啟二進位制日誌，格式如下:

[mysqld]
log-bin=dir/[filename]

其中，dir 引數指定二進位制檔案的儲存路徑；filename 引數指定二進位制檔案的檔名，其形式為 filename.number，number 的形式為 000001、000002 等。

每次重啟 MySQL 服務後，都會生成一個新的二進位制日誌檔案，這些日誌檔案的檔名中 filename 部分不會改變，number 會不斷遞增。

如果沒有 dir 和 filename 引數，二進位制日誌將預設儲存在資料庫的資料目錄下，預設的檔名為 hostname-bin.number，其中 hostname 表示主機名。

案例配置如下：

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

在mysql的data目錄下檢視生成的binlog日誌檔案，如下：
在這裡插入圖片描述

四、二進位制檔案基本操作

1. 檢視二進位制檔案

1.1 檢視二進位制日誌檔案列表

可以使用如下命令檢視 MySQL 中有哪些二進位制日誌檔案：

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000035 |    686110 | No        |
| mysql-bin.000036 |     96148 | No        |
+------------------+-----------+-----------+
2 rows in set (0.05 sec)

1.2 檢視當前正在寫入的二進位制日誌檔案

show master status；命令用來檢視當前的二進位制日誌；

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000036 |    96148 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

1.3 檢視二進位制日誌檔案內容

二進位制日誌使用二進位制格式儲存，不能直接開啟檢視。如果需要檢視二進位制日誌，使用show binlog events in ‘mysql-bin.000001’; 命令。

mysql> show binlog events in 'mysql-bin.000036'\G;
*************************** 825. row ***************************
   Log_name: mysql-bin.000036
        Pos: 95772
 Event_type: Query
  Server_id: 1
End_log_pos: 96117
       Info: use `lms_2006_users`; insert into `lms_admin_operation_log` (`user_id`, `path`, `method`, `ip`, `input`, `updated_at`, `created_at`) values (1, 'admin/products', 'GET', '127.0.0.1', '{\"_pjax\":\"#pjax-container\"}', '2020-09-24 09:52:05', '2020-09-24 09:52:05')
*************************** 826. row ***************************
   Log_name: mysql-bin.000036
        Pos: 96117
 Event_type: Xid
  Server_id: 1
End_log_pos: 96148
       Info: COMMIT /* xid=16643 */
826 rows in set (0.01 sec)

ERROR:
No query specified
mysql>

如果不指定查詢的二進位制檔案，則預設為第一個二進位制檔案。

2. 刪除二進位制檔案

二進位制日誌中記錄著大量的資訊，如果很長時間不清理二進位制日誌，將會浪費很多的磁碟空間。刪除二進位制日誌的方法很多，下面介紹幾種刪除二進位制日誌的方法。

2.1 刪除所有二進位制日誌

使用 RESET MASTER 語句可以刪除的所有二進位制日誌，該語句的形式如下：

RESET MASTER;

登入 MySQL 資料庫後，可以執行該語句來刪除所有二進位制日誌。刪除所有二進位制日誌後，MySQL 將會重新建立新的二進位制日誌，新二進位制日誌的編號從 000001 開始。

2.2 根據編號刪除二進位制日誌

每個二進位制日誌檔案後面有一個 6 位數的編號，如 000001。使用 PURGE MASTER LOGS TO 語句，可以刪除指定二進位制日誌的編號之前的日誌。該語句的基本語法形式如下：

PURGE MASTER LOGS TO 'filename.number';

該語句將刪除編號小於 filename.number 的所有二進位制日誌

下面刪除 mylog.000004 之前的二進位制日誌，程式碼如下：

PURGE MASTER LOGS TO 'mylog.000004';

程式碼執行完後，編號為 000001、000002 和 000003 的二進位制日誌將被刪除。

2.3 根據建立時間刪除二進位制日誌

使用 PURGE MASTER LOGS TO 語句，可以刪除指定時間之前建立的二進位制日誌，該語句的基本語法格式如下：

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

其中，“hh”為 24 制的小時。該語句將刪除在指定時間之前建立的所有二進位制日誌。

下面刪除 2019-12-20 15:00:00 之前建立的二進位制日誌，程式碼如下：

PURGE MASTER LOGS TO '2019-12-20 15:00:00";

程式碼執行完後，2019-12-20 15:00:00 之前建立的所有二進位制日誌將被刪除。

五、使用二進位制檔案恢復資料

環境準備

  1. 建立一個用於測試的庫create database mytest;
mysql> create database mytest;
Query OK, 1 row affected (0.03 sec)

mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.02 sec)
  1. 建立一張用於測試的使用者表並新增資料，如下：
    建表語句：
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `user`(username,age)values('starsky',10);
insert into `user`(username,age)values('ShineYork',10);
insert into `user`(username,age)values('Will',10);
mysql> CREATE TABLE `user` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `username` varchar(20) DEFAULT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.09 sec)

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user             |
+------------------+
1 row in set (0.02 sec)

mysql> insert into `user`(username,age)values('starsky',10);
Query OK, 1 row affected (0.02 sec)

mysql> insert into `user`(username,age)values('ShineYork',10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into `user`(username,age)values('Will',10);
Query OK, 1 row affected (0.01 sec)

mysql>

刪除資料或者表

mysql> select * from user;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  1 | starsky   |   10 |
|  2 | ShineYork |   10 |
|  3 | Will      |   10 |
+----+-----------+------+
3 rows in set (0.00 sec)

mysql> delete from `user` where id=1;
Query OK, 1 row affected (0.02 sec)

mysql> delete from `user` where id=2;
Query OK, 1 row affected (0.02 sec)

mysql> delete from `user` where id=3;
Query OK, 1 row affected (0.02 sec)

mysql> drop table `user`;
Query OK, 0 rows affected (0.04 sec)

mysql> drop database `mytest`;
Query OK, 0 rows affected (0.06 sec)

恢復資料

  1. 通過日誌檔案節點恢復資料
    根據節點需要我們檢視日誌檔案提供給我們的資料庫建立，表建立，資料新增等時建立的語句節點，從而恢復資料。
./bin/mysqlbinlog --start-position=96625 --stop-position=97758 /www/server/data/mysql-bin.000036 | mysql -uroot -p

[root@localhost mysql]# ./bin/mysqlbinlog --start-position=96148 --stop-position=97789 /www/server/data/mysql-bin.000036 | mysql -uroot -p
Enter password:
[root@localhost mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 453
Server version: 8.0.19 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mytest;
Database changed
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user             |
+------------------+
1 row in set (0.05 sec)

mysql> select * from `user`;
+----+-----------+------+
| id | username  | age  |
+----+-----------+------+
|  1 | starsky   |   10 |
|  2 | ShineYork |   10 |
|  3 | Will      |   10 |
+----+-----------+------+
3 rows in set (0.01 sec)

mysql>

其它恢復資料命令

//根據時間恢復資料
mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p

//直接執行binlog日誌
mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p

六、中繼日誌（暫時先了解）

中繼日誌

從伺服器I/O執行緒將主伺服器的二進位制日誌讀取過來記錄到從伺服器本地檔案，然後從伺服器SQL執行緒會讀取relay-log日誌的內容並應用到從伺服器，從而使從伺服器和主伺服器的資料保持一致

mysql> show variables like '%relay%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| max_relay_log_size        | 0                                          |
| relay_log                 | localhost-relay-bin                        |
| relay_log_basename        | /www/server/data/localhost-relay-bin       |
| relay_log_index           | /www/server/data/localhost-relay-bin.index |
| relay_log_info_file       | relay-log.info                             |
| relay_log_info_repository | TABLE                                      |
| relay_log_purge           | ON                                         |
| relay_log_recovery        | OFF                                        |
| relay_log_space_limit     | 0                                          |
| sync_relay_log            | 10000                                      |
| sync_relay_log_info       | 10000                                      |
+---------------------------+--------------------------------------------+
11 rows in set (0.01 sec)

mysql>

引數詳情：

max_relay_log_size
relay log 允許的最大值，如果該值為0，則預設值為 max_binlog_size (1G)；
如果不為0，則 max_relay_log_size 則為最大的relay_log檔案大小；

relay_log
定義 relay_log 的位置和名稱，如果值為空，則預設位置在資料檔案的目錄；

relay_log_index
定義 relay_log 索引的位置和名稱，記錄有幾個 relay_log 檔案，預設為2個

relay_log_info_file
定義 relay-log.info 的位置和名稱
relay-log.info 記錄 master 主庫的 binary_log 的恢復位置和 從庫 relay_log 的位置；

relay_log_purge
是否自動清空中繼日誌，預設值為1(啟用)；

relay_log_recovery
當slave從庫當機後，假如relay-log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay-log，並且重新從master上獲取日誌，這樣就保證了relay-log的完整性。預設情況下該功能是關閉的，將relay_log_recovery的值設定為 1時，可在slave從庫上開啟該功能，建議開啟；

sync_relay_log
當設定為1時，slave的I/O執行緒每次接收到master傳送過來的binlog日誌都要寫入系統緩衝區，然後刷入relay log中繼日誌裡，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁碟的大量I/O；
當設定為0時，並不是馬上就刷入中繼日誌裡，而是由作業系統決定何時來寫入，雖然安全性降低了，但減少了大量的磁碟I/O操作。這個值預設是0，可動態修改；

sync_relay_log_info
這個引數和 sync_relay_log 引數一樣。

下一篇：mysql效能問題 03
上一篇：mysql大體結構與執行流程 01

相關文章