mysql主從配置

標籤：linux;mysql主從複製;同步整個庫;同步一個庫;

在實際公司專屬應用程式環境當中，單台mysql資料庫是不足以滿足日後業務需求的。譬如伺服器發生故障，沒有備份伺服器來提供服務的話，業務就得停止。介於這種情況，我們來學習一下mysql主從複製。

使用mysql主從複製的好處有：

1、採用主從伺服器這種架構，穩定性得以提升。如果主伺服器發生故障，我們可以使用從伺服器來提供服務。

2、在主從伺服器上分開處理使用者的請求，可以提升資料處理效率。

3、將主伺服器上的資料複製到從伺服器上，保護資料免受意外的損失。

環境描述：

新企業要搭建架構為主從複製的mysql資料庫。

主伺服器（mysql-master）：IP地址：192.168.124.128，mysql已安裝，沒有使用者資料。

從伺服器（mysql-slave）   ：IP地址：192.168.124.129，mysql已安裝，沒有使用者資料。

主從伺服器均可正常提供服務。

同步整個資料庫

主從複製配置如下：

一。在主伺服器上操作：

1)、確保/etc/my.cnf中有如下參數，沒有的話需手工添加，並重啟mysql服務。

[mysqld]

log-bin=mysql-bin 啟動二進位檔案

server-id=1 伺服器ID

2)、登入mysql，在mysql中添加一個aaa的帳號，並授權給從伺服器。

[[email protected] ~]# mysql -uroot –p123456 登入mysql(預設沒有密碼)

mysql> grant replication slave on *.* to ‘aaa‘@‘192.168.124.129‘ identified by ‘1234‘; 建立aaa使用者，並授權給192.168.124.129使用。

3)、查詢主要資料庫狀態，並記下File及Position的值，這個在後面配置從伺服器的時候要用到。

mysql> show master status; 

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

二。在從伺服器上操作：

1)、確保/etc/my.cnf中有log-bin=mysql-bin和server-id=1參數，並把server-id=1修改為server-id=2。修改之後如下所示：

[mysqld]

log-bin=mysql-bin 啟動二進位檔案

server-id=2 伺服器ID

2）、重啟mysql服務。

[[email protected] ~]#service mysqld restart

3)、登入mysql，執行如下語句

[[email protected] ~]# mysql -uroot –p123456

mysql> change master to master_host=‘192.168.124.128‘,master_user=‘aaa‘,master_password=‘1234‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=258;

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

4)、啟動slave同步。

mysql> start slave;

5)、檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從複製串連正常。

mysql> show slave status\G

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

驗證配置是否正常，mysql主從能否正常複製。

在主要資料庫上建立一個庫，並且在庫中寫一個表和一些資料。

[[email protected] ~]# mysql -uroot –p123456

mysql> create database aaa;

mysql> use aaa;

mysql> create table user(id int(5),name char(10));

mysql> insert into user values (00001,‘zhangsan‘);

在從資料庫中驗證一下，是否正常複製到資料。

[[email protected] ~]# mysql -uroot –p123456

mysql> show databases;

mysql>use aaa;

mysql>select * from aaa.user;

從中的結果，我們可以看到mysql主從複製已經在起作用了，我們在主要資料庫中寫入的資料已經複製到我們的從資料庫中了。

同步單個庫

主從複製配置如下：

在主要資料庫上操作：

1)、確保/etc/my.cnf中有如下參數，沒有的話需手工添加，並重啟mysql服務。

[mysqld]

log-bin=mysql-bin 啟動二進位檔案

server-id=1 伺服器ID

binlog-do-db=test  #指定需要日誌的資料庫

[[email protected] ~]#service mysqld restart

2)、登入mysql，在mysql中添加一個使用者同步的帳號haha，並授權給從伺服器即Slave機器有File許可權，只賦予Slave機器有File許可權還不行，還要給它REPLICATION SLAVE的許可權才可以。。

[[email protected] ~]# mysql -uroot –p123456

mysql>grant file on *.* to ‘haha‘@‘192.168.124.129‘ identified by ‘1234‘;

mysql>grant replication slave on *.* to ‘haha‘@‘192.168.124.129‘ identified by ‘1234‘;

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

3）、用show master status/G命令看日誌情況。正常為：

4)、進行鎖表操作，不讓資料進行寫入動作，這麼做事為了防止從資料庫的未經處理資料和主要資料庫的未經處理資料不一致。

mysql> flush tables with read lock;

5)、使用mysqldump命令將剛才查詢到的庫匯出來。

[[email protected] ~]#mysqldump –uroot –p123456 aaa > aaa.sql

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

6)、將匯出來的庫檔案傳送到從資料庫的/tmp目錄下。

[[email protected] ~]#scp -r aaa.sql [email protected]:/tmp

這中間需要輸入一次驗證口令。見

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

7)、進入mysql，進行表解鎖操作。

mysql> unlock tables;

主要資料庫伺服器上的操作告一段落。

在從資料庫上操作：

1)、修改設定檔/etc/my.cnf

[mysqld]

log-bin=mysql-bin 啟動二進位檔案

server-id=2伺服器ID
master-host=192.168.124.128
master-user=haha    #同步處理的使用者帳號
master-password=1234
master-port=3306
master-connect-retry=60 #預設稍候再試60秒
replicate-do-db=aaa    #告訴slave只做backup資料庫的更新

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

2)、重啟mysql服務。

[[email protected] ~]# service mysqld restart

3)、登入資料庫，建立名為aaa的庫。

mysql> create database aaa;

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

4)、將傳過來的資料檔案分別匯入對應的資料庫下。

[[email protected] ~]# mysql -uroot –p123456 aaa < aaa.sql

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

5)、檢查主從同步，如果您看到Slave_IO_Running和Slave_SQL_Running均為Yes，則主從複製串連正常。

mysql> show slave status\G

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

驗證主從是否正常配置：

在主要資料庫上插入資料：

mysql> show databases;

mysql> show tables;

mysql> use aaa;

mysql> select * from aaa.user;

mysql> insert into user values (4,‘ergou‘);

mysql> insert into user values (5,‘sanwazi‘);

在從資料庫上查看是否正常同步：

mysql> show databases;

mysql> use aaa;

mysql> select * from aaa.user;

ps既做主又做從的my.cnf裡一定寫一行

log-slave-updates

mysql主從配置