mysql數據庫主從同步之雙主配置----互為主從

Mysql數據庫復制原理：

整體上來說，復制有3個步驟：  

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

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

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

下圖描述了復制的過程：

      該過程的第一部分就是master記錄二進制日志。在每個事務更新數據完成之前，master在二進制日志記錄這些改變。MySQL將事務串行的寫入二進制日 志，即使事務中的語句都是交叉執行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務。

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

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

        此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制 過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

具體配置

環境

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配置文件

兩臺MySQL均如要開啟binlog日志功能，開啟方法：在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

將兩個配置文件保存,分別重啟mysql服務器

講解幾個重要的常用選項：

server_id

值必須為2到2³²–1之間的一個正整數值。ID值唯一的標識了復制群集中的主從服務器，因此它們必須各不相同。

binlog-do-db=database

是要記錄日志的數據庫；

同步多個數據庫重復設置選項 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

不要記錄日志的數據庫名，多個數據庫中間用逗號(,)隔開；

log-slave-updates=1

這個參數用來配置從服務器的更新是否寫入二進制日志，這個選項默認是不打開的，但是，如果這個從服務器B是服務器A的 從服務器，同時還作為服務器C的主服務器，那么就需要開發這個選項，這樣它的從服務器C才能獲得它的二進制日志進行同步操作

sync_binlog=1

  這個參數直接影響mysql的性能和完整性. 在進行n次事務提交以后，Mysql將執行一次fsync之類的磁盤同步指令，同志文件系統將Binlog文件緩存刷新到磁盤。Mysql中默認的設置是sync_binlog=0，即不作任何強制性的磁盤刷新指令，這時性能是最好的，但風險也是最大的。一旦系統Crash，在文件系統緩存中的所有Binlog信息都會丟失

auto_increment_increment=2
auto_increment_offset=1

auto_increment字段產生的數值是：1, 3, 5, 7, …等奇數ID了

auto_increment_increment = 2

auto_increment_offset = 2

auto_increment字段產生的數值是：2, 4, 6, 8, …等偶數ID了

這樣才可以避免兩臺服務器同時做更新時自增長字段的值之間發生沖突。

另外：

master-connect-retry

master-connect-retry這個參數是用來設置在和主服務器連接丟失的時候，重試的時間間隔，默認是60秒

read-only

read-only是用來限制普通用戶對從數據庫的更新操作，以確保從數據庫的安全性，不過如果是超級用戶依然可以對從數據庫進行更新操作

slave-skip-errors

在復制過程中，由于各種的原因，從服務器可能會遇到執行BINLOG中的SQL出錯的情況，在默認情況下，服務器會停止復制進程，不再進行同步，等到用戶自行來處理。

Slave-skip-errors的作用就是用來定義復制過程中從服務器可以自動跳過的錯誤號，當復制過程中遇到定義的錯誤號，就可以自動跳過，直接執行后面的SQL語句。

skip-slave-start

表示從mysql服務器啟動時不啟動同步線程，這就要在啟動從服務器之后，手工啟動同步線程

1.2  做同步之前要保證兩個數據庫數據一致.

如果主數據庫有數據的話。 數據庫鎖表操作，不讓數據再進行寫入動作。

mysql> FLUSH TABLES WITH READ LOCK;

用命令mysqldump備份數據庫。

在master服務器執行

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

看主數據庫的狀態 mysql> show master status;

記錄File 和 Position 項的值

注：沒有鎖定主服務器，這里記錄的主服務器二進制日志position值可能會大于做mysqldump時的值，這將導致從服務器丟失在此期間的更新。如果可以保證在此期間主服務器不會出現創建新表的更新，那么丟失的影響不大；否則，將導致從服務器復制線程失敗，這時必須在做mysqldump時鎖定主服務器。

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

從服務器中my.cnf文件中加入選項

skip-slave-start

表示從mysql服務器啟動時不啟動同步線程，這就要在啟動從服務器之后，手工啟動同步線程，在mysql> 提示符下面運行“start slave”就可以

保存my.cnf后

執行

shell> mysqladmin -uroot -p123456 create test

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

啟動從服務器線程

mysql>start slave;

取消主數據庫鎖定 mysql>UNLOCK TABLES;

1.3、將10.6.1.210設為10.6.1.211的主服務器

在10.6.1.210上新建授權用戶 

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數據庫上的bin文件以及時間點. 登錄Master服務器的mysql  后執行：

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，節點為2474。

在10.6.1.211將10.6.1.210設為自己的主服務器

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)

紅色標注部分為10.6.1.210主機中show master status;命令結果中的file、postion兩個值

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

MySQL> show slave status/G

…

…

Slave_IO_Running:Yes            #IO thread  是否運行

Slave_SQL_Running:Yes         #SQL thread是否運行

…

1.4、將10.6.1.211設為10.6.1.210的主服務器

在10.6.1.211上新建授權用戶 

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數據庫上的bin文件以及時間點. 登錄Master服務器的mysql  后執行：

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，節點為1476。

在10.6.1.210將10.6.1.211設為自己的主服務器

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  是否運行

Slave_SQL_Running:Yes         #SQL thread是否運行

…

1.5 、其他命令

1.5.1、查看復制進度
需要在主庫上運行
mysql>show processlist \G;

1.5.2、主服務器上的相關命令：
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、從服務器上的相關命令:
slave start
slave stop
slave stop IO_THREAD //此線程把master段的日志寫到本地
slave start IO_THREAD
slave stop SQL_THREAD //此線程把寫到本地的日志應用于數據庫
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= //動態改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過的日志