MySQL Replication：
Master/Slave
Master: write/read
Slaves: read
為什么？
冗余：promte（提升為主），異地災備
人工
工具程序
負載均衡：轉移一部分“讀”請求；
支援安全的備份操作：
…
主/從架構：
異步復制：master只需要完成自己的數據庫操作即可。至于slaves是否收到二進制日志，是否完成操作，不用關心。MYSQL的默認設置。
半同步復制：master只保證slaves中的一個操作成功，就返回，其他slave不管。這個功能，是由google為MYSQL引入的。
一主多從；
一從一主；
級聯復制；分層次,每一級服務器上一級服務器同步數據,并傳遞給下一級的服務器
循環復制；
雙主復制；
一從多主（不建議）：
每個主服務器提供不同的數據庫；可以使用多線程復制，但是每個主服務器必須是提供不同的數據庫
==========================================================================
主從復制配置：（異步復制）
從服務器需要啟動兩個重要的線程：
第一個：從主服務器不斷同步時間到本地的中繼日志文件中 — > IO線程
第二個：從本地中繼日志讀取時間進行重放(replay) — > SQL線程
單主模型：
配置前準備：
時間同步：可以編輯 /etc/chrony.conf 文件添加合適的服務器時間源，然后重啟服務
復制的開始位置：
從0開始；
從主服務器備份中恢復到從節點后啟動的復制，指示比從零開始多了一步（從主服務器上備份數據庫到從服務器上恢復，然后加上binlog做時間點恢復）；
主從服務器mysqld程序版本不一致？
mysql版本只能是主服務器版本低于從服務器，或者與從服務器一致；但是從服務器版本不能低于主服務器
—————————————————————————————————————————————————
主服務器：
配置文件my.cnf
server_id=1（不能與其他節點相同）
log-bin=master-log
innodb_file_per_table = ON
skip_name_resolve = ON
啟動服務：
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#為了安全起見，只需要授予最小權限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，創建賬戶的時候二進制日志是否開啟，開啟了也會記錄創建用戶語句，視情況而定時候需要跳過；
mysql> FLUSH PRIVILEGES;
—————————————————————————–
從服務器：
配置文件my.cnf
server_id=2（不能與其他節點相同）
relay_log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
#注意：從服務器沒有必要的話，可以不啟動二進制日志
啟動服務：
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
mysql> SET @@global.read_only=1 #開啟只讀功能，但是對于root用戶以及super權限的用戶都無效
mysql> show slave hosts; #查看slave的連接狀態
—————————————————————————–
—————————————————————————–
雙主模型：
要點：
時間同步：可以編輯 /etc/chrony.conf 文件添加合適的服務器時間源，然后重啟服務
雙方版本：需要一致
中繼日志：雙方均要啟用中繼日志（relay-log）
二進制日志：雙方都要啟動二進制日志
自動增長字段：一方使用奇數，另一方使用偶數，避免重疊
—————————————————————————–
—————————————————————————–
主服務器（server_id=1）：
配置文件my.cnf
server_id=1（不能與其他節點相同）
log-bin=master-log
relay-log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 1 (設置字段增長以1開始，使用奇數)
auto_increment_increment = 2 (設置字段每次增長2)
啟動服務：（雙方均要創建可用于復制表的用戶，只需要最低權限，以及互相添加為主服務器）
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#為了安全起見，只需要授予最小權限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，創建賬戶的時候二進制日志是否開啟，開啟了也會記錄創建用戶語句，視情況而定時候需要跳過；
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
mysql> SET @@global.read_only=1 #開啟只讀功能，但是對于root用戶以及super權限的用戶都無效
—————————————————————————–
—————————————————————————–
主服務器（server_id=2）：
配置文件my.cnf
server_id=2（不能與其他節點相同）
log-bin=master-log
relay-log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 2 (設置字段增長以2開始，使用偶數)
auto_increment_increment = 2 (設置字段每次增長2)
啟動服務：（雙方均要創建可用于復制表的用戶，只需要最低權限，以及互相添加為主服務器）
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
#為了安全起見，只需要授予最小權限REPLICATION SLAVE,REPLICATION CLIENT就可以了，而且需要注意，創建賬戶的時候二進制日志是否開啟，開啟了也會記錄創建用戶語句，視情況而定時候需要跳過；
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
mysql> SHOW SLAVE STATUS;
—————————————————————————–
MariaDB [(none)]> show slave status\G #開啟從服務器后的狀態參數解析
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.35.1
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 1078
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: No
Slave_SQL_Running: No 由于還沒有啟動復制功能，所以這兩項為NO
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: 這些為過濾項，默認為空，及全部復制；也可以指定復制（忽略）的表或者庫
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1078
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key: #基于ssl復制的功能選項
Seconds_Behind_Master: NULL #檢查落后服務器多少秒，如果落后的秒數比價大，可以重啟IO線程（先STOP SLAVE IO_THREAD；然后再START SLAVE IO_THREAD）
Master_SSL_Verify_Server_Cert: No #是否啟用基于ssl功能進行復制
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: #線程的錯誤號
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
—————————————————————————–
—————————————————————————–
雙主模型弊端：
在第二個節點創建數據庫
MariaDB [(none)]> create database mydb;
在第一個節點創建一個邊，并定義自動增長的行
MariaDB [mydb]> create table tbl1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30));
返回第二節點，并查看在第二節點上創建的table，并插入數據（到此可以看出雙主互相復制）
MariaDB [(none)]> use mydb
MariaDB [mydb]> desc tbl1;
MariaDB [mydb]> insert into tbl1 (name) values ('stu1');
由于定義了第二節點auto_increment_offset = 2 ， 所以有關自動增長字段的數據都是從二開始，并每次使用都自增加2
在第一節點查看并插入數據
上圖就是設置雙主關于增長字段的跳躍性的弊端，只要不沖突，應該不會有問題。所以設置雙主模型最好有一個全局序列符號生成器，需要增長字段的時候，由序列符號生成器提供，而不是服務器自己定義。
—————————————————————————–
—————————————————————————–
以下為用到的命令解析
START SLAVE #啟動從服務器的復制功能
MariaDB [(none)]> HELP START SLAVE
Name: 'START SLAVE'
Description:
Syntax:
START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
[thread_type [, thread_type] … ]
thread_type: IO_THREAD | SQL_THREAD
—————————————————————————–
—————————————————————————–
CHANGE MASTER TO #在從服務器上設置主服務器信息的命令
MariaDB [(none)]> HELP CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
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 #從主服務器的二進制日志文件的哪個pos開始復制
| RELAY_LOG_FILE = 'relay_log_name' #復制到從服務器的哪個中繼日志文件，一般使用默認值
| RELAY_LOG_POS = relay_log_pos #復制到從服務器中繼日志文件的哪個，一般使用默認值pos位置
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list) #一從多住的選項，一般不建議一從多主；
server_id_list:
[server_id [, server_id] … ]
—————————————————————————–
—————————————————————————–
STOP SLAVE #停止從服務器的復制功能
MariaDB [hellodb]> HELP STOP
Name: 'STOP SLAVE'
Description:
Syntax:
STOP SLAVE [thread_types]
thread_types:
[thread_type [, thread_type] … ]
thread_type: IO_THREAD | SQL_THREAD
—————————————————————————–
—————————————————————————–
總結：
復制時應該注意的問題：
1、從服務設定為“只讀”；（雙主模式無需設置）
在從服務器啟動read_only，但僅對非SUPER權限的用戶有效；
阻止所有用戶：
mysql> FLUSH TABLES WITH READ LOCK;
2、盡量確保復制時的事務安全
在master節點啟用參數：
sync_binlog = ON #每次事務提交的時候，都立即將二進制日志時間都重內存同步到磁盤中，能確保從服務器能立即得到事件，而且能保護本地數據安全
如果用到的是InnoDB存儲引擎：
innodb_flush_logs_at_trx_commit=ON #在事務提交時，立即刷寫事務日志從內存到磁盤上
innodb_support_xa=ON #支持分布式事務
#這兩項啟動起來，能在一定程度上確保從服務器能夠立即得到主服務器的最新事件
3、從服務器意外中止時盡量避免自動啟動復制線程
由于數據是重要的，服務器能意外終止的時間都是奇異的，所以，終止后不要讓從服務器自動啟動復制線程，等人工手動排查后才從新開啟復制線程；有可能復制線程復制到一半的時候出現意外，復制線程或許沒有這種功能—–不知道時候需要再次復制上次中斷的事件
4、從節點：設置參數
每一個從服務器都會保存一個文件 relay-log-info，是記錄在主服務器上復制二進制日志的位置，以及本地中繼日志的位置，為避免產生繁忙的IO操作，這些參數都是先保存在內存上的，到一定程度才會同步到磁盤上的，所以不安全
sync_master_info=ON
sync_relay_log_info=ON
以上兩項為立即刷寫數據從內存到磁盤
===================================================================
===================================================================
===================================================================
===================================================================
半同步復制
每次有時間提交的時候，主服務器完成本地的所有寫入操作（事務日志，二進制日志），還要確保這個事件已經寫入到從服務器的中繼日志中以及完成重放，并報告主服務器已經完成寫入操作了，最后主服務器才能告訴客戶端數據已經寫入，雖然影響性能，但是數據安全性提高了。
半同步復制需要安裝Google的開源插件
主服務器需要的插件 –> semisync_master.so
從服務器需要的插件 –> semisync_slave.so
支持多種插件的位置：/usr/lib64/mysql/plugins/
需要安裝方可使用：
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
—————————————————————————–
在主從復制的基礎上開始做：
主節點：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安裝模塊
SHOW PLUGIN; #查看安裝模塊的情況
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
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
MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
從節點：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
rpl_semi_sync_slave_enabled OFF
rpl_semi_sync_slave_trace_level 32
MariaDB [mydb]> STOP SLAVE IO_THREAD;
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
MariaDB [mydb]> START SLAVE IO_THREAD;
判斷方法：
主節點：
MariaDB [mydb]> SELECT @@global.rpl_semi_sync_master_clients；
或者：
MariaDB [mydb]> SHOW STATUS LIKE ’%semi%‘；
| Rpl_semi_sync_master_clients 半同步復制的連接個數
| Rpl_semi_sync_master_net_avg_wait_time 主節點網絡等待平均時長
| Rpl_semi_sync_master_net_wait_time 主節點網絡等待的累積時長
| Rpl_semi_sync_master_net_waits 主節點網絡等待的累積次數
| Rpl_semi_sync_master_no_times 主節點關閉半同步復制的次數
| Rpl_semi_sync_master_no_tx 主節點等待超時的次數
| Rpl_semi_sync_master_status 是否為半同步復制狀態
| Rpl_semi_sync_master_timefunc_failures 時間函數未正常工作的次數
| Rpl_semi_sync_master_tx_avg_wait_time 主節點等待事務提交的平均時長
| Rpl_semi_sync_master_tx_wait_time 主節點等待事務提交的總時長
| Rpl_semi_sync_master_tx_waits 主節點等待事務提交的次數
| Rpl_semi_sync_master_wait_pos_backtraverse 改變當前等待最小二進制日志的次數
| Rpl_semi_sync_master_wait_sessions 當前有多少個session 因為slave 的回復而造成等待
| Rpl_semi_sync_master_yes_tx 主節點收到從節點回復的次數
===================================================================
===================================================================
復制過濾器：
僅復制有限一個或幾個數據庫相關的數據，而非所有；由復制過濾器進行；
有兩種實現思路：
(1) 主服務器過濾
主服務器僅向二進制日志中記錄有關特定數據庫相關的寫操作；
壞處：其它庫的point-recovery將無從實現；
好處：節約網絡IO以及磁盤IO
binlog_do_db= #白名單控制
binlog_ignore_db= #黑名單控制
如果基于上述控制，及在主節點用二進制日志文件控制，則其他庫的二進制日志信息無法得到保存，當需要數據恢復的時候，是無法恢復沒有保存二進制日志信息的數據庫的信息，慎用！
(2) 從服務器過濾
從服務器的SQL THREAD僅重放關注數據庫或表相關的事件，并將其應用于本地；
壞處：網絡IO和磁盤IO；
好處：數據安全性更高
MariaDB [mydb]> show slave status\G
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table= #wild為允許使用通配符
Replicate_Wild_Ignore_Table= #wild為允許使用通配符
由于是GLOBAL參數，只有root用戶才有權限修改，但是建議寫在配置文件中/etc/my.cnf
#通常不會在表級別過濾數據
===================================================================
===================================================================
基于SSL復制
前提：啟用SSL功能；
===================================================================
===================================================================
復制的監控和維護：
(1) 清理日志：PURGE
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };
binary：二進制日志文件
master：日志文件
清除前先備份，或者復制文件出來，再清理，過一段時間確實無用，再作左后的刪除
————————————————————————————–
(2) 復制監控
MASTER:
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
SLAVE:
SHOW SLAVE STATUS;
判斷從服務器是否落后于主服務器：
Seconds_Behind_Master: 0 如果主從服務器均不繁忙，但是從服務器依然落后于主服務器，可以重啟從服務器的 IO_THREAD （先stop slave IO_THREAD 然后 start slave IO_THREAD）
————————————————————————————–
(3) 如何確定主從節點數據是否一致？
通過表的CHECKSUM檢查；
使用percona-tools中pt-table-checksum；
————————————————————————————–
(4) 主人數據不一致時的修復方法？
重新復制；
數據集過大，可以使用單表導入導出；
===================================================================
===================================================================
原創文章，作者：hunter，如若轉載，請注明出處：http://www.www58058.com/60958
