MySQL的複製解決什麼樣的問題，MySQL的二進位日誌的格式對數據的影響，如何實現主從複製，MySQL的複製拓撲圖 ...
MySQL複製解決了什麼問題？
1、實現在不同伺服器上的數據分佈
2、利用二進位日誌增量進行
3、不需要太多的帶寬
4、但是使用基於行的複製在進行大批量的更改時會對帶寬頻來一定的壓力，特別是跨IDC環境下進行複製
5、實現在不同服務上的數據分佈
6、實現數據讀取的負載均衡、需要其它組件配合完成、使用DNS輪訓的方式把程式的讀連接到不同的備份資料庫
7、使用LVS，Haproxy這樣的代理方式
8、實現了數據讀取的負載均衡
9、增強了數據安全性
10、實現資料庫高可用和故障切換
11、實現資料庫線上升級MySQL二進位日誌
基於段的格式binlog_format=STATMENT
優點：
日誌記錄量相對較小，節約了磁碟及I/O網路
只對一條記錄修改或者插入
row格式所產生的日質量小於段產生的日誌量
缺點：
必須要記錄上下文信息
保證語句在從伺服器上執行結果和在主伺服器上一致
基於行的日誌格式binlog_format=ROW
優點：
使用MySQL主從複製更加安全
對每一行輸幾局的修改比基於段的複製高效
缺點：
記錄日誌量較大
binlog_row_image=[FULL]MINIMAL|NOBLOG
混合日誌格式binlog_format=MIXED
特點:
1、根據SQL語句由系統決策在基於段和基於行的日誌格式中進行選擇
2、數據量的大小由所執行的SQL語句決定
如何選擇二進位日誌的格式？！
建議
Binlog_format=mixed
Binlog_fromat=row (如果是在同一個機房內，同一個IDC機房內考慮複製數據的安全性，建議使用此選項)
如果使用該格式，建議設置Binlog_row_image=minimal (可以減少網路、磁碟I/O的負載)
MySQL二進位日誌格式對複製的影響
基於SQL語句的複製(STATMENT)
主庫會記錄進行修改的SQL語句，備庫會讀取重放SQL語句
優點：
1、生成的日質量少，節省網路傳輸的I/O
2、並不強制要求主從資料庫的表定義完全相同
3、相比基於行的複製的方式更加的靈活
缺點：
1、對於非確定性的事件，無法保證主從數據賦值數據的一致性
2、對於存儲過程，觸發器，自定義函數進行修改也可能造成數據不一致
3、對比與基於行的複製方式在從上執行時需要更多的行鎖
基於行的複製：
優點：
1、可以應用在任何SQL的複製包括非確定函數，存儲過程等
2、可以減少資料庫鎖的使用
缺點：
1、要求主從資料庫的表結構相同，否則可能會中斷複製
2、無法在從上單獨執行觸發器MySQL複製工作方式
首先來個圖來說明
上圖的工作流程講解
1、主將變更寫入到二進位
2、從庫讀取主的二進位日誌變更並寫入到relay_log中
3、在從上重放relay_log中的日誌
基於SQL段(statment)的日誌是在從庫上重新執行記錄的SQL語句
基於行(row)日誌則是在從庫上直接應用對資料庫行的修改配置MySQL複製
基於日誌點的複製配置步驟
1、主庫上開啟binlog的設置，只記錄增刪改
修改/etc/my.cnf配置文件，並添加修改如下數據
bin_log = mysql-bin （binlog日誌的名稱，意思就是binlog的名稱以mysql-bin開頭）
server_id = 100 （動態參數，可以通過在MySQL的命令行中進行修改set global server_id=100）
2、在主DB伺服器上建立複製賬號
CREATE USER 'repl'@'IP段' IDENTIFIED BY 'repl用戶的登錄密碼';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip段';
3、配置從資料庫伺服器
修改/etc/my.cnf
bin_log = mysql-bin
server_id = 101
relay_log = mysql-relay-bin （中繼日誌的名稱，預設是主機名，建議自己定義個名稱，避免更改主機名以後帶來不便）
log_slave_update = on [可選] (是否把從伺服器的重放二進位日誌記錄到本機的二進位日誌中，以作為其他從伺服器的主)
read_only = on [可選] (是否允許沒有沒有sql線程的用戶進行寫操作)
4、在主庫進行鎖表，並拿到binlog的日誌點，進行主庫的備份並把備份拷貝到從庫上,備份兩種方式如下
mysqldump --master-data --single-transaction --triggers --routines --all-databases -uroot -p --lock-tables >> all.sql
xtrabackup --slvae-info
5、啟動複製鏈路
CHANGE MASTER TO MASERT_HOST='mast_host_ip',
MASTER_USER=‘repl’,
MASTER_PASSWORD='repl用戶登錄密碼',
MASTER_LOG_FILE='mysql_log_file_name',
MASTER_LOG_POS=4;
主從複製實例演示
1、準備兩台伺服器主機，一臺為MySQL的主，一臺為MySQL的從
MySQL主伺服器的ip地址：192.168.1.2
MySQL從伺服器的ip地址：192.168.1.3
2、首先修改MySQL主服務的配置文件，加入如下信息
]# vim /etc/my.cnf
log-bin=mysql-bin
binlog_format=mixed
server-id=1
expire_logs_days=10
3、修改MySQL從伺服器的配置文件,加入如下信息(如果需要從伺服器作為其他的從伺服器主，加入bin_log否則不需要)
]# vim /etc/my.cnf
bin_log=mysql-bin
server_id=2
relay_log=mysql-relay-bin
log_slave_update=on
read_only=on
4、主庫上創建主從同步賬號，併進行許可權分配
~]# mysql -uroot -p
mysql> CREATE USER 'repl'@'192.168.1.3' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.3';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
5、主庫進行鎖表備份數據,可以略過備份系統庫--ignore-table=database.table_name
~]# mkdir mysql_backup
~]# cd mysql_backup/
~]# mysqldump --master-data --single-transaction --triggers --routines --all-databases --lock-tables -uroot -p >> all.sql
6、把主伺服器的MySQL備份的資料庫文件拷貝到從伺服器上
~]# scp all.sql [email protected]:/root/
7、從伺服器的初始化操作
~]# mysql -uroot -p < all.sql
8、執行change master命令連接主庫
首先需要找到二進位日誌的文件名稱，以及備份的位置點信息
~]# grep 'CHANGE MASTER TO MASTER_LOG_FILE' all.sql
下麵是查找到的結果
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=1717;
~]# mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.2',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=1717;
Query OK, 0 rows affected (0.01 sec)
9、啟動主從複製，從庫執行
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000042
Read_Master_Log_Pos: 1717
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 404
Relay_Master_Log_File: mysql-bin.000042
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 1717
Relay_Log_Space: 700
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:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
備註：
執行這條命令的時候，發現報了一個錯誤ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO，這個錯誤出現的原因是因為server_id的不一致致使的，執行show variables like 'server_id;發現server_id的值是0，並沒有生效，需要修改server_id即可set global server_id=2;
10、回到主服務的MySQL中對任意一個表進行插入數據測試，然後在回到從伺服器上看相應的表中是否有數據，有即表示主從同步已經實現~~~基於日誌點的賦值配置步驟的優缺點
優點：
1、是MySQL最早支持的複製技術，Bug相對較少
2、對SQL查詢沒有任何限制
3、故障處理比較容易
缺點：
1、故障轉義時重新獲取新主的日誌點信息比較的困難
基於GTID複製的優缺點
GTID的複製是從MySQL5.6開始支持的功能
什麼是GTID？
GTID即全局事務ID，起保證為每一個在主上提交的事務在複製的急群中可以生成一個唯一的ID
GTID=source_id:transaction_id
GTID複製的相關參數
主庫的/etc/my.cnf的配置文件參數
bin_log = /usr/local/mysql/log/mysql-bin
server_id = 100
gtid_mode = on
enforce_gtid_consistency
log_slave_updates = on
從庫/etc/my.cnf的配置文件參數
server_id = 101
relay_log = /usr/local/mysql/log/relay_log
gtid_mode = on
enforce_gtid_consistency
建議從庫中開啟的參數
log-slave-updates = on
read_only = on
master_info_repository = TABLE
relay_log_info_repository =TABLE
啟動基於GTID的複製
CHANGE MASTER TO MASERT_HOST='mast_host_ip',
MASTER_USER=‘repl’,
MASTER_PASSWORD='repl用戶登錄密碼',
MASTER_AUTO_POSITION=1;
主從複製基於GTID
1、準備兩台伺服器主機，一臺為MySQL的主，一臺為MySQL的從
MySQL主伺服器的ip地址：192.168.1.5
MySQL從伺服器的ip地址：192.168.1.2
2、首先修改MySQL主服務的配置文件，加入如下信息
]# vim /etc/my.cnf
server-id = 1
gtid_mod = on
binlog_format = mixed
expire_logs_days = 10
log_slave_updates=on
enforce_gtid_consistency = on
log-bin = /usr/local/mysql/log/mysql-bin
3、修改MySQL從伺服器的配置文件,加入如下信息(如果需要從伺服器作為其他的從伺服器主，加入bin_log否則不需要)
]# vim /etc/my.cnf
binlog_format=mixed
server-id = 2
gtid_mode = on
expire_logs_days = 10
log_slave_updates = on
enforce_gtid_consistency = on
master-info-repository = TABLE
relay-log-info-repository = TABLE
log_bin = /usr/local/mysql/log/mysql-bin
relay_log = /usr/local/mysql/log/relay-log
4、主庫上創建主從同步賬號，併進行許可權分配
~]# mysql -uroot -p
mysql> CREATE USER 'repl'@'192.168.1.2' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
5、主庫進行鎖表備份數據,可以略過備份系統庫
~]# mkdir mysql_backup
~]# cd mysql_backup/
~]# mysqldump --master-data=2 --single-transaction --triggers --routines --all-databases --set-gtid-purged=OFF --lock-tables -uroot -p >> all2.sql
6、把主伺服器的MySQL備份的資料庫文件拷貝到從伺服器上
~]# scp all2.sql [email protected]:/root/
7、從伺服器的初始化操作
~]# mysql -uroot -p < all2.sql
8、從庫執行change maset to語句，進行GTID主從複製
~]# mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.5',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_AUTO_POSITION=1;
9、啟動主從複製，從庫執行
mysql> start slave;
10、回到主服務的MySQL中對任意一個表進行插入數據測試，然後在回到從伺服器上看相應的表中是否有數據，有即表示主從同步已經實現~~~MySQL複製拓撲
在MySQL7.7之前，一個主庫只能有一個從庫，MySQL5.7以後支持一主多從架構
一主多從的複製拓撲
用途
1、為不同業務使用不同的從庫,根據不同的業務特點，使用不同的存儲引擎，分割前後臺查詢，把不同的查詢分配到從庫上，以此來創建索引提升性能
2、將一臺從庫放到遠程IDC中，用作災備恢復
3、多個從庫來分擔主庫的負載，可以分擔讀負載(主庫負責寫，查詢交給多個從庫)主-主複製拓撲
主主模式下的主-主複製的配置註意事項
1、兩個主中所操作的表最好能夠分開
2、使用下麵兩個參數控制自增ID的生成
auto_increment_increment = 2 (一臺為1，3，5，7，9，另外一臺的2，4，6，8，10）
auto_increment_offset = 1 | 2 （每次自增的值）
主備模式下的主-主複製的配置註意事項
1、只有一臺主伺服器對外提供服務
2、一臺伺服器處於只讀狀態並且作為熱備使用
3、在對外提供服務的主庫出現故障或是計劃性的維護時才會進行切換
4、使原來的備庫成為主庫，而原來的主庫則會成為新的備庫，並處理只讀或是下線狀態，待維護完畢後重新上線
5、確保兩台伺服器上的初始數據相同
6、確保兩台伺服器上的已經啟動binlog並且有不同的sever_id
7、在兩台的伺服器上啟用log_slave_updates參數
8、在初始的備庫上啟用read_only擁有備庫的主-主複製拓撲
擁有備庫的主-主複製註意事項
1、從庫的數量可多可少，建議不要太多，不然會對主庫造成I/O的壓力
2、每個從庫都應該設置成只讀狀態，分擔主庫的讀請求
3、一個主庫出現問題，將會損失這個主庫下的所有從庫的讀冗餘
4、一個主機離線時候，要去除改主機的從庫級聯複製
實現的方式
1、分發主庫也是個從庫
2、分發主庫記錄主庫傳遞過來的二進位日誌並分發給下麵的從庫
3、減輕主庫複製所消耗的負載
未完待續，MySQL複製優化、常見問題、高可用架構，請等下篇博文
原創作品，轉載請註明出處：http://www.cnblogs.com/demon89/p/8503814.html
