運維基礎-MYSQL數據庫-筆記

序

????欠10年前自己的一份筆記，獻給今后的自己。

數據庫介紹

數據的時代

涉及的數據量大
數據不隨程序的結束而消失
數據被多個應用程序共享
大數據

數據庫的發展史

萌芽階段：文件系統
使用磁盤文件來存儲數據
初級階段：第一代數據庫
出現了網狀模型、層次模型的數據庫
中級階段：第二代數據庫
關系型數據庫和結構化查詢語言
高級階段：新一代數據庫
“關系-對象”型數據庫

文件管理系統的缺點

編寫應用程序不方便
數據冗余不可避免
應用程序依賴性
不支持對文件的并發訪問
數據間聯系弱
難以按用戶視圖表示數據
無安全控制功能

數據庫管理系統的優點

相互關聯的數據的集合
較少的數據冗余
程序與數據相互獨立
保證數據的安全、可靠
最大限度地保證數據的正確性
數據可以并發使用并能同時保證一致性

數據庫管理系統

據庫是數據的匯集，它以一定的組織形式存于存儲介質上
DBMS是管理數據庫的系統軟件，它實現數據庫系統的各種功能。是數據庫系統的核心
DBA：負責數據庫的規劃、設計、協調、維護和管理等工作
應用程序指以數據庫為基礎的應用程序

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

數據定義
數據處理
數據安全
數據備份

數據庫系統的架構

單機架構
大型主機/終端架構
主從式架構（C/S）
分布式架構

????

關系型數據庫

關系：關系就是二維表。并滿足如下性質：
????????表中的行、列次序并不重要
行row：表中的每一行，又稱為一條記錄
列column：表中的每一列，稱為屬性，字段
主鍵（Primary key）：用于惟一確定一個記錄的字段
域domain：屬性的取值范圍，如，性別只能是‘男’和‘女’兩個值
RDBMS：
????????MySQL: MySQL, MariaDB, Percona Server
????????PostgreSQL: 簡稱為pgsql，EnterpriseDB
????????Oracle：
????????MSSQL：
????????DB2:
事務transaction：多個操作被當作一個整體對待
????????ACID:
????????????????A: 原子性
????????????????C：一致性
????????????????I: 隔離性
????????????????D：持久性

聯系類型

聯系的類型
? 一對一聯系(1:1)
? 一對多聯系(1:n)
? 多對多聯系(m:n)

數據三要素

數據結構：包括兩類
? 一類是與數據類型、內容、性質有關的對象，比如關系模型中的域、屬性和關系等；
? 另一類是與數據之間聯系有關的對象，它從數據組織層表達數據記錄與字段的結構
數據的操作：
? 數據提取：在數據集合中提取感興趣的內容。SELECT
? 數據更新：變更數據庫中的數據。INSERT、DELETE、UPDATE
數據的約束條件：是一組完整性規則的集合
? 實體（行）完整性 Entity integrity
? 域（列）完整性 Domain Integrity
? 參考完整性 Referential Integrity

簡易數據規劃流程

第一階段：收集數據，得到字段
1、收集必要且完整的數據項
2、轉換成數據表的字段
第二階段：把字段分類，歸入表，建立表的關聯
1、關聯：表和表間的關系
2、分割數據表并建立關聯的優點
3、節省空間
4、減少輸入錯誤
5、方便數據修改
第三階段：
1、規范化數據庫

數據庫的正規化分析

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，一個表可以存在多個
? 外鍵：一個表中的某字段可填入的數據取決于另一個表的主鍵或唯一鍵已有的數據
? 檢查：字段值在一定范圍內

基本概念

索引：將表中的一個或多個字段中的數據復制一份另存，并且此些需要按特定次序排序存儲
關系運算：
????????選擇：挑選出符合條件的行
????????投影：挑選出需要的字段
????????連接：表間字段的關聯

MySQL

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/
http://mariadb.org/
官方文檔
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

MYSQL的特性

