2015年5月28日11時，12小時后恢復，損失：平均每小時106.48W$
1）高可用
2）輔助備份
3）分擔負載
復制是 MySQL 的一項功能，允許服務器將更改從一個實例復制到另一個實例。
1）主服務器將所有數(shù)據(jù)和結構更改記錄到二進制日志中。
2）從屬服務器從主服務器請求該二進制日志并在本地應用其內容。
3）IO：請求主庫，獲取上一次執(zhí)行過的新的事件，并存放到relaylog
4）SQL：從relaylog中將sql語句翻譯給從庫執(zhí)行
主從復制的前提(保證數(shù)據(jù)的一致性,最好打點備份,寫入的位置點為打點備份的位置點或者cat /application/mysql/data/relay-log.info )
1）兩臺或兩臺以上的數(shù)據(jù)庫實例
2）主庫要開啟二進制日志
3）主庫要有復制用戶
4）主庫的server_id和從庫不同
5）從庫需要在開啟復制功能前，要獲取到主庫之前的數(shù)據(jù)（主庫備份，并且記錄binlog當時位置）
6）從庫在第一次開啟主從復制時，時必須獲知主庫：ip，port，user，password，logfile，pos
IP：10.0.0.51
Port：3306
User：rep
Password：oldboy123
logFile：mysql-bin.000002
Pos：120
7）從庫要開啟相關線程：IO、SQL
8）從庫需要記錄復制相關用戶信息，還應該記錄到上次已經(jīng)從主庫請求到哪個二進制日志
9）從庫請求過來的binlog，首先要存下來，并且執(zhí)行binlog，執(zhí)行過的信息保存下來
主從復制涉及到的文件和線程
主庫：
1）主庫binlog：記錄主庫發(fā)生過的修改事件
2）dump thread：給從庫傳送（TP）二進制日志線程
從庫：
1）relay-log（中繼日志）：存儲所有主庫TP過來的binlog事件
2）master.info：存儲復制用戶信息，上次請求到的主庫binlog位置點
3）IO thread：接收主庫發(fā)來的binlog日志，也是從庫請求主庫的線程
4）SQL thread：執(zhí)行主庫TP過來的日志
原理
1）通過change master to語句告訴從庫主庫的ip，port，user，password，file，pos
2）從庫通過start slave命令開啟復制必要的IO線程和SQL線程
3）從庫通過IO線程拿著change master to用戶密碼相關信息，連接主庫，驗證合法性
4）從庫連接成功后，會根據(jù)binlog的pos問主庫，有沒有比這個更新的
5）主庫接收到從庫請求后，比較一下binlog信息，如果有就將最新數(shù)據(jù)通過dump線程給從庫IO線程
6）從庫通過IO線程接收到主庫發(fā)來的binlog事件，存儲到TCP/IP緩存中，并返回ACK更新master.info
7）將TCP/IP緩存中的內容存到relay-log中
8）SQL線程讀取relay-log.info，讀取到上次已經(jīng)執(zhí)行過的relay-log位置點，繼續(xù)執(zhí)行后續(xù)的relay-log日志，執(zhí)行完成后，更新relay-log.info
主從復制搭建實戰(zhàn)
主庫操作:
1）修改配置文件
#編輯mysql配置文件[root@db01 ~]# vim /etc/my.cnf#在mysqld標簽下配置[mysqld]#主庫server-id為1，從庫不等于1server_id =1#開啟binlog日志log_bin=mysql-bin2）創(chuàng)建主從復制用戶
#登錄數(shù)據(jù)庫[root@db01 ~]# mysql -uroot -poldboy123#創(chuàng)建rep用戶mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';#replication全局,不能給單庫授權從庫操作:
1）修改配置文件
#修改db02配置文件[root@db02 ~]# vim /etc/my.cnf#在mysqld標簽下配置[mysqld]#主庫server-id為1，從庫不等于1server_id =5#重啟mysql[root@db02 ~]# /etc/init.d/mysqld restart#記錄主庫binlog及位置點(主庫沒數(shù)據(jù))mysql> show master status;#登陸數(shù)據(jù)庫[root@db02 ~]# mysql -uroot -poldboy123#執(zhí)行change master to 語句change master to master_host='10.0.0.51', master_user='rep', master_password='123', master_log_file='mysql-bin.000004', master_log_pos=68192 master_delay=#主庫有數(shù)據(jù)時:找起始位置點1.邏輯備份 zcat +備份文件 |head 25 或者查看relay-log.info2.物理備份 [root@db01 script]# cat /backup/full_2019-12-12-00/xtrabackup_binlog_infomysql-bin.00000115724master_log_file=master_log_pos=#恢復數(shù)據(jù),找截止點邏輯:mysqlbinlog --base64-output=decode-rows -vvv +mysql-bin.000001的絕對路徑物理:合并,--copy-back 先備份.在刪除dataIO線程
從庫上檢測
1.網(wǎng)路不通
ping 10.0.0.511.硬件層.路由,交換機,網(wǎng)路設備2.網(wǎng)路3.安全組規(guī)則4.差錯網(wǎng)線口2.端口不通
telnet 10.0.0.51 3306yum install -y tcpingtcping 172.16.1.51 3306mysql -urep -p1 -h172.16.1.51原因:1.防火墻 2.selinux解決:1.針對端口開放firewalld-cmd --add-service=mysql --add-port=3306/tcp 2.關閉selinux 3.用戶名錯誤
4.密碼錯誤
5.反向解析
skip_name_resolvesql線程是NO
1.從庫有該數(shù)據(jù),主庫要創(chuàng)建.操作對象已存在
從庫:a庫
主庫:要創(chuàng)建a庫
解決辦法: set global sql_slave_skip_counter=1 (生產(chǎn)環(huán)境不能交戶)
2.主庫有該數(shù)據(jù),從庫沒有,操作對象不存在(insert update delete drop truncate alter)
解決辦法:保證數(shù)據(jù)一致性,
連接主庫
1）user password ip port
2）網(wǎng)絡：不通，延時高，防火墻
請求binlog
1）binlog不存在或者損壞
更新relay-log和master.info
SQL線程
1）relay-log出現(xiàn)問題
2）從庫做寫入了
處理方法一：
#臨時停止同步mysql> stop slave;#將同步指針向下移動一個（可重復操作）mysql> set global sql_slave_skip_counter=1;#開啟同步mysql> start slave;處理方法二：
#編輯配置文件[root@db01 ~]# vim /etc/my.cnf#在[mysqld]標簽下添加以下參數(shù)slave-skip-errors=1032,1062,1007但是以上操作都是有風險存在的
處理方法三：
1）重新備份數(shù)據(jù)庫，恢復到從庫
2）給從庫設置為只讀
#在命令行臨時設置set global read_only=1;#在配置文件中永久生效read_only=1#grant all 中有(supper),可以創(chuàng)建,只能給其它權限,普通的主從復制可能存在不足
1）邏輯損壞怎么辦？
2）不能保證主庫的操作，從庫一定能做
3）高可用？自動failover？
4）過濾復制
企業(yè)中一般會延時3-6小時
延時從庫配置方法
#停止主從mysql>stop slave;#設置延時為180秒mysql>CHANGE MASTER TO MASTER_DELAY = 180;#開啟主從mysql>start slave;#查看狀態(tài)mysql> show slave status \GSQL_Delay: 603.延時從庫停止方法#停止主從mysql> stop slave;#設置延時為0,取消延時mysql> CHANGE MASTER TO MASTER_DELAY = 0;#開啟主從mysql> start slave;1.修改主庫從庫的配置文件主:server_id開啟:binlog從庫:配置server_id2.保證從庫和主庫的數(shù)據(jù)一致3.執(zhí)行chang master tomaster_host='',master_user='',master_password='',master_log_file='',master_log_pos= ,#找尋全量備份的點master_delay= ;思考問題：
總數(shù)據(jù)量級500G，正常備份去恢復需要1.5-2小時
1）配置延時3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;2）主庫
drop database db;3）怎么利用延時從庫，恢復數(shù)據(jù)？
提示：
1、從庫relaylog存放在datadir目錄下
2、mysqlbinlog 可以截取relaylog內容
3、show relay log events in 'db01-relay-bin.000001';
處理的思路：1）停止SQL線程
mysql> stop slave sql_thread;2）截取relaylog到誤刪除之前點
relay-log.info 獲取到上次運行到的位置點，作為恢復起點
1）停止SQL線程mysql> stop slave sql_thread;2）截取relaylog到誤刪除之前點relay-log.info 獲取到上次運行到的位置點，作為恢復起點全備mysqldump -uroot -p1 -A |gzip>/tmp/full1.sql起始點[root@db04 data]# cat relay-log.info 7./db04-relay-bin.000005283mysql-bin.000005截取截止點relay_log 文件[root@db04 data]# mysqlbinlog -uroot -p1 --start-position=59503 --stop-position=59691 db04-relay-bin.000002 >/tmp/yanchi3.sql將數(shù)據(jù)導入生產(chǎn)環(huán)境先導全備,在導增備從MYSQL5.5開始，支持半自動復制。之前版本的MySQL Replication都是異步（asynchronous）的，主庫在執(zhí)行完一些事務后，是不會管備庫的進度的。如果備庫不幸落后，而更不幸的是主庫此時又出現(xiàn)Crash（例如宕機），這時備庫中的數(shù)據(jù)就是不完整的。簡而言之，在主庫發(fā)生故障的時候，我們無法使用備庫來繼續(xù)提供數(shù)據(jù)一致的服務了。
半同步復制（Semi synchronous Replication）則一定程度上保證提交的事務已經(jīng)傳給了至少一個備庫。
出發(fā)點是保證主從數(shù)據(jù)一致性問題，安全的考慮。
5.5 出現(xiàn)概念，但是不建議使用，性能太差
5.6出現(xiàn)group commit 組提交功能，來提升開啟半同步復制的性能
5.7更加完善了，在group commit基礎上出現(xiàn)了MGR
5.7的增強半同步復制的新特性：after commit； after sync；
半同步復制開啟方法(等待ack返回時,主庫處于鎖表狀態(tài))
1）安裝（主庫）
#登錄數(shù)據(jù)庫[root@db01 ~]# mysql -uroot -poldboy123#查看是否有動態(tài)支持mysql> show global variables like 'have_dynamic_loading';#安裝自帶插件mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';#啟動插件mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;#設置超時mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;#修改配置文件[root@db01 ~]# vim /etc/my.cnf#在[mysqld]標簽下添加如下內容（不用重啟庫）[mysqld]rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000檢查安裝：mysql> show variables like'rpl%';mysql> show global status like 'rpl_semi%';2）安裝（從庫）
#登錄數(shù)據(jù)庫[root@mysql-db02 ~]# mysql -uroot -poldboy123#安裝slave半同步插件mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';#啟動插件mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;#重啟io線程使其生效mysql> stop slave io_thread;mysql> start slave io_thread;#編輯配置文件（不需要重啟數(shù)據(jù)庫）[root@mysql-db02 ~]# vim /etc/my.cnf#在[mysqld]標簽下添加如下內容[mysqld]rpl_semi_sync_slave_enabled =1注：相關參數(shù)說明
rpl_semi_sync_master_timeout=milliseconds
設置此參數(shù)值（ms）,為了防止半同步復制在沒有收到確認的情況下發(fā)生堵塞，如果Master在超時之前沒有收到任何確認，將恢復到正常的異步復制，并繼續(xù)執(zhí)行沒有半同步的復制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一個事務被提交,但Master沒有任何Slave的連接，這時不可能將事務發(fā)送到其它地方保護起來。默認情況下，Master會在時間限制范圍內繼續(xù)等待Slave的連接，并確認該事務已經(jīng)被正確的寫到磁盤上。
可以使用此參數(shù)選項關閉這種行為，在這種情況下，如果沒有Slave連接，Master就會恢復到異步復制。
測試半同步
#創(chuàng)建兩個數(shù)據(jù)庫，test1和test2mysql> create database test1;Query OK, 1 row affected (0.04 sec)mysql> create database test2;Query OK, 1 row affected (0.00 sec)#查看復制狀態(tài)mysql> show global status like 'rpl_semi%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 || Rpl_semi_sync_master_net_avg_wait_time | 768 || Rpl_semi_sync_master_net_wait_time | 1497 || Rpl_semi_sync_master_net_waits | 2 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status | ON || Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 884 || Rpl_semi_sync_master_tx_wait_time | 1769 || Rpl_semi_sync_master_tx_waits | 2 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 |#此行顯示2，表示剛才創(chuàng)建的兩個庫執(zhí)行了半同步| Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+14 rows in set (0.06 sec)#從庫查看mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || test1 || test2 |+--------------------+#關閉半同步（1:開啟 0:關閉）mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;#查看半同步狀態(tài)mysql> show global status like 'rpl_semi%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 || Rpl_semi_sync_master_net_avg_wait_time | 768 || Rpl_semi_sync_master_net_wait_time | 1497 || Rpl_semi_sync_master_net_waits | 2 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status | OFF | #狀態(tài)為關閉| Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 884 || Rpl_semi_sync_master_tx_wait_time | 1769 || Rpl_semi_sync_master_tx_waits | 2 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 || Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+14 rows in set (0.00 sec)#再一次創(chuàng)建兩個庫mysql> create database test3;Query OK, 1 row affected (0.00 sec)mysql> create database test4;Query OK, 1 row affected (0.00 sec)#再一次查看半同步狀態(tài)mysql> show global status like 'rpl_semi%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 || Rpl_semi_sync_master_net_avg_wait_time | 768 || Rpl_semi_sync_master_net_wait_time | 1497 || Rpl_semi_sync_master_net_waits | 2 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status | OFF || Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 884 || Rpl_semi_sync_master_tx_wait_time | 1769 || Rpl_semi_sync_master_tx_waits | 2 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 |#此行還是顯示2，則證明，剛才的那兩條并沒有執(zhí)行半同步否則應該是4| Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+14 rows in set (0.00 sec)注:不難發(fā)現(xiàn)，在查詢半同步狀態(tài)是，開啟半同步，查詢會有延遲時間，關閉之后則沒有主庫：
白名單:只記錄白名單中列出的庫的二進制日志
黑名單：不記錄黑名單列出的庫的二進制日志
從庫：
白名單：只執(zhí)行白名單中列出的庫或者表的中繼日志
黑名單：不執(zhí)行黑名單中列出的庫或者表的中繼日志
復制過濾配置：
[root@db01 data]# vim /data/3307/my.cnf #在[mysqld]標簽下添加replicate-do-db=world#關閉MySQLmysqladmin -S /data/3307/mysql.sock shutdown#啟動MySQLmysqld_safe --defaults-file=/data/3307/my.cnf &測試復制過濾：
第一次測試：
1）主庫：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use worldmysql> create table t1(id int);2）從庫查看結果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use worldmysql> show tables;第二次測試：
1）主庫：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use testmysql> create table tb1(id int); 2）從庫查看結果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use testmysql> show tables;