MySQL主從復制（二）

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

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

1.限制slave為只讀模式

可以設置在啟動參數(shù)中。

> show global variables like 'read_only';

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

阻止所有用戶：

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

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

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

master在執(zhí)行事務后，應該立即寫入都事務日志。 

二進制日志在內存中是有緩沖的。//一旦master宕機，slave仍然沒有獲取該事務，但是client已經commit，數(shù)據(jù)不一致 

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

1)在master啟動參數(shù)：

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

如果用到innodb存儲引擎：

innodb_flush_logs_at_trx_commit=ON 

innodb_support_xa=ON 

1.在事務提交時立即將事務日志緩沖區(qū)中與事務日志相關的數(shù)據(jù)刷寫到事務日志中去

2.xa:分布式事務,基于2段式提交，執(zhí)行分布式事務。

2)在slave節(jié)點上

skip_slave_start=OFF

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

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

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

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

//注意：怎么強調數(shù)據(jù)的安全性都不為過。

slave: 

/var/lib/mysql/

master.info :master的賬號密碼信息，復制位置等

relay-log.info 從節(jié)點自己記錄的，復制到哪個二進制日志的哪個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已經運行一段時間，且有大量數(shù)據(jù)，

如何配置并啟動slave

方法：

通過備份恢復數(shù)據(jù)至從服務器

復制起始位置為備份時，二進制日志文件及其pos

二、如何進行主主復制：

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

問題：

1.數(shù)據(jù)不一致，因此，慎用//選擇其中一個，刪除另外一個

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

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

A：奇數(shù)增長：

auto_increment_offset=1

auto_increment_increment=2

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

B：偶數(shù)增長: auto_increment_offset=2

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

auto_increment_offset=2

auto_increment_increment=2

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

3.循環(huán)復制

配置步驟：

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

2.都啟動binary log和relay log 

3.創(chuàng)建擁有復制權限的用戶賬號

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

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;

//現(xiàn)在兩個都是干凈的

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，二進制日志的位置已經發(fā)生改變

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

select * from tb1;

A:插入數(shù)據(jù)，

insert into tb1 (name ) ,,,,

最后：

select * from tb1;

1,3,5,6,8,10 //有一個內置的函數(shù)，保存了插入的id號

可以重置insert id即可

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

auto_increment_increment=2 ,...

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

5.5版本之后的

master只等待一個slave返回復制確認結果即可//

假如沒有任何一個節(jié)點返回同步成功消息：？

設置超時時間。超時后自動降級為異步模式

基于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節(jié)點的，一個是slave節(jié)點的

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是不需要創(chuàng)建該用戶的

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

將會有很多的狀態(tài)等待時間。

小結：

建議只配置一個半同步。

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可以復制復制一個或多個數(shù)據(jù)庫

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

在master上：會導致master的二進制日志不完整，不是記錄了所有數(shù)據(jù)庫的數(shù)據(jù)

讓從節(jié)點僅復制指定的數(shù)據(jù)庫，或指定數(shù)據(jù)庫的指定表//但是master會發(fā)送所有的二進制數(shù)據(jù)給slave，由slave決定選擇部分復制。

實現(xiàn)：

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

問題：時間 還原無法實現(xiàn)，不建議使用

binlog_do_db //數(shù)據(jù)庫白名單列表

binlog_ignore_db //數(shù)據(jù)庫黑名單列表，這兩個不要同時使用，

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

問題：會帶來網(wǎng)絡及磁盤IO浪費

replicate_do_db //白名單,逗號隔開

replicate_ignore_db //忽略的數(shù)據(jù)庫

實現(xiàn)： //在原有的主從架構上

mysql> show global variables like 'replicate%'

mysql> set global  replicate_do_db='mydb' ;

//在slave上進行過濾

mysql> show slave status \G;

//測試在master上創(chuàng)建其他數(shù)據(jù)庫

//在slave上是看不到的，但是master在mydb上插入數(shù)據(jù)，在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配置證書的私鑰；并且要創(chuàng)建一個要求必須私用SSL連接的復制賬號

help grant;

grant ... with ssl_option ssl_option 

//一般是slave驗證master的狀態(tài)

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

跟復制功能相關的文件：

master.info :用于保存slave連接至master時的相關信息，例如賬號、密碼、服務器地址等

relay-log.info：保存在當前slave節(jié)點上已經復制的二進制日志和本地relay log日志的對應關系

五、MySQL復制的監(jiān)控和維護：

（1）清理日志：

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

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

show binary logs; 

（2）復制監(jiān)控

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）如何確定主從節(jié)點是否一致

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

（5）數(shù)據(jù)不一致如何修復

1.slave數(shù)據(jù)刪除，在master上重新備份

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

重新復制。

六、讀寫分離器

//開源實現(xiàn)方案，有的公司自己開發(fā)或者在前端應用實現(xiàn)讀寫分離

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:官方提供，不支持讀寫分離，

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

算法：輪訓，加權輪訓等

七、如何添加一個新的salve 

1 dump主庫master的數(shù)據(jù)，停止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.原理：先將需要備份的數(shù)據(jù)庫加上一個讀鎖，

然后用FLUSH TABLES將內存中的數(shù)據(jù)寫回到硬盤上的數(shù)據(jù)庫，

最后，把需要備份的數(shù)據(jù)庫文件復制到目標目錄

mysqlhotcopy [option] dbname1 dbname2 backupDir/