Mysql的主主備份與主從備份--同步

Mysql的主主備份與主從備份--同步

一：實驗目标

mysql主從原理

實戰：mysql主從配置

實戰：mysql主主配置

二：實驗環境

導入測試資料庫book：

Mysql主xuegod63   IP：192.168.1.63

Mysql從xuegod64   IP：192.168.1.64

三：實驗代碼

實戰：mysql主從配置 

服務概述：主從複制

    複制解決的基本問題是讓一台伺服器的資料和另外的伺服器保持同步。 一台主伺服器可以連接配接多台從伺服器，并且從伺服器也可以反過來作主伺服器。主伺服器和從伺服器可以位亍丌同的網絡拓撲中，還能對整台伺服器、特定的資料庫，甚至特定的表迚行複制。 主從伺服器的版本必須一緻，即使丌一緻，主伺服器版本可以是舊的，從伺服器必須是新的版本。

複制解決的問題

MySQL複制技術有以下一些特點：

(1) 資料分布 (Data distribution )

(2) 負載平衡(load balancing)

(3) 備份(Backups)

(4) 高可用性和故障轉移 High availability and failover

複制如何工作

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

(1) master将改發記錄到二迚制日志(binary log)中（這些記錄叨做二迚制日志事件，binary log events）；

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

(3) slave重做中繼日志中的事件，修改salve上的資料

服務概述：

I/O  表示進和出，input和output

一）：運作過程

1：master輸入一條規則，binary log會将其轉換成二進制日志。在每個事務更新資料完成之前，master在二日志記錄這些改變。MySQL将事務串行的寫入二進制日志，即使事務中的語句都是交叉執行的。在事件寫入二進制日志完成後，master通知存儲引擎送出事務。

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

3：SQL slave thread（SQL從線程）處理該過程的最後一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的資料，使其與master中的資料一緻。隻要該線程與I/O線程保持一緻，中繼日志通常會位于OS的緩存中，是以中繼日志的開銷很小。

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

二）：複制配置 

有兩台MySQL資料庫伺服器Master和slave，Master為主伺服器，slave為從伺服器，初始狀态時，Master和slave中的資料資訊相同，當Master中的資料發生變化時，slave也跟着發生相應的變化，使得master和slave的資料資訊同步，達到備份的目的。

三）：要點：

負責在主、從伺服器傳輸各種修改動作的媒介是主伺服器的二進制變更日志，這個日志記載着需要傳輸，從伺服器的各種修改動作。是以，主伺服器必須激活二進制日志功能。從伺服器必須具備足以讓它連接配接主伺服器并請求主伺服器把二進制變更日志傳輸給它的權限。

    一台主伺服器可以連接配接多台從伺服器，并且從伺服器也可以反過來作主伺服器。 

主伺服器和從伺服器可以位于不同的網絡拓撲中，還能對整台伺服器、特定的資料庫，甚至特定的表迚行複制。 

主從伺服器的版本必須一緻，即使不一緻，主伺服器版本可以是舊的，從伺服器必須是新的版本。 

3.1 . 複制解決的問題，MySQL複制技術有以下一些特點： 

(1) 資料分布 (Data distribution )

(2) 負載平衡(load balancing)

(3) 備份(Backups)

3.2 複制如何工作

(3) slave重做中繼日志中的事件，修改salve上的資料。

模式：C/S 模式

端口： 3306

四）：實驗步驟---實戰mysql主從備份-同步

xuegod63 主mysql伺服器配置 ---建立要同步的資料庫： 

建立資料庫

mysql> create database mk;

mysql> use mk;

mysql> create table test1 (id int);

mysql> insert into test1 values(1)

停止mysql服務 

[root@xuegod63 ~]# service mysql stop

編輯配置檔案 

