mysql1.6(主從同步,資料讀寫分離,mysql最佳化)

標籤：SQL_error   lin   log檔案   def   min   mysql最佳化   指定   add   mysql中介軟體   

目錄：   一，主從同步

              二，資料讀寫分離

              三，Mysql最佳化

一，mysql主從同步。

1.1什麼是主從同步

1.2主從同步的工作原理

1.3配置主從同步，並驗證配置

1.4 主從同步結構模式

1.5主從同步常用配置參數

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.1什麼是主從同步？

讓其他的Mysql資料庫伺服器和正在被訪問的資料庫伺服器上的資料保持一致。

 

1.2主從同步工作原理？

IO線程：把主庫binlog日誌裡的sql命令儲存到原生中繼記錄檔裡    

SQL線程：執行本機中繼記錄檔裡sql命令，把資料寫進原生資料庫伺服器裡。

 

1.3配置主從同步 並驗證配置

主庫的配置步驟：

(1)使用者授權: grant replication slave on *.* to [email protected]“192.168.4.52” identified by "123456";

(2)使用binlog日誌:

vim /etc/my.cnf

[mysqld]

server_id=51
log-bin=/binlogdir/master51
binlog_format="mixed"

mkdir /binlogdir          

 chown mysql /binlogdir/    

 systemctl restart mysqld

(3)查看正在使用的binlog檔案資訊

 mysql-> show  master  status;

 

環境準備：關閉防火牆，selinux，確保主庫的資料和從庫的資料要一致。

保留初始4個庫。

 

從庫的配置步驟

測試主庫使用者授權：mysql  -h192.168.4.51   -uplj  -p123456

指定server_id

vim  /etc/my.cnf　　s

[mysqld]

server_id=52
log_bin=/mylog/slave52

資料庫管理配置主庫資訊

mkdir /mylog

chown -R mysql  /mylog

systemctl restart mysqld  ; mysql -uroot -p123456

發起同步操作：

mysql-> change  master  to master_host=‘主伺服器IP‘，

         -> master_user=‘plj‘, (被授權使用者名)

         -> master_password=‘密碼‘ ,（被授權使用者密碼）

         -> master_log_file=‘記錄檔‘,  //可以在主上面show master status

         -> master_log_pos=位移位置;  //可以在主上面show master status

start  slave  開啟服務 //如果以後要更改資訊要先stop slave在去修改。

查看本機IO線程和SQL線程

mysql -> show  slave  status\G;

mysql->stop  slave;

mysql->start slave;

               Last_IO_Error:IO進程報錯資訊

               Last_SQL_Error:SQL進程報錯資訊

 

在庫下的資料庫目錄下多處來的檔案：

master.info   記錄主庫資訊

主機名稱-relay-bin.編號   中繼記錄檔

主機名稱-relay-bin.index  索引檔案（記錄已有的中繼記錄檔）

relay-log.info     記錄中繼日誌資訊

 

主庫：show processlist;

可以看到有個binlog dump 這個程式每次新進sql命令都會通知從庫上面的I/O線程去實現同步更新。

實現最終效果：從庫實現增，刪，改的即時同步。對主庫上的所有操作

在從庫上都會有對應資料。

 

暫時關閉從庫： stop slave ;  show slave status;

取消從庫定義：刪除主目錄下面的幾個檔案

                         rm -rf  master.info  relay-log.info mysql52-relay-bin.*

                         重起服務******************************************************************************************1.4主從同步的結構模式

一主一從

一主多從

主從從

主主結構

1.5主從同步常用配置參數（/etc/my.cnf）

主庫的配置參數(在主庫的設定對所有的從生效)

binlog_do_db=庫名列表 用,間隔符號  #只允許同步的庫

binlog_ignore_db=庫名列表 用,間隔符號  #只不允許同步的庫

 

從庫的配置參數(只有對從庫自己生效)

log_slave_updates //允許級聯複製

relay_log=檔案名稱  //指定中繼記錄檔的名字

replicate_do_db=庫名列表 用,間隔符號  #只同步的庫

replicate_ignore_db=庫名列表 用,間隔符號  #只不同步的庫

