Mysql資料庫主從複製相關介紹

公司使用master-slave架構，就具體學習了下，這裡記錄下相關內容。

Replication的好處

讀寫分離：主要資料庫負責寫和update資料，從資料庫負責讀操作
資料安全：由於資料複製到從庫，並且可以對從庫的複製進程進行停止操作，所以可以在從庫上進行備份服務而不需要對主伺服器進行中斷操作
可分析：線上資料可以在master資料庫上建立，然後在slave資料庫上進行分析，且不會對master資料庫造成效能影響
長資料：如果一個分公司需要一份主要資料的copy，可以使用複製來建立一份本地備份。
Replication類型：

Statement Based Replication （SBR）：二進位日誌儲存為SQL語句，通過SQL在slave上執行達到同步效果
Row Based Replication（RBR）：只複製改變ROW
Mixed Based Replication（MBR）：混合型
NOTICE: 此部落格使用SBR（預設日誌格式）方式進行主從複製。

主備設定注意事項：

在master上，必須使用二進位日誌，且設定唯一ID號。
在每個slave伺服器上，為了與maste伺服器進行串連，必須設定唯一ID號
（可選）建立一個獨立使用者，用來認證主伺服器，並通過訪問二進位日誌來達到複製
在建立資料快照或啟動複製進程前，需要記錄master伺服器的二進位日誌位置。此資訊在後期slave啟動時需要
如果master伺服器上已經存在資料，且需要同步，需要建立資料快照。（可通過mysqldump或直接拷貝檔案）
需要配置slave伺服器，填寫master伺服器相關資訊。
一些選擇：

如果是新的master和slaves，則只需要設定檔
如果資料庫已經在運行，則需要先進行資料同步
如果增加slaves到一個已經存在主備的環境，只需要設定slaves（通過對slave資料拷貝到新slave方式，達到資料同步效果）
mysql資料庫相關操作

master伺服器配置

1. my.cnf配置

修改my.cnf檔案，啟用二進位日誌記錄，且設定server-id為唯一數字，不要跟從slave重複

[mysqld]
log-bin=mysql-bin
server-id=1
NOTICE：
為了良好的一致性，可以設定innodb_flush_log_at_trx_commit=1和sync_binlog=1，但是這樣會存在一定的效能影響。

2. 為主從建立專用賬戶

每個從伺服器與主伺服器串連，都是使用mysql使用者和密碼，為安全起見，建立專門的主從帳號。
在master伺服器上建立使用者，複製專用使用者需要具有REPLICATION SLAVE許可權。

mysql>create user 'username'@'address' identified by 'slavepass';
mysql>grant replication slave on *.* to 'username'@'address';
3. 擷取mater的二進位日誌位置

擷取master的當前二進位日誌位置，使slave從當前位置開始複製

串連主要資料庫，執行flush tables with read lock命令
mysql> FLUSH TABLES WITH READ LOCK;
另開啟一個回話，使用show master status確定當前二進位日誌的檔案名稱和位置（使用同一會話也可以）

mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
4. slave擷取同步點

如果資料庫以前存在資料，則需要先用mysqldump或資料快照進行資料庫複寫

mysqldump方式建立資料快照

shell> mysqldump –all-databases –master-data > dbdump.db
使用–master-data會自動鎖定表，並且會匯出位置，開啟匯出檔案，可以看到裡面有CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3254350;這樣的位置語句，就不需要執行手動查看master的二進位日誌位置了。

如果不使用–master-data，則需要另開啟一個終端，輸入FLUSH TABLES WITH READ LOCK，進行鎖表操作，mysqldump結束後輸入

UNLOCK TABLES
data目錄拷貝方式

當資料量大的時候，使用mysqldump效率比較低，推薦複製未經處理資料檔案進行遷移
為了擷取資料一致性，受限需要關閉資料庫

shell>mysqladmin shutdown
複製Mysql的data檔案
shell>tar cf /tmp/db.tar ./data
slave設定

修改my.cnf檔案，設定server-id為唯一數字

