MySQL主從複製讀寫分離
MySQL主從複製與讀寫分離
MySQL使用主從複製與讀寫分離的原因
1、在企業應用中，成熟的業務通常資料量都比較大
2、單臺MySQL在安全性、高可用性和高併發方面都無法滿足實際的需求
（myisam基於表級鎖定；innodb基於行級鎖定 --儲存引擎不能滿足併發讀、寫的需求）
3、配置多臺主從資料庫伺服器以實現讀寫分離
MySQL主從複製原理
MySQL的複製型別
基於語句的複製
基於行的複製
混合型別的複製（語句、行、日誌檔案）
MySQL讀寫分離原理
1、只在主伺服器上寫，只在從伺服器上讀
2、主資料庫處理事務性查詢，從資料庫處理select查詢
3、資料庫複製用於將事務性查詢的變更同步到叢集中的從資料庫
4、複製的基本過程如下：
- Master將使用者對資料庫更新的操作以二進位制格式儲存到Binary Log日誌檔案中
- Slave上面的IO程式連線上Master，並請求從指定日誌檔案的指定位置（或者從最開始的日誌）之後的日誌內容
- Master接收來自Slave的IO程式的請求後，通過負責複製的IO程式根據請求資訊讀取制定日誌指定位置之後的日誌資訊，返回給Slave的IO程式。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置
- Slave的IO程式接收到資訊後，將接收到的日誌內容依次新增到Slave端的relay-log檔案的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清除的告訴Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”
- SlaveSql程式檢測到relay-log中新增了內容後，會馬上解析relay-log的內容稱為在Master端真實執行時候的那些可執行的內容，並在自身執行
MySQL主從複製讀寫分離步驟
環境
主庫
20.0.0.11
從庫
20.0.0.12
20.0.0.13
amoeba
20.0.0.15
客戶機
20.0.0.16
時間同步 (所有裝置時間保持一致)
主從複製
主庫
[root@server1 ~]# vi /etc/my.cnf #資料庫配置檔案
server-id = 1 #伺服器id，每個伺服器不能相同
log_bin=master-bin #主伺服器日誌檔案
log_slave_updates=true #允許中繼日誌讀取主伺服器的二進位制日誌
[root@server1 ~]# systemctl restart mysqld.service
[root@server1 ~]# ls /usr/local/mysql/data/ #檢視生成的二進位制日誌檔案
auto.cnf ib_logfile1 master-bin.index
ib_buffer_pool ibtmp1 mysql
ibdata1 lcx performance_schema
ib_logfile0 master-bin.000001 #日誌檔案 sys
[root@server1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456'; #為所有伺服器所有庫授權
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges; #更新許可權
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'myslave'@'20.0.0.%';
+--------------------------------------------------------+
| Grants for myslave@20.0.0.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> mysql> show master status; #檢視主的狀態，記下position的值(同步的位置)
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1119 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
從庫（20.0.0.12、20.0.0.13）
[root@server1 ~]# vi /etc/my.cnf #修改從伺服器主配置檔案引數
server-id = 11 #三臺資料庫id不同
relay_log=relay-log-bin #從主伺服器上同步日誌檔案記錄到本地中繼日誌
relay_log_index=slave-relay-bin.index #定義中繼日誌的索引
[root@server1 ~]# systemctl restart mysqld.service
[root@server1 ~]# mysql -uroot -p #登陸MySQL從伺服器
mysql> change master to master_host='20.0.0.11',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1119; #新增要同步資料的master伺服器的賬號、二進位制日誌檔案、開始同步的位置（position）
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #啟動從伺服器
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G #檢視從伺服器狀態
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.11
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1119
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes #此項需為yes
Slave_SQL_Running: Yes #此項需為yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1119
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: af0fbd40-464c-11eb-a010-000c29463cae
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
讀寫分離（amoeba伺服器）
安裝jdk環境（需要java環境）
[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz
[root@amoeba ~]# mv jdk1.8.0_91/ /usr/local/java
[root@amoeba ~]# vi /etc/profile.d/java.sh #環境檔案最後新增
export JAVA_HOME=/usr/local/java #設定java根目錄
export PATH=$PATH:$JAVA_HOME/bin #在PATH環境變數中新增JAVA根目錄下的bin子目錄
[root@server1 ~]# source /etc/profile.d/java.sh
[root@server1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/java/bin
[root@server1 ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
下載amoeba
wget https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
部署amoeba
[root@server1 ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip
[root@server1 ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@server1 ~]# chmod -R 755 /usr/local/amoeba/
[root@server1 ~]# vim /usr/local/amoeba/jvm.properties
32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize =16m -XX:MaxPermSize=96m"
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
在三臺MySQL上給amoeba授權
mysql> grant all on *.* to 'test'@'20.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
修改amoeba主配置檔案
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml
28 <property name="user">amoeba</property> #修改root為amoeba
29
30 <property name="password">123456</property> #新增資料庫登陸密碼
83 <property name="defaultPool">master</property> #修改multiPool為master
84
85 <!-- #註釋，需刪除
86 <property name="writePool">master</property> #主庫寫許可權
87 <property name="readPool">slaves</property> #從庫讀許可權
88 --> #註釋，需刪除
修改dbServers.xml 檔案
[root@server1 ~]# vi /usr/local/amoeba/conf/dbServers.xml
22 <!-- mysql schema --> #註釋行
23 <property name="schema">mysql</property> #MySQL5.7版本沒有預設的test庫，所以修改為mysql（5.7預設的資料庫）
24
25 <!-- mysql user --> #註釋行
26 <property name="user">test</property>
27 <!-- mysql password --> #註釋行
28 <property name="password">123456</property>
#配置三臺MySQL伺服器主機名和地址
43 <dbServer name="master" parent="abstractServer"> #修改server1為master
44 <factoryConfig>
45 <!-- mysql ip -->
46 <property name="ipAddress">20.0.0.11</property> #修改為主庫master的IP地址
47 </factoryConfig>
48 </dbServer>
49
50 <dbServer name="slave1" parent="abstractServer"> #修改為slave1
51 <factoryConfig>
52 <!-- mysql ip -->
53 <property name="ipAddress">20.0.0.12</property> #修改為從庫1的IP
54 </factoryConfig>
#新增下面slave2模組
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">20.0.0.13</property>
</factoryConfig>
57 <dbServer name="slaves" virtual="true">
58 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
59 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
60 <property name="loadbalance">1</property>
61
62 <!-- Separated by commas,such as: server1,server2,server1 -->
63 <property name="poolNames">slave1,slave2</property> #修改為slave1，slave2
64 </poolConfig>
65 </dbServer>
[root@server1 ~]# /usr/local/amoeba/bin/launcher #啟動anoeba
………………
2020-12-28 22:50:03 [INFO] Project Name=Amoeba-MySQL, PID=15094 , System shutdown ....
^C
#強行結束
[root@server1 ~]# netstat -anpt | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 15051/java
效果驗證
客戶機
[root@server1 ~]# yum -y install mariadb*
[root@server1 ~]# mysql -uamoeba -p123456 -h 20.0.0.15 -P8066 #登入到amoeba伺服器
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 201047493
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
主庫
新建庫、表
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> create table aaa(id int(2), name char(32), age int(3));
Query OK, 0 rows affected (0.01 sec)
從庫1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc | #可以看到新建的庫
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| aaa | #新建的表
+---------------+
1 row in set (0.00 sec)
從庫2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| aaa |
+---------------+
1 row in set (0.00 sec)
從庫1關閉服務
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主庫新增資料
mysql> insert into aaa values(1,'zhangsan',19);
Query OK, 1 row affected (0.00 sec)
從庫1檢視資料為空
mysql> select * from aaa;
Empty set (0.00 sec)
從庫2正常複製資料
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
+------+----------+------+
1 row in set (0.00 sec)
從庫1開啟服務後，自動同步資料
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
+------+----------+------+
1 row in set (0.00 sec)
關閉兩臺從庫服務
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
向從庫中寫入資料
#從庫1
mysql> insert into aaa values(2,'wangwu',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
+------+----------+------+
2 rows in set (0.00 sec)
#從庫2
mysql> insert into aaa values(3,'chenqi',21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
+------+----------+------+
2 rows in set (0.00 sec)
客戶端檢視資料庫
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
+------+----------+------+
2 rows in set (0.00 sec)
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
+------+----------+------+
2 rows in set (0.00 sec)
#發現訪問的資料為輪詢機制
在客戶端上寫入資料
MySQL [abc]> insert into aaa values(5,'zhaosi',22);
Query OK, 1 row affected (0.00 sec)
只有主庫會記錄
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 5 | zhaosi | 22 |
+------+----------+------+
2 rows in set (0.00 sec)
開啟同步
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 5 | zhaosi | 22 |
+------+----------+------+
2 rows in set (0.00 sec)
在開啟同步後，主伺服器上的資料會同步到各從伺服器上中，但從伺服器上的自己增加的資料不會同步，只會本地儲存
客戶端訪問依然保持輪詢
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
| 5 | zhaosi | 22 |
+------+----------+------+
3 rows in set (0.01 sec)
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
| 5 | zhaosi | 22 |
+------+----------+------+
3 rows in set (0.00 sec)
相關文章
- MySQL主從複製與讀寫分離MySql
- Mysql-主從複製與讀寫分離MySql
- MySQL主從複製架構搭建及讀寫分離測試MySql架構
- MYSQL 主從 + ATLAS 讀寫分離 搭建MySql
- 配置\清除 MySQL 主從 讀寫分離MySql
- Linux下MySQL主從複製(GTID)+讀寫分離(ProxySQL)-實施筆記LinuxMySql筆記
- 搭建MySQL主從實現Django讀寫分離MySqlDjango
- 搭建Redis簡易叢集實現主從複製和讀寫分離Redis
- MySQL主從同步讀寫分離的叢集配置MySql主從同步
- 專題《一》mysql優化 ---------主從複製，讀寫MySql優化
- 寶塔 liunx redis 設定讀寫分離主從複製 + 哨兵自動值守Redis
- MySQL從庫卡主了--讀寫分離也不能亂讀MySql
- 高可用Mysql架構_Mysql主從複製、Mysql雙主熱備、Mysql雙主雙從、Mysql讀寫分離（Mycat中介軟體）、Mysql分庫分表架構（Mycat中介軟體）的演變MySql架構
- Mycat中介軟體實現Mysql主從讀寫分離MySql
- MySQL運維15-一主一從讀寫分離MySql運維
- MySQL運維16-雙主雙從讀寫分離MySql運維
- [PHP]Larval主從讀寫分離配置PHP
- MySQL主從複製與主主複製MySql
- Redis哨兵模式（sentinel）學習總結及部署記錄（主從複製、讀寫分離、主從切換）Redis模式
- MySQL 高可用架構：主從備份及讀寫分離MySql架構
- MySQL的主從複製與MySQL的主主複製MySql
- mysql5.7主從複製，主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- mysql複製--主從複製配置MySql
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- MySQL主從複製MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- MySQL怎麼實現主從同步和Django實現MySQL讀寫分離MySql主從同步Django
- MySQL主從複製_複製過濾MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL主從複製原理MySql
- MySQL的主從複製MySql
- mysql--主從複製MySql
- mysql主從複製搭建MySql
- MySql 主從複製配置MySql
- MySQL主從複製配置MySql