第六週基礎部分作業

szlhwei發表於2024-05-26

@所有人 第六週基礎部分作業：

  1. 總結關係型資料庫相關概念，關係，行，列，主鍵，惟一鍵，域。

關係型資料庫：以二維表的方式記錄資料。

關係relational：每張表；

行row：record，每條記錄；

列column：field，每個欄位、屬性；

主鍵Primary key：PK，唯一確定每條記錄的欄位，所以每個表只有一個主鍵且不能空，一般使用**id自增確保不會重複；

唯一鍵Unique key：UK，有意義的唯一確定每條記錄的欄位，可以用多個屬性、欄位組合，一張表可以有多個，且可以為NULL；

域domain：欄位、屬性的取值範圍；如性別為男或女，年齡為0到150；

  1. 總結關聯型別，1對1，1對多，多對多關係。可以自行設計表進行解釋。

1:1 ：A表的UK欄位field，對應B表的主鍵PK；

1:N ：A表的PK、UK欄位，對應B表的非唯一欄位；邏輯清晰；

N:M ：A表的非唯一欄位，對應B表的非唯一欄位；容易造成混亂，增加一張1:N的表；

第六週基礎部分作業

  1. 總結mysql設計正規化

目的：為了減少資料庫中的冗餘，增進資料的一致性。

第一正規化，1NF：每一列、每個屬性都是基本資料項，確保原子性、非重複值，屬性唯一。關聯式資料庫必須滿足1NF。

第二正規化，2NF：滿足1NF，且每個屬性完全依賴PK。

第三正規化，3NF：滿足2NF，且每個屬性間無關係。

  1. 總結Mysql多種安裝方式，及安全加固，並總結mysql配置檔案。

完成將server和client端的mysql配置預設字符集為utf8mb4;

yum安裝

配置好源即可，安裝mysql-server，mariadb-server服務端

https://dev.mysql.com/downloads/file/?id=527192

[root@rocky8-45 ~]$wget 'https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz'

##下載安裝

[root@rocky8-45 ~]$yum -y install libaio numactl-libs ncurses-compat-libs

[root@rocky8-45 ~]$groupadd mysql

[root@rocky8-45 ~]$useradd -r -g mysql -s /bin/false mysql

[root@rocky8-45 ~]$tar xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz -C /usr/local

[root@rocky8-45 local]$ln -s /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64/ mysql

[root@rocky8-45 local]$chown -R mysql.mysql mysql

[root@rocky8-45 local]$chown -R mysql.mysql mysql-8.4.0-linux-glibc2.28-x86_64/

##修改環境變數及配置

[root@rocky8-45 ~]$echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

[root@rocky8-45 ~]$. /etc/profile.d/mysql.sh

[root@rocky8-45 ~]$cat /etc/my.cnf

[mysqld]

datadir=/data/mysql

skip_name_resolve=1

socket=/data/mysql/mysql.sock

log-error=/data/mysql/mysql.log

pid-file=/data/mysql/mysql.pid

[client]

socket=/data/mysql/mysql.sock

[root@rocky8-45 ~]$mkdir /data/mysql -pv

mkdir: created directory '/data'

mkdir: created directory '/data/mysql'

[root@rocky8-45 ~]$chown -R mysql.mysql /data/mysql/

##初始化並生成root空密碼，可以登入

[root@rocky8-45 ~]$mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

[root@rocky8-45 ~]$cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@rocky8-45 ~]$chkconfig --add mysqld

[root@rocky8-45 ~]$systemctl start mysql

[root@rocky8-45 ~]$mysql

##修改密碼後再次驗證登入

[root@rocky8-45 ~]$mysqladmin -uroot password '123456'

[root@rocky8-45 ~]$mysql -uroot -p123456

##安全加固，設定root口令以及口令複雜度，禁止root遠端，刪除anonymous、刪除test，立刻生效

[root@rocky8-45 ~]$mysql_secure_installation

##修改預設字符集及排序，增加內容後重啟服務

