mysql複製(高可用架構方案的基礎)

標籤：pre   flush   表示   ble   images   sed   gtid   socket   read   

mysql複製:把一個資料庫執行個體上所有改變複製到另外一個資料庫程式庫伺服器執行個體的過程
特點:
1.沒有改變就無所謂複製 ;改變是複製的根本與資料來源
2.所有的改變:是指可以複製全部改變,也可以複製部分改變 可以在全部改變中根據業務需求選擇部分庫和部分表的複製
複製的情境：
1.資料庫容災
2.需求：建立一個從資料服務器，做資料的測試和分析
3.負載平衡
4.複製時高可用架構方案的基礎

mysql高可用架構特點
1.資料庫故障的檢測與排除
2.主從資料庫的切換
3.資料的備份和保護

mysql高可用架構常用方案
1.雙主 自動/手工 切換
2.Altas,opneproxy讀寫分離方案
3.MMM架構
4.MHA架構
5.DRDB高可用架構
6.mycat高可用分區架構
7.mysql NDB cluster叢集架構
8.percona xtradb cluster(pxc)叢集架構
9.mysql fabric高可用架構

mysql複製配置
同步複製步驟
1. 配置master伺服器
2. 配置slave執行個體
3. 配置slave的複製串連到master

1.配置master服務
log-bin=/var/lib/mysql/binlog/mysql-bin.log (開啟二進位檔案及二進位檔案的位置,注意是 是檔案 不是檔案夾 mysql-bin是二進位日誌的開頭格式 ,注意binlog目錄對mysql使用者的許可權)
server-id=108 (伺服器唯一標識,必須設定,如果沒設定，可通過 進入mysql 後 show binlog events in ‘mysql-bin.000001‘; 查看)

mysql> show binlog events in ‘mysql-bin.000001‘;+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.35-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+

設定完重啟mysql服務

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       143 || mysql-bin.000002 |       120 |+------------------+-----------+mysql> show binlog events in  ‘mysql-bin.000002‘;+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000002 |   4 | Format_desc |       108 |         120 | Server ver: 5.6.35-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+

master的/etc/my.cnf 樣本

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-bin=/var/lib/mysql/binlog/mysql-bin.logserver-id=108# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=true

2.從庫的配置 my.cnf裡

 server-id=251

開啟從庫進程
start slave ;
如果有警告
show warnings;

3.配置slave串連到master的複製

在主庫上建立一個複製許可權的帳號

grant replication slave on  *.* to [email protected]‘%‘ identified  by ‘rep123456‘;flush privileges;

在從伺服器mysql裡執行

change master   to master_host=‘192.168.1.250‘,     master_port=3306,     master_user=‘rep‘,     master_password=‘rep123456‘,     master_log_file=‘mysql-bin.000003‘,                    master_log_pos=106;

master_log_file 表示從哪個二進位檔案開始,master_log_pos 表示從哪個位置開始

比如我做測試的主庫是 192.168.1.250 從庫是192.168.1.251

查看從庫狀態

show slave status\G

 

  Slave_IO_State:                  Master_Host: 192.168.1.250                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 106               Relay_Log_File: mysqld-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000003             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: 106              Relay_Log_Space: 106              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:

View Code

 

主要參數

Slave_IO_Running: No
Slave_SQL_Running: No

 

開啟從庫進程
start slave ;

show slave status\G

這時 ：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

表示配置成功了，在主庫相應表裡插入資料,從庫也會有！

 

mysql複製(高可用架構方案的基礎)