MySQL 資料庫讀寫分離

語言: CN / TW / HK

MySQL 是最流行的關係型資料庫管理系統

♥ 文章宣告 ♥ 該系列文章部分文字描述,參考於以下文獻,化繁為簡. 《MySQL5.7從入門到精通》 - 劉增傑

MySQL 配置主備模式

基於一臺伺服器的資料複製,故得名單機熱備,主-備 Active-Standby 主-備方式,即指的是一臺伺服器處於某種業務的啟用狀態(即Active狀態),另一臺伺服器處於該業務的備用狀態(即Standby狀態),主資料庫資料更新後,備份伺服器同步資料只本機

兩臺同步執行: 安裝Mariadb,設定初始密碼,啟動服務,並設定開機自啟動,這裡的配置步驟應在兩臺主機上同步執行 BASH yum install -y mariadb mariadb-server systemctl restart mariadb mysql_secure_installation 修改MariaDB主配置檔案,寫入log-bin引數開啟二進位制日誌檔案,然後寫入server-id指定本臺MariaDB伺服器的ID號(注意:這裡主從不要重複) ```BASH vim /etc/my.cnf

[mysqld] log-bin=mysql-bin #開啟二進位制日誌檔案 server-id=12 #本臺MySQL伺服器ID(主從不能重複) systemctl restart mariadb #重啟生效 ```

主資料庫執行: 進入主MariaDB資料庫,授權給從資料庫登陸的賬號,這裡顯示二進位制日誌檔案大小並記錄下來 ```BASH grant replication slave on . to "使用者名稱"@'%' identified by "密碼"; #建立用於從伺服器登陸的賬號

show master status; #顯示日誌狀態

MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 245 | | | +------------------+----------+--------------+------------------+ row in set (0.00 sec) ```

從資料庫執行: 進入從MariaDB資料庫,使用主資料庫授權的使用者名稱,密碼,以及相應的日誌檔名稱 master_log_file,和日誌檔案位置 master_log_pos,此處在主資料庫上獲取即可 ```BASH change master to master_host='伺服器IP',master_user='授權賬號',master_password='密碼',master_log_file='日誌檔案',master_log_pos=檔案位置;

start slave; #啟用同步 show slave status\G; #檢視同步狀態

注意:兩個狀態同時開啟即可 Slave_IO_Running: Yes Slave_SQL_Running: Yes ```

MySQL 配置主主模式

基於兩臺伺服器的複製方式較多,故得名雙機熱備,雙主機方式即指兩種不同業務分別在兩臺伺服器上互為主備狀態(即 Active-Standby和Standby-Active狀態),這裡需要注意的是主-主同步配置,其實就是兩臺MariaDB資料庫互為主從,所以,要在兩臺主機間作兩次相同的操作

兩臺同步執行: 安裝MariaDB,設定初始密碼,啟動服務,並設定開機自啟動,這裡的配置步驟應在兩臺主機上同步執行 BASH yum install -y mariadb mariadb-server systemctl restart mariadb mysql_secure_installation 將MariaDB1的資料拷貝到MariaDB2上,實現資料的一致性,(注意:這裡應先鎖定表結構,防止資料不一致) ```BASH flush tables with read lock; #鎖定資料表為只讀模式 mysqldump -u root -p --all-databases >back.sql #備份資料庫

scp back.sql [email protected]:/root #將資料傳遞到遠端主機 unlock tables; #解除表鎖定 mysql -uroot -p <back.sql #從資料庫恢復資料 修改MariaDB主配置檔案(注意:兩臺都要修改),開啟二進位制日誌和制定伺服器ID號BASH vim /etc/my.cnf

[mysqld] log-bin=mysql-bin #開啟二進位制日誌 server-id=10 #指定伺服器ID號(兩臺不允許重複) replicate-do-db=test #要同步的資料庫 binlog-ignore-db=mysql #不需要同步的資料庫 binlog-ignore-db=information_schema #不需要同步的資料庫 auto-increment-increment=2 #每次自增長2 auto-increment-offset=1 #自增長從1開始

systemctl restart mariadb ```

