mysql數(shù)據(jù)庫(kù)主從同步之雙主配置----互為主從

Mysql數(shù)據(jù)庫(kù)復(fù)制原理：

整體上來(lái)說(shuō)，復(fù)制有3個(gè)步驟：  

(1)master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events）；

(2)slave將master的binary log events拷貝到它的中繼日志(relay log)；

(3)slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。

下圖描述了復(fù)制的過(guò)程：

      該過(guò)程的第一部分就是master記錄二進(jìn)制日志。在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，master在二進(jìn)制日志記錄這些改變。MySQL將事務(wù)串行的寫(xiě)入二進(jìn)制日 志，即使事務(wù)中的語(yǔ)句都是交叉執(zhí)行的。在事件寫(xiě)入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。

       下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開(kāi)始一個(gè)工作線程——I/O線程。I/O線程在master上打開(kāi)一個(gè)普通的連接，然后開(kāi)始binlog dump process。Binlog dump process從master的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上master，它會(huì)睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫(xiě)入中 繼日志。

       SQL slave thread（SQL從線程）處理該過(guò)程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù) 據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開(kāi)銷(xiāo)很小。

        此外，在master中也有一個(gè)工作線程：和其它MySQL的連接一樣，slave在master中打開(kāi)一個(gè)連接也會(huì)使得master開(kāi)始一個(gè)線程。復(fù)制 過(guò)程有一個(gè)很重要的限制——復(fù)制在slave上是串行化的，也就是說(shuō)master上的并行更新操作不能在slave上并行操作。

具體配置

環(huán)境

MySQL-master1：10.6.1.210

MySQL-master2：10.6.1.211

OS版本：CentOS 6.6
MySQL版本：5.1.73

一、MySQL master-master配置

1.1、修改MySQL配置文件

兩臺(tái)MySQL均如要開(kāi)啟binlog日志功能，開(kāi)啟方法：在MySQL-master1  配置文件/etc/my.cnf

 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed

relay-log=relay-bin

server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1     (log-slave-updates也可以)
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

在MySQL-master2 配置文件/etc/my.cnf

 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed
relay-log=relay-bin

server-id=10
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1   (log-slave-updates也可以)

slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2

將兩個(gè)配置文件保存,分別重啟mysql服務(wù)器

講解幾個(gè)重要的常用選項(xiàng)：

server_id

值必須為2到2³²–1之間的一個(gè)正整數(shù)值。ID值唯一的標(biāo)識(shí)了復(fù)制群集中的主從服務(wù)器，因此它們必須各不相同。

binlog-do-db=database

是要記錄日志的數(shù)據(jù)庫(kù)；

同步多個(gè)數(shù)據(jù)庫(kù)重復(fù)設(shè)置選項(xiàng) binlog-do-db=test 和replicate-do-db=test

例如                              

 binlog-do-db=test1

 replicate-do-db=test1

 binlog-do-db=test2 

 replicate-do-db=test2

binlog-ignore-db

不要記錄日志的數(shù)據(jù)庫(kù)名，多個(gè)數(shù)據(jù)庫(kù)中間用逗號(hào)(,)隔開(kāi)；

log-slave-updates=1

這個(gè)參數(shù)用來(lái)配置從服務(wù)器的更新是否寫(xiě)入二進(jìn)制日志，這個(gè)選項(xiàng)默認(rèn)是不打開(kāi)的，但是，如果這個(gè)從服務(wù)器B是服務(wù)器A的 從服務(wù)器，同時(shí)還作為服務(wù)器C的主服務(wù)器，那么就需要開(kāi)發(fā)這個(gè)選項(xiàng)，這樣它的從服務(wù)器C才能獲得它的二進(jìn)制日志進(jìn)行同步操作

sync_binlog=1

  這個(gè)參數(shù)直接影響mysql的性能和完整性. 在進(jìn)行n次事務(wù)提交以后，Mysql將執(zhí)行一次fsync之類(lèi)的磁盤(pán)同步指令，同志文件系統(tǒng)將Binlog文件緩存刷新到磁盤(pán)。Mysql中默認(rèn)的設(shè)置是sync_binlog=0，即不作任何強(qiáng)制性的磁盤(pán)刷新指令，這時(shí)性能是最好的，但風(fēng)險(xiǎn)也是最大的。一旦系統(tǒng)Crash，在文件系統(tǒng)緩存中的所有Binlog信息都會(huì)丟失

auto_increment_increment=2
auto_increment_offset=1

auto_increment字段產(chǎn)生的數(shù)值是：1, 3, 5, 7, …等奇數(shù)ID了

auto_increment_increment = 2

auto_increment_offset = 2

auto_increment字段產(chǎn)生的數(shù)值是：2, 4, 6, 8, …等偶數(shù)ID了

這樣才可以避免兩臺(tái)服務(wù)器同時(shí)做更新時(shí)自增長(zhǎng)字段的值之間發(fā)生沖突。

另外：

master-connect-retry

master-connect-retry這個(gè)參數(shù)是用來(lái)設(shè)置在和主服務(wù)器連接丟失的時(shí)候，重試的時(shí)間間隔，默認(rèn)是60秒

read-only

read-only是用來(lái)限制普通用戶(hù)對(duì)從數(shù)據(jù)庫(kù)的更新操作，以確保從數(shù)據(jù)庫(kù)的安全性，不過(guò)如果是超級(jí)用戶(hù)依然可以對(duì)從數(shù)據(jù)庫(kù)進(jìn)行更新操作

slave-skip-errors