[mysqld]
server-id=2
slave伺服器不需要強制使用二進位日誌，但是推薦啟用二進位日誌，這樣可以進行更多其他動作（Database Backup、作為其他slave的主庫）

主從複製具體操作

1. 新的主從伺服器搭建

配置master資料庫屬性

[mysqld]
log-bin=mysql-bin
server-id=1
啟動master伺服器

建立一個主從使用者
每個從伺服器與主伺服器串連，都是使用mysql使用者和密碼
在master伺服器上建立使用者
mysql>create user '*username*'@'*address*' identified by '*slavepass*';
mysql>grant replication slave on *.* to '*username*'@'*address*';
擷取master資料庫相關資訊

串連主要資料庫，執行flush tables with read lock命令

mysql> FLUSH TABLES WITH READ LOCK;
在master伺服器上再開啟一個回話，執行show master status來確定當前二進位記錄檔名和位置

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
在主伺服器上釋放read 鎖
mysql>unlock tables;

在slave伺服器上，配置mysql屬性
[mysqld]
server-id=2
啟動slave資料庫
在slave資料庫上執行change master to來設定master主從伺服器配置
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
NOTE主從配置無法使用Unix socket檔案，只能使用TCP/IP

在5.6.5或以前的版本，slave_master_info和
slave_relay_log_info使用的是myiam儲存引擎，可使用下列語句修改（不可以在資料庫運行階段修改）

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
查看slave運行狀態

show slave status\G
2. 已存在資料的複製操作

建立複製使用者
mysql>create user '*username*'@'*address*' identified by '*slavepass*';
mysql>grant replication slave on *.* to '*username*'@'*address*';
如果master資料庫未設定server-id和二進位日誌，則需要關閉mysql資料庫添加相關配置
如果需要關閉資料庫，則是一個建立快照的好機會。通過複製未經處理資料方式進行快照建立。

擷取data目錄位置
關閉資料庫
service mysql stop
對data目錄打包
tar cf /tmp/tmm/db.tar ./mysql
如果已設定，則擷取日誌位置，然後使用mysqldump或未經處理資料複製的方式進行資料匯出
可以使用“擷取mater的二進位日誌位置”介紹的方式擷取二進位位置，然後使用以下命令來匯出資料庫：

 >mysqldump --all-databases >db.dump
或者直接使用mysqldump –all-databases –master-data > dbdump.db，此命令不需要擷取日誌位置資訊，直接包含在檔案中。

設定slvae（具體可參看前面介紹）
這一步根據建立快照方式不同而不同
mysqldump方式：

啟動slave，使用–skip-slave-start選項使replication不在啟動資料庫的時候啟動
匯入資料
mysql<fulldb.dump
Raw data files方式：

解壓縮資料
tar xvf dbdump.tar
chown -R mysql:mysql mysql/
對解壓後的目錄需要設定為mysql使用者權限，使mysql能串連和修改這些檔案
啟動slave，使用–skip-slave-start選項
在slave上執行CHANGE MASTER TO語句，設定串連

啟動slave線程
mysql<start slave;
啟動成功後不要忘記刪掉my.cnf中的skip-slave-start選項，如果是通過命令列添加參數的，則可以忽略。

3. 增加slave

複製一個存在的slave

關閉slave服務
shell> mysqladmin shutdown
複製data目錄（進行raw形式的組從配置）
tar cf /tmp/fulldump.dump ./mysql
在增加的slave上解壓縮目錄
tar xvf fulldump.dump
修改目錄許可權：
chown -R mysql:mysql mysql/
在my.cnf中添加2個參數，手動指定新slave的relay-log首碼格式，不使用主機名稱作為格式：

relay-log=f8392e8f9f63-relay-bin
relay-log-index=f8392e8f9f63-relay-bin
如果不修改relay-log格式為拷貝過來的slave-relay-log格式，則mysql會自動使用主機名稱作為新的slave-relay-log格式，啟動會報錯：

 

1. 
   
   
   

 

最後：

讀寫分離就是通過主從複製實現的，主要資料庫負責寫，從資料庫負責讀，達到讀寫分離的目的。曾經認為高大上的東西，其實一層層剝離後也就那樣，繼續學習。