搭建MySQL的主從復制和讀寫分離
 

+--------+                          (write)        +--------+                   
| client |                 +---------------------+| master |    
+--------+|                   |                        +--------+|
          |                    |                            |
          |                 |                            |
          |            +--------+      (read)            |
(read)    +--------+| amoeba |+---------------------|-----------+    
(write)      |          +--------+|                        |            |
          |                        |                        |            |
          |                   (read)|                        |            |    
+--------+|                      |    +--------+            |            +--------+
| client |                      +-| slave1 |+---------+----------+| slave2 |
+--------+                      +--------+        (replication)    +--------+



master  mysql:172.17.0.4
slave1  mysql:172.17.0.5
slave2     mysql:172.17.0.6
mysql-proxy(amoeba):172.17.0.8
Test host:172.17.0.7

主從復制：

master mysql
：
安裝mysqlserver和mysql
yum install mysql-server mysql -y

修改配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log-bin = master-bin

啟動mysql
/etc/init.d/mysql start

修改root密碼
mysqladmin -uroot -p password mysql

登陸mysql測試并查看master狀態
mysql -uroot -pmysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22449
Server version: 5.5.47-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     3648 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.03 sec)

創建主從同步賬號
MariaDB [(none)]> grant replication slave on *.* to 'mysqlmaster'@'172.17.0.%' identified by '123456';

slave1 mysql
：
安裝mysqlserver和mysql
yum install mysql-server mysql -y

修改配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2    #id必須唯一
log-bin = slave-bin

啟動mysql
/etc/init.d/mysql start

修改root密碼
mysqladmin -uroot -p password mysql

創建同步文件
MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3648;
MariaDB [(none)]> start slave;

查看是否成功，確保下面兩項為Yes

MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

master mysql
：

在master mysql上創建數據查看slave1 mysql是否同步
MariaDB [(none)]> create database new1;
Query OK, 1 row affected (0.00 sec)

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new1               |
| new2               |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)



slave1 mysql
:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new1               |
| new2               |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)



我們生產環境中會碰到這種情況：備份主機數據，或者添加一臺主機。進行雙主機的結構。
另外就是在線上中途添加更多的從機。

而我們知道，從機上設置 slave 時要指定 master_log_file 和 master_log_pos, 即指定binlog文件和偏移值。
這也就是說，從機是可以從任意位置的 binlog 文件中進行數據的同步。比如：我們將 binlog 文件備份到其它某處放置，某天，數據庫出問題了，需要對某些數據進行數據恢復，這時候從該文件中進行恢復。
添加一個新的從機，可以有兩種方式：從 master 機器復制; 另一種是直接從 slave 復制.

mysql-主從結構添加新的slave

兩種解決辦法
1.copy master

master mysql
：
鎖定數據庫
MariaDB [(none)]>  flush tables with read lock;
Query OK, 0 rows affected (0.04 sec)

查看主機狀態，幾下file position參數
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     3974 |              |                  |
+------------------+----------+--------------+------------------+

備份所有數據庫
mysqldump --all-databases -uroot -pmysql  > backup.sql

拷貝到準備新加的slave主機
scp backup.sql root@172.17.0.6:/root


slave2 mysql
:
安裝mysqlserver和mysql
yum install mysql-server mysql -y

修改配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 3    #id必須唯一
log-bin = slave2-bin

啟動mysql
/etc/init.d/mysql start

修改root密碼
mysqladmin -uroot -p password mysql

導入主服務器scp過來的數據庫
mysql -uroot -pmysql < backup.sql

創建同步文件
MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3974;
MariaDB [(none)]> start slave;

查看是否成功，確保下面兩項為Yes

MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

master mysql
：
創建數據驗證是否添加成功
MariaDB [(none)]> create database new3;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new1               |
| new2                 |    
| new3               | 
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
 
slave2 mysql
:
MariaDB [(none)]> create database new3;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new1               |
| new2                 |    
| new3               | 
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

2 copy slave

復制從庫要步驟：
====================
可以看到，從主庫復制會有段時間鎖表，這段時間會影響主庫的使用。如果我們能直接從從庫進行復制，就不會對主庫產生影響了。但是，從從庫復制要保證的是復制過程中從庫上的數據不會發生變化，所以要先停掉從庫。
1.停止從庫: mysql> stop slave;
2.看當前從庫的狀態。和前面的看主庫狀態一樣。但現在是從從庫復制，所以查看從庫狀態：mysql> show slave status;
記下 Relay_Master_Log_file 和 Exec_Master_Log_Pos, 用處和前面一樣.
3.備份從庫數據.用 mysqldump
4.在新的從庫上還原數據
5.設置新從庫的 slave 參數.change master to master_host = '192.168.3.119',master_port = 3306,master_user = 'repl_user',master_password='root',master_log_file='master-bin.000005',master_log_pos=194244;
可以看到，雖然新從庫是從從庫復制的數據，但實際上 binlog 的 master 還是指向的主庫。
另外，這里將 master_log_file 和 master_log_pos 設置成第 2 步中的 Relay_Master_Log_file 和 Exec_Master_Log_Pos
start slave;


         
mysql的主從復制+讀寫分離  


