MySQL主從介紹、搭建、管理等詳細過程

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

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

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

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

5、8.52.130上來訪問登錄主庫進行二進制日志傳輸同步。PS:如果 mysql版本新舊密碼算法不同， 可以設置 set password for 'backup''00'=old_password('1234')4.2手動同步數據因為當開始搭建的時候，主庫上已經有了數據， 所以要先把主庫已經存在的數據先手動同步遷移到從庫上面去。搭建過程中，禁止在主庫從庫上進行任何對數據庫的ddl、dml等數據操作。這里可以用mysqldump也可以用xtrabackup導出主庫上面的數據：(421): xtrabackup 方式在主庫上192

6、.168.52.129上面進行數據備份，備份命令，要添加-safe-slave-backup參數：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、時候的主庫二進制信息，需要根據這個二進制信息來進行數據同步，如下所示：rootdata01 test# cd /data/backups/mysql/repl/backup_slaverootdata01 backup_slave#more xtrabackup_b inlog_infomysql-bi n.000147120rootdata01 backup_slave#壓縮備份文件并且傳輸到從庫佃30上面：tar -zcvf backup_slave.tar.gz backup_slave/scp backup_slave.tar.gz 30:/

8、tmp/(422)mysqldump 方式在主庫上佃29上做基于主庫做數據備份/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壓縮備份文件，傳輸到從庫

9、佃30上面去。gzip /tmp/alldb.sqlscp /tmp/alldb.sql.gz 30:/tmp/4.3 配置 master 庫(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 主庫，可以查看主庫的狀態(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 從庫(1

12、30 )Slave 的配置與 master 庫類似，如下所示：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 沒有必要開啟二進制日志，但是在一些情況下，必須 設置，例如，如果 slave為其它slave的master，必須設置bin_log。在這里，我們開啟了二進制日志，而且顯

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

15、e status;Empty set （0.05 sec）mysql> 沒有記錄，需要設置一些主從配置。4.5 設置主從連接復制生成CHANGE MASTER語句，然后在從上執(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 語句，如下：CHANGE MASTER TO MASTER_HOST='29', MASTER_USER='repl',MASTER_PASSWORD='repl_1234', MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;有報錯信息：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>具體原因目前不詳， 網上查找到的資料 :數據庫打開這幾張表的默認引擎為MyISAM ，但是這幾張表在建表時的引擎為 INNODB但是能確定的，這幾張表確實是在 mysql5.6 中新入的innodb_index_stats,innodb_tables_stats,slave_master_info,slave_relay_log_info,slave_worker_info解決方法：(1) 登錄數據庫，進入mysql庫，執(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 查看一下，看表的數據是否已經比刪除之前減少了，如果減 少了，說明你成功了！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#強行刪除 ibd 文件：rootdata02 mysql# rm -f *.ibd重啟數據庫，登錄 mysqlsource /usr/test/mysql/share/mysql_system_tables.sqlshow tables;發(fā)現表已經回來了，表數據大概總數量為 28 個。之后執(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>啟動 slavemysql> start slave;Query OK, 0 rows affected (0.02 se

24、c)mysql>4.6 驗證主從復制狀態(tài)在slave服務器上查看 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就表示從庫正常運行了。在 master 服務器上查看： 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 的線程在同步二進制數據4.7 master 添加數據驗證去 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 )上查詢下，看表數據是否同步過來，看到數據已經同步

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

30、數據快照；(2) master 當前的日志文件、以及生成快照時的字節(jié)偏移。這兩個值可以叫做日志文件坐標(log file coordinate)，因為它們確定了一個二進制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐標；(3) master 的二進制日志文件。也可以通過以下幾中方法來克隆一個slave：(1) 冷拷貝(cold copy)停止master，將master的文件拷貝到slave;然后重啟 master。缺點很明顯。(2) 熱拷貝(warm copy)如果你僅使用MylSAM表，你可以使用 mysqlhotcopy拷貝，即使服務器正在運行。 如果有my

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

32、up得到數據快照，見 422小節(jié)6，如何實現MSS當設置log_slave_updates時，你可以讓 slave扮演其它slave的master。此時，slave把SQL 線程執(zhí)行的事件寫進行自己的二進制日志(binary log)，然后，它的slave可以獲取這些事件7，復制過濾復制過濾可以讓你只復制服務器中的一部分數據，有兩種復制過濾：在master上過濾二進制日志中的事件；在 slave上過濾中繼日志中的事件。如下圖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主從拓撲結構復制的體系結構有以下一些基本原則：(1)每個slave只能有一個 master ；每個slave只能有一個唯一的服務器ID;(3) 每個master可以有很多slave；(4) 如果你設置 log_slave_updates， slave可以是其它 slave的 master，從而擴散 master 的更新。MySQL不支持多主服務器復制 (Mult

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