MySQL 主從同步 、 MySQL 讀寫分離

標籤：資源   資料庫伺服器   serve   ali   mysq   netstat   isa   err   rest   

一、mysql主從同步
二、資料讀寫分離
三、MySQL最佳化
++++++++++++++++++++++++++++++++
一、mysql主從同步
1.1 主從同步介紹？從程式庫伺服器自動同步主庫上資料（被用戶端訪問的資料庫伺服器做主程式庫伺服器）
1.2 結構

          54                 55

systemctl start mysqld systemctl start mysqld
主master資料庫伺服器 從slave資料庫伺服器

1.3 配置主從同步結構？
1.3.1主庫角色主機的配置
1 使用者授權
mysql> grant replication slave on . to [email protected]"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 重啟資料庫伺服器
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從庫角色主機的配置？
1 顯示自己是否是從資料庫伺服器
mysql> show slave status;

2 測試主庫授權的使用者是否有效
#mysql -h192.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 在本機資料管理員登入指定主庫資訊。
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

驗證主從同步配置：結果
用戶端串連主程式庫伺服器 產生的新資料，在從角色的主機上都能夠查看到。

+++++++++++++++++++++++++++++++
主從工作原理？
IO線程： 把master 庫的 binlog日誌內容 記錄到原生relay-binlog日誌裡。

SQL線程：執行本機relay-binlog日誌裡的sql命令 把資料寫進原生庫。

從角色主機的資料庫目錄下會多出如下檔案：
master.info 儲存主要資料庫的資訊
slave55-relay-bin.XXXXXX
中繼記錄檔 記錄本機產生新資料的sql命令
slave55-relay-bin.index 中繼記錄檔索引檔案
relay-log.info 記錄當前資料庫伺服器使用的中繼記錄檔

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

查看報錯資訊：
Last_IO_Error: 報錯資訊

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

SQL線程報錯： 執行中繼日誌裡的sql命令時，命令操作的庫或表在本機不存在。

查看報錯資訊：
Last_SQL_Error: 報錯資訊
mysql> stop slave;
在從本機有命令操作的庫或表
mysql> start slave;

++++++++++++++++++++++++++++
讓從資料庫臨時不同步主庫的資料？
mysql> stop slave;

把從庫 還原成獨立的資料庫伺服器？
#rm -rf master.info slave55-relay-bin.* relay-log.info
#systemctl restart mysqld

mysql> show slave status;

主從同步結構模式？
一主一從
一主多從
主從從
互為主從（主主結構）

reset master #清空主的日誌
reset slave #只清空日誌
reset slave all #需要重新設定change master

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

mysql> show master stauts;

從庫角色主機的配置參數
[mysqld]
log_slave_updates 級聯複製
replicate_do_db=資料庫名，資料庫名 #只同步的庫
replicate_ignore_db=資料庫名，資料庫名 #只不同步的庫relay_log=檔案名稱 #設定中繼記錄檔名稱
+++++++++++++++++++++++++++++
二、資料讀寫分離
2.1 讀寫分離介紹？把客戶的查詢記錄的操作和寫資料的操作在不同的資料庫伺服器上執行。

2.2 為什麼要做讀寫分離？ 減輕單台資料庫伺服器的並發訪問壓力和提供伺服器硬體資源的利用率

2.3 配置資料讀寫分離 （Maxscale + 主從同步結構）

2.3.1 拓撲結構
client
|
53 maxscale 4010 管理連接埠 4006 讀寫分離連接埠

insert select
寫 查
54 55
主 從

2.3.2 配置MySQL一主一從同步結構
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 # 監控資料庫服務服務的運行狀態和主從狀態
43 passwd=123456
44 monitor_interval=10000

66 [Read-Write Service]
67 type=service
68 router=readwritesplit
69 servers=server1, server2
70 user=maxscale #檢查接收用戶端串連請求時，串連的使用者名稱和密碼在資料庫伺服器上是否存在
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

根據設定檔的設定在資料庫伺服器上添加對應的授權使用者。
mysql> grant replication slave, replication client on . to [email protected]‘%‘ identified by

"123456";

mysql> grant select on mysql.* to [email protected]‘%‘ identified by "123456";

c. 啟動服務

systemctl stop mysqld

[[email protected] ~]# maxscale -f /etc/maxscale.cnf
[[email protected] ~]# netstat -utnalp | grep :4010
tcp6 0 0 :::4010 ::: LISTEN 9559/maxscale
[[email protected] ~]#
[[email protected] ~]# netstat -utnalp | grep :4006
tcp6 0 0 :::4006 ::: LISTEN 9559/maxscale
[[email protected] ~]# netstat -utnalp | grep maxscle
[[email protected] ~]# 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
[[email protected] ~]#
53訪問原生管理服務 查看監控主機的狀態
[[email protected] ~]# maxadmin -P4010 -uadmin -pmariadb
MaxScale>list servers

停止maxscale 服務
#ps -C maxscale
#kill -9 pid號

d. 在用戶端測試組態
#ping -c 2 192.168.4.53

d.1 在資料庫伺服器添加訪問資料的使用者
mysql> grant all on . to [email protected]‘%‘ identified by "123456";

d.2 訪問代理主機
#mysql -P4006 -h192.168.4.53 -ustudent -p123456

+++++++++++++++++++++++++++++++++++++
三、MySQL最佳化(最佳化思路、 最佳化什麼 、怎麼最佳化)
訪問資料時，出結果特別慢。分析可能是由哪些原因導致的。

1 硬體設定低：查看應用裝置的使用率CPU 記憶體 儲存(硬碟)
top 0.0 wa
sar
uptime I/O

                free  -m

2 網路頻寬： 使用網路測速軟體 網速

3 提供資料庫服務軟體版本低：
查看資料庫服務運行時的運行參數配置（常用配置參數）

逾時時間
connect_timeout
建立串連時，三向交握的逾時時間
wait_timeout 建立串連後，等待中斷連線的逾時時間

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

允許重複使用的線程的數量
mysql> show variables like "thread_cache_size";

為所有線程緩衝開啟表的數量。
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

查詢快取相關參數設定？
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 關閉 不儲存
1 開啟 無條件儲存
2 開啟 指定儲存才儲存

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

查詢查詢快取統計資訊？
MySQL> show global status like "qcache%";
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0

並發串連數：
mysql> show variables like "max_connections";

曾經有過的最大串連數
mysql> show global status like "Max_used_connections";

公式：
曾經有過的最大串連數/並發串連數=0.85 * 100% = 85%
3000/ X = 0.85

查看參數的值
MySQL> show variables like "%關鍵字%";
mysql> show variables like "%password%";
mysql> show variables like "old_passwords";

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

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

mysql體繫結構？
串連池
sql介面
分析器
最佳化器
查詢快取
儲存引擎
檔案系統
管理工具

MySQL服務處理查詢請求的過程？

4 程式編寫的查詢資料庫的sql查詢命令不合理 ，導致資料庫處理慢
在資料庫伺服器上啟用"慢查詢日誌"：記錄超過指定時間顯示查詢結果的sql命令.（忍受值3秒）

5 資料存放區架構設定有資料轉送瓶頸。

MySQL 主從同步 、 MySQL 讀寫分離