MySQL在企業中的拓撲圖

在企業網站中，后端MySQL資料庫只有一臺時，會有以下問題

• 單點故障，服務不可用
• 無法處理大量的并發資料請求
• 資料丟失—大災難

改造方法

• 增加MySQL資料庫服務器，對資料進行備份，形成主備
• 確保主備MySQL資料庫服務器資料是一樣的
• 主服務器宕機了，備份服務器繼續作業，資料有保障

更好的解決方案

• 通過主從復制的方式來同步資料，再通過讀寫分離來提升資料庫的并發負載能力
  

MySQL主從復制

MySQL主從復制的型別

• 基于陳述句的復制（默認，一般都是用這種型別）

  • 在主服務器上執行的陳述句，從服務器執行同樣的陳述句

• 基于行的復制

  • 把改變的內容復制到從服務器

• 混合型別的復制

  • 一旦發現基于陳述句無法精確復制時，就會采用基于行的復制

主從復制的作業程序

1. 每個事務更新資料完成之前，master在二進制日志記錄這些改變，寫入二進制日志完成后，master通知存盤引擎提交事務，
2. slave將master的Binary log復制到其中繼日志(Relay log)，首先，slave開始一個作業執行緒——I/O執行緒，I/O執行緒在master上打開一個普通連接，開始binlog dump process，binlog dump process從master的二進制日志中讀取事件，如果已經跟上master，它會睡眠并等待master產生新的事件，I/O執行緒將這些事件寫入中繼日志
3. sql slave thread（sql從執行緒）處理該程序最后一步，sql執行緒從中繼日志讀取事件，并重放其中的事件而更新 slave的資料，使其與master中的資料一致，只要該執行緒與I/O執行緒保持一致，中繼日志通常會位于OS的快取中，所以中繼日志的開銷很小
4. 復制程序中有一個重要限制，即復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作

MySQL主從復制步驟

安裝MySQL

我們通過二進制包安裝
tar xf mysql-5.7.25-el7-x86_64.tar.gz  -C /usr/local/
mv mysql-5.7.25-el7-x86_64  mysql

groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql

vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server_id=1
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
prompt=mysql [\\d]>

添加環境變數
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

初始化
mysqld --initialize-insecure  --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

如果報錯可以
yum install -y libaio

使用systemd管理mysql
vim /etc/systemd/system/mysqld.service 

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

systemctl daemon-reload
systemctl start mysqld
netstat -lnt

設定密碼
mysqladmin -uroot  password '123'

登陸驗證
mysql -uroot -p123 

保證三臺服務器都完整安裝MySQL

建立時間同步環境

在主節點上搭建時間同步服務器

安裝NTP（關閉防火墻/selinux）
yum -y install ntp

配置NTP
vim /etc/ntp.conf 

# local clock
server 127.127.1.0		# 本地時間供給源
fudge  127.127.1.0 stratum 8	# 設定時區為+08區

重啟服務并設定為開機啟動
systemctl enable ntpd --now

在從節點上進行時間同步
yum -y install ntpdate
ntpdate 192.168.188.65

將時間誤差縮小到0.00以下就行

配置主從復制

主節點配置

修改主節點配置
vim /etc/my.cnf

server_id = 1		# 服務器編號
log_bin = master-bin	# 開啟二進制日志
log-slave-updates = true	# 開啟從機復制

重啟mysqld
systemctl restart mysqld

進入mysql查看二進制日志是否開啟
show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

給服務器授權
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.188.%' IDENTIFIED BY '123';
重繪權限
FLUSH PRIVILEGES;
獲取日志名及偏移量
show master status;

從節點配置

修改從節點配置
vim /etc/my.cnf
server_id = 2		# 每個服務器編號不一樣
relay-log=relay-log-bin		# 開啟中繼日志
relay-log-index=slave-relay-bin.index	# 開啟索引記錄

重啟mysqld
systemctl restart mysqld

在從服務器上配置同步
change master to master_host='192.168.188.65',master_user='slave',master_password='123',master_log_file='maste
r-bin.000001',master_log_pos=604;	--這個數字就是在主服務器上 show master status; 查出來的

