Xtrabackup介紹
Xtrabackup 是由 percona 開源的免費資料庫熱備份軟體，它能對 InnoDB 資料庫和 XtraDB 儲存引擎的資料庫非阻塞地備份（對於 MyISAM 的備份同樣需要加表鎖）；mysqldump 備份方式是採用的邏輯備份，其最大的缺陷是備份和恢復速度較慢，如果資料庫大於 50G ，mysqldump 備份就不太適合。
Xtrabackup 安裝完成後有4個可執行檔案，其中2個比較重要的備份工具是 innobackupex xtrabackup
- xtrabackup 是專門用來備份 InnoDB 表的，和 mysql server 沒有互動；
- innobackupex 是一個封裝 xtrabackup 的 Perl 指令碼，支援同時備份 innodb 和myisam，但在對 myisam 備份時需要加一個全域性的讀鎖。
- xbcrypt 加密解密備份工具
- xbstream 流傳打包傳輸工具，類似 tar
Xtrabackup優點
- 備份速度快，物理備份可靠
- 備份過程不會打斷正在執行的事務（無需鎖表）
- 能夠基於壓縮等功能節約磁碟空間和流量
- 自動備份校驗
- 還原速度快
- 可以流傳將備份傳輸到另外一臺機器上
- 在不增加伺服器負載的情況備份資料
Xtrabackup備份原理
備份原理
備份開始時首先會開啟一個後臺檢測程式，實時檢測
mysq redo 的變化，一旦發現有新的日誌寫入，立刻將日誌記入後臺日誌檔案 xtrabackup_log 中，之後複製 innodb 的資料檔案一系統表空間檔案 ibdatax，複製結束後，將執行 flush tables with readlock ,然後複製 .frm MYI MYD 等檔案，最後執行 unlock tables ,最終停止 xtrabackup_log
輸出如下提示資訊
xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.
171205 10:17:52 completed OK!Xtrabackup增量備份介紹
xtrabackup增量備份的原理是：
- 首先完成一個完全備份，並記錄下此時檢查點LSN；
- 然後增量備份時，比較表空間中每個頁的LSN是否大於上次備份的LSN，若是則備份該頁並記錄當前檢查點的LSN。
增量備份優點：
- 資料庫太大沒有足夠的空間全量備份，增量備份能有效節省空間，並且效率高；
- 支援熱備份，備份過程不鎖表（針對InnoDB而言），不阻塞資料庫的讀寫；
- 每日備份只產生少量資料，也可採用遠端備份，節省本地空間；
- 備份恢復基於檔案操作，降低直接對資料庫操作風險；
- 備份效率更高，恢復效率更高。
Xtrabackup安裝
下載安裝xtrabackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar [root@centos ~]# ll total 703528 -rw-r--r-- 1 root root 654007697 Sep 27 09:18 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -rw-r--r-- 1 root root 65689600 Nov 30 00:11 Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar [root@centos ~]# tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar [root@centos ~]# yum install percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm -y [root@centos ~]# which xtrabackup /usr/bin/xtrabackup [root@centos ~]# innobackupex -v innobackupex version 2.4.9 Linux (x86_64) (revision id: a467167cdd4)已經安裝完成
建立測試資料
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);
Query OK, 0 rows affected (0.15 sec)
mysql> insert into T1 values('zhang','man');
Query OK, 1 row affected (0.01 sec)
mysql> insert into T1 values('zhan','man');
Query OK, 1 row affected (0.01 sec)
mysql> insert into T1 values('sun','woman');
Query OK, 1 row affected (0.00 sec)Xtrabackup全量備份與恢復
[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /root從備份過程截圖可以看出會建立一個時間的目錄
[root@centos ~]# ll /root/2017-12-04_13-57-29/
total 12352
-rw-r----- 1 root root 425 Dec 4 13:57 backup-my.cnf
-rw-r----- 1 root root 322 Dec 4 13:57 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec 4 13:57 ibdata1
drwxr-x--- 2 root root 4096 Dec 4 13:57 mysql
drwxr-x--- 2 root root 4096 Dec 4 13:57 performance_schema
drwxr-x--- 2 root root 12288 Dec 4 13:57 sys
drwxr-x--- 2 root root 4096 Dec 4 13:57 test
-rw-r----- 1 root root 22 Dec 4 13:57 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Dec 4 13:57 xtrabackup_checkpoints
-rw-r----- 1 root root 537 Dec 4 13:57 xtrabackup_info
-rw-r----- 1 root root 2560 Dec 4 13:57 xtrabackup_logfile這裡面就是相關的備份檔案，同樣也可以看到我們建立的庫的名稱
[root@centos ~]#innobackupex --apply-log /root/2017-12-04_13-57-29/使用此引數使用相關資料性檔案保持一致性狀態
mysql> drop table T1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from T1;
ERROR 1146 (42S02): Table 'test.T1' doesn't exist接下來準備恢復誤刪除資料
恢復資料之前需要保證資料目錄是空的狀態
[root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/具體看日誌截圖
[root@centos ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@centos ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 5935 mysql 21u IPv6 21850 0t0 TCP *:mysql (LISTEN)
mysql> use test;
Database changed
mysql> select * from T1;
+-------+-------+
| name | sex |
+-------+-------+
| zhang | man |
| zhan | man |
| sun | woman |
+-------+-------+
3 rows in set (0.00 sec)恢復成功
Xtrabackup增量備份與恢復
需要注意的是，增量備份僅能應用於InooDB或XtraDB表，對於MyISAM表，增量與全備相同
mysql> select * from T1;
+-------+-------+
| name | sex |
+-------+-------+
| zhang | man |
| zhan | man |
| sun | woman |
| susun | woman |
| sige | man |
| mgg | man |
+-------+-------+
6 rows in set (0.00 sec)建立用於增量備份的資料，用來模擬刪除掉了全備後的資料，能否透過增量備份檔案來恢復
[root@Vcentos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/2017-12-04_13-57-29
#--incremental /backup/ 指定增量備份檔案備份的目錄
#--incremental-basedir 指定上一次全備或增量備份的目錄[root@Vcentos ~]# ll /backup/2017-12-05_09-27-06/
total 312
-rw-r----- 1 root root 425 Dec 5 09:27 backup-my.cnf
-rw-r----- 1 root root 412 Dec 5 09:27 ib_buffer_pool
-rw-r----- 1 root root 262144 Dec 5 09:27 ibdata1.delta
-rw-r----- 1 root root 44 Dec 5 09:27 ibdata1.meta
drwxr-x--- 2 root root 4096 Dec 5 09:27 mysql
drwxr-x--- 2 root root 4096 Dec 5 09:27 performance_schema
drwxr-x--- 2 root root 12288 Dec 5 09:27 sys
drwxr-x--- 2 root root 4096 Dec 5 09:27 test
-rw-r----- 1 root root 21 Dec 5 09:27 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Dec 5 09:27 xtrabackup_checkpoints
-rw-r----- 1 root root 560 Dec 5 09:27 xtrabackup_info
-rw-r----- 1 root root 2560 Dec 5 09:27 xtrabackup_logfile
[root@centos ~]# cd /backup/2017-12-05_09-27-06/
[root@centos 2017-12-05_09-27-06]# cat xtrabackup_binlog_info
mysql-bin.000001 945
[root@centos 2017-12-05_09-27-06]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2542843
to_lsn = 2547308
last_lsn = 2547317
compact = 0
recover_binlog_info = 0刪除一條資料來測試增量恢復
mysql> delete from T1 where name='susun';
Query OK, 1 row affected (0.06 sec)增量恢復操作過程如下
[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/
[root@centos ~]# innobackupex --apply-log --redo-only /root/2017-12-04_13-57-29/ --incremental-dir=/backup/2017-12-05_09-27-06/恢復全部資料
[root@centos ~]#innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2017-12-04_13-57-29/
[root@centos ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
[root@centos ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 23217 mysql 21u IPv6 283226 0t0 TCP *:mysql (LISTEN)檢視恢復的資料完整性
[root@VM_0_8_centos ~]#mysql -uroot -p -e "select * from test.T1;"引擎（Engine）是電子平臺上開發程式或系統的核心元件。利用引擎，開發者可迅速建立、鋪設程式所需的功能，或利用其輔助程式的運轉。一般而言，引擎是一個程式或一套系統的支援部分。常見的程式引擎有遊戲引擎，搜尋引擎，防毒引擎等。
- Ok，我們知道了，引擎就是一個程式的核心元件。
- 簡單來說，儲存引擎就是指表的型別以及表在計算機上的儲存方式。
- 儲存引擎的概念是MySQL的特點，Oracle中沒有專門的儲存引擎的概念，Oracle有OLTP和OLAP模式的區分。不同的儲存引擎決定了MySQL資料庫中的表可以用不同的方式來儲存。我們可以根據資料的特點來選擇不同的儲存引擎。
- 在MySQL中的儲存引擎有很多種，可以透過“SHOW ENGINES”語句來檢視。下面重點關注InnoDB、MyISAM、MEMORY這三種。
InnoDB儲存引擎
- InnoDB給MySQL的表提供了事務處理、回滾、崩潰修復能力和多版本併發控制的事務安全。在MySQL從3.23.34a開始包含InnnoDB。它是MySQL上第一個提供外來鍵約束的表引擎。而且InnoDB對事務處理的能力，也是其他儲存引擎不能比擬的。靠後版本的MySQL的預設儲存引擎就是InnoDB。
- InnoDB儲存引擎總支援AUTO_INCREMENT。自動增長列的值不能為空，並且值必須唯一。MySQL中規定自增列必須為主鍵。在插入值的時候，如果自動增長列不輸入值，則插入的值為自動增長後的值；如果輸入的值為0或空（NULL），則插入的值也是自動增長後的值；如果插入某個確定的值，且該值在前面沒有出現過，就可以直接插入。
- InnoDB還支援外來鍵（FOREIGN KEY）。外來鍵所在的表叫做子表，外來鍵所依賴（REFERENCES）的表叫做父表。父表中被字表外來鍵關聯的欄位必須為主鍵。當刪除、更新父表中的某條資訊時，子表也必須有相應的改變，這是資料庫的參照完整性規則。
- InnoDB中，建立的表的表結構儲存在.frm檔案中（我覺得是frame的縮寫吧）。資料和索引儲存在innodb_data_home_dir和innodb_data_file_path定義的表空間中。
- InnoDB的優勢在於提供了良好的事務處理、崩潰修復能力和併發控制。缺點是讀寫效率較差，佔用的資料空間相對較大。
MyISAM儲存引擎
- MyISAM 是 MySQL 中常見的儲存引擎，曾經是 MySQL的預設儲存引擎。 MyISAM 是基於 ISAM 引擎發展起來的，增加了許多有用的擴充套件。
- MyISAM 的表儲存成3個檔案。檔案的名字與表名相同。擴充名為 frm 、 MYD 、 MYI 。其實，frm 檔案儲存表的結構；MYD 檔案儲存資料，是 MYData 的縮寫； MYI 檔案儲存索引，是 MYIndex 的縮寫。
- 基於 MyISAM 儲存引擎的表支援3種不同的儲存格式。包括靜態型、動態型和壓縮型。其中，靜態型是 MyISAM 的預設儲存格式，它的欄位是固定長度的；動態型包含變長欄位，記錄的長度不是固定的；壓縮型需要用到 myisampack 工具，佔用的磁碟空間較小。
- MyISAM 的優勢在於佔用空間小，處理速度快。缺點是不支援事務的完整性和併發性。
MEMORY儲存引擎
- MEMORY 是 MySQL 中一類特殊的儲存引擎。它使用儲存在記憶體中的內容來建立表，而且資料全部放在記憶體中。這些特性與前面的兩個很不同。
- 每個基於 MEMORY 儲存引擎的表實際對應一個磁碟檔案。該檔案的檔名與表名相同，型別為 frm 型別。該檔案中只儲存表的結構。而其資料檔案，都是儲存在記憶體中，這樣有利於資料的快速處理，提高整個表的效率。值得注意的是，伺服器需要有足夠的記憶體來維持 MEMORY 儲存引擎的表的使用。如果不需要了，可以釋放記憶體，甚至刪除不需要的表。
- MEMORY 預設使用雜湊索引。速度比使用B型樹索引快。當然如果你想用B型樹索引，可以在建立索引時指定。
- 注意，MEMORY 用到的很少，因為它是把資料存到記憶體中，如果記憶體出現異常就會影響資料。如果重啟或者關機，所有資料都會消失。因此，基於 MEMORY 的表的生命週期很短，一般是一次性的。
怎樣選擇儲存引擎
- InnoDB：支援事務處理，支援外來鍵，支援崩潰修復能力和併發控制。如果需要對事務的完整性要求比較高（比如銀行），要求實現併發控制（比如售票），那選擇 InnoDB 有很大的優勢。如果需要頻繁的更新、刪除操作的資料庫，也可以選擇 InnoDB ，因為支援事務的提交（ commit ）和回滾（ rollback ）。
- MyISAM：插入資料快，空間和記憶體使用比較低。如果表主要是用於插入新記錄和讀出記錄，那麼選擇 MyISAM 能實現處理高效率。如果應用的完整性、併發性要求比 較低，也可以使用。
- MEMORY：所有的資料都在記憶體中，資料的處理速度快，但是安全性不高。如果需要很快的讀寫速度，對資料的安全性要求較低，可以選擇 MEMOEY。它對錶的大小有要求，不能建立太大的表。所以，這類資料庫只使用在相對較小的資料庫表。
注意，同一個資料庫也可以使用多種儲存引擎的表。如果一個表要求比較高的事務處理，可以選擇 InnoDB。這個資料庫中可以將查詢要求比較高的表選擇 MyISAM 儲存。如果該資料庫需要一個用於查詢的臨時表，可以選擇 MEMORY 儲存引擎。
MySQL中的日誌包括：錯誤日誌、二進位制日誌、通用查詢日誌、慢查詢日誌等等。這裡主要介紹下比較常用的兩個功能：通用查詢日誌和慢查詢日誌。
- 通用查詢日誌：記錄建立的客戶端連線和執行的語句。
- 慢查詢日誌：記錄所有執行時間超過long_query_time秒的所有查詢或者不使用索引的查詢
通用查詢日誌
常用命令：
show variables like '%version%';show variables like ‘%general%’;show variables like ‘%log_output%’;檢視當前慢查詢日誌輸出的格式，可以是 FILE（儲存在數資料庫的資料檔案中的 hostname.log ），也可以是 TABLE（儲存在資料庫中的 mysql.general_log ）
如何開啟MySQL通用查詢日誌，以及如何設定要輸出的通用日誌輸出格式呢？
- 開啟通用日誌查詢： set global general_log=on;
- 關閉通用日誌查詢： set global general_log=off;
- 設定通用日誌輸出為表方式： set global log_output=’TABLE’;
- 設定通用日誌輸出為檔案方式： set global log_output=’FILE’;
- 設定通用日誌輸出為表和檔案方式：set global log_output=’FILE,TABLE’;
- 注意：上述命令只對當前生效，當MySQL重啟失效，如果要永久生效，需要配置my.cnf
日誌輸出的效果圖如下：
記錄到mysql.general_log表結構如下：
mysql> desc general_log;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| event_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host | mediumtext | NO | | NULL | |
| thread_id | bigint(21) unsigned | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| command_type | varchar(64) | NO | | NULL | |
| argument | mediumtext | NO | | NULL | |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)my.cnf檔案的配置如下：
general_log=1 #為1表示開啟通用日誌查詢，值為0表示關閉通用日誌查詢
log_output=FILE,TABLE#設定通用日誌的輸出格式為檔案和表慢查詢日誌
- MySQL 的慢查詢日誌是MySQL提供的一種日誌記錄，用來記錄在 MySQL 中響應時間超過閾值的語句，具體指執行時間超過 long_query_time 值的 SQL ，則會被記錄到慢查詢日誌中（日誌可以寫入檔案或者資料庫表，如果對效能要求高的話，建議寫檔案）。預設情況下， MySQL 資料庫是不開啟慢查詢日誌的，long_query_time 的預設值為10（即10秒，通常設定為1秒），即執行10秒以上的語句是慢查詢語句。
- 一般來說，慢查詢發生在大表（比如：一個表的資料量有幾百萬），且查詢條件的欄位沒有建立索引，此時，要匹配查詢條件的欄位會進行全表掃描，耗時查過 long_query_time ，則為慢查詢語句。
檢視當前慢查詢日誌的開啟情況
在MySQL中輸入命令：show variables like '%quer%';
mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| log_queries_not_using_indexes | ON |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql_slow.log |
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)
#主要掌握以下的幾個引數：
（1）slow_query_log 的值為ON為開啟慢查詢日誌，OFF則為關閉慢查詢日誌。
（2）slow_query_log_file 的值是記錄的慢查詢日誌到檔案中（注意：預設名為主機名.log，慢查詢日誌是否寫入指定檔案中，需要指定慢查詢的輸出日誌格式為檔案，相關命令為：show variables like ‘%log_output%’；去檢視輸出的格式）。
（3）long_query_time 指定了慢查詢的閾值，即如果執行語句的時間超過該閾值則為慢查詢語句，預設值為10秒。
（4）log_queries_not_using_indexes 如果值設定為ON，則會記錄所有沒有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設定為ON，而將slow_query_log設定為OFF，此時該設定也不會生效，即該設定生效的前提是slow_query_log的值設定為ON），一般在效能調優的時候會暫時開啟。設定MySQL慢查詢的輸出日誌格式為檔案還是表，或者兩者都有？
透過命令：show variables like ‘%log_output%’;
mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)透過log_output的值可以檢視到輸出的格式，上面的值為FILE,TABLE。當然，我們也可以設定輸出的格式為文字，或者同時記錄文字和資料庫表中，設定的命令如下：
#慢查詢日誌輸出到表中（即mysql.slow_log）
set globallog_output=’TABLE’;
#慢查詢日誌僅輸出到文字中(即：slow_query_log_file指定的檔案)
setglobal log_output=’FILE’;
#慢查詢日誌同時輸出到文字和表中
setglobal log_output=’FILE,TABLE’; 關於慢查詢日誌的表中的資料個文字中的資料格式分析：
慢查詢的日誌記錄myql.slow_log表中
mysql> mysql> select * from mysql.slow_log limit 1;
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
| 2018-02-07 11:16:55 | root[root] @ [121.196.203.51] | 00:00:00 | 00:00:00 | 13 | 40 | jp_core_db | 0 | 0 | 0 | select pd.lastAuction from Product pd where pd.status = 'O' and pd.auctionStatus = 'A' | 1621 |
+---------------------+--------------------------------+------------+-----------+-----------+---------------+------------+----------------+-----------+-----------+----------------------------------------------------------------------------------------+-----------+
1 row in set (0.00 sec)慢查詢的日誌記錄到 mysql_slow.log 檔案中
# Time: 180118 14:58:37
# User@Host: root[root] @ localhost [] Id: 150
# Query_time: 0.000270 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 6
SET timestamp=1516258717;
delete from user where User='app';
#可以看到，不管是表還是檔案，都具體記錄了：是那條語句導致慢查詢（sql_text），該慢查詢語句的查詢時間（query_time），鎖表時間（Lock_time），以及掃描過的行數（rows_examined）等資訊。如何查詢當前慢查詢的語句的個數？
在 MySQL 中有一個變數專門記錄當前慢查詢語句的個數：
輸入命令：show global status like ‘%slow%’;
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 132 |
| Slow_queries | 1772 |
+---------------------+-------+
2 rows in set (0.00 sec)
（注意：上述所有命令，如果都是透過MySQL的shell將引數設定進去，如果重啟MySQL，所有設定好的引數將失效，如果想要永久的生效，需要將配置引數寫入my.cnf檔案中）。如何利用MySQL自帶的慢查詢日誌分析工具mysqldumpslow分析日誌？
mysqldumpslow –s c –t 10 slow-query.log具體引數設定如下：
- -s 表示按何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
- -t 表示top的意思，後面跟著的資料表示返回前面多少條；
- -g 後面可以寫正規表示式匹配，大小寫不敏感。
[root@nginx-test /var/log/mysql]# mysqldumpslow -s c -t 2 /var/log/mysql/mysql_slow.log
Reading mysql slow query log from /var/log/mysql/mysql_slow.log
Count: 125448 Time=0.00s (131s) Lock=0.00s (3s) Rows=2.2 (272835), 2users@2hosts
select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionIndex as auctionI2_12_, productauc0_.bidCoins as bidCoins3_12_, productauc0_.bidPrice as bidPrice4_12_, productauc0_.bidStep as bidStep5_12_, productauc0_.bidTime as bidTime6_12_, productauc0_.bidder as bidder7_12_, productauc0_.buyFlag as buyFlag8_12_, productauc0_.categoryCode as category9_12_, productauc0_.createTime as createT10_12_, productauc0_.currentAuctionDetailId as current11_12_, productauc0_.currentBidPrice as current12_12_, productauc0_.currentBidTime as current13_12_, productauc0_.currentBidder as current14_12_, productauc0_.effectCoin as effectC15_12_, productauc0_.effetcPoint as effetcP16_12_, productauc0_.endTime as endTime17_12_, productauc0_.newUserFlag as newUser18_12_, productauc0_.productCode as product19_12_, productauc0_.productCost as product20_12_, productauc0_.productName as product21_12_, productauc0_.productPrice as product22_12_, productauc0_.refundRate as refundR23_12_, productauc0_.startPrice as startPr24_12_, productauc0_.startTime as startTi25_12_, productauc0_.status as status26_12_, productauc0_.updateTime as updateT27_12_ from ProductAuction productauc0_ where productauc0_.status='S'
Count: 66216 Time=0.00s (127s) Lock=0.00s (2s) Rows=1.7 (115074), root[root]@[121.196.203.51]
select productauc0_.productAuctionId as productA1_12_, productauc0_.auctionInd上述中的引數含義如下：
Count:125448 #語句出現了125448次；
Time=0.00s（131s） #執行最長時間為0.00s，累計總耗費時間131s；
Lock=0.0s（3s） #等待鎖最長時間為0s，累計等待鎖耗費時間為3s；
Rows=2.2（272835） #傳送給客戶端最多的行數為2.2，累計傳送給客戶端的函式為272835如何得知設定的慢查詢是有效的？
- 很簡單，我們可以手動產生一條慢查詢語句，比如，如果我們的慢查詢 log_query_time 的值設定為1，則我們可以執行如下語句：
- select sleep(1) ;
- 該條語句即是慢查詢語句，之後，便可以在相應的日誌輸出檔案或表中去檢視是否有該條語句。
Mysql的使用非常普遍，跟mysql有關的話題也非常多，如效能最佳化、高可用性、強一致性、安全、備份、叢集、橫向擴充套件、縱向擴充套件、負載均衡、讀寫分離等。要想掌握其中的精髓，可得花費不少功力，雖然目前流行的mysql替代方案有很多，可是從最小成本最容易維護的角度而言，mysql還是首選。大致分為了六種
單Master
- 單 Master 的情況是普遍存在的，對於很多個人站點、初創公司、小型內部系統，考慮到成本、更新頻率、系統重要性等問題，系統只依賴一個單例資料庫提供服務，基本上已經滿足需求。這種場景下我覺得重點應該關注的話題有上圖所示的四點。
- 其中最重要的環節是資料備份，如果是交易量非常低，並且具有非常明確的服務時間段特性的話，簡單的 mysqldump 是可以勝任的。但是這是有缺陷的，資料還原之後註定從備份點到還原點之間的資料會丟失。然而在極多數的情況下，備份的工作是沒法馬虎的，如下列舉的幾點小細節】
- 1）冷備：停機，直接copy物理檔案，InnoDB引擎（frm檔案、共享表空間檔案、獨立表空間檔案、重做日誌檔案、my.cnf）。
- 恢復：把檔案copy到對應目錄。
- 2）熱備： Ibbackup或者XtraBackup工具，記錄重做日誌檔案檢查點的LSN，copy共享表空間檔案以及獨立表空間檔案（不產生任何阻塞），記錄copy後重做日誌檔案檢查點的LSN，copy備份是產生的重做日誌。
- 恢復：恢復表空間檔案，應用重做日誌檔案。
- 3）溫備：-mysqldump，--single-transaction引數進行事務管理保證資料一致性。備份時不能用DDL語句。 恢復：直接執行檔案，mysql –uroot –p <檔名.sql> 二進位制半同步複製，主從伺服器增量複製
- 恢復：mysqlbinlog
一主一從
- 考慮一主一從的多數初衷是系統效能和系統高可用性問題，除了單Master場景中的備份工作需要做好以外，還有效能最佳化、讀寫分離、負載均衡三項重點工作需要考慮。其中效能最佳化的內容比較多，也是一塊大主題，要從系統的服務指標作為依據採取相應的動作，多數系統要求的是3秒內完成請求，總體換算下來，資料庫大概可以有1.5秒的總執行時間，能滿足這個效能要求就是合理的最佳化方案
- 讀寫分離和負載均衡的實現相對簡單些，我目前維護的系統比較落後，沒有做讀寫分離，因為是一套以報表類功能為主的系統，而負載均衡是依賴php程式碼來做的，從實際運維效果來看，不大理想，而且負載均衡的程式碼過分嵌入到業務邏輯程式碼中，給程式碼維護帶來一定噪音
一主 n 從
- 一旦開始考慮一主多從的伺服器架構，則證明你的系統對可用性、一致性、效能中一種或者多種的要求比較高。好多系統在開始搭建的時候都會往這個方向看齊，畢竟這樣“看起來”系統會健壯很多。不過其實並不能單單依靠mysql的配置和mysql自帶的中介軟體來解決可用性、一致性方面的問題。
橫向叢集
- 系統龐大到需要分庫分表，其實是一件可喜可賀的事情，但是切記的是要前面提到效能最佳化工作做到極致之後才好考慮這些會增加系統複雜度的解決方案。橫向叢集主要是從業務特性的角度對系統進行切分，最徹底就是切分成了各個子系統，子系統之間透過一些資料同步的方案來把一些核心資料進行共享，以避免跨庫呼叫跨庫join。
- 然後是各種系統介面呼叫，把大事務拆成小事務，事務之間做好隔離和同步。上圖中的三個問題在橫向叢集的架構體系中應屬於很有特色的問題，在實際專案中其實是儘量去避免這些需求的存在的，不過如果確實需要了，也得有解決方案。
縱向叢集
- 橫向叢集的切分思路最終是切分子系統，而縱向叢集最後遇到的最棘手的問題是擴縮容，我運維的一個系統是提前對資料做了256個切片，256切片中0~127切片和128~255切片分別存在兩個一主兩從的資料庫叢集中，系統運維了3年多，目前還沒有擴容需求。設計初衷應該是考慮得到，假設有一天資料量非常大，可以把256個切片分4大片，分別儲存到4個一主兩從的叢集中，從而實現擴容。
- 這個思路的確是可取的，只是我們的分庫邏輯當前是php程式碼實現，也有一定程度上影響了業務程式碼的邏輯，運維起來有點心驚膽戰，還是保持業務程式碼清爽比較好。
混合模式
與其說這部分內容討論上面5種場景的混合，不如說這部分內容是做總結。
儘量少用負向條件查詢
假設我們有一個Order表，表中有一個欄位是Status，這個欄位有4個值，分別是0=待支付、1=待發貨、2=待收貨、3=已完成。
這時，我們要查詢所有已經支付的訂單，很多人就會寫這樣的SQL：
select * from Order where Status != 0這就是一個不好的習慣了。負向條件查詢（例如：!=、not in、not exists）都是不能使用索引的，當Order表中的資料到達一定量級時，這個查詢的效率會急劇的下降。
正確的寫法應該是：
select * from Order where Status in (1,2,3)儘量少用前導模糊查詢
假設我們現在要根據使用者的訂單號（OrderNo）查詢使用者的訂單，如果是直接透過SQL查詢的話，儘量不要使用前導模糊查詢，也就是：
select * from Order where OrderNo like '%param'select * from Order where OrderNo like '%param%'因為，前導模糊查詢是無法命中索引的，所以，會整個資料庫去檢索，效率相當的差，而非前導模糊查詢則是可以使用索引的。
因此，我們儘量不要把萬用字元放在前面，改成下面這樣：
select * from Order where OrderNo like 'param%'儘量不要在條件欄位上進行運算
假設，現在有一個需求，是要查詢2018年全年的訂單資料，我們就需要透過建立時間（CreateTime）來進行檢索，但是，有些程式設計師就喜歡這樣寫SQL：
select * from Order where Year(CreateTime)=2019然後，每次執行時就會發現，查詢的速度異常的慢，導致了大量的請求掛起甚至超時。這是因為，我們即使在CreateTime上建立了索引，但是，如果使用了運算函式，查詢一樣會進行全表的檢索。
所以，我們可以改成這樣
select * from Order where CreateTime > '2019-1-1 00:00:00'當查詢允許Null值的列時，需要特別注意
我們在建立表的欄位時，如果這個欄位需要作為索引時，儘量不要允許Null。因為，單列索引不會存Null值，複合索引不存所有索引列都為Null的值，所以如果列允許為Null，可能會得到“不符合預期”的結果集。
複合索引，使用時要注意順序
登入，肯定是我們使用得最多的一個查詢了，為了保證效率，我們為LoginID和Password加上了複合索引。
當我們使用
select * from User where LoginID = '{LoginID}' and Password = '{Password}'
select * from User where Password = '{Password}' and LoginID = '{LoginID}查詢時，都是能夠準備的命中索引。當我們使用：
select * from User where LoginID = '{LoginID}' 查詢時，也是能夠命中索引的。但是，當我們使用
select * from User where Password = '{Password}' 查詢時，確無法命中索引，這是什麼原因呢？
- 這是由於，複合索引對於查詢的順序是非常的銘感的，所以，符合索引中包含了幾種規則，其中就有全列匹配和最左字首匹配。
- 當所有列都能夠匹配時，雖然查詢的順序上有不同，但是查詢最佳化器會將順序進行調整，以滿足適合索引的順序，所以，順序的顛倒是沒有問題的。
- 但是，如果所有列不能匹配時，就必須滿足最左字首匹配了，也就是，必須按照從左到右的順序進行排列。因此，當我們建立是索引是<LoginID, Password>時，where Password = '{Password}' 就不滿足最左字首規則，無法命中索引了。
結果唯一時
通常，我們設計User表時，並不會把LoginID作為主鍵，但是，LoginID確會在業務邏輯中驗證唯一性，因此，如果使用
select * from User where LoginID = '{LoginID}'查詢時，結果一定只有一條。但是，資料庫是不知道的，即使找到了這唯一的一條結果，他也會一直繼續，直到掃描完所有的資料。
因此，在執行這樣的查詢時，我們可以最佳化一下，改成：
select * from User where LoginID = '{LoginID}' limit 1這樣，當查詢到結果時，就不會再繼續了。
大概分為幾種
- 誤刪檔案
- 誤刪庫、表
- 錯誤全表刪除 / 更新
- 升級操作失誤
幾點我平時預防誤操作導致檔案/資料丟失不成熟的建議：
- 1.欲刪除檔案時，將rm命令改成mv，可在系統層面將rm命令做個alias（或參考 Windows / Mac OSX做法，刪除檔案時先進回收站）。
- 刪除資料庫、表時，不要用drop命令，而是rename到一個專用歸檔庫裡；
- 2.刪除表中資料時，不要直接用delete或truncate命令，尤其是truncate命令，目前不支援事務，無法回滾。
- 3.用delete命令刪除資料時，應當先顯式開啟事務，這樣誤操作時，還有機會進行回滾。
- 4.要大批次刪除資料時，可以將這些資料insert...select到一個新表，確認無誤後再刪除。或者反其道行之，把要保留的資料寫到新表，然後將表重新命名對掉。
- 5.執行重要命令之前，先準備好相關命令，再三確認無誤才之行，對於新鳥而言，最好請你的boss坐你旁邊鎮場幾次，否則極有可能會連累大家～
注: 一定要做好備份，不管是物理備份還是邏輯備份！
萬一發生誤操作時，怎麼以最快速度進行補救
- 執行DROP DATABASE / DROP TABLE命令誤刪庫表，如果碰巧採用共享表空間模式的話，還有恢復的機會。如果沒有，請直接從備份檔案恢復吧。神馬，你連備份檔案都沒有？那麻煩退出DBA屆吧，一個連備份都懶得做的人，不配成為DBA的。
- 接上，採用共享表空間模式下，誤刪後立刻殺掉（kill -9）mysql相關程式（mysqld_safe、mysqld），然後嘗試從ibdataX檔案中恢復資料。
- 誤刪除正在執行中的MySQL表ibd或ibdataX檔案。請立即申請對該例項進行維護，當然，不是指把例項關閉，而是把業務暫停，或者把該例項從線上環境摘除，不再寫入新資料，然後利用linux系統的proc檔案特點，把該ibd檔案從記憶體中拷出來，再進行恢復，因為此時mysqld例項在記憶體中是保持開啟該檔案的，切記這時不要把mysqld例項關閉了。
- 接上，把複製出來的ibdataX或ibd檔案複製回datadir後，重啟mysqld進入recovery模式，innodb_force_recovery 選項從 0 - 6 逐級測試，直至能備份出（整個例項或單表的）所有資料後，再重建例項（或單表），恢復資料。
- 未開啟事務模式下，執行delete誤刪資料。意識到後立即將mysqld（以及mysqld_safe）程式殺掉（kill -9），不要任何猶豫，然後再用工具將表空間資料讀取出來。因為執行delete刪除後，實際資料並沒被物理清除，只是先打上deleted-mark標籤，後續再統一清理，因此還有時間差。
- 執行truncate誤清整表。如果沒使用共享表空間模式的話，基本別想了，走備份恢復+binlog吧。
- 執行不帶where條件的update，或者update錯資料。也別費勁了，走備份恢復+binlog吧。
Too many connections（連線數過多，導致連線不上資料庫，業務無法正常進行）
問題還原
mysql> show variables like '%max_connection%';
| Variable_name | Value |
max_connections | 151 |
mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)
[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132
ERROR 1040 (00000): Too many connections解決問題的思路：
首先先要考慮在我們 MySQL 資料庫引數檔案裡面，對應的max_connections 這個引數值是不是設定的太小了，導致客戶端連線數超過了資料庫所承受的最大值。
- 該值預設大小是151，我們可以根據實際情況進行調整。
- 對應解決辦法：set global max_connections=500
但這樣調整會有隱患，因為我們無法確認資料庫是否可以承擔這麼大的連線壓力，就好比原來一個人只能吃一個饅頭，但現在卻非要讓他吃 10 個，他肯定接受不了。反應到伺服器上面，就有可能會出現當機的可能。
所以這又反應出了，我們在新上線一個業務系統的時候，要做好壓力測試。保證後期對資料庫進行最佳化調整。
其次可以限制Innodb 的併發處理數量，如果 innodb_thread_concurrency = 0（這種代表不受限制） 可以先改成 16或是64 看伺服器壓力。如果非常大，可以先改的小一點讓伺服器的壓力下來之後,然後再慢慢增大,根據自己的業務而定。個人建議可以先調整為 16 即可。
MySQL 隨著連線數的增加效能是會下降的，可以讓開發配合設定 thread pool，連線複用。在MySQL商業版中加入了thread pool這項功能,另外對於有的監控程式會讀取 information_schema 下面的表，可以考慮關閉下面的引數
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0主從複製報錯型別
Last_SQL_Errno: 1062 （從庫與主庫資料衝突）
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.t;
Duplicate entry '4' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log mysql-bin.000014, end_log_pos 1505針對這個報錯，我們首先要考慮是不是在從庫中誤操作導致的。結果發現，我們在從庫中進行了一條針對有主鍵表的 sql 語句的插入，導致主庫再插入相同 sql 的時候，主從狀態出現異常。發生主鍵衝突的報錯。
解決方法：
- 在確保主從資料一致性的前提下，可以在從庫進行錯誤跳過。一般使用 percona-toolkit 中的 pt-slave-restart 進行。在從庫完成如下操作
[root@zs bin]# ./pt-slave-restart -uroot -proot123 2017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002 1506 1062- 之後最好在從庫中開啟 read_only 引數，禁止在從庫進行寫入操作
Last_IO_Errno: 1593（server-id衝突）
Last_IO_Error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work
(or the --replicate-same-server-id option must be used on slave but this
does not always make sense; please check the manual before using it)
#這個報錯出現之後，就看一目瞭然看到兩臺機器的 server-id 是一樣的。在搭建主從複製的過程中，我們要確保兩臺機器的 server-id 是唯一的。這裡再強調一下 server-id 的命名規則（伺服器 ip 地址的最後一位+本 MySQL 服務的埠號）
解決方法:
- 在主從兩臺機器上設定不同的 server-id。
Last_SQL_Errno: 1032（從庫少資料，主庫更新的時候，從庫報錯）
Last_SQL_Error:
Could not execute Update_rows event on table test.t; Can't find record
in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the
event's master log mysql-bin.000014, end_log_pos 1708解決問題的辦法：
根據報錯資訊，我們可以獲取到報錯日誌和position號，然後就能找到主庫執行的哪條sql，導致的主從報錯。
在主庫執行：
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log
cat 1.log
#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F
### UPDATE `test`.`t`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='dd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='ddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1708
#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid = 654
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;獲取到 sql 語句之後，就可以在從庫反向執行 sql 語句。把從庫缺少的 sql 語句補全，解決報錯資訊。
在從庫依次執行：mysql> insert into t (b) values ('ddd'); Query OK, 1 row affected (0.01 sec) mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@node4 bin]# ./pt-slave-restart -uroot -proot123 2017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005 283 1032MySQL安裝過程中的報錯
[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 3758 [root@zs data]# 170720 14:41:24 mysqld_safe Logging to '/data/mysql/error.log'. 170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720 14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended 170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20 14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)./usr/local/mysql/bin/mysqld: File '/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied) 2017-07-20 14:41:25 4388 [ERROR] Aborting解決思路：
遇到這樣的報錯資訊，我們要學會時時去關注錯誤日誌 error log 裡面的內容。看見了關鍵的報錯點 Permission denied。證明當前 MySQL 資料庫的資料目錄沒有許可權。
解決方法：
[root@zs data]# chown mysql:mysql -R mysql
[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 4402
[root@zs data]# 170720 14:45:56 mysqld_safe Logging to '/data/mysql/error.log'.
170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql
#啟動成功。如何避免這類問題，個人建議在安裝MySQL初始化的時候，一定加上--user=mysql，這樣就可以避免許可權問題。
./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysql資料庫密碼忘記的問題
[root@zs ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@zs ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
#我們有可能剛剛接手別人的 MySQL 資料庫，而且沒有完善的交接文件。root 密碼可以丟失或者忘記了。解決思路：
目前是進入不了資料庫的情況，所以我們要考慮是不是可以跳過許可權。因為在資料庫中，mysql資料庫中user表記錄著我們使用者的資訊。
解決方法：
啟動 MySQL 資料庫的過程中，可以這樣執行：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
這樣啟動，就可以不用輸入密碼，直接進入 mysql 資料庫了。然後在修改你自己想要改的root密碼即可。
update mysql.user set password=password('root123') where user='root';truncate 刪除資料，導致自動清空自增ID，前端返回報錯 not found。
這個問題的出現，就要考慮下truncate 和 delete 的區別了。
看下實驗演練：
#首先先建立一張表；
CREATE TABLE `t` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` varchar(20) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8
#插入三條資料：
mysql> insert into t (b) values ('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (b) values ('bb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (b) values ('cc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+-----+------+
| a | b |
+-----+------+
| 300 | aa |
| 301 | bb |
| 302 | cc |
+-----+------+
3 rows in set (0.00 sec)
#先用 delete 進行刪除全表資訊，再插入新值。- 結果發現truncate把自增初始值重置了，自增屬性從1開始記錄了。當前端用主鍵id進行查詢時，就會報沒有這條資料的錯誤。
- 個人建議不要使用truncate對錶進行刪除操作，雖然可以回收表空間，但是會涉及自增屬性問題。這些坑，我們不要輕易鑽進去。
阿里雲 MySQL 的配置檔案中，需要注意一個引數設定就是：
lower_case_table_names = 0;預設情況
lower_case_table_names = 1;是不區分大小寫 . 如果報你小寫的表名找不到, 那你就把遠端資料庫的表名改成小寫 , 反之亦然 . 注意 Mybatis 的 Mapper 檔案的所有表名也要相應修改資料庫總會出現中文亂碼的情況
解決思路：
對於中文亂碼的情況，記住老師告訴你的三個統一就可以。還要知道在目前的mysql資料庫中字符集編碼都是預設的UTF8
處理辦法：
1、資料終端，也就是我們連線資料庫的工具設定為 utf8
2、作業系統層面；可以透過 cat /etc/sysconfig/i18n 檢視；也要設定為 utf8
3、資料庫層面；在引數檔案中的 mysqld 下，加入 character-set-server=utf8。Emoji 表情符號錄入 mysql 資料庫中報錯
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)解決思路：針對表情插入的問題，一定還是字符集的問題。
處理方法：我們可以直接在引數檔案中，加入
vim /etc/my.cnf
[mysqld]
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
注：utf8mb4 是 utf8 的超集。使用 binlog_format=statement 這種格式，跨庫操作，導致從庫丟失資料，使用者訪問導致出現錯誤資料資訊。
#當前資料庫二進位制日誌的格式為：binlog_format=statement
在主庫設定binlog-do-db=mydb1（只同步mydb1這一個庫）
在主庫執行use mydb2；
insert into mydb1.t1 values ('bb');這條語句不會同步到從庫。
但是這樣操作就可以；
use mydb1;
insert into mydb1.t1 values ('bb');因為這是在同一個庫中完成的操作。
#在生產環境中建議使用binlog的格式為row，而且慎用binlog-do-db引數。MySQL 資料庫連線超時的報錯
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01
org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection 'autoReconnect=true' to avoid this problem.
org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch update
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003
org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
#大多數做 DBA 的同學，可能都會被開發人員告知，你們的資料庫報了這個錯誤了。趕緊看看是哪裡的問題。這個問題是由兩個引數影響的，wait_timeout 和 interactive_timeout。資料預設的配置時間是28800（8小時）意味著，超過這個時間之後，MySQL 資料庫為了節省資源，就會在資料庫端斷開這個連線，Mysql伺服器端將其斷開了，但是我們的程式再次使用這個連線時沒有做任何判斷，所以就掛了。
解決思路：
- 先要了解這兩個引數的特性；這兩個引數必須同時設定，而且必須要保證值一致才可以。
- 我們可以適當加大這個值，8小時太長了，不適用於生產環境。因為一個連線長時間不工作，還佔用我們的連線數，會消耗我們的系統資源。
解決方法：
- 可以適當在程式中做判斷；強烈建議在操作結束時更改應用程式邏輯以正確關閉連線；然後設定一個比較合理的timeout的值（根據業務情況來判斷）
can't open file (errno:24)
有的時候，資料庫跑得好好的，突然報不能開啟資料庫檔案的錯誤了。
解決思路：
- 首先我們要先檢視資料庫的error log。然後判斷是表損壞，還是許可權問題。還有可能磁碟空間不足導致的不能正常訪問表；作業系統的限制也要關注下；用 perror 工具檢視具體錯誤！
linux:/usr/local/mysql/bin # ./perror 24 OS error code 24: Too many open files - 超出最大開啟檔案數限制！ulimit -n檢視系統的最大開啟檔案數是65535，不可能超出！那必然是資料庫的最大開啟檔案數超出限制！
- 在 MySQL 裡檢視最大開啟檔案數限制命令：show variables like 'open_files_limit';
- 發現該數值過小，改為2048，重啟 MySQL，應用正常
處理方法：
repair table ；
chown mysql許可權
#清理磁碟中的垃圾資料關於儲存引擎
- 建立合適的索引是SQL效能調優中最重要的技術之一。在學習建立索引之前，要先了解MySql的架構細節，包括在硬碟上面如何組織的，索引和記憶體用法和操作方式，以及儲存引擎的差異如何影響到索引的選擇。
- MySQL 有很多種衍生版本，這些衍生版本支援更多不同種類的儲存引擎。本文主要討論三種MySQL引擎。
- MyISAM 一種非事務性的儲存引擎，是MySQL 5.5之前版本預設的儲存引擎。
- InnoDB 最流行的事務性儲存引擎，從5.5版開始成為MySQL預設的引擎。
- Memory 基於記憶體的，非事務性的以及非永續性的儲存引擎。
從5.5版本開始，MySQL表的預設儲存引擎從MyISAM換成InnoDB，將會使使用者安裝那些依賴預設設定或者專門為MyISAM編寫的軟體包時帶來很大的影響。
MySQL索引型別
- MySQL支援在所有關聯式資料庫表中建立主鍵、唯一鍵、不唯一的非主碼索引等多種型別的索引。此外MySQL還支援純文字和空間索引型別。
- MySQL內建的儲存引擎對各種索引技術有不同的實現方式，包括：B-樹，B+樹，R-樹以及雜湊型別。
- 索引資料結構理論：
- B-樹
- B-樹中有兩種節點型別：索引節點和葉子節點。葉子節點是用來儲存資料的，而索引節點則用來告訴使用者儲存在葉子節點中的資料順序，並幫助使用者找到相應的資料。
- B-樹的搜尋，從根節點開始，對節點內的關鍵字有序進行二分查詢，如果命中則結束，否則進入查詢關鍵字所屬範圍的兒子節點，重複。直到所對應的兒子指標為空，或已經是葉子節點。
- B-樹是一種多路搜尋樹：
- (1). 定義任意非葉子節點最多有M個兒子，且M>2;
- (2). 根節點的兒子數為[2,M];
- (3). 除根節點以外的非葉子節點的兒子數為[M/2,M];
- (4). 每個節點存放至少M/2-1(取上整)和至多M-1個關鍵字;
- (5). 非葉子節點的關鍵字個數=指向兒子節點的指標的個數-1;
- (6). 非葉子節點的關鍵字：k[i]<k[i+1];
- (7). 非葉子節點的指標：p[1]，p[2]，·····，p[M]；其中p[1]指向的關鍵字小於k[1]的子樹，p[M]指向的關鍵字大於K[m-1]的子樹;
- (8). 所有的葉子節點位於同一層;
- B+樹
- B+樹資料結構是B-樹實現的增強版本。儘管B+樹支援B-樹索引的所有特性，它們之間最顯著的不同點在於B+樹中底層資料是根據被提及的索引列進行排序的。B+樹還透過葉子節點之間的附加引用來最佳化掃描效能。
- B+搜尋和B-搜尋不同，區別是B+樹只有達到葉子節點才命中（B-樹可以在非葉子節點命中），其效能等價於關鍵字全集做一次二分搜尋。
- B+樹的特性：
- （1）所有關鍵字都出現在葉子節點的連結串列中，葉子節點相當於儲存資料的資料層。
- (2）不可能在非葉子節點上命中。
- （3）非葉子節點相當於是葉子節點的索引，葉子節點相當於資料層。
- 雜湊
- 雜湊表資料結構是一種很簡單的概念，它將一種演算法應用到給定值中以在底層資料儲存系統中返回一個唯一的指標或位置。雜湊表的優點是始終以線性時間複雜度找到需要讀取的行的位置，而不像B-樹那樣需要橫跨多層節點來確定位置。
- 通訊R-樹
- R-樹資料結構支援基於資料型別對幾何資料進行管理。目前只有MyISAM使用R-樹實現支援空間索引，使用空間索引也有很多限制，比如只支援唯一的NOT NULL列等。
- 全文字
- 全文字結構也是一種MySQL採用的基本資料結構。這種資料結構目前只有當前版本MySQL中的MyISAM儲存引擎支援。5.6版本將要在InnoDB儲存引擎中加入全文字功能。全文字索引在大型系統中並沒有什麼實用的價值，因為大規模系統有很多專門的檔案檢索產品。所以不用在介紹。
- B-樹
MySQL實現
對B-樹，B+樹和雜湊等資料結構的基本概念有了一些瞭解之後，我們就可以開始討論MySQL透過支援它們的儲存引擎如何實現不同的演算法。同時每種實現也對磁碟和記憶體使用情況有不同的影響，這一點在大型資料庫系統中是非常重要的考慮因素。
MyISAM的B-樹
MyISAM儲存引擎使用B-樹資料結構來實現主碼索引、唯一索引以及非主碼索引。在 MyISAM 實現資料目錄和資料庫模式子目錄中，使用者可以找到和每個MySQL表對應的.MYD和.MYI檔案。資料庫表上定義的索引資訊就儲存在MYI檔案中，該檔案的塊大小是1024位元組。這個大小是可以透過myisam-block-size系統變數分配。
$ ls -1h /var/lib/mysql/book/source_words.MY* -rw-rw---- 1 mysql mysql 9.2M 2015-05-07 19:08 source_words.MYD -rw-rw---- 1 mysql mysql 7.8M 2015-05-07 19:08 source_words.MYI
- 這些檔案結構的內部格式可以從MySQL免費原始碼中找到，也可以檢視MySQL內部手冊。
- 在MyISAM中，非主碼索引的B-樹結構儲存索引值和一個指向主碼資料的指標，這是MyISAM和InnoDB的一個顯著區別。這一點導致了兩個儲存引擎的索引的不同工作方式。
- MyISAM索引是在記憶體的一個公共快取中管理的，這個快取的大小可以透過key_buffer_size或者其他命名鍵快取來定義。這是根據統計和規劃的表索引的大小來設定快取大小時主要的考慮因素。
InnoDB的B+樹聚簇主碼
- InnoDB儲存引擎在它的主碼索引（也被稱為聚簇主碼）中使用了B+樹，這種結構把所有資料都和對應的主碼組織在一起，並且在葉子節點這一層上新增額外的向前和向後的指標，這樣就可以更方便地進行範圍掃描。
- 在檔案系統層面，所有InnoDB資料和索引資訊都預設在公共InnoDB表空間中管理，否則管理員就透過innodb_data_file_path這個變數指定檔案路徑。這是一個叫ibdatal檔案。
- 由於InnoDB用聚簇主碼儲存資料，底層資訊佔用的磁碟空間的大小很大程度上取決於頁面的填充因子。對於按序排列的主碼，InnoDB會用16K頁面的15/16作為填充因子。對於不是按序排列的主碼，預設情況下InnoDB會插入初始資料的時候為每一個頁面分配50%作為填充因子。
- 在改索引的實現方式中B+樹的葉子節點上是data就是資料本身，key為主鍵，如果是一般索引的話，data便會指向對應的主索引。在B+樹的每一個葉子節點上面增加一個指向相鄰葉子節點的指標，就形成了帶有順序訪問指標的B+樹。其目的是提高區間訪問的效能。
InnoDB的B-樹非主碼
InnoDB中的非主碼索引使用了B-樹資料結構，但InnoDB中的B-樹結構實現和MyISAM中並不一樣。在InnoDB中，非主碼索引儲存的是主碼的實際值。而MyISAM中，非主碼索引儲存的包含主碼值的資料指標。這一點很重要。首先，當定義很大的主碼的時候，InnoDB的非主碼索引可能回更大，隨著非主碼索引數量的增加，索引之間大小差別可能會變得很大。另一個不同點在於非主碼索引當前可以包含主鍵的值，並且可以不是索引必須有的部分。
記憶體雜湊索引
在預設MySQL的引擎索引中，只有MEMORY引擎支援雜湊資料結構，雜湊結構的強度可以表示為直接鍵查詢的簡單性，雜湊索引的相似度模式匹配查詢比直接查詢慢。也可以為MEMORY引擎指定一個B-樹索引實現。
記憶體B-樹索引
對於大型MEMORY表來說，使用雜湊索引進行索引範圍搜尋的效率很低，B-樹索引在執行直接鍵查詢時確實比使用預設的雜湊索引快。根據B-樹的不同深度，B-樹索引在個別操作中的確可能比雜湊演算法快。
InnoDB內部雜湊索引
InnoDB儲存引擎在聚簇B+樹索引中儲存主碼：但在InnoDB內部還是使用記憶體中的雜湊表來更高效地進行主碼查詢。這個機制有InnoDB儲存引擎來管理，使用者只能透過innodb_adaptive_hash_index配置項來選擇是否啟用這個唯一的配置選項。
感覺有點長了 ,還有個mysql連線池
本作品採用《CC 協議》，轉載必須註明作者和本文連結