mysql主從複製

標籤：run   複製   oldboyedu   create   databases   產生   varchar   efault   單一實例   

要實現主從複製，首先得開啟主伺服器binlog功能，工作原理其實就是從庫向主庫擷取binlog日誌，記錄sql操作。
mysql的主從複製是一個非同步複製過程，將資料從一個主要資料庫複製到另一個從資料庫，在主和從之間實現複製的過程是由3個線程參與完成的，主庫IO線程從庫IO和SQL線程。i/o線程去請求主庫 的binlog，得到的binlog日誌寫到relay log（中繼日誌） 檔案中；
主庫會產生一個 log dump 線程，用來給從庫 i/o線程傳binlog
SQL 線程，會讀取relay log檔案中的日誌，並解析成具體操作，來實現主從的操作一致，而最終資料一致；

#讀寫分離軟體
maxscale,MyCat,atlas

mysql主從複製原理介紹
1）master伺服器上開啟二進位日誌，每個事務更新資料完成之前，master都會把資料變化記錄到二進位記錄檔中（串列寫入）
2）slave上配置change master to，將master的binary日誌拷貝到它自己的中繼日誌（I/O線程）
3）SQL線程從中繼日誌中讀取事件，並重放其中的事件更新slave的資料，使得和master的資料一樣

A：在主伺服器上配置如下
1、在master上建立一個備份賬戶，每個slave使用標準的mysql使用者名稱和密碼串連master，進行複製操作的使用者會授予replication slave許可權。
命令：
mariaDB [jiaoyu]> grant replication slave,reload on . to ‘backup‘@‘192.168.119.%‘ identified by ‘1234‘
2、master上開啟log-bin
配置my.cnf（啟用log-bin，配置server-id）
3、備份master伺服器上的資料（/server/backup）
[[email protected] backup]# mysqldump -uroot [email protected] -A -x > /server/backup/mysql_date +%F.sql
4、通過SCP把主伺服器上的資料拷貝到從伺服器上，在從伺服器上執行一遍，以便在主從同步之前，資料是一致的
5、記錄master伺服器上master_log_file和master_log_pos
MariaDB [jiaoyu]> show master status;

B、在從伺服器上配置如下（slave）
1、配置change master（master.info）
mariaDB [jiaoyu]>change master to master_host=‘192.168.119.128‘,master_port=3306,master_user=‘backup‘,master_password=‘1234‘,master_file_log=‘mysql-bin.000009‘,master_file_pos=643;
2、配置my.cnf開啟
log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
（server-id必須唯一，relay_log中繼日誌，log_slave_updates表示slave將複製事件寫進自己的二進位日誌(後面會看到它的用處)）
3、把從主伺服器拷貝過來的備份資料，還原資料庫
4、開啟start slave
MariaDB [(none)]> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.119.129
Master_User: req
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 643
Relay_Log_File: test-relay-bin.000012
Relay_Log_Pos: 537
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes #這個為yes
Slave_SQL_Running: Yes #這個為yes
...
Seconds_Behind_Master: 0
你可查看master和slave上線程的狀態。在master上，你可以看到slave的I/O線程建立的串連

小結：
1、主從複製是非同步邏輯的sql語句級的複製
2、複製時，主庫有一個I/O線程，從庫有倆個線程I/O和sql

=======================主從複製執行個體===============================
主從複製執行個體

#關閉單一實例
/etc/init.d/mysqld stop
#查看進程
lsof -i:3306
#開啟多執行個體
/data/3306/mysql start
/data/3307/mysql start
#查看進程
ss -lntup|grep 330

主從複製實踐：
1、準備
主庫log_bin
主從server-id不同
2、主庫準備
mysql -uroot -poldboy123 -S /data/3306/mysql.sock
grant replication slave on . to ‘rep‘@‘172.16.1.%‘ identified by ‘oldboy123‘;
flush privileges;

3、把資料從主庫搞到從庫。
複製前 讓從和主一致。
mysql -S /data/3306/mysql.sock
mysql> flush table with read lock; 不能關視窗
mysql> show master status;
+-------------------+----------+
| File | Position |
+-------------------+----------+
| oldboy-bin.000002 | 405 |
單開視窗
mysqldump -A -B --master-data=2 -S /data/3306/mysql.sock|gzip >/opt/3306.sql.gz
原視窗
mysql> unlock table;
從庫恢複：
gzip -d /opt/3306.sql.gz
mysql -uroot -poldboy123 -S /data/3307/mysql.sock </opt/3306.sql

5、指定主機、使用者、位置點同步
mysql> CHANGE MASTER TO
MASTER_HOST=‘172.16.1.51‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘oldboy123‘,
MASTER_LOG_FILE=‘mysql-bin.000001‘,
MASTER_LOG_POS=656166;

6、開啟同步開關，檢查是否同步。
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

