MySQL主從復制（二）

主從架構中：從node是不接受w操作的，否則可能會導致數據不一致。

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

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

auto_increment_increment=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

auto_increment_offset=1

auto_increment_increment=2

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:

systemctl stop mariadb 

rm -rf /var/lib/mysql/*

vim my.cnf 

[mysqld]

log-bin=master-bin

lelay_log=relay-log

server-id=5 //不一樣

innodb_file_per_table=ON 

skip_name_resolve=ON

auto_increment_offset=2 //不一樣

auto_increment_increment=2

systemctl start mariadb

msyql> 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;

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',

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

show slave staus;

start slave;

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

測試：

A：create database mydb;

show slave status;

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 ) ,,,,

最后：

select * from tb1;

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 

rm -rf /var/lib/mysql/* 

vim my.cnf 

log-bin=master-bin

server-id=1

innodb_file_per_table=ON 

skip_name_resolve=ON

systemctl start mariadb 

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

mysql> flush privileges;

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 

rm -rf /var/lib/mysql/* 

vim my.cnf 

relay_log=relay-log

server-id=2

innodb_file_per_tab=ON 

skip_name_resolve=ON 

systemctl start mariadb

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

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

install plugin rpl_semi_sync_slave SONAME 'symisync_slave.so';

set global rpl_semi_sync_slave_enabled=1 //啟用

start slave;

show slave staus;

//再次在A上查看，...client 為1 .... master_status=ON 

注意：help install

[mysqld]

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%';

將會有很多的狀態等待時間。

小結：

建議只配置一個半同步。

master：

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%';

slave:

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 master status;

show binlog events;

show binary logs;

show slave status;

show process list; //查看復制線程

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

slave:

show slave status;

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 

show master status;

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/