6\MySQL主從同步、MySQL讀寫分離、MySQL性能調(diào)優(yōu)

day06
一、MySQL主從同步
二、數(shù)據(jù)讀寫分離
三、MySQL優(yōu)化
++++++++++++++++++++++++++++++++
一、mysql主從同步
1.1 主從同步介紹？從庫服務(wù)器自動同步主庫上數(shù)據(jù)（被客戶端訪問的數(shù)據(jù)庫服務(wù)器做主庫服務(wù)器）
1.2 結(jié)構(gòu)

專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計、成都網(wǎng)站制作服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)牡丹免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了上千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

              54                 55

systemctl start mysqld systemctl start mysqld
主master數(shù)據(jù)庫服務(wù)器 從slave數(shù)據(jù)庫服務(wù)器

1.3 配置主從同步結(jié)構(gòu)？
1.3.1主庫角色主機(jī)的配置
1 用戶授權(quán)
mysql> grant replication slave on . to yaya@"192.168.4.55" identified by "123456";

2 啟用binlog日志
10 vim /etc/my.cnf
server_id=54
log-bin=master54
binlog-format="mixed"
：wq
3 重啟數(shù)據(jù)庫服務(wù)器
11 systemctl stop mysqld
12 systemctl start mysqld
13 ls /var/lib/mysql/master54.*

4 查看正在使用binlog日志文件。

mysql -uroot -p123456

mysql> show master status;

1.3.2從庫角色主機(jī)的配置？
1 顯示自己是否是從數(shù)據(jù)庫服務(wù)器
mysql> show slave status;

2 測試主庫授權(quán)的用戶是否有效
#mysql -h292.168.4.54 -uyaya -p123456
mysql> show grants;
mysql> quit

3 修改配置文件指定server_id
#vim /etc/my.cnf
[mysqld]
server_id=55
:wq
#systemctl restart mysqld

4 在本機(jī)數(shù)據(jù)管理員登錄指定主庫信息。
mysql> change master to
-> master_host="192.168.4.54",
-> master_user="yaya",
-> master_password="123456",
-> master_log_file="master54.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

驗證主從同步配置：結(jié)果
客戶端連接主庫服務(wù)器 產(chǎn)生的新數(shù)據(jù)，在從角色的主機(jī)上都能夠查看到。

+++++++++++++++++++++++++++++++
主從工作原理？
IO線程： 把master 庫的 binlog日志內(nèi)容 記錄到本機(jī)的relay-binlog日志里。

SQL線程：執(zhí)行本機(jī)relay-binlog日志里的sql命令 把數(shù)據(jù)寫進(jìn)本機(jī)的庫。

從角色主機(jī)的數(shù)據(jù)庫目錄下會多出如下文件：
master.info 存儲主數(shù)據(jù)庫的信息
slave55-relay-bin.XXXXXX
中繼日志文件 記錄本機(jī)產(chǎn)生新數(shù)據(jù)的sql命令
slave55-relay-bin.index 中繼日志文件索引文件
relay-log.info 記錄當(dāng)前數(shù)據(jù)庫服務(wù)器使用的中繼日志文件

主從同步配置排錯？
IO線程報錯： 從庫指定主庫的信息錯誤；
安全限制（firewall selinux）

查看報錯信息：
Last_IO_Error: 報錯信息

mysql> stop slave;
mysql> change mstart to 選項=值；
mysql> start slave;

SQL線程報錯： 執(zhí)行中繼日志里的sql命令時，命令操作的庫或表在本機(jī)不存在。

查看報錯信息：
Last_SQL_Error: 報錯信息
mysql> stop slave;
在從本機(jī)有命令操作的庫或表
mysql> start slave;

++++++++++++++++++++++++++++
讓從數(shù)據(jù)庫臨時不同步主庫的數(shù)據(jù)？
mysql> stop slave;

把從庫 還原成獨立的數(shù)據(jù)庫服務(wù)器？
#rm -rf master.info slave55-relay-bin.* relay-log.info
#systemctl restart mysqld

mysql> show slave status;

主從同步結(jié)構(gòu)模式？
一主一從
一主多從
主從從
互為主從（主主結(jié)構(gòu)）

reset master #清空主的日志
reset slave #只清空日志
reset slave all #需要重新配置change master

++++++++++++++++++++++++++++++++++++
主從同步常用配置參數(shù)？（/etc/my.cnf）
主庫角色主機(jī)的配置參數(shù)
[mysqld]
binlog_do_db=數(shù)據(jù)庫名，數(shù)據(jù)庫名 #只允許同步的庫binlog_ignore_db=數(shù)據(jù)庫名，數(shù)據(jù)庫名 #只不允許同步的庫

