資料庫的相關操作

来源:https://www.cnblogs.com/xuecaichang/archive/2019/01/23/10310962.html
-Advertisement-
Play Games

資料庫類型 一、版本介紹和選擇 PerconaDB 主流版本 企業版本 在Linux中啟動資料庫 二、MYSQL的體繫結構 MYSQL C/S結構 實例： 1、mysqld 的三層結構 2、MySQL的邏輯結構 三、MySQL安裝 1、 https://www.mysql.com/ MySQL官網 ...


資料庫類型

mysql

一、版本介紹和選擇

oracle MySQL    8.**0**
MariaDB

PerconaDB

主流版本

mysql   5.6     5.6.36  5.38  5.6.40
mysql   5.7     5.7.18  5.7.20  5.7.22

企業版本

6-12月之間的GA

在Linux中啟動資料庫

/etc/init.d/mysqld start

二、MYSQL的體繫結構

MYSQL C/S結構

2種連接方法：TCP/IP(遠程，本地);SOCKET(本地)
mysql -uroot -poldboy123 -h 10.0.0.128 -p3306
mysql -uroot -poldboy123 -s /tmp/mysql.sock

實例：

mysqld(最重要)--->master thread--->N Thread  --->記憶體結構（員工）

1、mysqld 的三層結構

1、連接層
    -提供鏈接協議（TCP/IP，Socket）
    -提供用戶驗證
    -提供專用連接線程
2、SQL層
    - 接受上層命令
    - 提供語法檢測
    - 語義（SQL類型)，許可權
    - 專用解析器解析SQL，解析成執行計劃
    - 優化器：幫我們選擇一個代價最低的執行計劃（cpu,IO，MEM）
    -執行器：按照優化器的選擇，執行SQL語句，得出獲取數據的方法
    -查詢緩存：預設關閉， 一般會使用redis產品替代
    -記錄產品日誌：查詢日誌，二進位日誌
3、存儲引擎層
    - 按照SQL層結論，找到想應數據，結構化成表的形式

2、MySQL的邏輯結構

庫（schema）：存儲表的地方
表（table）：二維表
    元數據：
        -表名
        -表的屬性（表的大小，許可權，存儲引擎，字元集等）
        -列：列名，列屬性（數據 類型，約束，其他定義）  
======================================
        -記錄：數據行
======================================

三、MySQL安裝

1、https://www.mysql.com/ MySQL官網

2、點擊DOWNLOADS

3、點擊Archives

4、點擊MYSQL Community Server

5、

四、sql語句(SQL92)

SQL種類

DDL數據定義語言；
DCL數據控制語言；
DML數據操作語言； 
DQL數據查詢語言

SQL語句的操作對象

庫
表

不同分類語句的作用

DDL:

    -庫：
    CREATE DATABASE(創建)
    DROP DATABASE（刪除）
    ALTER DATABASE（修改）
    SQL語句建庫規範第一條：
    1、關鍵字大寫（非必須），字面量（用戶定義的，必須）
    2、庫名，只能小寫，不能有數字開頭，不能是預留的關鍵字
    3、庫名必須和業務名字有關，例如his_user;
    4、必須加字元集
    -表
    CREATE TABLE（創建）
    DROP TABLE（刪除）
    ALTER TABLE (修改)
==================================  
CREATE TABLE t1 (
id  INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '用戶ID',
sname  VARCHAR(20) NOT NULL COMMENT '用戶姓名',
gender ENUM('f','m','u') NOT NULL DEFAULT 'u' COMMENT '用戶性別',
telnum CHAR(11) NOT NULL UNIQUE COMMENT '手機號',
tmdate DATETIME NOT NULL DEFAULT NOW() COMMENT '錄入時間'
)ENGINE INNODB CHARSET utf8mb4;

=================================
    SQL語句建庫規範第二條：
    1、關鍵字大寫（非必須），字面量（用戶定義的，必須）
    2、表名，只能小寫，不能有數字開頭，不能是預留的關鍵字
    3、庫名必須和業務名字有關，例如his_user;
    4、必須加存儲引擎和字元集
    5、使用的數據類型
    6、必須要有主鍵
    7、儘量加非空選項
    8、欄位唯一性
    9、必須加註釋
    10、避免使用外鍵，
    11、建立合理的索引

DCL:

    grant
    revoke
    lock

DML:

insert
update
delete
-SQL語句按批量插入數據
-update必須加where條件
-delete儘量替換為update
-如果有清空全表需求，不要用delete，推薦使用truncate

