MySQL主從復(fù)制類型及讀寫分離原理

本文主要給大家介紹MySQL主從復(fù)制類型及讀寫分離原理，其所涉及的東西，從理論知識(shí)來獲悉，有很多書籍、文獻(xiàn)可供大家參考，從現(xiàn)實(shí)意義來講，億速云累計(jì)多年的實(shí)踐經(jīng)驗(yàn)可分享給大家。

MySQL主從復(fù)制的類型

基于語句的復(fù)制(默認(rèn))

• 在主云服務(wù)器上執(zhí)行的語句，從服務(wù)器執(zhí)行同樣的語句

基于行的復(fù)制

• 把改變的內(nèi)容復(fù)制到從服務(wù)器

混合類型的復(fù)制

• 一旦發(fā)現(xiàn)基于語句無法精確復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制

主從復(fù)制的過

MySQL讀寫分離原理

• 讀寫分離就是只在主服務(wù)器上寫，只在從服務(wù)器上讀
• 主數(shù)據(jù)庫處理事務(wù)性查詢，而從數(shù)據(jù)庫處理select查詢
• 數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的變更同步到集群中的從數(shù)據(jù)庫

讀寫分離的過程

實(shí)踐操作

實(shí)驗(yàn)環(huán)境

amoeba服務(wù)器IP地址：192.168.144.160
master服務(wù)器IP地址：192.168.144.151
slave1服務(wù)器IP地址：192.168.144.163
slave2服務(wù)器IP地址：192.168.144.145
client客戶端

在master服務(wù)器上安裝ntp時(shí)間服務(wù)器

[root@ameoba ~]# yum install ntp -y   //安裝時(shí)間服務(wù)器
[root@master1 ~]# vim /etc/ntp.conf 
##在server下添加
server 127.127.144.0      //本地是時(shí)間源
fudge 127.127.144.0 stratum 8   //設(shè)置時(shí)間層級(jí)為8

[root@master1 ~]# systemctl start ntpd    //啟動(dòng)時(shí)間服務(wù)
[root@master1 ~]# systemctl stop firewalld   //關(guān)閉防火墻
[root@master1 ~]# setenforce 0

在slave1上安裝ntp，ntpdate服務(wù)

[root@slave1 ~]# yum install ntp ntpdate -y
[root@slave1 ~]# systemctl start ntpd
[root@slave1 ~]# systemctl stop firewalld
[root@slave1 ~]# setenforce 0
[root@slave1 ~]# /usr/sbin/ntpdate 192.168.144.151    //同步主服務(wù)器時(shí)間
29 Nov 16:58:43 ntpdate[4932]: the NTP socket is in use, exiting

在slave2上安裝ntp，ntpdate服務(wù)

[root@slave2 ~]# yum install ntp ntpdate -y
[root@slave2 ~]# systemctl start ntpd
[root@slave2 ~]# systemctl stop firewalld
[root@slave2 ~]# setenforce 0
[root@slave2 ~]# /usr/sbin/ntpdate 192.168.144.151
29 Nov 17:02:08 ntpdate[4850]: the NTP socket is in use, exiting

在master，slave1，slave2上分別安裝MySQL-5.5.24

[root@master1 ~]# mkdir /abc   //創(chuàng)建掛載點(diǎn)
[root@master1 ~]# mount.cifs //192.168.100.8/LNMP-C7 /abc/   //遠(yuǎn)程掛載
Password for root@//192.168.100.8/LNMP-C7:  
[root@master1 ~]# cd /abc/
[root@master1 abc]# lsmysql-5.5.24
[root@master1 abc]# tar zxvf mysql-5.5.24.tar.gz -C /opt/   //解壓
[root@master1 opt]# yum install -y \
> gcc gcc-c++ \
> ncurses \
> ncurese-devel \    //控制終端屏幕顯示的庫
> bison \                 //語法分析
> make
> cmake                 //cmake工具
> libaio-devel         //系統(tǒng)調(diào)用來實(shí)現(xiàn)異步IO