開啟同步（stop slave是停止）
start slave;

備注;  解決UUID重復的問題：
mv /usr/local/mysql/data/auto.cnf /usr/local/mysql/data/auto.cnf.bak

驗證

進入主MySQL
show slave hosts;

新建一個測驗庫
create database ceshi;

CREATE DATABASE IF NOT EXISTS `school`;
-- 創建一個school資料庫
USE `school`;-- 創建學生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '學號',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '學生姓名',
    `gender` varchar(2) NOT NULL DEFAULT '女' COMMENT '性別',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年級編號',
    `phone` VARCHAR(50) NOT NULL COMMENT '聯系電話，允許為空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允許為空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生時間',
    `email` VARCHAR (50) NOT NULL COMMENT '郵箱賬號允許為空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`gender`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','唐嘉','女',2,'13800001234','北京朝陽','1980-5-2','text123@qq.com','123456198001011298'),
(1001,'123456','劉美玲','女',1,'13800002222','廣東深圳','1991-6-4','text111@qq.com','123456199001011191'),
(1002,'123456','潘辰','男',1,'13800002111','四川成都','1992-7-6','text222@qq.com','123456199001011333'),
(1003,'123456','小田','男',2,'13800002333','四川成都','1993-8-8','text333@qq.com','123456199001011555'),
(1004,'123456','胡楊','男',3,'13800002555','浙江杭州','1994-9-10','text444@qq.com','123456199001011777'),
(1005,'123456','貓貓','女',2,'13800002666','日本東京','1995-1-12','text555@qq.com','123456199001011888'),
(1006,'123456','飛飛','男',2,'13800002666','上海浦東','1997-7-12','text666@qq.com','123456199001011666');

在從機上查看

MySQL讀寫分離

MySQL讀寫分離原理

只在主服務器上寫，只在從服務器上讀
基本原理是讓主資料庫處理事務性查詢，而從資料庫處理select查詢

• 實作方式

1. 程式代碼實作
2. 基于中間代理層實作

Atlas的簡介

Atlas是由 Qihoo 360公司Web平臺部基礎架構團隊開發維護的一個基于MySQL協議的資料中間層專案，
主要功能：

1. 讀寫分離

2. 從庫負載均衡

3. IP過濾

4. 自動分表

5. DBA可平滑上下線DB

6. 自動摘除宕機的DB

Atlas相對于官方MySQL-Proxy的優勢

1. 將主流程中所有Lua代碼用C重寫，Lua僅用于管理介面

2. 重寫網路模型、執行緒模型

3. 實作了真正意義上的連接池

4. 優化了鎖機制，性能提高數十倍

安裝并配置Atlas

rpm -ivh https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

補充：
安裝后，它會默認在”/usr/local/mysql-proxy”下生成4個檔案夾，以及需要配置的檔案

bin目錄下放的都是可執行檔案

1. “encrypt”是用來生成MySQL密碼加密的，在配置的時候會用到
2. “mysql-proxy”是MySQL自己的讀寫分離代理
3. “mysql-proxyd”是360的，后面有個“d”，服務的啟動、重啟、停止，都是用他來執行的

conf目錄下放的是組態檔

• “test.cnf”只有一個檔案，用來配置代理的，可以使用vim來編輯

lib目錄下放的是一些包，以及Atlas的依賴
log目錄下放的是日志，如報錯等錯誤資訊的記錄

配置讀寫分離

配置master，slave1，slave2,slave3中開放權限給atlas：
grant all on *.* to mao@'192.168.188.%' identified by '123';
flush privileges;

加密用戶名密碼                                                                                      
進入bin目錄，使用encrypt來對資料庫的密碼進行加密，我的MySQL資料的用戶名是mao，密碼是123，我需要對密碼進行加密
cd /usr/local/mysql-proxy/bin
./encrypt 123
3yb5jEku5h4=	# 加密的字符就是寫在組態檔里的

編輯test.cnf組態檔
vim /usr/local/mysql-proxy/conf/test.cnf

[mysql-proxy]

#帶#號的為非必需的配置專案
admin-username = atlas	# 自定義

#管理介面的密碼
admin-password = 123

