一、簡介

MySQL內(nèi)建的復(fù)制功能是構(gòu)建大型，高性能應(yīng)用程序的基礎(chǔ) 通過將MySQL的某一臺(tái)主機(jī)（master）的數(shù)據(jù)復(fù)制到其他主機(jī)（slaves）上，并重新執(zhí)行一遍來執(zhí)行 復(fù)制過程中一臺(tái)服務(wù)器充當(dāng)主服務(wù)器，而其他一個(gè)或多個(gè)其他服務(wù)器充當(dāng)從服務(wù)器

主從復(fù)制原理

主要基于MySQL二進(jìn)制日志 主要包括三個(gè)線程（2個(gè)I/O線程，1個(gè)SQL線程）

LNMP（centos7，mysql5.6）

vmware workstation pro配置了3個(gè)虛擬機(jī)，均安裝了LNMP環(huán)境：

• master： 192.168.0.105       
• slave：192.168.0.106 、192.168.0.107

二、原理

（1）主數(shù)據(jù)庫master進(jìn)行增刪改操作后，相應(yīng)操作記錄的語句（比如 create database test）會(huì)記錄到binlog日志文件中（binlog日志文件一般和數(shù)據(jù)庫data文件夾在一起）。

（2）從數(shù)據(jù)庫Slave會(huì)請求主數(shù)據(jù)庫的binlog日志文件，拷貝到Slave的中繼日志中，然后在自己的從數(shù)據(jù)庫上自動(dòng)執(zhí)行相同的操作語句，進(jìn)而實(shí)現(xiàn)主從的同步。

注：這里，我們所需要配置的只是主從環(huán)境以及開啟binlog日志，其他的mysql會(huì)自動(dòng)完成。

二、配置主從同步

2.1環(huán)境準(zhǔn)備

克隆三臺(tái)沒有下過mysql的虛擬機(jī)（或恢復(fù)快照）

ip為 

• master   192.168.1.112 
• slave1     192.168.1.113
•  slave2     192.168.1.114

下載mysql：yum install mysql-server -y

不會(huì)下載或者下載報(bào)錯(cuò)：MySQL數(shù)據(jù)庫安裝---離線下載

2.2主從配置

2.2.1【master】（主服務(wù)器）

1.）選舉112為主服務(wù)器

vim /etc/my.cnf
 
[client]
user=root
password=ABc1234
 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
log-error=/var/log/mysqld. log
pid-file=/var/run/mysqld/mysqld.pid
 
server_id=11 #可以是任意正整數(shù)，一般比slave大

然后開啟MySQL服務(wù)

2.）在數(shù)據(jù)庫中創(chuàng)建用戶，以及授權(quán)，修改master庫的密碼加密方式

• repl 你所創(chuàng)建的用戶名
• MySQL@123 用戶密碼

mysql> create user repl@'%'identified with mysql_native_password by 'MySQL@123'
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

查看主配置狀態(tài)：show master status;

3 ）關(guān)閉防火墻，禁用SElinux 

[root@openeuler-1 ~]  systemctl stop firewalld

[root@openeuler-1 ~]  setenforce 0
setenforce: SELinux is disabled  #已被禁用

[root@openeuler-1 ~]  getenforce #查看SElinux狀態(tài)
Disabled

2.2.2【slave】從服務(wù)器

【slave1】和【slave2】

1.）配置主配置文件，添加一個(gè)server_id（唯一標(biāo)識(shí)）

vim /etc/my.cnf

• slave1的   113

• slave2的   114

配置后開啟/重啟mysql服務(wù)：systemctl start mysql.service/systemctl restart mysql.service

2.）在從mysql上進(jìn)行部署，連接主庫（因?yàn)檫B接的是同一個(gè)主庫，兩個(gè)slave操作一樣）

ps：如果start slave后，要重新連接主庫（change maste to), 必須先stop slave

• 從庫指向主庫創(chuàng)建的host，用戶名，密碼，二進(jìn)制文件，以及節(jié)點(diǎn)