*************************************************************************************

二，資料讀寫分離(maxscale軟體+一主一從結構)

mysql中介軟體(和mysql服務一起提供服務的軟體)

實現讀寫分離的軟體：mycat   mysql-proxy   maxscale

2.1什麼是資料讀寫分離

把用戶端訪問資料時的查詢請求和寫入請求

                            分別給不同的資料庫伺服器處理。

2.2為什麼要做讀寫分離

減輕單台伺服器的並發訪問壓力同時提高硬體的利用率。

3.3配置讀寫分離

(1)配置主從同步(一主一從  51(主)  52 (從))

(2)配置Proxy 伺服器55

1,把資料庫服務停止並設定開機不運行,禁用selinux 和firewalld

2,裝包  修改設定檔  使用者授權 啟動服務   查看連接埠號碼

rpm -Uvh maxscale-2.1.2-1.rhel.7.x86_64.rpm 

cp /etc/maxscale.cnf  /root/

vim /etc/maxscale.cnf

9 [maxscale]
10 threads=auto   //用來自動匹配cpu的線程

18 [server1]   ：定義監聽主機1
19 type=server
20 address=192.168.4.51
21 port=3306
22 protocol=MySQLBackend
23
24 [server2]   ：定義監聽主機2
25 type=server
26 address=192.168.4.52
27 port=3306
28 protocol=MySQLBackend

36 [MySQL Monitor]
37 type=monitor
38 module=mysqlmon
39 servers=server1,server2  //定義監聽主機
40 user=scalemon    //監聽主機的登陸使用者
41 passwd=123456
42 monitor_interval=10000

53 #[Read-Only Service]   //注釋行
54 #type=service
55 #router=readconnroute
56 #servers=server1
57 #user=myuser
58 #passwd=mypwd
59 #router_options=slave

64 [Read-Write Service]
65 type=service
66 router=readwritesplit
67 servers=server1,server2  //額外添加主機
68 user=maxscale    //用來檢測客戶機登陸使用者是否存在的使用者
69 passwd=123456        
70 max_slave_connections=100%

86 #[Read-Only Listener]  //注釋掉這些行
87 #type=listener
88 #service=Read-Only Service
89 #protocol=MySQLClient
90 #port=4008

92 [Read-Write Listener]
93 type=listener
94 service=Read-Write Service
95 protocol=MySQLClient
96 port=4006
97
98 [MaxAdmin Listener]
99 type=listener
100 service=MaxAdmin Service
101 protocol=maxscaled
102 socket=default
103 port=4016 //額外添加行

在主從資料庫上授權：

grant replication slave,replication client on *.* to 

-> [email protected]‘%‘ identified by "123456";  //授權給maxscale軟體串連使用者監聽許可權

grant select on mysql.* to [email protected]‘%‘ identified by "123456"; //授權maxscale軟體maxscale     使用者查詢許可權，用來檢測代碼登陸使用者是否存在。　　

測試：mysql  -h51/52主機IP  -u授權使用者 -p密碼 去測試是否可以登陸

3,訪問管理連接埠查看主從同步狀態資訊及資料庫服務的狀態

maxscale -f /etc/maxscale.cnf   //啟動服務 

netstat  -antpul | grep maxscale  //查看進程

ps -C maxscale   //查看進程pid  

停止服務：killall  -9  maxscale

#maxadmin -uadmin -pmariadb -P4016

list servers //查尋讀寫分離客戶機的運行狀態。

 

3.4測試組態？在用戶端測試組態

注意：tony手動去主服務授權對一個庫.*授權all許可權.

#mysql  -h192.168.4.55  -P4006  -utony  -p123456

mysql -> 使用一個查詢語句/一個寫入命令。

測試是否讀寫分離，讀在52伺服器，寫在51伺服器。

 

3.5配置分離結構有什麼缺點？

單點故障，資料量大的時。資料轉送會有瓶頸。

3.6資料讀寫分離拓撲結構

                   4.254

                       |

                       |

                      55（安裝maxscale軟體）

+++++++++++++++++++++++++++

      51主                      52從

 

mysql1.6(主從同步,資料讀寫分離,mysql最佳化)