Mysql 主從復(fù)制實戰(zhàn)(學(xué)習(xí)筆記十三)

https://www.cnblogs.com/along21/p/8011596.html

https://blog.csdn.net/qq3401247010/article/details/78754671

一主一從，一主多從薄货，雙主

① iptables -F && setenforce 清空防火墻策略勒庄，關(guān)閉selinux

② 拿兩臺服務(wù)器都使用yum 方式安裝Mysql 服務(wù)垫卤，要求版本一致

③ 分別啟動兩臺服務(wù)器mysql

2囱稽、在主master 主服務(wù)器上

① vim /etc/my.cnf 修改mysql主配置文件陶夜，對master進(jìn)行配置浦译，包括打開二進(jìn)制日志傻丝，指定唯一的servr ID

server-id=1#配置server-id甘有，讓主服務(wù)器有唯一ID號

log-bin=mysql-bin? #打開Mysql日志，日志格式為二進(jìn)制

skip-name-resolve? #關(guān)閉名稱解析葡缰，（非必須）

②?創(chuàng)建并授權(quán)slave mysql 用的復(fù)制帳號

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%'IDENTIFIED BY'along';

分析：在Master的數(shù)據(jù)庫中建立一個備份帳戶：每個slave使用標(biāo)準(zhǔn)的MySQL用戶名和密碼連接master。進(jìn)行復(fù)制操作的用戶會授予REPLICATION SLAVE權(quán)限忱反。

③ 查看主服務(wù)器狀態(tài)

在Master的數(shù)據(jù)庫執(zhí)行show master status泛释，查看主服務(wù)器二進(jìn)制日志狀態(tài)，位置號

3温算、在從slave mysql1上

① 修改主配置文件

vim /etc/my.cnf 打開中繼日志怜校，指定唯一的servr ID，設(shè)置只讀權(quán)限

server-id=2#配置server-id注竿，讓從服務(wù)器有唯一ID號

relay_log = mysql-relay-bin? ? #打開Mysql日志茄茁，日志格式為二進(jìn)制

read_only =1? ? #設(shè)置只讀權(quán)限

log_bin = mysql-bin? ? ? ? #開啟從服務(wù)器二進(jìn)制日志，（非必須）

log_slave_updates =1#使得更新的數(shù)據(jù)寫進(jìn)二進(jìn)制日志中

systemctl start mariadb 開啟服務(wù)

②?啟動從服務(wù)器復(fù)制線程巩割，讓slave連接master裙顽，并開始重做master二進(jìn)制日志中的事件。

MariaDB [(none)]> change master to master_host='192.168.30.107',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000001',

? ? -> master_log_pos=245;

MariaDB [(none)]>? start slave;? # 啟動復(fù)制線程宣谈，就是打開I/O線程和SQL線程愈犹；實現(xiàn)拉主的bin-log到從的relay-log上；再從relay-log寫到數(shù)據(jù)庫內(nèi)存里

③ 查看從服務(wù)器狀態(tài)

可使用SHOW SLAVE STATUS\G查看從服務(wù)器狀態(tài)闻丑，如下所示漩怎，也可用show processlist \G查看當(dāng)前復(fù)制狀態(tài)：

Slave_IO_Running: Yes #IO線程正常運行

Slave_SQL_Running: Yes #SQL線程正常運行

4、測試

① 在主上創(chuàng)建一個along庫

② 從上自動生成along數(shù)據(jù)庫

5嗦嗡、若要繼續(xù)添加新salve勋锤，實現(xiàn)一主多從

假如master?已經(jīng)運行很久了，想對新安裝的slave 進(jìn)行數(shù)據(jù)同步侥祭，甚至它沒有master 的數(shù)據(jù)叁执。

（1）在主master-mysql 上

① 進(jìn)行完全備份

mysqldump --all-databases > /backup/mysql-all-backup-`date+%F-%T`.sql

把備份生成的文件發(fā)給salve-mysql2機器上scp/backup/mysql-all-backup-2017-11-20-22\:04\:06.sql @192.168.30.2:?

② 查看現(xiàn)在的二進(jìn)制文件狀態(tài)

MariaDB [(none)]> show master status;

（2）在從slave-mysql2上

① vim /etc/my.cnf 修改主配置文件，設(shè)為從

② 進(jìn)行master的完全備份恢復(fù)

mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql

systemctl start mariadb 開啟服務(wù)