##配置檔案mysqld為伺服器端，client為所有客戶端工具（有時候某些工具報錯，可能就是設定了client的內容），mysql僅為客戶端工具

[root@rocky8-45 ~]$cat /etc/my.cnf

[mysqld]

character-set-server=utf8mb4

[mysql]

default-character-set=utf8mb4

[client]

default-character-set=utf8mb4

[root@rocky8-45 ~]$systemctl restart mysqld.service

#

  1. 掌握如何獲取SQL命令的幫助，基於幫助完成新增testdb庫，字符集utf8, 排序集合utf8_bin.建立host表，欄位（id，host，ip，cname等）

##幫助

mysql> \h

mysql> help contents

mysql> help create database;

mysql> CREATE DATABASE testdb CHARACTER SET = utf8 COLLATE =utf8_bin ;

mysql> use testdb;

mysql> CREATE TABLE host (

-> id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

-> host char(50) DEFAULT NULL,

-> ip int(10) unsigned DEFAULT NULL,

-> cname varchar(50) DEFAULT NULL,

-> PRIMARY KEY (id)

-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

mysql> insert host(host,ip,cname)values('wang',10002,'wang');

  1. 根據表擴充套件出幾個語句，完成總結DDL, DML的用法，並配上示例。

DDL：create、alter（rename、add、modify、change、drop...）、drop

DDL屬於修改表結構、屬性等非資料資訊；

DML：insert、update、delete

DML屬於改變資料資訊；

DQL：distinct、group by、order by、limit 查詢語句

mysql> alter table host modify ip varchar(20);

mysql> insert host(host,ip,cname) values('mage',"10.0.0.3",'MAGE');

  1. 總結mysql架構原理

第六週基礎部分作業

  1. 總結myisam和Innodb儲存引擎的區別。

InnoDB引擎特點（目前預設資料庫引擎）

行級鎖

支援事務，適合處理大量短期事務 （保持資料完整性）

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

可快取資料和索引

支援聚簇索引

崩潰恢復性更好

支援MVCC高併發 （多個版本並存，有兩個欄位插入時間點、刪除時間點，實現多個版本並存）

myisam：不支援事務（可能導致資料不一致）、表級鎖定，不適用頻繁讀寫場景；

  1. 總結mysql索引作用，同時總結哪些查詢不會使用到索引。

索引index目的：是排序的快速查詢的特殊資料結構，索引透過儲存引擎實現。

某些索引型別會導致修改索引列（修改索引列或新增資料），需要改後面的索引（重新生成索引路徑），因此讀的多、寫的少的表，使用索引效果更好；

索引採用左字首索引，所以用where**=x%可以使用到索引快速查詢，但用where**=%x|%x%則無法使用索引。

  1. 總結事務ACID事務特性

事務從begin到commit（或rollback）作為一個整體修改，外界根據隔離級別檢視到事務內部資料（DML）的變動。

mysql預設可重複讀的隔離級別，則讀到事務開始前或提交後的資料。

  1. 總結事務日誌工作原理。

目的：保證一個整體的執行；

方法：將dml指令碼儲存，redo，備份操作（當系統故障時，能重新執行）；undo，回滾操作（當報錯時，能回滾，相當於整體不做變動）。

事務日誌效能最佳化innodb_flush_log_at_trx_commit=0|1|2：

0：提交事務後，寫入到log buffer，每秒寫入到記憶體，寫入到磁碟；

1：提交事務後，寫入到log buffer，寫入到記憶體，寫入到磁碟；預設值；

2：提交食物後，寫入到log buffer，寫入到記憶體，每秒寫入到磁碟；

1穩定（持續有磁碟IO，最慢但資料不丟失），2高併發更佳（會有1秒資料丟失的風險）

  1. 總結mysql日誌型別，並說明如何啟動日誌。

事務日誌

innodb_log_file_size 50331648 #每個日誌檔案大小

innodb_log_buffer_size 事務日誌緩衝區大小

innodb_log_files_in_group 2 #日誌組成員個數

innodb_log_group_home_dir ./ #事務檔案路徑

使用begin，或start transaction 開始，commit，或rollback 結束

錯誤日誌

記錄級別：

System event：0

Error event：1

Warning event：2

Note/information event：3

級別：

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings' ;

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity' ;

日誌路徑：

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';

通用日誌

可統計指令碼關鍵字，考慮建立搜尋多的內容建立索引；

general_log=ON|OFF

general_log_file=HOSTNAME.log log_output=TABLE|FILE|NONE

日誌路徑：

mysql> select @@general_log_file;

慢查詢日誌

記錄執行查詢時長超出指定時長的操作

slow_query_log=ON|OFF #開啟或關閉慢查詢，支援全域性和會話，只有全域性設定才會生成慢查詢檔案

long_query_time=N #慢查詢的閥值，單位秒 ,預設為10s slow_query_log_file=HOSTNAME-slow.log #慢查詢日誌檔案

二進位制日誌

（備份還原使用row）

記錄已提交的日誌（事務日誌記錄操作和已提交）

statement，記錄語句；row，記錄資料，日誌量大，能作為資料還原；mixed，混合型別；

##開啟

set sql_log_bin=1;

##修改伺服器選項，設定目錄以及檔案字首mysql-bin

[root@rocky8-42 ~]$cat /etc/my.cnf

[mysqld]

binlog_format=row

log_bin=/data/mysql/logbin/mysql-bin

##檢視日誌檔案

mysqlbinlog /data/mysql/logbin/mysql-bin.000005 -v

##可使用二進位制檔案生成sql指令碼，就能實現指令碼還原

mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

  1. 總結二進位制日誌的不同格式的使用場景。

binlog_format，二進位制格式

statement：記錄語句，如使用update>=或者now（）時間插入欄位，則沒有備份意義；

row：逐行，檢視日誌可知，逐條語句逐條改動，保證資料完全一樣，但日誌量巨大，備份就選擇row；

mixed：系統自行判斷；

  1. 總結mysql備份型別，並基於mysqldump, xtrabackup完成資料庫備份與恢復驗證。

備份型別：完全備份、增量備份、差異備份；冷備（停服務，可用於複製資料目錄），溫備（可讀不可寫，比較合適mysqldump+binlog），熱備（可讀可寫）；

二進位制檔案不應該與資料檔案放在同一磁碟；

範例：mysqldump全量+binlog增量還原

##原資料庫43，安裝修改二進位制目錄

[root@rocky8-43 ~]$yum -y install mysql-server

[root@rocky8-43 ~]$cat /etc/my.cnf

[mysqld]

log-bin=/data/mysql/mysql-bin

[root@rocky8-43 ~]$mkdir /data/mysql -p

[root@rocky8-43 ~]$chown -R mysql.mysql /data/mysql/

[root@rocky8-43 ~]$systemctl enable --now mysqld

##確認寫入資料，全量備份生成sql、以及新的二進位制日誌

[root@rocky8-43 ~]$mysql < hellodb_innodb.sql

[root@rocky8-43 ~]$mysql -e 'show databases;'

[root@rocky8-43 ~]$mysqldump -A -F --single-transaction --source-data=2 > full.sql

##修改資料，將修改的二進位制日誌生成sql

mysql> use hellodb;

mysql> update teachers set name='wang' where tid=1;

[root@rocky8-43 ~]$mysqlbinlog /data/mysql/mysql-bin.000003 > update_teachers.sql

##傳送備份指令碼

[root@rocky8-43 ~]$scp full.sql update_teachers.sql 10.0.0.44:

##目標資料庫44，執行指令碼後驗證

[root@rocky8-44 ~]$mysql < full.sql

[root@rocky8-44 ~]$mysql < update_teachers.sql

mysql> select * from teachers;

| 1 | wang | 45 | M |

15. 編寫crontab，每天按表備份所有mysql資料。將備份資料放在以天為時間的目錄下。基於xtrabackup，每週1，周5進行完全備份，周2到周4進行增量備份 xtrabackup這個先放一放。。。

相關文章