MySQL主從介紹、搭建、管理等詳細(xì)過(guò)程

1、參考文早 1: 參考文章 2： 參考文章 3: 1 ,主從復(fù)制架構(gòu)演示圖170 thread該過(guò)程的第一部分就是 master記錄二進(jìn)制日志。在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務(wù)串行的寫(xiě)入二進(jìn)制日志，即使事務(wù)中的語(yǔ)句都是交叉執(zhí)行的。在事件寫(xiě)入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。下一步就是slave將master的bi nary log拷貝到它自己的中繼日志。首先， slave開(kāi)始一 個(gè)工作線程 I/O線程。I/O線程在master上打開(kāi)一個(gè)普通的連接，然后開(kāi)始 binlog dump process。Binlog dump proces

2、s從 master的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上master，它會(huì)睡眠并等待 master產(chǎn)生新的事件。I/O線程將這些事件寫(xiě)入中 繼日志。SQL slave thread（SQL從線程）處理該過(guò)程的最后一步。SQL線程從中繼日志讀取事件， 并重放其中的事件而更新slave的數(shù)據(jù)，使其與 master中的數(shù) 據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開(kāi)銷很小。此外，在 master中也有一個(gè)工作線程：和其它MySQL的連接一樣，slave在master中打開(kāi)一個(gè)連接也會(huì)使得master開(kāi)始一個(gè)線程。復(fù)制過(guò)程有一個(gè)很重要的限制復(fù)制在slave上是

3、串行化的，也就是說(shuō)master上的并行更新操作不能在slave上并行操作。2 , mysql主從同步應(yīng)用場(chǎng)景(1)：數(shù)據(jù)分布(2)：負(fù)載均衡(3)：備份(4)：高可用和容錯(cuò)3，搭建mysql主從的環(huán)境要求主從系統(tǒng)要保持一致：包括數(shù)據(jù)庫(kù)版本，操作系統(tǒng)版本，磁盤(pán)io磁盤(pán)容量，網(wǎng)絡(luò)帶寬等。rootdata02 # cat /etc/redhat-releaseCen tOS release 6.5 (Fin al)rootdata02 #主庫(kù)master從庫(kù)slaveOS系統(tǒng)版本Cen tOS release 6.2 (Fin al)Cen tOS release 6.2 (Fin al)數(shù)據(jù)庫(kù)版本

4、5.6.12-log5.6.12-log磁盤(pán)容量50G30G主機(jī)ip地址2930端口33063306內(nèi)存2G2G服務(wù)器類型虛擬機(jī)虛擬機(jī)MySQL 安裝參考：http:/blog.csd n.n et/mchdba/article/details/511380634，開(kāi)始搭建mysql主從復(fù)制4.1建立復(fù)制賬號(hào)GRANT REPLICATIONSLAVE,RELOAD,SUPEON *.* TO repl'30' IDENTIFIED BY 'repl_1234：建立一個(gè)復(fù)制賬號(hào)，只允許從192.16

5、8.52.130上來(lái)訪問(wèn)登錄主庫(kù)進(jìn)行二進(jìn)制日志傳輸同步。PS:如果 mysql版本新舊密碼算法不同， 可以設(shè)置 set password for 'backup''00'=old_password('1234')4.2手動(dòng)同步數(shù)據(jù)因?yàn)楫?dāng)開(kāi)始搭建的時(shí)候，主庫(kù)上已經(jīng)有了數(shù)據(jù)， 所以要先把主庫(kù)已經(jīng)存在的數(shù)據(jù)先手動(dòng)同步遷移到從庫(kù)上面去。搭建過(guò)程中，禁止在主庫(kù)從庫(kù)上進(jìn)行任何對(duì)數(shù)據(jù)庫(kù)的ddl、dml等數(shù)據(jù)操作。這里可以用mysqldump也可以用xtrabackup導(dǎo)出主庫(kù)上面的數(shù)據(jù)：(421): xtrabackup 方式在主庫(kù)上192