插件式存儲引擎：也稱為“表類型”，存儲管理器有多種實現版本，功能和特性可能均略有差別；用戶可根據需要靈活選擇,Mysql5.5.5開始innoDB引擎是MYSQL默認引擎
????????MyISAM ==> Aria
????????InnoDB ==> XtraDB
單進程，多線程
諸多擴展和新特性
提供了較多測試組件
開源

安裝MYSQL

Mariadb安裝方式：
1、源代碼：編譯安裝
2、二進制格式的程序包：展開至特定路徑，并經過簡單配置后即可使用
3、程序包管理器管理的程序包
????????CentOS安裝光盤
????????項目官方：
????????https://downloads.mariadb.org/mariadb/repositories/

RPM包安裝MySQL

RPM包安裝
????????CentOS 7：安裝光盤直接提供
????????????????mariadb-server 服務器包
????????????????mariadb 客戶端工具包
????????CentOS 6
提高安全性
????????mysql_secure_installation
????????? 設置數據庫管理員root口令
????????? 禁止root遠程登錄
????????? 刪除anonymous用戶帳號
????????? 刪除test數據庫

MariaDB程序

客戶端程序：
????????mysql: 交互式的CLI工具
????????mysqldump：備份工具，基于mysql協議向mysqld發起查詢請求，并將查得的所
有數據轉換成insert等寫操作語句保存文本文件中
????????mysqladmin：基于mysql協議管理mysqld
????????mysqlimport：數據導入工具
MyISAM存儲引擎的管理工具：
????????myisamchk：檢查MyISAM庫
????????myisampack：打包MyISAM表，只讀
服務器端程序
????????mysqld_safe
????????mysqld
????????mysqld_multi：多實例，示例：mysqld_multi --example

用戶賬號

mysql用戶賬號由兩部分組成：
????????‘USERNAME’@'HOST‘
說明：
????????HOST限制此用戶可通過哪些遠程主機連接mysql服務器
????????支持使用通配符：
????????????????% 匹配任意長度的任意字符
????????????????172.16.0.0/255.255.0.0 或 172.16.%.%
????????????????_ 匹配任意單個字符

Mysql 客戶端

mysql使用模式：
交互式模式：
????????可運行命令有兩類：
????????????????客戶端命令：
???????? ???????? ????????\h, help
???????? ???????? ????????\u，use
???????? ???????? ????????\s，status
???????? ???????? ????????\!，system
???????? ????????服務器端命令：
???????? ???????? ????????SQL, 需要語句結束符；
腳本模式：
????????mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
????????mysql> source /path/from/somefile.sql

Mysql客戶端

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 獲取程序默認使用的配置

socket地址

服務器監聽的兩種socket地址：
????????ip socket: 監聽在tcp的3306端口，支持遠程通信
????????unix sock: 監聽在sock文件上，僅支持本機通信
????????????????如：/var/lib/mysql/mysql.sock)
????????說明：host為localhost,127.0.0.1時自動使用unix sock

執行命令

運行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();

服務器端配置

服務器端(mysqld)：工作特性有多種配置方式
1、命令行選項：
2、配置文件：類ini格式
集中式的配置，能夠為mysql的各應用程序提供配置信息
????????[mysqld]
????????[mysqld_safe]
????????[mysqld_multi]
????????[mysql]
????????[mysqldump]
????????[server]
????????[client]
格式：parameter = value
說明：_和- 相同
????????1，ON，TRUE意義相同， 0，OFF，FALSE意義相同

配置文件

配置文件：
后面覆蓋前面的配置文件，順序如下：
????????? /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 選項

MairaDB配置

偵聽3306/tcp端口可以在綁定有一個或全部接口IP上
vim /etc/my.cnf
????????[mysqld]
????????skip-networking=1 關閉網絡連接，只偵聽本地客戶端，所有和服務器的交互
都通過一個socket實現，socket的配置存放在/var/lib/mysql/mysql.sock）可在/etc/my.cnf修改

通用二進制格式安裝過程