MySQL1執行: 進入 MariaDB1 資料庫,建立用於從資料庫登陸的賬號和密碼 BASH grant replication slave on *.* to '使用者名稱'@'%' identified by '密碼'; #建立用於從資料庫登陸的使用者 show master status; #檢視二進位制檔名 進入 MariaDB2 資料庫,同步資料 ```BASH change master to master_host='主伺服器IP',master_user='授權賬號',master_password='密碼',master_log_file='二進位制文名',master_log_pos=檔案空間大小;

start slave; #啟動同步功能 show slave status\G; #檢視同步狀態 ```

MySQL2執行: 進入 MariaDB2 資料庫,建立用於從資料庫登陸的賬號和密碼 BASH grant replication slave on *.* to '使用者名稱'@'%' identified by '密碼'; #建立用於從資料庫登陸的使用者 show master status; #檢視二進位制檔名 進入 MariaDB1 資料庫,同步資料 ```BASH change master to master_host='主伺服器IP',master_user='授權賬號',master_password='密碼',master_log_file='二進位制文名',master_log_pos=檔案空間大小;

start slave; #啟動同步功能 show slave status\G; #檢視同步狀態 ```

MySQL 配置主主從模式

主-主-從,字面意思就是,兩個主一個從,主要用於資料備份,一次性備份兩個主資料庫資源,這樣一個從資料庫,就可以同時備份多個主資料庫了,節約了寶貴的伺服器資源

兩臺主資料庫執行: 兩臺主伺服器都需要安裝MySQL初始化並設定密碼 BASH yum install -y mariadb mariadb-server systemctl restart mariadb mysql_secure_installation 兩臺主伺服器都應配置以下內容(兩臺主MySQL都需要配置) ```BASH vim /etc/my.cnf #修改配置檔案

寫入以下內容↓ log-bin=mysql-bin #開啟二進位制日誌 server-id=147 #伺服器ID號

systemctl restart mariadb #重啟服務 登陸主MySQL並建立使用者從伺服器登陸的使用者 (兩臺主MySQL都需要配置)BASH mysql -uroot –p #登陸MySQL grant replication slave on . to '授權使用者'@'%' identified by '密碼'; #建立從伺服器使用者 show master status; #顯示日誌狀態 ```

從資料庫執行: 切記從MariaDB資料庫不能啟動,修改從MySQL主配置檔案 ```BASH vim /etc/my.cnf

寫入以下內容↓

[mysqld_multi] mysqld=/usr/bin/mysqld_safe mysqladmin=/usr/bin/mysqladmin log=/tmp/multi.log

[mysqld147] #名稱後面加數字,後期啟動用(不應相同) port=3306 #埠號(不應相同) pid-file=/var/lib/mysqla/mysqld.pid #MySQL程序號儲存位置 datadir=/var/lib/mysqla #資料檔案儲存位置 socket=/var/lib/mysqla/mysql.sock #指定登陸套接字 server-id=110 #ID號,同一主從環境不能相同 user=mysql #MySQL登陸使用者名稱

[mysqld148] #名稱後面加數字,後期啟動用(不應相同) port=3307 #埠號(不應相同) pid-file=/var/lib/mysqlb/mysqld.pid #MySQL程序號儲存位置 datadir=/var/lib/mysqlb #資料檔案儲存位置
socket=/var/lib/mysqlb/mysql.sock #指定登陸套接字 server-id=110 #ID號,同一主從環境不能相同 user=mysql #MySQL登陸使用者名稱 初始化資料庫BASH mysql_install_db --datadir=/var/lib/mysqla --user=mysql #初始化資料庫a mysql_install_db --datadir=/var/lib/mysqlb --user=mysql #初始化資料庫b 給相應目錄賦予許可權BASH cd /var/lib/ chown -R mysql:mysql /var/lib/mysqla/ #修改a檔案許可權 chown -R mysql:mysql /var/lib/mysqlb/ #修改b檔案許可權 啟動相應伺服器BASH mysqld_multi --defaults-file=/etc/my.cnf start 147 #開啟147伺服器 mysqld_multi --defaults-file=/etc/my.cnf start 148 #開啟148伺服器 登陸147和148資料庫確定主從關係BASH mysql -P 3306 -S /var/lib/mysqla/mysql.sock #登陸147資料庫 mysql -P 3306 -S /var/lib/mysqlb/mysql.sock #登陸148資料庫

change master to master_host='伺服器IP',master_user='授權賬號',master_password='密碼',master_log_file='日誌檔案',master_log_pos=檔案位置; start slave; #啟用同步 show slave status\G; #檢視同步狀態 ```