6、.168.52.129上面進(jìn)行數(shù)據(jù)備份，備份命令，要添加-safe-slave-backup參數(shù)：inn obackupex-user=backup-password="123456"-host=29-socket=/tmp/mysql.sock-defaults-file=/etc/my.c nf/data/backups/mysql/repl/backup_slave-parallel=3 -safe-slave-backup -no-timestamp去備份目錄/data/backups/mysql/repl/backup_slave 查看備份

7、時(shí)候的主庫(kù)二進(jìn)制信息，需要根據(jù)這個(gè)二進(jìn)制信息來(lái)進(jìn)行數(shù)據(jù)同步，如下所示：rootdata01 test# cd /data/backups/mysql/repl/backup_slaverootdata01 backup_slave#more xtrabackup_b inlog_infomysql-bi n.000147120rootdata01 backup_slave#壓縮備份文件并且傳輸?shù)綇膸?kù)佃30上面：tar -zcvf backup_slave.tar.gz backup_slave/scp backup_slave.tar.gz 30:/

8、tmp/(422)mysqldump 方式在主庫(kù)上佃29上做基于主庫(kù)做數(shù)據(jù)備份/usr/local/mysql/bi n/mysqldump-ubackup -password=123456-host=29-sin gle-tra nsacti on -flush-logs -master-data=2 -add-drop-table -create-opti on -quick -exte nded-i nsert=false -set-charset -disable-keys -A > /tmp/alldb.sql壓縮備份文件，傳輸?shù)綇膸?kù)

9、佃30上面去。gzip /tmp/alldb.sqlscp /tmp/alldb.sql.gz 30:/tmp/4.3 配置 master 庫(kù)(29)# Server ID# No sync databasesvim /etc/my.c nfserver-id=129log-bi n =/home/data/mysql/bi nlog/mysql-bin binl og-ig no re-db=mysql# No sync databases# No sync databasesbinlog-ignore-db=test bi

10、nlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-do-db=user_db保存退出，重啟 mysql 主庫(kù)，可以查看主庫(kù)的狀態(tài)，如下： mysql> show master status;+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB| Executed_Gtid_Set |+| mysql-bin.000151 | 120 | user_db | mysql,test,information_schema,performan

11、ce_schema | |+1 row in set (0.00 sec) mysql>mysql> show master statusG;*1. row*File: mysql-bin.000151Position: 120Binlog_Do_DB: user_dbBinlog_Ignore_DB: mysql,test,information_schema,performance_schema Executed_Gtid_Set:1 row in set (0.00 sec)ERROR:No query specified mysql>4.4 配置 slave 從庫(kù)(1

12、30 )Slave 的配置與 master 庫(kù)類似，如下所示：Vim /etc/f#Master-Slave configlog-slave-updates=1replicate-same-server-id=0server-id=230 # Server ID log-bin =/home/data/mysql/binlog/mysql-bin.log relay-log=mysql-relay-bin# No sync databases# No sync databases# No sync databasesexpire-logs-days=10master-in

13、fo-repository=TABLE relay-log-info-repository=TABLE binlog-ignore-db=mysql binlog-ignore-db=test binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-do-db=user_dbserver_id 是必須的，而且唯一。 slave 沒(méi)有必要開(kāi)啟二進(jìn)制日志，但是在一些情況下，必須 設(shè)置，例如，如果 slave為其它slave的master，必須設(shè)置bin_log。在這里，我們開(kāi)啟了二進(jìn)制日志，而且顯

