MySQL主從複制（二）

<code><</code><code>span</code> <code>style</code><code>=</code><code>"font-family:sans-serif;"</code><code>>主從架構中：從node是不接受w操作的，否則可能會導緻資料不一緻。</</code><code>span</code><code>><</code><code>br</code><code>></code>

一、複制架構中應該注意的問題：

1.限制slave為隻讀模式

可以設定在啟動參數中。

> show global variables like 'read_only';

此限制對擁有SUPER權限的使用者都無效。

阻止所有使用者：

mysql> flush tables with read lock; //将阻塞所有w操作。

//但是中繼日志的重放是可以的，不會被阻塞

2.如何保證主從複制的事務安全？

master在執行事務後，應該立即寫入都事務日志。

二進制日志在記憶體中是有緩沖的。//一旦master當機，slave仍然沒有擷取該事務，但是client已經commit，資料不一緻

方法：//保證master盡快将事務儲存到二進制日志

1)在master啟動參數：

sync_binlog=ON //立即刷寫二進制日志

如果用到innodb存儲引擎：

innodb_flush_logs_at_trx_commit=ON

innodb_support_xa=ON

1.在事務送出時立即将事務日志緩沖區中與事務日志相關的資料刷寫到事務日志中去

2.xa:分布式事務,基于2段式送出，執行分布式事務。

2)在slave節點上

skip_slave_start=OFF

//在slave啟動時，是否自動啟動複制線程

//為了事務安全：不建議啟動，直接加入很有可能會導緻出錯

//建議手動啟動。該線程

//參考http://www.2cto.com/database/201307/230420.html

//注意：怎麼強調資料的安全性都不為過。

slave:

/var/lib/mysql/

master.info :master的賬号密碼資訊，複制位置等

relay-log.info 從節點自己記錄的，複制到哪個二進制日志的哪個position步驟。

show global variables like '%relay_log%';

relay_log_info_file //儲存的檔案

sync_relay_log

sync_relay_log_info //

master

show global varibales like '%master%';

sync_master_info 0

//master.info中的資訊是否同步到磁盤，進而讓slave擷取最新資訊。

//有必要啟動。

小結：

master：

sync_master_info

slave:

sync_relay_log

sync_relay_log_info

問題2：思考

如果master已經運作一段時間，且有大量資料，

如何配置并啟動slave

方法：

通過備份恢複資料至從伺服器

複制起始位置為備份時，二進制日志檔案及其pos

二、如何進行主主複制：

A和B：都同時啟動relay-log，binary-log，并互為主從

問題：

1.資料不一緻，是以，慎用//選擇其中一個，删除另外一個

2.auto_increment,A和B都在自動增長，合并将會出錯。

//注意mysql 5.5及其之後的版本，在主主模型中 auto_increment 這個問題已經解決，不需要單獨設定

A：奇數增長：

auto_increment_offset=1

auto_increment_increment=2

//從1開始，一次增長2個

B：偶數增長: auto_increment_offset=2

//左右不均衡怎麼辦？沒有辦法

auto_increment_offset=2

//從2開始，一次增長2個

3.循環複制

配置步驟：

1.各node使用一個唯一server-id

2.都啟動binary log和relay log

3.建立擁有複制權限的使用者賬号

4.定義自動增長id字段的數值為奇偶

5.均把對方指定為主node，并啟動複制線程

步驟：

master A：

systemctl stop mariadb