mysql> change master to
    -> master_host='192.168.1.112',
    -> master_user='repl',
    -> master_password='MySQL@123',
    -> master_log_file='binlog.000001',
    -> master_log_pos=980;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

3.）開啟主從

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.) 關(guān)閉防火墻，禁用SElinux  

[root@openeuler-1 ~]  systemctl stop firewalld

[root@openeuler-1 ~]  setenforce 0
setenforce: SELinux is disabled  #已被禁用

[root@openeuler-1 ~]  getenforce #查看SElinux狀態(tài)
Disabled

5.) 查看是否成功并測試

三、基于binlog的主從同步

3.1【主服務(wù)器】

1.) 開啟binlog

在id后面添加兩行

[root@openeuler-1 Mysql]# vim /etc/my.cnf

server_id=11

gtid_mode=ON
enforce-gtid-consistency=ON

[root@openeuler-1 Mysql]# systemctl restart mysqld.service

3.2【從服務(wù)器】

1.）暫停slave，進(jìn)入配置文件添加兩行，重啟服務(wù)（重啟更改后的配置才會(huì)生效）

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> \q
Bye

[root@openeuler-1 Mysql]# vim /etc/my.cnf

server_id=113

gtid_mode=ON
enforce-gtid-consistency=ON

[root@openeuler-1 Mysql]# systemctl restart mysqld.service 

2.）測試gid是否開啟

mysql> show variables like '%gtid%';

3.） 暫停slave，連接主庫

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to
    -> master_host='192.168.1.112',
    -> master_user='repl',
    -> master_password='MySQL@123',
    -> master_auto_position=1;        #使用 GTID 自動(dòng)定位。
Query OK, 0 rows affected, 7 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

3.3 GTID 從庫誤寫入操作處理

從庫一般是不需要寫入數(shù)據(jù)的，如果不小心寫入一般會(huì)報(bào)錯(cuò)

Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'

Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3                #說明gid3報(bào)錯(cuò)
Executed_Gtid_Set:  71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1

解決方法：注入空事務(wù)

暫停salve：
stop slave;

#3是報(bào)錯(cuò)的ID號
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit; 
set gtid_next='AUTOMATIC';
    
這里的xxxxx:N 也就是你的slave sql thread報(bào)錯(cuò)的GTID，或者說是你想要跳過的GTID。

最好的解決方案：重新構(gòu)建主從環(huán)境

3.4  IO進(jìn)程錯(cuò)誤

發(fā)現(xiàn)IO進(jìn)程錯(cuò)誤，檢查日志，排除故障：

tail localhost.localdomain.err

常見錯(cuò)誤：各個(gè)復(fù)制的虛擬機(jī)server_id不能相同

解決方法：修改id，重啟mysql

四、延時(shí)同步

4.1簡介

是我們?nèi)藶榕渲玫囊环N特殊同步，從庫和主庫同步延時(shí)N小時(shí)

為什么要有延時(shí)同步？

數(shù)據(jù)庫有兩種故障：

• 物理損壞：可以用主從復(fù)制解決。主庫rm刪除，直接將應(yīng)用切換到從庫
• 邏輯損壞：普通主從同步無法解決。eg：主庫drop database ns，從庫也沒了

4.2配置延時(shí)同步

主庫加一個(gè)參數(shù)即可，其他不變

 change master to master_delay=300;  #延時(shí)300s

4.3故障模擬恢復(fù)

1.）主庫數(shù)據(jù)操作

mysql> stop slave;
Query Ok, 0 rows affected, l warning(0.00 sec)

mysal> change master to master_delay=300;  #延時(shí)300s
Query OK, 0 rows affected, 2 warnings (0.0l sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysq1> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

2.）停止從庫SQL線程

stop slave sql_thread;

3.) 找relaylog的截取點(diǎn)和終點(diǎn)

起點(diǎn)：

Relay_Log_File:db01-relay-bin.000002
Relay_Log_Pos:482

終點(diǎn)：

4.)  從庫恢復(fù)relaylog

source /tmp/relay.sq1

5.)  從庫身份解除

db0l [relay]>stop slave ;
db0l [relay]>reset slave all

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論