mysql> show master stauts;

從庫角色主機(jī)的配置參數(shù)
[mysqld]
log_slave_updates 級聯(lián)復(fù)制
replicate_do_db=數(shù)據(jù)庫名，數(shù)據(jù)庫名 #只同步的庫
replicate_ignore_db=數(shù)據(jù)庫名，數(shù)據(jù)庫名 #只不同步的庫relay_log=文件名 #設(shè)置中繼日志文件名稱
+++++++++++++++++++++++++++++
二、數(shù)據(jù)讀寫分離
2.1 讀寫分離介紹？把客戶的查詢記錄的操作和寫數(shù)據(jù)的操作在不同的數(shù)據(jù)庫服務(wù)器上執(zhí)行。

2.2 為什么要做讀寫分離？ 減輕單臺數(shù)據(jù)庫服務(wù)器的并發(fā)訪問壓力和提供服務(wù)器硬件資源的利用率

2.3 配置數(shù)據(jù)讀寫分離 （Maxscale + 主從同步結(jié)構(gòu)）

2.3.1 拓?fù)浣Y(jié)構(gòu)
client
|
53 maxscale 4010 管理端口 4006 讀寫分離端口

insert select
寫 查
54 55
主 從

2.3.2 配置MySQL一主一從同步結(jié)構(gòu)
2.3.3 配置maxscale (53)
a. 裝包
b. 修改配置文件
vim /etc/maxscale.cnf
9 [maxscale]
10 threads=1

18 [server1]
19 type=server
20 address=192.168.4.54
21 port=3306
22 protocol=MySQLBackend
23

25 [server2]
26 type=server
27 address=192.168.4.55
28 port=3306
29 protocol=MySQLBackend

38 [MySQL Monitor]
39 type=monitor
40 module=mysqlmon
41 servers=server1, server2
42 user=scalemon # 監(jiān)控數(shù)據(jù)庫服務(wù)服務(wù)的運行狀態(tài)和主從狀態(tài)
43 passwd=123456
44 monitor_interval=10000

66 [Read-Write Service]
67 type=service
68 router=readwritesplit
69 servers=server1, server2
70 user=maxscale #檢查接收客戶端連接請求時，連接的用戶名和密碼在數(shù)據(jù)庫服務(wù)器上是否存在
71 passwd=123456
72 max_slave_connections=100%

78 [MaxAdmin Service]
79 type=service
80 router=cli

94 [Read-Write Listener]
95 type=listener
96 service=Read-Write Service
97 protocol=MySQLClient
98 port=4006

100 [MaxAdmin Listener]
101 type=listener
102 service=MaxAdmin Service
103 protocol=maxscaled
104 socket=default
105 port=4010

根據(jù)配置文件的設(shè)置在數(shù)據(jù)庫服務(wù)器上添加對應(yīng)的授權(quán)用戶。
mysql> grant replication slave, replication client on . to scalemon@'%' identified by

"123456";

mysql> grant select on mysql.* to maxscale@'%' identified by "123456";

c. 啟動服務(wù)

systemctl stop mysqld

[root@003 ~]# maxscale -f /etc/maxscale.cnf
[root@003 ~]# netstat -utnalp | grep :4010
tcp6 0 0 :::4010 ::: LISTEN 9559/maxscale
[root@003 ~]#
[root@003 ~]# netstat -utnalp | grep :4006
tcp6 0 0 :::4006 ::: LISTEN 9559/maxscale
[root@003 ~]# netstat -utnalp | grep maxscle
[root@003 ~]# netstat -utnalp | grep maxscale
tcp 0 0 192.168.4.53:52855 192.168.4.55:3306 ESTABLISHED 9559/maxscale
tcp 0 0 192.168.4.53:52228 192.168.4.54:3306 ESTABLISHED 9559/maxscale
tcp6 0 0 :::4010 ::: LISTEN 9559/maxscale
tcp6 0 0 :::4006 ::: LISTEN 9559/maxscale
[root@003 ~]#
53訪問本機(jī)的管理服務(wù) 查看監(jiān)控主機(jī)的狀態(tài)
[root@003 ~]# maxadmin -P4010 -uadmin -pmariadb
MaxScale>list servers

停止maxscale 服務(wù)
#ps -C maxscale
#kill -9 pid號