恢復(fù)完后卑硫，數(shù)據(jù)直接與主完全一致

③?啟動從服務(wù)器復(fù)制線程

MariaDB [(none)]> change master to master_host='192.168.30.107',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000003',

? ? -> master_log_pos=500;

7徒恋、解除主從關(guān)系，恢復(fù)獨立的服務(wù)器

① MariaDB [(none)]> stop slave; 關(guān)閉兩個線程

② vim /etc/my.cnf 刪除3行

relay-log =mysql-relay-log

read-only =1log_slave_updates =1

③ systemctl restart mariadb 重啟服務(wù)

主從+復(fù)制過濾

1欢伏、環(huán)境準(zhǔn)備

機器名稱IP配置服務(wù)角色備注

master-mysql192.168.30.107主數(shù)據(jù)庫二進(jìn)制日志

slave-mysql1192.168.30.7從數(shù)據(jù)庫中繼日志

slave-mysql2192.168.30.2從數(shù)據(jù)庫中繼日志

2入挣、在主master 主服務(wù)器上

① vim /etc/my.cnf? 修改mysql主配置文件，對master進(jìn)行配置硝拧，打開二進(jìn)制日志径筏，指定唯一的servr ID葛假，設(shè)置復(fù)制過濾

server-id=1#配置server-id，讓主服務(wù)器有唯一ID號

log-bin=mysql-bin? #打開Mysql日志滋恬，日志格式為二進(jìn)制

skip-name-resolve? #關(guān)閉名稱解析聊训，（非必須）

binlog_ignore_db=home? #除了home數(shù)據(jù)庫，其他都允許從復(fù)制主的二進(jìn)制文件

#binlog_do_db=along? #僅允許從復(fù)制along數(shù)據(jù)庫的二進(jìn)制文件

systemctl start mariadb 開啟服務(wù)

②?創(chuàng)建并授權(quán)slave mysql 用的復(fù)制帳號

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%'IDENTIFIED BY'along';

分析：在Master的數(shù)據(jù)庫中建立一個備份帳戶：每個slave使用標(biāo)準(zhǔn)的MySQL用戶名和密碼連接master恢氯。進(jìn)行復(fù)制操作的用戶會授予REPLICATION SLAVE權(quán)限带斑。

③ 查看主服務(wù)器狀態(tài)

在Master的數(shù)據(jù)庫執(zhí)行show master status，查看主服務(wù)器二進(jìn)制日志狀態(tài)勋拟，位置號

3勋磕、在從slave mysql1上

① 修改主配置文件

vim /etc/my.cnf 打開中繼日志，指定唯一的servr ID敢靡，設(shè)置只讀權(quán)限

server-id=2#配置server-id挂滓，讓從服務(wù)器有唯一ID號

relay_log = mysql-relay-bin? ? #打開Mysql日志，日志格式為二進(jìn)制

read_only =1? ? #設(shè)置只讀權(quán)限

log_bin = mysql-bin? ? ? ? #開啟從服務(wù)器二進(jìn)制日志啸胧，（必須）

log_slave_updates =1#使得更新的數(shù)據(jù)寫進(jìn)二進(jìn)制日志中

systemctl start mariadb 開啟服務(wù)

②?啟動從服務(wù)器復(fù)制線程赶站，讓slave連接master，并開始重做master二進(jìn)制日志中的事件纺念。

MariaDB [(none)]> change master to master_host='192.168.30.107',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000001',

? ? -> master_log_pos=245;

MariaDB [(none)]>? start slave;? # 啟動復(fù)制線程贝椿，就是打開I/O線程和SQL線程；實現(xiàn)拉主的bin-log到從的relay-log上柠辞；再從relay-log寫到數(shù)據(jù)庫內(nèi)存里

③ 查看從服務(wù)器狀態(tài)

可使用SHOW SLAVE STATUS\G查看從服務(wù)器狀態(tài)团秽，如下所示，也可用show processlist \G查看當(dāng)前復(fù)制狀態(tài)：

Slave_IO_Running: Yes #IO線程正常運行

Slave_SQL_Running: Yes #SQL線程正常運行

4叭首、測試主從和復(fù)制過濾

（1）測試主從關(guān)系

在主上創(chuàng)建一個along习勤、home庫；從上自動生成along焙格、home數(shù)據(jù)庫

（2）測試復(fù)制過濾