二進制格式安裝過程
(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-huge.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 ncurses-devel gnutls-devel libxml2-devel openssl-
devel libevent-devel libaio-devel
提示：如果出錯，執行rm -f CMakeCache.txt
做準備用戶和數據目錄
mkdir /data
useradd –r –s /bin/false –m –d /data/mysqldb/ mysql
tar xvf mariadb-10.2.15.tar.gz
cmake 編譯安裝
cd mariadb-10.2.15/
編譯選項:
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

關系型數據庫的常見組件

數據庫：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 修飾符, …)
字段信息
1、col type1
2、 PRIMARY KEY(col1,…)
3、 INDEX(col1, …)
4、 UNIQUE KEY(col1, …)
表選項：
1、 ENGINE [=] engine_name
????????SHOW ENGINES;查看支持的engine類型
2、 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支持多種列類型：
? 數值類型
? 日期/時間類型
? 字符串(字符)類型
? https://dev.mysql.com/doc/refman/5.5/en/data-types.html
選擇正確的數據類型對于獲得高性能至關重要，三大原則：
? 更小的通常更好，盡量使用可正確存儲數據的最小數據類型
? 簡單就好，簡單數據類型的操作通常需要更少的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
1、 BLOB和text存儲方式不同，TEXT以文本方式存儲，英文存儲區分大小寫，
而Blob是以二進制方式存儲，不分大小寫
2、 BLOB存儲的數據只能整體讀出
3、 TEXT可以指定字符集，BLOB不用指定字符集
6.日期時間類型
1、 date 日期 ‘2008-12-2’
2、 time 時間 ‘12:25:36’
3、 datetime 日期時間 ‘2008-12-2 22:06:44’
4、 timestamp 自動存儲記錄修改時間
5、 YEAR(2), YEAR(4)：年份
timestamp字段里的時間數據會隨其他字段修改的時候自動刷新，這個數據類型的字段可以存放這條記錄最后被修改的時間

修飾符

所有類型：
1、 NULL 數據列可包含NULL值
2、 NOT NULL 數據列不允許包含NULL值
3、 DEFAULT 默認值
4、 PRIMARY KEY 主鍵
5、 UNIQUE KEY 唯一鍵
6、 CHARACTER SET name 指定一個字符集
數值型
1、 AUTO_INCREMENT 自動遞增，適用于整數類型
2、 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

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 選項：-U|–safe-updates| --i-am-a-dummy

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]
視圖中的數據事實上存儲于“基表”中，因此，其修改操作也會針對基表實現；
????????其修改操作受基表限制

函數

函數：系統函數和自定義函數
????????系統函數: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!“;
示例：有參數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//
DELIMITER ;
為變量賦值語法
? 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
????????END//
SELECT now();
delimiter ;
CALL showTime;
創建含參存儲過程:只有一個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’;
修改密碼：
1、 mysql>SET PASSWORD FOR ‘user’@‘host’ = PASSWORD(‘password’);
2、 mysql>UPDATE mysql.user SET password=PASSWORD(‘password’)
????????WHERE clause;
????????此方法需要執行下面指令才能生效：
????????mysql> FLUSH PRIVILEGES;
3、 #mysqladmin -u root –poldpass password ‘newpass‘
忘記管理員密碼的解決辦法：
????????1、啟動mysqld進程時，為其使用如下選項：
????????--skip-grant-tables --skip-networking
2、使用UPDATE命令修改管理員密碼
3、關閉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;

MySQL體系結構

在這里插入圖片描述

存儲引擎

在這里插入圖片描述