[root@xuegod63 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0 #在原配置檔案中，添加以下内容： 

log-bin=mysqllog #啟用二迚制日志，預設存在/var/lib/mysql 下面

server-id=1 #本機資料庫ID 标示。

binlog-do-db=mk #可以被從伺服器複制的庫。二進制需要同步的資料庫名

# binlog-ignore-db=mk2 不可以被從伺服器複制的庫

重新啟劢 mysql伺服器

[root@xuegod63 ~]# service mysqld restart

授權給使用者名為“slave”使用者登入密碼及權限

mysql> grant replication slave on *.* to [email protected] identified by "123456";

檢視狀态，資訊

資料庫備份

[root@xuegod63 ~]# mysqldump -u root -p123456 --all-databases > all.sql

[root@xuegod63 ~]# ll -h all.sql

-rw-r--r-- 1 root root 514K Mar 27 00:54 all.sql

把主伺服器中的資料傳給從伺服器：複制前保證兩個資料庫資料一緻： 

1):方法1：scp all.sql 192.168.1.64:/root

方法2：使用nc指令 

   NetCat，它短小精悍、功能實用，被設計為一個簡單、可靠的網絡工具，可通過TCP或UDP協定傳輸讀寫資料。同時，它還是一個網絡應用Debug分析器，因為它可以根據需要建立各種不同類型的網絡連接配接。

文法：

伺服器端：nc収送資料的文法： nc -l 端口 < 要傳輸的檔案

用戶端： nc接叐資料的文法： nc 程序nc伺服器端IP 端口 > 檔案名

例：監聽9999端口，當有用戶端連接配接時，就把對應檔案傳送到來連接配接的用戶端 

[root@xuegod63 ~]# rpm -qf `which nc `

nc-1.84-22.el6.x86_64

開啟監聽：

[root@xuegod63 ~]#nc -l 9999 < all.sql

[root@xuegod63 ~]# netstat -antup | grep 9999

tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 15164/nc

測試接收： 

1）配置從伺服器xuegod64 ： 

[root@xuegod64 ~]# cd /opt/

[root@xuegod64 opt]# ls

[root@xuegod64 opt]# nc 192.168.1.63 9999 > ncall.sql

[root@xuegod64 opt]# diff ncall.sql /root/all.sql

============================================================

2):資料庫版本相同

Mysql主伺服器xuegod63

mysql從服務xuegod64

[root@xuegod64 opt]# yum install mysql-server -y  

[root@xuegod64 opt]# service mysqld restart

mysql>show variables like '%version%';檢視版本

測試連接配接到主伺服器是否成功

[root@xuegod64 opt]# mysql -u slave -h 192.168.1.63 -p123456

從伺服器導入資料庫和主伺服器保持一緻

[root@xuegod64 ~]# mysql -u root -p < all.sql

Enter password:123456

[root@xuegod64 opt]# mysql -u root -p

/////////////////////////////////////////////////////////////////////////注釋：在這裡可能會報錯

解決方法

方法操作流程很簡單，如下：

# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & //跳過受權表通路

# mysql -u root mysql //登入mysql

1：關閉mysqld服務

[root@xuegod64 ~]# service mysqld stop

2：跳過授權通路

[root@xuegod64 ~]#  mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

3：修改資料庫更新

[root@xuegod64 ~]# mysql -u root mysql#在mysql5.7以下的版本如下：

mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost';//把空的使用者密碼都修改成非空的密碼就行了。

mysql> FLUSH PRIVILEGES;

mysql> quit

4：重新開機資料庫并重設密碼

[root@xuegod64 ~]# service mysqld start  :

[root@xuegod64 ~]# mysql -uroot -p

Enter password: <輸入新設的密碼newpassword>

/////////////////////////////////////////////////////////////////////////

修改從伺服器配置檔案：從伺服器沒必要開bin-log日志注。

[root@xuegod64 ~]# vim /etc/my.cnf

symbolic-links=0

#在配置檔案中寫入以下内容

server-id=2 #從伺服器ID号，不要和主ID相同 ，如果設定多個從伺服器，每個從伺服器必須有一個唯一的server-id值，必須與主伺服器以及其它從伺服器的不相同。可以認為server-id值類似亍IP位址：這些ID值能唯一識别複制伺服器群集中的每個伺服器執行個體。

