溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
禁用郵件通知：
vi /etc/profile
在末尾添加
#禁止郵件提示 unset MAILCHECK
數據庫部署及引擎管理
數據庫簡介
數據庫技術構成
1.數據庫系統 DBS
A.數據庫管理系統（DataBase Management System， DBMS）: SQL(RDS): ORACLE、Oracle MySQL、MariaDB、Percona server、DB2 NoSQL: Redis、MongoDB、Memcache
B.DBA數據庫管理員
2.SQL語言（Structured Query Language 即結構化查詢語言）
A. DDL語句 數據庫定義語言： 數據庫、表、視圖、索引、存儲過程、函數， CREATE DROP
ALTER //開發人員
B. DML語句 數據庫操縱語言： 插入數據INSERT、刪除數據DELETE,drop、更新數據
UPDATE //開發人員
C. DQL語句 數據庫查詢語言： 查詢數據 SELECT
D. DCL語句 數據庫控制語言： 例如控制用戶的訪問權限GRANT、REVOKE
grant all on . root to root@localhost identified by '123'
3.數據訪問技術
A.ODBC PHP <.php>
B.JDBC JAVA <.jsp>
主 從
主IP
數據庫分類關系型：mysql、甲骨文、IBM、微軟
非關系型數據庫：mogoDB、redis、memcache (數據庫緩存服務器) 1 name
2 key
數據庫部署
Mysql的YUM安裝
1/52
mysql的官方網站：www.mysql.com mysql.org
點擊downloadshttps點擊最下面的mysql community 1.下載mysql的yum倉庫
[root@22e34e653991 /]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 2.安裝mysql的yum倉庫 [root@22e34e653991 /]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
3.修改安裝版本（方法一）
[root@22e34e653991 /]# yum repolist all | grep mysql 查看所有關于mysql的庫
[root@22e34e653991 /]# yum -y install yum-utils yum的工具包
[root@22e34e653991 /]# yum-config-manager --enable mysql57-community 將禁用的yum源庫啟用
[root@22e34e653991 /]# yum-config-manager --disable mysql80-community 將啟用的yum源庫禁用
4.安裝數據庫
[root@22e34e653991 /]# yum -y install mysql mysql-server
修改yum倉庫（方法二）
5.查看數據庫的初始密碼
[root@22e34e653991 /]# grep 'password' /var/log/mysqld.log
2019-07-13T15:14:31.176905Z 1 [Note] A temporary password is generated for root@localhost: k12zPB1r;2Ta
6.使用密碼登陸
[root@22e34e653991 /]# mysql -u root -p'k12zPB1r;2Ta'
7.修改密碼方法一：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'QianFeng@123';
方法二：
[root@22e34e653991 /]# mysqladmin -u root -p'k12zPB1r;2Ta password 'QianFeng@123'
密碼：大小寫有特殊字符數字
mysql使用弱密碼登陸
編輯mysql的配置文件
[root@mysql1 mysql]# vim /etc/my.cnf [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data validate_password=off 添加后可設置弱密碼強度
lower_case_table_names=1 不區分大小寫
Mysql的編譯安裝
源碼安裝
與二進制(RPM)發行版本相比，如果我們選擇了通過源代碼進行安裝，那么在安裝過程中我們能夠對MySQL所做的調整將會更多更靈活一些。因為通過源代碼編譯我們可以：
a)針對自己的硬件平臺選用合適的編譯器來優化編譯后的二進制代碼；
b)根據不同的軟件平臺環境調整相關的編譯參數；
c)針對我們特定應用場景選擇需要什么組件不需要什么組件；
d)根據我們的所需要存儲的數據內容選擇只安裝我們需要的字符集；
2/52
e)同一臺主機上面可以安裝多個MySQL；
f)等等其他一些可以根據特定應用場景所作的各種調整。
在源碼安裝給我們帶來更大靈活性的同時，同樣也給我們帶來了可能引入的隱患：
a)對編譯參數的不夠了解造成編譯參數使用不當可能使編譯出來的二進制代碼不夠穩定；
b)對自己的應用環境把握失誤而使用的優化參數可能反而使系統性能更差；
c)還有一個并不能稱之為隱患的小問題就是源碼編譯安裝將使安裝部署過程更為復雜，所花費的時間更長；
Mysql部署
1.準備編譯環境
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake
2.準備源碼包(www.baidu.com;官網)
3.清空系統殘留并創建新的賬戶 userdel -r mysql
yum -y remove mariadb mariadb-libs mariadb-server mariadb-devel rm -rf /etc/my*
rm -rf /var/lib/mysql rm -rf /var/log/mysql*
4.環境準備 groupadd mysql
useradd -r -g mysql -s /bin/nolgin mysql
5.解壓
tar xvf mysql-boost-5.7.26.tar.gz
6.配置
[root@mysql-5.7.26 ~]# cmake . \ -DWITH_BOOST=boost_1_59_0/ \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DSYSCONFDIR=/etc \指定安裝目錄配置文件的位置,默認就是etc -DMYSQL_DATADIR=/usr/local/mysql/data \數據目錄 錯誤日志文件 -DINSTALL_MANDIR=/usr/share/man \ 幫助文檔的目錄 -DMYSQL_TCP_PORT=3306 \ 默認端口號3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ 用來做網絡通信,啟動的時候才會產生 -DDEFAULT_CHARSET=utf8 \默認字符集
-DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \可以上下翻歷史命令 -DWITH_SSL=system \ -DWITH_EMBEDDED_SERVER=1 \ 嵌入式服務器
-DENABLED_LOCAL_INFILE=1 \ 支持從本機導入-DWITH_INNOBASE_STORAGE_ENGINE=1
默認存儲引擎
提示:boost也可以使用如下指令自動下載
-DDOWNLOAD_BOOST=1
7.編譯
make
8.安裝 make install
9.初始化
cd /usr/local/mysql 把這個刪了就相當于卸載
3/52
mkdir mysql-files
chown -R mysql.mysql /usr/local/mysql
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
初始化,只需要初始化一次
10.啟動MySQL-使用mysqld_safe bin/mysqld_safe --user=mysql & （后臺運行）
./bin/mysqladmin -u root -p'原密碼' password 123
11.使用客戶端測試
[root@mysql1 bin]#./bin/mysql -u root -p '密碼'
12.創建數據庫配置文件
[root@mysql1 bin]#vim /etc/my.cnf [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
編譯配置文件
cmake . \ -DWITH_BOOST=boost/boost_1_59_0/ \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DSYSCONFDIR=/etc \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DINSTALL_MANDIR=/usr/share/man \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_EMBEDDED_SERVER=1 \ -DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1
擴展
添加環境變量：
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
讓環境變量生效
source /etc/profile
設置開機啟動：
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld centos 6 :
chkconfig --add mysqld chkconfig mysqld on
4/52
service mysqld start
Mysql基礎
編譯安裝：
[root@47ed2bec974d mysql]# ls
COPYING README bin include mysql-test support-files COPYING-test README-test docs lib share
1、bin目錄
用于放置一些可執行文件，如mysql、mysqld、mysqlbinlog等。2、include目錄用于放置一些頭文件，如：mysql.h、mysql_ername.h等。3、lib目錄
用于放置一系列庫文件。
4、share目錄
用于存放字符集、語言等信息。
yum安裝：
/var/lib/mysql 存放數據文件 /usr/share/mysql 用于存放字符集、語言等信息。
數據庫存儲引擎
數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據。不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引
擎，還可以 獲得特定的功能。現在許多不同的數據庫管理系統都支持多種不同的數據引擎。MySQL的核心就是存儲引擎。
InnoDB存儲引擎:
InnoDB是事務型數據庫的首選引擎，支持事務安全表（ACID），支持行鎖定和外鍵;InnoDB是默認的
MySQL引擎
InnoDB特點：
支持事務處理，支持外鍵，支持崩潰修復能力和并發控制。如果需要對事務的完整性要求比較高（比
如銀行），要求實現并發控制（比如售票），那選擇InnoDB有很大的優勢。如果需要頻繁的更新、刪除操作的數據庫，也可以選擇InnoDB，因為支持事務的提交（commit）和回滾（rollback）。
MyISAM存儲引擎：（了解）
MyISAM基于ISAM存儲引擎，并對其進行擴展。它是在Web、數據倉儲和其他應用環境下最常使用的存儲引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事務。
MyISAM特點：
插入數據快，空間和內存使用比較低。如果表主要是用于插入新記錄和讀出記錄，那么選擇MyISAM能實現處理高效率。如果應用的完整性、并發性要求比較低，也可以使用。
MEMORY存儲引擎(了解)
MEMORY存儲引擎將表中的數據存儲到內存中，未查詢和引用其他表數據提供快速訪問
MEMORY特點：
所有的數據都在內存中，數據的處理速度快，但是安全性不高。如果需要很快的讀寫速度，對數據的
安全性要求較低，可以選擇MEMOEY。它對表的大小有要求，不能建立太大的表。所以，這類數據庫只使用在相對較小的數據庫表。
引擎功能對比：
5/52
click me click me click me click me
功 能 MYISAM Memory InnoDB
存儲限制 256TB RAM 64TB
支持事物 No No Yes
支持全文索引 Yes No No
支持數索引 Yes Yes Yes
支持哈希索引 No Yes No
支持數據緩存 No N/A Yes
支持外鍵 No No Yes
如何選擇引擎：
如果要提供提交、回滾、崩潰恢復能力的事物安全（ACID兼容）能力，并要求實現并發控制，InnoDB是一個好的選擇；如果數據表主要用來插入和查詢記錄，則MyISAM引擎能提供較高的處理效率；如果只是臨時存放數據，數據量不大，并且不需要較高的數據安全性，可以選擇將數據保存在內存中的Memory引擎；MySQL中使用該引擎作為臨時表，存放查詢的中間結果；如果只有INSERT和SELECT操作，可以選擇
Archive，Archive支持高并發的插入操作，但是本身不是事務安全的。Archive非常適合存儲歸檔數據，如記錄日志信息可以使用Archive。
使用哪一種引擎需要靈活選擇，一個數據庫中多個表可以使用不同引擎以滿足各種性能和實際需求，使用合適的存儲引擎，將會提高整個數據庫的性能。
存儲引擎查看：
mysql> show engines;
+-------------------- +--------- +---------------------------------------------------------------- +-------------- +------ +------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+-------------------- +--------- +---------------------------------------------------------------- +-------------- +------ +------------ +
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+-------------------- +--------- +---------------------------------------------------------------- +-------------- +------ +------------ +
9 rows in set (0.00 sec)
Support列的值表示某種引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示該引擎為當前默認的存儲引擎
mysql> alter table service engine=innodb;
Mysql事務
6/52
MySQL 事務
MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！
?在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務。
?事務處理可以用來維護數據庫的完整性，保證成批的 SQL 語句要么全部執行，要么全部不執行。
?事務用來管理 insert,update,delete 語句
一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。
? 原子性：一個事務（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個
環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
?一致性：在事務開始之前和事務結束以后，數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作。
?隔離性：數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發
執行時由于交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable）。
?持久性：事務處理結束后，對數據的修改就是永久的，即便系統故障也不會丟失。
在MySQL 命令行的默認設置下，事務都是自動提交的，即執行 SQL 語句后就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務務須使用命令 BEGIN 或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。
MYSQL 事務處理主要有兩種方法：
1、用 BEGIN, ROLLBACK, COMMIT來實現
?BEGIN 開始一個事務
?ROLLBACK 事務回滾
?COMMIT 事務確認
2、直接用 SET 來改變 MySQL 的自動提交模式:
?SET AUTOCOMMIT=0 禁止自動提交
?SET AUTOCOMMIT=1 開啟自動提交
show variables like 'autocommit'; //查看是否修改成功
注意：在編寫應用程序時，最好事務的控制權限交給開發人員
表管理及數據類型
數據類型
分類：
7/52
數值類型字符串類型
時間和日期類型
數值類型： TINYINT SMALLINT MEDIUMINT INT BIGINT
整數類型
作用：用于存儲用戶的年齡、游戲的Level、經驗值等。
類型 大小 范圍（有符號） 范圍（無符號）
TINYINT 1 字節 (-128，127) (0，255)
SMALLINT 2 字節 (-32 768，32 767) (0，65 535)
MEDIUMINT 3 字節 (-8 388 608，8 388 607) (0，16 777 215)
INT或INTEGER 4 字節 (-2 147 483 648，2 147 483 647) (0，4 294 967 295)
BIGINT 8 字節 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 6
浮點數類型 FLOAT DOUBLE
作用：用于存儲用戶的身高、體重、薪水等
float(5.3) 5寬度 3精度
寬度不算小數點
mysql> create table t12(id float(6,2));
mysql> insert into t1 values ('2.22');
click me click me click me click me
FLOAT 4 字節 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 0，(1.175 494
823 466 351 E+38)
DOUBLE 8 字節 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0， 0，(2.225 073
(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
定點數類型 DEC 定點數在MySQL內部以字符串形式存儲，比浮點數更精確，適合用來表示貨幣等精度高的數據。
位類型 BIT BIT(M)可以用來存放多位二進制數，M范圍從1~64，如果不寫默認為1位
字符串類型：
CHAR系列 CHAR VARCHAR
TEXT系列TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚舉類型： SET ENUM
集合類型：
類型 大小 用途
CHAR 0-255字節 定長字符串
VARCHAR 0-65535 字節 變長字符串
TINYBLOB 0-255字節 不超過 255 個字符的二進制字符串
TINYTEXT 0-255字節 短文本字符串
BLOB 0-65 535字節 二進制形式的長文本數據
TEXT 0-65 535字節 長文本數據
枚舉類型： 枚舉列可以把一些不重復的字符串存儲成一個預定義的集合 mysql> create table enum_table( e ENUM('fish','apple','dog'));
Query OK, 0 rows affected (0.35 sec)
mysql> insert into enum_table(e) values('fish'); Query OK, 1 row affected (0.11 sec)
mysql> select * from enum_table; +------+
8/52
| e | +------+ | fish | +------+
1 row in set (0.00 sec)
mysql> insert into enum_table(e) values('nihao');
ERROR 1265 (01000): Data truncated for column 'e' at row 1
時間和日期類型： DATE TIME DATETIME TIMESTAMP YEAR
作用：用于存儲用戶的注冊時間，文章的發布時間，文章的更新時間，員工的入職時間等
類型 大小 范圍 格式
(字節)
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS
YEAR 1 1901/2155 YYYY
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD
TIMESTAMP 4 1970-01-01 00:00:00/2038 YYYYMMDD
結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間
2038年1月19日 凌晨 03:14:07
create table t8 (
id1 timestamp NOT NULL default CURRENT_TIMESTAMP,
id2 datetime default NULL
);
mysql> desc t8;
+------- +----------- +------ +----- +------------------- +------- +
| Field | Type | Null | Key | Default | Extra |
+------- +----------- +------ +----- +------------------- +------- +
| id1 | timestamp | NO | | CURRENT_TIMESTAMP ||
| d2 | datetime | YES | | NULL | |
+------- +----------- +------ +----- +------------------- +------- +
2 rows in set (0.01 sec)
timestamp 類型的列還有個特性：默認情況下，在 insert, update 數據時，timestamp 列會自動以當前時
間（CURRENT_TIMESTAMP）填充/更新。“自動”的意思就是，你不去管它，MySQL 會替你去處理。
mysql> insert into t8(id1) values('20180109000000');
mysql> select * from t8;
+--------------------- +------ +
| id1 | d2 |
+--------------------- +------ +
| 2018-01-09 00:00:00 | NULL |
+--------------------- +------ +
1 row in set (0.00 sec)
擴展：
select now();查看當前時間
表操作
MySQL表操作 DDL
表是數據庫存儲數據的基本單位，由若干個字段組成，主要用來存儲數據記錄。表的操作包括:
創建表、查看表、修改表和刪除表。
9/52
這些操作都是數據庫管理中最基本，也是最重要的操作。本節內容包括:
創建表 create table
查看表結構 desc table, show create table 修改表 alter table
復制表 create table ...
刪除表 drop table
一、創建表（表的基本操作）表:school.student1
字段 字段 字段
id name sex age
1 tom male 23 記錄
2 jack male 21 記錄
3 alice female 19 記錄
語法：
create table 表名(自定義)(
字段名1 類型[(寬度) 約束條件],字段名2 類型[(寬度) 約束條件],字段名3 類型[(寬度) 約束條件]
)[存儲引擎 字符集]; ==在同一張表中，字段名是不能相同==寬度和約束條件可選==字段名和類型是必須的
mysql> CREATE DATABASE school; //創建數據庫school
mysql> use school;
mysql> create table student1(
-> id int,
-> name varchar(50),
-> sex enum('m','f'),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
查看表（當前所在庫）
mysql> show tables;
+------------------ +
| Tables_in_school |
+------------------ +
| student1 |
+------------------ +
1 row in set (0.00 sec)
向表中插入內容
語法：
insert into 表名(字段1,字段2... ) values(字段值列表...);
mysql> insert into student1(id,name,sex,age) values(1,'xingdia','m','26');
查看表結構
mysql> desc student1;
+------- +--------------- +------ +----- +--------- +------- +
| Field | Type | Null | Key | Default | Extra |
+------- +--------------- +------ +----- +--------- +------- +
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+------- +--------------- +------ +----- +--------- +------- +
4 rows in set (0.00 sec)
10/52
mysql> select id,name,sex,age from student1; //查詢表中所有字段的值
Empty set (0.00 sec)
mysql> select * from student1; /查詢表中所有字段的值
Empty set (0.00 sec)
mysql> select name,age from student1; //查詢表中指定字段的值
Empty set (0.00 sec)
mysql> insert into student1 values (1,'xingdian','m',33),(2,'alice','m',20),(3,'jack','m',40); //
順序插入
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into student1(name,age) values ('zhuzhu',10),('gougou',20);//只向指定的字段插入值
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
表school.student2
字段名 數據類型
編號 id int
姓名 name varchar(50)
出生年份 born_year year
生日 birthday date
上課時間 class_time time
注冊時間 reg_time datetime
mysql> create table student2( id int,
name varchar(50), born_year year, birthday date, class_time time, reg_time datetime );
mysql> desc student2;
mysql> insert into student2 values(1,'tom',now(),now(),now(),now());
mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);
表school.student3
id id int
姓名 name varchar(50)
性別 sex enum('male','female')
愛好 hobby set('music','book','game','disc')
mysql> create table student3( id int,
name varchar(50),
sex enum('male','female'),
hobby set('music','book','game','disc') );
mysql> desc student3;
mysql> show create table student3\G
mysql> insert into student3 values (1,'tom','male','book,game'); mysql> insert into student3 values (2,'jack','male','film'); mysql> select * from student3;
二、查看表結構
DESCRIBE查看表結構
11/52
DESCRIBE 表名;
DESC 表名;
SHOW CREATE TABLE查看表詳細結構
SHOW CREATE TABLE 表名;
三、表完整性約束作用：用于保證數據的完整性和一致性
約束條件 說明
PRIMARY KEY (PK) 標識該字段為該表的主鍵，可以唯一的標識記錄，不可以為空 UNIQUE +
NOT NULL
FOREIGN KEY (FK) 標識該字段為該表的外鍵，實現表與表（父表主鍵/子表1外鍵/子表2外鍵）之
間的關聯
NOT NULL 標識該字段不能為空
UNIQUE KEY (UK) 標識該字段的值是唯一的，可以為空，一個表中可以有多個UNIQUE KEY
AUTO_INCREMENT 標識該字段的值自動增長（整數類型，而且為主鍵）
DEFAULT 為該字段設置默認值
說明：
1.是否允許為空，默認NULL，可設置NOT NULL，字段不允許為空，必須賦值
2.字段是否有默認值，缺省的默認值是NULL，如果插入記錄時不給字段賦值，此字段使用默認值
sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必須為正值（無符號） 不允許為空 默認是20
NOT NULL
表school.student4
mysql> create table school.student4( id int not null,
name varchar(50) not null,
sex enum('m','f') default 'm' not null, age int unsigned default 18 not null,
hobby set('music','disc','dance','book') default 'book,dance' );
mysql> insert into student4 values(1,'jack','m',20,'book'); Query OK, 1 row affected (0.00 sec)
mysql> select * from student4;
mysql> insert into student4(id,name) values(2,'robin'); Query OK, 1 row affected (0.00 sec)
mysql> insert into student4 values(3,NULL,'m',40,'book'); ERROR 1048 (23000): Column 'name' cannot be null
設置唯一約束 UNIQUE
MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。
表company.department1
CREATE TABLE company.department1 (
dept_id INT,
dept_name VARCHAR(30) UNIQUE,
comment VARCHAR(50)
);
mysql> desc department1;
+----------- +------------- +------ +----- +--------- +------- +
| Field | Type | Null | Key | Default | Extra |
+----------- +------------- +------ +----- +--------- +------- +
| dept_id | int(11) | YES | | NULL | |
12/52
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
設置主鍵約束 PRIMARY KEY
primary key 字段的值是不允許重復，且不允許不NULL（UNIQUE + NOT NULL）
單列做主鍵表school.student6 方法一
mysql> create table student6(
id int primary key not null auto_increment, name varchar(50) not null,
sex enum('male','female') not null default 'male', age int not null default 18
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student6 values (1,'alice','female',22);
mysql> insert into student6(name,sex,age) values ('jack','male',19),
('tom','male',23);
Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student6;
設置字段值增 AUTO_INCREMENT 表company.department3
CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30),
comment VARCHAR(50) );
四、修改表ALTER TABLE
語法：
ALTER TABLE 表名 RENAME 新表名;
ALTER TABLE 表名
ADD 字段名 數據類型 [完整性約束條件…] AFTER 字段名;
刪除字段
ALTER TABLE 表名 DROP 字段名;
13/52
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 舊數據類型 [完整性約束條件…];
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 新數據類型 [完整性約束條件…];
示例：
mysql> alter table service
-> engine=innodb; //engine=myisam|memory|....
mysql> create table student10 (id int);
mysql> alter table student10
-> add name varchar(20) not null,
-> add age int not null default 22;
mysql> alter table student10
-> add stu_num int not null after name; //添加name字段之后
mysql> alter table student10
add sex enum('male','female') default 'male' first; //添加到最前面
mysql> alter table student10
-> drop sex;
mysql> alter table service
-> drop mac;
mysql> alter table student10
-> modify age tinyint not null ; //注意保留原有的約束條件
mysql> alter table student10
-> modify id int not null primary key ; //修改字段類型、約束、主鍵
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table student10 modify id int not null auto_increment; Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
7.增加主鍵和自動增長 mysql> alter table student1
-> modify id int not null primary key auto_increment;
8.刪除主鍵[primary key auto_increment]
a. 刪除自增約束
mysql> alter table student10 modify id int not null;
b. 刪除主鍵 mysql> alter table student10
-> drop primary key;
14/52
五、復制表
復制表結構＋記錄 （key不會復制: 主鍵、外鍵和索引）復制表結構/記錄+表結構，不會將Key復制 mysql> create table new_service select * from service;
只復制表結構
mysql> create table new1_service select * from service where 1=2; //條件為假，查不到任何記錄
可以復制主鍵,只復制表結構 mysql> create table t4 like employees;
六、刪除表
DROP TABLE 表名;
表操作（擴展）
修改數據表中字段的值:語法：
Update 表名 set 列名=值where 條件 update student set name='123' where id=1
刪除某一行：語法：
delete from 表名 where id=1 delete from type where id=1
id name
1xingdian renren
庫操作
系統數據庫
information_schema： 虛擬庫，主要存儲了系統中的一些數據庫對象的信息，例如用戶表信息、列信息、權限信息、字符信息等
performance_schema： 主要存儲數據庫服務器的性能參數
mysql： 授權庫，主要存儲系統用戶的權限信息
sys： 主要存儲數據庫服務器的性能參數
創建數據庫：DDL 1. #mysqladmin -u root -p1 create db1
2.直接去創建數據庫目錄并且修改權限
3.mysql> create database xingdian;
數據庫命名規則：區分大小寫唯一性
不能使用關鍵字如 create select
不能單獨使用數字
查看數據庫 mysql> show databases;
mysql> show create database xingdian; mysql> select database(); 查看當前所在的庫
15/52
切換數據庫 mysql> use xingdian; mysql> show tables;
刪除數據庫
DROP DATABASE 數據庫名;
數據庫查詢
數據庫查詢
單表查詢
簡單查詢通過條件查詢查詢排序
限制查詢記錄數使用集合函數查詢分組查詢使用正則表達式查詢
測試表：company.employee5
雇員編號 id int
雇員姓名 name varchar(30)
雇員性別 sex enum
雇用時期 hire_date date
職位 post varchar(50)
職位描述 job_description varchar(100)
薪水 salary double(15,2)
辦公室 office int
部門編號 dep_id int
mysql> CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null, name varchar(30) not null,
sex enum('male','female') default 'male' not null, hire_date date not null,
post varchar(50) not null, job_description varchar(100), salary double(15,2) not null, office int,
dep_id int );
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
16/52
mysql> select 字段名稱,字段名稱2 from 表名 條件
簡單查詢：
mysql> select * from employee5;
mysql> select name, salary, dep_id from employee5 where id <=5;
避免重復DISTINCT SELECT post FROM employee5;
SELECT distinct post FROM employee5;
注：不能部分使用DISTINCT，通常僅用于某一字段。
通過四則運算查詢
SELECT name, salary, salary*14 FROM employee5;
SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
SELECT name, salary, salary*14 Annual_salary FROM employee5;
定義顯示格式 CONCAT() 函數用于連接字符串
SELECT concat(name, 's annual salary: ', salary*14) AS Annual_salary FROM employee5;
單條件查詢
SELECT name,post FROM employee5 WHERE post='hr';
多條件查詢
SELECT name,salary FROM employee5 WHERE post='hr' AND salary>10000; select * from employee5 where salary>5000 and salary<10000 or dep_id=102;
關鍵字BETWEEN AND between and
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
關鍵字IS NULL SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
SELECT name,job_description FROM employee5 WHERE job_description='';
NULL說明：
1、等價于沒有任何值、是未知數。
2、NULL與0、空字符串、空格都不同,NULL沒有分配存儲空間。
3、對空值做加、減、乘、除等運算操作，結果仍為空。
4、比較時使用關鍵字用“is null”和“is not null”。
5、排序時比其他數據都小（索引默認是降序排列，小→大），所以NULL值總是排在最前。
關鍵字IN集合查詢
SELECT name, salary FROM employee5
WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
SELECT name, salary FROM employee5
WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee
WHERE salary NOT IN (4000,5000,6000,9000) ;
關鍵字LIKE模糊查詢通配符’% ’:所有字符
SELECT * FROM employee5 WHERE name LIKE 'al%';
通配符’_’ 一個字符
SELECT * FROM employee5
WHERE name LIKE 'al___';
17/52
排序查詢
mysql> select china from t1 order by china; mysql> select china from t1 order by china desc;
mysql> select china from t1 order by china desc limit 3; 控制顯示前3行。
mysql> select china from t1 order by china desc limit 1,3; 從序號1開始顯示三行的內容。
注：
ascending 美音 /?'s?nd??/ 升序 descending 美音 /d?'s?nd??/ 降序
按多列排序:
入職時間相同的人薪水不同
SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
限制查詢的記錄數
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 5; //默認初始位置為0
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 0,5;
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 3,5; //從第4條開始，共顯示5條
使用集合函數查詢
count 可以查看共有多少條記錄
select count(*) from employee5;
select count( name) from employee5;
select max(salary) from employee5; //部門薪資最高
select min(salary) from employee5;
select avg(salary) from employee5;
sale這個部門的總工資：
select concat("Total Department Wages：",sum(salary)) from employee5 where post='sale';
打印薪水最高的這個人的詳細信息： select * from employee5 where salary = (select max(salary) from employee5);
分組查詢：
GROUP BY和GROUP_CONCAT()函數一起使用
部門ID相同，就把名字拼到一起：
SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
GROUP BY和集合函數一起使用部門最高薪資
SELECT post,max(salary) FROM employee5 GROUP BY post;
+------------ + -------------+
| post | max(salary) |
+------------ + -------------+
| hr | 6000.00 |
| instructor | 8000.00 |
| sale | 20000.00 |
+------------ + -------------+
3 rows in set (0.07 sec)
正則查詢
SELECT * FROM employee5 WHERE name REGEXP '^ali';
SELECT * FROM employee5 WHERE name REGEXP 'yun$';
SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
小結：對字符串匹配的方式
WHERE name = 'tom';
WHERE name LIKE 'to%'; _ %
18/52
WHERE name REGEXP 'yun$'; ^ $ {2}
多表查詢（擴展）
表查詢左右內鏈接
多表連接查詢復合條件連接查詢
一、準備兩張表
一、準備兩張測試表
表company.employee6 mysql> create table employee6(
emp_id int auto_increment primary key not null, emp_name varchar(50),
age int, dept_id int);
mysql> desc employee6;
mysql> insert into employee6(emp_name,age,dept_id) values ('',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('xingdian',16,200),
('natasha',28,204);
mysql> select * from employee6;
表company.department6
mysql> create table department6( dept_id int,
dept_name varchar(100) );
mysql> desc department6;
mysql> insert into department6 values (200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');
mysql> select * from department6;
注：
Financial department:財務部門 fd
二、多表的連接查詢
交叉連接： 生成笛卡爾積，它不使用任何匹配條件交叉聯接返回左表中的所有行，左表中的每一行與右表中的所有行組合
內連接： 只連接匹配的行
外連接
左連接： 會顯示左邊表內所有的值，不論在右邊表內匹不匹配
右連接： 會顯示右邊表內所有的值，不論在左邊表內匹不匹配
全外連接： 包含左、右兩個表的全部行
=================交叉連接=======================
select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
=================內連接=======================
19/52
只找出有部門的員工 (部門表中沒有natasha所在的部門)
select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6 where employee6.dept_id=department6.dept_id;
select employee6.emp_name,department6.dept_name from employee6 inner join department6 on employee6.dept_id=department6.dept_id;
外連接語法：
SELECT 字段列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
先用誰誰就是左。
=================外連接(左連接 left join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
找出所有員工及所屬的部門，包括沒有部門的員工
=================外連接(右連接right join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
找出所有部門包含的員工，包括空部門
20/52
=================全外連接=======================
mysql> select * from employee6 full join department6; +--------+----------+---------+---------+-----------+
| emp_id | emp_name | dept_id | dept_id | dept_name |
21/52
24 rows in set (0.00 sec)
三、復合條件連接查詢
示例1：以內連接的方式查詢employee6和department6表，并且employee6表中的age字段值必須大于25
找出公司所有部門中年齡大于25歲的員工
示例2：以內連接的方式查詢employee6和department6表，并且以age字段的升序方式顯示
四、子查詢
子查詢是將一個查詢語句嵌套在另一個查詢語句中。
22/52
內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。
子查詢中可以包含：IN、NOT IN等關鍵字
還可以包含比較運算符：= 、 !=、> 、<等
帶IN關鍵字的子查詢查詢employee表，但dept_id必須在department表中出現過
23/52
數據庫日志管理
日志分類
1 錯誤日志 ：啟動，停止，關閉失敗報錯。rpm安裝日志位置 /var/log/mysqld.log 排錯
2 通用查詢日志：所有的查詢都記下來。
3 二進制日志：實現備份，增量備份。只記錄改變數據，除了select都記。 備份 binlog
4 中繼日志：讀取主服務器的binlog，在本地回放。保持一致。 復制
5 slow log：慢查詢日志，指導調優，定義某一個查詢語句，定義超時時間，通過日志提供調優建議給開發
人員。 調優
6 DDL log： 定義語句的日志。
Error Log
log-error=/var/log/mysqld.log
Binary Log:前提需要開啟
log-bin=/var/log/mysql-bin/slave2 server-id=2
[root@slave2 ~]# mkdir /var/log/mysql-bin
[root@slave2 ~]# chown mysql.mysql /var/log/mysql-bin/ [root@slave2 ~]# systemctl restart mysqld
查看binlog日志
注：
1.重啟mysqld 會截斷
2.flush logs 會截斷
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
截取binlog
24/52
all：
datetime：
#mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"
#mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"
#mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54"
position：
#mysqlbinlog mysql.000002 --start-position=260
#mysqlbinlog mysql.000002 --stop-position=260
#mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
Slow Query Log
slow_query_log=1 slow_query_log_file=/var/log/mysql-slow/slow.log long_query_time=3 設置慢查詢超時時間 單位是：秒驗證:
#mysql -u root -p2
select sleep(6); #執行一個超過6秒的查詢操作
#cat /var/log/mysql/slow-log
權限控制和備份恢復
權限控制
mask
權限級別
權限級別：
Global level：系統級，所有庫，所有表，的權限 Database level：某個數據庫中的所有表，的權限 Table level：庫中的某個表，的權限 Column level：表中的某個字段，的權限 procs level：某個存儲過程，的權限 proxies level：代理服務器，的權限
查看權限記錄表：因為超級管理員默認已經設置。所以直接查詢權限即可。
Global level
select * from mysql.user\G;
用戶字段：root
權限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
Database level
select * from mysql.db\G;
25/52
測試庫權限 mysql> create database ttt;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on ttt.* to 'u1'@'localhost' identified by 'QianFeng@123'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from mysql.db\G
驗證：
#mysql -u u1 -pQianFeng@123 >show databases;
Table level
select * from mysql.tables_priv\G;
grant all on k0.t1 to 'u1'@'localhost' identified by 'QianFeng@123';
驗證：
#mysql -u u1 -pQianFeng@123 >use k0
show tables;
Column level
select * from mysql.columns_priv\G;
insert into mysql.columns_priv(host,db,user,table_name,column_name,column_priv) values('10.18.44. %','data','ying','t1','id','select');
前提是有庫，有表，有權限。
++++mysql ++++web（farm）
不允許root賬戶 給web項目普通用戶 farm -----> 庫（單個/多個）
sql漏洞注入------>脫庫
用戶管理
注意： 修改（update，grant）完權限后，要刷新授權表，目錄讓授權的用戶的權限立即生效。
MySQL用戶管理
1.登錄和退出MySQL
#mysql -h292.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’
-h 指定主機名 【默認為localhost】
-P MySQL服務器端口 【默認3306】
-u 指定用戶名 【默認root】
-p 指定登錄密碼 【默認為空密碼】
此處mysql為指定登錄的數據庫 -e 接SQL語句 (在腳本中使用)
方法一：CREATE USER語句創建 mysql> create user xingdian;
26/52
mysql> create user xingdian@’%’ identified by '123456'; 這樣可以直接從遠程登錄
方法二： GRANT語句創建(授權) mysql> GRANT ALL ON . TO 'xingdian'@’localhost’ IDENTIFIED BY ‘123456’;
mysql> grant select,insert on k1.* to admin1@'%' identified by '123';
FLUSH PRIVILEGES;
查看單獨的庫權限 mysql> select * from mysql.db\G
注意： ALL 單獨的權限 . 單獨的庫和單獨的表
xingdian@localhost 用戶有則授權無則創建 localhost % 10.19.40.% 10.19.40.11 3. 刪除用戶方法一：DROP USER語句刪除
DROP USER 'user1'@’localhost’;
方法二：DELETE語句刪除
DELETE FROM mysql.user WHERE user='user2' AND host=’localhost’;
FLUSH PRIVILEGES; 刷新授權表
方法二：
mysql> update mysql.user set authentication_string=password('Qianfeng123!') where user='root' and host='localhost';
方法三：
給那個用戶設置密碼，你要在那個用戶下執行
SET PASSWORD=password(‘new_password’);
上面方法將會在后面的版本remove,使用下面方法
SET PASSWORD='new_password'; 直接設置密碼
root修改其他用戶密碼方法一：
mysql> SET PASSWORD FOR user3@'localhost'=password('new_password');
上面的方法會在將來remove，使用下面的方法： mysql> SET PASSWORD FOR user3@’localhost’='new_password';
方法二：
UPDATE mysql.user SET authentication_string=password(‘new_password’)
WHERE user=’user3’ AND host=’localhost’;
普通用戶修改自己密碼 mysql> SET password=password('new_password');
mysql> select * from mysql.user\G
mysql> alter user 'wing'@'localhost' identified by 'Qianfeng123!@';
查看現有的密碼策略 mysql> SHOW VARIABLES LIKE 'validate_password%';
參數解釋：
1).validate_password_dictionary_file 指定密碼驗證的文件路徑; 2).validate_password_length 密碼最小長度 3).validate_password_mixed_case_count 密碼至少要包含的小寫字母個數和大寫字母個數; 4).validate_password_number_count 密碼至少要包含的數字個數
5).validate_password_policy 密碼強度檢查等級，對應等級為：0/LOW、1/MEDIUM、2/STRONG,默認為1 0/LOW：只檢查長度; 1/MEDIUM：檢查長度、數字、大小寫、特殊字符;
2/STRONG：檢查長度、數字、大小寫、特殊字符字典文件。
6).validate_password_special_char_count密碼至少要包含的特殊字符數
27/52
找回密碼
5.6/5.7版本：
mysql> UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='root' AND host='localhost';
mysql> FLUSH PRIVILEGES; 刷新授權表
注意：報錯處理
問題1：ERROR You must reset your password using ALTER USER statement before executing this statement
解決方案：ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xiaoming250';
報錯解決方案
創建用戶時報錯：
mysql> create user 'miner'@'192.168.%' IDENTIFIED BY 'miner123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
報錯原因：密碼強度不夠。
解決方法：(該賬號為測試賬號，所以采用降低密碼策略強度) mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=4; Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------- +------- +
| Variable_name | Value |
+-------------------------------------- +------- +
| validate_password_dictionary_file | |
| validate_password_length | 4 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+-------------------------------------- +------- +
6 rows in set (0.00 sec)
再次創建用戶，成功
報錯：
mysql> SET PASSWORD FOR xingdian@'localhost'=password('QianFeng!123'); ERROR 1133 (42000): Can't find any matching row in the user table
解決方案：
報錯：
28/52
[root@b0505f448652 ~]# mysqladmin -u root -p2 password '4';
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'
解決方案：
備份恢復
概述
MySQL數據備份
所有備份數據都應放在非數據庫本地，而且建議有多份副本。測試環境中做日常恢復演練，恢復較備份更為重要。
備份： 能夠防止由于機械故障以及人為誤操作帶來的數據丟失，例如將數據庫文件保存在了其它地方。
冗余： 數據有多份冗余，但不等備份，只能防止機械故障還來的數據丟失，例如主備模式、數據庫集群。
備份過程中必須考慮因素：
1.數據的一致性
2.服務的可用性
邏輯備份：
備份的是建表、建庫、插入等操作所執行SQL語句（DDL DML DCL），適用于中小型數據庫，效率相對較低。 mysqldump
物理備份：
直接復制數據庫文件，適用于大型數據庫環境，不受存儲引擎的限制，但不能恢復到不同的MySQL版
本。 tar,cp xtrabackup lvm snapshot
完全備份
增量備份:每次備份上一次備份到現在產生的新數據
29/52
差異備份:只備份跟完整備份不一樣的
tar數據備份(物理)
tar備份數據庫注：備份期間，服務不可用
備份的過程：【完全物理備份】
1.停止數據庫
2.tar備份數據
3.啟動數據庫
[root@slave2 ~]# systemctl stop mysqld [root@slave2 ~]# mkdir /backup [root@slave2 ~]# cd /var/lib/mysql
[root@slave2 ~]# tar -zcvf /backup/date +%F-mysql-all.tar ./*
注：備份文件應該復制其它服務器或存儲上
30/52
還原的過程：
1.停止數據庫
2.清理環境
3.導入備份數據
4.啟動數據庫
[root@slave2 ~]# systemctl stop mysqld [root@slave2 ~]# rm -rf /var/lib/mysql/* [root@slave2 ~]# cd /backup
[root@slave2 ~]# tar -xvf /backup/2019-08-20-mysql-all.tar -C /usr/lib/mysql [root@slave2 ~]# systemctl start mysqld
xtarbackup備份(物理)
percona-xtrabackup 物理備份 + binlog
它是開源免費的支持MySQL 數據庫熱備份的軟件，它能對InnoDB和XtraDB存儲引擎的數據庫非阻塞地
備份。它不暫停服務創建Innodb熱備份；為mysql做增量備份；在mysql服務器之間做在線表遷移；使創建replication更加容易；備份mysql而不增加服務器的負載。
percona是一家老牌的mysql技術咨詢公司。它不僅提供mysql的技術支持、培訓、咨詢，還發布了mysql
的分支版本--percona Server。并圍繞percona Server還發布了一系列的mysql工具。
軟件安裝
部署xtrabackup
31/52
#wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
#rpm -ivh percona-release-0.1-4.noarch.rpm
#yum -y install percona-xtrabackup-24.x86_64
注意：如果依賴包perl-DBD-MySQL安裝不上，]需先把percona源拿掉用centos的源單獨安裝，然后再安裝 percona-xtrabackup-24.x86_64
完整備份
完全備份流程創建備份目錄：
[root@xingdian full]# mkdir -p /xtrabackup/full
備份：
[root@xingdian full]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full
完全備份恢復流程
1.停止數據庫
2.清理環境
3.重演回滾－－> 恢復數據
4.修改權限
5.啟動數據庫關閉數據庫：
#systemctl stop mysqld
#rm -rf /var/lib/mysql/*
#rm -rf /var/log/mysqld.log
#rm -rf /var/log/mysql-slow/slow.log (有則刪除，無則不需要操作)
恢復之前的驗證恢復：
#innobackupex --apply-log /xtrabackup/full/2019-08-20_15-57-31/
確認數據庫目錄：
恢復之前需要確認配置文件內有數據庫目錄指定，不然xtrabackup不知道恢復到哪里
恢復數據：
[root@xingdian mysql]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/
修改權限：
[root@xingdian mysql]# chown mysql.mysql /var/lib/mysql -R
啟動數據庫：
[root@xingdian mysql]# systemctl start mysqld
驗證數據：
[root@xingdian mysql]# mysql -u root -pQianFeng@123 mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| k1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
增量備份
增量備份流程
原理：每次備份上一次備份到現在產生的新數據
32/52
創建備份目錄：
[root@xingdian ~]# mkdir -p /xtrabackup/full
1、完整備份：周一
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full 2、增量備份：周二 —— 周六
需要創建數據
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' --incremental /xtrabackup/zeng/ -- incremental-basedir=/xtrabackup/full/2019-08-20_15-57-31/
[root@xingdian zeng]# ls 2019-08-21_00-00-08
需要創建數據
[root@xingdian ~]# innobackupex --user=root --password=QianFeng@123 --incremental /xtrabackup/zeng/ -- incremental-basedir=/xtrabackup/zeng/2019-08-21_00-00-08/
[root@xingdian zeng]# ls 2019-08-21_00-00-08 2019-08-22_00-04-11
……
增量備份恢復流程
1.停止數據庫
2.清理環境
3.依次重演回滾redo log－－> 恢復數據
4.修改權限
5.啟動數據庫
[root@xingdian ~]# systemctl stop mysqld [root@xingdian ~]# rm -rf /var/lib/mysql/* 依次重演回滾redo log 周一：full
[root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2019-08-20_15-57-31/
周二 --- 周四
[root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2019-08-20_15-57-31/ --incremental-dir=/xtrabackup/zeng/2019-08-21_00-00-08/
[root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2019-08-20_15-57-31/ --incremental-dir=/xtrabackup/zeng/2019-08-22_00-04-11/
……
恢復數據
[root@xingdian ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/ (datadir)
修改權限
[root@xingdian ~]# chown -R mysql.mysql /var/lib/mysql
[root@xingdian ~]# systemctl start mysqld
驗證恢復：
[root@xingdian ~]# mysql -u root -pQianFeng@123
mysql> show databases;
+-------------------- +
| Database |
+-------------------- +
| information_schema |
| k1 |
| k2 |
33/52
| k3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
差異備份
差異備份流程：只備份跟完整備份不一樣的
創建備份目錄：
[root@xingdian ~]# mkdir -p /xtrabackup/full
1、完整備份：周一
[root@xingdian ~]# innobackupex --user=root --password=888 /xtrabackup/full
2、差異備份：周二 —— 周六創建備份目錄：
[root@xingdian ~]# mkdir -p /xtrabackup/jian
insert into testdb.test2 values(2);
[root@xingdian ~]# innobackupex --user=root --password=888 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/完全備份目錄（周一）
insert into testdb.test2 values(3);
[root@xingdian ~]# innobackupex --user=root --password=888 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/完全備份目錄（周一）
insert into testdb.test values(4);
[root@xingdian ~]# innobackupex --user=root --password=888 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/完全備份目錄（周一）
差異備份恢復流程
1.停止數據庫
2.清理環境
3.重演回滾redo log（周一，某次差異）－－> 恢復數據
4.修改權限
5.啟動數據庫
[root@xingdian ~]# systemctl stop mysqld
清理環境
[root@xingdian ~]# rm -rf /var/lib/mysql/*
4.復制數據文件(cp,rsync)，修改權限 [root@xingdian ~]# innobackupex --copy-back /xtrabackup/完全備份目錄（周一）
5.啟動mysqld
[root@xingdian ~]# systemctl start mysqld
34/52
6.驗證恢復：
[root@xingdian ~]# mysql -u root -pQianFeng@123
mysql> show databases;
+-------------------- +
| Database |
+-------------------- +
| information_schema |
| k1 |
| k2 |
| k3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysqldump（邏輯）
mysqldump實現邏輯完全備份 + binlog
數據一致，服務可用備份表
備份： # mysqldump -u root -p1 db1 t1 > /db1.t1.sql
恢復： # mysql -u root -p1 db1 < /db1.t1.sql
備份一個庫
備份多個庫
#mysqldump -u root -p1 -B db1 db2 db3 > /db123.sql
備份所有的庫
#mysqldump -u root -p1 -A > /alldb.sql
恢復數據庫
為保證數據一致性，應在恢復數據之前停止數據庫對外的服務,停止binlog日志因為binlog使用binlog日志恢復數據時也會產生binlog日志 mysql> set sql_log_bin=0
mysql> source db1.t1.sql
或者
#mysql -u root -p1 -D db1 < db1.t1.sql
常用備份選項：
-A, --all-databases
備份所有庫
-B, --databases bbs test mysql
備份多個數據庫
-F, --flush-logs
備份之前刷新binlog日志
35/52
bin_log日志備份
binlog日志方法備份恢復數據記錄每一個操作
默認存儲位置 ： rpm ： /var/lib/mysql
編譯: 安裝目錄的var下
產生binlog日志
一.在啟動服務的時候啟用日志(臨時的)
查看binlog日志
方法2. show binlog events; 默認查看第一個
show binlog events in 'mylog.00001';
二.配置文件(永久修改) #vim /etc/my.cnf
[mysqld] log-bin=mylog
server-id=1 //做AB復制的時候使用 #/etc/init.d/mysqld restart
根據binlog恢復數據根據時間點恢復數據
根據位置點恢復數據@后
注：可以同時讀取多個日志文件
刷新bin-log日志 #mysqladmin flush-logs -u root -p''
LVM快照備份(物理-擴展)
Lvm快照實現物理備份 + binlog 只保存Inode 號
數據一致，服務可用
注：MySQL數據lv和將要創建的snapshot 必須在同一VG，因此VG必須要有一定的剩于空間
優點：
幾乎是熱備 (創建快照前把表上鎖，創建完后立即釋放)支持所有存儲引擎備份速度快
無需使用昂貴的商業軟件(它是操作系統級別的)缺點：
可能需要跨部門協調(使用操作系統級別的命令，DBA一般沒權限)無法預計服務停止時間數據如果分布在多個卷上比較麻煩(針對存儲級別而言)
36/52
操作流程：
1、flush table with read locak;
2、create snapshot
3、show master status; show slave status; [可選] 4、unlock tables;
5、Copy files from the snapshot
6、Unmount the snapshot.
7、Remove snapshot
正常安裝MySQL：
1.安裝系統
2.準備LVM，例如 /dev/vg_tianyun/lv-mysql，mount /var/lib/mysql
3.安裝MySQL，默認datadir=/var/lib/mysql
MySQL運行一段時間，數據并沒有存儲LVM：將現在的數據遷移到LVM 1. 準備lvm及文件系統
[root@xingdian ~]# lvcreate -n lv-mysql -L 2G datavg [root@xingdian ~]# mkfs.xfs /dev/datavg/lv-mysql
[root@xingdian ~]# systemctl stop mysqld
[root@xingdian ~]# mount /dev/datavg/lv-mysql /mnt/ //臨時掛載點
[root@xingdian ~]# cp -a /var/lib/mysql/* /mnt //將MySQL原數據鏡像到臨時掛載點
[root@xingdian ~]# umount /mnt/
[root@xingdian ~]# vim /etc/fstab //加入fstab開機掛載
/dev/datavg/lv-mysql /var/lib/mysql xfs defaults 0 0
[root@xingdian ~]# mount -a
[root@xingdian ~]# chown -R mysql.mysql /var/lib/mysql [root@xingdian ~]# systemctl start mysqld
LVM快照備份流程：
加全局讀鎖 mysql> flush tables with read lock;
[root@tianyun ~]# lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql
[root@tianyun ~]# mysql -p'(TianYunYang584131420)' -e 'show master status' > /backup/date +% F_position.txt
1-3必須同一會話中完成
[root@tianyun ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; UNLOCK TABLES;" | mysql -p'(TianYunYang584131420)'
[root@tianyun ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; " | mysql -p'(TianYunYang584131420)'
[root@tianyun mnt]# tar -cf /backup/date +%F-mysql-all.tar ./* 5. 移除快照
[root@tianyun ~]# cd; umount /mnt/
37/52
[root@tianyun ~]# lvremove -f /dev/vg_tianyun/lv-mysql-snap LVM快照恢復流程：
1.停止數據庫
2.清理環境
3.導入數據
4.修改權限
5.啟動數據庫
6.binlog恢復
[root@slave2 ~]# tar -xf /backup/2016-12-07-mysql-all.tar -C /var/lib/mysql/ [root@slave2 ~]# systemctl start mysqld
Tar解壓的注意事項： tar -tf 2016-12-07-mysql-all.tar |less
腳本 + Cron #!/bin/bash
#LVM backmysql...
back_dir=/backup/date +%F
[ -d $back_dir ] || mkdir -p $back_dir
echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql; \ UNLOCK TABLES;" | mysql -p'(TianYunYang584131420)'
mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/
rsync -a /mnt/ $back_dir
if [ $? -eq 0 ];then umount /mnt/
lvremove -f /dev/datavg/lv-mysql-snap
fi
主從復制和讀寫分離
主從復制原理
一、什么是主從復制?
主從復制，是用來建立一個和主數據庫完全一樣的數據庫環境，稱為從數據庫；主數據庫一般是準實時的業務數據庫。
二、主從復制的作用
1、做數據的熱備，作為后備數據庫，主數據庫服務器故障后，可切換到從數據庫繼續工作，避免數據丟失。
2、架構的擴展。業務量越來越大，I/O訪問頻率過高，單機無法滿足，此時做多庫的存儲，降低磁盤I/O訪問的頻率，提高單個機器的I/O性能。
3、讀寫分離，使數據庫能支撐更大的并發。在報表中尤其重要。由于部分報表sql語句非常的慢，導致鎖表，影響前臺服務。如果前臺使用master，報表使用slave，那么報表sql將不會造成前臺鎖，保證了前臺速度。
三、主從復制的原理
1.數據庫有個bin-log二進制文件，記錄了所有sql語句。
2.我們的目標就是把主數據庫的bin-log文件的sql語句復制過來。
38/52
3.讓其在從數據的relay-log(中繼日志)重做日志文件中再執行一次這些sql語句即可。
四：具體原理
1.binlog輸出線程:每當有從庫連接到主庫的時候，主庫都會創建一個線程然后發送binlog內容到從庫。在從庫里，當復制開始的時候，從庫就會創建兩個線程進行處理：
2.從庫I/O線程:當START SLAVE語句在從庫開始執行之后，從庫創建一個I/O線程，該線程連接到主庫并請求
主庫發送binlog里面的更新記錄到從庫上。從庫I/O線程讀取主庫的binlog輸出線程發送的更新并拷貝這些更新到本地文件，其中包括relay log文件。3.從庫的SQL線程:從庫創建一個SQL線程，這個線程讀取從庫I/O線程寫到relay log的更新事件并執行。
可以知道，對于每一個主從復制的連接，都有三個線程。擁有多個從庫的主庫為每一個連接到主庫的從庫創建一個binlog輸出線程，每一個從庫都有它自己的I/O線程和SQL線程。
主從復制如圖：
master 對外提供 負責寫數據 slave 讀master日志寫到relay-log
I/O進程：負責通信
SQL進程：負責寫數據，根據log日志寫數據。
主從原理：從庫slave生成兩個線程，i/o線程和sql線程，i/o將變更記錄寫到二進制日志文件中，再寫到中繼日志中，sql線程讀取中繼日志，解析操作，最終數據統一
主從復制部署
AB復制
環境：MASTER原來沒有舊數據
1.主從都關閉防火墻selinux #/etc/init.d/iptables stop #systemctl stop firewalld #setenforce 0
2.主 誰是主 給誰提供賬戶安裝軟件mysql mysql-server
配置：
#vim /etc/my.cnf [mysqld]
log-bin = my1log
39/52
server-id = 1
創建賬戶： mysql> grant replication slave,reload,super on . to 'slave'@'%' identified by '123';
mysql> flush privileges;
重啟服務：
#systemctl restart mysqld
查看bin_log日志文件的名稱：
show binlog events;
注意:如果不成功刪除以前的binlog日志 replication slave：
擁有此權限可以查看從服務器，從主服務器讀取二進制日志。
super權限：
允許用戶使用修改全局變量的SET語句以及CHANGE MASTER語句reload權限：
必須擁有reload權限，才可以執行flush [tables | logs | privileges]
3.從
安裝軟件
配置：
#vim /etc/my.cnf [mysqld] server-id = 2
log-bin = my2log //1.使用雙主的時候必須寫 2.使用gtid方式的ab復制
啟動服務：
#systemctl restart mysqld
指定主服務器信息
help change master to 查看從mysql編輯文件
CHANGE MASTER TO MASTER_HOST='mysql-master-1.blackmed.cn/ip', MASTER_USER='slave', //主服務器用戶 MASTER_PASSWORD='big', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', //日志文件 MASTER_LOG_POS=4, //日志位置 MASTER_CONNECT_RETRY=10; //默認嘗試次數edit
show master status 在主服務器查看日志文件
啟動：start slave
mysql> show slave status \G
如果沒成功：刪除從的binlog重啟服務
======================================
log_slave_updates參數：
當從庫log_slave_updates參數沒有開啟時，從庫的binlog不會記錄來源于主庫的操作記錄。只有開啟log_slave_updates，從庫binlog才會記錄主庫同步的操作日志 log_slave_updates=1 重啟服務既可以
40/52
GTID主從復制
M-S GTID 基于事務ID復制
GTID
全局事務標識：global transaction identifiers
是用來代替傳統復制的方法，GTID復制與普通復制模式的最大不同就是不需要指定二進制文件名和位置，不再使用MASTER_LOG_FILE+MASTER_LOG_POS開啟復制。而是使用MASTER_AUTO_POSTION=1的方式開始復制。
MySQL-5.6.5開始支持的，MySQL-5.6.10后開始完善
在傳統的slave端，binlog是不用開啟的，但是在GTID中slave端的binlog是必須開啟的，目的是記錄執行過的GTID（強制）。
GTID的組成：
GTID = source_id:transaction_id
source_id源id，用于鑒別原服務器，即mysql服務器唯一的的server_uuid，由于GTID會傳遞到slave，所以也可以理解為源ID。
transaction_id事務id，為當前服務器上已提交事務的一個序列號，通常從1開始自增長的序列，一個數值對應一個事務。
示例：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
前面的一串為服務器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23為transaction_id
GTID的工作原理：
1、master更新數據時，會在事務前產生GTID，一同記錄到binlog日志中。
2、slave端的i/o 線程將變更的binlog，寫入到本地的relay log中。3、sql線程從relay log中獲取GTID，然后對比slave端的binlog是否有記錄。
4、如果有記錄，說明該GTID的事務已經執行，slave會忽略。
5、如果沒有記錄，slave就會從relay log中執行該GTID的事務，并記錄到binlog。
========================================================================
master1 (master) ---------> master2 (slave)
192.168.122.10 192.168.122.20
建議把master2重置，因為上一個實驗環境的原因
[root@master2 ~]# systemctl stop mysqld [root@master2 ~]# rm -rf /var/lib/mysql/* [root@master2 ~]# systemctl start mysqld [root@master2 ~]# grep password /var/log/mysqld.log
[root@master2 ~]# mysqladmin -p'5ovl_*4WV0Ct' password '(TianYunYang123)'
MS流程 GTID： Master
1.vim /etc/my.cnf
log-bin server-id=1 gtid_mode = ON enforce_gtid_consistency=1 restart 2. grant replication slave,reload,super on . to slave@'%'
Slave server-id=2 gtid_mode = ON
enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE
//這兩個參數會將master.info和relay.info保存在表中，默認是Myisam引擎，官方建議用
relay_log_recovery = on
41/52
雙主雙從部署
一.環境四臺mysql服務器
192.168.122.196 master1 192.168.122.197 master2 192.168.122.198 slave1 192.168.122.199 slave2
二：配置
M——M master1：
vim /etc/my.cnf validate_password=off log-bin = my1log server-id = 1
gtid_mode=ON //開啟gtid enforce_gtid_consistency=1
創建授權賬戶：
grant all on . to 'slave'@'%' identified by '123'; master2:
vim /etc/my.cnf validate_password=off log-bin = my2log server-id = 2 gtid_mode=ON enforce_gtid_consistency=1
help change master to 查找配置文件 >edit
change master to master_host='master1', master_user='授權用戶', master_password='授權密碼', master_auto_position=1;start slave;
show slave status\G
注意：
master2上也要創建一樣的授權用戶，在master1上進行change master to 的操作 S——S
slave1：
首先備份master1的所有數據，mysqldump -u root -p123 -A > /all.sql 數據分別導入slave1和slave2，mysql -u root -p123 < /all.sql
vim /etc/my.cof slave1和slave2做相同的操作 validate_password=off
log-bin = my3log server-id = 3 gtid_mode=ON enforce_gtid_consistency=1
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = on
當slave從庫宕機后，假如relay-log損壞了，導致一部分中繼日志沒有處理，則自動放棄所有未執行
42/52
的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性 >help change master to
edit
CHANGE MASTER TO MASTER_HOST='master1/ip', MASTER_USER='slave', //主服務器用戶 MASTER_PASSWORD='big',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'master1' >start slave;
show slave status; >edit
CHANGE MASTER TO MASTER_HOST='master2/ip', MASTER_USER='slave', //主服務器用戶 MASTER_PASSWORD='big',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'master2' >start slave;
show slave status\G
注意：slave1和slave2上做相同的change master to 操作。
讀寫分離
Mycat部署
galera(擴展)
mysql的組復制技術 group replication
Galera Replication http://galeracluster.com/downloads/
43/52
準備環境：主機解析：
[root@mysql-galera-1 /]# ntpdate galera1 cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.245.133 galera1
192.168.245.136 galera2 192.168.245.10 galera3 192.168.245.135 galera4
關閉防火墻和selinux: setenforce 0 && systemctl stop firewalld
安裝時間服務器：yum -y install ntp
時間同步：修改時區：
準備時間服務器：
#vim /etc/ntp.conf
server 127.127.1.0 # local clock fudge 127.127.1.0 stratum 10
#systemctl restart ntpd
客戶端同步時間：
[root@mysql-galera-2 /]# ntpdate galera1 [root@mysql-galera-3 /]# ntpdate galera1
下載和安裝Galera（每臺都需要安裝）
注意：Galera有獨立的補丁包，也有在mysql基礎上打好補丁的完整包我們要下載帶wsrep擴展補丁的版本，比如：
44/52
MySQL 5.7.20 extended with wsrep patch version 25.13
所以：刪除原版本mysql:
[root@mysql-galera-1 /]# yum erase rpm -qa | grep mysql -y
根據官方下載提示找到下載路徑，發現下載路徑下是已經做好yum源的路徑，所以可以直接修改yum配置文件使用yum安裝
配置yum源：
[root@mysql-galera-1 yum.repos.d]# cat galera.repo [galera]
name=galera baseurl=http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/ enabled=1
gpgcheck=0
[root@wing yum.repos.d]# yum list | grep 'galera'
安裝：
[root@mysql-galera-1 /]# yum install mysql-wsrep-5.7.x86_64 rsync -y
[root@mysql-galera-2 /]# yum install mysql-wsrep-5.7.x86_64 rsync -y
[root@mysql-galera-3 /]# yum install mysql-wsrep-5.7.x86_64 rsync -y
每臺啟動服務修改密碼：
#mysqladmin -u root -p'2rttwxb?3_oP' password 'Qianfeng123!' #mysql -u root -p'Qianfeng123!'
每臺機 器創建用于數 據 同步的用戶: mysql> grant all on . to 'syncuser'@'%' identified by 'QianFeng@123'; mysql> flush privileges;
配置Galera Replication:
galera1配置：主配置文件my.cnf追加如下內容，黑體部分是和其他節點不同的地方
server-id=1 binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://' //第一臺可以什么也不寫 wsrep_node_name='mysql-galera-1' wsrep_node_address='192.168.245.133'
wsrep_sst_auth=syncuser:'QianFeng@123' //創建的授權用戶和密碼 wsrep_sst_method=rsync galera2配置：主配置文件my.cnf追加如下內容 server-id=2
binlog_format=row
45/52
innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-3,mysql-galera-4' wsrep_node_name='mysql-galera-2'
wsrep_node_address='192.168.245.136' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync galera3配置：主配置文件my.cnf追加如下內容 server-id=3
binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-2,mysql-galera-4' wsrep_node_name='mysql-galera-3'
wsrep_node_address='192.168.245.10' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync galera4配置：主配置文件my.cnf追加如下內容 server-id=4
binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-2,mysql-galera-3' wsrep_node_name='mysql-galera-4'
wsrep_node_address='192.168.245.135' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync
重啟服務：每臺機器
[root@mysql-galera-1 ~]# systemctl restart mysqld
查看端口：galera端口4567 mysql端口3306
[root@galera1 ~]# ss -auntpl | grep -E '3306|4567'
tcp LISTEN 0 128 :4567 : users:(("mysqld",pid=11068,fd=12))
tcp LISTEN 0 80 :::3306 ::: users:(("mysqld",pid=11068,fd=39))
測試：
mysql> show status like 'wsrep%';
wsrep_incoming_addresses |
192.168.245.136:3306,192.168.245.135:3306,192.168.245.10:3306 |
| wsrep_cluster_size | 3 //表示一共有3個節點，我少配置了一個
階段測試:
在任何一臺機器上寫數據，在其他機器上全部會同步
配置文件參數解釋
46/52
1.binlog_format=row
① STATEMENT模式（SBR）
每一條會修改數據的sql語句會記錄到binlog中。優點是并不需要記錄每一條sql語句和每一行的數據變
化，減少了binlog日志量，節約IO，提高性能。缺點是在某些情況下會導致master-slave中的數據不一致(如sleep()函數， last_insert_id()，以及user-defined functions(udf)等會出現問題)
② ROW模式（RBR）
不記錄每條sql語句的上下文信息，僅需記錄哪條數據被修改了，修改成什么樣了。而且不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正確復制的問題。缺點是會產生大量的日志，尤其是alter table的時候會讓日志暴漲。
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的復制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇日志保存方式。
2.innodb_file_per_table=1
開啟數據和索引存儲到共享表空間
3.innodb_autoinc_lock_mode=2
由于這個模式下已經沒有了auto_inc鎖，所以這個模式下的性能是最好的；但是它也有一個問題，就是對于同一個語句來說它所得到的auto_incremant值可能不是連續的。
mycat部署
mycat 服務器端不需要安裝mysql 客戶端測試需要安裝mysql
一：部署mycat
1.安裝jdk環境
在/etc/profile下面寫java的環境變量
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH
source /etc/profile //使環境變量生效
安裝mycat
2.在～/.bash_profile下面寫mycat的環境變量
：/usr/local/mycat/bin
source ~/.bash_profile
然后啟動mycat start
3.測試端口：
jps出現
1762 WrapperSimpleApp
2023 Jps
ss -ntpl | grep java
LISTEN 0 1 127.0.0.1:32000 : users:(("java",pid=1762,fd=4))
LISTEN 0 100 :::9066 ::: users:(("java",pid=1762,fd=63))
LISTEN 0 50 :::42138 ::: users:(("java",pid=1762,fd=51))
LISTEN 0 50 :::46815 ::: users:(("java",pid=1762,fd=49))
LISTEN 0 50 :::1984 ::: users:(("java",pid=1762,fd=50))
LISTEN 0 100 :::8066 :::* users:(("java",pid=1762,fd=67))
ps aux | grep mycat
===========================================================
二：mycat使用mysql中添加數據庫和賬戶：
mysql> create database shop; mysql> create database bbs; mysql> create database blog;
mysql> grant all on shop. to shop@'%' identified by 'Qianfeng123!'; mysql> grant all on shop. to bbs@'%' identified by 'Qianfeng123!'; mysql> grant all on shop.* to blog@'%' identified by 'Qianfeng123!'; mysql> flush privileges;
47/52
設置3個賬戶和3個schema:
切換到mycat的工作目錄： server.xml Mycat的配置文件，設置賬號、參數等
schema.xml Mycat對應的物理數據庫和數據庫表的配置 rule.xml Mycat分片（分庫分表）規則1、user標簽 user 用戶配置節點
–name 登錄的用戶名，也就是連接Mycat的用戶名
–password 登錄的密碼，也就是連接Mycat的密碼
–schemas 數據庫名，這里會和schema.xml中的配置關聯，多個用逗號分開，例如需要這個用戶需要管理兩個數據庫db1,db2，則配置db1,db2 [root@mycat conf]# cd /usr/local/mycat/conf
[root@mycat conf]# cat server.xml <user name="shop">
<property name="password">123456</property> <property name="schemas">shop</property>
</user>
<user name="bbs">
<property name="password">123456</property> <property name="schemas">bbs</property>
</user>
<user name="blog">
<property name="password">123456</property> <property name="schemas">blog</property>
</user>
[root@master conf]# cat schema.xml <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="shop" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <schema name="bbs" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"> </schema> <schema name="blog" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"> </schema> <dataNode name="dn1" dataHost="localhost1" database="shop" />
<dataNode name="dn2" dataHost="localhost2" database="bbs" /> <dataNode name="dn3" dataHost="localhost3" database="blog" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>show status like 'wsrep%'</heartbeat>
<writeHost host="galera2" url="galera2:3306" user="shop" password="Qianfeng123!"> </writeHost> <writeHost host="galera3" url="galera3:3306" user="shop" password="Qianfeng123!"> </writeHost> <writeHost host="galera4" url="galera4:3306" user="shop" password="Qianfeng123!"> </writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>show status like 'wsrep%'</heartbeat>
<writeHost host="galera2" url="galera2:3306" user="bbs" password="Qianfeng123!"> </writeHost> <writeHost host="galera3" url="galera3:3306" user="bbs" password="Qianfeng123!"> </writeHost> <writeHost host="galera4" url="galera4:3306" user="bbs" password="Qianfeng123!"> </writeHost>
</dataHost>
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>show status like 'wsrep%'</heartbeat>
<writeHost host="galera2" url="galera2:3306" user="blog" password="Qianfeng123!"> </writeHost> <writeHost host="galera3" url="galera3:3306" user="blog" password="Qianfeng123!"> </writeHost> <writeHost host="galera4" url="galera4:3306" user="blog" password="Qianfeng123!"> </writeHost>
</dataHost>
</mycat:schema>
48/52
balance=1 開啟讀寫分離機制，所有讀操作都發送到當前備用的 writeHost 上。 wirteType=0 所有寫操作發送到第一個writeHost，第一個掛了切換到第二個 switchType=3 基于MySQL Galera cluster的切換機制，心跳語句為show status like 'wsrep%'
隨便找臺機器做客戶端：
[root@client ~]# mysql -u shop -p'123456' -h 192.168.245.3 -P8066
結論：
1.所有節點都正常writeHost負責寫操作，備writeHost負責讀操作
2.當第一個writeHost失效時，其中一個備的writeHost負責寫操作，其他備的writeHost負責讀操作
3.當只有一個writeHost時，同時負擔讀寫
mycat優化部署
server.xml優化
一：server.xml 配置文件
1.privileges標簽
對用戶的 schema以及表進行精細化的DML(數據操縱語言)權限控制
<privileges check="false"> </privileges> --check 表示是否開啟DML權限檢查。默認是關閉。 --dml 順序說明：insert,update,select,delete
<schema name="db1" dml="0110" > <table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table> </schema> db1的權限是update,select。tb01的權限是啥都不能干。
tb02的權限是insert,update,select,delete。其他表默認是udpate,select。
2.system標簽
這個標簽內嵌套的所有 property 標簽都與系統配置有關。
<property name="charset">utf8</property>
字符集
<property name="processors">1</property>
處理線程數量，默認是cpu數量。
<property name="processorBufferChunk">4096</property>
每次讀取留的數量，默認4096。
<property name="processorBufferPool">409600</property>
創建共享buffer需要占用的總空間大小。processorBufferChunkprocessors100。 <property name="processorBufferPoolType">0</property>
默認為0。0表示DirectByteBufferPool，1表示ByteBufferArena。 <property name="processorBufferLocalPercent">100</property> 二級共享buffer是processorBufferPool的百分比，這里設置的是百分比。 <property name="sequnceHandlerType">100</property>
全局ID生成方式。(0:為本地文件方式，1:為數據庫方式；2:為時間戳序列方式；3:為ZK生成ID；4:為ZK遞增ID生成。
<property name="useCompression">1</property>
是否開啟mysql壓縮協議。1為開啟，0為關閉，默認關閉。
<property name="packetHeaderSize">4</property>
指定 Mysql 協議中的報文頭長度。默認 4。 <property name="maxPacketSize">16M</property>
指定 Mysql 協議可以攜帶的數據最大長度。默認 16M。 <property name="idleTimeout">1800000</property>
指定連接的空閑超時時間。某連接在發起空閑檢查下，發現距離上次使用超過了空閑時間，那么這個連接
49/52
會被回收，就是被直接的關閉掉。默認 30 分鐘，單位毫秒。
<property name="txIsolation">3</property>
前端連接的初始化事務隔離級別，只在初始化的時候使用，后續會根據客戶端傳遞過來的屬性對后端數據
庫連接進行同步。默認為 REPEATED_READ，設置值為數字默認 3。 READ_UNCOMMITTED = 1;
READ_COMMITTED = 2;
REPEATED_READ = 3; SERIALIZABLE = 4;
<property name="sqlExecuteTimeout">300</property> SQL 執行超時的時間，Mycat 會檢查連接上最后一次執行 SQL 的時間，若超過這個時間則會直接關閉這連
接。默認時間為 300 秒，單位秒。
<property name="processorCheckPeriod">1000</property>
清理 NIOProcessor 上前后端空閑、超時和關閉連接的間隔時間。默認是 1 秒，單位毫秒。
<property name="dataNodeIdleCheckPeriod">300000</property>
對后端連接進行空閑、超時檢查的時間間隔，默認是 300 秒，單位毫秒。
<property name="dataNodeHeartbeatPeriod">10000</property>
對后端所有讀、寫庫發起心跳的間隔時間，默認是 10 秒，單位毫秒。
<property name="bindIp">0.0.0.0</property> mycat 服務監聽的 IP 地址，默認值為 0.0.0.0。 <property name="serverPort">8066</property>
定義 mycat 的使用端口，默認值為 8066。 <property name="managerPort">9066</property>
定義 mycat 的管理端口，默認值為 9066。 <property name="fakeMySQLVersion">5.6</property>
mycat 模擬的 mysql 版本號，默認值為 5.6 版本，如非特需，不要修改這個值，目前支持設置 5.5,5.6,5.7 版本，其他版本可能會有問題。
<property name="useSqlStat">0</property>
是否開啟實時統計。1為開啟；0為關閉 。
<property name="useGlobleTableCheck">0</property>
是否開啟全局表一致性檢測。1為開啟；0為關閉 。
<property name="handleDistributedTransactions">0</property>
分布式事務開關。0為不過濾分布式事務；1為過濾分布式事務；2 為不過濾分布式事務,但是記錄分布式事務日志。
<property name="maxStringLiteralLength">65535</property>
默認是65535。 64K 用于sql解析時最大文本長度以上舉例的屬性僅僅是一部分，可以配置的變量很多。
System標簽下的屬性，一般是上線后，需要根據實際運行的情況，分析后調優的時候進行修改。
<firewall>
<whitehost>
<host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost>
<blacklist check="false"> </blacklist>
</firewall>
50/52
schema.xml優化
一：schema.xml
–schema 數據庫設置，此數據庫為邏輯數據庫，name與server.xml中schema對應
–dataNode 分片信息，也就是分庫相關配置 –dataHost 物理數據庫，真正存儲數據的數據庫
1、schema 標簽
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="10"> </schema> schema標簽用來定義mycat實例中的邏輯庫，mycat可以有多個邏輯庫，每個邏輯庫都有自己的相關配置。可以使用schema標簽來劃分這些不同的邏輯庫,如果不配置schema標簽，所有表的配置會屬于同一個默認的
邏輯庫。邏輯庫的概念和MySql的database的概念一樣，我們在查詢兩個不同邏輯庫中的表的時候，需要切換到該邏輯庫下進行查詢。
–name 邏輯數據庫名，與server.xml中的schema對應 –checkSQLschema 數據庫前綴相關設置，當該值為true時，例如我們執行語句select from
TESTDB.company 。mycat會把語句修改為 select from company 去掉TESTDB。
–sqlMaxLimit 當該值設置為某個數值時，每條執行的sql語句，如果沒有加上limit語句，Mycat會自動加上對應的值。不寫的話，默認返回所有的值。需要自己sql語句加limit。
2、dataNode標簽
<dataNode name="dn1" dataHost="localhost1" database="db1" /> datanode標簽定義了mycat中的數據節點，也就是數據分片。一個datanode標簽就是一個獨立的數據分片。localhost1數據庫實例上的db1物理數據庫，這就組成一個數據分片，最后我們用dn1來標示這個分片。
–name 定義數據節點的名字，這個名字需要唯一。我們在table標簽上用這個名字來建立表與分片對應的關
系
–dataHost 用于定義該分片屬于哪個數據庫實例，屬性與datahost標簽上定義的name對應 –database 用于定義該分片屬于數據庫實例上 的具體庫。
3、dataHost標簽
這個標簽直接定義了具體數據庫實例，讀寫分離配置和心跳語句。
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456"> <readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" /> </writeHost>
</dataHost>
–name 唯一標示dataHost標簽，供上層使用
–maxCon 指定每個讀寫實例連接池的最大連接。
–minCon 指定每個讀寫實例連接池的最小連接，初始化連接池的大小
–balance 負載均稱類型
balance=“0”：不開啟讀寫分離機制，所有讀操作都發送到當前可用的writeHost上balance=“1”：全部的readHost與stand by writeHost參與select語句的負載均衡，簡單的說，當雙主雙從模式（M1-S1，M2-S2 并且M1 M2互為主備），正常情況下，M2,S1,S2都參與select語句的負載均衡。balance=“2”：所有讀操作都隨機的在writeHost、readHost上分發balance=“3”：所有讀請求隨機的分發到writeHst對應的readHost執行，writeHost不負擔讀寫壓力。 –writeType 負載均衡類型。 writeType=“0”, 所有寫操作發送到配置的第一個 writeHost，第一個掛了切到還生存的第二個writeHost，重新
啟動后已切換后的為準，切換記錄在配置文件中:dnindex.properties . writeType=“1”，所有寫操作都隨機的發送到配置的 writeHost。1.5以后版本廢棄不推薦。
–switchType -1不自動切換
1 默認值 自動切換
2 基于MySql主從同步的狀態決定是否切換心跳語句為 show slave status 3 基于mysql galary cluster 的切換機制（適合集群） 心跳語句為 show status like ‘wsrep%’
–dbType 指定后端鏈接的數據庫類型目前支持二進制的mysql協議，還有其他使用jdbc鏈接的數據庫，例
51/52
如：mongodb，oracle，spark等
–dbDriver 指定連接后段數據庫使用的driver，目前可選的值有native和JDBC。使用native的話，因為這個值執行的是二進制的mysql協議，所以可以使用mysql和maridb，其他類型的則需要使用JDBC驅動來支持。
如果使用JDBC的話需要符合JDBC4標準的驅動jar 放到mycat\lib目錄下，并檢查驅動jar包中包括如下目錄結構文件 META-INF\services\java.sql.Driver。 在這個文件寫上具體的driver類名，例如com.mysql.jdbc.Driver writeHost readHost指定后端數據庫的相關配置給mycat，用于實例化后端連接池。
–tempReadHostAvailable
如果配置了這個屬性 writeHost 下面的 readHost 仍舊可用，默認 0 可配置（0、1）。
1）heartbeat標簽
這個標簽內指明用于和后端數據庫進行心跳檢查的語句。
例如：MYSQL 可以使用 select user()，Oracle 可以使用 select 1 from dual 等。 2) writeHost /readHost 標簽
這兩個標簽都指定后端數據庫的相關配置，用于實例化后端連接池。唯一不同的是，writeHost 指定寫實例、readHost 指定讀實例。
在一個 dataHost 內可以定義多個 writeHost 和 readHost。但是，如果 writeHost 指定的后端數據庫宕機，那么這個 writeHost 綁定的所有 readHost 都將不可用。
另一方面，由于這個 writeHost 宕機，系統會自動的檢測到，并切換到備用的 writeHost 上去。這兩個標簽的屬性相同，這里就一起介紹。
–host 用于標識不同實例，一般 writeHost 我們使用M1，readHost 我們用S1。 –url 后端實例連接地址。Native：地址：端口 JDBC：jdbc的url –password 后端存儲實例需要的密碼
–user 后端存儲實例需要的用戶名字
–weight 權重 配置在 readhost 中作為讀節點的權重 –usingDecrypt 是否對密碼加密，默認0。
52/52
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