MyISAM引擎特點：
? 不支持事務
? 表級鎖定
? 讀寫相互阻塞，寫入不能讀，讀時不能寫
? 只緩存索引
? 不支持外鍵約束
? 不支持聚簇索引
? 讀取數據較快，占用資源較少
? 不支持MVCC（多版本并發控制機制）高并發
? 崩潰恢復性較差
? MySQL5.5.5前默認的數據庫引擎
適用場景：只讀（或者寫較少）、表較小（可以接受長時間進行修復操作）
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 DataBase)環境，如數據倉庫
Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息，只支持SELECT和INSERT操作；支持行級鎖和專用緩存區
Federated聯合：用于訪問其它遠程MySQL服務器一個代理，它通過創建一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，而后完成數據存取，提供鏈接單獨MySQL服務器的能力，以便從多個物理服務器創建一個邏輯數據庫。非常適合分布式或數據集市環境
BDB：可替代InnoDB的事務引擎，支持COMMIT、ROLLBACK和其他事務特性
Cluster/NDB：MySQL的簇式數據庫引擎，尤其適合于具有高性能查找要求的應用程序，這類查找需求還要求具有最高的正常工作時間和可用性
CSV：CSV存儲引擎使用逗號分隔值格式將數據存儲在文本文件中。可以使用CSV引擎以CSV格式導入和導出其他軟件和應用程序之間的數據交換
BLACKHOLE ：黑洞存儲引擎接受但不存儲數據，檢索總是返回一個空集。該功能可用于分布式數據庫設計，數據自動復制，但不是本地存儲
example：“stub”引擎，它什么都不做。可以使用此引擎創建表，但不能將數據存儲在其中或從中檢索。目的是作為例子來說明如何開始編寫新的存儲引擎

管理存儲引擎

查看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/
注意：其中有些參數支持運行時修改，會立即生效；有些參數不支持，且只能通過修改配置文件，并重啟服務器程序生效；有些參數作用域是全局的，且不可改變；有些可以為每個用戶提供單獨（會話）的設置
獲取mysqld的可用選項列表：
????????mysqld --help –verbose
????????mysqld --print-defaults 獲取默認設置
獲取運行中的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;

性能優化

MySQL架構

在這里插入圖片描述

查詢的執行路徑

在這里插入圖片描述

查詢緩存

查詢緩存（ Query Cache ）原理：
????????緩存SELECT操作或預處理查詢的結果集和SQL語句，當有新的SELECT語句或預處理查詢語句請求，先去查詢緩存，判斷是否存在可用的記錄集，判斷標準：與緩存的SQL語句，是否完全一樣，區分大小寫
優缺點
????????不需要對SQL語句做任何解析和執行，當然語法解析必須通過在先，直接從Query Cache中獲得查詢結果，提高查詢性能查詢緩存的判斷規則，不夠智能，也即提高了查詢緩存的使用門檻，降低其效率；查詢緩存的使用，會增加檢查和清理Query Cache中記錄集的開銷
哪些查詢可能不會被緩存
1、查詢語句中加了SQL_NO_CACHE參數
2、查詢語句中含有獲得值的函數，包含自定義函數，如：NOW()
CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
3、對系統數據庫的查詢：mysql、information_schema 查詢語句中使用， SESSION級別變量或存儲過程中的局部變量
4、查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句，查詢語句中類似SELECT …INTO 導出數據的語句
5、對臨時表的查詢操作；存在警告信息的查詢語句；不涉及任何表或視圖的查詢語句；某用戶只有列級別權限的查詢語句
6、事務隔離級別為Serializable時，所有查詢語句都不能緩存
查詢緩存相關的服務器變量
1、 query_cache_min_res_unit: 查詢緩存中內存塊的最小分配單位，默認4k，較小值會減少浪費，但會導致更頻繁的內存分配操作，較大值會帶來浪費，會導致碎片過多，內存不足
2、 query_cache_limit：單個查詢結果能緩存的最大值，默認為1M，對于查詢結果過大而無法緩存的語句，建議使用SQL_NO_CACHE
3、 query_cache_size：查詢緩存總共可用的內存空間；單位字節，必須是1024的整數倍，最小值40KB，低于此值有警報
4、 query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結果，默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續從緩存返回數據；ON則表示不允許
5、 query_cache_type: 是否開啟緩存功能，取值為ON, OFF, DEMAND
SELECT語句的緩存控制
1、 SQL_CACHE: 顯式指定存儲查詢結果于緩存之中
2、 SQL_NO_CACHE: 顯式查詢結果不予緩存
query_cache_type參數變量：
1、 query_cache_type的值為OFF或0時，查詢緩存功能關閉
2、 query_cache_type的值為ON或1時，查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存，否則，不予緩存，顯式指定SQL_NO_CACHE，不予緩存，此為默認值
3、 query_cache_type的值為DEMAND或2時，查詢緩存功能按需進行，顯式指定SQL_CACHE的SELECT語句才會緩存；其它均不予緩存
4、參看：https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

