MYSQL數據庫

MYSQL數據庫

數據的時代

?涉及的數據量大

?數據不隨程序的結束而消失

?數據被多個應用程序共享

?大數據

 

數據庫的發展史：

?萌芽階段—–文件系統：

使用磁盤文件來存儲數據

?初級階段—–第一代數據庫：

出現了網狀模型、層次模型的數據庫

?中級階段—–第二代數據庫：

關系型數據庫使用的是結構化查詢語言

?高級階段——新一代數據庫：

“關系-對象”型數據庫

 

 

文件管理系統的優點：

存儲時省事、方便

 

文件管理系統的缺點：

?編寫應用程序不方便

?數據冗余不可避免

?應用程序依賴性

?不支持對文件的并發訪問

?數據間聯系弱

?難以按用戶視圖表示數據

?無安全控制功能

 

 

數據庫管理系統（DBMS）的優點：

?相互關聯的數據的集合

?較少的數據冗余

?程序與數據相互獨立

?保證數據的安全、可靠

?最大限度地保證數據的正確性

?數據可以并發使用并能同時保證一致性

 

 

數據庫管理系統：

?數據庫是數據的匯集，它以一定的組織形式存于存儲介質上

?DBMS是管理數據庫的系統軟件，它實現數據庫系統的各種功能。是數據庫系統的核心

?DBA：負責數據庫的規劃、設計、協調、維護和管理等工作

?應用程序指以數據庫為基礎的應用程序

 

RAW文件系統：裸磁盤，沒有文件系統，即以二進制0101的方式進行存儲

 

數據庫管理系統的基本功能

?數據定義

?數據處理

?數據安全

?數據備份

 

數據庫系統的架構

?單機架構：數據庫在單一的主機上，訪問也只能在此單一主機上查詢

?大型主機/終端架構：multics

?主從式架構（C/S）：服務器與客戶端

?分布式架構：每個服務器存放一部分數據，多個服務器分擔

 

非關系型數據庫（NO SQL）：不是所有的服務器都是關系型數據庫

memcached? redis? mongodb

 

關系型數據庫：(數據穩定，安全但性能較差；NO SQL正好相反)

?關系 ：關系就是二維表。并滿足如下性質：

表中的行、列次序并不重要

?行row：表中的每一行，又稱為一條記錄（record）

?列column：表中的每一列，稱為屬性，字段

?主鍵（Primary key）：用于惟一確定一個記錄的字段

?域domain：屬性的取值范圍，如，性別只能是‘男’和‘女’兩個值

?RDBMS：

MySQL: MySQL, MariaDB, Percona Server

PostgreSQL: 簡稱為pgsql，EnterpriseDB

Oracle：

MSSQL：

DB2:

?事務transaction：多個操作被當作一個整體對待

ACID:

A: 原子性：不可分割

C：一致性

I: 隔離性

D：持久性

 

rollback? 回滾

 

 

實體-聯系模型E-R

?實體Entity

• 客觀存在并可以相互區分的客觀事物或抽象事件稱為實體。
• 在E-R圖中用矩形框表示實體，把實體名寫在框內

?屬性

• 實體所具有的特征或性質

 

?聯系

?聯系是數據之間的關聯集合，是客觀存在的應用語義鏈

• 實體內部的聯系：指組成實體的各屬性之間的聯系。如職工實體中，職工號和部門經理號之間有一種關聯關系
• 實體之間的聯系：指不同實體之間聯系。例學生選課實體和學生基本信息實體之間
• 實體之間的聯系用菱形框表示

 

聯系類型

?聯系的類型

?一對一聯系(1:1)

?一對多聯系(1:n)

?多對多聯系(m:n)

 

foreign key?? 外鍵??? 實現一對多聯系

 

 

數據三要素

?數據結構：

?????????? 一類是與數據類型、內容、性質有關的對象，比如關系模型中的域、屬性和關系等；

另一類是與數據之間聯系有關的對象，它從數據組織層表達數據記錄與字段的結構

?數據的操作：

?數據提?。涸跀祿现刑崛「信d趣的內容。（SELECT）

?數據更新：變更數據庫中的數據。（INSERT、DELETE、UPDATE）

?數據的約束條件 ：是一組完整性規則的集合

?實體（行）完整性 Entity integrity

?域（列）完整性 Domain Integrity

?參考完整性 Referential Integrity

 

主鍵必須有值，不能為? 空（null）

 

 

簡易數據規劃流程

?第一階段：收集數據，得到字段

• 收集必要且完整的數據項
• 轉換成數據表的字段

?第二階段：把字段分類，歸入表，建立表的關聯

• 關聯：表和表間的關系
• 分割數據表并建立關聯的優點
• 節省空間
• 減少輸入錯誤
• 方便數據修改

?第三階段：

• 規范化數據庫(范式):為了不至于出現重復的信息

 

 

數據庫的正規化分析

?RDMBS設計范式基礎概念

設計關系數據庫時，遵從不同的規范要求，設計出合理的關系型數據庫，這些不同的規范要求被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余越小

?目前關系數據庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎上進一步滿足更多規范要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數據庫只需滿足第三范式(3NF）即可

 

 

范式

?1NF：無重復的列，每一列都是不可分割的基本數據項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。除去同類型的字段，就是無重復的列

說明：第一范式（1NF）是對關系模式的基本要求，不滿足第一范式（1NF）的數據庫就不是關系數據庫

?2NF：屬性完全依賴于主鍵，第二范式必須先滿足第一范式，要求表中的每個行必須可以被唯一地區分。通常為表加上一個列，以存儲各個實例的唯一標識PK，非PK的字段需要與整個PK有直接相關性

?3NF：屬性不依賴于其它非主屬性，滿足第三范式必須先滿足第二范式。第三范式要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字信息，非PK的字段間不能有從屬關系

 

 

SQL概念

?SQL: Structure Query Language

結構化查詢語言

SQL解釋器：

數據存儲協議：應用層協議，C/S

?S：server, 監聽于套接字，接收并處理客戶端的應用請求

?C：Client

程序接口

CLI

GUI

應用編程接口

ODBC：Open Database Connectivity

JDBC：Java Data Base Connectivity

 

 

約束

?約束：constraint，表中的數據要遵守的限制

?主鍵：一個或多個字段的組合，填入的數據必須能在本表中唯一標識本行；必須提供數據，即NOT NULL，一個表只能有一個

?惟一鍵：一個或多個字段的組合，填入的數據必須能在本表中唯一標識本行；允許為NULL，一個表可以存在多個

?外鍵：一個表中的某字段可填入的數據取決于另一個表的主鍵或唯一鍵已有的數據

?檢查：字段值在一定范圍內

 

 

基本概念

?索引：將表中的一個或多個字段中的數據復制一份另存，并且此些需要按特定次序排序存儲

?關系運算：

選擇：挑選出符合條件的行

投影：挑選出需要的字段

連接：表間字段的關聯

 

 

數據模型

?數據抽象：

?物理層：數據存儲格式，即RDBMS在磁盤上如何組織文件

?邏輯層：DBA角度，描述存儲什么數據，以及數據間存在什么樣的關系

?視圖層：用戶角度，描述DB中的部分數據

?關系模型的分類：

?關系模型

?基于對象的關系模型

?半結構化的關系模型：XML（擴展的標記語言）數據

 

 

MySQL歷史

?1979年：TcX公司 Monty Widenius，Unireg

?1996年：發布MySQL1.0，Solaris版本，Linux版本

?1999年：MySQL AB公司，瑞典

?2003年：MySQL 5.0版本，提供視圖、存儲過程等功能

?2008年：sun 收購

?2009年：oracle收購sun

?2009年：Monty成立MariaDB

 

 

MySQL和MariaDB官方網址：

https://www.mysql.com/

MariaDB Foundation

?官方文檔

https://dev.mysql.com/doc/

https://mariadb.com/kb/en/

?版本演變：

MySQL：5.1 –> 5.5 –> 5.6 –> 5.7

MariaDB：5.5 –>10.0–> 10.1 –> 10.2 –> 10.3

 

 

MariaDB的特性

?插件式存儲引擎：也稱為“表類型”，存儲管理器有多種實現版本，功能和特性可能均略有差別；用戶可根據需要靈活選擇,Mysql5.5.5開始innoDB引擎是MYSQL默認引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

?單進程，多線程

?諸多擴展和新特性

?提供了較多測試組件

?開源

 

 

安裝Mariadb

?Mariadb安裝方式：

?1、源代碼：編譯安裝

?2、二進制格式的程序包：展開至特定路徑，并經過簡單配置后即可使用

?3、程序包管理器管理的程序包

?CentOS安裝光盤

?項目官方：

https://downloads.mariadb.org/mariadb/repositories/

 

centos6安裝mysql

yum? install? mysql-server???? 安裝服務器端的mysql

mysql

mysql> ?\h??????? 查看幫助

mysql>? show? databases；??????? 查看數據庫支持的列表

mysql>? use? myaql列表文件?????? 調用此文件

mysql>? select? user();?????????? 顯示當前登錄用戶

 

Rpm安裝mariadb

?安裝和使用MariaDB

?Rpm包安裝

CentOS 7：安裝光盤直接提供

mariadb-server 服務器包

mariadb 客戶端工具包

systemctl? start? mariadb???? 啟動服務

 

（一般默認都以root的身份登錄）

保證數據庫安全方法：

提高安全性的腳本：

mysql_secure_installation

設置數據庫管理員root口令

???????? 禁止root遠程登錄

???????? 刪除匿名（anonymous）用戶帳號

???????? 刪除測試（test）數據庫

是否使設置生效

 

 

MariaDB程序

客戶端程序：

mysql: 交互式的CLI工具

mysqldump: 備份工具，基于mysql協議向mysqld發起查詢請求，并將查得的所有數據轉換成insert等寫操作語句保存文本文件中

mysqladmin：基于mysql協議管理mysqld

mysqlimport: 數據導入工具

MyISAM存儲引擎的管理工具：（逐漸淘汰）（innodb現在使用）

myisamchk：檢查MyISAM庫

myisampack：打包MyISAM表，只讀

服務器端程序

mysqld_safe

mysqld 獲取默認設置：mysqld –print-defaults

mysqld_multi：多實例（一個機器上一個軟件跑多份） ，示例：mysqld_multi –example

 

 

用戶賬號

?mysql用戶賬號由兩部分組成：

‘USERNAME’@’HOST‘

?說明：

HOST限制此用戶可通過哪些遠程主機連接mysql服務器

支持使用通配符：

% 匹配任意長度的任意字符

172.16.0.0/255.255.0.0 或 172.16.%.%

_ 匹配任意單個字符

 

 

mysql使用模式：

?交互式模式：

可運行命令有兩類：

客戶端命令：

\h, help?????? 幫助

\u，use??????? 使用。。。

\s，status

\!，system ????查看。。。

服務器端命令：

SQL, 需要語句結束符；

?腳本模式：

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql

mysql> source /path/from/somefile.sql

 

 

?mysql客戶端可用選項：

-A, –no-auto-rehash 禁止補全

-u, –user= 用戶名,默認為root

-h, –host= 服務器主機,默認為localhost

-p, –passowrd= 用戶密碼,建議使用-p,默認為空密碼

-P, –port= 服務器端口

-S, –socket= 指定連接socket文件路徑

-D, –database= 指定默認數據庫

-C, –compress 啟用壓縮

-e “SQL“ 執行SQL命令

-V, –version 顯示版本

-v –verbose 顯示詳細信息

–print-defaults 獲取程序默認使用的配置

 

 

配置文件：

后面覆蓋前面的配置文件，順序如下：

?/etc/my.cnf Global選項

?/etc/mysql/my.cnf Global全局選項

?SYSCONFDIR/my.cnf Global選項

?$MYSQL_HOME/my.cnf Server-specific 選項

?–defaults-extra-file=path

?~/.my.cnf User-specific 選項

?獲取可用參數列表：

mysqld –help –verbose

 

 

MairaDB配置

?偵聽3306/tcp端口可以在綁定有一個或全部接口IP上

?vim /etc/my.cnf

[mysqld]

skip-networking=1

systemctl? start? mariadb關閉網絡連接，只偵聽本地客戶端， 所有和服務器的交互都通過一個socket實現，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改

 

 

執行命令

?運行mysql命令：默認空密碼登錄

mysql>use mysql

mysql>select user();查看當前用戶

mysql>SELECT User,Host,Password FROM user;

?登錄系統：mysql –uroot –p

?客戶端命令：本地執行

mysql> help

每個命令都完整形式和簡寫格式

mysql> status 或 \s

?服務端命令：通過mysql協議發往服務器執行并取回結果

每個命令都必須命令結束符號；默認為分號

SELECT VERSION();

 

 

通用二進制格式安裝過程

?二進制格式安裝過程

?(1) 準備用戶

?groupadd -r -g 306 mysql

?useradd -r -g 306 -u 306 –m –d /app/data mysql

?(2) 準備數據目錄

以/app/data為例,建議使用邏輯卷

?chown mysql:mysql /app/data

?(3) 準備二進制程序

?tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local

?cd /usr/local;ln -sv mariadb-VERSION mysql

?chown -R root:mysql /usr/local/mysql/

(4) 準備配置文件

mkdir /etc/mysql/

cp support-files/my-large.cnf /etc/mysql/my.cnf

[mysqld]中添加三個選項：

datadir = /app/data

innodb_file_per_table = on

skip_name_resolve = on 禁止主機名解析，建議使用

(5)創建數據庫文件

cd /usr/local/mysql/

./scripts/mysql_install_db –datadir=/app/data –user=mysql

?(6)準備日志文件

touch /var/log/mysqld.log

chown mysqld /var/log/mysqld.log

?(7)準備服務腳本，并啟動服務

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig –add mysqld

service mysqld start

?(8)安全初始化

/user/local/mysql/bin/mysql_secure_installation

 

 

源碼編譯安裝mariadb

?安裝包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-devel ncurses-devel libxml2-devel

?做準備用戶和數據目錄

mkdir /data

useradd –r –s /bin/false –m –d /data/mysqldb/ mysql

tar xvf mariadb-10.2.12.tar.gz

?cmake 編譯安裝

cd mariadb-10.2.12/

編譯選項:

https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

cmake . \

-DCMAKE_INSTALL_PREFIX=/app/mysql \

-DMYSQL_DATADIR=/data/mysqldb/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make && make install

 

準備環境變量

echo ‘PATH=/app/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

?生成數據庫文件

cd /app/mysql/

scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql

?準備配置文件

cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf

?準備啟動腳本

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

?啟動服務

chkconfig –add mysqld ;service mysqld start

 

 

多實例：yum安裝包的實現多實例

 

 

關系型數據庫的常見組件：

?數據庫：database

?表：table

行：row

列：column

?索引：index

?視圖：view

?用戶：user

?權限：privilege

?存儲過程：procedure，無返回值

?存儲函數：function，有返回值

?觸發器：trigger

?事件調度器：event scheduler，任務計劃

 

 

SQL語言的興起與語法標準

?20世紀70年代，IBM開發出SQL，用于DB2

?1981年，IBM推出SQL/DS數據庫

?業內標準微軟和Sybase的T-SQL，Oracle的PL/SQL

?SQL作為關系型數據庫所使用的標準語言，最初是基于IBM的實現在1986年被批準的。1987年，“國際標準化組織(ISO)”把ANSI(美國國家標準化組織) SQL作為國際標準。

?SQL：ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

 

 

SQL語言規范

?在數據庫系統中，SQL語句不區分大小寫(建議用大寫)

?但字符串常量區分大小寫

?SQL語句可單行或多行書寫，以“;”結尾

?關鍵詞不能跨多行或簡寫

?用空格和縮進來提高語句的可讀性

?子句通常位于獨立行，便于編輯，提高可讀性

?注釋：

??? SQL標準：

/*注釋內容*/ 多行注釋

— 注釋內容 單行注釋，注意有空格

??? MySQL注釋：

#

 

 

數據庫對象

?數據庫的組件(對象)：

數據庫、表、索引、視圖、用戶、存儲過程、函數、觸發器、事件調度器等

?命名規則：

???? 必須以字母開頭

???? 可包括數字和三個特殊字符（# _ $）

???? 不要使用MySQL的保留字

???? 同一數據庫（database(Schema)）下的對象不能同名

 

 

SQL語句分類

?SQL語句分類：

?DDL: Data Defination Language（數據定義語言）

CREATE, DROP, ALTER（增，刪，改）對數據的結構

?DML: Data Manipulation Language（數據操作語言）

INSERT, DELETE, UPDATE（增，刪，改）對數據的操作

?DCL：Data Control Language（數據的控制語言）

GRANT, REVOKE（授權，撤銷）

?DQL：Data Query Language（數據的查詢語言）

SELECT（查詢）

 

 

SQL語句構成

?SQL語句構成：

Keyword（關鍵字）組成clause（子句）

多條clause組成語句

?示例：

SELECT * SELECT子句

FROM products FROM子句

WHERE price>400 WHERE子句

說明：一組SQL語句，由三個子句構成，SELECT,FROM和WHERE是關鍵字

 

 

數據庫操作

?創建數據庫：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’;

CHARACTER SET ‘character set name’

COLLATE ‘collate name’

?刪除數據庫

DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

?查看支持所有字符集：SHOW CHARACTER SET;

?查看支持所有排序規則：SHOW COLLATION;

?獲取命令使用幫助：

mysql> HELP KEYWORD;

?查看數據庫列表：

mysql> SHOW DATABASES;

 

 

表

?表：二維關系

?設計表：遵循規范

?定義：字段，索引

字段：字段名，字段數據類型，修改符

約束，索引：應該創建在經常用作查詢條件的字段上

 

 

創建表

?創建表：CREATE TABLE

?(1) 直接創建

?(2) 通過查詢現存表創建；新表會被直接插入查詢而來的數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options]