+--------+                          (write)        +--------+                   
| client |                 +---------------------+| master |    
+--------+|                   |                        +--------+|
          |                    |                            |
          |                 |                            |
          |            +--------+      (read)            |
(read)    +--------+| amoeba |+---------------------|-----------+    
(write)      |          +--------+|                        |            |
          |                        |                        |            |
          |                   (read)|                        |            |    
+--------+|                      |    +--------+            |            +--------+
| client |                      +-| slave1 |+---------+----------+| slave2 |
+--------+                      +--------+        (replication)    +--------+


讀寫分離

Amoeba(變形蟲)項目，專注 分布式數據庫 proxy 開發。座落與Client、DB Server(s)之間。對客戶端透明。具有負載均衡、高可用性、sql過濾、讀寫分離、可路由相關的query到目標數據庫、可并發請求多臺數據庫合并結果。再看下上面的架構圖。

因為amoeba是java編寫的，所以需要先安裝java框架

Amoeba
：

java安裝

下載java
wget http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz

創建java目錄，并解壓
mkdir -p /usr/jdk
tar -xzvf jdk-7u79-linux-x64.tar.gz -C /usr/jdk

配置環境變量，在/etc/profile文件最后添加如下配置
vim /etc/profile

export JAVA_HOME=/usr/jdk/
export CLASSPATH=${JAVA_HOME}/lib
export PATH=${JAVA_HOME}/bin:$PATH

使配置文件生效
source /etc/profile

測試
java -version
java version "1.7.0_79"
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)
證明已經安裝完成

amoeba安裝配置

下載解壓
wget http://ufpr.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
unzip amoeba-mysql-3.0.5-RC-distribution.zip
mv amoeba-mysql-3.0.5-RC /usr/local/

配置
vim amoeba-mysql-3.0.5-RC/conf/amoeba.xml （前段連接文件）

<service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">
            <!-- port -->
            <property name="port">8066</property>               ///////amoeba監聽端口/////////
            
            <!-- bind ipAddress -->
            <!-- 
            <property name="ipAddress">127.0.0.1</property>     ///////amoeba監聽地址/////////
             -->
            
            <property name="connectionFactory">
                <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
                    <property name="sendBufferSize">128</property>
                    <property name="receiveBufferSize">64</property>
                </bean>
            </property>
            
            <property name="authenticateProvider">
                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
                    
                    <property name="user">root</property>            ///Amoeba代理用戶名/////
                    
                    <property name="password">mysql</property>      ///Amoeba代理用戶密碼/////
                    
                    <property name="filter">
                        <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController">
                            <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                        </bean>
                    </property>
                </bean>
            </property>
            
        </service>
.......................
.......................
    <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
        <property name="ruleLoader">
            <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
                <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
                <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
            </bean>
        </property>
        <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
        <property name="LRUMapSize">1500</property>
        <property name="defaultPool">master</property>     ///////默認地址池////////
        
        <property name="writePool">master</property>       //////寫地址池///////////
        <property name="readPool">vipdb</property>          //////讀地址池////////
        <property name="needParse">true</property>
    </queryRouter>




vim amoeba-mysql-3.0.5-RC/conf/dbServers.xml （后端數據庫參數文件）

<dbServer name="abstractServer" abstractive="true">
        <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
            <property name="connectionManager">${defaultManager}</property>
            <property name="sendBufferSize">64</property>
            <property name="receiveBufferSize">128</property>
                
            <!-- mysql port -->
            <property name="port">3306</property>   /////數據庫連接端口///////
            
            <!-- mysql schema -->
            <property name="schema">test</property>  ////默認數據庫//// 
            
            <!-- mysql user -->
            <property name="user">amoeba</property>   ///主從數據庫默認連接用戶////
            
            <property name="password">mysql</property>  ////主從數據庫默認連接用戶密碼////
        </factoryConfig>
............................
............................
</dbServer>

    <dbServer name="master"  parent="abstractServer">                            +---+
        <factoryConfig>                                                                 |                
            <!-- mysql ip -->                                                         |    
            <property name="ipAddress">172.17.0.4</property>                         |
        </factoryConfig>                                                             |
    </dbServer>                                                                         |
                                                                                     |        
    <dbServer name="slave1"  parent="abstractServer">                                 |
        <factoryConfig>                                                                 |
            <!-- mysql ip -->                                                         |+-------//////定義后端數據庫，dbServer可以隨意命名，但自己必須清楚哪個是主，那個是從，而且主服務器命名要和amoeba.xml中writePool相對應，從服務器對應下面
            <property name="ipAddress">172.17.0.5</property>                         |virturl dbServer中的poolNames ip對應各個db///////
        </factoryConfig>                                                              |
    </dbServer>                                                                         |
    <dbServer name="slave2"  parent="abstractServer">                                 |
        <factoryConfig>                                                                 |
            <!-- mysql ip -->                                                         |
            <property name="ipAddress">172.17.0.6</property>                         |
        </factoryConfig>                                                         +---|
    </dbServer>
    <dbServer name="vipdb" virtual="true">                                         +---+
        <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">             |
            <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->     |
            <property name="loadbalance">1</property>                                |
                                                                                     |+-------///第一行的dbserver name命名一定和amoeba.xml的readPool相對應，property name配置從服務器的集合，可以是一個，可以是多個！
            <!-- Separated by commas,such as: server1,server2,server1 -->            |
            <property name="poolNames">slave1,slave2</property>                           |
        </poolConfig>                                                                 |        
    </dbServer>                                                                     +---+