14、示的命名（默認(rèn)名稱為host name,但是，如果 host name改變則會(huì)出現(xiàn)問(wèn)題）。relay_log 配置中繼日志, log_slave_updates 表示 slave 將復(fù)制事件寫(xiě)進(jìn)自己的二進(jìn)制日志（后面會(huì)看到它的用處 ）。有些人開(kāi)啟了 slave的二進(jìn)制日志，卻沒(méi)有設(shè)置 log_slave_updates，然后查看slave的數(shù)據(jù) 是否改變，這是一種錯(cuò)誤的配置。所以，盡量 使用read_only，它防止改變數(shù)據(jù)（除了特殊 的線程）。但是，read_only并是很實(shí)用，特別是那些需要在 slave上創(chuàng)建表的應(yīng)用。配置玩，重啟 slave 數(shù)據(jù)庫(kù)mysql> show slav

15、e status;Empty set （0.05 sec）mysql> 沒(méi)有記錄，需要設(shè)置一些主從配置。4.5 設(shè)置主從連接復(fù)制生成CHANGE MASTER語(yǔ)句，然后在從上執(zhí)行，master信息，從備份集合里面獲?。簒trabackup備份的話，從 xtrabackup_binlognfo里面獲取，如下所示： rootdata02 tmp#tar -xvf backup_slave.tar.gzrootdata02 tmp#cd backup_slaverootdata02 backup_slave# more xtrabackup_binlog_infomysql-bin.00014

16、1 120rootdata02 backup_slave#Mysqldump的啊，從sql文件的頭幾列獲取，如下所示：rootdata02 tmp#cd /tmp/rootdata02 tmp#gunzip alldb.sql.gzrootdata02 tmp# more alldb.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE"|grep "MASTER_LOG_POS" |more- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000141', MA

17、STER_LOG_POS=120; 生成 change master 語(yǔ)句，如下：CHANGE MASTER TO MASTER_HOST='29', MASTER_USER='repl',MASTER_PASSWORD='repl_1234', MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;有報(bào)錯(cuò)信息：mysql> CHANGE MASTER TO MASTER_HOST='29',-> MA

18、STER_USER='repl',-> MASTER_PASSWORD='repl_1234',-> MASTER_LOG_FILE='mysql-bin.000141',-> MASTER_LOG_POS=120;ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set -server-id to enable either a master or a slave. Additional

19、 error messages can be found intheMySQL error log.mysql>具體原因目前不詳， 網(wǎng)上查找到的資料 :數(shù)據(jù)庫(kù)打開(kāi)這幾張表的默認(rèn)引擎為MyISAM ，但是這幾張表在建表時(shí)的引擎為 INNODB但是能確定的，這幾張表確實(shí)是在 mysql5.6 中新入的innodb_index_stats,innodb_tables_stats,slave_master_info,slave_relay_log_info,slave_worker_info解決方法：(1) 登錄數(shù)據(jù)庫(kù)，進(jìn)入mysql庫(kù)，執(zhí)行如下SQL刪除5張表記住，一定要是 drop tabl

20、e if existsdrop table if exists innodb_index_stats; drop table if exists innodb_table_stats;drop table if exists slave_master_info; drop table if exists slave_relay_log_info; drop table if exists slave_worker_info;執(zhí)行完后，可以用 show tables 查看一下，看表的數(shù)據(jù)是否已經(jīng)比刪除之前減少了，如果減 少了，說(shuō)明你成功了！rootdata02 test cd /home/dat

21、a/mysql/data/mysqlrootdata02 mysql# ll *.ibd-rw-rw.1mysql mysql 983041?9 02:08 innodb_index_stats.ibd-rw-rw.1mysql mysql 983041?9 02:08 innodb_table_stats.ibd-rw-rw.1mysql mysql 983041?9 02:08 slave_master_info.ibd-rw-rw.1mysql mysql 983041?9 02:08 slave_relay_log_info.ibd-rw-rw.1mysql mysql 983041?

22、9 02:08 slave_worker_info.ibdrootdata02 mysql#強(qiáng)行刪除 ibd 文件：rootdata02 mysql# rm -f *.ibd重啟數(shù)據(jù)庫(kù)，登錄 mysqlsource /usr/test/mysql/share/mysql_system_tables.sqlshow tables;發(fā)現(xiàn)表已經(jīng)回來(lái)了，表數(shù)據(jù)大概總數(shù)量為 28 個(gè)。之后執(zhí)行 change master to , OK,搞定，如下所示：mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.03 sec)mysql>