[partition_options] select_statement

?(3) 通過復制現存的表的表結構創建，但不復制數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

?注意：

?Storage Engine是指表類型，也即在表創建時指明其使用的存儲引擎，同一庫中不同表可以使用不同的存儲引擎

?同一個庫中表建議要使用同一種存儲引擎類型

?CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修飾符, col2 type2 修飾符, …)

?字段信息

• col type1
• PRIMARY KEY(col1,…)
• INDEX(col1, …)
• UNIQUE KEY(col1, …)

?表選項：

• ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine類型

• ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

?獲取幫助：mysql> HELP CREATE TABLE;

 

 

表操作

?查看所有的引擎：SHOW ENGINES

?查看表：SHOW TABLES [FROM db_name]

?查看表結構：DESC [db_name.]tb_name

?刪除表：DROP TABLE [IF EXISTS] tb_name

?查看表創建命令：SHOW CREATE TABLE tbl_name

?查看表狀態：SHOW TABLE STATUS LIKE ‘tbl_name’

?查看庫中所有表狀態：SHOW TABLE STATUS FROM db_name

 

 

?數據類型：

?數據長什么樣？

?數據需要多少空間來存放？

?系統內置數據類型和用戶定義數據類型

?MySql支持多種列類型：

?數值類型

?日期/時間類型

?字符串(字符)類型

?選擇正確的數據類型對于獲得高性能至關重要，三大原則：

?更小的通常更好，盡量使用可正確存儲數據的最小數據類型

?簡單就好，簡單數據類型的操作通常需要更少的CPU周期

?盡量避免NULL，包含為NULL的列，對MySQL更難優化

 

?1、整型

?tinyint(m) 1個字節 范圍(-128~127)

?smallint(m) 2個字節 范圍(-32768~32767)

?mediumint(m) 3個字節 范圍(-8388608~8388607)

?int(m) 4個字節 范圍(-2147483648~2147483647)

?bigint(m) 8個字節 范圍(+-9.22*10的18次方)

取值范圍如果加了unsigned，則最大值翻倍，如tinyint unsigned的取值范圍為(0~255)

int(m)里的m是表示SELECT查詢結果集中的顯示寬度，并不影響實際的取值范圍，規定了MySQL的一些交互工具（例如MySQL命令行客戶端）用來顯示字符的個數。對于存儲和計算來說，Int(1)和Int(20)是相同的

?BOOL，BOOLEAN：布爾型，是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真

 

?2、浮點型(float和double)，近似值

?float(m,d) 單精度浮點型 8位精度(4字節) m總個數，d小數位

?double(m,d) 雙精度浮點型16位精度(8字節) m總個數，d小數位

?設一個字段定義為float(6,3)，如果插入一個數123.45678,實際數據庫里存的是123.457，但總個數還以實際為準，即6位

 

?3、定點數

?在數據庫中存放的是精確值,存為十進制

?decimal(m,d) 參數m<65 是總個數，d<30且 d<m 是小數位

?MySQL5.0和更高版本將數字打包保存到一個二進制字符串中（每4個字節存9個數字）。例如，decimal(18,9)小數點兩邊將各存儲9個數字，一共使用9個字節：小數點前的數字用4個字節，小數點后的數字用4個字節，小數點本身占1個字節

?浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節存儲。double占用8個字節

?因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用decimal——例如存儲財務數據。但在數據量比較大的時候，可以考慮使用bigint代替decimal

 

?4、字符串(char,varchar,_text)

?char(n) 固定長度，最多255個字符

?varchar(n)可變長度，最多65535個字符

?tinytext 可變長度，最多255個字符

?text 可變長度，最多65535個字符

?mediumtext 可變長度，最多2的24次方-1個字符

?longtext 可變長度，最多2的32次方-1個字符

?BINARY(M) 固定長度，可存二進制或字符，長度為0-M字節

?VARBINARY(M) 可變長度，可存二進制或字符，允許長度為0-M字節

?內建類型：ENUM枚舉, SET集合

 

char和varchar：

• 1.char(n) 若存入字符數小于n，則以空格補于其后，查詢之時再將空格去掉。所以char類型存儲的字符串末尾不能有空格，varchar不限于此。
• 2.char(n) 固定長度，char(4)不管是存入幾個字符，都將占用4個字節，varchar是存入的實際字符數+1個字節（n< n>255)，所以varchar(4),存入3個字符將占用4個字節。
• 3.char類型的字符串檢索速度要比varchar類型的快

?varchar和text：

• 1.varchar可指定n，text不能指定，內部存儲varchar是存入的實際字符數+1個字節（n< n>255)，text是實際字符數+2個字節。
• 2.text類型不能有默認值
• 3.varchar可直接創建索引，text創建索引要指定前多少個字符。varchar查詢速度快于text

 

5.二進制數據：BLOB

• BLOB和text存儲方式不同，TEXT以文本方式存儲，英文存儲區分大小寫，而Blob是以二進制方式存儲，不分大小寫
• BLOB存儲的數據只能整體讀出
• TEXT可以指定字符集，BLOB不用指定字符集

?6.日期時間類型

• date 日期 ‘2008-12-2’
• time 時間 ’12:25:36′
• datetime 日期時間 ‘2008-12-2 22:06:44’
• timestamp 自動存儲記錄修改時間
• YEAR(2), YEAR(4)：年份

timestamp字段里的時間數據會隨其他字段修改的時候自動刷新，這個數據類型的字段可以存放這條記錄最后被修改的時間

 

 