在這里插入圖片描述

查詢緩存相關的狀態變量：SHOW GLOBAL STATUS LIKE ‘Qcache%';
1、 Qcache_free_blocks：處于空閑狀態 Query Cache中內存 Block 數
2、 Qcache_free_memory：處于空閑狀態的 Query Cache 內存總量
3、 Qcache_hits：Query Cache 命中次數
4、 Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，即沒有命中的次數
5、 Qcache_lowmem_prunes：當 Query Cache 內存容量不夠，需要刪除老的Query Cache 以給新的 Cache 對象使用的次數
6、 Qcache_not_cached：沒有被 Cache 的 SQL 數，包括無法被 Cache 的 SQL以及由于 query_cache_type 設置的不會被 Cache 的 SQL語句
7、 Qcache_queries_in_cache：在 Query Cache 中的 SQL 數量
8、 Qcache_total_blocks：Query Cache 中總的 Block

命中率和內存使用率估算

查詢緩存中內存塊的最小分配單位query_cache_min_res_unit ：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查詢緩存命中率：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
查詢緩存內存使用率：(query_cache_size – qcache_free_memory) /query_cache_size * 100%

索引

索引是特殊數據結構：定義在查找時作為查找條件的字段
優點：提高查詢速度，缺點：占用額外空間，影響插入速度
索引實現在存儲引擎
索引類型：
1、B+ TREE、HASH、R TREE
2、聚簇（集）索引、非聚簇索引：數據是否與索引存儲在一起
3、主鍵索引、輔助索引
3、稠密索引、稀疏索引：是否索引了每一個數據項
4、簡單索引、組合索引
????????左前綴索引：取前面的字符做索引
????????覆蓋索引：從索引中即可取出要查詢的數據，性能高

索引B+TREE

在這里插入圖片描述

B+ Tree索引：順序存儲，每一個葉子節點到根結點的距離是相同的；左前綴索引，適合查詢范圍類的數據
可以使用B-Tree索引的查詢類型：
????????全值匹配：精確所有索引列，如：姓wang，名xiaochun，年齡30
????????匹配最左前綴：即只使用索引的第一列，如：姓wang
????????匹配列前綴：只匹配一列值開頭部分，如：姓以w開頭的
????????匹配范圍值：如：姓ma和姓wang之間
????????精確匹配某一列并范圍匹配另一列：如：姓wang,名以x開頭的只訪問索引的查詢
B-Tree索引的限制：
? 如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結尾
? 不能跳過索引中的列：如：查找姓wang,年齡30的，只能使用索引第一列
? 如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如：姓wang,名x%,年齡30，只能利用姓和名上面的索引
特別提示：
? 索引列的順序和查詢語句的寫法應相匹配，才能更好的利用索引
? 為優化性能，可能需要針對相同的列但順序不同創建不同的索引來滿足不同類型的查詢需求

聚簇和非聚簇索引，主鍵和二級索引

在這里插入圖片描述

索引

高性能索引策略：
1、獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較符號的一側
2、左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估索引選擇性：不重復的索引值和數據表的記錄總數的比值
3、多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引
4、選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側
冗余和重復索引：（A），（A，B）即為冗余索引不好的索引使用策略，建議擴展索引，而非冗余

索引優化建議

