當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL学习笔记之九：MySQL Replication

發布時間：2025/5/22 数据库 124 豆豆

生活随笔收集整理的這篇文章主要介紹了 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

? ? 測試：

? 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的方式連接

? ? ⑸在從庫上也啟用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!

? ? ?從庫：

[root@node2?~]#?vim?/etc/my.cnf [mysqld] ... gtid_mode?=?on enforce_gtid_consistency?=?on log_slave_updates?=?on slave_parallel_workers?=?3???#啟動三個SQL線程 report_host?=?192.168.30.20 report_port?=?3306[root@node2?~]#?rm?-f?/mydata/data/master.info #刪除現有的master.info只是為了測試后面change?master命令中的master_auto_position選項是否能實現復制位置的自動確定 [root@node2?~]#?service?mysqld?restart Shutting?down?MySQL..?SUCCESS! [root@node2?~]#?mysql mysql>?show?global?variables?like?'%gtid%'; +---------------------------------+-------+ |?Variable_name???????????????????|?Value?| +---------------------------------+-------+ |?binlog_gtid_simple_recovery?????|?OFF???| |?enforce_gtid_consistency????????|?ON????| |?gtid_executed???????????????????|???????| |?gtid_mode???????????????????????|?ON????| |?gtid_owned??????????????????????|???????| |?gtid_purged?????????????????????|???????| |?simplified_binlog_gtid_recovery?|?OFF???| +---------------------------------+-------+ 7?rows?in?set?(0.00?sec)mysql>?change?master?to?master_host='192.168.30.10',master_user='repluser',master_password='replp@ss',\->?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',\->?master_auto_position=1; Query?OK,?0?rows?affected,?2?warnings?(0.01?sec)mysql>?start?slave; Query?OK,?0?rows?affected,?1?warning?(0.01?sec)mysql>?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.000026Read_Master_Log_Pos:?151......Auto_Position:?1???#表示已開啟自動位置確定 1?row?in?set?(0.00?sec)mysql>?show?processlist;???#確實已啟動三個sql線程 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ |?Id?|?User????????|?Host??????|?db???|?Command?|?Time?|?State???????????????????????????????????????????????????????????????????????|?Info?????????????| +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ |??6?|?root????????|?localhost?|?NULL?|?Query???|????0?|?init????????????????????????????????????????????????????????????????????????|?show?processlist?| |??7?|?system?user?|???????????|?NULL?|?Connect?|???68?|?Waiting?for?master?to?send?event????????????????????????????????????????????|?NULL?????????????| |??8?|?system?user?|???????????|?NULL?|?Connect?|???68?|?Slave?has?read?all?relay?log;?waiting?for?the?slave?I/O?thread?to?update?it?|?NULL?????????????| |??9?|?system?user?|???????????|?NULL?|?Connect?|???68?|?Waiting?for?an?event?from?Coordinator???????????????????????????????????????|?NULL?????????????| |?10?|?system?user?|???????????|?NULL?|?Connect?|???68?|?Waiting?for?an?event?from?Coordinator???????????????????????????????????????|?NULL?????????????| |?11?|?system?user?|???????????|?NULL?|?Connect?|???68?|?Waiting?for?an?event?from?Coordinator???????????????????????????????????????|?NULL?????????????| +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 6?rows?in?set?(0.00?sec)

? ? ?測試：

主庫： 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进行敏捷开发的