Mysql DBA 進階營運學習筆記-mysql雙主及多主同步過程

標籤：設定   user   int   egrep   來講   value   主從   master   event   

1.Mysql雙主及多主同步實戰，互為主從

使用主主前提：

a.表的主鍵自增(M庫id1,3,5；M庫id 2,4,6)

準備：兩台機器，這裡用多執行個體來講解

第一台：

Ip:192.168.1.115
Port:3306

第二台：

Ip:192.168.1.115
Port:3307

1.1 第一台機器的操作

（1）配置3306的my.cnf設定檔添加開啟下面參數

[[email protected] ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3306/my.cnf [mysqld]auto_increment_increment= 2 自增的間隔如1 3 5 間隔為2auto_increment_offset   = 1 ID的初始位置log-bin = /data/3306/mysql-binlog-slave-updates

（2）重啟3306mysql資料庫服務

[[email protected] ~]# /data/3306/mysql stopStoping MySQL....[[email protected] ~]# /data/3306/mysql startStarting MySQL......

（3）配置同步參數

CHANGE MASTER TOMASTER_HOST=‘192.168.1.115‘, MASTER_PORT=3307,MASTER_USER=‘rep‘,MASTER_PASSWORD=‘123456‘;

提示：如果之前是主從同步想給改成雙主同步，我們要帶—master-data參數備份之前主庫的資料然後匯入到從庫。

例如，備份3306更新的資料

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2 --events >/opt/3306bak.sql

用—master-data 參數備份資料，在change master的時候就不用添加下面的參數了以及不用show master status;查看主庫的狀態查看binlog的位置了。

MASTER_LOG_FILE=‘mysql-bin.000004‘ MASTER_LOG_POS=1895

（4）啟動從庫同步開關並查看同步狀態

mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************   Slave_IO_State: Waiting for master to send event  Master_Host: 192.168.1.115  Master_User: rep  Master_Port: 3307Connect_Retry: 60  Master_Log_File: mysql-bin.000004  Read_Master_Log_Pos: 1895   Relay_Log_File: relay-bin.000012Relay_Log_Pos: 1019Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: YesSlave_SQL_Running: Yes  Replicate_Do_DB:   Replicate_Ignore_DB: mysql   Replicate_Do_Table:    Replicate_Ignore_Table:   Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:    Last_Errno: 0   Last_Error:  Skip_Counter: 0  Exec_Master_Log_Pos: 1895  Relay_Log_Space: 1315  Until_Condition: None   Until_Log_File: Until_Log_Pos: 0   Master_SSL_Allowed: No   Master_SSL_CA_File:    Master_SSL_CA_Path:   Master_SSL_Cert: Master_SSL_Cipher:    Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:    Last_SQL_Errno: 0   Last_SQL_Error:   Replicate_Ignore_Server_Ids:  Master_Server_Id: 21 row in set (0.00 sec)

1.2 第二台機器操作

第二台操作和第一台操作差不多

（1）配置3307的my.cnf設定檔添加開啟下面參數

[[email protected] ~]# egrep "\[mysqld]|auto_increment|log-bin|log-slave" /data/3307/my.cnf [mysqld]auto_increment_increment= 2auto_increment_offset   = 2log-bin = /data/3307/mysql-binlog-slave-updates

（2）重啟3307mysql資料庫服務

[[email protected] ~]# /data/3307/mysql stopStoping MySQL....[[email protected] ~]# /data/3307/mysql startStarting MySQL......

（3）配置同步參數

CHANGE MASTER TOMASTER_HOST=‘192.168.1.115‘, MASTER_PORT=3306,MASTER_USER=‘rep‘,MASTER_PASSWORD=‘123456‘;

（4）啟動從庫同步開關並查看同步狀態

mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************   Slave_IO_State: Waiting for master to send event  Master_Host: 192.168.1.115  Master_User: rep  Master_Port: 3306Connect_Retry: 60  Master_Log_File: mysql-bin.000015  Read_Master_Log_Pos: 1895   Relay_Log_File: relay-bin.000042Relay_Log_Pos: 1326Relay_Master_Log_File: mysql-bin.000015 Slave_IO_Running: YesSlave_SQL_Running: Yes  Replicate_Do_DB:   Replicate_Ignore_DB:    Replicate_Do_Table:    Replicate_Ignore_Table:   Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:    Last_Errno: 0   Last_Error:  Skip_Counter: 0  Exec_Master_Log_Pos: 1895  Relay_Log_Space: 1622  Until_Condition: None   Until_Log_File: Until_Log_Pos: 0   Master_SSL_Allowed: No   Master_SSL_CA_File:    Master_SSL_CA_Path:   Master_SSL_Cert: Master_SSL_Cipher:    Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:    Last_SQL_Errno: 0   Last_SQL_Error:   Replicate_Ignore_Server_Ids:  Master_Server_Id: 11 row in set (0.00 sec)

1.3 測試mysql資料庫主主（M-M）同步互為主從。

（1）3306主機資料庫操作

a.現在在linzhongniao庫裡建立student表

mysql> create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> primary key(id)-> );Query OK, 0 rows affected (0.01 sec)

b.在student表中插入三條資料

mysql> insert into student(name) values(‘nishishei‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘zhangsan‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘lisi‘);Query OK, 1 row affected (0.00 sec)

c.查看一下插入的資料

mysql> select * from student;+----+-----------+| id | name  |+----+-----------+|  1 | nishishei ||  3 | zhangsan  ||  5 | lisi  |+----+-----------+1   rows in set (0.00 sec)

我們探索資料的ID的自增不是連續的，因為我們在3306主機的my.cnf設定了下面的參數，這兩個參數的意思是我ID欄位自增的開始位置為1以一次間隔為2的方式自增，所以我們上面插入的資料是以2為間隔自增的，那麼auto_increment_offset的值等於2 呢？當然auto_increment_increment參數的值也是可以設定的。

auto_increment_increment= 2 自增的間隔如1 3 5 間隔為2auto_increment_offset   = 1 ID的初始位置

（2）接下來我們在3307主機的資料庫上也插入三條資料

mysql> use linzhongniao;Database changedmysql> insert into student(name) values(‘burenshi‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘liushishi‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘luhan‘);Query OK, 1 row affected (0.00 sec)

（3）查看一下插入的資料

mysql> select * from student;+----+-----------+| id | name  |+----+-----------+|  1 | nishishei ||  3 | zhangsan  ||  5 | lisi  ||  6 | burenshi  ||  8 | liushishi || 10 | luhan |+----+-----------+

我們看新插入的資料以6、8、10的形式自增，因為我們在3307主機的my.cnf設定檔中設定的auto_increment_increment參數的值等於2和auto_increment_offset的值等於2，設定這兩個參數的意思是以2為起始位置，2為間隔自增的。所以插入資料的id為6、8、10。

Mysql DBA 進階營運學習筆記-mysql雙主及多主同步過程