MySQL主從同步與讀寫分離

標籤：停止   儲存   netstat   部署   搭建   索引   客戶   均衡   構建   

MySQL主從同步
MySQL AB複製
1.對指定庫的異地同步。
2.MySQL主-->從複製架構的實現。
3.MySQL伺服器的唯讀控制。

主從：
單向複製時，建議將從庫設定為唯讀。

主從複製的原理：
Master,記錄資料更改操作。
-啟動binlog日誌
-設定binlog日誌格式
-設定server_id

Slave，運行2個線程。
-Slave_IO：複製master主機binlog記錄檔裡的SQL到原生relay-log檔案裡。
-Slave_SQL：執行本機relay-log檔案裡的SQL語句，重現Master的資料操作。

構建主從同步
1.確保資料相同
-從庫必須要有主庫上的資料。

2.配置主伺服器
-啟用binlog日誌及設定格式，設定server_id,授權使用者。

3.配置從伺服器
-設定server_id，配置為從資料庫伺服器。

4.測試同步效果
-用戶端串連主庫，寫入的資料，在訪問從庫的時候也能夠看到。

確保資料相同
Master伺服器：
-應包括希望同步的所有庫
-對採用MyISAM的庫，可離線備份
mysql>reset master; //重設binlog日誌
...
#mysqldump -u root -p 123456 -B mysql test > /root/mytest.sql

Slave伺服器：
-離線匯入有Master提供的備份
-清空同名庫（如果存在）
mysql>drop database test; //先清理目標庫
#scp master:/root/mytest.sql ./ //直接scp遠程拷貝
#mysql -u root -p 123456 < /root/mytest.sql //確保與master的資料相同

配置主伺服器
1.啟用binlog及允許同步：
vim /etc/my.cnf
[mysqld]
log_bin=master-bin //啟用binlog日誌
server_id = 10 //指定伺服器ID號
binlog_format=mixed //指定日誌格式
sync-binlog=1 //允許日誌同步
....

#systemctl start mysqld //啟動服務

2.授權備份使用者：
-允許lisi從192.168.4.0/24網段訪問
-對所有庫（預設不允許對單個庫）有同步許可權
mysql>grant replication slave on . to "lisi"@"192.168.4.%" identified by "123456";

3.查看Master狀態：
-記住當前的記錄檔名、位移量位置
mysql>show master status\G;
......
File: master-bin.000002 //記錄檔名
Position: 334 //位移量
.....

配置從伺服器
1.啟用binlog及允許同步，啟用唯讀模式。
vim /etc/my.cnf
[mysqld]
log_bin=slave-bin //啟用binlog日誌
server_id = 20 //指定伺服器ID號
sync-binlog=1 //允許日誌同步
read_only=1 //唯讀模式，同步及SUPER許可權使用者例外

#systemctl start mysqld //啟動服務

2.指定Master相關參數
mysql>change master to master_host="192.168.4.10",
->master_user="lisi",
->master_password="123456",
->master_log_file="master-bin.000002", //記錄檔
->master_log_pos=334; //位移量

mysql>start slave; //啟動複製

Master資訊會自動儲存到/var/lib/mysql/master.info檔案，若要更改Master資訊時，應先stop slave;

3.查看Slave狀態
-確認IO線程、SQL線程都已運行
mysql>show slave status\G
.....
Slave_IO_Running:Yes //IO線程已運行
Slave_SQL_Running:Yes //SQL線程已運行

從資料庫目錄下多出檔案：
/var/lib/mysql/master.info //串連主伺服器資訊
/var/lib/mysql/relay-log.info //中繼日誌資訊
/var/lib/mysql/主機名稱-relay-bin.xxxxxx //中繼記錄檔
/var/lib/mysql/bogon-relay-bin.index //中繼日誌索引檔案

測試主從同步配置：
1 在主程式庫伺服器上添加訪問資料的使用者
2 在用戶端使用授權使用者串連主庫，產生的資料在從庫本機也能夠查看的到。

Master伺服器常用選項：
binlog_do_db= //只允許複製的庫 binlog_do_db=庫名1，庫名2，庫名n
binlog_ignore_db= //不允許複製的庫 binlog_ignore_db=庫名1，庫名2，庫名n

Slave伺服器常用選項：
relay_log=Slave-relay-bin //指定中繼記錄檔名
log_slave_updates //記錄從庫更新，允許鏈式複製（級聯複製）
replicate_do_db=庫名1，庫名2，庫名n //僅複製指定庫，其他庫將被忽略（省略時複製所有庫）
replicate_ignore_db=庫名1，庫名2，庫名n //不複製哪些庫，其他庫將被忽略

註：replicate_do_db和replicate_ignore_db只需選用其中一種

主從複製的結構
單向瀆職：主-->從
鏈式複製：主-->從-->從
雙向複製：主<-->從
放射式複製：從<--主-->從

        MySQL讀寫分離

讀寫分離：把用戶端訪問資料時的查詢請求select 和寫insert 給不同的資料庫伺服器處理。
讀寫分離的原理：
1.多台MySQL伺服器
-分別提供讀、寫服務，均衡流量
-通過主從複製保持一致性
2.由MySQL代理面向用戶端
-收到SQL寫請求時，交給伺服器A處理
-收到SQL讀請求時，交給伺服器B處理
-具體區分策略由服務設定

構建MySQL讀寫分離
1.搭建好MySQL主從複製
-Slave為唯讀

2.添加一台MySQLProxy 伺服器
-部署/啟用 maxscale

3.用戶端通過代理主機訪問MySQL資料庫

部署MySQL代理
安裝maxscale：
rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
rpm -qc maxscale
/etc/maxscale.cnf //主設定檔
....

修改設定檔：
vim /etc/maxscale.cnf
[server1] //定義資料庫伺服器
type=server
address=192.168.4.10 //master主機ip地址
port=3306
protocol=MySQLBackend

[server2] //定義資料庫伺服器
type=server
address=192.168.4.20 //slave主機ip地址
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
server=server1,server2 //定義的主、從資料庫伺服器列表
user=lisi //使用者名稱
passwd=123456 //密碼
monitor_interval=10000

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2 //定義的主、從資料庫伺服器列表
user=zhangsan //使用者名稱
passwd=123456 //密碼
max_slave_connections=100%

在主、從資料庫伺服器上建立授權使用者
mysql>grant replication slave,replication client on . to [email protected]‘%‘ identified by "123456" ; //建立監控使用者
mysql>grant select on mysql. to [email protected]‘%‘ identified by "123456"; //建立路由使用者
mysql>grant all on .* to [email protected]‘%‘ identified by "123456"; //建立訪問使用者

啟動maxscale
主要命令：
-啟動服務
-查看連接埠
-停止服務
maxscale --config=/etc/maxscale.cnf //啟動服務
或 maxscale -f /etc/maxscale.cnf
netstat -alntpu |grep maxscale
pkill -9 maxscale //停止服務

讀寫分離服務使用的連接埠、管理服務使用的連接埠
4006 4009

用戶端測試
登入MySQL代理:
-mysql -h 代理的ip地址 -p 連接埠 -u 使用者名稱 -p 密碼

測試SQL查詢、更新操作
-可成功查詢表記錄
-可成功寫入資料

登入MySQL代理：
mysql -h192.168.4.100 -p4006 -uadmin -p123456
mysql>select @@hostname; //查看當前訪問的主機名稱

MySQL主從同步與讀寫分離