mysql的主從復制如何配置

這篇文章給大家分享的是有關mysql的主從復制如何配置的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

數據庫復制對于系統高可用、高性能的提升扮演者很重要的角色。

1 主庫配置

1.1 my.cnf配置：

在主庫配置文件my.cnf中進行如下基本配置：

log-bin  =  mysql-bin　//二進制日志文件名稱主體
log-bin-index  =  mysql-bin.index　//二進制日志文件索引文件
server-id  =  1　//唯一的服務器ID，為了保持唯一性，可以去ip的尾部
binlog-format  =  mixed　//控制復制基于的方式，有基于語句(statement)，基于行(row)，混合(mixed)，**主從庫需要保持一致**
#sync_binlog=1 //推薦配置，開啟該選項，mysql每次在事務提交前會將二進制日志同步到磁盤上，保證在服務器崩潰時不會丟失事件。

默認復制全部數據庫，如果需要指定數據庫，請參照第7節（復制過濾）。

比如說要指定db1和db2兩個數據庫進行主從復制：
binlog-do-db = db1
binlog-do-db = db2

1.2 添加復制賬戶：

復制賬戶添加以及權限設置：

mysql> grant replication slave, replicatin client on \*.\* to repl@'172.16.226.192' identified by 'repl123456';　//其中repl是用戶名，repl123456為賬戶密碼，172.16.226.168為備庫的地址。
mysql> flush privileges;　//在不重啟mysql服務的情況下完成權限的更新

2 備庫配置

在備庫配置文件my.cnf中進行如下基本配置：

relay-log  =  slave-relay-bin　//中繼日志文件名稱主體
relay-log-index  =  slave-relay-bin.index　//中繼日志文件索引文件
server-id  =  2　//唯一的服務器ID，必須要異于主庫
#read_only = 1　//限制備庫為只讀，可選
log_slave_updates = 1　//控制是否在中繼日志執行之后，將同步過來的數據添加到自己的binlog中去，1代表是
skip_slave_start // 該選項能夠阻止備庫在崩潰后自動啟動復制，建議開啟
即使開啟了建議的選項，備庫仍然可能在崩潰后被中斷，因為master.info和中級日志文件都不是崩潰安全的，所以建議開啟一下選項：
sync_master_info     = 1
sync_relay_log       = 1
sync_relay_log_info  = 1

同樣可以過濾待同步的數據庫，或者表，參考復制過濾一節。

3 數據庫遠程備份

數據庫遠程備份可以選擇mysqldump（邏輯備份）進行熱備，但對于數據量較大時會比較慢，Xtrabackup（物理備份）也可以對mysql數據庫進行熱備（這里使用innobackupex-1.5.1），Xtrabackup可以實現innoDB等數據庫的在線備份，速度較快且不影響正常讀寫。這里對全庫進行備份。

3.1 創建備份賬戶

在主服務器創建用戶backup（使用最小權限），用于數據庫備份。

mysql> grant reload, lock tables, replication client on \*.\* to backup@'%' identified by 'backup123';
mysql> flush privileges;　//在不重啟mysql服務的情況下完成權限的更新

3.2 數據庫完全備份

完全備份和恢復準備兩個步驟都是在主庫服務器完成。

innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123  /mysqlbackup
--defaults-file：選擇默認的配置文件
--user和--password：分別為進行備份的用戶名和密碼
/mysqlbackup：目標目錄

3.3 恢復準備

一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。
innobakupex命令的--apply-log選項可用于實現上述功能。如下面的命令：

innobackupex-1.5.1 --apply-log --user=backup --password=backup123  /mysqlbackup/2017-01-11_21-20-57
如果執行正確，其最后輸出的幾行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407  9:01:36  InnoDB: Starting shutdown...
120407  9:01:40  InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40  innobackupex: completed OK!

在實現“準備”的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的內存的大小，默認通常為100M。如果有足夠的內存可用，可以多劃分一些內存給prepare的過程，以提高其完成速度。

3.4 數據拷貝

將主服務器上準備好的數據庫拷貝到從服務器。(當然也可以打包后再拷貝)

scp -r /mysqlbackup/ copyer@192.168.1.192:/data/

3.5 數據恢復

在數據恢復之前首先關閉從服務器mysql服務，并從備份文件夾中的xtrabackup_binlog_info文件中獲取當前正在使用的二進制日志文件，以及備份這一刻為止二進制日志事件的位置。如果datadir目錄不為空，還需要清空datadir目錄。
innobackupex命令的--copy-back選項用于執行恢復操作，其通過復制所有數據相關的文件至mysql服務器datadir目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取datadir目錄的相關信息（也可以通過--defaults-file指定my.cnf目錄，還要確保datadir路徑為空）

innobackupex-1.5.1 --copy-back  /mysqlbackup
如果執行正確，其輸出信息的最后幾行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10  innobackupex: completed OK!

請確保如上信息的最后一行出現“innobackupex: completed OK!”。

當數據恢復至datadir目錄以后，還需要確保所有數據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如：

chown -R  mysql:mysql  /var/lib/mysql/

4 主從連接

4.1 開啟從數據庫

service mysql start

如果開啟mysql失敗，可以通過查看錯誤日志尋找失敗原因。

4.2 建立主從連接

從庫通過復制賬戶連接到主庫：（slave必須處于stop狀態才能使以下連接生效）

mysql> change master to master_host='192.168.1.208'，master_user='repl'，
master_password='repl123456'，master_log_file='mysql-bin.000001'（備份時得到的活動日志）,master_log_pos=0（備份時得到的活動日志中事件的位置）;

