本文主要給大家介紹MySQL主從復制類型及讀寫分離原理，其所涉及的東西，從理論知識來獲悉，有很多書籍、文獻可供大家參考，從現實意義來講，億速云累計多年的實踐經驗可分享給大家。
amoeba服務器IP地址：192.168.144.160
master服務器IP地址：192.168.144.151
slave1服務器IP地址：192.168.144.163
slave2服務器IP地址：192.168.144.145
client客戶端[root@ameoba ~]# yum install ntp -y //安裝時間服務器
[root@master1 ~]# vim /etc/ntp.conf
##在server下添加
server 127.127.144.0 //本地是時間源
fudge 127.127.144.0 stratum 8 //設置時間層級為8
[root@master1 ~]# systemctl start ntpd //啟動時間服務
[root@master1 ~]# systemctl stop firewalld //關閉防火墻
[root@master1 ~]# setenforce 0[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 //同步主服務器時間
29 Nov 16:58:43 ntpdate[4932]: the NTP socket is in use, exiting[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[root@master1 ~]# mkdir /abc //創建掛載點
[root@master1 ~]# mount.cifs //192.168.100.8/LNMP-C7 /abc/ //遠程掛載
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 //系統調用來實現異步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 //創建安裝目錄
[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 \ //存儲引擎
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_MEMORY_STORAGE_ENGINE=1 \
> -DWITH_READLINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DMYSQL_DATADIR=/home/mysql \ //數據文件路徑
> -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 //設置mysql屬主屬組
[root@master1 mysql-5.5.24]# vim /etc/profile //配置環境變量便于系統識別
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 //啟動文件
[root@master1 mysql-5.5.24]# chmod 755 /etc/init.d/mysqld //設置權限
[root@master1 mysql-5.5.24]# chkconfig --add /etc/init.d/mysqld //添加到service管理中
[root@master1 mysql-5.5.24]# chkconfig mysqld --level 35 on //開機自啟動
[root@master1 mysql-5.5.24]# /usr/local/mysql/scripts/mysql_install_db \ //初始化數據庫
> --user=mysql \
> --ldata=/var/lib/mysql \
> --basedir=/usr/local/mysql \
> --datadir=/home/mysql
[root@master1 mysql-5.5.24]# vim /etc/init.d/mysqld //編輯啟動腳本文件
basedir=/usr/local/mysql //找到此處添加路徑
datadir=/home/mysql
[root@master1 mysql-5.5.24]# service mysqld start //啟動MySQL
Starting MySQL.. SUCCESS!
[root@master1 mysql-5.5.24]# mysqladmin -u root password 'abc123' //設置密碼[root@master1 mysql-5.5.24]# vim /etc/my.cnf
server-id = 11 //服務ID號
log-bin=master-bin //主服務器日志文件
log-slave-updates=true //從服務器更新二進制日志
[root@master1 mysql-5.5.24]# service mysqld restart //重啟MySQL服務
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@master1 ~]# mysql -uroot -pabc123 //進入數據庫
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.144.%' IDENTIFIED BY '123456'; //給從服務器提權復制權限，名為myslave密碼123456對于144段網段
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES; ##刷新提權
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; ##查看主服務器狀態
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 338 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)[root@slave1 mysql-5.5.24]# vim /etc/my.cnf
server-id = 22 //另外一臺為23
relay-log=relay-log-bin //從主服務器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index //定義relay-log的位置和名稱
[root@slave1 mysql-5.5.24]# service mysqld restart //重新服務
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; //同步主服務器二進制文件和位置使用授權的賬號密碼
Query OK, 0 rows affected (0.00 sec)
mysql> start slave; //開啟同步
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G; ##查看狀態
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[root@slave2 mysql-5.5.24]# vim /etc/my.cnf
server-id = 23
relay-log=relay-log-bin //從主服務器上同步日志文件記錄到本地
relay-log-index=slave-relay-bin.index //定義relay-log的位置和名稱
[root@slave2 mysql-5.5.24]# service mysqld restart //重新服務
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; //同步主服務器二進制文件和位置使用授權的賬號密碼
Query OK, 0 rows affected (0.00 sec)
mysql> start slave; //開啟同步
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G; //查看狀態
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: Yesmysql> create database school; //創建school數據庫
Query OK, 1 row affected (0.00 sec)mysql> show databases; //查看數據庫,實現主從同步
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.01 sec)mysql> show databases; //查看數據庫,實現主從同步
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.01 sec)[root@amoeba ~]# systemctl stop firewalld.service //關閉防火墻
[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/ //復制jdk二進制文件到/usr/local下
[root@amoeba mnt]# cd /usr/local/
[root@amoeba local]# ./jdk-6u14-linux-x64.bin //直接執行安裝
Do you agree to the above license terms? [yes or no]
yes //選擇yes進行安裝
Press Enter to continue.....
##回車繼續
[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6 //簡化文件名
[root@amoeba local]# vim /etc/profile //設置環境變量
...
export JAVA_HOME=/usr/local/jdk1.6 //家目錄
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib //class環境變量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin //環境變量
export AMOEBA_HOME=/usr/local/amoeba //amoeba家目錄
export PATH=$PATH:$AMOEBA_HOME/bin //環境變量
[root@amoeba local]# source /etc/profile //刷新配置文件
[root@amoeba local]# mkdir /usr/local/amoeba //創建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/ //設置權限
[root@amoeba mnt]# /usr/local/amoeba/bin/amoeba //檢查是否安裝成功
amoeba start|stopgrant all on *.* to test@'192.168.144.%' identified by '123.com'; //給amoeba訪問權限用戶test密碼123.com[root@amoeba conf]# vim amoeba.xml //修改主配置文件
---30行--
<property name="user">amoeba</property> //從服務器同步主服務器的用戶密碼
----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 //配置數據庫配置文件
--26-29--去掉注釋--
<property name="user">test</property>
<property name="password">123.com</property>
-----42-主服務器地址---
<dbServer name="master" parent="abstractServer">
<property name="ipAddress">192.168.144.151</property>
--52-從服務器主機名-
<dbServer name="slave1" parent="abstractServer">
<property name="ipAddress">192.168.144.163</property>
##復制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服務
[root@amoeba ~]# netstat -anpt | grep java //開啟另一個終端查看開啟情況
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 [root@client ~]# yum install mysql -y //安裝測試數據庫[root@client ~]# mysql -u amoeba -p123456 -h 192.168.144.160 -P8066 //使用amoeba賬戶密碼登錄amoeba
MySQL [(none)]> show databases; //查看數據庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.00 sec)
MySQL [(none)]> use school; //使用數據庫
Database changed
MySQL [school]> create table info ( //創建表
-> 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)mysql> use school; ##使用數據庫
Database changed
mysql> show tables; ##查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)MySQL [school]> insert into info (id,name,score) values (1,'zhangsan',88); //插入數據內容
Query OK, 1 row affected (0.03 sec)mysql> select * from info;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 88.0 |
+----+----------+-------+
1 row in set (0.00 sec)mysql> select * from info; //slave上沒有寫入
Empty set (0.00 sec)mysql> insert into info (id,name,score) values (2,'lisi',70);
Query OK, 1 row affected (0.00 sec)mysql> insert into info (id,name,score) values (3,'wuwang',60);
Query OK, 1 row affected (0.00 sec)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主從復制類型及讀寫分離原理，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，大家可以繼續關注億速云行業資訊板塊，會定期給大家更新行業新聞和知識，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