修飾符

?所有類型：

• NULL 數據列可包含NULL值
• NOT NULL 數據列不允許包含NULL值
• DEFAULT 默認值
• PRIMARY KEY 主鍵
• UNIQUE KEY 唯一鍵
• CHARACTER SET name 指定一個字符集

?數值型

• AUTO_INCREMENT 自動遞增，適用于整數類型
• UNSIGNED 無符號

 

示例

?CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);

?DESC students;

?CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

 

 

表操作

?DROP TABLE [IF EXISTS] ‘tbl_name’;

?ALTER TABLE ‘tbl_name’

字段：

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

刪除字段：drop

修改字段：

alter（默認值）, change（字段名）, modify（字段屬性）

索引:

添加索引：add index

刪除索引: drop index

表選項

修改:

?查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

?查看幫助：Help ALTER TABLE

 

修改表示例

?ALTER TABLE students RENAME s1;

?ALTER TABLE s1 ADD phone varchar(11) AFTER name;

?ALTER TABLE s1 MODIFY phone int;

?ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

?ALTER TABLE s1 DROP COLUMN mobile;

?Help ALTER TABLE 查看幫助

ALTER TABLE students ADD gender ENUM(‘m’,’f’)

?ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;

?ALTER TABLE students ADD UNIQUE KEY(name);

?ALTER TABLE students ADD INDEX(age);

?DESC students;

?SHOW INDEXES FROM students;

?ALTER TABLE students DROP age;

 

 

DML語句

?DML:

?INSERT, DELETE, UPDATE, SELECT

?INSERT：

一次插入一行或多行數據

語法

?INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

{VALUES | VALUE} ({expr | DEFAULT},…),(…),…

[ ON DUPLICATE KEY UPDATE 如果重復更新之

col_name=expr

[, col_name=expr] … ]

簡化寫法：

INSERT tbl_name [(col1,…)] VALUES (val1,…), (val21,…)

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

?INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

 

UPDATE：

?UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

?注意：一定要有限制條件，否則將修改所有行的指定字段

限制條件：

WHERE

LIMIT

?Mysql 選項：–safe-updates| –i-am-a-dummy|-U

 

DELETE:

?DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

可先排序再指定刪除的行數

?注意：一定要有限制條件，否則將清空表中的所有數據

限制條件：

WHERE

LIMIT

?TRUNCATE TABLE tbl_name; 清空表

 

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr …]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], … [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], …]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[FOR UPDATE | LOCK IN SHARE MODE]

 

SELECT

?字段顯示可以使用別名：

col1 AS alias1, col2 AS alias2, …

?WHERE子句：指明過濾條件以實現“選擇”的功能：

過濾條件：布爾型表達式

算術操作符：+, -, *, /, %

比較操作符：=, !=, <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2, …)

IS NULL???????? 表示空

IS NOT NULL

 

LIKE:

%: 任意長度的任意字符

_：任意單個字符

?RLIKE：正則表達式，索引失效，不建議使用

?REGEXP：匹配字符串可用正則表達式書寫模式，同上

?邏輯操作符：

NOT

AND

OR

XOR

 

?GROUP：根據指定的條件把查詢結果進行“分組”以用于做“聚合”運算

avg(), ??max(), ?min(), count(), sum()

平均值，最大值，最小值，總個數，? 相加

HAVING: 對分組聚合運算后的結果指定過濾條件

?ORDER BY: 根據指定的字段對查詢結果進行排序

升序：ASC

降序：DESC

?LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制

?對查詢結果中的數據請求施加“鎖”

FOR UPDATE: 寫鎖，獨占或排它鎖，只有一個讀和寫

LOCK IN SHARE MODE: 讀鎖，共享鎖，同時多個讀

 

 

多表查詢

?交叉連接：笛卡爾乘積

?內連接：

等值連接：讓表之間的字段以“等值”建立連接關系；

不等值連接

自然連接:去掉重復列的等值連接

自連接

?外連接：

左外連接：

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外連接

FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

 

子查詢：在查詢語句嵌套著查詢語句，性能較差

基于某語句的查詢結果再次進行的查詢

?用在WHERE子句中的子查詢：

?用于比較表達式中的子查詢；子查詢僅能返回單個值

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

?用于IN中的子查詢：子查詢應該單鍵查詢并返回一個或多個值從構成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

?用于EXISTS

 

用于FROM子句中的子查詢

使用格式：SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias WHERE Clause;

示例：

SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

?聯合查詢：UNION

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

 

 

視圖

?視圖：VIEW,虛表，保存有實表的查詢結果

?創建方法：

CREATE VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

?查看視圖定義：SHOW CREATE VIEW view_name

?刪除視圖：

DROP VIEW [IF EXISTS]

view_name [, view_name] …

[RESTRICT | CASCADE]

?視圖中的數據事實上存儲于“基表”中，因此，其修改操作也會針對基表實現；其修改操作受基表限制

物化視圖：是存儲在硬盤的真實的表

 

 

函數：必須要嵌入到SQL語句中使用，不能單獨使用

?函數：系統函數和自定義函數

系統函數:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

?自定義函數 (user-defined function UDF)

?保存在mysql.proc表中

?創建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,…])

RETURNS {STRING|INTEGER|REAL}

runtime_body

?說明：

參數可以有多個,也可以沒有參數

必須有且只有一個返回值

 

 

自定義函數

?查看函數列表：

SHOW FUNCTIOIN STATUS;

?查看函數定義

SHOW CREATE FUNCTION function_name

?刪除UDF:

DROP FUNCTION function_name

?調用自定義函數語法:

SELECT function_name(parameter_value,…)

 

示例:無參UDF

CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN “Hello World!“;

執行此函數返回一個“hello?? world”?

示例：有參數UDF

DELIMITER //

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)

BEGIN

DELETE FROM students WHERE stuid = uid;

RETURN (SELECT COUNT(uid) FROM students);

END//

DELIMITER ;

 

自定義函數中定義局部變量語法:

?DECLARE 變量1[,變量2,… ]變量類型 [DEFAULT 默認值]

?說明：局部變量的作用范圍是在BEGIN…END程序中,而且定義局部變量語句必須在BEGIN…END的第一行定義

?示例:

DELIMITER //

CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT

BEGIN

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

SET a = x, b = y;

RETURN a+b;

END//

 

為變量賦值語法

?SET parameter_name = value[,parameter_name = value…]

?SELECT INTO parameter_name

?示例:

…

DECLARE x int;

SELECT COUNT(id) FROM tdb_name INTO x;

RETURN x;

END//

 

 

存儲過程

?存儲過程：存儲過程保存在mysql.proc表中

?創建存儲過程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])

routime_body

其中:proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型

?查看存儲過程列表

SHOW PROCEDURE STATUS

 

查看存儲過程定義

SHOW CREATE PROCEDURE sp_name

?調用存儲過程:

CALL sp_name ([ proc_parameter [,proc_parameter …]])

CALL sp_name

說明:當無參時,可以省略”()”,當有參數時,不可省略”()”

?存儲過程修改:

ALTER語句修改存儲過程只能修改存儲過程的注釋等無關緊要的東西,不能修改存儲過程體,所以要修改存儲過程,方法就是刪除重建

?刪除存儲過程:

DROP PROCEDURE [IF EXISTS] sp_name

 

 

存儲過程示例

?創建無參存儲過程:

delimiter //

 

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END//

 

delimiter ;

 

CALL showTime;??????? 用CALL命令呼叫執行

 

創建含參存儲過程:只有一個IN參數

delimiter //

CREATE PROCEDURE seleById(IN id SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = uid;

END//

delimiter ;

call seleById(2);

 

示例

delimiter //

CREATE PROCEDURE dorepeat(p1 INT)

BEGIN

SET @x = 0;

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

END

//

delimiter ;

CALL dorepeat(1000);

SELECT @x;

 

創建含參存儲過程:包含IN參數和OUT參數

delimiter //

CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = id;

SELETE row_count() into num;

END//

delimiter ;

call seleById(2,@Line);

SELETE @Line;

?說明:創建存儲過程deleteById,包含一個IN參數和一個OUT參數.調用時,傳入刪除的ID和保存被修改的行數值的用戶變量@Line,select @Line;輸出被影響行數

 

 

存儲過程

?存儲過程優勢:

?存儲過程把經常使用的SQL語句或業務邏輯封裝起來,預編譯保存在數據庫中,當需要時從數據庫中直接調用,省去了編譯的過程

?提高了運行速度

?同時降低網絡數據傳輸量

?存儲過程與自定義函數的區別:

?存儲過程實現的過程要復雜一些,而函數的針對性較強

?存儲過程可以有多個返回值,而自定義函數只有一個返回值

?存儲過程一般獨立的來執行,而函數往往是作為其他SQL語句的一部分來使用

 

 

流程控制

?存儲過程和函數中可以使用流程控制來控制語句的執行

?流程控制：

?IF：用來進行條件判斷。根據是否滿足條件，執行不同語句

?CASE：用來進行條件判斷，可實現比IF語句更復雜的條件判斷

?LOOP：重復執行特定的語句，實現一個簡單的循環

?LEAVE：用于跳出循環控制

?ITERATE：跳出本次循環，然后直接進入下一次循環

?REPEAT：有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句

?WHILE：有條件控制的循環語句

 

 

觸發器

?觸發器的執行不是由程序調用，也不是由手工啟動，而是由事件來觸發、激活從而實現執行

?創建觸發器

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

?說明：

trigger_name：觸發器的名稱

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發

trigger_event:：{ INSERT |UPDATE | DELETE }，觸發的具體事件

tbl_name：該觸發器作用在表名

 

 

觸發器示例

CREATE TABLE student_info (

stu_no INT(11) NOT NULL AUTO_INCREMENT,

stu_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (stu_no)

);

CREATE TABLE student_count (

student_count INT(11) DEFAULT 0

);

INSERT INTO student_count VALUES(0);

 

示例：創建觸發器，在向學生表INSERT數據時，學生數增加，DELETE學生時，學生數減少

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

 

 

觸發器

?查看觸發器

SHOW TRIGGERS

查詢系統表information_schema.triggers的方式指定查詢條件，查看指定的觸發器信息。

mysql> USE information_schema;

Database changed

mysql> SELECT * FROM triggers WHERE trigger_name=’trigger_student_count_insert’;

?刪除觸發器

DROP TRIGGER trigger_name;

 

 

MySQL用戶和權限管理

?元數據數據庫：mysql

系統授權表：

db, host, user

columns_priv, tables_priv, procs_priv, proxies_priv

?用戶賬號：

‘USERNAME’@’HOST’：

@’HOST’:

主機名；

IP地址或Network;

通配符：

%, _: 172.16.%.%

 

 

用戶管理

?創建用戶：CREATE USER

CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’]；

默認權限：USAGE

?用戶重命名：RENAME USER

RENAME USER old_user_name TO new_user_name

?刪除用戶：

DROP USER ‘USERNAME’@’HOST‘

示例：刪除默認的空用戶

DROP USER ”@’localhost’;

 

修改密碼：

?mysql>SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘password’);

?mysql>UPDATE mysql.user SET password=PASSWORD(‘your_password’) WHERE clause;

此方法需要執行下面指令才能生效：mysql> FLUSH PRIVILEGES;

? #mysqladmin -u root –poldpass password ‘newpass‘

?忘記管理員密碼的解決辦法：

?啟動mysqld進程時，為其使用如下選項：

–skip-grant-tables –skip-networking

?使用UPDATE命令修改管理員密碼

?關閉mysqld進程，移除上述兩個選項，重啟mysqld

 

 

MySQL權限管理

?權限類別：