rm -rf /var/lib/mysql/*

vim my.cnf

[mysqld]

log-bin=master-bin

lelay_log=relay-log

server-id=1

innodb_file_per_table=ON

skip_name_resolve=ON

systemctl start mariadb

mysql> show global variables like '%log%' //relay_log,log_bin

msyql> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.%' identified by 'replipass';

mysql> flush privileges;

//現在兩個都是幹淨的

show master status //檢視對方處于什麼位置

master-bin.000003 506

change master to master_host='192.168.1.68',master_user='repuser',master_password='replpass',master_log_file='master-bin.000003',

master_log_pos=506; //從master的這個位置開始

show slave status;

start slave;

master B:

rm -rf /var/lib/mysql/*

server-id=5 //不一樣

auto_increment_offset=2 //不一樣

msyql> show global variables like '%log%'; //relay-log,log-bin

mysql> show master status //檢視自己處于什麼位置

mysql> show master status ;

master-bin.000003 506 //也是506

change master to master_host='192.168.1.67',master_user='repuser',master_password='replpass',master_log_file='master-bin.000003',

show slave staus;

=======================================

測試：

A：create database mydb;

B: use mydb;

create table tb1(id int unsigned not null auto_increment primary key,name char(30))

desc tb1;

show master status;

A:檢視show slave status，二進制日志的位置已經發生改變

insert into tb1 (name) values ('yang kang'),('yang guo');

select * from tb1;

A:插入資料，

insert into tb1 (name ) ,,,,

最後：

1,3,5,6,8,10 //有一個内置的函數，儲存了插入的id号

可以重置insert id即可

這樣：就不需要在mariadb-server啟動的時候指定

auto_increment_increment=2 ,...

三、半同步複制 //借助于插件

5.5版本之後的

master隻等待一個slave傳回複制确認結果即可//

假如沒有任何一個節點傳回同步成功消息：？

設定逾時時間。逾時後自動降級為異步模式

基于mariadb插件

/usr/lib64/mysql/plugin/

[root@localhost mysql]# ls /usr/lib64/mysql/plugin/sem*

/usr/lib64/mysql/plugin/semisync_master.so

/usr/lib64/mysql/plugin/semisync_slave.so

//一個是master節點的，一個是slave節點的

A：systemctl stop mariadb

server-id=1

mysql> flush privileges //slave是不需要建立該使用者的

mysql-bin.00003 496

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

show plugins //檢視插件

show global variables like '%semi5'; //多了好幾個變量

show global status like '%semi%'

。。client //有多少個半同步node

set global rpl_semi_sync_master_enabled=1

B:systemctl stop mariadb

relay_log=relay-log

server-id=2

innodb_file_per_tab=ON

skip_name_resolve=ON

master_log_pos=496; //從master的這個位置開始

install plugin rpl_semi_sync_slave SONAME 'symisync_slave.so';

set global rpl_semi_sync_slave_enabled=1 //啟用

//再次在A上檢視，...client 為1 .... master_status=ON

注意：help install

plugin_dir=/path/to/plugin/directory //預設/usr/lib64/mysql/plugin

A：master

msyql> create table tb1 (id int,name char(30));

msyql> show global status like '%semi%';

将會有很多的狀态等待時間。

建議隻配置一個半同步。

mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> set global variables rpl_semi_sync_master_enabled=1;

mysql> show gloabl variables like '%semi%';

mysql> show global status like '%semi%';

msyql> install plugin rpm_semi_sync_slave SONAME 'semisync_slave.so';

msyql> set global variables rpl_semi_sync_slave_enabled=1;

四、複制過濾器：

msyql可以複制複制一個或多個資料庫

複制過濾器可以在master和slave node做

在master上：會導緻master的二進制日志不完整，不是記錄了所有資料庫的資料

讓從節點僅複制指定的資料庫，或指定資料庫的指定表//但是master會發送所有的二進制資料給slave，由slave決定選擇部分複制。

實作：

（1）主服務僅向二進制日志中記錄與特定資料庫(特定表)相關的事件

問題：時間還原無法實作，不建議使用

binlog_do_db //資料庫白名單清單

binlog_ignore_db //資料庫黑名單清單，這兩個不要同時使用，

（2）slave sql_thread在replay中繼日志中的事件時，僅讀取與特定資料庫(特定表)相關的事件并應用于本地。

問題：會帶來網絡及磁盤IO浪費

replicate_do_db //白名單,逗号隔開

replicate_ignore_db //忽略的資料庫

實作： //在原有的主從架構上

mysql> show global variables like 'replicate%'

mysql> set global replicate_do_db='mydb' ;

//在slave上進行過濾

mysql> show slave status \G;

//測試在master上建立其他資料庫

//在slave上是看不到的，但是master在mydb上插入資料，在slave上是可以看到的

注意：show slave status \G;

replicate_ignore_table;

replicate_do_table; //限制可以單個表

replicate_do_db:

replicate_ignore_db;

replicate_wild_do_table; //通配符

replicate_wild_ignore_table; //通配符

基于SSL的複制

help change master //

change master master_ssl //指定使用ssl進行複制

檢視是否支援：

mysql> show global variables like '%ssl%'; //為了安全起見，在前端應用到mysql之間使用ssl

前提：支援SSL

（1）master配置證書的私鑰；并且要建立一個要求必須私用SSL連接配接的複制賬号

help grant;

grant ... with ssl_option ssl_option

//一般是slave驗證master的狀态

（2）slave端使用change master to 指令時指明ssl相關選項;

跟複制功能相關的檔案：

master.info :用于儲存slave連接配接至master時的相關資訊，例如賬号、密碼、伺服器位址等

relay-log.info：儲存在目前slave節點上已經複制的二進制日志和本地relay log日志的對應關系

五、MySQL複制的監控和維護：

（1）清理日志：

purge binary logs to 'mysql-bin.010';

purge binary logs before '2008-04-01 10:00:12';

show binary logs;

（2）複制監控

show binlog events;

show binary logs;

show process list; //檢視複制線程

（3）從server是否落後于master服務

Second_Behind_master;//落後于master多長時間。

（4）如何确定主從節點是否一緻

percona-tools //一個工具可以檢測

（5）資料不一緻如何修複

1.slave資料删除，在master上重新備份

2.多個slave，灰階模式，逐個上下線

重新複制。

六、讀寫分離器

//開源實作方案，有的公司自己開發或者在前端應用實作讀寫分離

mysql-proxy //很多坑，已經不用

http://www.cnblogs.com/phpstudy2015-6/p/6687480.html#_label1

阿裡巴巴的cobar

360的atlas

golang的kingshard

http://blog.csdn.net/hu_wen/article/details/53635976

mysql-router:官方提供，不支援讀寫分離，

它實作了失敗轉移和失敗切換，而且這個工具有自己的ip和端口，實作了高可用。

算法：輪訓，權重輪訓等

七、如何添加一個新的salve

1 dump主庫master的資料，停止slave。

mysqldump -uroot -p --all-databases > all.sql

mysql-bin.000002 652

2 傳遞到從庫slave上，然後在從庫slave上進行還原。

mysql > stop slave;

# msyql < all.sql;

change master to master_host='192.168.1.106',master_user='repluser',master_password='replpass'

,master_log_file='mysql-bin.000002',master_log_pos=652,master_connect_retry=5;

msyql> start slave;

備注：reset slave //重置slave

附件：如何切換slave為master

注意：

1.mysqlhotcopy 隻能對MyISAM表進行熱備

2.原理：先将需要備份的資料庫加上一個讀鎖，

然後用FLUSH TABLES将記憶體中的資料寫回到硬碟上的資料庫，

最後，把需要備份的資料庫檔案複制到目标目錄

mysqlhotcopy [option] dbname1 dbname2 backupDir/

本文轉自MT_IT51CTO部落格，原文連結：http://blog.51cto.com/hmtk520/1943729，如需轉載請自行聯系原作者

MySQL主從複制（二）

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Bugku-WEB-web33

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述