① 在主上：在along庫中創(chuàng)建一個classes的表图毕；從上自動生成

MariaDB [home]> create table classes (id int not null,name varchar(20));

② 在主上：在home庫中創(chuàng)建一個classes的表；從上沒有生成

MariaDB [home]> create table classes (id int not null,name varchar(20));

slave-mysql1 上眷唉，過濾成功

5予颤、設(shè)置slave-mysql2 為slave-mysql1 的從，且在mysql2 設(shè)置復(fù)制過濾

（1）在slave-mysql1上冬阳，不用怎么設(shè)置

因為上邊主配置文件已經(jīng)開啟了自己的二進(jìn)制文件蛤虐；且slave-mysql1 是從開始就同步master的蔗草，所以授權(quán)命令也同步過了

MariaDB [home]> select user,host from mysql.user; 可以查看自己授權(quán)過的用戶

（2）slave-mysql2 上局雄，可以像上實驗一樣，先給主的完全備份在本機恢復(fù)一下

① 在主上完備

mysqldump --all-databases > /backup/mysql-all-backup-`date+%F-%T`.sqlscp/backup/mysql-all-backup-2017-11-21-11:14:59.sql @192.168.30.2:?

② 進(jìn)行master的完全備份恢復(fù)

mysql -uroot -p < mysql-all-backup-2017-11-20-22\:04\:06.sql

③ 在slave-mysql2 上

vim /etc/my.cnf? 修改主配置文件柔纵，設(shè)為從；且設(shè)置過濾

server-id=3relay-log =mysql-relay-log

read-only =1log-bin = mysql-bin

log_slave_updates =1replicate_do_dB=along? ? #只復(fù)制它的主的along數(shù)據(jù)庫

systemctl start mariadb 開啟服務(wù)

④ mysql 打開數(shù)據(jù)庫饲常，查看數(shù)據(jù)恢復(fù)成功蹲堂；

啟動從服務(wù)器復(fù)制線程，讓slave連接master贝淤，并開始重做master二進(jìn)制日志中的事件柒竞。

MariaDB [(none)]> change master to master_host='192.168.30.107',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000008',

? ? -> master_log_pos=773;

MariaDB [(none)]> start slave;

⑤ MariaDB [(none)]> show slave status \G; 查看，兩個進(jìn)程打開播聪，且只復(fù)制主的along數(shù)據(jù)庫

6朽基、測試主從從和slave-mysql2的復(fù)制過濾

（1）在主上刪除job數(shù)據(jù)庫，master 和slave-mysql1 都刪除成功

MariaDB [home]> drop database job; 刪除job庫

因為slave-mysql2 只同步slave-mysql1 的along庫离陶，所以沒有刪除

（2）在主上的along數(shù)據(jù)庫踩晶，創(chuàng)建一個grade 表，master 和slave-mysql1 都刪除成功

MariaDB [along]> create table grade (id int not null,name varchar(20));

在slave-mysql2 上也自動生成成功

雙主

應(yīng)用：只適合小型公司枕磁，小并發(fā)訪問量，畢竟同時寫入易出錯

1术吝、環(huán)境準(zhǔn)備

機器名稱IP配置服務(wù)角色備注

mysql1192.168.30.107數(shù)據(jù)庫中繼日志计济、二進(jìn)制日志

mysql2192.168.30.7數(shù)據(jù)庫中繼日志、二進(jìn)制日志

2排苍、配置總配置文件沦寂，除了ID號和起始數(shù)，兩邊都是一樣的

vim /etc/my/cnf

server-id=1? ? #mysql1的配置ID為1淘衙，mysql2的ID為2

relay-log =mysql-relay-log

log-bin = mysql-bin

log_slave_updates =1auto_increment_increment=2? #表示自增長字段每次遞增的量传藏，步長

auto_increment_offset=1#表示自增長字段從那個數(shù)開始，mysql1從1開始彤守；mysql2從2開始

systemctl start mariadb

3毯侦、互相設(shè)為對方的從

（1）授權(quán)遠(yuǎn)程登錄的用戶

mysql1、2 上

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%'IDENTIFIED BY'along';

（2）開啟復(fù)制線程

① mysql1 上

MariaDB [(none)]> change master to master_host='192.168.30.7',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000002',

? ? -> master_log_pos=245;

MariaDB [(none)]>? start slave;? # 啟動復(fù)制線程

② mysql2 上