數據庫級別

表級別

字段級別

管理類

程序類

 

 

MySQL用戶和權限管理

?管理類：

CREATE TEMPORARY TABLES

CREATE USER

FILE

SUPER

SHOW DATABASES

RELOAD

SHUTDOWN

REPLICATION SLAVE

REPLICATION CLIENT

LOCK TABLES

PROCESS

 

程序類： FUNCTION、PROCEDURE、TRIGGER

CREATE

ALTER

DROP

EXCUTE

?庫和表級別：DATABASE、TABLE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION：能將自己獲得的權限轉贈給其他用戶

 

數據操作：

SELECT

INSERT

DELETE

UPDATE

?字段級別：

SELECT(col1,col2,…)

UPDATE(col1,col2,…)

INSERT(col1,col2,…)

?所有權限：ALL PRIVILEGES 或 ALL

 

 

授權

?參考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

?GRANT priv_type [(column_list)],… ON [object_type] priv_level TO ‘user’@’host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

?priv_type: ALL [PRIVILEGES]

?object_type:TABLE | FUNCTION | PROCEDURE

?priv_level: *(所有庫) | *.* | db_name.* | db_name.tbl_name | tbl_name(當前庫的表) | db_name.routine_name(指定庫的函數,存儲過程,觸發器)

?with_option: GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;

 

回收授權：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] priv_level FROM user [, user] …

示例：REVOKE DELETE ON testdb.* FROM ‘testuser’@’%‘

?查看指定用戶獲得的授權：

Help SHOW GRANTS

SHOW GRANTS FOR ‘user’@’host’;

SHOW GRANTS FOR CURRENT_USER[()];

?注意：MariaDB服務進程啟動時會讀取mysql庫中所有授權表至內存

(1) GRANT或REVOKE等執行權限操作會保存于系統表中，MariaDB的服務進程通常會自動重讀授權表，使之生效

(2) 對于不能夠或不能及時重讀授權表的命令，可手動讓MariaDB的服務進程重讀授權表：

mysql> FLUSH PRIVILEGES;

 

 

存儲引擎

?MyISAM引擎特點：

?不支持事務

?表級鎖定

?讀寫相互阻塞，寫入不能讀，讀時不能寫

?只緩存索引

?不支持外鍵約束

?不支持聚簇索引

?讀取數據較快，占用資源較少

?不支持MVCC（多版本并發控制機制）高并發

?崩潰恢復性較差

?MySQL5.5.5前默認的數據庫引擎

 

適用場景：只讀（或者寫較少）、表較?。梢越邮荛L時間進行修復操作）

?MyISAM引擎文件：

tbl_name.frm: 表格式定義

tbl_name.MYD: 數據文件

tbl_name.MYI: 索引文件

 

?InnoDB引擎特點：

?支持事務，適合處理大量短期事務

?行級鎖

?讀寫阻塞與事務隔離級別相關

?可緩存數據和索引

?支持聚簇索引

?崩潰恢復性更好

?支持MVCC高并發

?從MySQL5.5后支持全文索引

?從MySQL5.5.5開始為默認的數據庫引擎

 

?InnoDB數據庫文件

?所有InnoDB表的數據和索引放置于同一個表空間中

表空間文件：datadir定義的目錄下

數據文件：ibddata1, ibddata2, …

?每個表單獨使用一個表空間存儲表的數據和索引

啟用：innodb_file_per_table=ON

兩類文件放在數據庫獨立目錄中

數據文件(存儲數據和索引)：tb_name.ibd

表格式定義：tb_name.frm

 

 

其它存儲引擎

?Performance_Schema:Performance_Schema數據庫

?Memory ：將所有數據存儲在RAM中，以便在需要快速查找參考和其他類似數據的環境中進行快速訪問。適用存放臨時數據。引擎以前被稱為HEAP引擎

?MRG_MyISAM：使MySQL DBA或開發人員能夠對一系列相同的MyISAM表進行邏輯分組，并將它們作為一個對象引用。適用于VLDB(Very Large Data Base)環境，如數據倉庫

?Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息，只支持SELECT和INSERT操作；支持行級鎖和專用緩存區

?Federated聯合：用于訪問其它遠程MySQL服務器一個代理，它通過創建一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，而后完成數據存取，提供鏈接單獨MySQL服務器的能力，以便從多個物理服務器創建一個邏輯數據庫。非常適合分布式或數據集市環境

 

 

其它數據庫引擎

?BDB：可替代InnoDB的事務引擎，支持COMMIT、ROLLBACK和其他事務特性

?Cluster/NDB：MySQL的簇式數據庫引擎，尤其適合于具有高性能查找要求的應用程序，這類查找需求還要求具有最高的正常工作時間和可用性

?CSV：CSV存儲引擎使用逗號分隔值格式將數據存儲在文本文件中?？梢允褂肅SV引擎以CSV格式導入和導出其他軟件和應用程序之間的數據交換

?BLACKHOLE ：黑洞存儲引擎接受但不存儲數據，檢索總是返回一個空集。該功能可用于分布式數據庫設計，數據自動復制，但不是本地存儲

?example：“stub”引擎，它什么都不做。可以使用此引擎創建表，但不能將數據存儲在其中或從中檢索。目的是作為例子來說明如何開始編寫新的存儲引擎

 

MariaDB支持的其它存儲引擎：

?OQGraph

?SphinxSE

?TokuDB

?Cassandra

?CONNECT

?SQUENCE

 

 

管理存儲引擎

?查看mysql支持的存儲引擎:

show engines;

?查看當前默認的存儲引擎:

show variables like ‘%storage_engine%’;

?設置默認的存儲引擎：

vim /etc/my.conf

[mysqld]

default_storage_engine= InnoDB;

 

查看庫中所有表使用的存儲引擎

Show table status from db_name;

?查看庫中指定表的存儲引擎

show table status like ‘tb_name‘;

show create table tb_name;

?設置表的存儲引擎：

CREATE TABLE tb_name(… ) ENGINE=InnoDB;

ALTER TABLE tb_name ENGINE=InnoDB;

 

MySQL中的系統數據庫

?mysql數據庫：是mysql的核心數據庫，類似于sql server中的master庫，主要負責存儲數據庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理信息

?PERFORMANCE_SCHEMA:MySQL 5.5開始新增的數據庫，主要用于收集數據庫服務器性能參數,庫里表的存儲引擎均為PERFORMANCE_SCHEMA，用戶不能創建存儲引擎為PERFORMANCE_SCHEMA的表

?information_schema數據庫:MySQL 5.0之后產生的，一個虛擬數據庫，物理上并不存在。information_schema數據庫類似與“數據字典”，提供了訪問數據庫元數據的方式，即數據的數據。比如數據庫名或表名，列類型，訪問權限（更加細化的訪問方式）

 

 

服務器配置

?mysqld選項，服務器系統變量和服務器狀態變量

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

?獲取運行中的mysql進程使用各服務器參數及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

?注意：其中有些參數支持運行時修改，會立即生效；有些參數不支持，且只能通過修改配置文件，并重啟服務器程序生效；有些參數作用域是全局的，且不可改變；有些可以為每個用戶提供單獨（會話）的設置

 

設置服務器系統變量三種方法：

?在命令行中設置:

shell> ./mysqld_safe –aria_group_commit=”hard“

?在配置文件my.cnf中設置：

aria_group_commit = “hard”

?在mysql客戶端使用SET命令：

SET GLOBAL aria_group_commit=”hard”;

 

 

服務器端設置

?修改服務器變量的值：

mysql> help SET

?修改全局變量：僅對修改后新創建的會話有效；對已經建立的會話無效

mysql> SET GLOBAL system_var_name=value;

mysql> SET @@global.system_var_name=value;

?修改會話變量：

mysql> SET [SESSION] system_var_name=value;

mysql> SET @@[session.]system_var_name=value;

?狀態變量（只讀）：用于保存mysqld運行中的統計數據的變量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

 

 

SQL_MODE

?服務器變量SQL_MODE：對其設置可以完成一些約束檢查的工作,可分別進行全局的設置或當前會話的設置

?常見MODE:

?NO_AUTO_CREATE_USER

禁止GRANT創建密碼為空的用戶

?NO_AUTO_VALUE_ON_ZERO

在自增長的列中插入0或NULL將不會是下一個自增長值

?NO_BACKSLASH_ESCAPES

反斜杠“\”作為普通字符而非轉義字符

?PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截斷空洞數據

?PAD_CHAR_TO_FULL_LENGTH

啟用后，對于CHAR類型將不會截斷空洞數據

?PIPES_AS_CONCAT

將”||”視為連接操作符而非“或運算符”

 

 

查詢緩存

?查詢緩存（ Query Cache ）原理：

緩存SELECT操作或預處理查詢的結果集和SQL語句，當有新的SELECT語句或預處理查詢語句請求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標準：與緩存的SQL語句，是否完全一樣，區分大小寫

?優缺點

不需要對SQL語句做任何解析和執行，當然語法解析必須通過在先，直接從Query Cache中獲得查詢結果，提高查詢性能

查詢緩存的判斷規則，不夠智能，也即提高了查詢緩存的使用門檻，降低其效率；

查詢緩存的使用，會增加檢查和清理Query Cache中記錄集的開銷

 

哪些查詢可能不會被緩存

?查詢語句中加了SQL_NO_CACHE參數

?查詢語句中含有獲得值的函數，包含自定義函數，如：NOW()

CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等

?對系統數據庫的查詢：mysql、information_schema 查詢語句中使用SESSION級別變量或存儲過程中的局部變量

?查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句 查詢語句中類似SELECT …INTO 導出數據的語句

?對臨時表的查詢操作；存在警告信息的查詢語句；不涉及任何表或視圖的查詢語句；某用戶只有列級別權限的查詢語句

?事務隔離級別為Serializable時，所有查詢語句都不能緩存

 

查詢緩存相關的服務器變量

?query_cache_min_res_unit: 查詢緩存中內存塊的最小分配單位，默認4k，較小值會減少浪費，但會導致更頻繁的內存分配操作，較大值會帶來浪費，會導致碎片過多，內存不足

?query_cache_limit：單個查詢結果能緩存的最大值，默認為1M，

對于查詢結果過大而無法緩存的語句，建議使用SQL_NO_CACHE

?query_cache_size：查詢緩存總共可用的內存空間；單位字節，必須是1024的整數倍，最小值40KB，低于此值有警報

?query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結果， 默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續從緩存返回數據；ON則表示不允許

?query_cache_type: 取值為ON, OFF, DEMAND

?參看：https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

 

SELECT語句的緩存控制

?SQL_CACHE: 顯式指定存儲查詢結果于緩存之中

?SQL_NO_CACHE: 顯式查詢結果不予緩存

?query_cache_type參數變量：

?query_cache_type的值為OFF或0時，查詢緩存功能關閉

?query_cache_type的值為ON或1時，查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存，否則，不予緩存，顯式指定SQL_NO_CACHE，不予緩存，此為默認值

?query_cache_type的值為DEMAND或2時，查詢緩存功能按需進行，顯式指定SQL_CACHE的SELECT語句才會緩存；其它均不予緩存

 

 

查詢緩存相關的狀態變量

SHOW GLOBAL STATUS LIKE ‘Qcache%’;

?Qcache_free_blocks：處于空閑狀態 Query Cache中內存 Block 數

?Qcache_free_memory：處于空閑狀態的 Query Cache 內存總量

?Qcache_hits：Query Cache 命中次數

?Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，即沒有命中的次數

?Qcache_lowmem_prunes：當 Query Cache 內存容量不夠，需要刪除老的 Query Cache 以給新的 Cache 對象使用的次數

?Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL 以及由于 query_cache_type 設置的不會被 Cache 的 SQL語句

?Qcache_queries_in_cache：在 Query Cache 中的 SQL 數量

?Qcache_total_blocks：Query Cache 中總的 Block

 

 

命中率

?命中率和內存使用率估算

?query_cache_min_res_unit ≈(query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache

?查詢緩存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

?查詢緩存內存使用率 ≈ (query_cache_size – qcache_free_memory) / query_cache_size * 100%

 

 

InnoDB存儲引擎

?InnoDB存儲引擎的緩沖池:

通常InnoDB存儲引擎緩沖池的命中不應該小于99%

?查看相關狀態變量：

show global status like ‘innodb%read%’\G

?Innodb_buffer_pool_reads: 表示從物理磁盤讀取頁的次數

?Innodb_buffer_pool_read_ahead: 預讀的次數

?Innodb_buffer_pool_read_ahead_evicted: 預讀頁，但是沒有讀取就從緩沖池中被替換的頁數量，一般用來判斷預讀的效率

?Innodb_buffer_pool_read_requests: 從緩沖池中讀取頁次數

?Innodb_data_read: 總共讀入的字節數

?Innodb_data_reads: 發起讀取請求的次數，每次讀取可能需要讀取多個頁

 

 

索引

?索引是特殊數據結構：定義在查找時作為查找條件的字段

?優點：提高查詢速度，缺點：占用額外空間，影響插入速度

?索引實現在存儲引擎

?索引類型：

?聚簇（集）索引、非聚簇索引（區別）：數據是否與索引存儲在一起

?主鍵索引、輔助索引

?稠密索引、稀疏索引：是否索引了每一個數據項

?B+ TREE、HASH、R TREE

?簡單索引（一個字段上建立索引）、組合索引（多個字段上建立索引）

左前綴索引：取前面的字符做索引

覆蓋索引：從索引中即可取出要查詢的數據，性能高

 

 

B-Tree索引

?B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左前綴索引，適合查詢范圍類的數據

?可以使用B-Tree索引的查詢類型：

全值匹配：精確所有索引列，如：姓wang，名xiaochun，年齡30

匹配最左前綴：即只使用索引的第一列，如：姓wang

匹配列前綴：只匹配一列值開頭部分，如：姓以w開頭的

匹配范圍值：如：姓ma和姓wang之間

精確匹配某一列并范圍匹配另一列：如：姓wang,名以x開頭的

只訪問索引的查詢

 

 

B-Tree索引的限制：

?如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結尾

?不能跳過索引中的列：如：查找姓wang,年齡30的，只能使用索引第一列

?如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如：姓wang,名x%,年齡30，只能利用姓和名上面的索引

?特別提示：

?索引列的順序和查詢語句的寫法應相匹配，才能更好的利用索引

?為優化性能，可能需要針對相同的列但順序不同創建不同的索引來滿足不同類型的查詢需求

 

 

Hash索引

?Hash索引：基于哈希表實現，只有精確匹配索引中的所有列的查詢才有效，索引自身只存儲索引列對應的哈希值和數據指針，索引結構緊湊，查詢性能好

?只有Memory存儲引擎支持顯式hash索引

?適用場景：

只支持等值比較查詢，包括=, IN(), <=>

?不適合使用hash索引的場景：

不適用于順序查詢：索引存儲順序的不是值的順序

不支持模糊匹配

不支持范圍查詢

不支持部分索引列匹配查找：如A，B列索引，只查詢A列索引無效

 

 

索引

?空間索引（R-Tree）：

MyISAM支持空間索引，可以使用任意維度組合查詢，使用特有的函數訪問，常用于做地理數據存儲，使用不多

?全文索引(FULLTEXT)：

在文本中查找關鍵詞，而不是直接比較索引中的值，類似搜索引擎

?索引優點：

索引可以降低服務需要掃描的數據量，減少了IO次數

索引可以幫助服務器避免排序和使用臨時表

索引可以幫助將隨機I/O轉為順序I/O

 

高性能索引策略：

?獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較符號的一側

?左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估

索引選擇性：不重復的索引值和數據表的記錄總數的比值

?多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引

?選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側

?冗余和重復索引：（A），（A，B）即為冗余索引

不好的索引使用策略，建議擴展索引，而非冗余

 

 

索引優化建議

?只要列中含有NULL值，就最好不要在此例設置索引，復合索引如果有NULL值，此列在使用時也不會使用索引

?盡量使用短索引，如果可以，應該制定一個前綴長度

?對于經常在where子句使用的列，最好設置索引

?對于有多個列where或者order by子句，應該建立復合索引

?對于like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引

?盡量不要在列上進行運算（函數操作和表達式操作）

?盡量不要使用not in和<>操作

?多表連接時，盡量小表驅動大表，即小表 join 大表

?在千萬級分頁時使用limit

?對于經常使用的查詢，可以開啟緩存

?大部分情況連接效率遠大于子查詢、

 

 

EXPLAIN

?通過EXPLAIN來分析索引的有效性：

?EXPLAIN SELECT clause

獲取查詢執行計劃信息，用來查看查詢優化器如何執行查詢

?輸出信息說明：參考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

?id: 當前查詢語句中，每個SELECT語句的編號

復雜類型的查詢有三種：

簡單子查詢

用于FROM中的子查詢

聯合查詢：UNION

注意：UNION查詢的分析結果會出現一個額外匿名臨時表

 

select_type：

簡單查詢為SIMPLE

復雜查詢：

SUBQUERY: 簡單子查詢

PRIMARY:最外面的SELECT

DERIVED: 用于FROM中的子查詢

UNION：UNION語句的第一個之后的SELECT語句

UNION RESULT: 匿名臨時表

?table：SELECT語句關聯到的表

 

type：關聯類型或訪問類型，即MySQL決定的如何去查詢表中的行的方式，以下順序，性能從低到高

?ALL: 全表掃描

?index：根據索引的次序進行全表掃描；如果在Extra列出現“Using index”表示了使用覆蓋索引，而非全表掃描

?range：有范圍限制的根據索引實現范圍掃描；掃描位置始于索引中的某一點，結束于另一點

?ref: 根據索引返回表中匹配某單個值的所有行

?eq_ref：僅返回一個行，但與需要額外與某個參考值做比較

?const, system: 直接返回單個行

?possible_keys：查詢可能會用到的索引

?key: 查詢中使用到的索引

?key_len: 在索引使用的字節數

 

ref: 在利用key字段所表示的索引完成查詢時所用的列或某常量值

?rows：MySQL估計為找所有的目標行而需要讀取的行數

?Extra：額外信息

Using index：MySQL將會使用覆蓋索引，以避免訪問表

Using where：MySQL服務器將在存儲引擎檢索后，再進行一次過濾

Using temporary：MySQL對結果排序時會使用臨時表

Using filesort：對結果使用一個外部索引排序

 

 

管理索引

?創建索引：

CREATE INDEX index_name ON tbl_name (index_col_name,…);

help CREATE INDEX

?刪除索引：

DROP INDEX index_name ON tbl_name;

?查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

?優化表空間：

OPTIMIZE TABLE tb_name

 

 

SQL語句性能優化

?查詢時，能不要*就不用*，盡量寫全字段名

?大部分情況連接效率遠大于子查詢

?多表連接時，盡量小表驅動大表，即小表 join 大表

?在千萬級分頁時使用limit

?對于經常使用的查詢，可以開啟緩存

?多使用explain和profile分析查詢語句

?查看慢查詢日志，找出執行時間長的sql語句優化

 

 

并發控制

?鎖粒度：

表級鎖

行級鎖

?鎖：

讀鎖：共享鎖，只讀不可寫，多個讀互不阻塞，

寫鎖：獨占鎖,排它鎖，一個寫鎖會阻塞其它讀和它鎖

?實現

存儲引擎：自行實現其鎖策略和鎖粒度

服務器級：實現了鎖，表級鎖；用戶可顯式請求

?分類：

隱式鎖：由存儲引擎自動施加鎖

顯式鎖：用戶手動請求

 

鎖策略：在鎖粒度及數據安全性尋求的平衡機制

?顯示使用鎖

?LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] …

lock_type: READ ， WRITE

UNLOCK TABLES 解鎖

?FLUSH TABLES tb_name[,…] [WITH READ LOCK]

關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

?SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查詢時加寫或讀鎖

 

 

事務

?事務Transactions：一組原子性的SQL語句，或一個獨立工作單元

?事務日志：記錄事務信息，實現undo,redo等故障恢復功能

?ACID特性：

?A：atomicity原子性；整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾

?C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態

?I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發

?D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中

 

啟動事務：START TRANSACTION

?結束事務：

(1) COMMIT：提交

(2) ROLLBACK: 回滾

?注意：只有事務型存儲引擎方能支持此類操作

?建議：顯式請求和提交事務，而不要使用“自動提交”功能

set autocommit={1|0}

?事務支持保存點：savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

 

 

顯性的事務：明確規定事務的開始。。

隱性的事務：自動完成對事務的修改

 

 

事務隔離級別

?事務隔離級別：從上至下更加嚴格

?READ UNCOMMITTED 可讀取到未提交數據，產生臟讀

?READ COMMITTED 可讀提交數據，但未提交數據不可讀，產生不可重復讀，即可讀取到多個提交數據，導致每次讀取數據不一致

?REPEATABLE READ 可重復讀，多次讀取數據都一致，產生幻讀，即讀取過程中，即使有其它提交的事務修改數據，仍只能讀取到未修改前的舊數據。此為MySQL默認設置

?SERIALIZABILE 可串行化，未提交的讀事務阻塞修改事務，或者未提交的修改事務阻塞讀事務。導致并發性能差

?MVCC: 多版本并發控制，和事務級別相關

 

 

指定事務隔離級別：

?服務器變量tx_isolation指定，默認為REPEATABLE-READ，可在GLOBAL和SESSION級進行設置

SET tx_isolation=”

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

?服務器選項中指定

vim /etc/my.cnf

[mysqld]

transaction-isolation=SERIALIZABLE

 

 

并發控制

?死鎖：

兩個或多個事務在同一資源相互占用，并請求鎖定對方占用的資源的狀態

?事務日志：

事務日志的寫入類型為“追加”，因此其操作為“順序IO”；通常也被稱為：預寫式日志 write ahead logging

日志文件： ib_logfile0， ib_logfile1

 

 

日志

?事務日志：transaction log

?錯誤日志：error log

?查詢日志：query log

?慢查詢日志：slow query log

?二進制日志：binary log

?中繼日志：reley log

 

事務日志：transaction log

?事務型存儲引擎自行管理和使用

redo log

undo log

?Innodb事務日志相關配置：

show variables like ‘%innodb_log%’;

innodb_log_file_size 5242880 每個日志文件大小

innodb_log_files_in_group 2 日志組成員個數

innodb_log_group_home_dir ./ 事務文件路徑

?中繼日志：relay log

主從復制架構中，從服務器用于保存從主服務器的二進制日志中讀取到的事件

 

錯誤日志

mysqld啟動和關閉過程中輸出的事件信息

mysqld運行中產生的錯誤信息

event scheduler運行一個event時產生的日志信息

在主從復制架構中的從服務器上啟動從服務器線程時產生的信息

?錯誤日志相關配置

SHOW GLOBAL VARIABLES LIKE ‘log_error’

錯誤文件路徑：

log_error=/PATH/TO/LOG_ERROR_FILE

是否記錄警告信息至錯誤日志文件

log_warnings=1|0 默認值1

 

查詢日志：記錄查詢操作

文件：file，默認值

表：table

?查詢日志相關設置

general_log=ON|OFF

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE

 

慢查詢日志