vim amoeba-mysql-3.0.5-RC/jvm.properties （java虛擬機配置）

把原來的這一句JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
改成JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"

在master，slave1，slave2分別授權dbServer.xml中定義的用戶名和密碼

MariaDB [(none)]> grant all on *.* to 'amoeba'@'%' identified by "mysql";
MariaDB [(none)]> grant all on *.* to 'amoeba'@'localhost' identified by "mysql";

啟動amoeba
/usr/local/amoeba-mysql-3.0.5-RC/bin/launcher&

查看進程(如果啟動成功，會看到如下進程)
ps -ef
root       467   337  0 12:51 pts/0    00:00:00 /bin/bash amoeba-mysql-3.0.5-RC/bin/launcher
root       472   467  0 12:51 pts/0    00:00:05 /usr/local/jdk1.7.0_67/bin/java -server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m
root       473   467  0 12:51 pts/0    00:00:00 tail -f /usr/local/amoeba-mysql-3.0.5-RC/logs/console.log

查看監聽端口
netstat -anpl
tcp        0      0 :::8066                     :::*                        LISTEN      472/java                       
tcp        0      0 ::ffff:172.17.0.8:8066      ::ffff:172.17.0.7:39978     ESTABLISHED 472/java            
tcp        0      0 ::ffff:172.17.0.8:46624     ::ffff:172.17.0.5:3306      ESTABLISHED 472/java            
tcp        0      0 ::ffff:172.17.0.8:41286     ::ffff:172.17.0.4:3306      ESTABLISHED 472/java 

Test host測試
測試是拿amoeba.xml中定義的用戶名密碼去測試，千萬別被這地方繞進去

[root@7898596a875b ~]# mysql -uroot -p -h272.17.0.8 -P8066
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1265630343
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MariaDB Server

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

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

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new1               |
| new2               |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

測試讀寫分離
master mysql
:
在master上建表
MariaDB [(none)]> use new1;
MariaDB [new1]> create table student( id int(4) not null AUTO_INCREMENT, name char(20) not null, age tinyint(2) NOT NULL default '0', dept varchar(16) default NULL, primary key(id), KEY index_name (name) );

slave1,slave2
:
slave1
停掉slave
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)

MariaDB [new1]> insert into new1.student(id,name) values(2,'slave');
Query OK, 1 row affected (0.03 sec)

MariaDB [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  2 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)


slave2
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> insert into new1.student(id,name) values(3,'slave');
Query OK, 1 row affected (0.04 sec)

MariaDB [(none)]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  3 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)


master上也插入一條數據
MariaDB [new1]> insert into new1.student(id,name) values(1,'slave');
Query OK, 1 row affected (0.02 sec)

查詢插入
MariaDB [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  1 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)

Test host測試

MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  3 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)

第一次查詢發現只查詢到slave的數據，以為另一個slave讀寫分離沒有做成功，再次查詢發現
amoeba是輪詢著從從數據庫池里讀取數據

MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  2 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)

再插入一條數據，發現查詢不到插入的，還是只能查詢到slave上數據
MySQL [new1]> insert into student(id,name) values(4,'yufyang');
Query OK, 1 row affected (0.02 sec)

MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  3 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)

MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name  | age | dept |
+----+-------+-----+------+
|  2 | slave |   0 | NULL |
+----+-------+-----+------+
1 row in set (0.01 sec)

master mysql
：
MariaDB [new1]> select * from new1.student;
+----+---------+-----+------+
| id | name    | age | dept |
+----+---------+-----+------+
|  1 | slave   |   0 | NULL |
|  4 | yufyang |   0 | NULL |
+----+---------+-----+------+
2 rows in set (0.00 sec)
發現剛才在測試機上通過amoeba插入的數據已經出現在主數據庫的表中

開啟slave
再次查詢slave上的數據
MariaDB [new1]> select * from new1.student;
+----+---------+-----+------+
| id | name    | age | dept |
+----+---------+-----+------+
|  1 | slave   |   0 | NULL |
|  2 | slave   |   0 | NULL |
|  4 | yufyang |   0 | NULL |
+----+---------+-----+------+
發現已經同步master，amoeba測試機上的數據了

名稱欄目：搭建mysql的主從復制和讀寫分離
本文來源：http://www.hntjjpw.com/article12/jsddgc.html

成都網站建設公司_創新互聯，為您提供域名注冊、外貿建站、App開發、品牌網站設計、定制網站、建站公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