[root@slave1 opt]# useradd -s /sbin/nologin mysql   //添加不可登錄的mysql用戶
[root@slave1 opt]# cd /opt/mysql-5.5.24/
[root@slave1 mysql-5.5.24]# mkdir /usr/local/mysql   //創(chuàng)建安裝目錄
[root@slave1 mysql-5.5.24]# cmake \      //配置
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \    //安裝路徑
> -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock \   //sock文件路徑
> -DDEFAULT_CHARSET=utf8 \      //字符集
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DWITH_EXTRA_CHARSETS=all \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \     //存儲(chǔ)引擎
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_MEMORY_STORAGE_ENGINE=1 \
> -DWITH_READLINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DMYSQL_DATADIR=/home/mysql \     //數(shù)據(jù)文件路徑
> -DMYSQL_USER=mysql \      //用戶
> -DMYSQL_TCP_PORT=3306    //端口

[root@slave1 mysql-5.5.24]# make && make install   //編譯及安裝
[root@master1 mysql-5.5.24]# chown -R mysql.mysql /usr/local/mysql   //設(shè)置mysql屬主屬組
[root@master1 mysql-5.5.24]# vim /etc/profile   //配置環(huán)境變量便于系統(tǒng)識(shí)別
export PATH=$PATH:/usr/local/mysql/bin/
[root@master1 mysql-5.5.24]# source /etc/profile   //刷新配置文件
[root@master1 mysql-5.5.24]# cp support-files/my-medium.cnf /etc/my.cnf   //主配置文件
cp：是否覆蓋"/etc/my.cnf"？ yes
[root@master1 mysql-5.5.24]# cp support-files/mysql.server /etc/init.d/mysqld   //啟動(dòng)文件
[root@master1 mysql-5.5.24]# chmod 755 /etc/init.d/mysqld    //設(shè)置權(quán)限
[root@master1 mysql-5.5.24]# chkconfig --add /etc/init.d/mysqld    //添加到service管理中
[root@master1 mysql-5.5.24]# chkconfig mysqld --level 35 on   //開機(jī)自啟動(dòng)
[root@master1 mysql-5.5.24]# /usr/local/mysql/scripts/mysql_install_db \   //初始化數(shù)據(jù)庫
> --user=mysql \
> --ldata=/var/lib/mysql \
> --basedir=/usr/local/mysql \
> --datadir=/home/mysql

[root@master1 mysql-5.5.24]# vim /etc/init.d/mysqld    //編輯啟動(dòng)腳本文件
basedir=/usr/local/mysql   //找到此處添加路徑
datadir=/home/mysql
[root@master1 mysql-5.5.24]# service mysqld start    //啟動(dòng)MySQL
Starting MySQL.. SUCCESS! 
[root@master1 mysql-5.5.24]# mysqladmin -u root password 'abc123'  //設(shè)置密碼

配置master主服務(wù)器

[root@master1 mysql-5.5.24]# vim /etc/my.cnf
server-id       = 11     //服務(wù)ID號(hào)
log-bin=master-bin                         //主服務(wù)器日志文件
log-slave-updates=true                   //從服務(wù)器更新二進(jìn)制日志
[root@master1 mysql-5.5.24]# service mysqld restart   //重啟MySQL服務(wù)
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!
[root@master1 ~]# mysql -uroot -pabc123   //進(jìn)入數(shù)據(jù)庫

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.144.%' IDENTIFIED BY '123456';    //給從服務(wù)器提權(quán)復(fù)制權(quán)限，名為myslave密碼123456對(duì)于144段網(wǎng)段
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;    ##刷新提權(quán)
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;   ##查看主服務(wù)器狀態(tài)
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      338 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

配置slave1從服務(wù)器

[root@slave1 mysql-5.5.24]# vim /etc/my.cnf
server-id       = 22              //另外一臺(tái)為23

relay-log=relay-log-bin                         //從主服務(wù)器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index           //定義relay-log的位置和名稱
[root@slave1 mysql-5.5.24]# service mysqld restart    //重新服務(wù)
Shutting down MySQL. SUCCESS!  
Starting MySQL.. SUCCESS!
[root@slave1 mysql-5.5.24]# mysql -uroot -pabc123

