Mysql主從同步

標籤：mysql主從同步

什麼是Mysql 主從同步 ？

從庫同步主庫上的資料，（讓其他資料庫伺服器自動同步當前正在被訪問的資料服務器上的資料）

主從同步的結構類型

1主------>1從 （常用） 一台主伺服器（負責業務更新）一台從伺服器

1主------->多從（常用）一台主伺服器（負責業務更新）多台從伺服器

主------->從-------->從 一台主伺服器（負責業務更新）一台從伺服器 （同時作為另一台的主伺服器）一台從伺服器

主<==>主(不單獨用，一般和第三方軟體一起做高可用叢集或負載平衡) 伺服器相互為主從伺服器

環境要求

1.mysql伺服器版本需一樣

2.網路能互聯

3.防火牆和selinux需要允許存取mysql 實驗環境可以將selinux 和防火牆全部關閉

4.在配置成主從同步結構之前，一定要讓從庫有和主庫一樣的資料（如果兩個伺服器資料結構不相同在主伺服器上能執行的sql在從伺服器上，在從伺服器上執行出錯，sql線程就會NO了，不再同步）

主要資料庫伺服器

1.使用者授權

grant replication slave on . to 授權使用者@"從伺服器ip地址" identified by "密碼"

2.啟用binlog日誌

vim /etc/my.cnf

[mysqld]

server_id=0-255的值主從不要重複

log-bin=/指定binlog日誌的檔案夾/記錄檔名 mysql服務使用者需要有指定檔案夾的讀寫執行許可權 可以不指定預設是在資料庫目錄下/var/lib/mysql

binlog-format="mixed" 使用混合模式記錄日誌（混合模式記錄改變結果和sql命令）

3.查看正在使用的binlog日誌

show master status；

從資料庫伺服器

1.驗證主庫授權

#mysql -h主庫地址 -u授權使用者 -p授權密碼;

在從伺服器命令列能登陸說明授權成功

2.指定server_id

vim /etc/my.cnf

[mysqld]

server_id=52

:wq

show slave status; 查看自己是不是從庫資訊

因為還沒有指定過查不到資訊

3.指定主庫資訊

mysql> change master to
-> master_user="repluser", // 主伺服器授權使用者
-> master_host="192.168.4.51", //主伺服器ip地址
-> master_password="123456", //授權密碼
-> master_log_file="A1-bin.000001", //主伺服器正在使用的binlog日誌 可以在主伺服器上show master status 查看到正在使用的日誌以及已經記錄的位移量
-> master_log_pos=452;//binlog日誌已經記錄的位移量
Query OK, 0 rows affected, 2 warnings (0.23 sec)

4.查看配置

show slave status\G;
1. row
Slave_IO_State:
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: A1-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: A2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: A1-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 452
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5.器用salve進程

mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

再次查看配置的時候以下兩個參數的值是YES

         Slave_IO_Running: YES   //IO線程            將主伺服器上的binlog日誌（由主伺服器上的  Binlog Dump 推送新的日誌記錄）的sql命令記錄到原生中繼記錄檔裡 /var/lib/mysql/主機名稱-relay-bin.編號        Slave_SQL_Running: YES //SQL線程

驗證配置

在主伺服器上建立資料庫，從伺服器上就有主伺服器上建立的資料庫

常見錯誤原因

I/O線程起不來

配置參數出錯將

防火牆

selinux

如果有錯下列參數會有記錄錯誤原因

show slave status\G;

           Last_IO_Errno: 0           Last_IO_Error:           Last_SQL_Errno: 0           Last_SQL_Error:

SQL線程起不來

主從配置之前資料庫不統一，導致衝突

解決辦法 stop slave ;

先將從伺服器上的資料庫恢複成主伺服器上統一的資料庫

再啟動 start slave;

一主多從就是再加一個從伺服器

在主伺服器上再建立一個從伺服器授權使用者

再配置一台從資料庫伺服器

主要資料庫伺服器部分設定檔參數

[mysqld]

binlog_do_db=庫名列表 //允許從同步的庫

binlog_ignore_db=庫名列表 //不允許從同步的庫

兩個參數都不寫是同步所有的庫

從資料庫伺服器部分設定檔參數

[mysqld]

binlog_do_db=庫名列表 //只從主同步的庫

binlog_ignore_db=庫名列表 //不從主同步的庫

兩個參數都不寫是同步所有的庫

log_slave_updates //級聯複製 執行中繼日誌中的sql命令時記錄到binlog日誌中 預設binlog不記錄執行中繼日誌的sql（該參數需要在做主從從結構時中間的從伺服器上配置）

Mysql主從同步