MySQL 實現讀寫分離

MyCat是一個開源的,面向企業應用開發的大資料庫叢集,支援事務、ACID、可以替代MySQL的加強版資料庫, 其功能有可以視為MySQL叢集的企業級資料庫,用來替代昂貴的Oracle叢集.融合了記憶體快取技術、NoSQL技術、HDFS大資料的新型SQLServer,結合傳統資料庫和新型分散式資料倉庫的新一代企業級資料庫產品.

```BASH 192.168.1.5 MyCat Server

192.168.1.11 Master 主MariaDB 192.168.1.12 Slave1 從1 192.168.1.13 Slave2 從2 1.在配置讀寫分離前請確保你的主機有四臺,分別在每臺主機安裝MariaDB資料庫,並初始化,需要注意的是MyCat服務端的MariaDB不需要初始化.BASH [root@localhost ~]# yum install -y mariadb mariadb-server [root@localhost ~]# systemctl start mariadb [root@localhost ~]# systemctl enable mariadb 2.在MyCat服務端安裝JDK環境,由於MyCat是使用Java開發的,在這裡我們要先配置一下JDK環境.BASH [root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/ [root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk [root@localhost ~]# vim /etc/profile

------------------------------------------------------------------

JAVA-JDK-PATH

export JAVA_HOME=/usr/local/jdk export JAVA_BIN=/usr/local/jdk/bin export PATH=$PATH:$JAVA_HOME/bin export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar export JAVA_HOME JAVA_BIN PATH CLASSPATH

------------------------------------------------------------------

[root@localhost ~]# source /etc/profile [root@localhost ~]# java -version java version "1.8.0_171" Java(TM) SE Runtime Environment (build 1.8.0_171-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode) 3.此步驟需要進入每一個數據庫建立授權使用者,也就是給MyCAT準備的使用資料庫的授權使用者.BASH [root@localhost ~]# mysql -uroot -p Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database MyCatDB; Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"%" identified by "123123"; Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"localhost" identified by "123123"; Query OK, 0 rows affected (0.00 sec) 4.解壓MyCat工具,並修改MyCat使用者授權檔案,寫入MyCat登陸賬戶,這裡配置檔案有很多選項,我們不需要動,只需要修改以下幾個地方就行.BASH [root@localhost ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz [root@localhost ~]# tar -xzvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/ [root@localhost ~]# vim /usr/local/mycat/conf/server.xml

    <user name="root" defaultAccount="true">            #指定MyCat登陸使用者名稱
            <property name="password">123456</property>     #指定密碼
            <property name="schemas">MyCatDB</property>     #指定同步資料庫
    </user>

    <user name="user">                      #指定MyCat登陸使用者名稱
            <property name="password">123456</property>     #指定密碼
            <property name="schemas">MyCatDB</property>     #指定同步資料庫
            <property name="readOnly">true</property>       #指定只讀
    </user>

5.修改MyCat讀寫分離策略,這裡我們刪除原始檔,並自己建立一個,寫入以下內容.BASH [root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

            <heartbeat>select user()</heartbeat>
            <writeHost host="Master1" url="192.168.1.11:3306" user="MyCat" password="123123">           #配置寫主機
            <readHost host="Slave1" url="192.168.1.12:3306" user="MyCat" password="123123" />           #配置讀主機
    <readHost host="Slave2" url="192.168.1.13:3306" user="MyCat" password="123123" />           #配置讀主機
            </writeHost>
    </dataHost>

6.啟動MyCat若無報錯則說明啟動成功,檢視埠看虛擬埠是否開啟成功.BASH [root@localhost ~]# /usr/local/mycat/bin/mycat start [root@localhost ~]# netstat -an |grep "9066" #虛擬schema管理埠 [root@localhost ~]# netstat -an |grep "8066" #虛擬schema登陸埠 7.在MyCat服務主機登陸資料庫,測試同步效果.SQL [root@localhost ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 9066

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (monitor)

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)]> show @@heartbeat; #RS_CODE為1表示心跳正常 +---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP | +---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | Master1 | mysql | 192.168.1.11 | 3306 | -1 | 0 | idle | 0 | 0,16,16 | 2018-12-21 01:29:43 | false | | Slave1 | mysql | 192.168.1.12 | 3306 | -1 | 0 | idle | 0 | 34,31,31 | 2018-12-21 01:29:43 | false | | Slave2 | mysql | 192.168.1.13 | 3306 | -1 | 0 | idle | 0 | 1,16,16 | 2018-12-21 01:29:43 | false | +---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ 3 rows in set (0.00 sec)

MySQL [(none)]> show @@datasource; #檢視讀寫分離的機器配置情況 +----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ | DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD | +----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ | dn_test | Master1 | mysql | 192.168.1.11 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 | | dn_test | Slave1 | mysql | 192.168.1.12 | 3306 | R | 0 | 0 | 1000 | 0 | 0 | 0 | | dn_test | Slave2 | mysql | 192.168.1.13 | 3306 | R | 0 | 0 | 1000 | 0 | 0 | 0 | +----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ 3 rows in set (0.01 sec)

MySQL [(none)]> 8.登入MyCat代理端,測試讀寫分離服務.BASH [root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 -P 8066

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)

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 [(none)]> MySQL [(none)]> MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | MyCatDB | +----------+ 1 row in set (0.00 sec)

MySQL [(none)]> ```