#測試
create database ddd;
drop database ddd;

資料庫主從複製原理

要實現主從複製，首先得開啟主伺服器binlog功能，工作原理其實就是從庫向主庫擷取binlog日誌，記錄sql操作。
mysql的主從複製是一個非同步複製過程，將資料從一個主要資料庫複製到另一個從資料庫，在主和從之間實現複製的過程是由3個線程參與完成的，主庫IO線程從庫IO和SQL線程。

工作中錯誤。
stop slave; #<==臨時停止同步開關。
set global sql_slave_skip_counter =1 ; #<==將同步指標向下移動一個，如果多次不同步，可以重複操作。
start slave;

32期mysql主從複製
1、部署主從複製時不正常問題匯總
1、server-id相同導致
2、userver-uuid相同導致（帶著資料庫複製虛擬機器）
[[email protected] ~]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=5f61a6ed-0950-11e7-94ed-000c29210a05
3、change master錯誤。

（2）快速主從複製
mysqldump -A -B --master-data=1 -S /data/3306/mysql.sock >/opt/3306.sql
從庫：
CHANGE MASTER TO
MASTER_HOST=‘172.16.1.51‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘oldboy123‘;

（3）5.6 GTID複製特性：

（4）監控主從複製：
兩個yes一個0 必要條件
關鍵：類比使用者訪問，主寫時間戳記，從讀。
（5）主從複製故障排除
stop slave; #<==臨時停止同步開關。
set global sql_slave_skip_counter =1 ; #<==將同步指標向下移動一個，如果多次不同步，可以重複操作。
start slave;
設定檔：
[[email protected] ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062,1007
（6）從庫記錄binlog
log-slave-updates #<==必須要有這個參數
log-bin = /data/3307/oldboy-bin
expire_logs_days = 7
從庫記binlog: 1、從庫備份。2、從庫再做主庫（級聯）。
A--->B-->C-->D
(7)MySQL主從複寫延遲問題原因及解決方案
講了6點。
（8）級聯複製與主主複製
A--->B-->C-->D
（9）從庫唯讀read-only
M--->S
從庫配置read-only：具有SUPER許可權的使用者不受read-only參數影響，例如：管理員root。
授權Web串連使用者，盡量不要有all。create。
（10）生產讀寫分離情況如何授權網站使用者
oldboy授權Web串連使用者select。
主庫：oldboy 123456 3306 172.16.1.51 W
從庫：oldboy 123456 3306 172.16.1.52 W
a.主庫 增刪改查，從庫：查（revoke增刪改）
b.主庫 增刪改查，從庫：read-only
c.忽略mysql同步（不記錄binlog），主庫從庫分別授權oldboy.主庫 增刪改查，從庫：查
replicate-ignore-db = mysql
binlog-ignore-db = mysql

=========================================================
導致lave_IO_Running 為connecting 的原因主要有以下 3 個方面：
1、網路不通
2、密碼不對
3、pos不對

==============================================
主主複製常見方法
解決資料寫入衝突方法

實現的具體參數：
解決主鍵自增長變數衝突：
Master1:
auto_increment_increment = 2 #自增ID的間隔,如 1 3 5間隔為2。
auto_increment_offset = 1 #ID的初始位置
（將形成 1,3,5,7...序列）

Master2:
auto_increment_increment = 2 #自增ID的間隔,如 2 4 6間隔為2。
auto_increment_offset = 2 #ID的初始位置
（將形成 2,4,6,8 ...序列）
缺點：ID號不連續
優點：前端網站程式不需要任何修改。
應用：盡量不要雙寫，而是單寫，作用可作為主宕機後備用選擇

第二種主主複製
使用序號伺服器
優點：ID號連續
缺點：前端網站程式需要修改。

測試單個主庫
create database oldboy CHARACTER SET utf8 COLLATE utf8_general_ci;
use oldboy;
CREATE TABLE student (
id bigint(12) NOT NULL auto_increment COMMENT ‘主鍵‘,
name varchar(12) NOT NULL COMMENT ‘姓名‘,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

主庫：
auto_increment_increment = 2
auto_increment_offset = 1
log-slave-updates

從庫：
auto_increment_increment = 2
auto_increment_offset = 2
log-slave-updates

===========================================================
半同步 +++++++++++++++++++++++++++++++++++++++++++++++++++++

1、分別在主從節點上安裝相關的外掛程式

master> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;

2、啟用半同步複製

在master上的設定檔中，添加
rpl_semi_sync_master_enabled=ON

在至少一個slave節點的設定檔中添加
rpl_semi_sync_slave_enabled=ON

而後重新啟動mysql服務即可生效。

或者，也可以mysql服務上動態啟動其相關功能：

master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

3、確認半同步功能已經啟用

master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE ‘Rpl_semi_sync_master_yes_tx‘;

slave> SHOW DATABASES;

mysql主從複製