一、主從復(fù)制

1.目的和解決的問題：

目的：讀寫分離，一個數(shù)據(jù)庫只負(fù)責(zé)讀，一個只負(fù)責(zé)寫。

解決的問題：

數(shù)據(jù)分布:隨意停止或開始復(fù)制，并在不同地理位置分布數(shù)據(jù)備份；

負(fù)載均衡:降低單個服務(wù)器的壓力；

故障切換:幫助應(yīng)用程序避免單點失?。?/p>

升級測試:可以使用更高版本的MySQL作為從庫。

2.基本原理：

如圖

3.復(fù)制的三步驟：

（1）master將改變記錄到二進(jìn)制日志，這些記錄過程叫做二進(jìn)制日志事件binary log events；

（2）slave將master的binary log events拷貝到它的中繼日志；

（3）slave重做中繼日志中的事件,將改變應(yīng)用到自己的數(shù)據(jù)庫中。

MySQL復(fù)制是異步的且串行的。

4.復(fù)制的基本原則：

每個slave只有一個master；

每個slave只能有一個唯一的服務(wù)器ID；

每個master可以有多個salve。

5.一主一從常見配置：

MySQL版本一致且后臺服務(wù)可以運(yùn)行；

主從主機(jī)可以相互通信；

主從配置都在[mysqld]結(jié)點下,都是小寫。

主機(jī)（Windows系統(tǒng)）配置my.ini：

server_id=2 #[必須]主服務(wù)器唯一ID，不能與從機(jī)重復(fù)

log-bin=自己本地的路徑/mysqlbin（例如mysql-bin） #[必須]啟用二進(jìn)制日志

log-err = 自己本地的路徑/mysqlerr #[可選]啟用錯誤日志

1

2

3

從機(jī)（Linux Ubuntu系統(tǒng)）配置mysqld.cnf(/etc/mysql/mysql.conf.d/mysqld.cnf)：

bind-address=0.0.0.0 #讓任何IP地址連接

server_id=1 #[必須]主服務(wù)器唯一ID

log-bin=自己本地的路徑/mysqlbin（例如/var/log/mysql/mysql-bin.log） #[可選]啟用二進(jìn)制日志

1

2

3

修改過配置文件之后，要重啟MySQL服務(wù)：

service mysql restart

1

關(guān)閉Linux防火墻：

service iptables stop

1

關(guān)閉Windows防火墻：

關(guān)閉專用網(wǎng)絡(luò)防火墻。

在Windows主機(jī)上建立賬戶并授權(quán)slave：

create user 'slave'@'從機(jī)數(shù)據(jù)庫IP' identified with mysql_native_password by 'password';

#例如create user 'zhangsan'@'192.168.1.1' identified with mysql_native_password by '123456';

1

2

授權(quán)并查看狀態(tài)：

grant replication slave on *.* to 'slave'@'從機(jī)數(shù)據(jù)庫IP' identified by 'password';

#例如grant replication slave on *.* to 'zhangsan'@'192.168.1.1' identified by '123456';

show master status;

1

2

3

4

記錄下File和position的值。

6.配置Linux從機(jī)

change master to master_host = '192.168.0.161',

master_user = 'jerry',

master_password = '123456',

master_log_file = 'binlog.000004',

master_log_pos= 908;

7.測試是否配置成功

start slave;? #啟動從服務(wù)器復(fù)制功能

show slave status;

1

2

3

下面兩個參數(shù)都是yes,則說明主從配置成功：

slave_io_running:yes；

slave_sql_running:yes。

主從復(fù)制總結(jié)：

當(dāng)讀數(shù)據(jù)和寫數(shù)據(jù)都是對同一個數(shù)據(jù)庫進(jìn)行操作時，可能會遇到性能瓶頸，所以新增一個與原數(shù)據(jù)庫相同的數(shù)據(jù)庫作為從數(shù)據(jù)庫，即備份，實現(xiàn)讀寫分離（主數(shù)據(jù)庫寫，從數(shù)據(jù)庫讀），既能提高讀寫效率，又能提高安全性，但是會產(chǎn)生一定延遲。

二、MySQL操作規(guī)范

1.命名規(guī)范

表名建議使用有業(yè)務(wù)意義的英文詞匯，必要時可加數(shù)字和下劃線，并以英文字母開頭。

庫、表、字段全部采用小寫：

MySQL在Linux下默認(rèn)是區(qū)分大小寫的，而在Windows下不區(qū)分大小寫。因此，防止出現(xiàn)問題，建議都設(shè)置為小寫。

避免用 MySQL 的保留字。

命名（包括表名、列名）禁止超過 30 個字符。

臨時庫、表名必須以tmp為前綴，并以日期為后綴，如：tmp_shop_info_20200120。

備份庫、表必須以bak為前綴，并以日期為后綴，如：bak_shop_info_20200120。