#Atlas后端連接的MySQL主庫的IP和埠，可設定多項，用逗號分隔
proxy-backend-addresses = 192.168.188.65:3306

#Atlas后端連接的MySQL從庫的IP和埠，@后面的數字代表權重，用來作負載均衡，若省略則默認為1，可設定多項，用逗號分隔
#proxy-read-only-backend-addresses = 127.0.0.1:3305@1
proxy-read-only-backend-addresses = 192.168.188.66:3306@1,192.168.188.67:3306@1,192.168.188.69:3306@1

#用戶名與其對應的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程式encrypt加密
pwds = mao:3yb5jEku5h4=

# 守護行程
daemon = true

keepalive = true

#作業執行緒數，對Atlas的性能有很大影響，可根據情況適當設定
event-threads = 4

#日志級別，分為message、warning、critical、error、debug五個級別
log-level = message

#日志存放的路徑
log-path = /usr/local/mysql-proxy/log

#sql-log = OFF

#慢日志輸出設定，當設定了該引數時，則日志只輸出執行時間超過sql-log-slow（單位：ms)的日志記錄，不設定該引數則輸出全部日志，
#sql-log-slow = 10

#實體名稱，用于同一臺機器上多個Atlas實體間的區分
#instance = test

#Atlas監聽的作業介面IP和埠
proxy-address = 0.0.0.0:1234

#Atlas監聽的管理介面IP和埠
admin-address = 0.0.0.0:2345

#分表設定，此例中person為庫名，mt為表名，id為分表欄位，3為子表數量，可設定多項，以逗號分隔，若不分表則不需要設定該項
#tables = person.mt.id.3

#默認字符集，設定該項后客戶端不再需要執行SET NAMES陳述句
#charset = utf8

#允許連接Atlas的客戶端的IP，可以是精確IP，也可以是IP段，以逗號分隔，若不設定該項則允許所有IP連接，否則只允許串列中的IP連接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面掛接的LVS的物理網卡的IP(注意不是虛IP)，若有LVS且設定了client-ips則此項必須設定，否則可以不設定
#lvs-ips = 192.168.1.1

配置無誤后，啟動Atlas軟體

/usr/local/mysql-proxy/bin/mysql-proxyd test start

測驗

在Atlas 代理服務器上

yum -y install mysql

mysql -h127.0.0.1 -P2345 -uatlas -p123	# 這里用戶和密碼就是組態檔里面寫的

select * from help;		# 可以查看幫助

select * from backends;		# 查看目前的主從復制

通過代理訪問Mysql
mysql -h192.168.188.68 -P1234  -umao -p123	# 這里用戶名和密碼就是之前授權的賬號

進入資料庫后可以查詢到主MySQL的所有庫和表
select * from school.student;

在MASTER/測驗機上創建一個表

create table student (
    -> id int(4) not null auto_increment comment '學號',
    -> name varchar(30) default null comment '姓名',
    -> gender varchar(2) not null default '女' comment '性別',
    -> primary key (id)
    -> )
    -> engine=innodb default charset=utf8;


分別在兩臺從服務器上停止復制，
stop slave;

在主服務器上，插入資料
insert into student (id,name,gender)
    -> values
    -> (1,'貓貓','女');

在從服務器1上，手動插入一條內容
insert into student (id,name,gender)
values (2,'豬豬','男');

在從服務器2上，手動插入一條內容
insert into student (id,name,gender)
values (3,'牛牛','男');

在從服務器3上，手動插入一條內容
insert into student (id,name,gender)
    -> values (4,'小宋','女');

測驗讀操作

在測驗機上第一次查詢結果	`
在測驗機上第二次查詢結果
在測驗機上第三次查詢結果
在Atlas主機上插入一條陳述句：
insert into student (id,name,gender)
values (5,'小田','男');


在Atlas上查詢不到，最終只有在MASTER上才能看到這條陳述句內容，說明寫操作只能在Master上，
由此驗證：已經實作了MYSQL讀寫分離，目前所有的寫操作都在MASTER主服務器上，用來避免資料的不同步，所有的讀操作都分攤給了slave從服務器，用來分擔資料庫壓力，