溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
禁用郵件通知：
vi /etc/profile
在末尾添加
#禁止郵件提示 unset MAILCHECK
數(shù)據(jù)庫(kù)部署及引擎管理
數(shù)據(jù)庫(kù)簡(jiǎn)介
數(shù)據(jù)庫(kù)技術(shù)構(gòu)成
1.數(shù)據(jù)庫(kù)系統(tǒng) DBS
A.數(shù)據(jù)庫(kù)管理系統(tǒng)（DataBase Management System， DBMS）: SQL(RDS): ORACLE、Oracle MySQL、MariaDB、Percona server、DB2 NoSQL: Redis、MongoDB、Memcache
B.DBA數(shù)據(jù)庫(kù)管理員
2.SQL語(yǔ)言（Structured Query Language 即結(jié)構(gòu)化查詢語(yǔ)言）
A. DDL語(yǔ)句 數(shù)據(jù)庫(kù)定義語(yǔ)言： 數(shù)據(jù)庫(kù)、表、視圖、索引、存儲(chǔ)過程、函數(shù)， CREATE DROP
ALTER //開發(fā)人員
B. DML語(yǔ)句 數(shù)據(jù)庫(kù)操縱語(yǔ)言： 插入數(shù)據(jù)INSERT、刪除數(shù)據(jù)DELETE,drop、更新數(shù)據(jù)
UPDATE //開發(fā)人員
C. DQL語(yǔ)句 數(shù)據(jù)庫(kù)查詢語(yǔ)言： 查詢數(shù)據(jù) SELECT
D. DCL語(yǔ)句 數(shù)據(jù)庫(kù)控制語(yǔ)言： 例如控制用戶的訪問權(quán)限GRANT、REVOKE
grant all on . root to root@localhost identified by '123'
3.數(shù)據(jù)訪問技術(shù)
A.ODBC PHP <.php>
B.JDBC JAVA <.jsp>
主 從
主IP
數(shù)據(jù)庫(kù)分類關(guān)系型：mysql、甲骨文、IBM、微軟
非關(guān)系型數(shù)據(jù)庫(kù)：mogoDB、redis、memcache (數(shù)據(jù)庫(kù)緩存服務(wù)器) 1 name
2 key
數(shù)據(jù)庫(kù)部署
Mysql的YUM安裝
1/52
mysql的官方網(wǎng)站：www.mysql.com mysql.org
點(diǎn)擊downloadshttps點(diǎn)擊最下面的mysql community 1.下載mysql的yum倉(cāng)庫(kù)
[root@22e34e653991 /]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 2.安裝mysql的yum倉(cāng)庫(kù) [root@22e34e653991 /]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
3.修改安裝版本（方法一）
[root@22e34e653991 /]# yum repolist all | grep mysql 查看所有關(guān)于mysql的庫(kù)
[root@22e34e653991 /]# yum -y install yum-utils yum的工具包
[root@22e34e653991 /]# yum-config-manager --enable mysql57-community 將禁用的yum源庫(kù)啟用
[root@22e34e653991 /]# yum-config-manager --disable mysql80-community 將啟用的yum源庫(kù)禁用
4.安裝數(shù)據(jù)庫(kù)
[root@22e34e653991 /]# yum -y install mysql mysql-server
修改yum倉(cāng)庫(kù)（方法二）
5.查看數(shù)據(jù)庫(kù)的初始密碼
[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'
密碼：大小寫有特殊字符數(shù)字
mysql使用弱密碼登陸
編輯mysql的配置文件
[root@mysql1 mysql]# vim /etc/my.cnf [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data validate_password=off 添加后可設(shè)置弱密碼強(qiáng)度
lower_case_table_names=1 不區(qū)分大小寫
Mysql的編譯安裝
源碼安裝
與二進(jìn)制(RPM)發(fā)行版本相比，如果我們選擇了通過源代碼進(jìn)行安裝，那么在安裝過程中我們能夠?qū)ySQL所做的調(diào)整將會(huì)更多更靈活一些。因?yàn)橥ㄟ^源代碼編譯我們可以：
a)針對(duì)自己的硬件平臺(tái)選用合適的編譯器來優(yōu)化編譯后的二進(jìn)制代碼；
b)根據(jù)不同的軟件平臺(tái)環(huán)境調(diào)整相關(guān)的編譯參數(shù)；
c)針對(duì)我們特定應(yīng)用場(chǎng)景選擇需要什么組件不需要什么組件；
d)根據(jù)我們的所需要存儲(chǔ)的數(shù)據(jù)內(nèi)容選擇只安裝我們需要的字符集；
2/52
e)同一臺(tái)主機(jī)上面可以安裝多個(gè)MySQL；
f)等等其他一些可以根據(jù)特定應(yīng)用場(chǎng)景所作的各種調(diào)整。
在源碼安裝給我們帶來更大靈活性的同時(shí)，同樣也給我們帶來了可能引入的隱患：
a)對(duì)編譯參數(shù)的不夠了解造成編譯參數(shù)使用不當(dāng)可能使編譯出來的二進(jìn)制代碼不夠穩(wěn)定；
b)對(duì)自己的應(yīng)用環(huán)境把握失誤而使用的優(yōu)化參數(shù)可能反而使系統(tǒng)性能更差；
c)還有一個(gè)并不能稱之為隱患的小問題就是源碼編譯安裝將使安裝部署過程更為復(fù)雜，所花費(fèi)的時(shí)間更長(zhǎng)；
Mysql部署
1.準(zhǔn)備編譯環(huán)境
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake
2.準(zhǔn)備源碼包(www.baidu.com;官網(wǎng))
3.清空系統(tǒng)殘留并創(chuàng)建新的賬戶 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.環(huán)境準(zhǔn)備 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 \指定安裝目錄配置文件的位置,默認(rèn)就是etc -DMYSQL_DATADIR=/usr/local/mysql/data \數(shù)據(jù)目錄 錯(cuò)誤日志文件 -DINSTALL_MANDIR=/usr/share/man \ 幫助文檔的目錄 -DMYSQL_TCP_PORT=3306 \ 默認(rèn)端口號(hào)3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ 用來做網(wǎng)絡(luò)通信,啟動(dòng)的時(shí)候才會(huì)產(chǎn)生 -DDEFAULT_CHARSET=utf8 \默認(rèn)字符集
-DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \可以上下翻歷史命令 -DWITH_SSL=system \ -DWITH_EMBEDDED_SERVER=1 \ 嵌入式服務(wù)器
-DENABLED_LOCAL_INFILE=1 \ 支持從本機(jī)導(dǎo)入-DWITH_INNOBASE_STORAGE_ENGINE=1
默認(rèn)存儲(chǔ)引擎
提示:boost也可以使用如下指令自動(dòng)下載
-DDOWNLOAD_BOOST=1
7.編譯
make
8.安裝 make install
9.初始化
cd /usr/local/mysql 把這個(gè)刪了就相當(dāng)于卸載
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.啟動(dòng)MySQL-使用mysqld_safe bin/mysqld_safe --user=mysql & （后臺(tái)運(yùn)行）
./bin/mysqladmin -u root -p'原密碼' password 123
11.使用客戶端測(cè)試
[root@mysql1 bin]#./bin/mysql -u root -p '密碼'
12.創(chuàng)建數(shù)據(jù)庫(kù)配置文件
[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
擴(kuò)展
添加環(huán)境變量：
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
讓環(huán)境變量生效
source /etc/profile
設(shè)置開機(jī)啟動(dòng)：
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基礎(chǔ)
編譯安裝：
[root@47ed2bec974d mysql]# ls
COPYING README bin include mysql-test support-files COPYING-test README-test docs lib share
1、bin目錄
用于放置一些可執(zhí)行文件，如mysql、mysqld、mysqlbinlog等。2、include目錄用于放置一些頭文件，如：mysql.h、mysql_ername.h等。3、lib目錄
用于放置一系列庫(kù)文件。
4、share目錄
用于存放字符集、語(yǔ)言等信息。
yum安裝：
/var/lib/mysql 存放數(shù)據(jù)文件 /usr/share/mysql 用于存放字符集、語(yǔ)言等信息。
數(shù)據(jù)庫(kù)存儲(chǔ)引擎
數(shù)據(jù)庫(kù)存儲(chǔ)引擎是數(shù)據(jù)庫(kù)底層軟件組織，數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平等功能，使用不同的存儲(chǔ)引
擎，還可以 獲得特定的功能?，F(xiàn)在許多不同的數(shù)據(jù)庫(kù)管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。MySQL的核心就是存儲(chǔ)引擎。
InnoDB存儲(chǔ)引擎:
InnoDB是事務(wù)型數(shù)據(jù)庫(kù)的首選引擎，支持事務(wù)安全表（ACID），支持行鎖定和外鍵;InnoDB是默認(rèn)的
MySQL引擎
InnoDB特點(diǎn)：
支持事務(wù)處理，支持外鍵，支持崩潰修復(fù)能力和并發(fā)控制。如果需要對(duì)事務(wù)的完整性要求比較高（比
如銀行），要求實(shí)現(xiàn)并發(fā)控制（比如售票），那選擇InnoDB有很大的優(yōu)勢(shì)。如果需要頻繁的更新、刪除操作的數(shù)據(jù)庫(kù)，也可以選擇InnoDB，因?yàn)橹С质聞?wù)的提交（commit）和回滾（rollback）。
MyISAM存儲(chǔ)引擎：（了解）
MyISAM基于ISAM存儲(chǔ)引擎，并對(duì)其進(jìn)行擴(kuò)展。它是在Web、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事務(wù)。
MyISAM特點(diǎn)：
插入數(shù)據(jù)快，空間和內(nèi)存使用比較低。如果表主要是用于插入新記錄和讀出記錄，那么選擇MyISAM能實(shí)現(xiàn)處理高效率。如果應(yīng)用的完整性、并發(fā)性要求比較低，也可以使用。
MEMORY存儲(chǔ)引擎(了解)
MEMORY存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，未查詢和引用其他表數(shù)據(jù)提供快速訪問
MEMORY特點(diǎn)：
所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高。如果需要很快的讀寫速度，對(duì)數(shù)據(jù)的
安全性要求較低，可以選擇MEMOEY。它對(duì)表的大小有要求，不能建立太大的表。所以，這類數(shù)據(jù)庫(kù)只使用在相對(duì)較小的數(shù)據(jù)庫(kù)表。
引擎功能對(duì)比：
5/52
click me click me click me click me
功 能 MYISAM Memory InnoDB
存儲(chǔ)限制 256TB RAM 64TB
支持事物 No No Yes
支持全文索引 Yes No No
支持?jǐn)?shù)索引 Yes Yes Yes
支持哈希索引 No Yes No
支持?jǐn)?shù)據(jù)緩存 No N/A Yes
支持外鍵 No No Yes
如何選擇引擎：
如果要提供提交、回滾、崩潰恢復(fù)能力的事物安全（ACID兼容）能力，并要求實(shí)現(xiàn)并發(fā)控制，InnoDB是一個(gè)好的選擇；如果數(shù)據(jù)表主要用來插入和查詢記錄，則MyISAM引擎能提供較高的處理效率；如果只是臨時(shí)存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存中的Memory引擎；MySQL中使用該引擎作為臨時(shí)表，存放查詢的中間結(jié)果；如果只有INSERT和SELECT操作，可以選擇
Archive，Archive支持高并發(fā)的插入操作，但是本身不是事務(wù)安全的。Archive非常適合存儲(chǔ)歸檔數(shù)據(jù)，如記錄日志信息可以使用Archive。
使用哪一種引擎需要靈活選擇，一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同引擎以滿足各種性能和實(shí)際需求，使用合適的存儲(chǔ)引擎，將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能。
存儲(chǔ)引擎查看：
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表示該引擎為當(dāng)前默認(rèn)的存儲(chǔ)引擎
mysql> alter table service engine=innodb;
Mysql事務(wù)
6/52
MySQL 事務(wù)
MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你即需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)！
?在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。
?事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的 SQL 語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。
?事務(wù)用來管理 insert,update,delete 語(yǔ)句
一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。
? 原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)
環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。
?一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。
?隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)
執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。
?持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。
在MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交的，即執(zhí)行 SQL 語(yǔ)句后就會(huì)馬上執(zhí)行 COMMIT 操作。因此要顯式地開啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來禁止使用當(dāng)前會(huì)話的自動(dòng)提交。
MYSQL 事務(wù)處理主要有兩種方法：
1、用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn)
?BEGIN 開始一個(gè)事務(wù)
?ROLLBACK 事務(wù)回滾
?COMMIT 事務(wù)確認(rèn)
2、直接用 SET 來改變 MySQL 的自動(dòng)提交模式:
?SET AUTOCOMMIT=0 禁止自動(dòng)提交
?SET AUTOCOMMIT=1 開啟自動(dòng)提交
show variables like 'autocommit'; //查看是否修改成功
注意：在編寫應(yīng)用程序時(shí)，最好事務(wù)的控制權(quán)限交給開發(fā)人員
表管理及數(shù)據(jù)類型
數(shù)據(jù)類型
分類：
7/52
數(shù)值類型字符串類型
時(shí)間和日期類型
數(shù)值類型： TINYINT SMALLINT MEDIUMINT INT BIGINT
整數(shù)類型
作用：用于存儲(chǔ)用戶的年齡、游戲的Level、經(jīng)驗(yàn)值等。
類型 大小 范圍（有符號(hào)） 范圍（無符號(hào)）
TINYINT 1 字節(jié) (-128，127) (0，255)
SMALLINT 2 字節(jié) (-32 768，32 767) (0，65 535)
MEDIUMINT 3 字節(jié) (-8 388 608，8 388 607) (0，16 777 215)
INT或INTEGER 4 字節(jié) (-2 147 483 648，2 147 483 647) (0，4 294 967 295)
BIGINT 8 字節(jié) (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 6
浮點(diǎn)數(shù)類型 FLOAT DOUBLE
作用：用于存儲(chǔ)用戶的身高、體重、薪水等
float(5.3) 5寬度 3精度
寬度不算小數(shù)點(diǎn)
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 字節(jié) (-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 字節(jié) (-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)
定點(diǎn)數(shù)類型 DEC 定點(diǎn)數(shù)在MySQL內(nèi)部以字符串形式存儲(chǔ)，比浮點(diǎn)數(shù)更精確，適合用來表示貨幣等精度高的數(shù)據(jù)。
位類型 BIT BIT(M)可以用來存放多位二進(jìn)制數(shù)，M范圍從1~64，如果不寫默認(rèn)為1位
字符串類型：
CHAR系列 CHAR VARCHAR
TEXT系列TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚舉類型： SET ENUM
集合類型：
類型 大小 用途
CHAR 0-255字節(jié) 定長(zhǎng)字符串
VARCHAR 0-65535 字節(jié) 變長(zhǎng)字符串
TINYBLOB 0-255字節(jié) 不超過 255 個(gè)字符的二進(jìn)制字符串
TINYTEXT 0-255字節(jié) 短文本字符串
BLOB 0-65 535字節(jié) 二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)
TEXT 0-65 535字節(jié) 長(zhǎng)文本數(shù)據(jù)
枚舉類型： 枚舉列可以把一些不重復(fù)的字符串存儲(chǔ)成一個(gè)預(yù)定義的集合 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
時(shí)間和日期類型： DATE TIME DATETIME TIMESTAMP YEAR
作用：用于存儲(chǔ)用戶的注冊(cè)時(shí)間，文章的發(fā)布時(shí)間，文章的更新時(shí)間，員工的入職時(shí)間等
類型 大小 范圍 格式
(字節(jié))
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
結(jié)束時(shí)間是第 2147483647 秒，北京時(shí)間 2038-1-19 11:14:07，格林尼治時(shí)間
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 類型的列還有個(gè)特性：默認(rèn)情況下，在 insert, update 數(shù)據(jù)時(shí)，timestamp 列會(huì)自動(dòng)以當(dāng)前時(shí)
間（CURRENT_TIMESTAMP）填充/更新。“自動(dòng)”的意思就是，你不去管它，MySQL 會(huì)替你去處理。
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)
擴(kuò)展：
select now();查看當(dāng)前時(shí)間
表操作
MySQL表操作 DDL
表是數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的基本單位，由若干個(gè)字段組成，主要用來存儲(chǔ)數(shù)據(jù)記錄。表的操作包括:
創(chuàng)建表、查看表、修改表和刪除表。
9/52
這些操作都是數(shù)據(jù)庫(kù)管理中最基本，也是最重要的操作。本節(jié)內(nèi)容包括:
創(chuàng)建表 create table
查看表結(jié)構(gòu) desc table, show create table 修改表 alter table
復(fù)制表 create table ...
刪除表 drop table
一、創(chuàng)建表（表的基本操作）表:school.student1
字段 字段 字段
id name sex age
1 tom male 23 記錄
2 jack male 21 記錄
3 alice female 19 記錄
語(yǔ)法：
create table 表名(自定義)(
字段名1 類型[(寬度) 約束條件],字段名2 類型[(寬度) 約束條件],字段名3 類型[(寬度) 約束條件]
)[存儲(chǔ)引擎 字符集]; ==在同一張表中，字段名是不能相同==寬度和約束條件可選==字段名和類型是必須的
mysql> CREATE DATABASE school; //創(chuàng)建數(shù)據(jù)庫(kù)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)
查看表（當(dāng)前所在庫(kù)）
mysql> show tables;
+------------------ +
| Tables_in_school |
+------------------ +
| student1 |
+------------------ +
1 row in set (0.00 sec)
向表中插入內(nèi)容
語(yǔ)法：
insert into 表名(字段1,字段2... ) values(字段值列表...);
mysql> insert into student1(id,name,sex,age) values(1,'xingdia','m','26');
查看表結(jié)構(gòu)
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
字段名 數(shù)據(jù)類型
編號(hào) id int
姓名 name varchar(50)
出生年份 born_year year
生日 birthday date
上課時(shí)間 class_time time
注冊(cè)時(shí)間 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;
二、查看表結(jié)構(gòu)
DESCRIBE查看表結(jié)構(gòu)
11/52
DESCRIBE 表名;
DESC 表名;
SHOW CREATE TABLE查看表詳細(xì)結(jié)構(gòu)
SHOW CREATE TABLE 表名;
三、表完整性約束作用：用于保證數(shù)據(jù)的完整性和一致性
約束條件 說明
PRIMARY KEY (PK) 標(biāo)識(shí)該字段為該表的主鍵，可以唯一的標(biāo)識(shí)記錄，不可以為空 UNIQUE +
NOT NULL
FOREIGN KEY (FK) 標(biāo)識(shí)該字段為該表的外鍵，實(shí)現(xiàn)表與表（父表主鍵/子表1外鍵/子表2外鍵）之
間的關(guān)聯(lián)
NOT NULL 標(biāo)識(shí)該字段不能為空
UNIQUE KEY (UK) 標(biāo)識(shí)該字段的值是唯一的，可以為空，一個(gè)表中可以有多個(gè)UNIQUE KEY
AUTO_INCREMENT 標(biāo)識(shí)該字段的值自動(dòng)增長(zhǎng)（整數(shù)類型，而且為主鍵）
DEFAULT 為該字段設(shè)置默認(rèn)值
說明：
1.是否允許為空，默認(rèn)NULL，可設(shè)置NOT NULL，字段不允許為空，必須賦值
2.字段是否有默認(rèn)值，缺省的默認(rèn)值是NULL，如果插入記錄時(shí)不給字段賦值，此字段使用默認(rèn)值
sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必須為正值（無符號(hào)） 不允許為空 默認(rèn)是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
設(shè)置唯一約束 UNIQUE
MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。
表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 | |
+-----------+-------------+------+-----+---------+-------+
設(shè)置主鍵約束 PRIMARY KEY
primary key 字段的值是不允許重復(fù)，且不允許不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;
設(shè)置字段值增 AUTO_INCREMENT 表company.department3
CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30),
comment VARCHAR(50) );
四、修改表ALTER TABLE
語(yǔ)法：
ALTER TABLE 表名 RENAME 新表名;
ALTER TABLE 表名
ADD 字段名 數(shù)據(jù)類型 [完整性約束條件…] AFTER 字段名;
刪除字段
ALTER TABLE 表名 DROP 字段名;
13/52
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 舊數(shù)據(jù)類型 [完整性約束條件…];
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 新數(shù)據(jù)類型 [完整性約束條件…];
示例：
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.增加主鍵和自動(dòng)增長(zhǎng) 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
五、復(fù)制表
復(fù)制表結(jié)構(gòu)＋記錄 （key不會(huì)復(fù)制: 主鍵、外鍵和索引）復(fù)制表結(jié)構(gòu)/記錄+表結(jié)構(gòu)，不會(huì)將Key復(fù)制 mysql> create table new_service select * from service;
只復(fù)制表結(jié)構(gòu)
mysql> create table new1_service select * from service where 1=2; //條件為假，查不到任何記錄
可以復(fù)制主鍵,只復(fù)制表結(jié)構(gòu) mysql> create table t4 like employees;
六、刪除表
DROP TABLE 表名;
表操作（擴(kuò)展）
修改數(shù)據(jù)表中字段的值:語(yǔ)法：
Update 表名 set 列名=值where 條件 update student set name='123' where id=1
刪除某一行：語(yǔ)法：
delete from 表名 where id=1 delete from type where id=1
id name
1xingdian renren
庫(kù)操作
系統(tǒng)數(shù)據(jù)庫(kù)
information_schema： 虛擬庫(kù)，主要存儲(chǔ)了系統(tǒng)中的一些數(shù)據(jù)庫(kù)對(duì)象的信息，例如用戶表信息、列信息、權(quán)限信息、字符信息等
performance_schema： 主要存儲(chǔ)數(shù)據(jù)庫(kù)服務(wù)器的性能參數(shù)
mysql： 授權(quán)庫(kù)，主要存儲(chǔ)系統(tǒng)用戶的權(quán)限信息
sys： 主要存儲(chǔ)數(shù)據(jù)庫(kù)服務(wù)器的性能參數(shù)
創(chuàng)建數(shù)據(jù)庫(kù)：DDL 1. #mysqladmin -u root -p1 create db1
2.直接去創(chuàng)建數(shù)據(jù)庫(kù)目錄并且修改權(quán)限
3.mysql> create database xingdian;
數(shù)據(jù)庫(kù)命名規(guī)則：區(qū)分大小寫唯一性
不能使用關(guān)鍵字如 create select
不能單獨(dú)使用數(shù)字
查看數(shù)據(jù)庫(kù) mysql> show databases;
mysql> show create database xingdian; mysql> select database(); 查看當(dāng)前所在的庫(kù)
15/52
切換數(shù)據(jù)庫(kù) mysql> use xingdian; mysql> show tables;
刪除數(shù)據(jù)庫(kù)
DROP DATABASE 數(shù)據(jù)庫(kù)名;
數(shù)據(jù)庫(kù)查詢
數(shù)據(jù)庫(kù)查詢
單表查詢
簡(jiǎn)單查詢通過條件查詢查詢排序
限制查詢記錄數(shù)使用集合函數(shù)查詢分組查詢使用正則表達(dá)式查詢
測(cè)試表：company.employee5
雇員編號(hào) id int
雇員姓名 name varchar(30)
雇員性別 sex enum
雇用時(shí)期 hire_date date
職位 post varchar(50)
職位描述 job_description varchar(100)
薪水 salary double(15,2)
辦公室 office int
部門編號(hào) 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 表名 條件
簡(jiǎn)單查詢：
mysql> select * from employee5;
mysql> select name, salary, dep_id from employee5 where id <=5;
避免重復(fù)DISTINCT SELECT post FROM employee5;
SELECT distinct post FROM employee5;
注：不能部分使用DISTINCT，通常僅用于某一字段。
通過四則運(yùn)算查詢
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() 函數(shù)用于連接字符串
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;
關(guān)鍵字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;
關(guān)鍵字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、等價(jià)于沒有任何值、是未知數(shù)。
2、NULL與0、空字符串、空格都不同,NULL沒有分配存儲(chǔ)空間。
3、對(duì)空值做加、減、乘、除等運(yùn)算操作，結(jié)果仍為空。
4、比較時(shí)使用關(guān)鍵字用“is null”和“is not null”。
5、排序時(shí)比其他數(shù)據(jù)都?。ㄋ饕J(rèn)是降序排列，小→大），所以NULL值總是排在最前。
關(guān)鍵字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) ;
關(guān)鍵字LIKE模糊查詢通配符’% ’:所有字符
SELECT * FROM employee5 WHERE name LIKE 'al%';
通配符’_’ 一個(gè)字符
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; 從序號(hào)1開始顯示三行的內(nèi)容。
注：
ascending 美音 /?'s?nd??/ 升序 descending 美音 /d?'s?nd??/ 降序
按多列排序:
入職時(shí)間相同的人薪水不同
SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
限制查詢的記錄數(shù)
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 5; //默認(rèn)初始位置為0
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 0,5;
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 3,5; //從第4條開始，共顯示5條
使用集合函數(shù)查詢
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這個(gè)部門的總工資：
select concat("Total Department Wages：",sum(salary)) from employee5 where post='sale';
打印薪水最高的這個(gè)人的詳細(xì)信息： select * from employee5 where salary = (select max(salary) from employee5);
分組查詢：
GROUP BY和GROUP_CONCAT()函數(shù)一起使用
部門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和集合函數(shù)一起使用部門最高薪資
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}';
小結(jié)：對(duì)字符串匹配的方式
WHERE name = 'tom';
WHERE name LIKE 'to%'; _ %
18/52
WHERE name REGEXP 'yun$'; ^ $ {2}
多表查詢（擴(kuò)展）
表查詢左右內(nèi)鏈接
多表連接查詢復(fù)合條件連接查詢
一、準(zhǔn)備兩張表
一、準(zhǔn)備兩張測(cè)試表
表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:財(cái)務(wù)部門 fd
二、多表的連接查詢
交叉連接： 生成笛卡爾積，它不使用任何匹配條件交叉聯(lián)接返回左表中的所有行，左表中的每一行與右表中的所有行組合
內(nèi)連接： 只連接匹配的行
外連接
左連接： 會(huì)顯示左邊表內(nèi)所有的值，不論在右邊表內(nèi)匹不匹配
右連接： 會(huì)顯示右邊表內(nèi)所有的值，不論在左邊表內(nèi)匹不匹配
全外連接： 包含左、右兩個(gè)表的全部行
=================交叉連接=======================
select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
=================內(nèi)連接=======================
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;
外連接語(yǔ)法：
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)
三、復(fù)合條件連接查詢
示例1：以內(nèi)連接的方式查詢employee6和department6表，并且employee6表中的age字段值必須大于25
找出公司所有部門中年齡大于25歲的員工
示例2：以內(nèi)連接的方式查詢employee6和department6表，并且以age字段的升序方式顯示
四、子查詢
子查詢是將一個(gè)查詢語(yǔ)句嵌套在另一個(gè)查詢語(yǔ)句中。
22/52
內(nèi)層查詢語(yǔ)句的查詢結(jié)果，可以為外層查詢語(yǔ)句提供查詢條件。
子查詢中可以包含：IN、NOT IN等關(guān)鍵字
還可以包含比較運(yùn)算符：= 、 !=、> 、<等
帶IN關(guān)鍵字的子查詢查詢employee表，但dept_id必須在department表中出現(xiàn)過
23/52
數(shù)據(jù)庫(kù)日志管理
日志分類
1 錯(cuò)誤日志 ：?jiǎn)?dòng)，停止，關(guān)閉失敗報(bào)錯(cuò)。rpm安裝日志位置 /var/log/mysqld.log 排錯(cuò)
2 通用查詢?nèi)罩荆核械牟樵兌加浵聛怼?
3 二進(jìn)制日志：實(shí)現(xiàn)備份，增量備份。只記錄改變數(shù)據(jù)，除了select都記。 備份 binlog
4 中繼日志：讀取主服務(wù)器的binlog，在本地回放。保持一致。 復(fù)制
5 slow log：慢查詢?nèi)罩?，指?dǎo)調(diào)優(yōu)，定義某一個(gè)查詢語(yǔ)句，定義超時(shí)時(shí)間，通過日志提供調(diào)優(yōu)建議給開發(fā)
人員。 調(diào)優(yōu)
6 DDL log： 定義語(yǔ)句的日志。
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 會(huì)截?cái)?/p>
2.flush logs 會(huì)截?cái)?/p>
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 設(shè)置慢查詢超時(shí)時(shí)間 單位是：秒驗(yàn)證:
#mysql -u root -p2
select sleep(6); #執(zhí)行一個(gè)超過6秒的查詢操作
#cat /var/log/mysql/slow-log
權(quán)限控制和備份恢復(fù)
權(quán)限控制
mask
權(quán)限級(jí)別
權(quán)限級(jí)別：
Global level：系統(tǒng)級(jí)，所有庫(kù)，所有表，的權(quán)限 Database level：某個(gè)數(shù)據(jù)庫(kù)中的所有表，的權(quán)限 Table level：庫(kù)中的某個(gè)表，的權(quán)限 Column level：表中的某個(gè)字段，的權(quán)限 procs level：某個(gè)存儲(chǔ)過程，的權(quán)限 proxies level：代理服務(wù)器，的權(quán)限
查看權(quán)限記錄表：因?yàn)槌?jí)管理員默認(rèn)已經(jīng)設(shè)置。所以直接查詢權(quán)限即可。
Global level
select * from mysql.user\G;
用戶字段：root
權(quán)限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
Database level
select * from mysql.db\G;
25/52
測(cè)試庫(kù)權(quán)限 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
驗(yàn)證：
#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';
驗(yàn)證：
#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');
前提是有庫(kù)，有表，有權(quán)限。
++++mysql ++++web（farm）
不允許root賬戶 給web項(xiàng)目普通用戶 farm -----> 庫(kù)（單個(gè)/多個(gè)）
sql漏洞注入------>脫庫(kù)
用戶管理
注意： 修改（update，grant）完權(quán)限后，要刷新授權(quán)表，目錄讓授權(quán)的用戶的權(quán)限立即生效。
MySQL用戶管理
1.登錄和退出MySQL
#mysql -h292.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’
-h 指定主機(jī)名 【默認(rèn)為localhost】
-P MySQL服務(wù)器端口 【默認(rèn)3306】
-u 指定用戶名 【默認(rèn)root】
-p 指定登錄密碼 【默認(rèn)為空密碼】
此處mysql為指定登錄的數(shù)據(jù)庫(kù) -e 接SQL語(yǔ)句 (在腳本中使用)
方法一：CREATE USER語(yǔ)句創(chuàng)建 mysql> create user xingdian;
26/52
mysql> create user xingdian@’%’ identified by '123456'; 這樣可以直接從遠(yuǎn)程登錄
方法二： GRANT語(yǔ)句創(chuàng)建(授權(quán)) mysql> GRANT ALL ON . TO 'xingdian'@’localhost’ IDENTIFIED BY ‘123456’;
mysql> grant select,insert on k1.* to admin1@'%' identified by '123';
FLUSH PRIVILEGES;
查看單獨(dú)的庫(kù)權(quán)限 mysql> select * from mysql.db\G
注意： ALL 單獨(dú)的權(quán)限 . 單獨(dú)的庫(kù)和單獨(dú)的表
xingdian@localhost 用戶有則授權(quán)無則創(chuàng)建 localhost % 10.19.40.% 10.19.40.11 3. 刪除用戶方法一：DROP USER語(yǔ)句刪除
DROP USER 'user1'@’localhost’;
方法二：DELETE語(yǔ)句刪除
DELETE FROM mysql.user WHERE user='user2' AND host=’localhost’;
FLUSH PRIVILEGES; 刷新授權(quán)表
方法二：
mysql> update mysql.user set authentication_string=password('Qianfeng123!') where user='root' and host='localhost';
方法三：
給那個(gè)用戶設(shè)置密碼，你要在那個(gè)用戶下執(zhí)行
SET PASSWORD=password(‘new_password’);
上面方法將會(huì)在后面的版本remove,使用下面方法
SET PASSWORD='new_password'; 直接設(shè)置密碼
root修改其他用戶密碼方法一：
mysql> SET PASSWORD FOR user3@'localhost'=password('new_password');
上面的方法會(huì)在將來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!@';
查看現(xiàn)有的密碼策略 mysql> SHOW VARIABLES LIKE 'validate_password%';
參數(shù)解釋：
1).validate_password_dictionary_file 指定密碼驗(yàn)證的文件路徑; 2).validate_password_length 密碼最小長(zhǎng)度 3).validate_password_mixed_case_count 密碼至少要包含的小寫字母?jìng)€(gè)數(shù)和大寫字母?jìng)€(gè)數(shù); 4).validate_password_number_count 密碼至少要包含的數(shù)字個(gè)數(shù)
5).validate_password_policy 密碼強(qiáng)度檢查等級(jí)，對(duì)應(yīng)等級(jí)為：0/LOW、1/MEDIUM、2/STRONG,默認(rèn)為1 0/LOW：只檢查長(zhǎng)度; 1/MEDIUM：檢查長(zhǎng)度、數(shù)字、大小寫、特殊字符;
2/STRONG：檢查長(zhǎng)度、數(shù)字、大小寫、特殊字符字典文件。
6).validate_password_special_char_count密碼至少要包含的特殊字符數(shù)
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; 刷新授權(quán)表
注意：報(bào)錯(cuò)處理
問題1：ERROR You must reset your password using ALTER USER statement before executing this statement
解決方案：ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xiaoming250';
報(bào)錯(cuò)解決方案
創(chuàng)建用戶時(shí)報(bào)錯(cuò)：
mysql> create user 'miner'@'192.168.%' IDENTIFIED BY 'miner123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
報(bào)錯(cuò)原因：密碼強(qiáng)度不夠。
解決方法：(該賬號(hào)為測(cè)試賬號(hào)，所以采用降低密碼策略強(qiáng)度) 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)
再次創(chuàng)建用戶，成功
報(bào)錯(cuò)：
mysql> SET PASSWORD FOR xingdian@'localhost'=password('QianFeng!123'); ERROR 1133 (42000): Can't find any matching row in the user table
解決方案：
報(bào)錯(cuò)：
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'
解決方案：
備份恢復(fù)
概述
MySQL數(shù)據(jù)備份
所有備份數(shù)據(jù)都應(yīng)放在非數(shù)據(jù)庫(kù)本地，而且建議有多份副本。測(cè)試環(huán)境中做日?；謴?fù)演練，恢復(fù)較備份更為重要。
備份： 能夠防止由于機(jī)械故障以及人為誤操作帶來的數(shù)據(jù)丟失，例如將數(shù)據(jù)庫(kù)文件保存在了其它地方。
冗余： 數(shù)據(jù)有多份冗余，但不等備份，只能防止機(jī)械故障還來的數(shù)據(jù)丟失，例如主備模式、數(shù)據(jù)庫(kù)集群。
備份過程中必須考慮因素：
1.數(shù)據(jù)的一致性
2.服務(wù)的可用性
邏輯備份：
備份的是建表、建庫(kù)、插入等操作所執(zhí)行SQL語(yǔ)句（DDL DML DCL），適用于中小型數(shù)據(jù)庫(kù)，效率相對(duì)較低。 mysqldump
物理備份：
直接復(fù)制數(shù)據(jù)庫(kù)文件，適用于大型數(shù)據(jù)庫(kù)環(huán)境，不受存儲(chǔ)引擎的限制，但不能恢復(fù)到不同的MySQL版
本。 tar,cp xtrabackup lvm snapshot
完全備份
增量備份:每次備份上一次備份到現(xiàn)在產(chǎn)生的新數(shù)據(jù)
29/52
差異備份:只備份跟完整備份不一樣的
tar數(shù)據(jù)備份(物理)
tar備份數(shù)據(jù)庫(kù)注：備份期間，服務(wù)不可用
備份的過程：【完全物理備份】
1.停止數(shù)據(jù)庫(kù)
2.tar備份數(shù)據(jù)
3.啟動(dòng)數(shù)據(jù)庫(kù)
[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 ./*
注：備份文件應(yīng)該復(fù)制其它服務(wù)器或存儲(chǔ)上
30/52
還原的過程：
1.停止數(shù)據(jù)庫(kù)
2.清理環(huán)境
3.導(dǎo)入備份數(shù)據(jù)
4.啟動(dòng)數(shù)據(jù)庫(kù)
[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
它是開源免費(fèi)的支持MySQL 數(shù)據(jù)庫(kù)熱備份的軟件，它能對(duì)InnoDB和XtraDB存儲(chǔ)引擎的數(shù)據(jù)庫(kù)非阻塞地
備份。它不暫停服務(wù)創(chuàng)建Innodb熱備份；為mysql做增量備份；在mysql服務(wù)器之間做在線表遷移；使創(chuàng)建replication更加容易；備份mysql而不增加服務(wù)器的負(fù)載。
percona是一家老牌的mysql技術(shù)咨詢公司。它不僅提供mysql的技術(shù)支持、培訓(xùn)、咨詢，還發(fā)布了mysql
的分支版本--percona Server。并圍繞percona Server還發(fā)布了一系列的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的源單獨(dú)安裝，然后再安裝 percona-xtrabackup-24.x86_64
完整備份
完全備份流程創(chuàng)建備份目錄：
[root@xingdian full]# mkdir -p /xtrabackup/full
備份：
[root@xingdian full]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full
完全備份恢復(fù)流程
1.停止數(shù)據(jù)庫(kù)
2.清理環(huán)境
3.重演回滾－－> 恢復(fù)數(shù)據(jù)
4.修改權(quán)限
5.啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉數(shù)據(jù)庫(kù)：
#systemctl stop mysqld
#rm -rf /var/lib/mysql/*
#rm -rf /var/log/mysqld.log
#rm -rf /var/log/mysql-slow/slow.log (有則刪除，無則不需要操作)
恢復(fù)之前的驗(yàn)證恢復(fù)：
#innobackupex --apply-log /xtrabackup/full/2019-08-20_15-57-31/
確認(rèn)數(shù)據(jù)庫(kù)目錄：
恢復(fù)之前需要確認(rèn)配置文件內(nèi)有數(shù)據(jù)庫(kù)目錄指定，不然xtrabackup不知道恢復(fù)到哪里
恢復(fù)數(shù)據(jù)：
[root@xingdian mysql]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/
修改權(quán)限：
[root@xingdian mysql]# chown mysql.mysql /var/lib/mysql -R
啟動(dòng)數(shù)據(jù)庫(kù)：
[root@xingdian mysql]# systemctl start mysqld
驗(yàn)證數(shù)據(jù)：
[root@xingdian mysql]# mysql -u root -pQianFeng@123 mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| k1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
增量備份
增量備份流程
原理：每次備份上一次備份到現(xiàn)在產(chǎn)生的新數(shù)據(jù)
32/52
創(chuàng)建備份目錄：
[root@xingdian ~]# mkdir -p /xtrabackup/full
1、完整備份：周一
[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full 2、增量備份：周二 —— 周六
需要?jiǎng)?chuàng)建數(shù)據(jù)
[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
需要?jiǎng)?chuàng)建數(shù)據(jù)
[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
……
增量備份恢復(fù)流程
1.停止數(shù)據(jù)庫(kù)
2.清理環(huán)境
3.依次重演回滾redo log－－> 恢復(fù)數(shù)據(jù)
4.修改權(quán)限
5.啟動(dòng)數(shù)據(jù)庫(kù)
[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/
……
恢復(fù)數(shù)據(jù)
[root@xingdian ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/ (datadir)
修改權(quán)限
[root@xingdian ~]# chown -R mysql.mysql /var/lib/mysql
[root@xingdian ~]# systemctl start mysqld
驗(yàn)證恢復(fù)：
[root@xingdian ~]# mysql -u root -pQianFeng@123
mysql> show databases;
+-------------------- +
| Database |
+-------------------- +
| information_schema |
| k1 |
| k2 |
33/52
| k3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
差異備份
差異備份流程：只備份跟完整備份不一樣的
創(chuàng)建備份目錄：
[root@xingdian ~]# mkdir -p /xtrabackup/full
1、完整備份：周一
[root@xingdian ~]# innobackupex --user=root --password=888 /xtrabackup/full
2、差異備份：周二 —— 周六創(chuàng)建備份目錄：
[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/完全備份目錄（周一）
差異備份恢復(fù)流程
1.停止數(shù)據(jù)庫(kù)
2.清理環(huán)境
3.重演回滾redo log（周一，某次差異）－－> 恢復(fù)數(shù)據(jù)
4.修改權(quán)限
5.啟動(dòng)數(shù)據(jù)庫(kù)
[root@xingdian ~]# systemctl stop mysqld
清理環(huán)境
[root@xingdian ~]# rm -rf /var/lib/mysql/*
4.復(fù)制數(shù)據(jù)文件(cp,rsync)，修改權(quán)限 [root@xingdian ~]# innobackupex --copy-back /xtrabackup/完全備份目錄（周一）
5.啟動(dòng)mysqld
[root@xingdian ~]# systemctl start mysqld
34/52
6.驗(yàn)證恢復(fù)：
[root@xingdian ~]# mysql -u root -pQianFeng@123
mysql> show databases;
+-------------------- +
| Database |
+-------------------- +
| information_schema |
| k1 |
| k2 |
| k3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysqldump（邏輯）
mysqldump實(shí)現(xiàn)邏輯完全備份 + binlog
數(shù)據(jù)一致，服務(wù)可用備份表
備份： # mysqldump -u root -p1 db1 t1 > /db1.t1.sql
恢復(fù)： # mysql -u root -p1 db1 < /db1.t1.sql
備份一個(gè)庫(kù)
備份多個(gè)庫(kù)
#mysqldump -u root -p1 -B db1 db2 db3 > /db123.sql
備份所有的庫(kù)
#mysqldump -u root -p1 -A > /alldb.sql
恢復(fù)數(shù)據(jù)庫(kù)
為保證數(shù)據(jù)一致性，應(yīng)在恢復(fù)數(shù)據(jù)之前停止數(shù)據(jù)庫(kù)對(duì)外的服務(wù),停止binlog日志因?yàn)閎inlog使用binlog日志恢復(fù)數(shù)據(jù)時(shí)也會(huì)產(chǎn)生binlog日志 mysql> set sql_log_bin=0
mysql> source db1.t1.sql
或者
#mysql -u root -p1 -D db1 < db1.t1.sql
常用備份選項(xiàng)：
-A, --all-databases
備份所有庫(kù)
-B, --databases bbs test mysql
備份多個(gè)數(shù)據(jù)庫(kù)
-F, --flush-logs
備份之前刷新binlog日志
35/52
bin_log日志備份
binlog日志方法備份恢復(fù)數(shù)據(jù)記錄每一個(gè)操作
默認(rèn)存儲(chǔ)位置 ： rpm ： /var/lib/mysql
編譯: 安裝目錄的var下
產(chǎn)生binlog日志
一.在啟動(dòng)服務(wù)的時(shí)候啟用日志(臨時(shí)的)
查看binlog日志
方法2. show binlog events; 默認(rèn)查看第一個(gè)
show binlog events in 'mylog.00001';
二.配置文件(永久修改) #vim /etc/my.cnf
[mysqld] log-bin=mylog
server-id=1 //做AB復(fù)制的時(shí)候使用 #/etc/init.d/mysqld restart
根據(jù)binlog恢復(fù)數(shù)據(jù)根據(jù)時(shí)間點(diǎn)恢復(fù)數(shù)據(jù)
根據(jù)位置點(diǎn)恢復(fù)數(shù)據(jù)@后
注：可以同時(shí)讀取多個(gè)日志文件
刷新bin-log日志 #mysqladmin flush-logs -u root -p''
LVM快照備份(物理-擴(kuò)展)
Lvm快照實(shí)現(xiàn)物理備份 + binlog 只保存Inode 號(hào)
數(shù)據(jù)一致，服務(wù)可用
注：MySQL數(shù)據(jù)lv和將要?jiǎng)?chuàng)建的snapshot 必須在同一VG，因此VG必須要有一定的剩于空間
優(yōu)點(diǎn)：
幾乎是熱備 (創(chuàng)建快照前把表上鎖，創(chuàng)建完后立即釋放)支持所有存儲(chǔ)引擎?zhèn)浞菟俣瓤?/p>
無需使用昂貴的商業(yè)軟件(它是操作系統(tǒng)級(jí)別的)缺點(diǎn)：
可能需要跨部門協(xié)調(diào)(使用操作系統(tǒng)級(jí)別的命令，DBA一般沒權(quán)限)無法預(yù)計(jì)服務(wù)停止時(shí)間數(shù)據(jù)如果分布在多個(gè)卷上比較麻煩(針對(duì)存儲(chǔ)級(jí)別而言)
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.安裝系統(tǒng)
2.準(zhǔn)備LVM，例如 /dev/vg_tianyun/lv-mysql，mount /var/lib/mysql
3.安裝MySQL，默認(rèn)datadir=/var/lib/mysql
MySQL運(yùn)行一段時(shí)間，數(shù)據(jù)并沒有存儲(chǔ)LVM：將現(xiàn)在的數(shù)據(jù)遷移到LVM 1. 準(zhǔn)備lvm及文件系統(tǒng)
[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/ //臨時(shí)掛載點(diǎn)
[root@xingdian ~]# cp -a /var/lib/mysql/* /mnt //將MySQL原數(shù)據(jù)鏡像到臨時(shí)掛載點(diǎn)
[root@xingdian ~]# umount /mnt/
[root@xingdian ~]# vim /etc/fstab //加入fstab開機(jī)掛載
/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必須同一會(huì)話中完成
[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快照恢復(fù)流程：
1.停止數(shù)據(jù)庫(kù)
2.清理環(huán)境
3.導(dǎo)入數(shù)據(jù)
4.修改權(quán)限
5.啟動(dòng)數(shù)據(jù)庫(kù)
6.binlog恢復(fù)
[root@slave2 ~]# tar -xf /backup/2016-12-07-mysql-all.tar -C /var/lib/mysql/ [root@slave2 ~]# systemctl start mysqld
Tar解壓的注意事項(xiàng)： 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
主從復(fù)制和讀寫分離
主從復(fù)制原理
一、什么是主從復(fù)制?
主從復(fù)制，是用來建立一個(gè)和主數(shù)據(jù)庫(kù)完全一樣的數(shù)據(jù)庫(kù)環(huán)境，稱為從數(shù)據(jù)庫(kù)；主數(shù)據(jù)庫(kù)一般是準(zhǔn)實(shí)時(shí)的業(yè)務(wù)數(shù)據(jù)庫(kù)。
二、主從復(fù)制的作用
1、做數(shù)據(jù)的熱備，作為后備數(shù)據(jù)庫(kù)，主數(shù)據(jù)庫(kù)服務(wù)器故障后，可切換到從數(shù)據(jù)庫(kù)繼續(xù)工作，避免數(shù)據(jù)丟失。
2、架構(gòu)的擴(kuò)展。業(yè)務(wù)量越來越大，I/O訪問頻率過高，單機(jī)無法滿足，此時(shí)做多庫(kù)的存儲(chǔ)，降低磁盤I/O訪問的頻率，提高單個(gè)機(jī)器的I/O性能。
3、讀寫分離，使數(shù)據(jù)庫(kù)能支撐更大的并發(fā)。在報(bào)表中尤其重要。由于部分報(bào)表sql語(yǔ)句非常的慢，導(dǎo)致鎖表，影響前臺(tái)服務(wù)。如果前臺(tái)使用master，報(bào)表使用slave，那么報(bào)表sql將不會(huì)造成前臺(tái)鎖，保證了前臺(tái)速度。
三、主從復(fù)制的原理
1.數(shù)據(jù)庫(kù)有個(gè)bin-log二進(jìn)制文件，記錄了所有sql語(yǔ)句。
2.我們的目標(biāo)就是把主數(shù)據(jù)庫(kù)的bin-log文件的sql語(yǔ)句復(fù)制過來。
38/52
3.讓其在從數(shù)據(jù)的relay-log(中繼日志)重做日志文件中再執(zhí)行一次這些sql語(yǔ)句即可。
四：具體原理
1.binlog輸出線程:每當(dāng)有從庫(kù)連接到主庫(kù)的時(shí)候，主庫(kù)都會(huì)創(chuàng)建一個(gè)線程然后發(fā)送binlog內(nèi)容到從庫(kù)。在從庫(kù)里，當(dāng)復(fù)制開始的時(shí)候，從庫(kù)就會(huì)創(chuàng)建兩個(gè)線程進(jìn)行處理：
2.從庫(kù)I/O線程:當(dāng)START SLAVE語(yǔ)句在從庫(kù)開始執(zhí)行之后，從庫(kù)創(chuàng)建一個(gè)I/O線程，該線程連接到主庫(kù)并請(qǐng)求
主庫(kù)發(fā)送binlog里面的更新記錄到從庫(kù)上。從庫(kù)I/O線程讀取主庫(kù)的binlog輸出線程發(fā)送的更新并拷貝這些更新到本地文件，其中包括relay log文件。3.從庫(kù)的SQL線程:從庫(kù)創(chuàng)建一個(gè)SQL線程，這個(gè)線程讀取從庫(kù)I/O線程寫到relay log的更新事件并執(zhí)行。
可以知道，對(duì)于每一個(gè)主從復(fù)制的連接，都有三個(gè)線程。擁有多個(gè)從庫(kù)的主庫(kù)為每一個(gè)連接到主庫(kù)的從庫(kù)創(chuàng)建一個(gè)binlog輸出線程，每一個(gè)從庫(kù)都有它自己的I/O線程和SQL線程。
主從復(fù)制如圖：
master 對(duì)外提供 負(fù)責(zé)寫數(shù)據(jù) slave 讀master日志寫到relay-log
I/O進(jìn)程：負(fù)責(zé)通信
SQL進(jìn)程：負(fù)責(zé)寫數(shù)據(jù)，根據(jù)log日志寫數(shù)據(jù)。
主從原理：從庫(kù)slave生成兩個(gè)線程，i/o線程和sql線程，i/o將變更記錄寫到二進(jìn)制日志文件中，再寫到中繼日志中，sql線程讀取中繼日志，解析操作，最終數(shù)據(jù)統(tǒng)一
主從復(fù)制部署
AB復(fù)制
環(huán)境：MASTER原來沒有舊數(shù)據(jù)
1.主從都關(guān)閉防火墻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
創(chuàng)建賬戶： mysql> grant replication slave,reload,super on . to 'slave'@'%' identified by '123';
mysql> flush privileges;
重啟服務(wù)：
#systemctl restart mysqld
查看bin_log日志文件的名稱：
show binlog events;
注意:如果不成功刪除以前的binlog日志 replication slave：
擁有此權(quán)限可以查看從服務(wù)器，從主服務(wù)器讀取二進(jìn)制日志。
super權(quán)限：
允許用戶使用修改全局變量的SET語(yǔ)句以及CHANGE MASTER語(yǔ)句reload權(quán)限：
必須擁有reload權(quán)限，才可以執(zhí)行flush [tables | logs | privileges]
3.從
安裝軟件
配置：
#vim /etc/my.cnf [mysqld] server-id = 2
log-bin = my2log //1.使用雙主的時(shí)候必須寫 2.使用gtid方式的ab復(fù)制
啟動(dòng)服務(wù)：
#systemctl restart mysqld
指定主服務(wù)器信息
help change master to 查看從mysql編輯文件
CHANGE MASTER TO MASTER_HOST='mysql-master-1.blackmed.cn/ip', MASTER_USER='slave', //主服務(wù)器用戶 MASTER_PASSWORD='big', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', //日志文件 MASTER_LOG_POS=4, //日志位置 MASTER_CONNECT_RETRY=10; //默認(rèn)嘗試次數(shù)edit
show master status 在主服務(wù)器查看日志文件
啟動(dòng)：start slave
mysql> show slave status \G
如果沒成功：刪除從的binlog重啟服務(wù)
======================================
log_slave_updates參數(shù)：
當(dāng)從庫(kù)log_slave_updates參數(shù)沒有開啟時(shí)，從庫(kù)的binlog不會(huì)記錄來源于主庫(kù)的操作記錄。只有開啟log_slave_updates，從庫(kù)binlog才會(huì)記錄主庫(kù)同步的操作日志 log_slave_updates=1 重啟服務(wù)既可以
40/52
GTID主從復(fù)制
M-S GTID 基于事務(wù)ID復(fù)制
GTID
全局事務(wù)標(biāo)識(shí)：global transaction identifiers
是用來代替?zhèn)鹘y(tǒng)復(fù)制的方法，GTID復(fù)制與普通復(fù)制模式的最大不同就是不需要指定二進(jìn)制文件名和位置，不再使用MASTER_LOG_FILE+MASTER_LOG_POS開啟復(fù)制。而是使用MASTER_AUTO_POSTION=1的方式開始復(fù)制。
MySQL-5.6.5開始支持的，MySQL-5.6.10后開始完善
在傳統(tǒng)的slave端，binlog是不用開啟的，但是在GTID中slave端的binlog是必須開啟的，目的是記錄執(zhí)行過的GTID（強(qiáng)制）。
GTID的組成：
GTID = source_id:transaction_id
source_id源id，用于鑒別原服務(wù)器，即mysql服務(wù)器唯一的的server_uuid，由于GTID會(huì)傳遞到slave，所以也可以理解為源ID。
transaction_id事務(wù)id，為當(dāng)前服務(wù)器上已提交事務(wù)的一個(gè)序列號(hào)，通常從1開始自增長(zhǎng)的序列，一個(gè)數(shù)值對(duì)應(yīng)一個(gè)事務(wù)。
示例：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23
前面的一串為服務(wù)器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23為transaction_id
GTID的工作原理：
1、master更新數(shù)據(jù)時(shí)，會(huì)在事務(wù)前產(chǎn)生GTID，一同記錄到binlog日志中。
2、slave端的i/o 線程將變更的binlog，寫入到本地的relay log中。3、sql線程從relay log中獲取GTID，然后對(duì)比slave端的binlog是否有記錄。
4、如果有記錄，說明該GTID的事務(wù)已經(jīng)執(zhí)行，slave會(huì)忽略。
5、如果沒有記錄，slave就會(huì)從relay log中執(zhí)行該GTID的事務(wù)，并記錄到binlog。
========================================================================
master1 (master) ---------> master2 (slave)
192.168.122.10 192.168.122.20
建議把master2重置，因?yàn)樯弦粋€(gè)實(shí)驗(yàn)環(huán)境的原因
[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
//這兩個(gè)參數(shù)會(huì)將master.info和relay.info保存在表中，默認(rèn)是Myisam引擎，官方建議用
relay_log_recovery = on
41/52
雙主雙從部署
一.環(huán)境四臺(tái)mysql服務(wù)器
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
創(chuàng)建授權(quán)賬戶：
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='授權(quán)用戶', master_password='授權(quán)密碼', master_auto_position=1;start slave;
show slave status\G
注意：
master2上也要?jiǎng)?chuàng)建一樣的授權(quán)用戶，在master1上進(jìn)行change master to 的操作 S——S
slave1：
首先備份master1的所有數(shù)據(jù)，mysqldump -u root -p123 -A > /all.sql 數(shù)據(jù)分別導(dǎo)入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
當(dāng)slave從庫(kù)宕機(jī)后，假如relay-log損壞了，導(dǎo)致一部分中繼日志沒有處理，則自動(dòng)放棄所有未執(zhí)行
42/52
的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性 >help change master to
edit
CHANGE MASTER TO MASTER_HOST='master1/ip', MASTER_USER='slave', //主服務(wù)器用戶 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', //主服務(wù)器用戶 MASTER_PASSWORD='big',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'master2' >start slave;
show slave status\G
注意：slave1和slave2上做相同的change master to 操作。
讀寫分離
Mycat部署
galera(擴(kuò)展)
mysql的組復(fù)制技術(shù) group replication
Galera Replication http://galeracluster.com/downloads/
43/52
準(zhǔn)備環(huán)境：主機(jī)解析：
[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
關(guān)閉防火墻和selinux: setenforce 0 && systemctl stop firewalld
安裝時(shí)間服務(wù)器：yum -y install ntp
時(shí)間同步：修改時(shí)區(qū)：
準(zhǔn)備時(shí)間服務(wù)器：
#vim /etc/ntp.conf
server 127.127.1.0 # local clock fudge 127.127.1.0 stratum 10
#systemctl restart ntpd
客戶端同步時(shí)間：
[root@mysql-galera-2 /]# ntpdate galera1 [root@mysql-galera-3 /]# ntpdate galera1
下載和安裝Galera（每臺(tái)都需要安裝）
注意：Galera有獨(dú)立的補(bǔ)丁包，也有在mysql基礎(chǔ)上打好補(bǔ)丁的完整包我們要下載帶wsrep擴(kuò)展補(bǔ)丁的版本，比如：
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
根據(jù)官方下載提示找到下載路徑，發(fā)現(xiàn)下載路徑下是已經(jīng)做好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
每臺(tái)啟動(dòng)服務(wù)修改密碼：
#mysqladmin -u root -p'2rttwxb?3_oP' password 'Qianfeng123!' #mysql -u root -p'Qianfeng123!'
每臺(tái)機(jī) 器創(chuàng)建用于數(shù) 據(jù) 同步的用戶: mysql> grant all on . to 'syncuser'@'%' identified by 'QianFeng@123'; mysql> flush privileges;
配置Galera Replication:
galera1配置：主配置文件my.cnf追加如下內(nèi)容，黑體部分是和其他節(jié)點(diǎn)不同的地方
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://' //第一臺(tái)可以什么也不寫 wsrep_node_name='mysql-galera-1' wsrep_node_address='192.168.245.133'
wsrep_sst_auth=syncuser:'QianFeng@123' //創(chuàng)建的授權(quán)用戶和密碼 wsrep_sst_method=rsync galera2配置：主配置文件my.cnf追加如下內(nèi)容 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追加如下內(nèi)容 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追加如下內(nèi)容 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
重啟服務(wù)：每臺(tái)機(jī)器
[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))
測(cè)試：
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個(gè)節(jié)點(diǎn)，我少配置了一個(gè)
階段測(cè)試:
在任何一臺(tái)機(jī)器上寫數(shù)據(jù)，在其他機(jī)器上全部會(huì)同步
配置文件參數(shù)解釋
46/52
1.binlog_format=row
① STATEMENT模式（SBR）
每一條會(huì)修改數(shù)據(jù)的sql語(yǔ)句會(huì)記錄到binlog中。優(yōu)點(diǎn)是并不需要記錄每一條sql語(yǔ)句和每一行的數(shù)據(jù)變
化，減少了binlog日志量，節(jié)約IO，提高性能。缺點(diǎn)是在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會(huì)出現(xiàn)問題)
② ROW模式（RBR）
不記錄每條sql語(yǔ)句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了，修改成什么樣了。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點(diǎn)是會(huì)產(chǎn)生大量的日志，尤其是alter table的時(shí)候會(huì)讓日志暴漲。
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的復(fù)制使用STATEMENT模式保存binlog，對(duì)于STATEMENT模式無法復(fù)制的操作使用ROW模式保存binlog，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式。
2.innodb_file_per_table=1
開啟數(shù)據(jù)和索引存儲(chǔ)到共享表空間
3.innodb_autoinc_lock_mode=2
由于這個(gè)模式下已經(jīng)沒有了auto_inc鎖，所以這個(gè)模式下的性能是最好的；但是它也有一個(gè)問題，就是對(duì)于同一個(gè)語(yǔ)句來說它所得到的auto_incremant值可能不是連續(xù)的。
mycat部署
mycat 服務(wù)器端不需要安裝mysql 客戶端測(cè)試需要安裝mysql
一：部署mycat
1.安裝jdk環(huán)境
在/etc/profile下面寫java的環(huán)境變量
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH
source /etc/profile //使環(huán)境變量生效
安裝mycat
2.在～/.bash_profile下面寫mycat的環(huán)境變量
：/usr/local/mycat/bin
source ~/.bash_profile
然后啟動(dòng)mycat start
3.測(cè)試端口：
jps出現(xiàn)
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中添加數(shù)據(jù)庫(kù)和賬戶：
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
設(shè)置3個(gè)賬戶和3個(gè)schema:
切換到mycat的工作目錄： server.xml Mycat的配置文件，設(shè)置賬號(hào)、參數(shù)等
schema.xml Mycat對(duì)應(yīng)的物理數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)表的配置 rule.xml Mycat分片（分庫(kù)分表）規(guī)則1、user標(biāo)簽 user 用戶配置節(jié)點(diǎn)
–name 登錄的用戶名，也就是連接Mycat的用戶名
–password 登錄的密碼，也就是連接Mycat的密碼
–schemas 數(shù)據(jù)庫(kù)名，這里會(huì)和schema.xml中的配置關(guān)聯(lián)，多個(gè)用逗號(hào)分開，例如需要這個(gè)用戶需要管理兩個(gè)數(shù)據(jù)庫(kù)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 開啟讀寫分離機(jī)制，所有讀操作都發(fā)送到當(dāng)前備用的 writeHost 上。 wirteType=0 所有寫操作發(fā)送到第一個(gè)writeHost，第一個(gè)掛了切換到第二個(gè) switchType=3 基于MySQL Galera cluster的切換機(jī)制，心跳語(yǔ)句為show status like 'wsrep%'
隨便找臺(tái)機(jī)器做客戶端：
[root@client ~]# mysql -u shop -p'123456' -h 192.168.245.3 -P8066
結(jié)論：
1.所有節(jié)點(diǎn)都正常writeHost負(fù)責(zé)寫操作，備writeHost負(fù)責(zé)讀操作
2.當(dāng)?shù)谝粋€(gè)writeHost失效時(shí)，其中一個(gè)備的writeHost負(fù)責(zé)寫操作，其他備的writeHost負(fù)責(zé)讀操作
3.當(dāng)只有一個(gè)writeHost時(shí)，同時(shí)負(fù)擔(dān)讀寫
mycat優(yōu)化部署
server.xml優(yōu)化
一：server.xml 配置文件
1.privileges標(biāo)簽
對(duì)用戶的 schema以及表進(jìn)行精細(xì)化的DML(數(shù)據(jù)操縱語(yǔ)言)權(quán)限控制
<privileges check="false"> </privileges> --check 表示是否開啟DML權(quán)限檢查。默認(rèn)是關(guān)閉。 --dml 順序說明：insert,update,select,delete
<schema name="db1" dml="0110" > <table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table> </schema> db1的權(quán)限是update,select。tb01的權(quán)限是啥都不能干。
tb02的權(quán)限是insert,update,select,delete。其他表默認(rèn)是udpate,select。
2.system標(biāo)簽
這個(gè)標(biāo)簽內(nèi)嵌套的所有 property 標(biāo)簽都與系統(tǒng)配置有關(guān)。
<property name="charset">utf8</property>
字符集
<property name="processors">1</property>
處理線程數(shù)量，默認(rèn)是cpu數(shù)量。
<property name="processorBufferChunk">4096</property>
每次讀取留的數(shù)量，默認(rèn)4096。
<property name="processorBufferPool">409600</property>
創(chuàng)建共享buffer需要占用的總空間大小。processorBufferChunkprocessors100。 <property name="processorBufferPoolType">0</property>
默認(rèn)為0。0表示DirectByteBufferPool，1表示ByteBufferArena。 <property name="processorBufferLocalPercent">100</property> 二級(jí)共享buffer是processorBufferPool的百分比，這里設(shè)置的是百分比。 <property name="sequnceHandlerType">100</property>
全局ID生成方式。(0:為本地文件方式，1:為數(shù)據(jù)庫(kù)方式；2:為時(shí)間戳序列方式；3:為ZK生成ID；4:為ZK遞增ID生成。
<property name="useCompression">1</property>
是否開啟mysql壓縮協(xié)議。1為開啟，0為關(guān)閉，默認(rèn)關(guān)閉。
<property name="packetHeaderSize">4</property>
指定 Mysql 協(xié)議中的報(bào)文頭長(zhǎng)度。默認(rèn) 4。 <property name="maxPacketSize">16M</property>
指定 Mysql 協(xié)議可以攜帶的數(shù)據(jù)最大長(zhǎng)度。默認(rèn) 16M。 <property name="idleTimeout">1800000</property>
指定連接的空閑超時(shí)時(shí)間。某連接在發(fā)起空閑檢查下，發(fā)現(xiàn)距離上次使用超過了空閑時(shí)間，那么這個(gè)連接
49/52
會(huì)被回收，就是被直接的關(guān)閉掉。默認(rèn) 30 分鐘，單位毫秒。
<property name="txIsolation">3</property>
前端連接的初始化事務(wù)隔離級(jí)別，只在初始化的時(shí)候使用，后續(xù)會(huì)根據(jù)客戶端傳遞過來的屬性對(duì)后端數(shù)據(jù)
庫(kù)連接進(jìn)行同步。默認(rèn)為 REPEATED_READ，設(shè)置值為數(shù)字默認(rèn) 3。 READ_UNCOMMITTED = 1;
READ_COMMITTED = 2;
REPEATED_READ = 3; SERIALIZABLE = 4;
<property name="sqlExecuteTimeout">300</property> SQL 執(zhí)行超時(shí)的時(shí)間，Mycat 會(huì)檢查連接上最后一次執(zhí)行 SQL 的時(shí)間，若超過這個(gè)時(shí)間則會(huì)直接關(guān)閉這連
接。默認(rèn)時(shí)間為 300 秒，單位秒。
<property name="processorCheckPeriod">1000</property>
清理 NIOProcessor 上前后端空閑、超時(shí)和關(guān)閉連接的間隔時(shí)間。默認(rèn)是 1 秒，單位毫秒。
<property name="dataNodeIdleCheckPeriod">300000</property>
對(duì)后端連接進(jìn)行空閑、超時(shí)檢查的時(shí)間間隔，默認(rèn)是 300 秒，單位毫秒。
<property name="dataNodeHeartbeatPeriod">10000</property>
對(duì)后端所有讀、寫庫(kù)發(fā)起心跳的間隔時(shí)間，默認(rèn)是 10 秒，單位毫秒。
<property name="bindIp">0.0.0.0</property> mycat 服務(wù)監(jiān)聽的 IP 地址，默認(rèn)值為 0.0.0.0。 <property name="serverPort">8066</property>
定義 mycat 的使用端口，默認(rèn)值為 8066。 <property name="managerPort">9066</property>
定義 mycat 的管理端口，默認(rèn)值為 9066。 <property name="fakeMySQLVersion">5.6</property>
mycat 模擬的 mysql 版本號(hào)，默認(rèn)值為 5.6 版本，如非特需，不要修改這個(gè)值，目前支持設(shè)置 5.5,5.6,5.7 版本，其他版本可能會(huì)有問題。
<property name="useSqlStat">0</property>
是否開啟實(shí)時(shí)統(tǒng)計(jì)。1為開啟；0為關(guān)閉 。
<property name="useGlobleTableCheck">0</property>
是否開啟全局表一致性檢測(cè)。1為開啟；0為關(guān)閉 。
<property name="handleDistributedTransactions">0</property>
分布式事務(wù)開關(guān)。0為不過濾分布式事務(wù)；1為過濾分布式事務(wù)；2 為不過濾分布式事務(wù),但是記錄分布式事務(wù)日志。
<property name="maxStringLiteralLength">65535</property>
默認(rèn)是65535。 64K 用于sql解析時(shí)最大文本長(zhǎng)度以上舉例的屬性僅僅是一部分，可以配置的變量很多。
System標(biāo)簽下的屬性，一般是上線后，需要根據(jù)實(shí)際運(yùn)行的情況，分析后調(diào)優(yōu)的時(shí)候進(jìn)行修改。
<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優(yōu)化
一：schema.xml
–schema 數(shù)據(jù)庫(kù)設(shè)置，此數(shù)據(jù)庫(kù)為邏輯數(shù)據(jù)庫(kù)，name與server.xml中schema對(duì)應(yīng)
–dataNode 分片信息，也就是分庫(kù)相關(guān)配置 –dataHost 物理數(shù)據(jù)庫(kù)，真正存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)庫(kù)
1、schema 標(biāo)簽
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="10"> </schema> schema標(biāo)簽用來定義mycat實(shí)例中的邏輯庫(kù)，mycat可以有多個(gè)邏輯庫(kù)，每個(gè)邏輯庫(kù)都有自己的相關(guān)配置。可以使用schema標(biāo)簽來劃分這些不同的邏輯庫(kù),如果不配置schema標(biāo)簽，所有表的配置會(huì)屬于同一個(gè)默認(rèn)的
邏輯庫(kù)。邏輯庫(kù)的概念和MySql的database的概念一樣，我們?cè)诓樵儍蓚€(gè)不同邏輯庫(kù)中的表的時(shí)候，需要切換到該邏輯庫(kù)下進(jìn)行查詢。
–name 邏輯數(shù)據(jù)庫(kù)名，與server.xml中的schema對(duì)應(yīng) –checkSQLschema 數(shù)據(jù)庫(kù)前綴相關(guān)設(shè)置，當(dāng)該值為true時(shí)，例如我們執(zhí)行語(yǔ)句select from
TESTDB.company 。mycat會(huì)把語(yǔ)句修改為 select from company 去掉TESTDB。
–sqlMaxLimit 當(dāng)該值設(shè)置為某個(gè)數(shù)值時(shí)，每條執(zhí)行的sql語(yǔ)句，如果沒有加上limit語(yǔ)句，Mycat會(huì)自動(dòng)加上對(duì)應(yīng)的值。不寫的話，默認(rèn)返回所有的值。需要自己sql語(yǔ)句加limit。
2、dataNode標(biāo)簽
<dataNode name="dn1" dataHost="localhost1" database="db1" /> datanode標(biāo)簽定義了mycat中的數(shù)據(jù)節(jié)點(diǎn)，也就是數(shù)據(jù)分片。一個(gè)datanode標(biāo)簽就是一個(gè)獨(dú)立的數(shù)據(jù)分片。localhost1數(shù)據(jù)庫(kù)實(shí)例上的db1物理數(shù)據(jù)庫(kù)，這就組成一個(gè)數(shù)據(jù)分片，最后我們用dn1來標(biāo)示這個(gè)分片。
–name 定義數(shù)據(jù)節(jié)點(diǎn)的名字，這個(gè)名字需要唯一。我們?cè)趖able標(biāo)簽上用這個(gè)名字來建立表與分片對(duì)應(yīng)的關(guān)
系
–dataHost 用于定義該分片屬于哪個(gè)數(shù)據(jù)庫(kù)實(shí)例，屬性與datahost標(biāo)簽上定義的name對(duì)應(yīng) –database 用于定義該分片屬于數(shù)據(jù)庫(kù)實(shí)例上 的具體庫(kù)。
3、dataHost標(biāo)簽
這個(gè)標(biāo)簽直接定義了具體數(shù)據(jù)庫(kù)實(shí)例，讀寫分離配置和心跳語(yǔ)句。
<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 唯一標(biāo)示dataHost標(biāo)簽，供上層使用
–maxCon 指定每個(gè)讀寫實(shí)例連接池的最大連接。
–minCon 指定每個(gè)讀寫實(shí)例連接池的最小連接，初始化連接池的大小
–balance 負(fù)載均稱類型
balance=“0”：不開啟讀寫分離機(jī)制，所有讀操作都發(fā)送到當(dāng)前可用的writeHost上balance=“1”：全部的readHost與stand by writeHost參與select語(yǔ)句的負(fù)載均衡，簡(jiǎn)單的說，當(dāng)雙主雙從模式（M1-S1，M2-S2 并且M1 M2互為主備），正常情況下，M2,S1,S2都參與select語(yǔ)句的負(fù)載均衡。balance=“2”：所有讀操作都隨機(jī)的在writeHost、readHost上分發(fā)balance=“3”：所有讀請(qǐng)求隨機(jī)的分發(fā)到writeHst對(duì)應(yīng)的readHost執(zhí)行，writeHost不負(fù)擔(dān)讀寫壓力。 –writeType 負(fù)載均衡類型。 writeType=“0”, 所有寫操作發(fā)送到配置的第一個(gè) writeHost，第一個(gè)掛了切到還生存的第二個(gè)writeHost，重新
啟動(dòng)后已切換后的為準(zhǔn)，切換記錄在配置文件中:dnindex.properties . writeType=“1”，所有寫操作都隨機(jī)的發(fā)送到配置的 writeHost。1.5以后版本廢棄不推薦。
–switchType -1不自動(dòng)切換
1 默認(rèn)值 自動(dòng)切換
2 基于MySql主從同步的狀態(tài)決定是否切換心跳語(yǔ)句為 show slave status 3 基于mysql galary cluster 的切換機(jī)制（適合集群） 心跳語(yǔ)句為 show status like ‘wsrep%’
–dbType 指定后端鏈接的數(shù)據(jù)庫(kù)類型目前支持二進(jìn)制的mysql協(xié)議，還有其他使用jdbc鏈接的數(shù)據(jù)庫(kù)，例
51/52
如：mongodb，oracle，spark等
–dbDriver 指定連接后段數(shù)據(jù)庫(kù)使用的driver，目前可選的值有native和JDBC。使用native的話，因?yàn)檫@個(gè)值執(zhí)行的是二進(jìn)制的mysql協(xié)議，所以可以使用mysql和maridb，其他類型的則需要使用JDBC驅(qū)動(dòng)來支持。
如果使用JDBC的話需要符合JDBC4標(biāo)準(zhǔn)的驅(qū)動(dòng)jar 放到mycat\lib目錄下，并檢查驅(qū)動(dòng)jar包中包括如下目錄結(jié)構(gòu)文件 META-INF\services\java.sql.Driver。 在這個(gè)文件寫上具體的driver類名，例如com.mysql.jdbc.Driver writeHost readHost指定后端數(shù)據(jù)庫(kù)的相關(guān)配置給mycat，用于實(shí)例化后端連接池。
–tempReadHostAvailable
如果配置了這個(gè)屬性 writeHost 下面的 readHost 仍舊可用，默認(rèn) 0 可配置（0、1）。
1）heartbeat標(biāo)簽
這個(gè)標(biāo)簽內(nèi)指明用于和后端數(shù)據(jù)庫(kù)進(jìn)行心跳檢查的語(yǔ)句。
例如：MYSQL 可以使用 select user()，Oracle 可以使用 select 1 from dual 等。 2) writeHost /readHost 標(biāo)簽
這兩個(gè)標(biāo)簽都指定后端數(shù)據(jù)庫(kù)的相關(guān)配置，用于實(shí)例化后端連接池。唯一不同的是，writeHost 指定寫實(shí)例、readHost 指定讀實(shí)例。
在一個(gè) dataHost 內(nèi)可以定義多個(gè) writeHost 和 readHost。但是，如果 writeHost 指定的后端數(shù)據(jù)庫(kù)宕機(jī)，那么這個(gè) writeHost 綁定的所有 readHost 都將不可用。
另一方面，由于這個(gè) writeHost 宕機(jī)，系統(tǒng)會(huì)自動(dòng)的檢測(cè)到，并切換到備用的 writeHost 上去。這兩個(gè)標(biāo)簽的屬性相同，這里就一起介紹。
–host 用于標(biāo)識(shí)不同實(shí)例，一般 writeHost 我們使用M1，readHost 我們用S1。 –url 后端實(shí)例連接地址。Native：地址：端口 JDBC：jdbc的url –password 后端存儲(chǔ)實(shí)例需要的密碼
–user 后端存儲(chǔ)實(shí)例需要的用戶名字
–weight 權(quán)重 配置在 readhost 中作為讀節(jié)點(diǎn)的權(quán)重 –usingDecrypt 是否對(duì)密碼加密，默認(rèn)0。
52/52
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