只要列中含有NULL值，就最好不要在此例設置索引，復合索引如果有NULL值，此列在使用時也不會使用索引
盡量使用短索引，如果可以，應該制定一個前綴長度
對于經常在where子句使用的列，最好設置索引
對于有多個列where或者order by子句，應該建立復合索引
對于like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引
盡量不要在列上進行運算（函數操作和表達式操作）
盡量不要使用not in和<>操作

管理索引

創建索引：
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

日志

事務日志：transaction log
錯誤日志：error log
查詢日志：query log
慢查詢日志：slow query log
二進制日志：binary log
中繼日志：reley log
事務日志：transaction log
1、事務型存儲引擎自行管理和使用
????????redo log
????????undo log
2、 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
二進制日志記錄格式
1、二進制日志記錄三種格式
????????基于“語句”記錄：statement，記錄語句，默認模式
????????基于“行”記錄：row，記錄數據，日志量較大
????????混合模式：mixed, 讓系統自行判定該基于哪種方式進行
2、格式配置
????????show variables like ‘%binlog_format%’;
3、二進制日志文件的構成
有兩類文件
日志文件：mysql|mariadb-bin.文件名后綴，二進制格式
????????如： mysql-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式
二進制日志相關的服務器變量：
1、 sql_log_bin=ON|OFF：是否記錄二進制日志，默認ON
2、 log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩項都開啟才可
3、 binlog_format=STATEMENT|ROW|MIXED：二進制日志記錄的格式，默認STATEMENT
4、 max_binlog_size=1073741824：單個二進制日志文件的最大體積，到達最大值
????????會自動滾動，默認為1G
????????????????說明：文件達到上限時的大小未必為指定的精確值
5、 sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到磁盤
6、 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;

備份和還原

為什么要備份
災難恢復：硬件故障、軟件故障、自然災害、黑客攻擊、誤操作測試等數據丟失場景
備份注意要點
能容忍最多丟失多少數據
恢復數據需要在多長時間內完成
需要恢復哪些數據
還原要點
做還原測試，用于測試備份的可用性
還原演練
備份類型：
1、完全備份，部分備份
????????完全備份：整個數據集
????????部分備份：只備份數據子集，如部分庫或表
2、完全備份、增量備份、差異備份
????????增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的數據，備份較快，還原復雜
????????差異備份：僅備份最近一次完全備份以來變化的數據，備份較慢，還原簡單
注意：二進制日志文件不應該與數據文件放在同一磁盤
冷、溫、熱備份
1、冷備：讀寫操作均不可進行
2、溫備：讀操作可執行；但寫操作不可執行
3、熱備：讀寫操作均可執行
????????MyISAM：溫備，不支持熱備
????????InnoDB: 都支持
物理和邏輯備份
1、物理備份：直接復制數據文件進行備份，與存儲引擎有關，占用較多的空間，速度快
2、邏輯備份：從數據庫中“導出”數據另存而進行的備份，與存儲引擎無關，占用空間少，速度慢，可能丟失精度
備份時需要考慮的因素
????????溫備的持鎖多久
????????????????備份產生的負載
????????????????備份過程的時長
????????恢復過程的時長
備份什么
????????????????數據
????????二進制日志、InnoDB的事務日志
????????程序代碼（存儲過程、存儲函數、觸發器、事件調度器）
????????服務器的配置文件
設計備份方案
1、數據集：完全+增量
2、備份手段：物理，邏輯
備份工具
1、 mysqldump：邏輯備份工具，適用所有存儲引擎，溫備；支持完全或部分備份；對InnoDB存儲引擎支持熱備
2、 cp, tar等復制歸檔工具：物理備份工具，適用所有存儲引擎；只支持冷備；完全和部分備份
3、 LVM的快照：先加鎖，做快照后解鎖，幾乎熱備；借助文件系統管理工具進行備份
4、 mysqlhotcopy：幾乎冷備；僅適用于MyISAM存儲引擎
備份工具的選擇：
1、 mysqldump+復制binlog：
????????mysqldump：完全備份
????????復制binlog中指定時間范圍的event：增量備份
2、 LVM快照+復制binlog：
????????LVM快照：使用cp或tar等做物理備份；完全備份復制binlog中指定時間范圍的event：增量備份
3、 xtrabackup：由Percona提供支持對InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份
4、 MariaDB Backup：從MariaDB 10.1.26開始集成，基于Percona XtraBackup2.3.8實現
5、 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常見選項
1、-F, - -flush-logs ：備份前滾動日志，鎖定表完成后，執行flush logs命令,生成新的二進制日志文件，配合-A時，會導致刷新多次數據庫，在同一時刻執行轉儲和日志刷新，則應同時使用- -flush-logs和-x，- -master-data或-single-transaction,此時只刷新一次建議：和-x，- -master-data或 - -single-transaction一起使用
2、- -compact 去掉注釋，適合調試，生產不使用
3、-d, - -no-data 只備份表結構
4、-t, - -no-create-info 只備份數據,不備份create table
5、-n,- -no-create-db 不備份create database，可被-A或-B覆蓋
6、- -flush-privileges 備份mysql或相關時需要使用
7、-f, - -force 忽略SQL錯誤，繼續執行
8、- -hex-blob 使用十六進制符號轉儲二進制列（例如，“abc”變為0x616263），受影響的數據類型包括BINARY， VARBINARY，BLOB，BIT
9、-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