MariaDB [(none)]> change master to master_host='192.168.30.107',

? ? -> master_user='slave',

? ? -> master_password='along',

? ? -> master_log_file='mysql-bin.000002',

? ? -> master_log_pos=245;

MariaDB [(none)]>? start slave;? # 啟動復(fù)制線程

4具垫、測試雙方互為主從的關(guān)系

在mysql1上侈离，刪除test數(shù)據(jù)庫；mysql2 上也自動刪除

在mysql2上筝蚕，創(chuàng)建xiaohei數(shù)據(jù)庫卦碾；mysql2 上也自動生成

5、設(shè)置自增長ID的表

MariaDB [along]> create table home (id int PRIMARY KEY AUTO_INCREMENT,name varchar(20));

① 在mysql1上向表中插入數(shù)據(jù)

MariaDB [along]> insert into home(name) value('mayun'),('mahuateng'),('wangjianlin');

② 在mysql2上向表中插入數(shù)據(jù)

MariaDB [along]> insert into home(name) value('dinglei'),('liyanhong'),('leijun');

場景

公司規(guī)模已經(jīng)形成起宽，用戶數(shù)據(jù)已成為公司的核心命脈洲胖，一次老王一不小心把數(shù)據(jù)庫文件刪除，通過mysqldump備份策略恢復(fù)用了兩個小時坯沪，在這兩小時中绿映，公司業(yè)務(wù)中斷，損失100萬屏箍，老王做出深刻反省绘梦，公司也因此對于數(shù)據(jù)庫的性能和可靠性提出更高要求橘忱。要求對數(shù)據(jù)庫進(jìn)行改造，使其承載力進(jìn)行提升卸奉，故障修復(fù)時間減少钝诚，有沒有能實現(xiàn)的方案呢？

數(shù)據(jù)庫常遇到的問題

一榄棵、性能問題

1凝颇、向上拓展 scale up：針對單臺服務(wù)器，提高服務(wù)器的硬件性能疹鳄，比如：內(nèi)存拧略，cpu等，個體本身容易達(dá)到極限

2瘪弓、向外拓展 scale out：多臺服務(wù)器形成集群垫蛆，共同完成一件事情

二、可用性問題

1腺怯、數(shù)據(jù)庫服務(wù)中斷

2袱饭、誤操作數(shù)據(jù)損壞

3、硬件故障

4呛占、數(shù)據(jù)庫升級測試遭遇bug

5虑乖、黑客攻擊

數(shù)據(jù)庫高可用技術(shù)說明

高可用架構(gòu)對于互聯(lián)網(wǎng)服務(wù)基本是標(biāo)配，無論是應(yīng)用服務(wù)還是數(shù)據(jù)庫服務(wù)都需要做到高可用晾虑。雖然互聯(lián)網(wǎng)服務(wù)號稱7*24小時不間斷服務(wù)疹味，但多多少少有一些時候服務(wù)不可用，比如某些時候網(wǎng)頁打不開帜篇，百度不能搜索或者無法發(fā)微博糙捺，發(fā)微信等。一般而言坠狡，衡量高可用做到什么程度可以通過一年內(nèi)服務(wù)不可用時間作為參考继找，要做到3個9的可用性，一年內(nèi)只能累計有8個小時不可服務(wù)逃沿，而如果要做到5個9的可用性婴渡，則一年內(nèi)只能累計5分鐘服務(wù)中斷。所以雖說每個公司都說自己的服務(wù)是7*24不間斷的凯亮，但實際上能做到5個9的屈指可數(shù)边臼，甚至根本做不到，國內(nèi)互聯(lián)網(wǎng)巨頭BAT(百度假消，阿里巴巴柠并，騰訊)都有因為故障導(dǎo)致的停服問題。對于一個系統(tǒng)而言，可能包含很多模塊臼予，比如前端應(yīng)用鸣戴，緩存，數(shù)據(jù)庫粘拾，搜索窄锅，消息隊列等，每個模塊都需要做到高可用缰雇，才能保證整個系統(tǒng)的高可用入偷。對于數(shù)據(jù)庫服務(wù)而言，高可用可能更復(fù)雜械哟，對用戶的服務(wù)可用疏之，不僅僅是能訪問，還需要有正確性保證暇咆，因此锋爪，對于實現(xiàn)數(shù)據(jù)庫高可用，對互聯(lián)網(wǎng)公司來說極其重要爸业！