索引命名：

非唯一索引必須按照"idx_字段名稱"或"idx_表名_字段名稱"進(jìn)行命名；

唯一索引必須按照"uniq_字段名稱"或"uniq_表名_字段名稱"進(jìn)行命名。

2.設(shè)計規(guī)范

主鍵：

表必須有主鍵；

不使用更新頻繁的列做主鍵；

盡量不選擇字符串列做主鍵；

不使用 UUID（不重復(fù)的字符串）、MD5 HASH 做主鍵；

默認(rèn)使用非空的唯一鍵。

如無特殊要求，建議都使用 InnoDB 引擎。

默認(rèn)使用 utf8mb4 字符集，數(shù)據(jù)排序規(guī)則使用 utf8mb4_general_ci：

utf8mb4 為萬國碼，無亂碼風(fēng)險；與 utf8 編碼相比，utf8mb4 能支持 Emoji 表情。

所有表、字段都需要增加 comment 來描述此表、字段所表示的含義：

如data_status TINYINT NOT NULL DEFAULT '1' COMMENT '1代表記錄有效，0代表記錄無效'。

盡可能不使用 TEXT、BLOB 類型：

原因：會浪費更多的磁盤和內(nèi)存空間，非必要的大量大字段查詢會淘汰掉熱數(shù)據(jù)，導(dǎo)致內(nèi)存命中率急劇降低，影響數(shù)據(jù)庫性能。如果實在有某個字段過長需要使用TEXT、BLOB類型，則建議獨立出來一張表，用主鍵來對應(yīng)，避免影響原表的查詢效率。字段用多大就取多大，避免資源浪費和性能下降。

單表列數(shù)目建議小于30。

3.SQL語句規(guī)范

避免隱式轉(zhuǎn)換：

varchar要加引號。

盡量不使用select *,只select需要的字段：

讀取不需要的列會增加CPU、IO、NET消耗，并且不能有效的利用覆蓋索引。使用SELECT *容易在增加或者刪除字段后導(dǎo)致程序報錯。

建議將子查詢轉(zhuǎn)換為關(guān)聯(lián)查詢。

建議應(yīng)用程序捕獲SQL異常，并有相應(yīng)處理：

可以避免數(shù)據(jù)庫攻擊。

4.行為規(guī)范

批量導(dǎo)入、導(dǎo)出數(shù)據(jù)必須提前通知DBA協(xié)助觀察；

不在業(yè)務(wù)高峰期批量更新、查詢數(shù)據(jù)庫；

刪除表或者庫要求盡量先重命名rename、備份，觀察幾天，確定對業(yè)務(wù)沒影響，再drop

三、數(shù)據(jù)庫基礎(chǔ)總結(jié)

1.數(shù)據(jù)類型

整數(shù)：tinyint、smallint、mediumint、int、bigint；

實數(shù)：float、double；

字符串：varchar、char、text、blob；

日期時間：timestamp、datetime。

2.列屬性

自增auto_increment；

默認(rèn)值default；

非空not null；

零填充zerofill；

無符號unsigned。

3.數(shù)據(jù)庫操作

連接數(shù)據(jù)庫：

mysql -uroot -p #更安全

或者

mysql -u root -proot

1

2

3

退出數(shù)據(jù)庫：

exit

--或者quit

1

2

查看所有數(shù)據(jù)庫：

show databases;

1

創(chuàng)建數(shù)據(jù)庫：

#創(chuàng)建數(shù)據(jù)庫

create database 數(shù)據(jù)庫名字 charset = utf8;

#查看創(chuàng)建數(shù)據(jù)庫的命令

show create database 數(shù)據(jù)庫名字;

#使用數(shù)據(jù)庫

use mydatabase;

4.數(shù)據(jù)庫表操作

查看當(dāng)前數(shù)據(jù)庫中所有表：

show tables;

1

創(chuàng)建表：

create table 數(shù)據(jù)表名字(字段 類型 約束[,字段 類型 約束]);

1

查看表：

desc demo1;

1

查看表的創(chuàng)建語句：

show create table salary;

1

5.DML數(shù)據(jù)庫管理語言

新增：

#全列插入：

insert [into] 表名 values(...);

#部分插入：

insert into 表名(列1 ,...) values(值1 ,...);

修改：

update 表名 SET 字段1=新值,字段2=新值[where 條件];

update 表名 SET 字段1=新值,字段2=新值;

1

2

刪除：

delete from 表名 [where條件];

delete from 表名; #清空表中的數(shù)據(jù)

1

2

查詢：

where子句：=、>、<

邏輯運(yùn)算符：and、or、not

模糊查詢：%、_

范圍查詢：in、between…and…

空判斷：is null、is not null

聚合函數(shù)：count、max、min、sum、avg

分組：group by

排序：asc、desc

分頁：limit