DQL：

selete
show
SQL語句規範第四條：
1. select語句避免使用 select * from t1;---->  select id,name from t1;
2. select語句儘量加等值的where條件，例如：select * from t1 where id=20;
3、select語句 對於範圍查詢，例如：select * from t1 where id>200; 儘量添加limit或者  id>200 and id<300 union all id>300 and id<400 
4、select的where條件，不要使用<>like '%name' not in not exist
5、不要出現3表以上的表連接，避免子查詢
6、where條件中不要出現函數操作

SQL語句規範第五條

1、少於10位的數字int,大於10位數的char，例如手機號
2、char和varchar選擇時，字元長度一定不變的可以使用char，可變的儘量使用varchar,在可變長度的存儲時，將來使用不同的數據類型，對於索引樹的高度有影響的
3、選擇合適的數據類型
4、合適的長度

===============

五、MySQL 5.7 初始化配置

5.1 初始化數據:

/usr/local/mysql/bin/mysqld --initialize-insecure  --user=mysql --datadir=/opt/mysql/data --basedir=/opt/mysql

5.2 配置文件

vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/mydata
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log
user=mysql
port=6606
[mysql]
socket=/tmp/mysql.sock

作用:

1.影響服務端的啟動
標簽: [mysqld][mysqld_safe]  [server] ...
[mysqld]
basedir=/opt/mysql              
datadir=/opt/mysql/data
user=mysql
socket=/tmp/mysql.sock
port=3306 
server_id=6

2.影響客戶端連接
標簽: [client][mysql]  [mysqldump] ....
[mysql] 
socket=/tmp/mysql.sock

5.3 創建相關目錄

mkdir -p /data/330{7..9}/data

5.4創建配置文件

cat>> /data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql              
datadir=/data/3307/data
user=mysql
socket=/data/3307/mysql.sock
port=3307 
server_id=3307
EOF

cp /data/3307/my.cnf /data/3308 
cp /data/3307/my.cnf /data/3309 



sed -i 's#3307#3308#g' /data/3308/my.cnf 
sed -i 's#3307#3309#g' /data/3309/my.cnf

5.5 初始化數據

mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql

5.6 啟動多實例

