搭建mysql的主從復(fù)制和讀寫分離
+--------+ (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
主從復(fù)制：
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
啟動(dòng)mysql
/etc/init.d/mysql start
修改root密碼
mysqladmin -uroot -p password mysql
登陸mysql測(cè)試并查看master狀態(tài)
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)
創(chuàng)建主從同步賬號(hào)
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
啟動(dòng)mysql
/etc/init.d/mysql start
修改root密碼
mysqladmin -uroot -p password mysql
創(chuàng)建同步文件
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;
查看是否成功，確保下面兩項(xiàng)為Yes
MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master mysql
：
在master mysql上創(chuàng)建數(shù)據(jù)查看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)
我們生產(chǎn)環(huán)境中會(huì)碰到這種情況：備份主機(jī)數(shù)據(jù)，或者添加一臺(tái)主機(jī)。進(jìn)行雙主機(jī)的結(jié)構(gòu)。
另外就是在線上中途添加更多的從機(jī)。
而我們知道，從機(jī)上設(shè)置 slave 時(shí)要指定 master_log_file 和 master_log_pos, 即指定binlog文件和偏移值。
這也就是說(shuō)，從機(jī)是可以從任意位置的 binlog 文件中進(jìn)行數(shù)據(jù)的同步。比如：我們將 binlog 文件備份到其它某處放置，某天，數(shù)據(jù)庫(kù)出問(wèn)題了，需要對(duì)某些數(shù)據(jù)進(jìn)行數(shù)據(jù)恢復(fù)，這時(shí)候從該文件中進(jìn)行恢復(fù)。
添加一個(gè)新的從機(jī)，可以有兩種方式：從 master 機(jī)器復(fù)制; 另一種是直接從 slave 復(fù)制.
mysql-主從結(jié)構(gòu)添加新的slave
兩種解決辦法
1.copy master
master mysql
：
鎖定數(shù)據(jù)庫(kù)
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.04 sec)
查看主機(jī)狀態(tài)，幾下file position參數(shù)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 3974 | | |
+------------------+----------+--------------+------------------+
備份所有數(shù)據(jù)庫(kù)
mysqldump --all-databases -uroot -pmysql > backup.sql
拷貝到準(zhǔn)備新加的slave主機(jī)
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
啟動(dòng)mysql
/etc/init.d/mysql start
修改root密碼
mysqladmin -uroot -p password mysql
導(dǎo)入主服務(wù)器scp過(guò)來(lái)的數(shù)據(jù)庫(kù)
mysql -uroot -pmysql < backup.sql
創(chuàng)建同步文件
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;
查看是否成功，確保下面兩項(xiàng)為Yes
MariaDB [(none)]> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master mysql
：
創(chuàng)建數(shù)據(jù)驗(yàn)證是否添加成功
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
復(fù)制從庫(kù)要步驟：
====================
可以看到，從主庫(kù)復(fù)制會(huì)有段時(shí)間鎖表，這段時(shí)間會(huì)影響主庫(kù)的使用。如果我們能直接從從庫(kù)進(jìn)行復(fù)制，就不會(huì)對(duì)主庫(kù)產(chǎn)生影響了。但是，從從庫(kù)復(fù)制要保證的是復(fù)制過(guò)程中從庫(kù)上的數(shù)據(jù)不會(huì)發(fā)生變化，所以要先停掉從庫(kù)。
1.停止從庫(kù): mysql> stop slave;
2.看當(dāng)前從庫(kù)的狀態(tài)。和前面的看主庫(kù)狀態(tài)一樣。但現(xiàn)在是從從庫(kù)復(fù)制，所以查看從庫(kù)狀態(tài)：mysql> show slave status;
記下 Relay_Master_Log_file 和 Exec_Master_Log_Pos, 用處和前面一樣.
3.備份從庫(kù)數(shù)據(jù).用 mysqldump
4.在新的從庫(kù)上還原數(shù)據(jù)
5.設(shè)置新從庫(kù)的 slave 參數(shù).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;
可以看到，雖然新從庫(kù)是從從庫(kù)復(fù)制的數(shù)據(jù)，但實(shí)際上 binlog 的 master 還是指向的主庫(kù)。
另外，這里將 master_log_file 和 master_log_pos 設(shè)置成第 2 步中的 Relay_Master_Log_file 和 Exec_Master_Log_Pos
start slave;
mysql的主從復(fù)制+讀寫分離
+--------+ (write) +--------+
| client | +---------------------+| master |
+--------+| | +--------+|
| | |
| | |
| +--------+ (read) |
(read) +--------+| amoeba |+---------------------|-----------+
(write) | +--------+| | |
| | | |
| (read)| | |
+--------+| | +--------+ | +--------+
| client | +-| slave1 |+---------+----------+| slave2 |
+--------+ +--------+ (replication) +--------+
讀寫分離
Amoeba(變形蟲)項(xiàng)目，專注 分布式數(shù)據(jù)庫(kù) proxy 開發(fā)。座落與Client、DB Server(s)之間。對(duì)客戶端透明。具有負(fù)載均衡、高可用性、sql過(guò)濾、讀寫分離、可路由相關(guān)的query到目標(biāo)數(shù)據(jù)庫(kù)、可并發(fā)請(qǐng)求多臺(tái)數(shù)據(jù)庫(kù)合并結(jié)果。再看下上面的架構(gòu)圖。
因?yàn)閍moeba是java編寫的，所以需要先安裝java框架
Amoeba
：
java安裝
下載java
wget http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz
創(chuàng)建java目錄，并解壓
mkdir -p /usr/jdk
tar -xzvf jdk-7u79-linux-x64.tar.gz -C /usr/jdk
配置環(huán)境變量，在/etc/profile文件最后添加如下配置
vim /etc/profile
export JAVA_HOME=/usr/jdk/
export CLASSPATH=${JAVA_HOME}/lib
export PATH=${JAVA_HOME}/bin:$PATH
使配置文件生效
source /etc/profile
測(cè)試
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)
證明已經(jīng)安裝完成
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 （前段連接文件）
8066 ///////amoeba監(jiān)聽端口/////////
128
64
root ///Amoeba代理用戶名/////
mysql ///Amoeba代理用戶密碼/////
${amoeba.home}/conf/access_list.conf
.......................
.......................
${amoeba.home}/conf/rule.xml
${amoeba.home}/conf/ruleFunctionMap.xml
${amoeba.home}/conf/functionMap.xml
1500
master ///////默認(rèn)地址池////////
master //////寫地址池///////////
vipdb //////讀地址池////////
true
vim amoeba-mysql-3.0.5-RC/conf/dbServers.xml （后端數(shù)據(jù)庫(kù)參數(shù)文件）
${defaultManager}
64
128
3306 /////數(shù)據(jù)庫(kù)連接端口///////
test ////默認(rèn)數(shù)據(jù)庫(kù)////
amoeba ///主從數(shù)據(jù)庫(kù)默認(rèn)連接用戶////
mysql ////主從數(shù)據(jù)庫(kù)默認(rèn)連接用戶密碼////
............................
............................
+---+
|
|
172.17.0.4 |
|
|
|
|
|
|+-------//////定義后端數(shù)據(jù)庫(kù)，dbServer可以隨意命名，但自己必須清楚哪個(gè)是主，那個(gè)是從，而且主服務(wù)器命名要和amoeba.xml中writePool相對(duì)應(yīng)，從服務(wù)器對(duì)應(yīng)下面
172.17.0.5 |virturl dbServer中的poolNames ip對(duì)應(yīng)各個(gè)db///////
|
|
|
|
|
172.17.0.6 |
+---|
+---+
|
|
1 |
|+-------///第一行的dbserver name命名一定和amoeba.xml的readPool相對(duì)應(yīng)，property name配置從服務(wù)器的集合，可以是一個(gè)，可以是多個(gè)！
|
slave1,slave2 |
|
+---+
vim amoeba-mysql-3.0.5-RC/jvm.properties （java虛擬機(jī)配置）
把原來(lái)的這一句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分別授權(quán)dbServer.xml中定義的用戶名和密碼
MariaDB [(none)]> grant all on *.* to 'amoeba'@'%' identified by "mysql";
MariaDB [(none)]> grant all on *.* to 'amoeba'@'localhost' identified by "mysql";
啟動(dòng)amoeba
/usr/local/amoeba-mysql-3.0.5-RC/bin/launcher&
查看進(jìn)程(如果啟動(dòng)成功，會(huì)看到如下進(jìn)程)
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
查看監(jiān)聽端口
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測(cè)試
測(cè)試是拿amoeba.xml中定義的用戶名密碼去測(cè)試，千萬(wàn)別被這地方繞進(jìn)去
[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)
測(cè)試讀寫分離
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上也插入一條數(shù)據(jù)
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測(cè)試
MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name | age | dept |
+----+-------+-----+------+
| 3 | slave | 0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)
第一次查詢發(fā)現(xiàn)只查詢到slave的數(shù)據(jù)，以為另一個(gè)slave讀寫分離沒(méi)有做成功，再次查詢發(fā)現(xiàn)
amoeba是輪詢著從從數(shù)據(jù)庫(kù)池里讀取數(shù)據(jù)
MySQL [new1]> select * from new1.student;
+----+-------+-----+------+
| id | name | age | dept |
+----+-------+-----+------+
| 2 | slave | 0 | NULL |
+----+-------+-----+------+
1 row in set (0.00 sec)
再插入一條數(shù)據(jù)，發(fā)現(xiàn)查詢不到插入的，還是只能查詢到slave上數(shù)據(jù)
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)
發(fā)現(xiàn)剛才在測(cè)試機(jī)上通過(guò)amoeba插入的數(shù)據(jù)已經(jīng)出現(xiàn)在主數(shù)據(jù)庫(kù)的表中
開啟slave
再次查詢slave上的數(shù)據(jù)
MariaDB [new1]> select * from new1.student;
+----+---------+-----+------+
| id | name | age | dept |
+----+---------+-----+------+
| 1 | slave | 0 | NULL |
| 2 | slave | 0 | NULL |
| 4 | yufyang | 0 | NULL |
+----+---------+-----+------+
發(fā)現(xiàn)已經(jīng)同步master，amoeba測(cè)試機(jī)上的數(shù)據(jù)了
成都創(chuàng)新互聯(lián)公司專注于企業(yè)全網(wǎng)營(yíng)銷推廣、網(wǎng)站重做改版、遂川網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5網(wǎng)站設(shè)計(jì)、購(gòu)物商城網(wǎng)站建設(shè)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為遂川等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
網(wǎng)站題目：搭建mysql的主從復(fù)制和讀寫分離-創(chuàng)新互聯(lián)
本文地址：http://weahome.cn/article/docgdo.html
重慶分公司
重慶分公司