?慢查詢日志：記錄執行查詢時長超出指定時長的操作

slow_query_log=ON|OFF 開啟或關閉慢查詢

long_query_time=N 慢查詢的閥值，單位秒

slow_query_log_file=HOSTNAME-slow.log 慢查詢日志文件

log_slow_filter = admin,filesort,filesort_on_disk,full_join,

full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

log_queries_not_using_indexes=ON 不使用索引也沒有達到慢查詢閥值的語句是否記錄日志，默認OFF，即不記錄

log_slow_rate_limit = 1 多少次查詢才記錄，mariadb特有

log_slow_verbosity= Query_plan,explain 記錄內容log_slow_queries = OFF 同slow_query_log 新版已廢棄

 

二進制日志

記錄導致數據改變或潛在導致數據改變的SQL語句

功能：通過“重放”日志文件中的事件來生成數據副本

注意：建議二進制日志和數據文件分開存放

?二進制日志相關配置

查看mariadb自行管理使用中的二進制日志文件列表

SHOW {BINARY | MASTER} LOGS

查看使用中的二進制日志文件

SHOW MASTER STATUS

查看二進制文件中的指定內容

SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

show binlog events in ‘mariadb-bin.000001’ from 6516 limit 2,3

 

二進制日志記錄格式

?二進制日志記錄三種格式

基于“語句”記錄：statement，記錄語句，默認模式

基于“行”記錄：row，記錄數據，日志量較大

混合模式：mixed, 讓系統自行判定該基于哪種方式進行

?格式配置

show variables like ‘%binlog_format%’;

?二進制日志文件的構成

有兩類文件

日志文件：mysql|mariadb-bin.文件名后綴，二進制格式

如： mysql-bin.000001

索引文件：mysql|mariadb-bin.index，文本格式

 

二進制日志相關的服務器變量：

?sql_log_bin=ON|OFF：是否記錄二進制日志，默認ON

?log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩項都開啟才可

?binlog_format=STATEMENT|ROW|MIXED：二進制日志記錄的格式，默認STATEMENT

?max_binlog_size=1073741824：單個二進制日志文件的最大體積，到達最大值會自動滾動，默認為1G

說明：文件達到上限時的大小未必為指定的精確值

?sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到磁盤

?expire_logs_days=N：二進制日志可以自動刪除的天數。 默認為0，即不自動刪除

 

mysqlbinlog：二進制日志的客戶端命令工具

?命令格式：

mysqlbinlog [OPTIONS] log_file…

–start-position=# 指定開始位置

–stop-position=#

–start-datetime=

–stop-datetime=

時間格式：YYYY-MM-DD hh:mm:ss

–base64-output[=name]

示例：mysqlbinlog –start-position=6787 –stop-position=7527 /var/lib/mysql/mariadb-bin.000003

mysqlbinlog –start-datetime=”2018-01-30 20:30:10″ –stop-datetime=”2018-01-30 20:35:22″ mariadb-bin.000003;

 

二進制日志事件的格式：

# at 328

#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1446712300/*!*/;

CREATE TABLE tb1 (id int, name char(30))

/*!*/;

事件發生的日期和時間：151105 16:31:40

事件發生的服務器標識：server id 1

事件的結束位置：end_log_pos 431

事件的類型：Query

事件發生時所在服務器執行此事件的線程的ID：thread_id=1

語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0

錯誤代碼：error_code=0

事件內容：

GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID

 

清除指定二進制日志：

PURGE { BINARY | MASTER } LOGS

{ TO ‘log_name’ | BEFORE datetime_expr }

示例：

PURGE BINARY LOGS TO ‘mariadb-bin.000003’;刪除3前日志

PURGE BINARY LOGS BEFORE ‘2017-01-23’;

PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;

?刪除所有二進制日志，index文件重新記數