d. 在客戶端測試配置
#ping -c 2 192.168.4.53

d.1 在數(shù)據(jù)庫服務(wù)器添加訪問數(shù)據(jù)的用戶
mysql> grant all on . to student@'%' identified by "123456";

d.2 訪問代理主機(jī)
#mysql -P4006 -h292.168.4.53 -ustudent -p123456

+++++++++++++++++++++++++++++++++++++
三、MySQL優(yōu)化(優(yōu)化思路、 優(yōu)化什么 、怎么優(yōu)化)
訪問數(shù)據(jù)時，出結(jié)果特別慢。分析可能是由哪些原因?qū)е碌摹?/p>

1 硬件配置低：查看應(yīng)用設(shè)備的使用率CPU 內(nèi)存 存儲(硬盤)
top 0.0 wa
sar
uptime I/O

                    free  -m

2 網(wǎng)絡(luò)帶寬： 使用網(wǎng)絡(luò)測速軟件 網(wǎng)速

3 提供數(shù)據(jù)庫服務(wù)軟件版本低：
查看數(shù)據(jù)庫服務(wù)運行時的運行參數(shù)配置（常用配置參數(shù)）

超時時間
connect_timeout
建立連接時，三次握手的超時時間
wait_timeout 建立連接后，等待斷開連接的超時時間

mysql> show variables like "connect_timeout";
mysql> show variables like "wait_timeout";

允許重復(fù)使用的線程的數(shù)量
mysql> show variables like "thread_cache_size";

為所有線程緩存打開表的數(shù)量。
table_open_cache=2000
mysql> show variables like "table_open_cache";
pc1 T1

pc2 t3 mysqld

pc3 T1

key_buffer-size
sort_buffer_size
read_buffer_sizeread_rnd_buffer_sizename Index
select name from user where name="zhangsan";
select name,uid from user order by uid desc; <----mysqld
select * from user; <------ mysqld
select shell from user group by shell;<------ mysqld

查詢緩存相關(guān)參數(shù)設(shè)置？
MySQL> show variables like "query_cache%";

查詢緩存寫鎖有效 存儲引擎（MYISAM）
query_cache_wlock_invalidate | OFF

       t1

pc1 select name from user where name="lucy";
name="lucy" ------> 查詢緩存

pc2 select name from user where name="lucy";--> 查詢緩存
name="lucy"

pc3 update user set name="lili" where name="lucy";

query_cache_type 0|1|2
0 關(guān)閉 不存儲
1 開啟 無條件存儲
2 開啟 指定存儲才存儲

query_cache_limit 1048576 超過1M此數(shù)不存
query_cache_min_res_unit 4096 最小存儲單元4k

查詢查詢緩存統(tǒng)計信息？
MySQL> show global status like "qcache%";
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0

并發(fā)連接數(shù)：
mysql> show variables like "max_connections";

曾經(jīng)有過的最大連接數(shù)
mysql> show global status like "Max_used_connections";

公式：
曾經(jīng)有過的最大連接數(shù)/并發(fā)連接數(shù)=0.85 * 100% = 85%
3000/ X = 0.85

查看參數(shù)的值
MySQL> show variables like "%關(guān)鍵字%";
mysql> show variables like "%password%";
mysql> show variables like "old_passwords";

臨時定義 mysql> set [global] 變量名=值；

永久定義
vim /etc/my.cnf
[mysqld]
變量名=值
....
:wq
#systemctl restart mysqld

mysql體系結(jié)構(gòu)？
連接池
sql接口
分析器
優(yōu)化器
查詢緩存
存儲引擎
文件系統(tǒng)
管理工具

MySQL服務(wù)處理查詢請求的過程？

4 程序編寫的查詢數(shù)據(jù)庫的sql查詢命令不合理 ，導(dǎo)致數(shù)據(jù)庫處理慢
在數(shù)據(jù)庫服務(wù)器上啟用"慢查詢?nèi)罩?quot;：記錄超過指定時間顯示查詢結(jié)果的sql命令.（忍受值3秒）

5 數(shù)據(jù)存儲架構(gòu)設(shè)置有數(shù)據(jù)傳輸瓶頸。

網(wǎng)站題目：6\MySQL主從同步、MySQL讀寫分離、MySQL性能調(diào)優(yōu)
網(wǎng)址分享：http://muchs.cn/article36/ipjjpg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、網(wǎng)站建設(shè)、小程序開發(fā)、響應(yīng)式網(wǎng)站、Google、虛擬主機(jī)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)