MySQL 增加監控

1.首先我們先來下載MyCat以及Zookeeper. BASH [root@localhost ~]# wget http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.12/zookeeper-3.4.12.tar.gz [root@localhost ~]# wget http://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz 2.在MyCat服務端安裝JDK環境,由於MyCat是使用Java開發的,在這裡我們要先配置一下JDK環境. ```BASH [root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/ [root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk [root@localhost ~]# vim /etc/profile

------------------------------------------------------------------

JAVA-JDK-PATH

export JAVA_HOME=/usr/local/jdk export JAVA_BIN=/usr/local/jdk/bin export PATH=$PATH:$JAVA_HOME/bin export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar export JAVA_HOME JAVA_BIN PATH CLASSPATH

------------------------------------------------------------------

[root@localhost ~]# source /etc/profile [root@localhost ~]# java -version java version "1.8.0_171" Java(TM) SE Runtime Environment (build 1.8.0_171-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode) 3.配置MariaDB開啟`lower_case_table_names`選項,每臺都要開啟.BASH [root@localhost ~]# yum install -y mariadb mariadb-server [root@localhost ~]# vim /etc/my.cnf

[mysqld] lower_case_table_names = 1

[root@localhost ~]# systemctl restart mariadb 4.安裝zookeeper,其主要用來統計資料.BASH [root@localhost ~]# tar -xzvf zookeeper-3.4.12.tar.gz -C /usr/local/ [root@localhost ~]# cd /usr/local/zookeeper-3.4.12/conf/ [root@localhost ~]# cp zoo_sample.cfg zoo.cfg [root@localhost ~]# vim zoo.cfg

dataDir=/usr/local/... dataLogDir=/usr/local/... 5.執行zookeeperBASH [root@localhost ~]# cd /usr/local/zookeeper-3.4.12/bin/ [root@localhost ~]# ./zkServer.sh start [root@localhost ~]# netstat -ant | grep 2181 tcp 0 0 :::2181 :::* LISTEN

如果出現錯誤:nohup: failed to run command `java’: No such file or directory 可以在zkServer.sh中的首行新增如下程式碼

export JAVA_HOME=/usr/lib/jdk export PATH=$JAVA_HOME/bin:$PATH 6.最後一步,安裝並執行Mycat-WebBASH [root@localhost ~]# tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz -C /usr/local [root@localhost ~]# cd /usr/local/mycat-web [root@localhost ~]# ./start.sh & [root@localhost ~]# netstat -an | grep "8082" tcp 0 0 :::8082 :::* LISTEN

[root@localhost ~]# curl http://localhost:8082/mycat ```