23、;CHANGEMASTERTOMASTER_HOST='29',MASTER_USER='repl',MASTER_PASSWORD='repl_1234',MASTE R_LOG_FILE='mysql-bin.000141', MASTER_LOG_POS=120;Query OK, 0 rows affected, 2 warnings (0.07 sec)mysql>啟動(dòng) slavemysql> start slave;Query OK, 0 rows affected (0.02 se

24、c)mysql>4.6 驗(yàn)證主從復(fù)制狀態(tài)在slave服務(wù)器上查看 slave狀態(tài):mysql> show slave statusGSlave_IO_State: Waiting for master to send event Master_Host: 29 Master_User: repl Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000151 Read_Master_Log_Pos: 346Relay_Log_File: mysql-relay-bin.000018Rel

25、ay_Log_Pos: 509Relay_Master_Log_File: mysql-bin.000151Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: business_db,user_db,plocc_systemReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0E

26、xec_Master_Log_Pos: 346Relay_Log_Space: 845Until_Condition: None這里主要看：Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 010和SQL線程都是 Yes以及Seconds_Behind_Master是0就表示從庫(kù)正常運(yùn)行了。在 master 服務(wù)器上查看： mysql> show full processlist;+| db | Command| Time | State| Id | User| Host| Info|+|1 | event

27、_scheduler | localhostempty queue| 21 | root| localhost| NULL | Daemon| NULL | Query| 5874 | Waiting on| NULL|0 | init| show full processlist | 24 | repl | 30:45665 | NULL | Binlog Dump | 88 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL|+3 rows in set (0.

28、03 sec)mysql>看到有 30:45665 的線程在同步二進(jìn)制數(shù)據(jù)4.7 master 添加數(shù)據(jù)驗(yàn)證去 master (29)上操作，添加表記錄： mysql> create table master_test select 1 as a,'a' as b; Query OK, 1 row affected (0.72 sec)Records: 1 Duplicates: 0 Warnings: 0mysql>去 slave(30 )上查詢下，看表數(shù)據(jù)是否同步過(guò)來(lái)，看到數(shù)據(jù)已經(jīng)同步

29、過(guò)來(lái)了， 如下所示：mysql> select * from user_db.master_test;+-+-+| a | b |+-+-+| 1 | a |+-+-+1 row in set (0.06 sec)mysql>5 ，如何添加新的 slave 服務(wù)器總結(jié)如果master運(yùn)行很久了，需要添加新的 slave服務(wù)器，那么搭建新的 slave,此時(shí)，有幾種 方法可以使 slave 從另一個(gè)服務(wù)開(kāi)始，例如，從 master 拷貝數(shù)據(jù)，從另一個(gè) slave 克隆，從 最近的備份開(kāi)始一個(gè) slave。Slave與master同步時(shí)，需要三樣?xùn)|西：(1) master 的某個(gè)時(shí)刻的

30、數(shù)據(jù)快照；(2) master 當(dāng)前的日志文件、以及生成快照時(shí)的字節(jié)偏移。這兩個(gè)值可以叫做日志文件坐標(biāo)(log file coordinate)，因?yàn)樗鼈兇_定了一個(gè)二進(jìn)制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐標(biāo)；(3) master 的二進(jìn)制日志文件。也可以通過(guò)以下幾中方法來(lái)克隆一個(gè)slave：(1) 冷拷貝(cold copy)停止master，將master的文件拷貝到slave;然后重啟 master。缺點(diǎn)很明顯。(2) 熱拷貝(warm copy)如果你僅使用MylSAM表，你可以使用 mysqlhotcopy拷貝，即使服務(wù)器正在運(yùn)行。 如果有my

