mysql主從複制無憂（主從及主主配置全過程）

主從複制解決的是：讓一台伺服器資料與另外的伺服器資料保持一緻。

主從伺服器的版本最好一緻。（從伺服器的版本也可以高于主伺服器）

主從同步的原理；

主從同步涉及到3個線程：主伺服器（master）I/O thread 和從伺服器（slave）I/Othread 以及SQL thread

實作mysql主從複制（同步）的本質：

在主伺服器上開啟Mysql的binary-log（産生二進制日志檔案）功能。

整個複制過程：從伺服器（slave）從主伺服器端（master）擷取該日志，然後在（slave）從伺服器上将二進制檔案解析成SQL語句并完全順序地執行SQL語句所記錄的各種操作。

具體過程：

1）從伺服器上的I/O線程連接配接上主伺服器，然後請求從指定日志檔案的指定位置或者從最開始的日志位置之後的日志内容。

2）從伺服器在接收到來自從伺服器的I/O線程請求後，，通過自身的I/O線程，根據請求資訊讀取指定日志位置之後的日志資訊，

并傳回從伺服器端的I/O線程，傳回的資訊中除了日志所包含的資訊之外，還包括此次傳回的資訊在主伺服器端對應二進制檔案

的名稱以及在二進制日志中的位置。

3）從伺服器的I/O線程接收到資訊後，将擷取到的日志内容依次寫入從伺服器端的中繼日志檔案（relay）的最後，并且講讀取到的主伺服器端的二進制日志的檔案名和位置記錄到一個名為master-info檔案中，友善在下一次讀取的時候能夠迅速定位從哪個位

置開始往後讀取日志資訊。

4）從伺服器的SQL線程在檢測到中繼日志檔案中新增加了内容後，會馬上解析該中繼日志檔案中的内容，将日志内容解析為SQL語句，然後在自身執行這些SQL，由于是在主伺服器端和從伺服器端執行了同樣的SQL操作，是以兩端的資料是完全一樣的。

實施分四步： 1.建立mysql主從伺服器連接配接

2.master開啟 binarylog 功能

3.master授權使用者（用來在slave上面登入的）

4.slave擷取master資訊。

實驗過程：

準備階段：

master 端

ip :192.168.1.151

[root@www data]# mysqld -V # 檢視mysql版本

mysqld Ver 5.5.33-log for linux2.6 on x86_64 (MySQL Community Server (GPL))

[root@www data]# uname -r

2.6.32-642.11.1.el6.x86_64

[root@www data]# cat /etc/issue

CentOS release 6.8 (Final)

slave 端

ip ；192.168.1.123

[root@jf6 data]# mysqld -V

mysqld Ver 5.5.33-log for linux2.6 on x86_64 (MySQL Community Server (GPL))

[root@jf6 data]# uname -r

[root@jf6 data]# cat /etc/issue

統一資料庫環境：（同步一個庫檔案庫名：swallow）

master端

[root@jf6 data]# service mysqld status

SUCCESS! MySQL running (16394)

進入資料庫：

[root@jf6 data]# mysql -uroot -p

Enter password:

mysql>

建立一個庫名：swallow 表名 test 字段 id 插入資料 1

mysql> select * from swallow.test;

+------+

| id |

| 1 |

複制這個庫：

[root@jf6 data]# mysqldump -uroot -p swallow >mysql.sql

Enter password:

将建立的資料庫複制到slave端下

[root@jf6 data]# scp mysql.sql [email protected]:/root/

[email protected]'s password:

mysql.sql 100% 1784 1.7KB/s 00:00

slave 端：

[root@www data]# mysql -uroot -p

mysql> create database swallow;

mysql> use swallow

Database changed

mysql> source /root/mysql.sql;

開啟主從複制配置模式：

master端：

vim /etc/my.cnf

server-id = 1 #屬于辨別符，差別資料庫

log-bin=mysql-bin #開啟binarylog 功能生成二進制檔案

binlog-do-db=swallow # 指明要同步的庫

重新開機mysql。

複制同步的第一條件：2個mysql伺服器可以互通，采用授權的方式。

