共計 6048 個字符，預計需要花費 16 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章給大家分享的是有關 mysql 的主從復制如何配置的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考。一起跟隨丸趣 TV 小編過來看看吧。
數(shù)據(jù)庫復制對于系統(tǒng)高可用、高性能的提升扮演者很重要的角色。
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 每次在事務提交前會將二進制日志同步到磁盤上，保證在服務器崩潰時不會丟失事件。默認復制全部數(shù)據(jù)庫，如果需要指定數(shù)據(jù)庫，請參照第 7 節(jié)（復制過濾）。
比如說要指定 db1 和 db2 兩個數(shù)據(jù)庫進行主從復制：binlog-do-db = db1
binlog-do-db = db21.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 // 控制是否在中繼日志執(zhí)行之后，將同步過來的數(shù)據(jù)添加到自己的 binlog 中去，1 代表是
skip_slave_start // 該選項能夠阻止備庫在崩潰后自動啟動復制，建議開啟
即使開啟了建議的選項，備庫仍然可能在崩潰后被中斷，因為 master.info 和中級日志文件都不是崩潰安全的，所以建議開啟一下選項：sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1同樣可以過濾待同步的數(shù)據(jù)庫，或者表，參考復制過濾一節(jié)。
3 數(shù)據(jù)庫遠程備份
數(shù)據(jù)庫遠程備份可以選擇 mysqldump（邏輯備份）進行熱備，但對于數(shù)據(jù)量較大時會比較慢，Xtrabackup（物理備份）也可以對 mysql 數(shù)據(jù)庫進行熱備（這里使用 innobackupex-1.5.1），Xtrabackup 可以實現(xiàn) innoDB 等數(shù)據(jù)庫的在線備份，速度較快且不影響正常讀寫。這里對全庫進行備份。
3.1 創(chuàng)建備份賬戶
在主服務器創(chuàng)建用戶 backup（使用最小權限），用于數(shù)據(jù)庫備份。
mysql grant reload, lock tables, replication client on \*.\* to backup@ % identified by backup123
mysql flush privileges; // 在不重啟 mysql 服務的情況下完成權限的更新3.2 數(shù)據(jù)庫完全備份
完全備份和恢復準備兩個步驟都是在主庫服務器完成。
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123 /mysqlbackup
--defaults-file：選擇默認的配置文件
--user 和 --password：分別為進行備份的用戶名和密碼
/mysqlbackup：目標目錄3.3 恢復準備
一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復操作，因為備份的數(shù)據(jù)中可能會包含尚未提交的事務或已經提交但尚未同步至數(shù)據(jù)文件中的事務。因此，此時數(shù)據(jù)文件仍處理不一致狀態(tài)。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
innobakupex 命令的 –apply-log 選項可用于實現(xiàn)上述功能。如下面的命令：
innobackupex-1.5.1 --apply-log --user=backup --password=backup123 /mysqlbackup/2017-01-11_21-20-57
如果執(zhí)行正確，其最后輸出的幾行信息通常如下：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!在實現(xiàn)“準備”的過程中，innobackupex 通常還可以使用 –use-memory 選項來指定其可以使用的內存的大小，默認通常為 100M。如果有足夠的內存可用，可以多劃分一些內存給 prepare 的過程，以提高其完成速度。
3.4 數(shù)據(jù)拷貝
將主服務器上準備好的數(shù)據(jù)庫拷貝到從服務器。(當然也可以打包后再拷貝)
scp -r /mysqlbackup/ copyer@192.168.1.192:/data/3.5 數(shù)據(jù)恢復
在數(shù)據(jù)恢復之前首先關閉從服務器 mysql 服務，并從備份文件夾中的 xtrabackup_binlog_info 文件中獲取當前正在使用的二進制日志文件，以及備份這一刻為止二進制日志事件的位置。如果 datadir 目錄不為空，還需要清空 datadir 目錄。
innobackupex 命令的 –copy-back 選項用于執(zhí)行恢復操作，其通過復制所有數(shù)據(jù)相關的文件至 mysql 服務器 datadir 目錄中來執(zhí)行恢復過程。innobackupex 通過 backup-my.cnf 來獲取 datadir 目錄的相關信息（也可以通過 –defaults-file 指定 my.cnf 目錄，還要確保 datadir 路徑為空）
innobackupex-1.5.1 --copy-back /mysqlbackup
如果執(zhí)行正確，其輸出信息的最后幾行通常如下：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!請確保如上信息的最后一行出現(xiàn)“innobackupex: completed OK!”。
當數(shù)據(jù)恢復至 datadir 目錄以后，還需要確保所有數(shù)據(jù)文件的屬主和屬組均為正確的用戶，如 mysql，否則，在啟動 mysqld 之前還需要事先修改數(shù)據(jù)文件的屬主和屬組。如：
chown -R mysql:mysql /var/lib/mysql/4 主從連接 4.1 開啟從數(shù)據(jù)庫
service mysql start如果開啟 mysql 失敗，可以通過查看錯誤日志尋找失敗原因。
4.2 建立主從連接
從庫通過復制賬戶連接到主庫：（slave 必須處于 stop 狀態(tài)才能使以下連接生效）
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 狀態(tài)，可以發(fā)現(xiàn) IO 線程和 SQL 線程已處于開啟狀態(tài)，有非常多表征從庫連接狀態(tài)的變量（這些變量同樣可以用于設置主從監(jiān)控），在這里不一一做介紹。
mysql show slave status;
Slave_IO_Running: Yes // 表示 IO 線程運行正常
Slave_SQL_Running: Yes // 表示 SQL 線程運行正常
Seconds_Behind_Master: 0 // 表示在網絡條件較好的情況下，從庫能夠及時同步上主庫4.3 常用監(jiān)控命令
mysql show processlist\G; // 查看數(shù)據(jù)庫服務線程情況
mysql show master/slave status\G; // 查看主備庫狀態(tài)
mysql flush logs; // 強制輪換 (rotate) 二進制日志，從而得到一個完整的二進制日志文件
mysql show binlog events in 指定二進制日志文件名稱 from（從指定位置開始顯示）limit（需要顯示的事件數(shù)量）\G; // 查看 binlog 中事件
mysql show binary logs; // 顯示所有的 binlogs
mysql reset master; // 刪除所有二進制日志文件并清空索引文件
mysql reset slave; // 刪除 slave 上復制用的所有文件重新開始
mysql show slave hosts; // 查看主庫所擁有的從庫信息
5 從庫延遲較大
如果發(fā)現(xiàn)從庫延遲較大，就需要找到延遲大的原因。參數(shù) innodb_flush_log_at_trx_commit 對 mysql 的寫入效率影響較大，有三個取值：
0：每隔一秒，把事務日志緩存區(qū)的數(shù)據(jù)寫到日志文件中，以及把日志文件的數(shù)據(jù)刷新到磁盤上；1：每個事務提交時候，把事務日志從緩存區(qū)寫到日志文件中，并且刷新日志文件的數(shù)據(jù)到磁盤上；2：每事務提交的時候，把事務日志數(shù)據(jù)從緩存區(qū)寫到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盤上，而是取決于操作系統(tǒng)的調度；
取 1 時的 IO 耗費最大，雖然一致性和完整性方面效果最好，但是寫入效率最低，而這也是導致從庫延遲較大的原因（如果服務器配置較高或許會好些）。取 0 時 mysql 寫入性能很好，但如果 mysqld 進程崩潰，通常會導致最后 1s 的日志丟失。取 2 時的寫入性能也很好，每次事務提交會寫入日志文件，但并不會立即刷寫到磁盤，日志文件會每秒刷寫一次到磁盤。這時如果 mysqld 進程崩潰，由于日志已經寫入到系統(tǒng)緩存，所以并不會丟失數(shù)據(jù)；在操作系統(tǒng)崩潰的情況下，通常會導致最后 1s 的日志丟失。
6 混合模式復制
正常情況下使用使用基于語句的復制，而對不安全的語句則切換到基于行的復制。主要有以下幾種情況：
該語句調用了：UUID 函數(shù)用戶自定義函數(shù) CURRENT_USER 或 USER 函數(shù) LOAD_FILE 函數(shù)一個語句同時更新了兩個或者兩個以上含有 AUTO_INCREMENT 列的表語句使用了服務器變量存儲引擎不允許使用基于語句的復制，例如，mysql cluster 引擎 7 復制過濾
有時候我們不需要對數(shù)據(jù)庫中所有的庫進行復制，或者不想對指定庫中的某些表進行復制操作，那么我們就需要對復制進行一定的過濾配置，以達到更合理的復制效果。
1. 基于 master
**binlog-do-db=mysql**：主庫只是將指定庫 (mysql) 發(fā)生的變化記錄到二進制日志中。**binlog-ignore-db=mysql**：主庫取消將指定庫 (mysql) 發(fā)生的變化記錄到二進制日志中。2. 基于 slave
針對數(shù)據(jù)庫進行的過濾：
**replicate-do-db=mysql**：從庫只是將指定庫 (mysql) 發(fā)生的變化進行重現(xiàn)。**replicate-ignore-db=mysql**：從庫取消將指定庫 (mysql) 發(fā)生的變化進行重現(xiàn)。針對表進行的過濾：**replicate-wild_do-table=mysql.learn**：從庫只是將指定庫 (mysql) 中指定表 (learn) 發(fā)生的變化進行重現(xiàn)。**replicate-wild_ignore-table=mysql.learn**：從庫取消將指定庫 (mysql) 中指定表 (learn) 發(fā)生的變化進行重現(xiàn)。以上復制過濾方式乍一看沒有問題，其實還是有需要注意的地方。因為這些過濾方式的效果與復制方式有關系。如果是基于語句的復制，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); 清除三天前的日志定時清理
參數(shù)：expire_logs_days
說明：二進制日志自動刪除 / 過期的天數(shù)。默認值為 0，即沒有過期的
示例：expire_logs_days = 5，代表日志的有效時間為 5 天
什么時候會刪除過期日志？
每次進行 log flush 的時候會自動刪除過期的日志什么時候會觸發(fā) log flush？
1、重啟
2、binlog 文件的大小達到了最大限制
3、手動執(zhí)行 flush logs 命令感謝各位的閱讀！關于 mysql 的主從復制如何配置就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
向 AI 問一下細節(jié)
丸趣 TV 網 – 提供最優(yōu)質的資源集合！