31、isam和innodb表，可以在業(yè)務(wù)低峰期用tar包的方式來(lái)熱拷貝。(3) 使用 mysqldump使用mysqldump來(lái)得到一個(gè)數(shù)據(jù)快照可分為以下幾步：<3.a>鎖表：如果你還沒(méi)有鎖表，你應(yīng)該對(duì)表加鎖，防止其它連接修改數(shù)據(jù)庫(kù)，否則，你得 到的數(shù)據(jù)可以是不一致的。如下：mysql> FLUSH TABLES WITH READ LOCK;<3.b>在另一個(gè)連接用 mysqldump創(chuàng)建一個(gè)你想進(jìn)行復(fù)制的數(shù)據(jù)庫(kù)的轉(zhuǎn)儲(chǔ)：見(jiàn)421小節(jié)<3.c>對(duì)表釋放鎖。mysql> UNLOCK TABLES;(4) 使用 xtrabackup使用xtraback

32、up得到數(shù)據(jù)快照，見(jiàn) 422小節(jié)6，如何實(shí)現(xiàn)MSS當(dāng)設(shè)置log_slave_updates時(shí)，你可以讓 slave扮演其它slave的master。此時(shí)，slave把SQL 線程執(zhí)行的事件寫(xiě)進(jìn)行自己的二進(jìn)制日志(binary log)，然后，它的slave可以獲取這些事件7，復(fù)制過(guò)濾復(fù)制過(guò)濾可以讓你只復(fù)制服務(wù)器中的一部分?jǐn)?shù)據(jù)，有兩種復(fù)制過(guò)濾：在master上過(guò)濾二進(jìn)制日志中的事件；在 slave上過(guò)濾中繼日志中的事件。如下圖7.png所示：SlaveMasteriepkare_do_db iepliiate_do_tabie 即 1 tatt_igncre_db ieplkate_igncre

33、_table ieplicate_rewpite_lb iepl tatcwid_do_ta t>e irpl icdte w I d tgim «訕忖亡hn lcg_(h_db:bhloj-切no杷8，常用的mysql主從拓?fù)浣Y(jié)構(gòu)復(fù)制的體系結(jié)構(gòu)有以下一些基本原則：(1)每個(gè)slave只能有一個(gè) master ；每個(gè)slave只能有一個(gè)唯一的服務(wù)器ID;(3) 每個(gè)master可以有很多slave；(4) 如果你設(shè)置 log_slave_updates， slave可以是其它 slave的 master，從而擴(kuò)散 master 的更新。MySQL不支持多主服務(wù)器復(fù)制 (Mult

34、imaster Replication)即一個(gè) slave可以有多個(gè) master。但是，通過(guò)一些簡(jiǎn)單的組合，我們卻可以建立靈活而強(qiáng)大的復(fù)制體系結(jié)構(gòu)。9， mysql主從同步之間的管理介紹一下基本的 mysql主從的管理操作命令：9.1停止mysql從服務(wù)STOP SLAVE IO_THREAD; #停止 10 進(jìn)程STOP SLAVE SQL_THREAD; #停止 SQL進(jìn)程STOP SLAVE;#停止 10 和 SQL進(jìn)程9.2開(kāi)啟mysql主從同步服務(wù)START SLAVE IO_THREAD; # 啟動(dòng) 10 進(jìn)程START SLAVE SQL_THREAD#啟動(dòng) SQL進(jìn)程START SLAVE;#啟動(dòng)10進(jìn)程和SQL進(jìn)程9.3重置mysql主從同步RESET SLAVE;#用于讓從屬服務(wù)器忘記其在主服務(wù)器的二進(jìn)制日志中的復(fù)制位置，它會(huì)刪除masternfo和文件，以及所有的中繼日志，并啟動(dòng)一個(gè)新的中繼日志，當(dāng)你不需要主從的時(shí)候可以在從上執(zhí)行這個(gè)操作。不然以后