mysql> change master to master_host='192.168.144.151',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=338;    //同步主服務(wù)器二進(jìn)制文件和位置使用授權(quán)的賬號(hào)密碼
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;     //開啟同步
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;   ##查看狀態(tài)
                            Master_Log_File: master-bin.000002
                    Read_Master_Log_Pos: 338
                             Relay_Log_File: relay-log-bin.000001
                                Relay_Log_Pos: 4
                Relay_Master_Log_File: master-bin.000002
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: Yes

配置slave2從服務(wù)器

[root@slave2 mysql-5.5.24]# vim /etc/my.cnf
server-id       = 23              

relay-log=relay-log-bin                         //從主服務(wù)器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index           //定義relay-log的位置和名稱
[root@slave2 mysql-5.5.24]# service mysqld restart    //重新服務(wù)
Shutting down MySQL. SUCCESS!  
Starting MySQL.. SUCCESS!
[root@slave2 mysql-5.5.24]# mysql -uroot -pabc123

mysql> change master to master_host='192.168.144.151',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=338;    //同步主服務(wù)器二進(jìn)制文件和位置使用授權(quán)的賬號(hào)密碼
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;    //開啟同步
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;    //查看狀態(tài)
                            Master_Log_File: master-bin.000002
                    Read_Master_Log_Pos: 338
                             Relay_Log_File: relay-log-bin.000001
                                Relay_Log_Pos: 4
                Relay_Master_Log_File: master-bin.000002
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: Yes

測(cè)試同步情況

Master

mysql> create database school;    //創(chuàng)建school數(shù)據(jù)庫
Query OK, 1 row affected (0.00 sec)

Slave1

mysql> show databases;   //查看數(shù)據(jù)庫,實(shí)現(xiàn)主從同步
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.01 sec)

Slave2

mysql> show databases;   //查看數(shù)據(jù)庫,實(shí)現(xiàn)主從同步
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.01 sec)

配置amoeba服務(wù)器