MySQL復制

擴展方式： Scale Up ，Scale Out
MySQL的擴展
復制：每個節點都有相同的數據集
????????????????向外擴展
????????二進制日志
????????單向
復制的功用：
????????數據分布
????????負載均衡讀
????????備份
????????高可用和故障切換
????????MySQL升級測試

在這里插入圖片描述

MySQL垂直分區

在這里插入圖片描述

MySQL水平分片（Sharding）

在這里插入圖片描述

對應shard中查詢相關數據

在這里插入圖片描述

主從復制線程：
1、主節點：
????????dump Thread：為每個Slave的I/O Thread啟動一個dump線程，用于向其發送binary log
????????events
從節點：
????????I/O Thread：向Master請求二進制日志事件，并保存于中繼日志中
????????SQL Thread：從中繼日志中讀取日志事件，在本地完成重放
跟復制功能相關的文件：
1、 master.info：用于保存slave連接至master時的相關信息，例如賬號、密碼、服務器地址等
2、 relay-log.info：保存在當前slave節點上已經復制的當前二進制日志和本地replay log日志的對應關系
主從復制特點：
1、異步復制
2、主從數據不一致比較常見
復制架構：
????????Master/Slave, Master/Master, 環狀復制
????????一主多從
從服務器還可以再有從服務器

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

復制需要考慮二進制日志事件記錄格式
????????STATEMENT（5.0之前）、ROW（5.1之后，推薦）、MIXED
各種復制模型實戰：
????????主從、主主、半同步復制、復制過濾器

MySQL復制模型

在這里插入圖片描述

主從配置過程：
????????參看：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節點
1、通過備份恢復數據至從服務器
2、復制起始位置為備份時，二進制日志文件及其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/
1、在master節點啟用參數：
????????sync_binlog=1 每次寫后立即同步二進制日志到磁盤，性能差
????????如果用到的為InnoDB存儲引擎：
????????innodb_flush_logs_at_trx_commit=1
????????每次事務提交立即同步日志寫磁盤
????????innodb_support_xa=ON 默認值，分布式事務MariaDB10.3.0廢除
????????sync_master_info=# 多少次事件后master.info同步到磁盤
2、在slave節點啟用服務器選項：
????????- -skip_slave_start=ON 不自動啟動slave
3、在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日志，這就會造成主服務器和從服務器的數據不一致，甚至在恢復時造成數據的丟失

在這里插入圖片描述

半同步復制實現：
1、主服務器配置:
????????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%‘;
2、從服務器配置:
????????mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
????????mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;

讀寫分離

讀寫分離應用：
? 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/

關于馬哥教育

博客：http://mageedu.blog.51cto.com
主頁：http://www.magedu.com
QQ：1661815153, 113228115
QQ群：203585050, 279599283