在復(fù)制過(guò)程中，由于各種的原因，從服務(wù)器可能會(huì)遇到執(zhí)行BINLOG中的SQL出錯(cuò)的情況，在默認(rèn)情況下，服務(wù)器會(huì)停止復(fù)制進(jìn)程，不再進(jìn)行同步，等到用戶(hù)自行來(lái)處理。

Slave-skip-errors的作用就是用來(lái)定義復(fù)制過(guò)程中從服務(wù)器可以自動(dòng)跳過(guò)的錯(cuò)誤號(hào)，當(dāng)復(fù)制過(guò)程中遇到定義的錯(cuò)誤號(hào)，就可以自動(dòng)跳過(guò)，直接執(zhí)行后面的SQL語(yǔ)句。

skip-slave-start

表示從mysql服務(wù)器啟動(dòng)時(shí)不啟動(dòng)同步線程，這就要在啟動(dòng)從服務(wù)器之后，手工啟動(dòng)同步線程

1.2  做同步之前要保證兩個(gè)數(shù)據(jù)庫(kù)數(shù)據(jù)一致.

如果主數(shù)據(jù)庫(kù)有數(shù)據(jù)的話。 數(shù)據(jù)庫(kù)鎖表操作，不讓數(shù)據(jù)再進(jìn)行寫(xiě)入動(dòng)作。

mysql> FLUSH TABLES WITH READ LOCK;

用命令mysqldump備份數(shù)據(jù)庫(kù)。

在master服務(wù)器執(zhí)行

shell> mysqldump -uroot -p123456 test >test.sql

看主數(shù)據(jù)庫(kù)的狀態(tài) mysql> show master status;

記錄File 和 Position 項(xiàng)的值

注：沒(méi)有鎖定主服務(wù)器，這里記錄的主服務(wù)器二進(jìn)制日志position值可能會(huì)大于做mysqldump時(shí)的值，這將導(dǎo)致從服務(wù)器丟失在此期間的更新。如果可以保證在此期間主服務(wù)器不會(huì)出現(xiàn)創(chuàng)建新表的更新，那么丟失的影響不大；否則，將導(dǎo)致從服務(wù)器復(fù)制線程失敗，這時(shí)必須在做mysqldump時(shí)鎖定主服務(wù)器。

-----------------------------------------------

從服務(wù)器中my.cnf文件中加入選項(xiàng)

skip-slave-start

表示從mysql服務(wù)器啟動(dòng)時(shí)不啟動(dòng)同步線程，這就要在啟動(dòng)從服務(wù)器之后，手工啟動(dòng)同步線程，在mysql> 提示符下面運(yùn)行“start slave”就可以

保存my.cnf后

執(zhí)行

shell> mysqladmin -uroot -p123456 create test

shell> mysql -uroot -p123456 test <test.sql

啟動(dòng)從服務(wù)器線程

mysql>start slave;

取消主數(shù)據(jù)庫(kù)鎖定 mysql>UNLOCK TABLES;

1.3、將10.6.1.210設(shè)為10.6.1.211的主服務(wù)器

在10.6.1.210上新建授權(quán)用戶(hù) 

MySQL> grant  replicationclient,replication  slave  on  *.*   to  'repluser'@'10.6.1.211'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec) 

MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看Master數(shù)據(jù)庫(kù)上的bin文件以及時(shí)間點(diǎn). 登錄Master服務(wù)器的mysql  后執(zhí)行：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |     2474 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

此處，bin文件為mysql-bin.000015，節(jié)點(diǎn)為2474。

在10.6.1.211將10.6.1.210設(shè)為自己的主服務(wù)器

MySQL> change master to master_host='10.6.1.210',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000015',master_log_pos=2474;
Query OK, 0 rows affected (0.05 sec)

紅色標(biāo)注部分為10.6.1.210主機(jī)中show master status;命令結(jié)果中的file、postion兩個(gè)值

MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)

MySQL> show slave status/G

…

…

Slave_IO_Running:Yes            #IO thread  是否運(yùn)行

Slave_SQL_Running:Yes         #SQL thread是否運(yùn)行

…

1.4、將10.6.1.211設(shè)為10.6.1.210的主服務(wù)器

在10.6.1.211上新建授權(quán)用戶(hù) 

MySQL> grant  replicationclient,replication  slave  on  *.*   to 'repluser'@'10.6.1.210'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec)

 MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看Master數(shù)據(jù)庫(kù)上的bin文件以及時(shí)間點(diǎn). 登錄Master服務(wù)器的mysql  后執(zhí)行：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1476 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

此處，bin文件為mysql-bin.000004，節(jié)點(diǎn)為1476。

在10.6.1.210將10.6.1.211設(shè)為自己的主服務(wù)器

MySQL> change master to master_host='10.6.1.211',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000004',master_log_pos=1476;
Query OK, 0 rows affected (0.05 sec)  

MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)   

MySQL>

MySQL> show slave status/G

…

…

Slave_IO_Running:Yes            #IO thread  是否運(yùn)行

Slave_SQL_Running:Yes         #SQL thread是否運(yùn)行

…

1.5 、其他命令

1.5.1、查看復(fù)制進(jìn)度
需要在主庫(kù)上運(yùn)行
mysql>show processlist \G;

1.5.2、主服務(wù)器上的相關(guān)命令：
show processlist;
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

1.5.3、從服務(wù)器上的相關(guān)命令:
slave start
slave stop
slave stop IO_THREAD //此線程把master段的日志寫(xiě)到本地
slave start IO_THREAD
slave stop SQL_THREAD //此線程把寫(xiě)到本地的日志應(yīng)用于數(shù)據(jù)庫(kù)
slave start SQL_THREAD
reset slave
set global sql_slave_skip_counter
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動(dòng)態(tài)改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過(guò)的日志