注：如果這里在進行主從連接的時候一直連不上master，有一個可能的原因是my.cnf配置文件中綁定了本機，即bind-address = 127.0.0.1，我們要做的就是將其注釋掉，否則外部機器是訪問不了的。

開啟slave：

mysql> start slave;

查看slave狀態，可以發現IO線程和SQL線程已處于開啟狀態，有非常多表征從庫連接狀態的變量（這些變量同樣可以用于設置主從監控），在這里不一一做介紹。

mysql> show slave status;

Slave_IO_Running: Yes  //表示IO線程運行正常
Slave_SQL_Running: Yes  //表示SQL線程運行正常
Seconds_Behind_Master: 0  //表示在網絡條件較好的情況下，從庫能夠及時同步上主庫

4.3 常用監控命令

mysql> show processlist\G;　//查看數據庫服務線程情況
mysql> show master/slave status\G;　//查看主備庫狀態
mysql> flush logs;　//強制輪換(rotate)二進制日志，從而得到一個完整的二進制日志文件
mysql> show binlog events in '指定二進制日志文件名稱'  from （從指定位置開始顯示） limit （需要顯示的事件數量）\G;　//查看binlog中事件
mysql> show binary logs;　//顯示所有的binlogs
mysql> reset master;　//刪除所有二進制日志文件并清空索引文件
mysql> reset slave;　//刪除slave上復制用的所有文件重新開始
mysql> show slave hosts;  //查看主庫所擁有的從庫信息

5 從庫延遲較大

如果發現從庫延遲較大，就需要找到延遲大的原因。參數 innodb_flush_log_at_trx_commit對mysql的寫入效率影響較大，有三個取值：

0：每隔一秒，把事務日志緩存區的數據寫到日志文件中，以及把日志文件的數據刷新到磁盤上；
1：每個事務提交時候，把事務日志從緩存區寫到日志文件中，并且刷新日志文件的數據到磁盤上；
2：每事務提交的時候，把事務日志數據從緩存區寫到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盤上，而是取決于操作系統的調度；

取1時的IO耗費最大，雖然一致性和完整性方面效果最好，但是寫入效率最低，而這也是導致從庫延遲較大的原因（如果服務器配置較高或許會好些）。取0時mysql寫入性能很好，但如果 mysqld 進程崩潰，通常會導致最后 1s 的日志丟失 。取2時的寫入性能也很好，每次事務提交會寫入日志文件，但并不會立即刷寫到磁盤，日志文件會每秒刷寫一次到磁盤。這時如果 mysqld 進程崩潰，由于日志已經寫入到系統緩存，所以并不會丟失數據；在操作系統崩潰的情況下，通常會導致最后 1s 的日志丟失。

6 混合模式復制

正常情況下使用使用基于語句的復制，而對不安全的語句則切換到基于行的復制。主要有以下幾種情況：

1. 該語句調用了：
   • UUID函數
   • 用戶自定義函數
   • CURRENT_USER或USER函數
   • LOAD_FILE函數
2. 一個語句同時更新了兩個或者兩個以上含有AUTO_INCREMENT列的表
3. 語句使用了服務器變量
4. 存儲引擎不允許使用基于語句的復制，例如，mysql cluster引擎

7 復制過濾

有時候我們不需要對數據庫中所有的庫進行復制，或者不想對指定庫中的某些表進行復制操作，那么我們就需要對復制進行一定的過濾配置，以達到更合理的復制效果。

1. 基于master

**binlog-do-db=mysql**：主庫只是將指定庫(mysql)發生的變化記錄到二進制日志中。
**binlog-ignore-db=mysql**：主庫取消將指定庫(mysql)發生的變化記錄到二進制日志中。

2. 基于slave

針對數據庫進行的過濾：

**replicate-do-db=mysql**：從庫只是將指定庫(mysql)發生的變化進行重現。
**replicate-ignore-db=mysql**：從庫取消將指定庫(mysql)發生的變化進行重現。
針對表進行的過濾：
**replicate-wild_do-table=mysql.learn**：從庫只是將指定庫(mysql)中指定表(learn)發生的變化進行重現。
**replicate-wild_ignore-table=mysql.learn**：從庫取消將指定庫(mysql)中指定表(learn)發生的變化進行重現。

以上復制過濾方式乍一看沒有問題，其實還是有需要注意的地方。因為這些過濾方式的效果與復制方式有關系。如果是基于語句的復制，binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db與跨庫(如use庫內和use外)有關系，這一點需要注意。

8 日志清理

暴力清理：（沒有主從復制的情況下）

1、重啟mysql服務器即可關閉bin日志的記錄
2、通過reset master命令進行清理

條件清理

如果存在主從復制關系，則應當使用purge的方式來清理bin日志，語法如下：

purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'

用戶刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志，同時這些日志也會從日志索引文件的清單中刪除。

eg.
purge master logs to 'mysql-bin.000005';
purge master logs before '2014-08-30 00:00:00';//清除指定日期之前的日志
purge master logs before date_sub(now(),Interval 3 day);清除三天前的日志

定時清理

參數：expire_logs_days
說明：二進制日志自動刪除/過期的天數。默認值為'0'，即沒有過期的
示例：expire_logs_days = 5，代表日志的有效時間為5天
什么時候會刪除過期日志？

每次進行log flush的時候會自動刪除過期的日志

什么時候會觸發log flush？

1、重啟
2、binlog文件的大小達到了最大限制
3、手動執行flush logs命令

感謝各位的閱讀！關于mysql的主從復制如何配置就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！