11_MySQL笔记-主从复制-延迟备份-读写分离
生活随笔
收集整理的這篇文章主要介紹了
11_MySQL笔记-主从复制-延迟备份-读写分离
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
文章目錄
- 主從復制
- 異步復制
- 半同步復制
- 同步復制
- 延遲備份
- 讀寫分離
- 高可用
- 實驗
個人博客
https://blog.csdn.net/cPen_web
主從復制
集群cluster：多臺MySQL服務器 集群：很多臺服務器做一樣的事情 如何保證數據庫里的內容是一樣的？ 如何分工？ 從服務器是如何知道主服務器上更新了哪些數據？答：二進制日志（binlog） 主從復制：其實就是復制的是主服務器的二進制日志，從服務器然后再重新根據日志操作一遍，從而達到和主服務器里的數據一樣，但是時間上會有延遲 如何解決延遲的問題？中間件 middleware --> WebLogic (oracle和java)、mysql-router、onekey (第三方公司)負載均衡器 (load balancer)讀寫分離：read、write 主從復制的環境準備：硬件：CPU、內存相同2~4個核心內存：4G系統：centos7軟件：MySQL版本要一樣5.7.32編譯安裝 --> 推薦yum安裝 (rpm)二進制安裝 --> 下載好別人已經編譯好的二進制包，解壓就可以使用了 --> 免安裝主從復制原理
異步復制
配置
操作步驟： (異步復制)步驟1 .修改MySQL的配置文件，啟動二進制日志功能 #步驟1.1：在主服務器上開啟二進制日志，server_id=1 [root@mysql-master ~]# vim /etc/my.cnf [mysqld] #binary log log_bin server_id = 1 [root@mysql-master ~]# killall -9 mysqld_safe [root@mysql-master ~]# killall -9 mysqld [root@mysql-master ~]# service mysqld start#步驟1.2：在從服務器上也可以開啟二進制日志，server_id=2 [root@mysql-slave ~]# vim /etc/my.cnf [mysqld] #binary log log_bin server_id = 2 [root@mysql-slave ~]# killall -9 mysqld_safe [root@mysql-slave ~]# killall -9 mysqld [root@mysql-slave ~]# service mysqld start--------------------------------------------------------------------------------------------------------------------------------- 步驟2 .在主服務器上新建并且授權一個用戶，用于復制二進制日志 [root@mysql-master ~]# mysql -uroot -p'Sanchuang123#' root@(none) mysql>grant replication slave on *.* to 'oudi'@'192.168.31.181' identified by 'Sanchuang123#';--------------------------------------------------------------------------------------------------------------------------------- 步驟3 .備份出主服務器上的數據，然后導入到從服務器上 [root@mysql-master ~]# mysqldump -uroot -p'Sanchuang123#' --all-databases > /backup/all_db.sql [root@mysql-master backup]# scp /backup/all_db.sql root@192.168.31.181:/root/在從服務器上導入數據 [root@mysql-slave ~]# mysql -uroot -p'Sanchuang123#' <all_db.sql--------------------------------------------------------------------------------------------------------------------------------- 步驟4 .在從服務器上配置master的信息 #注：在主服務器上查看當前的二進制日志的文件和位置號 root@(none) mysql>show master status; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | mysql-master-bin.000002 | 450 | | | | #注：刷新二進制日志文件 root@(none) mysql>flush logs; root@(none) mysql>show master status; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | mysql-master-bin.000003 | 154 | | | |------------------------------------------------------------------------------------------ #注：在從服務器上配置master的信息 [root@mysql-slave ~]# mysql -uroot -p'Sanchuang123#' root@(none) mysql>CHANGE MASTER TO MASTER_HOST='192.168.31.179' ,-> MASTER_USER='oudi',-> MASTER_PASSWORD='Sanchuang123#',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='mysql-master-bin.000003',-> MASTER_LOG_POS=154;CHANGE MASTER TO MASTER_HOST='192.168.31.179' ,MASTER_USER='oudi',MASTER_PASSWORD='Sanchuang123#',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-master-bin.000003',MASTER_LOG_POS=154;------------------------------------------------------------------------ #注：查看slave的狀態信息 root@(none) mysql>show slave status \G; *************************** 1. row ***************************Slave_IO_State: Master_Host: 192.168.31.180Master_User: oudiMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-master-bin.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql-slave-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-master-bin.000003Slave_IO_Running: NoSlave_SQL_Running: No …… #注：從服務器的IO線程和SQL線程沒有起來--------------------------------------------------------------------------------------------------------------------------------- 步驟5 .在master和slave上關閉防火墻和selinux #注：主服務器 [root@mysql-master ~]# service firewalld stop [root@mysql-master ~]# getenforce Disabled#注：從服務器 [root@mysql-slave ~]# service firewalld stop [root@mysql-slave ~]# getenforce Disabled--------------------------------------------------------------------------------------------------------------------------------- 解決UUID一樣的問題 下面步驟報錯：原因 虛擬機克隆時，主從兩臺數據庫的UUID相同https://blog.csdn.net/t1anyuan/article/details/78117672解決方法：data目錄下的 auto.cnf 中的 server-uuid值相同了找個字母隨便改一下就行 比如把 9改成8 ,重啟數據庫[root@mysql-slave ~]# vim /data/mysql/auto.cnf [auto]server-uuid=65d10cce-6c25-11eb-b2d5-000c29c71e05 #注：修改幾個字符就可以，和主服務器里的不一樣就可以[root@mysql-slave ~]# service mysqld restart--------------------------------------------------------------------------------------------------------------------------------- 步驟6 .在slave上啟動復制功能 [root@mysql-slave ~]# mysql -uroot -p'Sanchuang123#' root@(none) mysql>start slave; root@(none) mysql>show slave status \G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.31.179Master_User: oudiMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-master-bin.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql-slave-relay-bin.000007Relay_Log_Pos: 381Relay_Master_Log_File: mysql-master-bin.000003Slave_IO_Running: Yes --> 是yesSlave_SQL_Running: Yes --> 是yes 表示主從復制已經配置成功了 ……--------------------------------------------------------------------------------------------------------------------------------- 步驟7 .驗證主從復制的效果 #注：在master上新建庫 [root@mysql-master ~]# mysql -uroot -p'Sanchuang123#' root@(none) mysql>create database zouweicheng; root@(none) mysql>use zouweicheng; root@zouweicheng mysql>create table t1(id int); root@zouweicheng mysql>insert into t1 values(1),(2);#注：slave上查看 [root@mysql-slave ~]# mysql -uroot -p'Sanchuang123#' root@(none) mysql>show databases; | Database | | zouweicheng | …… root@(none) mysql>use zouweicheng; root@zouweicheng mysql>select * from t1; | id | | 1 | | 2 |--------------------------------------------------------------------------------------------------------------------------------- 步驟8 .到slave上去看master.info和relay-log.info [root@mysql-slave ~]# cd /data/mysql/ #注：到數據目錄下查看 [root@mysql-slave mysql]# ls |egrep "master.info|relay-log.info" master.info relay-log.info [root@mysql-slave mysql]# cat master.info …… [root@mysql-slave mysql]# cat relay-log.info ……--------------------------------------------------------------------------------------------------------------------------------- 相關問題解決方案 #示例：清空原來配置錯誤的master信息清除從服務器上填寫的主服務器的信息 root@(none) mysql>stop slave; root@(none) mysql>reset slave all; #注：清除所有的master信息 #注：重新輸入master的信息 CHANGE MASTER TO MASTER_HOST='192.168.31.179' ,MASTER_USER='oudi',MASTER_PASSWORD='Sanchuang123#',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-master-bin.000003',MASTER_LOG_POS=154;半同步復制
半同步 semi-sync
https://www.cnblogs.com/ivictor/p/5735580.html
配置
同步復制
https://blog.csdn.net/bmengmeng/article/details/98742927
https://dev.mysql.com/doc/refman/5.7/en/group-replication-enterprise-backup.html
延遲備份
https://dev.mysql.com/doc/refman/5.7/en/replication-delayed.html
https://www.cnblogs.com/yangxiaochu/p/9323118.html
GTID的主從復制
步驟參考 https://blog.51cto.com/13434336/2178937
https://blog.csdn.net/wzy0623/article/details/91047395
log_slave_updates=ON 有什么作用？
http://blog.itpub.net/12679300/viewspace-1319263
核心步驟參考 https://blog.51cto.com/13434336/2178937
GTID主從配置配置master vim /etc/my.cnf [mysqld] gtid-mode=ON enforce-gtid-consistency=ON server-id=1 binlog_format=row配置slave vim /etc/my.cnf [mysqld] gtid-mode=ON enforce-gtid-consistency=ON server-id=2 binlog_format=ROW log_slave_updates=ONmaster授權配置 略slave配置同步 mysql> change master to master_host='10.0.0.132', master_user='rep',master_password='123',master_port=3306,master_auto_position=1;=============================================================================== 配置gtid_mode=on enforce_gtid_consistency=onMySQL的集群架構主主復制主+從+從+從 --> 一主多從 雙機熱備雙機熱備的概念簡單說一下，就是要保持兩個數據庫的狀態自動同步。對任何一個數據庫的操作都自動應用到另外一個數據庫，始終保持兩個數據庫數據一致。這樣做的好處多1 .可以做災備，其中一個壞了可以切換到另一個2 .可以做負載均衡，可以將請求分攤到其中任何一臺上，提高網站吞吐量 主主復制 (異步復制)2臺主機都是master，業務數據同時往2臺機器上寫要避免數據冗余
主主復制主鍵沖突問題解決方案 https://www.jianshu.com/p/469279c1ad39
讀寫分離
讀寫分離的控制問題主要是用戶權限的控制，有寫權限的用戶走讀的通道也可以寫前提是在后端的MySQL數據庫服務器里創建讀和寫分開的用戶 日志/var/log/messages/var/log/mysqlrouter/mysqlrouter.log middleware：中間件軟件中間件：MySQLrouter、mycat、weblogic、oneproxymycat 開源的 http://www.mycat.org.cn/MySQLrouter 官方weblogic oracle數據庫的oneproxy https://www.cnblogs.com/youge-OneSQL/articles/4208583.html圖解 https://www.cnblogs.com/f-ck-need-u/p/9276639.html
高可用
高可用 - HAhigh availability 為什么要高可用？為了防止單點故障有備份，有替補 failover：故障切換、故障轉移 --> HA什么是keepalived？ https://www.keepalived.org/
vrrp協議 虛擬路由冗余協議 https://www.cnblogs.com/ananing/p/12803961.html
腦裂現象：master和backup上都有vip 為什么會出現腦裂？1 .兩邊的vrrp通告報文不能正常發送 --> 網絡通信不暢或者2邊有防火墻2 .master和backup的router id不同 腦裂有危害嗎？/腦裂對原來的業務有什么影響？后端真正去訪問db的ip地址不是vip，而是ens33上原來的ip地址。沒有影響驗證過的結論：沒有影響，可以正常訪問業務連接到后端的db不是使用vip，而是本機的真正配置的ip地址反而還可以起到負載均衡的作用正常 --> 發生腦裂，以及腦裂過程中這里應該是沒影響的 腦裂 --> 恢復正常，這個過程mysql這種有狀態的服務可能會導致數據丟失、連接中斷實驗
#注：安裝mysqlrouter [root@keepalived-master ~]# ls |grep mysql-router-community-8.0.23-1.el8.x86_64.rpm mysql-router-community-8.0.23-1.el8.x86_64.rpm [root@keepalived-master ~]# scp mysql-router-community-8.0.23-1.el8.x86_64.rpm root@192.168.31.84:/root[root@keepalived-backup ~]# rpm -ivh mysql-router-community-8.0.23-1.el8.x86_64.rpm #注：拷貝配置文件 [root@keepalived-master ~]# scp /etc/mysqlrouter/mysqlrouter.conf root@192.168.31.84:/root#注：替換配置文件 [root@keepalived-backup ~]# cp mysqlrouter.conf /etc/mysqlrouter/mysqlrouter.conf [root@keepalived-backup ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:read_write] bind_address = 192.168.31.84 bind_port = 7001 mode = read-write destinations = 192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2[routing:read_only] bind_address = 192.168.31.84 bind_port = 7002 mode = read-only destinations = 192.168.31.181:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2--------------------------------------------------------------------------------------------------------------------------------- [root@keepalived-backup ~]# service mysqlrouter start [root@keepalived-backup ~]# netstat -anplut |grep mysqlrouter tcp 0 0 192.168.31.84:7001 0.0.0.0:* LISTEN 16062/mysqlrouter tcp 0 0 192.168.31.84:7002 0.0.0.0:* LISTEN 16062/mysqlrouter #注：MySQL主從 目前OK root@(none) mysql>show slave status \G;Slave_IO_Running: YesSlave_SQL_Running: Yes#注：前面已經在master上建過讀寫、讀用戶--------------------------------------------------------------------------------------------------------------------------------- #注：測試讀寫分離功能 [root@test ~]# mysql -h 192.168.31.84 -P 7001 -uwuxiaohui -p'123456wu' #注：7001 寫的端口 wuxiaohui@(none) mysql> wuxiaohui@(none) mysql>show processlist; | Id | User | Host | db | Command | Time | State | Info | | 16 | wuxiaohui | 192.168.31.84:35704 | NULL | Query | 0 | starting | show processlist | [root@test ~]# mysql -h 192.168.31.84 -P 7002 -uzouweicheng -p'123456zou' #注：7002 讀的端口 #注：wuxiaohui連到master上面了 #注：zouweicheng連到slave上面了 #注：通過不同端口，讓它走master和slave，實現讀寫分離 #注：配置master、slave都可以讀修改mysqlrouter的配置文件 [root@keepalived-master ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:read_write] bind_address = 192.168.31.83 bind_port = 7001 mode = read-write destinations = 192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2[routing:read_only] bind_address = 192.168.31.83 bind_port = 7002 mode = read-only destinations = 192.168.31.181:3306,192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2 [root@keepalived-master ~]# service mysqlrouter restart[root@keepalived-backup ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:read_write] bind_address = 192.168.31.84 bind_port = 7001 mode = read-write destinations = 192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2[routing:read_only] bind_address = 192.168.31.84 bind_port = 7002 mode = read-only destinations = 192.168.31.181:3306,192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2 [root@keepalived-backup ~]# service mysqlrouter restart 部署keepalived # 在mysql-router1上安裝keepalived軟件 [root@keepalived-master ~]# yum install keepalived -y# 在mysql-router2上安裝keepalived軟件 [root@keepalived-backup ~]# yum install keepalived -y--------------------------------------------------------------------------------------------------------------------------------- # 第1臺keepalived服務器（Master）配置[root@keepalived-master ~]# vim /etc/keepalived/keepalived.conf ! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strict #注：注釋vrrp_garp_interval 0vrrp_gna_interval 0 }vrrp_instance VI_1 { #注：表示實例名state MASTER #注：狀態是masterinterface ens33 #注：在哪個網絡接口提供服務virtual_router_id 187 #注：虛擬路由器的id，id一樣表示是一個虛擬路由組，主和備id要一樣priority 180 #注：表示優先級，優先級數字越大優先級越高advert_int 1 #注：通告消息發送的間隔時間 默認為1秒authentication { #注：認證的密碼auth_type PASS #注：認證類型auth_pass 1111sc #注：認證密碼}virtual_ipaddress { #注：虛擬ip地址 vip192.168.31.136192.168.31.137} } [root@keepalived-master ~]# service keepalived start #注：刷新服務#注：把配置文件拷貝到keepalived-backup服務器上 [root@keepalived-master ~]# scp /etc/keepalived/keepalived.conf root@192.168.31.84:/etc/keepalived/ --------------------------------------------------------------------------------------------------------------------------------- # 第2臺keepalived服務器（BACKUP）配置[root@keepalived-backup ~]# vim /etc/keepalived/keepalived.conf ! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_DEVELvrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0 }vrrp_instance VI_1 {state BACKUPinterface ens33virtual_router_id 187priority 110advert_int 1authentication {auth_type PASSauth_pass 1111sc}virtual_ipaddress {192.168.31.136192.168.31.137} } [root@keepalived-backup ~]# service keepalived start #注：刷新服務 [root@keepalived-backup ~]# ps aux|grep keepalived root 16755 0.0 0.1 106212 992 ? Ss 15:38 0:00 /usr/sbin/keepalived -D root 16756 0.0 0.1 106212 996 ? S 15:38 0:00 /usr/sbin/keepalived -D root 16760 0.0 0.1 12324 1088 pts/0 S+ 15:39 0:00 grep --color=auto keepalived [root@keepalived-backup ~]# netstat -anplut |grep keepalived#注：keepalived進程運行起來了，但是不對外提供服務 (沒有監聽端口)=============================================================================== #注：在master和backup上的mysqlrouter都需要修改綁定ip為vip (或者0.0.0.0) 將vip綁定到mysqlrouter上 [root@keepalived-master ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:read_write] bind_address = 0.0.0.0 #注：綁定到本機所有的ip地址上 bind_port = 7001 mode = read-write destinations = 192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2[routing:read_only] bind_address = 0.0.0.0 bind_port = 7002 mode = read-only destinations = 192.168.31.181:3306,192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2 [root@keepalived-master ~]# service mysqlrouter restart #注：刷新服務[root@keepalived-backup ~]# vim /etc/mysqlrouter/mysqlrouter.conf [routing:read_write] bind_address = 0.0.0.0 bind_port = 7001 mode = read-write destinations = 192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2[routing:read_only] bind_address = 0.0.0.0 bind_port = 7002 mode = read-only destinations = 192.168.31.181:3306,192.168.31.179:3306 max_connections = 65535 max_connect_errors = 100 client_connect_timeout = 2 [root@keepalived-backup ~]# service mysqlrouter restart #注：刷新服務 #示例：vip漂移驗證vip是否能在master和backup之間漂移在master停止keepalived服務 [root@keepalived-master ~]# service keepalived stop #注：將master上的keepalived服務停掉=============================================================================== #示例：腦裂 1 .修改virtual_router_id [root@keepalived-backup ~]# vim /etc/keepalived/keepalived.conf vrrp_instance VI_1 {state BACKUPinterface ens33virtual_router_id 188 …… [root@keepalived-backup ~]# service keepalived restart#注：中間件mysqlrouter真正訪問后端的，用的不是vip，而是mysqlrouter上面真實的ip MySQL高可用解決方案經典MHAmysql實現高可用架構之MHA https://www.cnblogs.com/keerya/p/7883766.htmlMMMhttps://www.cnblogs.com/panwenbin-logs/p/8284593.html組復制同步復制mysql-cluster --> 官方 https://blog.csdn.net/nawenqiang/article/details/85051623https://dev.mysql.com/downloads/cluster/總結
以上是生活随笔為你收集整理的11_MySQL笔记-主从复制-延迟备份-读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 工程技术x计算机科学，TOP期刊Tran
- 下一篇： Unity使用Package Manag