[root@amoeba ~]# systemctl stop firewalld.service   //關(guān)閉防火墻
[root@amoeba ~]# setenforce 0
[root@amoeba ~]# mount.cifs //192.168.100.8/LNMP-C7 /mnt/  //掛載
Password for root@//192.168.100.8/LNMP-C7:  
[root@amoeba ~]# cd /mnt/
[root@amoeba mnt]# ls
[root@amoeba mnt]# cp jdk-6u14-linux-x64.bin /usr/local/   //復(fù)制jdk二進(jìn)制文件到/usr/local下
[root@amoeba mnt]# cd /usr/local/
[root@amoeba local]# ./jdk-6u14-linux-x64.bin      //直接執(zhí)行安裝
Do you agree to the above license terms? [yes or no]
yes                //選擇yes進(jìn)行安裝
Press Enter to continue.....
##回車?yán)^續(xù)
[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6         //簡化文件名
[root@amoeba local]# vim /etc/profile         //設(shè)置環(huán)境變量
...
export JAVA_HOME=/usr/local/jdk1.6            //家目錄
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib            //class環(huán)境變量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin           //環(huán)境變量
export AMOEBA_HOME=/usr/local/amoeba                  //amoeba家目錄
export PATH=$PATH:$AMOEBA_HOME/bin                //環(huán)境變量
[root@amoeba local]# source /etc/profile            //刷新配置文件
[root@amoeba local]# mkdir /usr/local/amoeba         //創(chuàng)建amoeba目錄
[root@amoeba local]# cd /mnt/
[root@amoeba mnt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba      //解壓amoeba壓縮包
[root@amoeba mnt]# chmod -R 755 /usr/local/amoeba/            //設(shè)置權(quán)限
[root@amoeba mnt]# /usr/local/amoeba/bin/amoeba                //檢查是否安裝成功
amoeba start|stop

在master，slave1，slave2上授權(quán)amoeba訪問

grant all on *.* to test@'192.168.144.%' identified by '123.com';    //給amoeba訪問權(quán)限用戶test密碼123.com

回到amoeba服務(wù)器修改配置文件

[root@amoeba conf]# vim amoeba.xml       //修改主配置文件
---30行--
 <property name="user">amoeba</property>        //從服務(wù)器同步主服務(wù)器的用戶密碼
----32行---------
 <property name="password">123456</property>
---117-去掉注釋-
 <property name="defaultPool">master</property>
 <property name="writePool">master</property>
 <property name="readPool">slaves</property>
:wq
[root@amoeba conf]# vim conf/dbServers.xml       //配置數(shù)據(jù)庫配置文件
--26-29--去掉注釋--
 <property name="user">test</property>

 <property name="password">123.com</property>
-----42-主服務(wù)器地址---
<dbServer name="master"  parent="abstractServer">
 <property name="ipAddress">192.168.144.151</property>
--52-從服務(wù)器主機(jī)名-
<dbServer name="slave1"  parent="abstractServer">
 <property name="ipAddress">192.168.144.163</property>
 ##復(fù)制6行添加slave2
 <dbServer name="slave2"  parent="abstractServer">
 <property name="ipAddress">192.168.144.145</property>
--65行左右--
 <dbServer name="slaves" virtual="true">
 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
--末尾--
<property name="poolNames">slave1,slave2</property>
 </poolConfig>
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&      //開啟amoeba服務(wù)
[root@amoeba ~]# netstat -anpt | grep java           //開啟另一個(gè)終端查看開啟情況
tcp6       0      0 127.0.0.1:26268         :::*                    LISTEN      40925/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      40925/java          
tcp6       0      0 192.168.144.160:34090    192.168.144.151:3306     ESTABLISHED 40925/java          
tcp6       0      0 192.168.144.160:33866    192.168.144.145:3306     ESTABLISHED 40925/java          
tcp6       0      0 192.168.144.160:55984    192.168.144.163:3306     ESTABLISHED 40925/java 

在客戶端測(cè)試讀寫分離

[root@client ~]# yum install mysql -y      //安裝測(cè)試數(shù)據(jù)庫

client創(chuàng)建表

[root@client ~]# mysql -u amoeba -p123456 -h 192.168.144.160 -P8066     //使用amoeba賬戶密碼登錄amoeba
MySQL [(none)]> show databases;   //查看數(shù)據(jù)庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MySQL [(none)]> use school;        //使用數(shù)據(jù)庫
Database changed
MySQL [school]> create table info (         //創(chuàng)建表
        -> id int(4) not null primary key,
        -> name varchar(10) not null,
        -> score decimal(4,1) not null);
Query OK, 0 rows affected (0.02 sec)

查看主從服務(wù)器上是否有創(chuàng)建的表

mysql> use school;    ##使用數(shù)據(jù)庫
Database changed
mysql> show tables;  ##查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

在兩臺(tái)從服務(wù)器上關(guān)閉同步slave

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

在client上寫入表數(shù)據(jù)

MySQL [school]> insert into info (id,name,score) values (1,'zhangsan',88);  //插入數(shù)據(jù)內(nèi)容
Query OK, 1 row affected (0.03 sec)

在Master上查看

mysql> select * from info;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |  88.0 |
+----+----------+-------+
1 row in set (0.00 sec)

在從服務(wù)器上查看

mysql> select * from info;  //slave上沒有寫入
Empty set (0.00 sec)

在client查看

mysql> insert into info (id,name,score) values (2,'lisi',70);
Query OK, 1 row affected (0.00 sec)

在slave2寫入內(nèi)容

mysql> insert into info (id,name,score) values (3,'wuwang',60);
Query OK, 1 row affected (0.00 sec)

在測(cè)試機(jī)上查看info表數(shù)據(jù)（輪詢的方式）

MySQL [school]> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  3 | wuwang |  60.0 |
+----+--------+-------+
1 row in set (0.00 sec)

MySQL [school]> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | lisi |  70.0 |
+----+------+-------+
1 row in set (0.00 sec)
看了以上介紹MySQL主從復(fù)制類型及讀寫分離原理，希望能給大家在實(shí)際運(yùn)用中帶來一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，大家可以繼續(xù)關(guān)注億速云行業(yè)資訊板塊，會(huì)定期給大家更新行業(yè)新聞和知識(shí)，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問題的。