RESET MASTER [TO #]; 日志文件從#開始記數，默認從1開始，一般是master第一次啟動時執行，MariaDB10.1.6開始支持TO #

?切換日志文件：

FLUSH LOGS;

 

 

備份和恢復

?為什么要備份

災難恢復：硬件故障、軟件故障、自然災害、黑客攻擊、誤操作測試等數據丟失場景

?備份注意要點

?能容忍最多丟失多少數據

?恢復數據需要在多長時間內完成

?需要恢復哪些數據

?還原要點

?做還原測試，用于測試備份的可用性

?還原演練

 

備份類型：

?完全備份，部分備份

完全備份：整個數據集

部分備份：只備份數據子集，如部分庫或表

?完全備份、增量備份、差異備份

增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的數據，備份較快，還原復雜

差異備份：僅備份最近一次完全備份以來變化的數據，備份較慢，還原簡單

?注意：二進制日志文件不應該與數據文件放在同一磁盤

 

冷、溫、熱備份

?冷備：讀寫操作均不可進行

?溫備：讀操作可執行；但寫操作不可執行

?熱備：讀寫操作均可執行

MyISAM：溫備，不支持熱備

InnoDB: 都支持

?物理和邏輯備份

? 物理備份：直接復制數據文件進行備份，與存儲引擎有關，占用較多的空間，速度快

? 邏輯備份：從數據庫中“導出”數據另存而進行的備份，與存儲引擎無關，占用空間少，速度慢，可能丟失精度

 

備份時需要考慮的因素

溫備的持鎖多久

備份產生的負載

備份過程的時長

恢復過程的時長

?備份什么

數據

二進制日志、InnoDB的事務日志

程序代碼（存儲過程、存儲函數、觸發器、事件調度器）

服務器的配置文件

 

設計備份方案

?數據集：完全+增量

?備份手段：物理，邏輯

?備份工具

?mysqldump：邏輯備份工具，適用所有存儲引擎，溫備；支持完全或部分備份；對InnoDB存儲引擎支持熱備

?cp, tar等復制歸檔工具：物理備份工具，適用所有存儲引擎；只支持冷備；完全和部分備份

?LVM的快照：先加鎖，做快照后解鎖，幾乎熱備；借助文件系統管理工具進行備份

?mysqlhotcopy：幾乎冷備；僅適用于MyISAM存儲引擎

 

備份工具的選擇：

?mysqldump+復制binlog：

mysqldump：完全備份

復制binlog中指定時間范圍的event：增量備份

?LVM快照+復制binlog：

LVM快照：使用cp或tar等做物理備份；完全備份

復制binlog中指定時間范圍的event：增量備份

?xtrabackup：由Percona提供支持對InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份

?MariaDB Backup： 從MariaDB 10.1.26開始集成，基于Percona XtraBackup 2.3.8實現

?mysqlbackup：熱備份， MySQL Enterprise Edition組件

 

邏輯備份工具：mysqldump, mydumper, phpMyAdmin

?Schema和數據存儲在一起、巨大的SQL語句、單個巨大的備份文件

?mysqldump工具：客戶端命令，通過mysql協議連接至mysqld服務器進行備份

mysqldump [OPTIONS] database [tables]

mysqldump [OPTIONS] –B DB1 [DB2 DB3…]

mysqldump [OPTIONS] –A [OPTIONS]

?mysqldump參考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

 

mysqldump常見選項：

?-A， –all-databases 備份所有數據庫，含create database

?-B , –databases db_name… 指定備份的數據庫，包括create database語句

?-E, –events：備份相關的所有event scheduler

?-R, –routines：備份所有存儲過程和存儲函數

?–triggers：備份表相關的觸發器，默認啟用,用–skip-triggers，不備份觸發器

?–master-data[=#]： 此選項須啟用二進制日志

1：所備份的數據之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定#，默認為1

2：記錄為注釋的CHANGE MASTER TO語句

此選項會自動關閉–lock-tables功能，自動打開–lock-all-tables功能（除非開啟–single-transaction）

 

mysqldump常見選項

?-F, –flush-logs ：備份前滾動日志，鎖定表完成后，執行flush logs命令,生成新的二進制日志文件，配合-A時，會導致刷新多次數據庫，在同一時刻執行轉儲和日志刷新，則應同時使用–flush-logs和-x，–master-data或-single-transaction,此時只刷新一次

建議：和-x，–master-data或 –single-transaction一起使用

?–compact 去掉注釋，適合調試，生產不使用

?-d, –no-data 只備份表結構

?-t, –no-create-info 只備份數據,不備份create table

?-n,–no-create-db 不備份create database，可被-A或-B覆蓋

?–flush-privileges 備份mysql或相關時需要使用

?-f, –force 忽略SQL錯誤，繼續執行

?–hex-blob 使用十六進制符號轉儲二進制列（例如，“abc”變為0x616263），受影響的數據類型包括BINARY， VARBINARY，BLOB，BIT

?-q, –quick 不緩存查詢，直接輸出，加快備份速度

 

?MyISAM備份選項：

支持溫備；不支持熱備，所以必須先鎖定要備份的庫，而后啟動備份操作

鎖定方法如下：

-x,–lock-all-tables：加全局讀鎖，鎖定所有庫的所有表，同時加–single-transaction或–lock-tables選項會關閉此選項功能

注意：數據量大時，可能會導致長時間無法并發訪問數據庫

-l,–lock-tables：對于需要備份的每個數據庫，在啟動備份之前分別鎖定其所有表，默認為on,–skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能會造成數據不一致

注：以上選項對InnoDB表一樣生效，實現溫備，但不推薦使用

 

?InnoDB備份選項：

支持熱備，可用溫備但不建議用

–single-transaction

此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執行START TRANSACTION指令，并且在備份期間，不允許對數據進行修改操作

此選項和–lock-tables（此選項隱含提交掛起的事務）選項是相互排斥

備份大型表時，建議將–single-transaction選項和–quick結合一起使用

 

 

生產備份策略

?InnoDB建議備份策略

mysqldump –uroot –A –F –E –R –single-transaction –master-data=1 –flush-privileges –triggers –hex-blob >$BACKUP_DIR/fullbak_$BACKUP_TIME.sql

?MyISAM建議備份策略

mysqldump –uroot –A –F –E –R –x –master-data=1 –flush-privileges –triggers –hex-blob >$BACKUP_DIR/fullbak_$BACKUP_TIME.sql

 

 

備份和恢復

?基于LVM的備份

?(1) 請求鎖定所有表

mysql> FLUSH TABLES WITH READ LOCK;

?(2) 記錄二進制日志文件及事件位置

mysql> FLUSH LOGS;

mysql> SHOW MASTER STATUS;

mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE

?(3) 創建快照

lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

?(4) 釋放鎖

mysql> UNLOCK TABLES;

?(5) 掛載快照卷，執行數據備份

?(6) 備份完成后，刪除快照卷

?(7) 制定好策略，通過原卷備份二進制日志

 

 

xtrabackup

?Percona

官網：www.percona.com

percona-server

InnoDB –> XtraDB

?Xtrabackup

percona提供的mysql數據庫備份工具，惟一開源的能夠對innodb和xtradb數據庫進行熱備的工具

特點：

?(1)備份還原過程快速、可靠

?(2)備份過程不會打斷正在執行的事務

?(3)能夠基于壓縮等功能節約磁盤空間和流量

?(4)自動實現備份檢驗

?(5)開源，免費

 

 

Xtrabackup2.2版之前包括4個可執行文件：

Innobackupex: perl 腳本

Xtrabackup: C/C++ 編譯的二進制

Xbcrypt:加解密

Xbstream:支持并發寫的流文件格式

?xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表，和 mysqld server 沒有交互；innobackupex 腳本用來備份非 InnoDB 表，同時會調用 xtrabackup 命令來備份 InnoDB 表，還會和 mysqld server 發送命令進行交互，如加讀鎖（FTWRL）、獲取位點（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一層封裝實現的。

?雖然目前一般不用 MyISAM 表，只是mysql 庫下的系統表是 MyISAM 的，因此備份基本都通過 innobackupex 命令進行

 

 

Xtrabackup的新版變化

?xtrabackup版本升級到2.4后，相比之前的2.1有了比較大的變化：innobackupex 功能全部集成到 xtrabackup 里面，只有一個 binary程序，另外為了兼容考慮，innobackupex作為 xtrabackup 的軟鏈接，即xtrabackup現在支持非Innodb表備份，并且Innobackupex在下一版本中移除，建議通過xtrabackup替換innobackupex

?xtrabackup安裝：

yum install percona-xtrabackup 在EPEL源中

最新版本下載安裝：

https://www.percona.com/downloads/XtraBackup/LATEST/

 

 

innobakupex備份

?使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命名的目錄中,在備份時，innobackupex還會在備份目錄中創建如下文件：

?(1)xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日志序列號)范圍信息,每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的

?(2)xtrabackup_binlog_info：mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置

 

 

(3)xtrabackup_binlog_pos_innodb：二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position

?(4)xtrabackup_binary：備份中用到的xtrabackup的可執行文件

?(5)backup-my.cnf：備份命令用到的配置選項信息

在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據

 

 

Xtrabackup用法

?備份：innobackupex [option] BACKUP-ROOT-DIR

?選項說明：

?–user：該選項表示備份賬號

?–password：該選項表示備份的密碼

?–host：該選項表示備份數據庫的地址

?–databases：該選項接受的參數為數據名，如果要指定多個數據庫，彼此間需要以空格隔開；如：”xtra_test dba_test”，同時，在指定某數據庫時，也可以只指定其中的某張表。如：”mydatabase.mytable”。該選項對innodb引擎表無效，還是會備份所有innodb表

?–defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選項的位置

?–incremental：該選項表示創建一個增量備份，需要指定–incremental-basedir

?–incremental-basedir：該選項表示接受了一個字符串參數指定含有full backup的目錄為增量備份的base目錄，與–incremental同時使用

?–incremental-dir：該選項表示增量備份的目錄

?–include=name：指定表名，格式：databasename.tablename

 

 

Prepare：innobackupex –apply-log [option] BACKUP-DIR

?選項說明：

?–apply-log：一般情況下,在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。此選項作用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處于一致性狀態

?–use-memory：該選項表示和–apply-log選項一起使用，prepare 備份的時候，xtrabackup做crash recovery分配的內存大小，單位字節。也可(1MB,1M,1G,1GB)，推薦1G

?–defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選項的位置

?–export：表示開啟可導出單獨的表之后再導入其他Mysql中

?–redo-only：這個選項在prepare base full backup，往其中merge增量備份時候使用

 

 

還原：innobackupex –copy-back [選項] BACKUP-DIR

?innobackupex –move-back [選項] [–defaults-group=GROUP-NAME] BACKUP-DIR

?選項說明：

?–copy-back：做數據恢復時將備份數據文件拷貝到MySQL服務器的datadir

?–move-back：這個選項與–copy-back相似，唯一的區別是它不拷貝文件，而是移動文件到目的地。這個選項移除backup文件，用時候必須小心。使用場景：沒有足夠的磁盤空間同事保留數據文件和Backup副本

 

 

還原注意事項：

?1.datadir目錄必須為空。除非指定innobackupex –force-non-empty-directorires選項指定，否則–copy-backup選項不會覆蓋

?2.在restore之前,必須shutdown MySQL實例，你不能將一個運行中的實例restore到datadir目錄中

?3.由于文件屬性會被保留，大部分情況下你需要在啟動實例之前將文件的屬主改為mysql，這些文件將屬于創建備份的用戶

chown -R mysql:mysql /data/mysql

以上需要在用戶調用innobackupex之前完成

–force-non-empty-directories：指定該參數時候，使得innobackupex –copy-back或–move-back選項轉移文件到非空目錄，已存在的文件不會被覆蓋。如果–copy-back和–move-back文件需要從備份目錄拷貝一個在datadir已經存在的文件，會報錯失敗

 

 

MySQL復制

?擴展方式： Scale Up ，Scale Out

?MySQL的擴展

復制：每個節點都有相同的數據集

向外擴展

二進制日志

單向

?復制的功用：

數據分布

負載均衡讀

備份

高可用和故障切換

MySQL升級測試

 

 

主從復制線程：

?主節點：

dump Thread：為每個Slave的I/O Thread啟動一個dump線程，用于向其發送binary log events

?從節點：

I/O Thread：向Master請求二進制日志事件，并保存于中繼日志中

SQL Thread：從中繼日志中讀取日志事件，在本地完成重放

?跟復制功能相關的文件：

?master.info：用于保存slave連接至master時的相關信息，例如賬號、密碼、服務器地址等

?relay-log.info：保存在當前slave節點上已經復制的當前二進制日志和本地replay log日志的對應關系

 

主從復制特點：

?異步復制

?主從數據不一致比較常見

?復制架構：

Master/Slave, Master/Master, 環狀復制

一主多從

從服務器還可以再有從服務器

一從多主:適用于多個不同數據庫

?復制需要考慮二進制日志事件記錄格式

STATEMENT（5.0之前）、ROW（5.1之后，推薦）、MIXED

?各種復制模型實戰：

主從、主主、半同步復制、復制過濾器

 

 

主從配置過程：

參看：https://mariadb.com/kb/en/library/setting-up-replication/

https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

主節點：

(1) 啟動二進制日志

[mysqld]

log_bin=mysql-bin

(2) 為當前節點設置一個全局惟的ID號

[mysqld]

server_id=#

(3) 創建有復制權限的用戶賬號

GRANT REPLCATION SLAVE ON *.* TO ‘repluser’@’HOST’ IDENTIFIED BY ‘replpass

 

從節點配置：

?(1) 啟動中繼日志

[mysqld]

server_id=# 為當前節點設置一個全局惟的ID號

relay_log=relay-log relay log的文件路徑，默認值hostname-relay-bin

relay_log_index=relay-log.index 默認值hostname-relay-bin.index

?(2) 使用有復制權限的用戶賬號連接至主服務器，并啟動復制線程

mysql> CHANGE MASTER TO MASTER_HOST=’host’, MASTER_USER=’repluser’, MASTER_PASSWORD=’replpass’, MASTER_LOG_FILE=’mysql-bin.xxxxx’, MASTER_LOG_POS=#;

mysql> START SLAVE [IO_THREAD|SQL_THREAD];

 

 

如果主節點已經運行了一段時間，且有大量數據時，如何配置并啟動slave節點

?通過備份恢復數據至從服務器

?復制起始位置為備份時，二進制日志文件及其POS

?如果要啟用級聯復制,需要在從服務器啟用以下配置

[mysqld]

log_bin

log_slave_updates

 

 

復制架構中應該注意的問題：

?1、限制從服務器為只讀

?在從服務器上設置read_only=ON

注意：此限制對擁有SUPER權限的用戶均無效

?阻止所有用戶, 包括主服務器復制的更新

mysql> FLUSH TABLES WITH READ LOCK

 

 

2、如何保證主從復制的事務安全

參看https://mariadb.com/kb/en/library/server-system-variables/

?在master節點啟用參數：

sync_binlog=1 每次寫后立即同步二進制日志到磁盤，性能差

如果用到的為InnoDB存儲引擎：

innodb_flush_logs_at_trx_commit=1

每次事務提交立即同步日志寫磁盤

innodb_support_xa=ON 默認值，分布式事務MariaDB10.3.0廢除

sync_master_info=# 多少次事件后master.info同步到磁盤

?在slave節點啟用服務器選項：

–skip_slave_start=ON 不自動啟動slave

?在slave節點啟用參數：

sync_relay_log=# #次寫后同步relay log到磁盤

sync_relay_log_info=#多個次事務后同步relay-log.info到磁盤

 

 

主主復制

?主主復制：互為主從

?容易產生的問題：數據不一致；因此慎用

?考慮要點：自動增長id

配置一個節點使用奇數id

auto_increment_offset=1 開始點

auto_increment_increment=2 增長幅度

另一個節點使用偶數id

auto_increment_offset=2

auto_increment_increment=2

 

主主復制的配置步驟：

(1) 各節點使用一個惟一server_id

(2) 都啟動binary log和relay log

(3) 創建擁有復制權限的用戶賬號

(4) 定義自動增長id字段的數值范圍各為奇偶

(5) 均把對方指定為主節點，并啟動復制線程

 

 

半同步復制

?默認情況下，MySQL的復制功能是異步的，異步復制可以提供最佳的性能，主庫把binlog日志發送給從庫即結束，并不驗證從庫是否接收完畢。這意味著當主服務器或從服務器端發生故障時，有可能從服務器沒有接收到主服務器發送過來的binlog日志，這就會造成主服務器和從服務器的數據不一致，甚至在恢復時造成數據的丟失

 

半同步復制實現：

?主服務器配置:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;

mysql> SET GLOBAL VARIABLES rpl_semi_sync_master_enabled=1;

mysql> SHOW GLOBAL VARIABLES LIKE ‘%semi%’;

mysql> SHOW GLOBAL STATUS LIKE ‘%semi%‘;

?從服務器配置:

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;

 

 

MySQL復制

?復制過濾器：

讓從節點僅復制指定的數據庫，或指定數據庫的指定表

?兩種實現方式：

?(1) 主服務器僅向二進制日志中記錄與特定數據庫(特定表)相關的事件

問題：時間還原無法實現；不建議使用

binlog_do_db= 數據庫白名單列表，用逗號分隔

binlog_ignore_db= 數據庫黑名單列表，和前項不要同時使用

?(2) 從服務器SQL_THREAD在replay中繼日志中的事件時，僅讀取與特定數據庫(特定表)相關的事件并應用于本地

問題：會造成網絡及磁盤IO浪費

 

 

復制過濾器從服務器上的相關設置

?replicate_do_db= ????????????????????????指定復制庫的白名單

?replicate_ignore_db= ????????????????????指定復制庫黑名單

?replicate_do_table= ?????????????????????指定復制表的白名單

?replicate_ignore_table= ?????????????????指定復制表的黑名單

?replicate_wild_do_table= foo%.bar% ??????支持通配符

?replicate_wild_ignore_table=

 

 

MySQL復制加密

?基于SSL復制：

在默認的主從復制過程或遠程連接到MySQL/MariaDB所有的鏈接通信中的數據都是明文的，外網里訪問數據或則復制，存在安全隱患。通過SSL/TLS加密的方式進行復制的方法，來進一步提高數據的安全性

?配置實現：

參看：https://mariadb.com/kb/en/library/replication-with-secure-connections/

?主服務器開啟SSL：[mysqld] 加一行ssl

?master配置證書和私鑰；并且創建一個要求必須使用SSL連接的復制賬號

?slave端使用CHANGER MASTER TO 命令時指明ssl相關選項

 

 

?Master配置

[mysqld]

log-bin

server_id=1

ssl

ssl-ca=/etc/my.cnf.d/ssl/cacert.pem

ssl-cert=/etc/my.cnf.d/ssl/master.crt

ssl-key=/etc/my.cnf.d/ssl/master.key

 

 

Slave配置

mysql>CHANGE MASTER TO

MASTER_HOST=’MASTERIP’,

MASTER_USER=’rep’,

MASTER_PASSWORD=’centos’,

MASTER_LOG_FILE=’mariadb-bin.000001′,

MASTER_LOG_POS=245,

MASTER_SSL=1,

MASTER_SSL_CA = ‘/etc/my.cnf.d/ssl/cacert.pem’,

MASTER_SSL_CERT = ‘/etc/my.cnf.d/ssl/slave.crt’,

MASTER_SSL_KEY = ‘/etc/my.cnf.d/ssl/slave.key’;

 

 

MySQL復制

?復制的監控和維護：

?(1) 清理日志

PURGE

?(2) 復制監控

SHOW MASTER STATUS

SHOW BINLOG EVENTS

SHOW BINARY LOGS

SHOW SLAVE STATUS

SHOW PROCESSLIST

?(3) 從服務器是否落后于主服務

Seconds_Behind_Master: 0

?(4) 如何確定主從節點數據是否一致

percona-tools

?(5) 數據不一致如何修復

刪除從數據庫，重新復制

 

 

MySQL高可用

?Master HA或多主模型

?MMM: Multi Master MySQL，基于主從復制實現

?MHA：Master HA，對主節點進行監控，可實現自動故障轉移至其它從節點；通過提升某一從節點為新的主節點，基于主從復制實現，還需要客戶端配合實現，目前MHA主要支持一主多從的架構，要搭建MHA,要求一個復制集群中必須最少有三臺數據庫服務器，一主二從，即一臺充當master，一臺充當備用master，另外一臺充當從庫，出于機器成本的考慮，淘寶進行了改造，目前淘寶TMHA已經支持一主一從

https://code.google.com/archive/p/mysql-master-ha/

?Galera Cluster：wresp

通過wresp協議在全局實現復制；任何一節點都可讀寫，不需要主從復制，實現多主可讀可寫

 

 

MHA工作原理

?1 從宕機崩潰的master保存二進制日志事件（binlog events）

?2 識別含有最新更新的slave

?3 應用差異的中繼日志（relay log）到其他的slave

?4 應用從master保存的二進制日志事件（binlog events）

?5 提升一個slave為新的master

?6 使其他的slave連接新的master進行復制

 

 

?MHA軟件由兩部分組成，Manager工具包和Node工具包

?Manager工具包主要包括以下幾個工具：

?masterha_check_ssh 檢查MHA的SSH配置狀況

?masterha_check_repl 檢查MySQL復制狀況

?masterha_manger 啟動MHA

?masterha_check_status 檢測當前MHA運行狀態

?masterha_master_monitor 檢測master是否宕機

?masterha_master_switch 制故障轉移（自動或手動）

?masterha_conf_host 添加或刪除配置的server信息

 

 

?Node工具包：這些工具通常由MHA Manager的腳本觸發，無需人為操作）主要包括以下幾個工具：

?save_binary_logs 保存和復制master的二進制日志

?apply_diff_relay_logs 識別差異的中繼日志事件并將其差異的事件應用于其他的slave

?filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用這個工具）

?purge_relay_logs 清除中繼日志（不會阻塞SQL線程）

?注意：為了盡可能的減少主庫硬件損壞宕機造成的數據丟失，因此在配置MHA的同時建議配置成MySQL 5.5的半同步復制

 

 

自定義擴展：

?secondary_check_script：通過多條網絡路由檢測master的可用性

?master_ip_ailover_script：更新Application使用的masterip

?shutdown_script：強制關閉master節點

?report_script：發送報告

?init_conf_load_script：加載初始配置參數

?master_ip_online_change_script：更新master節點ip地址

?配置文件：

global配置，為各application提供默認配置

application配置：為每個主從復制集群

 

 

實現MHA

?在管理節點上安裝兩個包：

mha4mysql-manager

mha4mysql-node

?在被管理節點安裝：

mha4mysql-node

 

 

在管理節點建立配置文件

vim /etc/mastermha/app1.cnf

[server default]

user=mhauser

password=centos

manager_workdir=/data/mastermha/app1/

manager_log=/data/mastermha/app1/manager.log

remote_workdir=/data/mastermha/app1/

ssh_user=root

repl_user=repluser

repl_password=centos

ping_interval=1

[server1]

hostname=192.168.27.17

candidate_master=1

[server2]

hostname=192.168.27.27

candidate_master=1

[server3]

hostname=192.168.27.37

 

 

實現MHA

?實現Master

vim /etc/my.cnf

[mysqld]

log-bin

server_id=1

innodb_file_per_table

skip_name_resolve=1

mysql>show master logs

mysql>grant replication slave on *.* to repluser@’192.168.8.%’ identified by ‘centos’;

mysql>grant all on *.* to mhauser@‘192.168.8.%’ identified by ‘magedu‘;

 

 

實現slave

[mysqld]

server_id=2 不同節點此值各不相同

log-bin

read_only

relay_log_purge=0

skip_name_resolve=1

innodb_file_per_table

mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP’, MASTER_USER=’repluser’, MASTER_PASSWORD=‘magedu’, MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=245;

 

 

在所有節點實現相互之間ssh key驗證

?Mha驗證和啟動

masterha_check_ssh –conf=/etc/mastermha/app1.cnf

masterha_check_repl –conf=/etc/mastermha/app1.cnf

masterha_manager –conf=/etc/mastermha/app1.cnf

?排錯日志：

/data/mastermha/app1/manager.log

 

 

Galera Cluster

?Galera Cluster：集成了Galera插件的MySQL集群，是一種新型的，數據不共享的，高度冗余的高可用方案，目前Galera Cluster有兩個版本，分別是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架構，是一個既穩健，又在數據一致性、完整性及高性能方面有出色表現的高可用解決方案

右圖圖示：三個節點組成了一個集群，與普通的主從架構不同，它們都可以作為主節點，三個節點是對等的，稱為multi-master架構，當有客戶端要寫入或者讀取數據時，連接哪個實例都是一樣的，讀到的數據是相同的，寫入某一個節點之后，集群自己會將新數據同步到其它節點上面，這種架構不共享任何數據，是一種高冗余架構

 

 

Galera Cluster特點

?多主架構：真正的多點讀寫的集群，在任何時候讀寫數據，都是最新的。

?同步復制：集群不同節點之間數據同步，沒有延遲，在數據庫掛掉之后，數據不會丟失。

?并發復制：從節點APPLY數據時，支持并行執行，更好的性能

?故障切換：在出現數據庫故障時，因支持多點寫入，切換容易

?熱插拔：在服務期間，如果數據庫掛了，只要監控程序發現的夠快，不可服務時間就會非常少。在節點故障期間，節點本身對集群的影響非常小

?自動節點克?。涸谛略龉濣c，或者停機維護時，增量數據或者基礎數據不需要人工手動備份提供，Galera Cluster會自動拉取在線節點數據，最終集群會變為一致

?對應用透明：集群的維護，對應用程序是透明的

 

 

Galera Cluster官方文檔：

http://galeracluster.com/documentation-webpages/galera-documentation.pdf

http://galeracluster.com/documentation-webpages/index.html

https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

?Galera Cluster包括兩個組件

?Galera replication library (galera-3)

?WSREP：MySQL擴展Write Set Replication API

?wresp復制實現：

?percona-cluster

?MariaDB-Cluster

?注意：都至少需要三個節點，不能安裝mariadb-server

 

 

MySQL復制

?yum install MariaDB-Galera-server

?Vim /etc/my.cnf.d/server.cnf

wsrep_provider = /usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address=”gcomm://172.16.0.7,172.16.0.17,172.16.0.27″

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

下面配置可選項

wsrep_cluster_name = ‘mycluster‘默認my_wsrep_cluster

wsrep_node_name = ‘node1′

wsrep_node_address = ‘172.16.0.7’

 

 

MySQL復制

?首次啟動時，需要初始化集群，在其中一個節點上執行命令：

/etc/init.d/mysql start –wsrep-new-cluster

?而后正常啟動其它節點

service mysql start

?查看集群中相關系統變量和狀態變量：

SHOW VARIABLES LIKE ‘wsrep_%‘;

SHOW STATUS LIKE ‘wsrep_%‘;

SHOW STATUS LIKE ‘wsrep_cluster_size‘;

 

 

讀寫分離

?讀寫分離應用：

?mysql-proxy：Oracle

https://downloads.mysql.com/archives/proxy/

?Atlas：Qihoo

https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

?dbproxy：美團

https://github.com/Meituan-Dianping/DBProxy

?Amoeba：

https://sourceforge.net/projects/amoeba/

 

 

復制的問題和解決方案

?復制的問題和解決方案：

(1) 數據損壞或丟失

Master：MHA + semi repl

Slave： 重新復制

(2) 混合使用存儲引擎

MyISAM：不支持事務

InnoDB：支持事務

(3) 不惟一的server id

重新復制

(4) 復制延遲

需要額外的監控工具的輔助

一從多主:mariadb10版后支持

多線程復制：對多個數據庫復制

 

 

性能衡量指標

?數據庫服務衡量指標：

qps: query per second

tps: transaction per second

?壓力測試工具：

?mysqlslap

?Sysbench：功能強大

https://github.com/akopytov/sysbench

?tpcc-mysql

?MySQL Benchmark Suite

?MySQL super-smack

?MyBench

 

 

MYSQL壓力測試

?Mysqlslap：來自于mariadb包，測試的過程默認生成一個mysqlslap的schema,生成測試表t1，查詢和插入測試數據，mysqlslap庫自動生成，如果已經存在則先刪除。用–only-print來打印實際的測試過程，整個測試完成后不會在數據庫中留下痕跡

?使用格式：mysqlslap [options]

?常用參數 [options] 說明：

?–auto-generate-sql, -a 自動生成測試表和數據，表示用mysqlslap工具自己生成的SQL腳本來測試并發壓力

?–auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認)

 

–auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持

?–number-char-cols=N, -x N 自動生成的測試表中包含多少個字符類型的列，默認1

?–number-int-cols=N, -y N 自動生成的測試表中包含多少個數字類型的列，默認1

?–number-of-queries=N 總的測試查詢次數(并發客戶數×每客戶查詢次數)

?–query=name,-q 使用自定義腳本執行測試，例如可以調用自定義的存儲過程或者sql語句來執行測試

?–create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database

?–commint=N 多少條DML后提交一次

 

–compress, -C 如服務器和客戶端都支持壓縮，則壓縮信息

?–concurrency=N, -c N 表示并發量，即模擬多少個客戶端同時執行select。可指定多個值，以逗號或者–delimiter參數指定值做為分隔符。如：–concurrency=100,200,500

?–engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個，用分隔符隔開。例如：–engines=myisam,innodb

?–iterations=N, -i N 測試執行的迭代次數，代表要在不同并發環境下，各自運行測試多少次。

?–only-print 只打印測試語句而不實際執行。

?–detach=N 執行N條語句后斷開重連

?–debug-info, -T 打印內存和CPU的相關信息

 

 

mysqlslap示例

?單線程測試

mysqlslap -a -uroot -pmagedu

?多線程測試。使用–concurrency來模擬并發連接

mysqlslap -a -c 100 -uroot -pmagedu

?迭代測試。用于需要多次執行測試得到平均值

mysqlslap -a -i 10 -uroot -pmagedu

mysqlslap —auto-generate-sql-add-autoincrement -a

mysqlslap -a –auto-generate-sql-load-type=read

mysqlslap -a –auto-generate-secondary-indexes=3

mysqlslap -a –auto-generate-sql-write-number=1000

mysqlslap –create-schema world -q “select count(*) from City”

mysqlslap -a -e innodb -uroot -pmagedu

mysqlslap -a –number-of-queries=10 -uroot -pmagedu

 

 

測試同時不同的存儲引擎的性能進行對比：

mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info -uroot -pmagedu

?執行一次測試，分別50和100個并發，執行1000次總查詢：

mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -pmagedu

?50和100個并發分別得到一次測試結果(Benchmark)，并發數越多，執行完所有查詢的時間越長。為了準確起見，可以多迭代測試幾次:

mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info -uroot -pmagedu