chown -R mysql.mysql /data/*


 mysqld_safe --defaults-file=/data/3307/my.cnf &
 mysqld_safe --defaults-file=/data/3308/my.cnf &
 mysqld_safe --defaults-file=/data/3309/my.cnf &

驗證：

[root@standby data]# netstat -lnp|grep 330

5.7、 systemd管理多實例

cat >> /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=![img](file:///C:\Users\26685\AppData\Local\Temp\%W@GJ$ACOF(TYDYECOKVDYB.png)http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3308.service 
cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3309.service 
sed -i 's#3307#3308#g'   /etc/systemd/system/mysqld3308.service
sed -i 's#3307#3309#g'   /etc/systemd/system/mysqld3309.service

[root@standby ~]# systemctl start mysqld3307
[root@standby ~]# systemctl start mysqld3308
[root@standby ~]# systemctl start mysqld3309
[root@standby ~]# netstat -lnp|grep 330
[root@standby ~]# systemctl stop mysqld3309
[root@standby ~]# systemctl stop mysqld3308
[root@standby ~]# systemctl stop mysqld3307

[root@standby ~]# systemctl enable  mysqld3307
[root@standby ~]# systemctl enable  mysqld3308
[root@standby ~]# systemctl enable  mysqld3309

六、忘記密碼處理：

mysqladmin -uroot -p password 123

select user,authentication_string,host from mysql.user;

1.停資料庫
/etc/init.d/mysqld stop
2.啟動資料庫為無密碼驗證模式
mysqld_safe --skip-grant-tables --skip-networking  &
update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';
/etc/init.d/mysqld restart

[root@standby ~]# mysql -uroot -p123
[root@standby ~]# mysql -uroot -p456

七、數據類型和字元集

int 最多存10位數字
-2^31` 2^31-1
2^32 10位數
浮點：
字元串類型
char    定長，存儲數據效率較高，對於變化較多的欄位，空間浪費較多
varchar 變長，存儲時判斷長度，存儲會有額外開銷按需分配存儲空間，
enum(枚舉):   
時間類型：datetime，timestamp，date，time

八、索引及執行計劃

8.1 索引

  • 作用：優化查詢，select查詢有三種情況：緩存查詢（不在MySQL中進行數據查詢），全表掃描，索引掃描

8.2索引種類

Btree(btree b+tree b*tree)
Rtreee
HASH
FullText

Btree分類

聚集索引：基於主鍵，自動生成的，一般是建表時創建主鍵，自動 選擇唯一鍵作為聚集索引。
輔助索引：人為創建的 （普通，覆蓋） 
唯一索引：人為創建（普通索引，聚集索引）

聚集索引和輔助索引的區別：

1、葉子結點，按照主鍵列的順序，存儲的整行數據 ，就是真正的數據頁
2、輔助索引：葉子結點，列值排序之後，存儲到葉子結點+主鍵對應的主鍵的值，便於會表查詢

8.4 索引管理命令

8.4.1索引鍵（key),表中的某個列

創建普通輔助索引(MUL)
alter table blog_userinfo add key idx_email(email);
create index idx_phone on blog_userinfo(phone);
查看索引
desc blog_userinfo;
show index from blog_userinfo;
刪除索引
alter table blog_userinfo drop index idx_email;
drop index idx_phone on   blog_userinfo;

首碼索引

select count(*),substring(password,1,20) as sbp  from blog_userinfo group by sbp;
alter table blog_userinfo add index idx(password(10));
唯一鍵索引 （UNI ，不能有重覆值）
alter table 表名  add unique key uni_email(email);
覆蓋索引（聯合索引）
    -作用：不需要會表查詢，不需要聚集索引，所有查詢的數據都從輔助索引中獲取

8.5重要的欄位：

8.5.1 type:查詢類型

作用：
    -可以 判斷出，全表掃描還是索引掃描 （all就是全索引掃描，其他的就是索引掃描）
    -對於索引掃描 來講，又可以細化分，可以判斷出事哪一種類 的索引掃描 
type的具體類型介紹：
    All ：全表掃描
    Index：全索引掃描
        -例子：desc select countrycode from city;
    range:索引範圍掃描
        < ,>,<=,>=,in,or ,between ,and,like 'CH%'
in或者or改寫成union 
select *from city where countrycode='CHN'
union all 
select *from city where countrycode='USA';
ref:輔助索引的等值查詢
select *from city where countrycode='CHN'；
eq_ref:多錶鏈接查詢（join on）
const,system主鍵或唯一鍵等值查詢

九、 後端伺服器

ping埠號（22，80，443，3306，6397，8080，8000）

ssh:
    cpu:
    mem:
    IO：

查看CPU

cat /proc/cpuinfo

查看所有進程

ps aux

yum install -y sysstat

伺服器的啟停：

​ 真實硬體：遠程管理卡，fence設備等

​ 虛擬化產品：kvm,openstack, docker, k8s,vmware esxi

自動裝系統：

​ 真實硬體：kickstart + cobbler

​ 虛擬化產品：克隆，啟動新容器

自動化配置：

​ ansible ,saltstack (批量化配置)

生命周期管理：

​ 啟停服務，監控：zabbix(硬體)系統

Devops:代碼上線發佈

現在用git和jenkins發佈

堡壘機(jumpserver)

VPN

資料庫審核:
​ 危險性操作
​ SQL性能審計 (全表掃描,抓取執行事件過長的語)
​ 性能參數審核,根據性能指標,提出性能優化建議
​ 資料庫對象監控,提出整改建議
​ explain

存儲引擎

作用：和磁碟的數據打交道

簡介：MySQL基於存儲引擎管理，表空間數據文件

存儲引擎種類

Innodb存儲引擎
ibd:存儲表的數據行和索引
frm:表基本結構信息
Myisam存儲引擎
frm：表基本結構信息，myi：存索引,myd：存數據行

事務

保證交易的完整性
    ACID特性
    Atomic（原子性）
    所有語句作為一個單元全部成功執行或全部取消。不允許出現中間過程.
    Consistent（一致性）
    如果資料庫在事務開始時處於一致狀態，則在執行該事務期間將保留一致狀態。 
    Isolated（隔離性）
    事務之間不相互影響。
    兩個方面:  修改同一行 , 一致性讀
    Durable（持久性）
    事務成功完成後，所做的所有更改都會準確地記錄在資料庫中。所做的更改不會丟失。

十、日誌

錯誤日誌修改

log_error=/var/log/mysql.log   分析[error]

二進位日誌(binog，邏輯型日誌)

作用：記錄了所有變更類的語句，可以做數據恢復和操作的審計
DDL，DCL：以語句方式（startement）記錄
DML：預設是以行模式記錄（row模式 ，數據行的變化）

配置方法

查看：show variables like 'log_bin';
log_bin=/opt/mysql/data/mysql-bin
binlog_format=row
server_id=6
sync_binlog=1

查看日誌信息

mysql> show binary logs;
mysql> show master status; 查看正在使用的日誌

日誌內容的查看

按事件查看日誌內容

mysql> show binlog events in 'mysql-bin.000012'; #000012事件

直接查看日誌內容

mysqlbinlog --base64-output=decode-rows -vvv /opt/mysql/data/mysql-bin.000012 |more

截取二進位日誌

[root@standby data]# mysqlbinlog --start-position=219 --stop-position=186613 /opt/mysql/data/mysql-bin.000012 >/tmp/binlog.sql

慢日誌（slow-log）

​ 記錄慢語句的日誌文件

vim /etc/my.cnf
配置：
slow_query_log=1
slow_query_log_file=/opt/mysql/data/standby-slow.log
long_query_time=1   
log_queries_not_using_indexes=1

十一、備份恢復

備份的種類 

邏輯備份：SQL語句備份
物理備份：數據頁備份

邏輯備份工具的介紹

select xxxx from t1  into outfile '/tmp/redis.txt';

mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli

mysqldump(邏輯備份工具)

建個目錄：mkdir /backup 用於放備份文件

-A 全庫備份
​ mysqldump -uroot -p123 -A >/backup/full.sql

-B 備份一個或多個指定庫
​ mysqldump -uroot -p123 -B world bbs >/backup/wb.sql

備份單庫中的表
​ mysqldump -uroot -p123 world city country >/backup/ccc.sql

主從複製：

​ 基於二進位日誌完成的


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • centOS防火牆
    預設防火牆firewall #停止firewall systemcl stop firewall.service #禁止firewall開機啟動 systemctl disable firewall.service 查看firewall狀態 firewall-cmd --state 防火牆iptab ...
  • 10分鐘搭建伺服器集群——Windows7系統中nginx與IIS伺服器搭建集群實現負載均衡
    分散式，集群，雲電腦、大數據、負載均衡、高併發······當耳邊響起這些詞時，做為一個菜鳥程式猿無疑心中會激動一番（或許這是判斷是否是一個標準阿猿的標準吧）！ 首先自己從巨集觀把控一下，通過上網科普自己遇到問題，然後再站在這些巨人的肩膀上解決問題。 問題：在大型WEB應用程系統中，由於請求的數量過大 ...
  • ifconfig和ping
    命令： ifconfig 對應英文： configure a network interface 作用： 查看 / 配置電腦當前的網卡配置信息 安裝： sudo apt install net-tools 使用： # 查看網卡配置信息 ifconfig # 查看網卡對應的IP地址 ifconfig ...
  • 關機重啟
    命令： shutdown 作用： 可以安全關閉或重啟啟動系統 選項： -r：重新啟動 註意： 不指定選項和參數，預設表示1分鐘之後，關閉電腦 遠程維護伺服器時，最好不要關閉系統，而是應該 加 -r 重啟系統 示例： # 重啟系統，其中now表示現在 shutdown -r now # 立刻關機 sh ...
  • 管道 |
    命令： | 作用： Linux允許將一個命令的輸出通過管道作為另一個命令的輸入，管道左側為寫入，右側為讀取 使用： 命令 | 命令 例子： 分頁顯示當前頁的目錄及文件詳情 ls -lha | more 查詢當前目錄下有python文字的目錄及文件 ls -lha | grep python ...
  • echo和重定向
    命令： echo 作用： echo有重覆的意思，會在終端中顯示參數指定的文字，通常會和重定向聯合使用 使用： echo 文字內容 例子： 在終端中顯示hello echo hello 命令： >　和　>> 作用： Linux允許將命令執行結果重定向到一個文件，將本應顯示在終端上的內容 輸出 / 追加 ...
  • kubernetes系列05—kubectl應用快速入門
    本文收錄在容器技術學習系列文章總目錄 1、使用kubectl 1.1 介紹 kubectl用於運行Kubernetes集群命令的管理工具。 1.2 語法 command：指定要在一個或多個資源執行的操作，例如操作create，get，describe，delete。TYPE：指定資源類型Resour ...
  • Oracle 創建函數
    Oracle創建函數的方法如下： 舉個例子： 調用一下 並輸出結果： ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類