MySQL主從同步(複製)的配置

標籤：主從同步   主從複製   

1、主從複製的原理:

*Master，記錄資料更改操作

- 啟用binlog記錄模式

- 允許Slave讀取binlog日誌

*Slave運行2個同步線程

- Slave_IO：負責串連Master，複製其binlog記錄檔到原生relay-log檔案

- Slave_SQL：執行本機relay-log檔案裡的SQL語句，重現Master的資料操作

2、基本構建思路:

1）初始化現有庫：將主庫匯入從庫，確保資料一致性

2）配置Master，主伺服器：調整運行參數，授權一個同步處理的使用者

3）配置Slave，從伺服器：調整運行參數，發起同步操作

3、初始化現有庫：

**從資料庫伺服器上一定要有主要資料庫伺服器的庫和表，但主要資料庫伺服器上不需要必須有從資料庫伺服器上的庫和表。從伺服器應包括希望同步的所有庫，對採用MyISAM的庫，可離線備份。

mysql>reset master;  //重設binlog日誌

[[email protected]~]#mysqldump -uroot -p123 -B mysql test db6000 >/dbbak/threedb.sql //完整備份主伺服器資料庫檔案

對於從伺服器，離線匯入由主伺服器提供的備份，清空同名庫（若有的話）

Mysql>drop database test;

[[email protected]~]#scp svr:/dbbak/threedb.sql  /root/   //遠程拷貝主伺服器備份檔案

[[email protected]~]#mysql -uroot -p123 <threedb.sql  //恢複主伺服器的備份檔案到本機資料庫

**從資料庫伺服器如果是複製的虛擬機器的話要修改下面檔案uuid的值

[[email protected] mysql]# cat /var/lib/mysql/auto.cnf

[auto]

server-uuid=a1c22f29-7d1a-11e4-b7f9-099e0022adfD

4、配置主伺服器:

1）調整運行參數，啟用binlog及允許同步

[[email protected]~]#vim /etc/my.cnf

[mysqld]

Log_bin          //啟用binlog日誌

Server_id=6     //指定伺服器ID號，標識自己身份，一般使用IP地址主機位

Innodb_flush_log_at_trx_commit=1    //最佳化設定

Sync-binlog=1     //允許日誌同步

……

[[email protected]~]#service mysql restart   //重啟服務，使設定生效

2）授權備份使用者：允許slaveuser從192.168.4.0/24網段訪問，對所有庫（預設不允許對單個庫）有同步許可權

mysql>grant replication slave on *.* to [email protected]”192.168.4.%”  identified by “123”;

3）查看主伺服器狀態，記住當前的binlog記錄檔名、位移量pos點號

mysql>show master status\G;  //查看當前正在使用的binlog記錄檔名和位移量 pos點號

file:svr6-bin.000003   //記錄檔名

position:335     //位移量

……

5、配置從伺服器：

1）調整運行參數，啟用binlog及允許同步，啟用唯讀模式

[[email protected]~]#vim  /etc/my.cnf

[mysqld]

log_bin        //啟用binlog日誌

server_id=20       //指定伺服器ID號

Innodb_flush_log_at_trx_commit=1    //最佳化設定

sync-binlog=1     //允許日誌同步

read_only=1    //唯讀模式

……

[[email protected]~]#service mysql restart   //重啟服務，使設定生效

2）發起同步操作，指定主伺服器相關參數

[[email protected]~]#mysql   -uroot  -p123

mysql> show slave status;

 

mysql>change  master  to  master_host="192.168.4.6",

master_user="slaveuser",

master_password="123456",

master_log_file="svr6-bin.000003",    //binlog記錄檔

master_log_pos=335;   //位移量pos點號

 

mysql> showslave status\G;

slave_IO_Running: No  

slave_SQL_Running: No  

 

mysql>start   slave;     //啟動複製

 

mysql> show slave status\G;   //查看從伺服器狀態

slave_IO_Running: YES     //IO線程已運行

slave_SQL_Running: YES     //SQL線程已運行

6、測試主從同步效果：

