MySQL学习笔记之九：MySQL Replication
? ?Mysql內建的復制功能是構建大型、高性能應用程序的基礎。復制功能不僅有利于構建高性能的應用，同時也是高可用性、可擴展性、災難恢復、備份以及數據倉庫等工作的基礎。
一、復制概述
? 1、復制的功用
? ? ? 數據分布、負載均衡、備份、高可用性、MySQL升級測試
? 2、復制的工作原理
? ? ? ①主庫把數據更改記錄到二進制日志（binary log）中
? ? ? ②備庫將主庫的二進制日志復制到本地的中繼日志（relay log）中。首先，備庫會啟動一個I/O線程，跟主庫建立一個普通的客戶端連接，然后在主庫上啟動一個特殊的二進制轉儲（binlog dump）線程，此轉儲線程會讀取主庫上二進制日志中的事件。如果該線程追趕上了主庫，則進入睡眠狀態，直到主庫發送信號通知其有新的事件產生時才會被喚醒，備庫I/O線程會將接收到的事件記錄到中繼日志中。
? ? ? ③備庫的SQL線程從中繼日志中讀取事件本執行，從而實現備庫數據更新。
? 3、復制方式
? ? ? ①基于語句的復制：實際上是把主庫上執行的SQL語句在從庫上重放一遍，因此效率高，占用帶寬小，但不如基于行的復制精確，對于不確定性的語句（例如包含時間函數的語句）會有問題。另外這種復制是串行的，為了保證串行執行，需要加更多的鎖。
? ? ? ②基于行的復制：此時二進制日志記錄的是數據本身，這無疑會增加網絡帶寬消耗和I/O線程負載，優點是從庫無需sql語句重放，但無法判斷執行了哪些SQL語句
? ? ? ③混合模式，即上面兩種方式的組合
? ? ? mysql默認基于語句復制，建議采用基于行的復制；
? 4、mysql復制常用架構
? ? ? 一主一從
? ? ? 一主多從：常用于寫操作不頻繁，查詢量較大的環境中
? ? ? 主主互備：即兩臺mysql server互相將對方作為自己的master，避免了單點故障，主要用于對mysql寫操作比較頻繁的環境中
? ? ? 多源復制：即slave服務器可指向多個master服務器；MySQL 5.7和MariaDB 10支持，主要用于對mysql讀寫量都比較大的環境中
?★5、特別說明
? ? ? mysql復制集群中，每個server的Server ID必須唯一；
? ? ? 集群中如果有兩個或更多的master，只允許對不同的數據庫進行寫操作，否則在復制時會產生混亂；
? ? ? slave服務器只許讀，不許寫；
? ? ? 建議使用InnoDB作為默認存儲引擎，基于語句復制
二、主從復制
? 1、版本：從節點版本不能低于主節點
? 2、從哪里開始復制
? ? ?⑴從0開始：適用于主從均為新建立的服務器；
? ? ?⑵如果主服務器已經運行一段時間且存在不小的數據量：
? ? ? ? ①完全備份主服務器數據，并將數據恢復至從服務器；
? ? ? ? ?mysqldump --single-transaction --all-databases --master-data=2 --host=SERVER1 | mysql --host=SERVER2
? ? ? ? ? ?--master-data：該選項將binlog的位置和文件名追加到輸出文件中。如果為1，將會輸出CHANGE MASTER TO命令；如果為2，則在輸出的CHANGE MASTER TO命令前添加注釋。該選項會打開--lock-all-tables選項，除非另外指定--single-transaction。
? ? ? ? ②從服務器從備份時主服務器二進制日志所在位置開始復制；
? 3、配置過程
? ? ⑴master：
? ? ?①啟用二進制日志
? ? ?②定義server-id
? ? ?③創建有復制權限的賬號：
? ? ? ? ?GRANT REPLICATION SLAVE?ON *.* TO repluser@'192.168.30.%' IDENTIFIED BY 'replp@ss';
? ? ? ? ?FLUSH PRIVILEGES;
? ? ⑵slave：
? ? ?①啟用中繼日志
? ? ?②定義server-id
? ? ?③使用有復制權限的賬號連接master
? ? ? ? ?CHANGE MASTER TO option [, option] ...
? ? ? ? ? ?option:
? ? ? ? ? ?MASTER_BIND = 'interface_name'
? ? ? ? ? ?| MASTER_HOST = 'host_name' ? ?# 指明要連接的主節點
? ? ? ? ? ?| MASTER_USER = 'user_name' ? ?# 具有復制權限的賬號
? ? ? ? ? ?| MASTER_PASSWORD = 'password' ?# 上述用戶的密碼
? ? ? ? ? ?| MASTER_PORT = port_num
? ? ? ? ? ?| MASTER_CONNECT_RETRY = interval
? ? ? ? ? ?| MASTER_HEARTBEAT_PERIOD = interval
? ? ? ? ? ?| MASTER_LOG_FILE = 'master_log_name' ?# 復制起點，主節點上的二進制日志
? ? ? ? ? ?| MASTER_LOG_POS = master_log_pos ? ? # 復制起點，主節點上二進制日志中的事件位置
? ? ? ? ? ?......
? ? ? ? ?例如：CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=495;
? ? ?④啟動io thread以及sql thread：START SLAVE;
? ? ? ? ?也可單獨啟動IO_THREAD或者SQL_THREAD線程，如START SLAVE IO_THREAD;
? ? ?⑤查看從節點的狀態：SHOW SLAVE STATUS\G
? ? ?⑥查看線程狀態：SHOW PROCESSLIST;
? ? ?⑦相關文件：
? ? ? ? ?master.info：記錄了主庫帳號信息和I/O線程當前讀取到主庫的二進制文件的位置
? ? ? ? ?relay-log.info：記錄從庫的SQL線程當前讀取到中繼日志的位置
? ? ?⑧從庫如果不做級連復制或者備份，就不要開啟二進制日志（在配置文件中添加log_bin=off）
? 4、復制中要注意的問題
? ? ⑴如何限制從服務器只讀？
? ? ? ?①更改slave的全局服務器變量read-only為yes：
? ? ? ? ? ?動態：SET GLOBAL read_only = on;
? ? ? ? ? ?靜態：編輯配置文件
? ? ? ? ? ? ? ?[mysqld]
? ? ? ? ? ? ? ?read_only = on
? ? ? ? ?注意：此限制對于擁有super權限的用戶無效
? ? ? ?②阻止所有用戶執行寫操作：FLUSH TABLES WITH READ LOCK;
? ? ⑵如何保證復制的“安全”？
? ? ? ?master：
? ? ? ? ? 設置參數：sync_binlog = 1
? ? ? ? ? ? mysql處理commit語句，它將整個事務寫入二進制日志并將事務提交給存儲引擎中，如果開啟該選項，mysql每次在提交事務給存儲引擎之前會將二進制日志同步到磁盤上，保證在服務器崩潰時不會丟失事件。
? ? ? ? ? 如果使用InnoDB存儲引擎： ? ? ? ? ? ?
? ? ? ? ? ? innodb_flush_log_at_trx_commit = 1 （默認啟用）
? ? ? ? ? ? innodb_support_xa = on （默認啟用）
? ? ? ?slave：
? ? ? ? ? sync_master_info = 1
? ? ? ? ? sync_relay_log = 1
? ? ? ? ? sync_relay_log_info = 1
? ? ? ? ? 說明：
? ? ? ? ? ? slave從master復制binary log寫到relay log，指的是先寫到 “OS cache”的relay-log，而不是馬上刷新到磁盤上，什么時候刷新到磁盤還依賴于cache刷新時間。sync_relay_log #就表示每寫入幾次，觸發一次文件同步，其它同理；這三個選項是從MySQL 5.5才開始引進的
? 以下示例中，主庫node1: 192.68.30.10，從庫node2: 192.68.30.20；使用MySQL 5.6.30?
? ? 主庫：
[root@node1?~]#?vim?/etc/my.cnf [mysqld]basedir?=?/usr/local/mysql datadir?=?/mydata/data port?=?3306 server_id?=?1 socket?=?/tmp/mysql.sock skip-name-resolve log-bin?=?/mydata/binlogs/master-bin binlog_format?=?row sync_binlog?=?1 default_storage_engine?=?innodb??#從MySQL?5.5開始默認的存儲引擎就是InnoDB，這項可不設置[root@node1?~]#?service?mysqld?start Starting?MySQL?SUCCESS!? [root@node1?~]#?mysql?<?hellodb.sql???#生成一些數據 [root@node1?~]#?mysql ... mysql>?prompt?master? PROMPT?set?to?'master>?' master>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?hellodb????????????| |?mysql??????????????| |?performance_schema?| |?test???????????????| +--------------------+ 5?rows?in?set?(0.00?sec)#創建具有復制權限的賬號： master>?grant?replication?slave?on?*.*?to?repluser@'192.168.30.%'?identified?by?'replp@ss'; Query?OK,?0?rows?affected?(0.01?sec)master>?flush?privileges; Query?OK,?0?rows?affected?(0.00?sec)master>?exit Bye[root@node1?~]#?mysqldump?--single-transaction?--all-databases?--master-data=2?>?msback.sql???#完全備份 [root@node1?~]#?scp?msback.sql?root@node2:/root/ msback.sql??????????????????????????????100%??640KB?640.1KB/s???00:00? ? 從庫：
[root@node2?~]#?vim?/etc/my.cnf? [mysqld]basedir?=?/usr/local/mysql datadir?=?/mydata/data port?=?3306 server_id?=?2???#要與主庫不同 socket?=?/tmp/mysql.sock skip-name-resolve log-bin?=?/mydata/binlogs/slave-bin???#如果不是基于GTID復制或不打算讓slave充當其它slave的master，可不用開啟 binlog_format?=?row sync_binlog?=?1 relay-log?=?/mydata/relaylogs/slave-relay???#啟用中繼日志 sync_master_info?=?1 sync_relay_log?=?1 sync_relay_log_info?=?1 read_only?=?on???#只讀[root@node2?~]#?service?mysqld?start Starting?MySQL?SUCCESS! [root@node2?~]#?mysql?<?msback.sql???#先將主庫的備份數據恢復至從庫 [root@node2?~]#?grep?"^--?CHANGE"?msback.sql???#查找主庫備份那一刻的二進制日志文件位置 --?CHANGE?MASTER?TO?MASTER_LOG_FILE='master-bin.000004',?MASTER_LOG_POS=120; [root@node2?~]#?mysqlmysql>?prompt?slave>? PROMPT?set?to?'slave>?' slave>?change?master?to?master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\->?master_log_file='master-bin.000004',master_log_pos=120; Query?OK,?0?rows?affected,?2?warnings?(0.26?sec)slave>?start?slave; Query?OK,?0?rows?affected?(0.01?sec)slave>?show?slave?status\G ***************************?1.?row?***************************Slave_IO_State:?Waiting?for?master?to?send?eventMaster_Host:?192.168.30.10Master_User:?repluserMaster_Port:?3306Connect_Retry:?60Master_Log_File:?master-bin.000004Read_Master_Log_Pos:?120???#io?thread當前讀取到主庫的二進制文件的位置Relay_Log_File:?slave-relay.000002Relay_Log_Pos:?284Relay_Master_Log_File:?master-bin.000004Slave_IO_Running:?Yes???#io?thread線程的狀態Slave_SQL_Running:?YesReplicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0Last_Error:?Skip_Counter:?0Exec_Master_Log_Pos:?120Relay_Log_Space:?453Until_Condition:?NoneUntil_Log_File:?Until_Log_Pos:?0Master_SSL_Allowed:?NoMaster_SSL_CA_File:?Master_SSL_CA_Path:?Master_SSL_Cert:?Master_SSL_Cipher:?Master_SSL_Key:?Seconds_Behind_Master:?0???#落后于master服務器的時長 Master_SSL_Verify_Server_Cert:?NoLast_IO_Errno:?0Last_IO_Error:?Last_SQL_Errno:?0Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?1???#主庫的server_idMaster_UUID:?5b1eb4fa-270c-11e6-917c-000c2940359dMaster_Info_File:?/mydata/data/master.infoSQL_Delay:?0SQL_Remaining_Delay:?NULLSlave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?the?slave?I/O?thread?to?update?itMaster_Retry_Count:?86400Master_Bind:?Last_IO_Error_Timestamp:?Last_SQL_Error_Timestamp:?Master_SSL_Crl:?Master_SSL_Crlpath:?Retrieved_Gtid_Set:?Executed_Gtid_Set:?Auto_Position:?0 1?row?in?set?(0.00?sec)[root@node2?~]#?cat?/mydata/data/master.info???#master.info文件的初始數據是由change?master命令生成的?23 master-bin.000004 120???#一旦復制了新的日志，master_log_pos的值就會更新 repluser replp@ss 3306 60 0? ? 測試：
master>?create?database?testdb; Query?OK,?1?row?affected?(0.00?sec)master>?use?testdb Database?changed master>?create?table?students?(Name?char(30),Gender?enum('m','f')); Query?OK,?0?rows?affected?(0.03?sec)slave>?show?databases; +--------------------+ |?Database???????????| +--------------------+ ... |?testdb?????????????| +--------------------+ 6?rows?in?set?(0.00?sec)slave>?show?tables?from?testdb; +------------------+ |?Tables_in_testdb?| +------------------+ |?students?????????| +------------------+ 1?row?in?set?(0.00?sec)slave>?show?processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ |?Id?|?User????????|?Host??????|?db???|?Command?|?Time?|?State???????????????????????????????????????????????????????????????????????|?Info?????????????| +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ |??2?|?system?user?|???????????|?NULL?|?Connect?|?5759?|?Slave?has?read?all?relay?log;?waiting?for?the?slave?I/O?thread?to?update?it?|?NULL?????????????| |??3?|?root????????|?localhost?|?NULL?|?Query???|????0?|?init????????????????????????????????????????????????????????????????????????|?show?processlist?| |??4?|?system?user?|???????????|?NULL?|?Connect?|?5759?|?Waiting?for?master?to?send?event????????????????????????????????????????????|?NULL?????????????| +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 3?rows?in?set?(0.00?sec)[root@node2?~]#?cat?/mydata/data/master.info?23 master-bin.000004 356 192.168.30.10 repluser replp@ss 3306 60 0? 5、半同步復制
? ? ? MySQL 5.5之前的復制都是異步的，主服務器在將更新操作寫入二進制日志文件中后，不用管從服務器是否已經完成復制，就可以自由處理其它事務處理請求。異步復制能提供較高的性能，但無疑易造成主/從服務器數據的不一致。
? ? ? MySQL 5.5開始引入半同步復制功能，此功能是由google開發的一個插件實現的。半同步復制要求主庫提交的每一個事務，至少有一個備庫成功接收后，才能繼續提交下一個。
? ? ? 半同步復制的概念詳解：
? ? ? ? ①當slave主機連接到master時，能夠查看其是否已開啟半同步復制功能。
? ? ? ? ②當master上開啟半同步復制的功能時，至少應該有一個slave開啟此功能。此時，一個線程在master上提交事務將受到阻塞，直到得知一個已開啟半同步復制功能的slave已收到此事務的所有事件，或等待超時。
? ? ? ? ③當一個事務的事件都已寫入relay-log中且已刷新到磁盤，slave才會告知已收到。在 master實例上，有一個專門的線程(ack_receiver)接收備庫的響應消息。
? ? ? ? ④如果等待超時，也就是master沒被告知已收到，此時master會自動轉換為異步復制模式。當至少一個半同步的slave趕上了，master與其slave自動轉換為半同步復制。
? ? ? ? ⑤半同步復制的功能要在master，slave都開啟，若只開啟一邊，它依然為異步復制。
? ? ? master：
? ? ? ? ?INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
? ? ? ? ?SHOW GLOBAL VARIABLES LIKE '%semi%';
? ? ? ? ?SET GLOBAL rpl_semi_sync_master_enabled = on;
? ? ? ? ?SET GLOBAL rpl_semi_sync_master_timeout = 5000; ? #等待從服務器確認的超時時長，單位為毫秒，超時則轉為異步模式
? ? ? ? ?[mysqld]
? ? ? ? ? ?rpl_semi_sync_master_enabled = on
? ? ? ? ? ?rpl_semi_sync_master_timeout = 5000
? ? ? ? ?SHOW GLOBAL STATUS LIKE 'rpl_semi%';
? ? ? slave：
? ? ? ? ?INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
? ? ? ? ?SET GLOBAL rpl_semi_sync_slave_enabled = on;
? ? ? ? ?然后只需重啟io thread：
? ? ? ? ? ? STOP SLAVE IO_THREAD;
? ? ? ? ? ? START SLAVE IO_THREAD;
? ? ? ? ?[mysqld]
? ? ? ? ? ?rpl_semi_sync_slave_enabled = on
主庫： master>?install?plugin?rpl_semi_sync_master?soname?'semisync_master.so'; Query?OK,?0?rows?affected?(0.20?sec)master>?show?global?variables?like?'%semi%'; +------------------------------------+-------+ |?Variable_name??????????????????????|?Value?| +------------------------------------+-------+ |?rpl_semi_sync_master_enabled???????|?OFF???| |?rpl_semi_sync_master_timeout???????|?10000?| |?rpl_semi_sync_master_trace_level???|?32????| |?rpl_semi_sync_master_wait_no_slave?|?ON????| +------------------------------------+-------+ 4?rows?in?set?(0.00?sec)master>?set?global?rpl_semi_sync_master_enabled?=?on; Query?OK,?0?rows?affected?(0.01?sec)master>?set?global?rpl_semi_sync_master_timeout?=?5000; Query?OK,?0?rows?affected?(0.00?sec)master>?show?global?variables?like?'%semi%'; +------------------------------------+-------+ |?Variable_name??????????????????????|?Value?| +------------------------------------+-------+ |?rpl_semi_sync_master_enabled???????|?ON????| |?rpl_semi_sync_master_timeout???????|?5000??| |?rpl_semi_sync_master_trace_level???|?32????| |?rpl_semi_sync_master_wait_no_slave?|?ON????| +------------------------------------+-------+ 4?rows?in?set?(0.00?sec)[root@node1?~]#?vim?/etc/my.cnf [mysqld] ... rpl_semi_sync_master_enabled?=?on rpl_semi_sync_master_timeout?=?5000從庫： slave>?install?plugin?rpl_semi_sync_slave?soname?'semisync_slave.so'; Query?OK,?0?rows?affected?(0.11?sec)slave>?show?global?variables?like?'%semi%'; +---------------------------------+-------+ |?Variable_name???????????????????|?Value?| +---------------------------------+-------+ |?rpl_semi_sync_slave_enabled?????|?OFF???| |?rpl_semi_sync_slave_trace_level?|?32????| +---------------------------------+-------+ 2?rows?in?set?(0.00?sec)slave>?set?global?rpl_semi_sync_slave_enabled?=?on; Query?OK,?0?rows?affected?(0.00?sec)slave>?stop?slave?io_thread; Query?OK,?0?rows?affected?(0.01?sec)slave>?start?slave?io_thread; Query?OK,?0?rows?affected?(0.00?sec)slave>?show?global?status?like?'rpl_semi%'; +----------------------------+-------+ |?Variable_name??????????????|?Value?| +----------------------------+-------+ |?Rpl_semi_sync_slave_status?|?ON????| +----------------------------+-------+ 1?row?in?set?(0.00?sec)[root@node2?mydata]#?vim?/etc/my.cnf [mysqld] ... rpl_semi_sync_slave_enabled?=?on主庫： master>?show?global?status?like?'rpl_semi%'; +--------------------------------------------+-------+ |?Variable_name??????????????????????????????|?Value?| +--------------------------------------------+-------+ |?Rpl_semi_sync_master_clients???????????????|?1?????|???#已連接的啟用了半同步復制的從服務器數量 |?Rpl_semi_sync_master_net_avg_wait_time?????|?0?????| |?Rpl_semi_sync_master_net_wait_time?????????|?0?????| |?Rpl_semi_sync_master_net_waits?????????????|?0?????| |?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??????|?0?????| |?Rpl_semi_sync_master_tx_wait_time??????????|?0?????| |?Rpl_semi_sync_master_tx_waits??????????????|?0?????| |?Rpl_semi_sync_master_wait_pos_backtraverse?|?0?????| |?Rpl_semi_sync_master_wait_sessions?????????|?0?????| |?Rpl_semi_sync_master_yes_tx????????????????|?0?????|?????#表示通過半同步復制到從庫的事務數 +--------------------------------------------+-------+ 14?rows?in?set?(0.00?sec)? 6、發送復制事件到其它從庫
? ? ? MySQL支持多級復制，即一個slave可以充當其它服務器的master。這需要開啟log_slave_updates選項，在開啟該選項后，MySQL會將其執行過的事件記錄到自己的二進制日志中。這樣其它從庫就可以復制其二進制日志。
? ? ? 復制級別不宜過多，以免數據落后太多。
? ? ? 可以讓一個從庫專門做分發主庫，而不用負責查詢工作，這時候可將它的存儲引擎修改為blackhole（不生成任何實際數據）。? ? ??
? 7、復制過濾器
? ? ? 讓slave僅復制有限的幾個數據庫，甚至于僅復制某數據庫內有限的幾張表的機制；
? ? ? 有兩種方案：
? ? ? ? ⑴在主節點上過濾：在向二進制日志記錄事件時，僅記錄指定數據庫的相關操作；
? ? ? ? ? ?binlog_do_db = ? ? # 數據庫白名單
? ? ? ? ? ?binlog_ignore_db = ? # 數據庫黑名單
? ? ? ? ⑵在從節點上過濾：僅從中繼日志中讀取指定的數據庫或表的相關事件并應用于本地；
? ? ? ? ? ?replicate_do_db =?
? ? ? ? ? ?replicate_ignore_db =?
? ? ? ? ? ?replicate_db_table = DB_NAME.TB_NAME
? ? ? ? ? ?replicate_ignore_table =?
? ? ? ? ? ?replicate_wild_do_table = ? #可使用通配符
? ? ? ? ? ?replicate_wild_ignore_table =
? ? ? 復制過濾容易出問題，不到萬不得已，不建議使用；如果實在要使用，建議在從節點上過濾
? 8、基于ssl的復制
? ? ⑴配置主庫為CA服務器
[root@node1?~]#?cd?/etc/pki/CA [root@node1?CA]#?(umask?077;openssl?genrsa?-out?private/cakey.pem?2048)???#生成密鑰對 Generating?RSA?private?key,?2048?bit?long?modulus ....................+++ ............................+++ e?is?65537?(0x10001) [root@node1?CA]#?openssl?req?-new?-x509?-key?private/cakey.pem?-out?cacert.pem?-days?3650??#CA自簽證書 ... Country?Name?(2?letter?code)?[XX]:CN State?or?Province?Name?(full?name)?[]:Zhejiang Locality?Name?(eg,?city)?[Default?City]:Hangzhou Organization?Name?(eg,?company)?[Default?Company?Ltd]:Dongpu Organizational?Unit?Name?(eg,?section)?[]:tech Common?Name?(eg,?your?name?or?your?server's?hostname)?[]:ca.dongpu.com Email?Address?[]:ca@dongpu.com [root@node1?CA]#?touch?index.txt?serial [root@node1?CA]#?echo?01?>?serial [root@node1?CA]#?mkdir?csr? ? ⑵為主服務器創建證書
[root@node1?CA]#?mkdir?/usr/local/mysql/ssl [root@node1?CA]#?cd?/usr/local/mysql/ssl [root@node1?ssl]#?(umask?077;openssl?genrsa?-out?master.key?2048) Generating?RSA?private?key,?2048?bit?long?modulus .......................+++ ...................+++ e?is?65537?(0x10001) [root@node1?ssl]#?openssl?req?-new?-key?master.key?-out?master.csr???#生成證書簽署請求 ... Country?Name?(2?letter?code)?[XX]:CN State?or?Province?Name?(full?name)?[]:Zhejiang Locality?Name?(eg,?city)?[Default?City]:Hangzhou Organization?Name?(eg,?company)?[Default?Company?Ltd]:Dongpu Organizational?Unit?Name?(eg,?section)?[]:tech Common?Name?(eg,?your?name?or?your?server's?hostname)?[]:mysqlmaster@dongpu.com Email?Address?[]:mysqlmaster@dongpu.com ... [root@node1?ssl]#?openssl?ca?-in?master.csr?-out?master.crt?-days?1000???#CA簽署證書 ... 1?out?of?1?certificate?requests?certified,?commit??[y/n]y Write?out?database?with?1?new?entries Data?Base?Updated [root@node1?ssl]#?cp?/etc/pki/CA/cacert.pem?./ [root@node1?ssl]#?ls cacert.pem??master.crt??master.csr??master.key [root@node1?ssl]#?chown?-R?mysql.mysql?. [root@node1?ssl]#?ll???#注意，要確保密碼文件、證書文件等對于mysql用戶可讀 total?20 -rw-r--r--?1?mysql?mysql?1464?Jun??1?01:30?cacert.pem -rw-r--r--?1?mysql?mysql?4686?Jun??1?02:31?master.crt -rw-r--r--?1?mysql?mysql?1074?Jun??1?02:31?master.csr -rw-------?1?mysql?mysql?1675?Jun??1?03:27?master.key? ? ⑶為從服務器創建證書，參考上述步驟，略
[root@node2?ssl]#?ls cacert.pem??slave.crt??slave.csr??slave.key? ? ⑷在主庫上啟用ssl認證，并指定有復制權限的用戶要以ssl的方式連接
mysql>?show?global?variables?like?'%ssl%'; +---------------+----------+ |?Variable_name?|?Value????| +---------------+----------+ |?have_openssl??|?DISABLED?| |?have_ssl??????|?DISABLED?| |?ssl_ca????????|??????????| |?ssl_capath????|??????????| |?ssl_cert??????|??????????| |?ssl_cipher????|??????????| |?ssl_crl???????|??????????| |?ssl_crlpath???|??????????| |?ssl_key???????|??????????| +---------------+----------+ 9?rows?in?set?(0.04?sec)[root@node1?~]#?vim?/etc/my.cnf [mysqld] ... ssl_ca?=?/usr/local/mysql/ssl/cacert.pem ssl_cert?=?/usr/local/mysql/ssl/master.crt ssl_key?=?/usr/local/mysql/ssl/master.key[root@node1?~]#?service?mysqld?restart Shutting?down?MySQL..?SUCCESS!? Starting?MySQL..?SUCCESS! mysql>?show?global?variables?like?'%ssl%'; +---------------+---------------------------------+ |?Variable_name?|?Value???????????????????????????| +---------------+---------------------------------+ |?have_openssl??|?YES?????????????????????????????| |?have_ssl??????|?YES???#顯示已啟用??????????????????????????| |?ssl_ca????????|?/usr/local/mysql/ssl/cacert.pem?| |?ssl_capath????|?????????????????????????????????| |?ssl_cert??????|?/usr/local/mysql/ssl/master.crt?| |?ssl_cipher????|?????????????????????????????????| |?ssl_crl???????|?????????????????????????????????| |?ssl_crlpath???|?????????????????????????????????| |?ssl_key???????|?/usr/local/mysql/ssl/master.key?| +---------------+---------------------------------+ 9?rows?in?set?(0.00?sec)mysql>?revoke?all?privileges,?grant?option?from?repluser@'192.168.30.%'; Query?OK,?0?rows?affected?(0.01?sec)mysql>?grant?replication?slave,replication?client?on?*.*?to?repluser@'192.168.30.%'?identified?by?'replp@ss'?require?ssl; Query?OK,?0?rows?affected?(0.01?sec) #?在grant命令中指定require?ssl選項mysql>?flush?privileges; Query?OK,?0?rows?affected?(0.02?sec)? ? ⑸在從庫上也啟用ssl認證
[root@node2?~]#?vim?/etc/my.cnf [mysqld] ... ssl_ca?=?/usr/local/mysql/ssl/cacert.pem ssl_cert?=?/usr/local/mysql/ssl/slave.crt ssl_key?=?/usr/local/mysql/ssl/slave.key[root@node2?~]#?service?mysqld?restart Shutting?down?MySQL..?SUCCESS!? Starting?MySQL..?SUCCESS! [root@node2?~]#?less?/mydata/data/master.info 23 master-bin.000014 721 192.168.30.10 repluser replp@ss 3306 60 0 [root@node2?~]#?mysql #啟用ssl后需要在change?master?to命令中指定ssl相關選項重新指向主庫 mysql>?change?master?to?master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\->?master_log_file='master-bin.000014',master_log_pos=721,\->?master_ssl=1,\->?master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',\->?master_ssl_cert='/usr/local/mysql/ssl/slave.crt',\->?master_ssl_key='/usr/local/mysql/ssl/slave.key'; Query?OK,?0?rows?affected,?2?warnings?(0.19?sec)mysql>?start?slave; Query?OK,?0?rows?affected?(0.01?sec)mysql>?show?slave?status\G ***************************?1.?row?***************************...Master_SSL_Allowed:?YesMaster_SSL_CA_File:?/usr/local/mysql/ssl/cacert.pemMaster_SSL_CA_Path:?Master_SSL_Cert:?/usr/local/mysql/ssl/slave.crtMaster_SSL_Cipher:?Master_SSL_Key:?/usr/local/mysql/ssl/slave.key...Slave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?the?slave?I/O?thread?to?update?it...[root@node2?data]#?vim?master.info?23 master-bin.000024 120 192.168.30.10 repluser replp@ss 3306 60 1???#是否啟用ssl的標志位 /usr/local/mysql/ssl/cacert.pem/usr/local/mysql/ssl/slave.crt/usr/local/mysql/ssl/slave.key 0 1800.000? 9、為從庫設定新的主庫
? ? ? 在從庫停止復制線程，而后重新設定CHANGE MASTER TO命令即可；
? ? ⑴計劃內提升一個從庫為主庫：
? ? ? ?①停止向老的主庫寫入數據；
? ? ? ?②讓計劃提升為主庫的從庫趕上主庫；
? ? ? ?③提升從庫為主庫
? ? ? ?④修改其它從庫的指向
? ? ⑵計劃外提升一個從庫為主庫：
? ? ? ?①確定哪個從庫的數據為最新最全；
? ? ? ?②等待所有的從庫執行從主庫那復制而來的生成的中繼日志；
? ? ? ?③在提升為主庫的從庫上STOP SLAVE；而后，讓各從庫指向新的主庫；
? ? ? ?④再次比較主庫和各從庫上的兩個參數：
? ? ? ? ? ?Master_Log_File，Read_Master_Log_Pos
三、雙主互備
? (1)雙節點都得創建具有復制權限的用戶；
? (2)雙節點都得啟用中繼日志和二進制日志；
? (3)為保證具有自動增長功能的字段能正確生成ID，需要配置兩個節點分別使用偶數或奇數ID號；
? (4)互相把對方配置為自己的主節點；
? A節點：
? ? ?[mysqld]
? ? ?server-id=1
? ? ?log-bin=mysql-bin
? ? ?relay-log=mysql-relay
? ? ?auto_increment_increment=2
? ? ?auto_increment_offset=1
? B節點：
? ? ?[mysqld]
? ? ?server-id=2
? ? ?log-bin=mysql-bin
? ? ?relay-log=mysql-relay
? ? ?auto_increment_increment=2
? ? ?auto_increment_offset=2
四、MySQL 5.6中關于復制的幾個新特性
? 1、master.info和relay-log.info支持存儲在表中：
? ? ? master_info_repository = table
? ? ? relay_log_info_repository = table
? ? ? 對應的表的名稱為slave_master_info、slave_relay_log_info，位于mysql庫中
? 2、multi-threads slave
? ? ? 5.6以前的從服務器，一個io線程負責復制binary log，還有一個sql線程負責執行relay log中的sql語句。如果主服務器的數據更新相當頻繁，而從服務器由于某些原因跟不上，會導致從服務器落后比較長的時間。5.6之后可采用多個sql線程，每個sql線程處理不同的database，提高了并發性能。
? ? ? slave_parallel_workers = # (0為禁用，最大1024）
? 3、延時slave
? ? ? 在每個slave sql線程執行的時候都要等延遲時間到后進行下一個操作；
? ? ? MASTER_DELAY=#，單位為秒，在CHANGE MASTER TO命令中指定，例如：
? ? ? ? ?CHANGE MASTER TO MASTER_DELAY=120
? ? ? 優點：在一定程度上防止了誤操作，比如說刪表等等；
? ? ? ? ? 可以一定程度上作為有效的數據庫備份，無需再另行備份；
? ? ? 案例：誤刪了test庫中的test1表，沒有備份，只有延時slave
? ? ? ? ①先在主庫上查找刪除表的位置：
? ? ? ? ? ? SHOW BINLOG EVENTS\G
? ? ? ? ? ? 假設刪除表的位置為Log_name:master-bin.000001 Pos:884
? ? ? ? ②然后在從庫上：
? ? ? ? ? ? STOP SLAVE;
? ? ? ? ? ? CHANGE MASTER TO MASTER_DELAY = 0
? ? ? ? ? ? START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='mater-bin.000001',MASTER_LOG_POS=884;
? 4、GTID
? ? ⑴概述
? ? ? ?GTID（global transaction identifier，全局事務標識）是對于一個已提交事務的編號，并且是一個全局唯一的編號，由UUID+TID組成的，其中UUID是一個MySQL實例的唯一標識，TID代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增。
? ? ? ? ?例如：7800a22c-95ae-11e4-983d-080027de205a:10
? ? ? ?GTID用來代替傳統的復制方法。不再使用binlog+pos開啟復制，而是使用master_auto_postion=1的方式自動匹配GTID斷點進行復制。
? ? ? ?在傳統的slave端，binlog是不用開啟的，但是在GTID中，slave端的binlog必須開啟，目的是記錄執行過的GTID（強制）
? ? ⑵GTID的作用
? ? ? 假設有如上一個MySQL復制架構，Server A宕機，需要將業務切換到Server B上。同時，又需要將Server C的復制源改成Server B。復制源修改的命令語法很簡單即CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS=#。而難點在于，由于同一個事務在每臺機器上所在的binlog名字和位置都不一樣，那么找到Server C當前同步停止點所對應的Server B上的二進制日志位置就成了難題。這種問題在MySQL 5.6的GTID出現后，就顯得非常的簡單。由于同一事務的GTID在所有節點上的值一致，那么根據Server C當前停止點的GTID就能唯一定位到Server B上的GTID。甚至由于MASTER_AUTO_POSITION功能的出現，我們都不需要知道GTID的具體值，直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可完成failover的工作。
? ? ⑶GITD工作原理
? ? ? ?①master更新數據時，會在事務前產生GTID，一同記錄到binlog日志中。
? ? ? ?②slave端的i/o線程將變更的binlog，寫入到本地的relay log中。
? ? ? ?③sql線程從relay log中獲取GTID，然后對比slave端的binlog是否有記錄。
? ? ? ?④如果有記錄，說明該GTID的事務已經執行，slave會忽略。
? ? ? ?⑤如果沒有記錄，slave就會從relay log中執行該GTID的事務，并記錄到binlog。
? ? ⑷開啟GTID必須啟用如下幾項：
? ? ? ?gtid_mode = on
? ? ? ?enforce_gtid_consistency = on
? ? ? ?log_slave_updates = on
? ? ? ?這三個選項在主從服務器上都要啟用
? ? ?其它可選項： ? ? ??
? ? ? ?binlog_checksum = NONE or CRC32（默認為CRC32）
? ? ? ? ? 每個session都會產生checksum值，并且寫入到binlog?
? ? ? ?master_verify_checksum = on
? ? ? ? ? Master 當從binlog dump事件的時候會校驗checksum值
? ? ? ?slave_sql_verify_checksum = on（默認已啟用）
? ? ? ? ? SQL線程當從relay log讀取事件應用到slave之前會校驗checksum值
? ? ? ?report_host = XXX
? ? ? ? ? 從庫向主庫報告的主機名或IP地址，可在主庫使用SHOW SLAVE HOSTS命令查看
? ? ? ?report_port = #
? ? ? ? ? 從庫向主庫報告的連接端口號，默認為3306 ? ? ??
? ? ⑸啟用GTID后，從庫可這樣指向主庫：
? ? ? ? CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replp@ss', MASTER_AUTO_POSITION=1;（不用指定具體的二進制日志位置了）
? ? ⑹MariaDB中使用GTID需要做的修改：
? ? ? ?①不支持的參數：
? ? ? ? ? gtid-mode = on ? #MariaDB 10默認已啟用gtid
? ? ? ? ? enforce-gtid-consistency = on
? ? ? ?②修改的參數：
? ? ? ? ? slave_parallel_workers參數修改為slave_parallel_threads
? ? ? ?③連接至主庫使用的命令：
? ? ? ? ? 一個新的參數：MASTER_USE_GTID={current_pos/slave_pos/no}
? ? ? ? ? change master to master_host="127.0.0.1",master_port=3310,master_user="root",master_use_gtid=current_pos;
? 5、啟用GTID做主從復制示例
? ? ?主庫：
[root@node1?~]#?vim?/etc/my.cnf [mysqld] ... gtid_mode?=?on enforce_gtid_consistency?=?on log_slave_updates?=?on master_verify_checksum?=?on[root@node1?~]#?service?mysqld?restart Shutting?down?MySQL..?SUCCESS!? ? ?從庫：
? ? ?測試：
主庫： mysql>?show?slave?hosts;???#此命令可查看有哪些已連接的從庫 +-----------+---------------+------+-----------+--------------------------------------+ |?Server_id?|?Host??????????|?Port?|?Master_id?|?Slave_UUID???????????????????????????| +-----------+---------------+------+-----------+--------------------------------------+ |?????????2?|?192.168.30.20?|?3306?|?????????1?|?f61472ed-2714-11e6-91b4-000c29bd6823?| +-----------+---------------+------+-----------+--------------------------------------+ 1?row?in?set?(0.00?sec)mysql>?create?database?wuxia; Query?OK,?1?row?affected?(0.02?sec)從庫： mysql>?show?databases; +--------------------+ |?Database???????????| +--------------------+ ...... |?wuxia??????????????| +--------------------+? ? ?基于GTID的復制的其它相關問題可參考博客http://www.tuicool.com/articles/rua2emE
五、多源復制
? ?MySQL 5.7.2和MariaDB 10開始支持多源復制，即一個slave可指向多個master；
? ?以下是MariaDB 10中從庫指向多個主庫的語法： ? ??
? ? ? CHANGE MASTER ['connection_name'] TO ....
? ? ? FLUSH RELAY LOGS ['connection_name']
? ? ? MASTER_POS_WAIT(....,['connection_name'])
? ? ? RESET SLAVE ['connection_name'] [ALL]
? ? ? SHOW RELAYLOG ['connection_name'] EVENTS
? ? ? SHOW SLAVE ['connection_name'] STATUS
? ? ? SHOW ALL SLAVES STATUS
? ? ? START SLAVE ['connection_name'...]]
? ? ? START ALL SLAVES ...
? ? ? STOP SLAVE ['connection_name'] ...
? ? ? STOP ALL SLAVES ...
? ? 示例： ? ? ?
? ? ? CHANGE MASTER 'master1' TO MASTER_HOST= '192.168.30.10',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000028',master_log_pos=1485;
? ? ? CHANGE MASTER 'master2' TO MASTER_HOST= '192.168.30.20',MASTER_USER='repluser',MASTER_PASSWORD='replp@ss',MASTER_LOG_FILE='master-bin.000023',master_log_pos=639;
? ? ? START ALL SLAVES;
? ? ? SHOW ALL SLAVES STATUS\G
? ? ? 也可單獨操作某個connection：
? ? ? ? START SLAVE 'master1';
? ? ? ? SHOW SLAVE 'master1' STATUS\G
轉載于:https://blog.51cto.com/9124573/1785454
總結
以上是生活随笔為你收集整理的MySQL学习笔记之九：MySQL Replication的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 解决android.permission
- 下一篇： 我是如何用Worktile进行敏捷开发的