一、主從複製基礎概念
在瞭解主從複製之前必須要了解的就是數據庫的二進制日誌(binlog),主從複製架構大多基於二進制日誌進行。
1.1 二進制日誌管理說明
二進制日誌在哪？如何設置位置和命名？
在my.cnf文件中使用 log-bin = 指定；命名規則為 mysql-bin.000000 （後為6位數字）
二進制日誌位置：
mysql> show variables like '%log_bin%' ; +---------------------------------+-----------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------+ | log_bin | ON | | log_bin_basename | /application/mysql/data/mysql-bin | | log_bin_index | /application/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------+ 6 rows in set (0.06 sec)
日誌命名：
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2979 | | mysql-bin.000002 | 120 | +------------------+-----------+ 2 rows in set (0.00 sec)
二進制日誌記錄什麼？二進制日誌中記錄的是一個個完成的事件
二進制日誌格式是怎樣的？推薦使用row格式
查看當前使用的日誌格式：
mysql> show variables like '%format%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | binlog_format | ROW | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | time_format | %H:%i:%s | +--------------------------+-------------------+ 8 rows in set (0.00 sec)
二進制日誌如何滾動？每次重啟都會刷新日誌，也可以通過命令進行刷新 reset master;
二進制日誌用來幹嘛？備份恢復，起始點的備份恢復
二進制日誌的操作命令？查看都有哪些二進制日誌
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2979 | | mysql-bin.000002 | 167 | | mysql-bin.000003 | 120 | +------------------+-----------+ 3 rows in set (0.00 sec)
查看當前使用的二進制日誌文件：
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
binlog相關詳情參照：
http://www.cnblogs.com/clsn/p/8087678.html#_label6
1.2 mysql傳統備份方式和缺陷
二進制日誌備份mysqldump必須有數據庫服務器完成邏輯工作，需要更多地cpu週期邏輯備份還原速度慢：需要MySQL加載和解釋語句、轉化存儲格式、重建引擎xtrabackup文件大不總是可以跨平臺、操作系統和MySQL版本1.3 MySQL主從複製能為我們做什麼
高可用輔助備份分擔負載二、MySQL主從複製介紹
2.1 複製技術
作用：
保證數據安全(異機實時備份)保證服務持續運行（宕機接管）主從複製實現基本原理
自帶功能，複製是 MySQL 的一項功能，允許服務器將更改從一個實例複製到另一個實例。主服務器將所有數據和結構更改記錄到二進制日誌中。從屬服務器從主服務器請求該二進制日誌並在本地應用其內容。即通過把主庫的binlog傳送到從庫，從新解析應用到從庫。2.2 複製架構
mysql複製的應用常見場景：
應用場景1：從服務器作為主服務器的實時數據備份
應用場景2：主從服務器實現讀寫分離，從服務器實現負載均衡
應用場景3：把多個從服務器根據業務重要性進行拆分訪問
傳統的 MySQL複製提供了一種簡單的主–從複製方法，有一個主，以及一個或多個從。
主節點執行和提交事務，然後將它們（異步地）發送到從節點，以重新執行（在基於語句的複製中）或應用（在基於行的複製中）。
這是一個 shared-nothing 的系統，默認情況下所有 server 成員都有一個完整的數據副本。
（圖）MySQL 異步複製
還有一個半同步複製，它在協議中添加了一個同步步驟。
這意味著主節點在提交時需要等待從節點確認它已經接收到事務。只有這樣，主節點才能繼續提交操作。
（圖）MySQL 異步複製
在上面的兩個圖片中，可以看到傳統異步 MySQL 複製協議（以及半同步）的圖形展示。
藍色箭頭表示在不同 server 之間或者 server 與 client 應用之間的信息交互。
2.3 MySQL主從複製原理介紹
複製過程：
開啟binlog日誌，通過把主庫的binlog傳送到從庫，從新解析應用到從庫。複製需要3個線程（dump、io、sql）完成，5.6從庫多個sql。複製是異步的過程。主從複製是異步的邏輯的SQL語句級的複製。複製前提：
主服務期一定要打開二進制日誌必須兩臺服務器（或者是多個實例）從服務器需要一次數據初始化如果主從服務器都是新搭建的話，可以不做初始化主從怎麼實現的？
通過二進制日誌2.4 執行原理--第一次開啟主從過程
從庫通過手工執行change master to 語句連接主庫，提供了連接的用戶一切條件：（user、password、port、ip）並且讓從庫知道，二進制日誌的起點位置（file名 position號）start slave從庫的IO和主庫的dump線程建立連接從庫根據change master to 語句提供的file名和position號，IO線程向主庫發起binlog的請求主庫dump線程根據從庫的請求，將本地binlog以events的方式發給從庫IO線程從庫IO線程接收binlog evnets，並存放到本地relay-log中，傳送過來的信息，會記錄到master.info中。到此為止，一次主從複製就完成
一旦主從運行起來：就不需要手工執行change master to，因為信息都會被存放到master.info（user、password、port、ip，上次獲取過的binlog信息file和position）中。
詳細的mysql replication 過程
三、 主從搭建配置
本次主從搭建使用mysql多實例進行實驗。多實例配置參考文檔進行配置：
http://www.cnblogs.com/clsn/p/8038964.html#_label8
3.1 多實例數據庫slave配置
系統環境說明：
[root@db02 ~]# cat /etc/redhat-release CentOS release 6.9 (Final) [root@db02 ~]# uname -r 2.6.32-696.el6.x86_64 [root@db02 ~]# /etc/init.d/iptables status iptables: Firewall is not running. # 注意：務必關閉防火牆（iptables selinux） [root@db02 ~]# getenforce Disabled [root@db02 ~]# mysql --version mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper
1、啟動多實例數據庫
[root@db02 ~]# /data/3306/mysql start Starting MySQL... [root@db02 ~]# /data/3307/mysql start Starting MySQL...
2、配置文件說明：
master 配置文件說明：
[root@db02 ~]# cat /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data log-bin = /data/3306/mysql-bin server-id = 6 # server id 不能相同 skip_name_resolve = 0 # 跳過域名解析參數 [mysqld_safe] log-error=/data/3306/mysql_3306.err pid-file=/data/3306/mysqld.pid
slave 配置文件說明：
[root@db02 ~]# cat /data/3307/my.cnf [client] port = 3307 socket = /data/3307/mysql.sock [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql datadir = /data/3307/data log-bin = /data/3307/mysql-bin server-id = 7 # server id 不能相同 skip_name_resolve = 0 # 跳過域名解析參數 read_only = 1 # 從庫只讀 （非root用戶 ） [mysqld_safe] log-error=/data/3307/mysql_3307.err pid-file=/data/3307/mysqld.pid
3、在主庫創建複製用戶
登陸到主數據庫中：
mysql -uroot -p123 -S /data/3306/mysql.sock
創建授權用戶，注意是slave用戶。
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
4、初始化從庫數據
備份主庫當前數據
mysqldump -uroot -p123 -A -B -F --master-data=2 -S /data/3306/mysql.sock >/tmp/full.sql
部分參數說明：
-F 刷新二進制日誌--master-data [=＃]這會導致二進制日誌的位置和文件名被追加到輸出中。如果等於1，則將其打印為CHANGE MASTER命令; 如果等於2，那麼該命令將以註釋符號為前綴。到從庫進行恢復
mysql -uroot -p123 -S /data/3307/mysql.sock
恢復備份的數據
set sql_log_bin=0; source /tmp/full.sql
5、開啟從庫複製
查看備份的當前使用的文件及POS號
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
登入數據庫，進行slave配置。
mysql -uroot -p123 -S /data/3307/mysql.sock CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120; start slave; # 啟動從庫複製
該配置想關說明可以通過 help 獲得。
mysql> help CHANGE MASTER TO CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
3.2 測試主從同步
查看slave庫的狀態，主要查看：
Slave_IO_Running與Slave_SQL_Running是否都為Yes
主庫進行操作，在從庫驗證
[root@db02 ~]# mysql -uroot -p123 -S /data/3306/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> create database clsn; Query OK, 1 row affected (0.00 sec)
在從庫上可以看到該數據庫已創建
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | clsn | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
至此mysql主從複製就搭建完成
3.3 忘記數據庫密碼？
shell> /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --skip-networking & mysql> update user set password=password('123') where user='root' and host='localhost'; mysql> flush privileges;
3.4 主從複製狀態失敗的原因？
Last_IO_Error: error reconnecting to master 'repl@10.0.0.52:3306' - retry-time: 60 retries: 1
原因：
主機沒啟動，或者宕機，檢查主庫的狀態。網絡通信問題，使用ping命令進行檢查；或使用mysql命令進行shell端登陸測試防火牆，selinux（務必檢查）複製用戶和密碼、端口號、地址有問題，使用mysql命令進行shell端登陸測試。mysql自動解析，會將連接的IP解析成主機名（skip-name-resolve = 0）寫入my.cnf文件即可。從庫IO異常關閉，通過show slave status \G 進行查看。四、MySQL主從複製常見問題
4.1 從庫binlog落後主庫binlog？
從庫記錄的已經主庫已經給我傳送的binlog事件的座標，一般在繁忙的生產環境下會落後於主庫
show master status\G --- 主 show slave status \G --- 從 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 729
落後太遠的原因：
硬件條件有關的，機器磁盤IO性能不足。主要還是網絡問題，網絡傳輸的性能。主庫存放二進制日誌的存儲性能太低，建議binlog日誌存咋SSD中。主庫DUMP線程太繁忙，主要發生在一主多從的環境下。從庫IO線程太忙人為控制（delay節點、延時節點 ）4.2 主庫update，從庫遲遲的沒有更新
特殊情況：日誌已經傳過來了，數據並沒有同步
一般情況：
沒開啟SQL線程傳的東西有問題（你要做的事情，我提前已經做了，不想重複做了，然後他就死了）4.3 主從複製延時配置(從庫配置)
停止從庫複製
mysql>stop slave; Query OK, 0 rows affected (0.01 sec)
修改延時參數，MASTER_DELAY，單位位S （秒）。
mysql>CHANGE MASTER TO MASTER_DELAY = 30; Query OK, 0 rows affected (0.07 sec)
啟動從庫複製
mysql>start slave; Query OK, 0 rows affected (0.07 sec)
查看配置是否生效
mysql> show slave status \G …… SQL_Delay: 30
4.4 從庫安全配置（其他用戶只讀）
修改my.cnf配置文件，添加只讀參數
read_only = 1 ====> 控制普通用戶 innodb_read_only = 1 ====> 控制root用戶，正常情況不要加
添加完成後重啟數據庫
mysql> show variables like '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.00 sec)
延時從庫: delay節點、延時節點
命令行設置
stop slave; #<==臨時停止同步開關。 set global sql_slave_skip_counter = 1 ; #<==將同步指針向下移動一個，如果多次不同步，可以重複操作。 start slave;
在配置文件修改，設置要跳過的pos
/etc/my.cnf slave-skip-errors = 1032,1062,1007
在mysql中可以跳過某些錯誤,但是最好的解決辦法，重新搭建主從複製。
4.6 延時節點概念 --> SQL線程延時？
Last_SQL_Errno: 0 Last_SQL_Error:
原因:
主庫做操作的對象，在從庫不存在主庫做操作的對象屬性不一致主庫做操作的對象，從庫已經存在……
4.7 Slave_*_Running：？
4.8 中繼日誌座標
Relay_Log_File 和 Relay_Log_Pos 列標識從屬服務器中繼日誌中 SQL 線程已經執行的最近事件的座標。
這些座標對應於 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列標識的主服務器二進制日誌中的座標。
如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的輸出遠遠落後於 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 線程的座標）
這表示 SQL 線程（而不是 I/O 線程）中存在延遲。即，它表示複製日誌事件快於執行這些事件。
4.9 單一主從需要改變的地方
從庫的作用
1、相當於實時備份
2、使用從庫備份
3、一主多從應對讀多的業務需求
如果，從庫只做備份服務器用，那麼主庫的壓力會不減反增。因為，所有的業務都在主庫實現，讀和寫，dump線程讀取並投遞binlog
解決方案：
可不可以挪走一部分讀業務到從庫，讀寫分離一主多從應對讀多的業務需求，一旦發展成這個架構，dump線程投遞binlog的壓力更大多級主從，採用中間庫緩解主庫dump的壓力，會出現中間庫瓶頸的問題，選擇blackhole引擎，看性能與安全的權衡