在主要資料庫伺服器本機登入，建庫建表，從資料庫伺服器上有主要資料庫伺服器上的庫和表，在主要資料庫伺服器執行插入、更新操作，從伺服器有同步更新，就成功了。

7、MySQL資料庫主從同步工作原理：

1）Slave_IO_Running：負責把主要資料庫伺服器當前正在使用的binlog日誌裡的sql語句，拷貝到本機relaybinlog日誌裡，在master_log_file的檔案裡從master_log_pos開始拷貝

Master.info ：儲存主從關聯資訊（change  master   to……），啟動服務時載入。

 pc205-relay-bin.000002    pc205-relay-bin.index   relay-log.info

vim /etc/my.cnf

[mysqld]

Relay_log=……

2）Slave_SQL_Running：負責執行本機relaybinlog日誌裡的sql語句，把資料寫進資料庫裡

3）報錯資訊：

IO進程出錯：查看狀態時Last_IO_Errno: 0   //錯誤次數

                Last_IO_Error: //最後一次出錯資訊

IO出錯的情況：

- 串連不上主要資料庫伺服器（ping  iptables selinux 授權  主伺服器當前使用的記錄檔和節點指錯（在主伺服器使用show master status；查看在從伺服器使用change master to ……重新設定，要先停止進程 stop slave；改好後start slave；）  ）

SQL進程出錯：Last_SQL_Errno: 0

               Last_SQL_Error:

SQL出錯的情況：和主要資料庫伺服器上的庫、表、表結構不一致（把主伺服器上的庫、表備份還原到從伺服器上）

4）停止從伺服器：

stop slave； 

刪除Master.info   pc205-relay-bin.000002    pc205-relay-bin.index   relay-log.info

8、主從同步的結構模式：

1）一主一從：主伺服器壞掉訪問從伺服器的時候，從伺服器的資料沒有備份；從伺服器壞掉的時候主伺服器的資料也沒有備份裡

2）一主多從：配置時在主伺服器上授權多個slave使用者在做從伺服器的主機上串連自己；或者授權一個使用者在@“%”所有主機串連自己（不安全）。

3）主從從：配置主從，配置從從（同主從），主伺服器和從從伺服器不同步，用戶端串連從伺服器產生的sql語句記錄到本身的binlog日誌，而拷貝的主伺服器的sql語句記錄到relaybinlog日誌裡，從從伺服器唯讀取從伺服器本身binlog日誌。要主伺服器和從從伺服器同步，要配置從伺服器：

Vim  /etc/my.cnf

[mysqld]

……

Log-slave-updates   //記錄從庫更新，允許鏈式複製（A-B-C）配置後從主伺服器同步過來的日誌即寫到relaylog裡也寫到自己的binlog日誌裡，以便自身的從伺服器根據binlog讀取日誌以同步資料

……

4）主主結構：互為主從

9、僅需要從伺服器備份主伺服器的某個或某些庫：

授權時不可以僅對某庫授權，replicationslave授權必須時全域授權

**在配置主從同步時，在主要資料庫伺服器上設定讓從資料庫伺服器如何備份自己的庫：

Vim  /etc/my.cnf

[mysqld]

……

Binlog-do-db=資料庫名   //設定主伺服器對哪些庫記binlog日誌，此選項可設定多條，省略時複製所有庫

Binlog-ignore-db=資料庫名 //設定主伺服器對哪些庫不記binlog日誌（兩條矛盾，不同時用）

……

**或在配置主從同步時，在從資料庫伺服器上設定自己如何備份主要資料庫伺服器的庫：

Vim  /etc/my.cnf

[mysqld]

……

Replicate-do-db=資料庫名  //僅複製指定庫，其他庫將被忽略，此選項可設定多條，省略時複製所有庫

Replicate-ignore-db=資料名 //不複製哪些庫（兩條矛盾，不同時用）

**只在主伺服器或從伺服器一端設定即可。

本文出自 “IT技術學習” 部落格，請務必保留此出處http://learningit.blog.51cto.com/9666723/1789591

MySQL主從同步(複製)的配置