mysql> grant replication slave on *.* to 'slave123'@'192.168.1.123' identified by '123456';

（ replication slave 賦予取得二進制日志的權利）

server-id =2

重新開機mysql

測試連通性：

[root@www ~]# mysql -h 192.168.1.151 -u slave123 -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.33-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

可以連接配接上master端。

開啟slave伺服器：目的是探知 master伺服器的二進制檔案的名稱和開始點

首先查詢 master狀态：（取得二進制檔案和開始點）

master端

mysql> show master status;

+------------------+----------+--------------+------------------+

slave端

mysql> change master to \

-> master_host='192.168.1.151', #指明master位址

-> master_user='slave123', #指明授權使用者。可以連接配接master的使用者

-> master_password='123456', #指明進入master的驗證

-> master_log_file='mysql-bin.000002', # 指明二進制檔案

-> master_log_pos=107; #指明擷取該檔案的開始點

mysql> slave start; #開啟 slave

mysql> slave start;

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.151 ######## master_host

Master_User: slave123 ######## master_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 107

Relay_Log_File: www-relay-bin.000003

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes ######### IOthread 正常

Slave_SQL_Running: Yes ######## SQLthread 正常

測試主從：

master端：

mysql> use swallow

mysql> insert into test values(2);

mysql> insert into test values(3);

mysql> insert into test values(4);

slave端：

mysql> select * from swallow.test;

+------+

| id |

| 1 |

| 2 |

| 3 |

| 4 |

小貼士：還有一種開啟slave的方法：就是在mysql5.1 版本下在配置檔案中寫入 master_host master_user master_password

這3個參數。由于後期版本采用引擎（mysql5.5的預設存儲引擎是）innodb,是不是myisam引擎（mysql5.1的版本）。

是以，如果安裝的版本高于5.1 不要這樣做了。因為：引擎的變化。/etc/my.cnf主配置檔案裡面不支援上面3個參數。

加入後，會造成slave伺服器的mysql無法啟動。

順手做個主主配置吧。（實質就是把剛才的配置主從交換下）

slave端（這裡沿襲上面的叫法。防止弄亂了）

vim /etc/my.cnf

log-bin=mysql-bin #開啟産生日志功能

binlog-do-db=swallow #定義同步庫

重新開機mysql服務

mysql> grant replication slave on *.* to 'slave151'@'192.168.1.151' identified by '123456';

檢視伺服器狀态：

+------------------+----------+--------------+------------------+

master端（這裡沿襲上面的叫法。防止弄亂了）

測試連通性：

[root@jf6 data]# mysql -h 192.168.1.123 -uslave151 -p123456

Your MySQL connection id is 4

mysql>

開啟slave配置：

-> master_host='192.168.1.123',

-> master_user='slave151',

-> master_password='123456',

-> master_log_file='mysql-bin.000002',

mysql> show slave statu \G;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'statu' at line 1

ERROR:

No query specified

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.123

Master_User: slave151

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 265

Relay_Log_File: jf6-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

測試方法：

slave端：

mysql> use swallow

mysql> insert into test values(1);

mysql> insert into test values(2);

兩邊交替插入資料；

結果：

mysql> select * from test;

+------+

| id |

| 1 |

| 2 |

| 3 |

| 4 |

這裡需要注意的關鍵點：

1.主伺服器開啟binarylog功能（log-bin=mysql-bin ），以及日志狀态。

mysql> show master status;

2.開啟slave服務時：别忘了開啟slave。

3.slave的狀态監控：（如果IO 和SQL都OK 就OK了）

Slave_IO_Running: Yes

如果是no：IO一般和權限之類有關系。因為IO負責2個服務之間通信

SQL 則是自己的原因。因為他的作用就是從relay-log裡面檢視變化

轉化成SQL 依照順序寫入從伺服器。

4.主配置檔案： server-id 不要相同。

本文轉自 swallow_zys 51CTO部落格，原文連結：http://blog.51cto.com/12042068/1891922，如需轉載請自行聯系原作者

mysql主從複制無憂（主從及主主配置全過程）

繼續閱讀

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

windows不能在本地計算機上運作oracleDbConsoleorcl

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List