master-host=192.168.1.63 #指定主伺服器IP位址

master-user=slave #指定在主伺服器上可以進行同步的使用者名

master-password=123456 #指定在主伺服器上可以進行同步的使用者名的密碼

####以下可以不寫

master-port = 3306 #同步所用的端口

master-connect-retry=60 #斷點重新連接配接時間

重新開機mysql服務 

[root@xuegod64 ~]# service mysqld restart

測試： 主伺服器上檢視：

[root@xuegod63 ~]# mysql -u root -p

Enter password:  

mysql> show master status;

#證明主伺服器成功

從伺服器上檢視：--#證明從伺服器成功

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.63

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqllog.000001

Read_Master_Log_Pos: 315

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 459

Relay_Master_Log_File: mysqllog.000001

Slave_IO_Running: Yes #可以看到這兩個Yes，說明從伺服器安裝成功。

Slave_SQL_Running: Yes

Slave_IO_Running ：一個負責不主機的io通信

Slave_SQL_Running：負責自己的slave mysql迚程

測試：資料同步

xuegod63寫資料： 

Enter password:

mysql> show tables;

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

| Tables_in_mk |

| test1         |

1 row in set (0.00 sec)

mysql> insert into test1 values(5);

xuegod64讀資料：

排錯：

同步之前如果懷疑主從資料不同步可以采取上面冷備份程序拷貝法或者在從伺服器上命行同步方法。

實戰：mysql主主配置 

mysql主：服務端：xuegod63.cn IP：192.168.1.63

mysql主：服務端：xuegod64.cn IP：192.168.1.64

配置xuegod64 ： 身份1： xuegod63的主。

                  身份2： xuegod63的從。

root@xuegod64 ~]# vim /etc/my.cnf

server-id=2

master-host=192.168.1.63

master-user=slave

master-password=123456

####to master

log-bin=mysqlslave-bin-log

binlog-do-db=mk

binlog-ignore-db=mysql #避免同步mysql使用者 相關配置。

授權名為slave64使用者登入密碼及權限：

[root@xuegod64 opt]# mysql -u root -p123456

mysql> grant replication slave on *.* to slave64@'192.168.1.63' identified by '123456';

[root@xuegod64 ~]# mysql -u root -p  

#說明xuegod64 作為mysql 主已經成功。

配置xuegod63 ： 身份1： xuegod64的主。

                  身份2： xuegod64的從。

[root@xuegod63 ~]# vim /etc/my.cnf

### to master -64

log-bin=mysqllog

server-id=1

### to slave -64

master-host=192.168.1.64

master-user=slave64

replicate-do-db=mk

mysql> show slave status \G;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4

Current database: mk

Master_Host: 192.168.1.64 #主伺服器是64

Master_User: slave64  #授權的使用者是slave64

Master_Log_File: mysqlslave-bin-log.000001

Read_Master_Log_Pos: 106

Relay_Log_Pos: 260

Relay_Master_Log_File: mysqlslave-bin-log.000001

Slave_IO_Running: Yes

Replicate_Do_DB: mk

Replicate_Ignore_DB:

測試主主資料同步：

xuegod64主伺服器 添加表

mysql> create table test2(id int);

mysql> create table xuegod (id int);

mysql> insert into test2 values(10);

mysql> insert into xuegod values(100);

xuegod63從伺服器-測試

<a href="http://s4.51cto.com/wyfs02/M01/8A/76/wKiom1gxCweiA0j-AAAfDMBpGXI409.png" target="_blank"></a>

<a href="http://s4.51cto.com/wyfs02/M00/8A/76/wKiom1gxCwfSMHjeAAAhHW6M8cc961.png" target="_blank"></a>

本文轉自 于學康 51CTO部落格，原文連結：http://blog.51cto.com/blxueyuan/1874710，如需轉載請自行聯系原作者